tspore Posted January 12, 2009 Share Posted January 12, 2009 I really need to speed up my mysql, and I think I have a lot of room for it to go. I am running mysql - 5.0.27 on Darwin - with core2 duo, SAS drives, 7 GB ram. I was over built the server, for web / email / mysql DB sites, but it is way to slow. Currently the server has been running for 10 hours - I have used 2.5 GB ram, and the processors are only around 10%, Ram is at around 2.5 GB, Read / writes are only around 3-4 MBS. So the machine isn't being taxed at all. Non DB sites load very quick, but my issues are with DB sites. They are by far to slow. I am running my-huge.cnf but I don't think that is enough. According to phpmyadmin I see these issues after 10 hours of the server running - Slow_queries 1 - than long_query_time Innodb_buffer_pool_reads 229 - not satisfy from buffer pool Handler_read_rnd 516 k - Handler_read_rnd_next 114 M - tables index Qcache_lowmem_prunes 9,101 - decide which queries to remove Created_tmp_disk_tables 1,455 -tmp_table_size Key_reads 110 k - key_buffer_size value is probably too small. Key_reads/Key_read_requests. Select_full_join 178 - check index Select_range_check 8 - check index Sort_merge_passes 152 - sort_buffer_size system variable. Opened_tables 4,870 - Table cache to small Table_locks_waited 31- split table / use replication So above are my "problematic variables" but really out I don't know what to change, to help get them under control. Here is a real world example - I have a site on a plesk server it takes 1.3 seconds to load home page (non DB used) and then ~1.3 seconds when a product is selected to open that page (DB is used) On my Darwin server - it takes less than 1 second to load the page (Apache / DNS superior) but to open the same product takes around 2-2.5 seconds (DB is used and slow) So outside of adding in my-huge.cnf I don't know what to do. I am thinking that I would like to throw more ram at it (but not sure how to do that) and also remove the cache make it 0 (query_cache_size is 32M now) Even in memory health my key buffer usage is 74.99M but every other second the hit rate is 100% and the traffic usage is every other second 100% too. So some advise on what to do would be greatly appreciated. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 12, 2009 Share Posted January 12, 2009 If it's a large site with lots of dynamic queries, or if you're changing data frequently, just go ahead and disable the query cache. If you have generic queries in which the return won't change for a while, the query cache can be good. How many queries does your MySQL server get per second? That should help us figure out if it's a problem with the number of queries, or the configuration. Based on the power of your server (although you didn't say CPU speed or RAM speed), I would imagine it's config based (MySQL configured badly, indexes, or something else) unless your site gets quite a bit of traffic. Quote Link to comment Share on other sites More sharing options...
tspore Posted January 13, 2009 Author Share Posted January 13, 2009 Thanks for replying back - Here is the other info - 2 x 2 Ghz Dual-core processors and 7 GB of 667 MHz DDR2 Ram. The server gets ~14.21 Queries per second. How can I dedicate 1 GB of ram to the server? I will also disable the Cache on the server tonight. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 13, 2009 Share Posted January 13, 2009 With MySQL, you can't exactly throw memory at it. You have to tweak individual settings. 14.21 queries per second shouldn't be intense at all x.x. Chances are you have some horrible queries in there somewhere. Anyway you could find the slow ones? Have you tried enabling the slow query log? How many databases/tables do you have? If you don't have many, you could just post the schema and some common queries. You could post the SQL from one of your slow loading pages, but that probably wouldn't help since other things could influence it too. PHPMyAdmin is usually pretty good at giving advice as to what should be tweaked. Quote Link to comment Share on other sites More sharing options...
tspore Posted January 13, 2009 Author Share Posted January 13, 2009 I disabled cache, and that has already helped a ton. My forum, was going down to a crawl, it was almost unbearable for many. Ok I will leave the cache disabled, and play with it unless someone has something specific. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 13, 2009 Share Posted January 13, 2009 I think it's probably just 1 or two bad queries somewhere personally. Did you try the slow query log? Quote Link to comment Share on other sites More sharing options...
tspore Posted January 13, 2009 Author Share Posted January 13, 2009 I will turn it on tonight, but phpmyadmin show 0 slow queries. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 13, 2009 Share Posted January 13, 2009 Errr... if it's not turned on, PHPMyAdmin would show 0 ;p. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.