MySQL CPU usage hits 500%+ and Site Crashes up on Approving Comments

[syedabbas]

I have been using WordPress 2.7.1 for a while now but I have started facing an issue with the Database recently.

7 out of 10 times I take action on "unapproved" comments in the Admin panel, MySQL process on my host hits 500%+ CPU usage and the site goes down.

The only way to bring the site back up is by dropping the existing tables in the database and installing the backup.

When I look into the database I see more than 60 queries stacked up running from anywhere between 10 to 1500 seconds.

Following query is found lined up in the database
"SELECT c . * , p . * FROM wp_comments c INNER JOIN wp_posts p ON p."

Query status on each of the queries is "Sending Data"

Initially had about 1000 unapproved comments and I thought that might be the problem. I cleared the unapproved backlog but the issue still exists.

I do not approve comments on my site until I take backup of the database everyday.

Hope this information helps in getting to the root of the problem.

[filosofo]

Replying to syedabbas:

Following query is found lined up in the database
"SELECT c . * , p . * FROM wp_comments c INNER JOIN wp_posts p ON p."

I don't see any query using "c" as an alias for wp_commments in 2.7.1 or any queries like that, so I bet it's a plugin. Try disabling all plugins to see if the problem goes away.

[syedabbas]

Thanks for the suggestion Filosofo. I did zero down on a plugin I have been using for almost a year now.. "Comment Relish".

Found the query in the code of that plugin. Things seem to be fine now..

Thanks again!