MySQL Performance Optimization - A Lesson
By Angsuman Chakraborty, Gaea News NetworkWednesday, March 8, 2006
The MySQL database supporting this blog was consuming massive amount of CPU, which effectively lead to site going down 3-4 times this week. I knew an obvious culprit. However reality differed.
I knew for sometime I had a jumbo query, which was not cached, and is likely to contribute to MySQL server load:
SELECT cat_ID, cat_name AS Category, count( * ) AS ‘Count’
FROM $wpdb->categories, $wpdb->post2cat, $wpdb->posts
WHERE cat_ID = category_id and
category_nicename != ‘headline’ and ID = post_id and
post_status = ‘publish’
GROUP BY cat_name
HAVING count( * ) > 10
ORDER BY ‘Count’ DESC
LIMIT 0 , 10
I couldn’t see an obvious way to optimize this without curbing the functionality. In case you are wondeering substituting category_nicename != ‘headline’ with cat_ID = 37 doesn’t help.
I also thought about using slow query log to pinpoint the problem. Fortunately none of them was required. I got an email from friendly WestHost High Load support. Jonny found out by repeatedly running ‘SHOW FULL PROCESSLIST;’ that the culprit was:
SELECT distinct cat_ID, cat_name
FROM st_categories, st_post2cat
ORDER BY category_nicename;
This took me by surprise. Th query takes anywhere between 3-5 seconds. In high loads with multiple simultaneous requests that is good enough to overload the server. The solution was deceptively simple:
SELECT distinct cat_ID, cat_name
FROM st_categories
ORDER BY category_nicename;
The original query was redundant and wasteful. Originally I intended to display only categories with one or more posts in them. However somewhere down the line I forgot to add the WHERE clause. Today all my categories have posts. So simply removing st_post2cat from the FROM list is good enough to bring down the execution time to 0.01 seconds.
The take home lessons are:
- Never guess bottleneck queries; find it.
- Simple tool like “SHOW FULL PROCESS LIST” is often as good as definitive, yet harder to analyze tool like slow query log.
- Having good support from your web hosting provider doesn’t hurt either
June 23, 2006: 4:08 pm
I too am on Westhost and have been working with people like Jonny to find a solution to my blog problems. I too am using WP-Cache, but now my blog is consuming 4.52% of the entire server from my VPS. Ia m not as technical as you, so we have slow-going, but I will post any solution I find. Tks! |
May 10, 2006: 6:20 am
The code was specified only as an example. It is in a plugin I wrote for this blog. You will not find it in generic wordpress installation. However if you have a highly commented blog you may benefit from this wp-cache optimization technique. |
Paulus |
May 10, 2006: 2:22 am
Hello, My wordpress just crashied my semi-dedicated server due to high CPU load.. So I just installed the WP-Cache plugin and found this blog.. I asked my webhosting admin to do this optimization and here’s what he wrote me: I checked all of your files for the text “SELECT distinct cat_ID”, and couldn’t find a match. I also checked for “SELECT cat_ID, cat_name AS Category, count(” and couldn’t find a match either. I also checked for “st_post2cat” and couldn’t find it, so they may be running a different version of the code. I was just wondering if you could help me regarding this matter. BTW, I’m running wordpress 2.0.2 Thank you for your time…you can just reply this to my e-mail.. Regards, Paulus |
Westhost User Too