Very slow MySQL Queries on better hardware
Posted by YYamagishi, 03-27-2008, 08:12 PM Hi, I'm having a problem with a MySQL servers. I had: Core2Duo E6600 4GB Ram 320GB SATA Now I have: Dell SC1435 Opteron 2200 4GB Ram 73GB 15k RPM SAS Now here's the problem. I have a very big IPB board and it takes about 10 seconds to load a single page. (Servers are clustered) I just changed the datacenter and only difference in spec is that MySQL server. From looking at it, I can pretty much can say this is a problem with MySQL server as it comes up in slow-queries-log. The server has CentOS 5 with MySQL version 5.0.22 I have copied the database using mysqlhotcopy. Here is my my.cnf: Here's the slow query log: Here's the EXPLAIN Query for each query above: What I have tried: Try 1: Try 2: Any clues? Thanks
Posted by wKkaY, 03-27-2008, 10:01 PM Judging from the few queries you've posted, my opinion is that it's the queries are just slow. Doing things like changing server variables or running OPTIMIZE on the tables won't help significantly. Going into each query.. ... WHERE a.attach_ext='torrent' ORDER BY t.fid... Possibly add an index to ibf_attachments.attach_ext ... LOWER(p.post) LIKE '%10000 B\.C\.%' ... ... LOWER(p.post) LIKE '%inside out%' ... Holy moly, that's a substring search - very expensive! No amount of tuning can save you for this one. I'm pretty sure this is some custom code - IPB won't be written like this. ... where LOWER(name)='username-was-here' ... Unless you have 100000s of members, I don't see why this query runs so slowly. If you really do have 100000s of members, I suggest you remove LOWER() from the query (it's irrelevant for MySQL) and index on ibf_members.name.
Posted by YYamagishi, 03-28-2008, 12:14 AM Well the thing is it was running very fast on the old server. Around 15ms page generation time. Any comments on why it runs fast on the old server and doesn't on the new server?
Posted by RBBOT, 03-28-2008, 10:37 AM Any query where the rowsexamined is in the 100,000s for an interactive web based application is a poor query and needs better indexing. However with forums, these queries are the result of extensions/plugins written by 3rd party programmers who don't think about database performance so don't add new indexes to the forums database, or use the existing ones properly. As to why the poor performance: There is no such config setting as key_buffer - you can remove that. key_buffer_size of 64Mb on a 4Gb machine is probably too small depending on how big your databases are. Run http://hackmysql.com/mysqlreport for a report on how effective your settings are. There is guide on the website on how to interpret the report and which settings to change. That site also has some tutorials on how to interpret the output of the EXPLAIN queries and decide what indexes to create. (edit: Apologies for the bad formatting in the initial version of this post - it seems the noscript Firefox extension makes these forums concatenate everything you type into one long sentence) Last edited by RBBOT; 03-28-2008 at 10:42 AM.
Posted by wKkaY, 03-28-2008, 11:19 AM Don't know. Do you still access to the old servers? Are there any other variables? (increased traffic, data, etc)
Posted by HKPlane, 03-29-2008, 05:02 AM If everything is exactly the same, then the only reason there is hardware failure or driver problem. Run Prime to burn the system and mem86 for memory test(but it should require a support staff to burn the mem86 boot disc and you monitor the result via KVM). You may want to do some BIOS tweaking too. http://mersenne.org/freesoft.htm http://memtest86.com/
Posted by YYamagishi, 03-29-2008, 07:42 AM I had to move back to the old datacenter as it seemed to be a datacenter's issues. I wasn't able to solve the problem
Posted by HKPlane, 03-29-2008, 08:21 AM Your new server is(was) indeed better for DB purpose. AMD has better memory performance and you are on SCSI 15krpm. It's very common to see performance go downhill when heavy loading if there are some hardware have stability issues. You may not experience crash if the problem isn't big enough but you definitely will feel something wrong.