In the last two weeks, two WordPress weblog sites have had their sites suspended or moved to interim servers because of performance issues. In both cases, the ISPs who hosted the sites (different companies) sent snapshots of the MySQL processes that caused the problems with the emails.
I worked with one of the sites offline, but the owner of the second site, Ampersand, posted the copy of the email he received at the WordPress support site. I grabbed a sample of it, as follows:
| 2073 | theenn2_amptoons | localhost | theenn2_MT | Query |
4 | Copying to tmp table | SELECT alas_posts.*,
MAX(comment_date) AS max_comment_date FROM alas_comments,
alas_posts WHERE alas |
| 2078 | theenn2_amptoons | localhost | theenn2_MT | Query |
4 | Copying to tmp table | SELECT alas_posts.*,
MAX(comment_date) AS max_comment_date FROM alas_comments,
alas_posts WHERE alas |
Plain as dirt what the problem is, eh?
Both of the weblogs are WordPress, but the SQL that generated the performance hit differed. With one, it was the latest comment plug-in; with the other, it was the SQL to support a category listing. In addition, this isn’t specific to WordPress–it could occur with the PHP-based version of Movable Type, ExpressionEngine, or any other MySQL based tools that have dynamic access.
Ostensibly, there is something wrong with these two sites. However, they’re only representative of what we’ll most likely see more of in the future. Our weblogging tools are becoming increasingly sophisticated, the data richer and more complex, the functionality modular and extensible by every person with a text editor and a yen–all packaged up in one reusable, standard, one-size-fits-all package. To make it all even more interesting, these applications are being installed on systems where you can get all you can eat for $5.88 a month, which means that a lot of sites need to be hosted on each server in order for the company to break even.
Things are bound to start breaking. But hey, it’s all fun, until you get that email that says your site is a pig, and it’s just been sent to the butcher.
Please! Won’t someone save the pig!
Now that we’ve established that the world is out to get our weblogs, let’s focus back on these problems, and in particular, the information that the ISPs sent.
In both cases, the messages contained a phrase “Copying to tmp table” and then what looks like a standard SQL query. If you look for this phrase in any of the WordPress code, you won’t find it–it’s a MySQL process that only shows up when you run SHOW PROCESSLIST within MySQL, or access the same from PHPMyAdmin.
Now, depending on your ISP’s tech person and how proficient they are with database optimization, you may be told that “Copying to tmp” is a ‘bad’ thing and shouldn’t happen, and therefore something is wrong and the code is crappy. Well, this isn’t true.
MySQL optimizes queries before they’re executed, to get the maximum amount of data in finished format with the minimum amount of processing and time. Part of the optimization can be to build a temporary table to hold an intermediate set of data before finishing the query. In addition, if the order the data (the sorting sequence) is on one column, but it’s grouped on another, or on a column in a different table, MySQL uses a temporary table.
There is a function, EXPLAIN, that provides information about how MySQL will execute a query. Developers use this in order to fine-tune the SQL so that the use of ‘expensive’ operations are avoided. If you have access to PHPMyAdmin, and run a query, the option to run EXPLAIN is provided with the results. Still, you can only tweak a query so much, and sometimes even the optimal SQL results in MySQL creating a temporary table.
When the use of a temporary table is always bad is when MySQL doesn’t have enough memory to hold all of the contents of the temporary table; the tool then needs to copy the contents to disk. Anytime MySQL has to go to the disk rather than memory, performance takes a hit. This shows up in the processes as:
“Copying to tmp table on disk”
However, what showed up with both of the weblogs that had problems is:
“Copying to tmp table”
An open question at MySQL asks whether this is the tmp table or memory, but from the impact to the servers, we can probably assume it’s to disk.
If it is, then the problem could be that tmp space allocated isn’t enough, and MySQL is having to write to the disk frequently. Or it could be, depending on your type of MySQL, that the maximum space allocated for memory is less than the maximum size allocated for the tmp space. Or other variations of settings at the database level.
Or it could be a badly written plugin, or too many plugins, or a cheap host that doesn’t allocate enough space for the sites hosted, or less than optimum SQL query, or even a trackback attack
Get used to sleeping with the pig
Ampersand’s site, Alas, a Blog gets between 1000 and 2000 visitors a day. It’s a popular site and gets lots of comments, and spam, of course, so several plug-ins were installed to help contain it. Evidentally, it was one of these plug-ins that started to have problems, because the query is not part of the WordPress code. In particular, if you look for max_comment_date in WordPress, you don’t find it.
However, with the second weblog, the query is within list_cats, a built-in WordPress function, and looks like the following:
SELECT cat_ID, cat_name, category_nicename, category_description, COUNT( wp_post2cat.post_id ) AS cat_count
FROM wp_categories
INNER JOIN wp_post2cat ON ( cat_ID = category_id )
INNER JOIN wp_posts ON ( ID = post_id )
WHERE post_status = ‘publish’
AND post_date_gmt < ‘2005-03-18 20:48:04’
AND cat_ID <>1
GROUP BY category_id
LIMIT 0 , 30
While Ampersand’s weblog problem was being discussed in the WordPress forum, a third person also had the same problem — most likely in a completely different area.
Bacon
With the growing number of ‘cheap’ hosting sites, and an increasing use of sophisticated PHP applications and MySQL queries, not to mention the extensibility of the tools, we’ll see more of this problem–especially as we demand more and more from our tools. Think about it: how many plugins are you using with your WordPress installation?
So what can you do? As a starter, you might want to look at that ‘good’ deal you get from your host. Not all inexpensive hosts cut costs and have less than optimal installations, but you’re less likely to have a host that will patiently work through problems with you if you’re only there for the 5.88 special.
You could also trim the fat by dropping plugins that you really don’t need, and make sure that whatever tools or applications or plugins you use are fully baked, i.e. have been through a healthy bug fix period.
If a problem does occur, make sure to file a report with the developers of whatever tool you’re using, providing all the information the host provides. The SQL used in the tool may not be optimum, and being informed of problems provides necessary feedback.
Hopefully this hasn’t been more info than you want or need (”too much sharing!”). At the least, if this situation comes up, you’re not going to be as intimidated when your host sends you an email that tells you …your site is a pig, fix it, or we’re kicking you off.