Yeah, we know, you can’t read the blog. We are once again the victim of frequent “This Account Has Exceeded Its CPU Quota” errors. Apparently we have a bunch of slow mysql queries, and need to optimize our indices. Which might be very straightforward, if any of us knew what those words meant. Dammit, Jim, I’m a doctor, not a database manager!
Here is the kind of error message we’re getting:
# Sat Jun 9 01:23:22 2007
# Query_time: 4 Lock_time: 0 Rows_sent: 27359 Rows_examined: 83792
SELECT
comment_post_ID, post_title
FROM (wp_comments LEFT JOIN wp_posts ON (comment_post_ID = ID))
WHERE comment_approved = ‘1’
AND comment_type NOT LIKE ‘%pingback%’ AND comment_type NOT LIKE ‘%trackback%’
ORDER BY comment_date DESC
Full of important information, I’m sure, but I have no idea what it means or how to fix it. We might just change web hosts as a way to sidestep the problem, but that sounds like work. Any other suggestions?
Update: The particular problem mentioned here has been traced to a particular plugin and fixed. We’ve eliminated all of the noticeably slow mysql queries, but the problem persists. Once in a while an apparently ordinary request (“GET” a certain page, for example) takes 30 seconds, for no discernible reason. We’ve optimized the database, and even created some new indices, although I’m not even sure if that helps or hurts things. Maybe it will fix itself.
Don’t know how much I could help. But first I would need to know which tables you have… Do you have any control on databases?
I would look for, what’s the need to do a LEFT JOIN… and how did you get 27359 rows…
@Guillermo, it seams that the query returns the post title, so the JOIN to posts table is needed.
Why do you need to retrieve all the posts? are you doing paging stuff in the blog app itself rather than in the DB?
Anyway, even if the query logic is indeed needed, there are more problems.
1. The comment_type field might not be indexed.
2. If applicable, I’d loose the % on the search terms (if the comment_type field includes the types ‘pingback’ and ‘trackback’).
3. Also verify that the field comment_approved is indexed.
If you cannot revise the application and/or the DB, then I suggest you switch to another blogging engine, rather than switching the host.
Thanks for the comments, but just to emphasize: I have no idea what you are talking about. I don’t know what a table is, what paging is, what indexing is. And I sort of don’t want to know, although I do want this problem to go away.
Changing blogging software is very unlikely, as it would require a complete reconstruction of the site, exactly what we don’t want to do.
I’m given to understand the new version of WordPress is optimised somewhat. I don’t know what version you’re running, but getting the latest would seem sensible.
If CPU time is a problem, it might also be worth replacing Wordpess’ search box with a Google site search. It wouldn’t look as in-keeping with the main site, but it’d shift a large amount of CPU power from your servers to Google’s, who don’t seem to mind.
It probably wouldn’t solve the problem, but it’d certainly reduce it.
This is the slow part:
“comment_type NOT LIKE ‘%pingback%’ AND comment_type NOT LIKE ‘%trackback%'”
The % is a wild card. It appears as if the comment type is embedded in a string of characters in one of the database field. The query looks through all the “comment_type” fields, it then looks for the letters “pingback” anywhere in that field. If it finds “pingback” it then looks for “trackback”. If it also finds “trackback” then it excludes that row from the result set.
For what this query logic appears to be doing, this is a poor way to go about doing it for a couple of reasons. First, the wild card searches are relatively slow. Second, it’s doing the wild card searches on items which will be excluded from the final results. This means it has to read the result row, then execute the pattern matching logic, then exclude the matches.
A better way is to do the search for what you want to return. In other words, “Give me only these results” instead of “Give me everything, then exclude the things that match ‘this’ and ‘that'”.
Another way to speed things up is to use some sort of flag to mark the different types of items you’d be searching for regularly. If, for example, you had comments of type “pingback”, “trackback”, and “regular” and you assigned them the integers 1, 2, and 4 (I skipped three for a reason) and placed those integers in the comment type field, you could then do a query like “Give me everything where comment_type = 4”. This would be much, much faster.
Why did I skip three? There is a neat little trick you can do when you use flags based on powers of two (1, 2, 4, 8, 16, 32, 64, etc…). If you want all comment_types that are “pingback” OR “regular” ( 1 + 4 = 5 ) you can use some boolean algebra to do a quick query for those also. “Give me everything where comment_type logically ANDed with 5 is TRUE”
It looks like WordPress needs some redesign. Depending on the core design, it may be a trivial fix or a complex overhaul. These sorts of things can turn into a string of dominoes. It also looks like you’re not in a position to do anything about it.
Brian, thanks, that was actually very helpful. I tracked down where that line was coming from, and it was actually in the “latest comments” plugin that shows the recent comments up on the sidebar. Then I went and looked for the most recent version of that plugin, and found that they had indeed changed that line to remove the wildcards. So I’ve installed the updated version; let’s see if that makes a difference.
Awesome! 🙂 I hope it helped — this blog is much more interesting than reading a CPU quota error. Yes, really! 😉
… and actually, Brian’s insights in SQL optimizations may have been a bit helpful for me too, building some SQL queries for a geographical database on a networked Oracle database. I.e. lots of unnecessary network traffic if the queries aren’t optimized well enough. I’ll especially keep his “bit” trick into mind, because I *do* have control over my queries. 🙂
you are an astronomer. Doctors save people…. At most we bore them.
Pingback: Quest » Blog Archive » Downtime
A table is (an implementation of a set-theoretic) relation.
E.g. { (post1, comment1), (post1, comment2), (post2,comment3), (post1,comment4)}
Paging is how a computer manages data transfers between main memory and disk. To quote: “The operating system copies a certain number of pages from your storage device to main memory. When a program needs a page that is not in main memory, the operating system copies the required page into memory and copies another page back to the disk. One says that the operating system pages the data. Each time a page is needed that is not currently in memory, a page fault occurs.”
Each page fault greatly slows the program down.
A index is an implementation of a quick lookup in a database table, kind of like a book index.
A join is a Cartesian product of two relations, followed by some filtering condition.
E.g., given relations R1 = { (post#, comment#),…} and R2 = { ( comment#, author),….} a join would be used to find all posts with comments by a particular author, as the subset of R1 x R2 where R1.comment# = R2.comment# and
R2.author = .
I haven’t found a good exposition of the relational algebra/relational calculus on the web, but for any physicist, about 20 minutes with that abstraction will permanently demystify relational databases. The problem is that usually databases are taught to people who are less able to deal with abstraction and so the web presentations of the relational algebra are verbose.
Another 20 minutes on how engineers have dealt with the fact of the relative slowness of main storage relative main memory and slowness of main memory with respect to CPU will clear up the rest.
When I was actively using wordpress, I think it did keep all of the messages that got blocked as spam. I think you can configure it not to do this. One thing that does happen is that your comment tables will get *huge* with spam. That not only uses up disk space, but will start to slow down queries like this one.
I would hesitate to give you any actual sql to do anything about this here, but you might be able to check your wordpress config to see if you can do anything about it there.
What I did myself was go in every so often and delete all the spam; it’s a quick SQL command, and I *am* a database manager in addition to all those other things….
-Rob
One more thing that could be slowing this down is the lack of any date restriction; it’s retrieving all non-pingback, non-trackback comments since the beginning of time. Given the location as you describe it (to generate the “lastest sidebar” list), if you have any control over the query you may want to tweak that. Then again I may be biased by working in an environment where all queries are required to have a closed daterange due to the extreme volume of data, so that may actually not help all that much here.
Updating the “latest comments” plugin (which led to the above error) doesn’t seem to have helped, nor has disabling it entirely. I’ve even optimized the databases, for what it’s worth. But the problem persists.
Perhaps your hosting service would be willing to look at some of the SQL queries for you and check if the right fields are indexed in the database? After all, it is their server time that you are using if the database isn’t structured well.
Compacting the database would normally improve its efficiency a lot.
I wonder if the software you use permanently deletes unapproved comments, or simply leaves them in there flagged?
The Query is checking the flag ‘comment_approved’ as part of a boolean evaluation with 3 conditions. This is a very CPU intensive operation.
If for example you are receiving lots of unwanted comments and these remain in the database it might slow it down to the point where it exceeds the providers quota. Possibly something that could be exploited in denial of service attacks.
I believe that you just have a lot of visitors and that your host has a strict quota requirement, better suited to simpler or low-traffic sites. The solution is probably to ask them increase the quota, or change host.
Pingback: Cosmic Variance account is suspended on Bluehost.com « Bob Dudesky
To optimize, you may also try some of the following:
• if possible, upgrade to the latest WP version or at least 2.1;
• if your host allows, enable the mySQL query cache (and possibly also use this plugin)
• enable the WordPress caching system by adding this line to wp-config.php:
define( ‘ENABLE_CACHE’, true );
(and make sure that wp-content/cache is writable or has 777 permission).
• For a stronger cache, try wp-cache2 which staticizes the whole site.
Check your usage, in terms of number of pageviews – i found (and have started reading) this blog due to an article on slashdot, which commands a hefty readership. I can’t recall if you were directly linked but I still found you – others would have too.
It may be you’re just another victim of your own popularity and you need to host your blog somewhere that allows you to use some more resources – although i must say that optimisation can often stave off such moves for some time, a well thought out database can use a fraction of the resources.
I did see a good blog article on this from a bloke running a large south-african web application, but buggered if I can find it again 🙁
Ignore all these suggestions! I’ve been in the field for forty years, and you had it exactly right at the beginning:
That has always worked for me, though some of the worse FORTRAN II bugs didn’t fix themselves until all the hardware had become obsolete.