WordPress MU

Hello,

I'm having an issue with our website. A few of the queries on the query analyzer are taking up to/over an hour to complete (if they complete at all). This has caused our mySQL database server to lock up and frequently make our website unavailable. Does anyone know why these queries might be taking so long?

I just recently upgraded from WPMU 2.6 to 2.9.1 and deactivated all of our plugins, so it should be running smoothly. I was able to login to the WPMU dashboard last night, but now I can't even do that. Everytime I reboot the SQL container, it crashes within seconds again. Below is a list of the queries:

### 1744 Queries
### Total time: 457899, Average time: 262.556766055046
### Taking 7 to 4032 seconds to complete
### Rows analyzed 232 - 7390510
SELECT option_name, option_value FROM wp_XXX_options WHERE autoload = 'XXX';

SELECT option_name, option_value FROM wp_1_options WHERE autoload = 'yes';

### 339 Queries
### Total time: 439903, Average time: 1297.64896755162
### Taking 71 to 3684 seconds to complete
### Rows analyzed 0 - 0
UPDATE wp_XXX_options SET option_value = 'XXX' WHERE option_name = 'XXX';

UPDATE wp_1_options SET option_value = '1267560342' WHERE option_name = '_transient_doing_cron';

### 447 Queries
### Total time: 249068, Average time: 557.199105145414
### Taking 9 to 3969 seconds to complete
### Rows analyzed 1965597 - 7390510
SELECT option_value FROM wp_XXX_options WHERE option_name = 'XXX' LIMIT XXX;

SELECT option_value FROM wp_1_options WHERE option_name = 'embed_autourls' LIMIT 1;

### 6 Queries
### Total time: 52, Average time: 8.66666666666667
### Taking 5 , 5 , 6 , 7 , 8 , 21 seconds to complete
### Rows analyzed 4706242, 4706300, 5145379, 7390510, 7390510 and 7390510
use dbXXX_wpmu;
SELECT option_name, option_value FROM wp_XXX_options WHERE autoload = 'XXX';

use db30295_wpmu;
SELECT option_name, option_value FROM wp_1_options WHERE autoload = 'yes';

### 2 Queries
### Total time: 9, Average time: 4.5
### Taking 2 , 7 seconds to complete
### Rows analyzed 10522 and 10522
SELECT SQL_CALC_FOUND_ROWS wp_XXX_posts.* FROM wp_XXX_posts WHERE XXX=XXX AND wp_XXX_posts.post_type = 'XXX' AND (wp_XXX_posts.post_status = 'XXX') ORDER BY wp_XXX_posts.post_date DESC LIMIT XXX, XXX;

SELECT SQL_CALC_FOUND_ROWS wp_1_posts.* FROM wp_1_posts WHERE 1=1 AND wp_1_posts.post_type = 'post' AND (wp_1_posts.post_status = 'publish') ORDER BY wp_1_posts.post_date DESC LIMIT 0, 10;

bump

I raised the issue with the autoload query not using an index (as none is defined).

Have you tried optimizing your database?

Optimizing..like with mysqlcheck?

I'm talking about the mysql optimize command which analyses and optimizes your database tables.

Also you could look at tuning your MySQL server parameters.

Looking at those times there is something seriously wrong with your database

I updated the slow query report and I think the upgrade script may be stuck.

### 455 Queries
### Total time: 16395, Average time: 36.032967032967
### Taking 6 to 756 seconds to complete
### Rows analyzed 7390496 - 7390515
SELECT option_name, option_value FROM wp_XXX_options WHERE autoload = 'XXX';

SELECT option_name, option_value FROM wp_1_options WHERE autoload = 'yes';

### 180 Queries
### Total time: 5684, Average time: 31.5777777777778
### Taking 8 to 441 seconds to complete
### Rows analyzed 7390483 - 7390510
SELECT option_value FROM wp_XXX_options WHERE option_name = 'XXX' LIMIT XXX;

SELECT option_value FROM wp_1_options WHERE option_name = '_transient_update_core' LIMIT 1;

### 41 Queries
### Total time: 4746, Average time: 115.756097560976
### Taking 57 to 275 seconds to complete
### Rows analyzed 0 - 0
UPDATE wp_XXX_options SET option_value = 'XXX' WHERE option_name = 'XXX';

UPDATE wp_1_options SET option_value = 'a:8:{i:1267752197;a:1:{s:16:\"wp_update_themes\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:10:\"twicedaily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:43200;}}}i:1267759675;a:1:{s:19:\"wp_scheduled_delete\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:5:\"daily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:86400;}}}i:1267795361;a:1:{s:16:\"wp_version_check\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:10:\"twicedaily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:43200;}}}i:1267795376;a:1:{s:17:\"wp_update_plugins\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:10:\"twicedaily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:43200;}}}i:1267795397;a:1:{s:16:\"wp_update_themes\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:10:\"twicedaily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:43200;}}}i:1267813087;a:1:{s:10:\"polls_cron\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:5:\"daily\";s:4:\"args\";a:0:{}s:8:\"interval\";i:86400;}}}i:1268079820;a:1:{s:17:\"wp_db_backup_cron\";a:1:{s:32:\"40cd750bba9870f18aada2478b24840a\";a:3:{s:8:\"schedule\";s:6:\"weekly\";s:4:\"args\";a:0:{}s:8:\"interval\";i:604800;}}}s:7:\"version\";i:2;}' WHERE option_name = 'cron';

### 108 Queries
### Total time: 3362, Average time: 31.1296296296296
### Taking 8 to 88 seconds to complete
### Rows analyzed 7390500 - 7390515
SELECT autoload FROM wp_XXX_options WHERE option_name = 'XXX';

SELECT autoload FROM wp_1_options WHERE option_name = '_transient_update_core';

### 69 Queries
### Total time: 1930, Average time: 27.9710144927536
### Taking 8 to 158 seconds to complete
### Rows analyzed 0 - 0
DELETE FROM wp_XXX_options WHERE option_name = 'XXX';

DELETE FROM wp_1_options WHERE option_name = '_transient_update_core';

### 3 Queries
### Total time: 22, Average time: 7.33333333333333
### Taking 7 , 7 , 8 seconds to complete
### Rows analyzed 7390508, 7390510 and 7390510
use dbXXX_wpmu;
SELECT option_name, option_value FROM wp_XXX_options WHERE autoload = 'XXX';

use db30295_wpmu;
SELECT option_name, option_value FROM wp_1_options WHERE autoload = 'yes';

Any idea on how to fix this? I tried optimize and auto-repair, it didn't help...

Have you done a clean shutdown of the Database and then started it up and then shut it down cleanly again before opening it up. If you do that then you shouldn't have any transactional issues.

Also what are you using to produce those stats?

I'm using my web host's built in "slow query analyzer" feature (mediatemple). I haven't shutdown the database. How do I do a "clean shutdown"? Thanks for your help by the way.

I have no idea how you'd do it through their panel

I can log in and do it in the shell. Just not sure what you mean.

had the same problem:

upgraded from 2.9.1 to 2.9.2 and when I run the upgrade afterwards from within the wpmu-admin panel it crashes my server... well, more exactly it starts swapping like crazy.
After restarting the mysql server all is back to normal but the upgrade isn't complete.
Restarting the upgrade results in the same problem.

DB was definitely optimized and I have upgraded this particular wpmu installation since somewhere around version 1.5 or so - never had a problem :-(

Anyone upgrading to 2.9.2 had any similar problems?

Yes I'm trying to install 2.9.2 and it gets through the first part of the process - successfully adds the tables, declares success and gives me a password, etc - but when I try to run it declares itself unfit.

Tracing this through the debugger I get:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE autoload = 'yes'' at line 1"

which if ignored eventually results in a crash and the message that 'the install wasn't successful, contact your administrator'. I've contacted him, its me and as I don't know what the answer is I was hoping I would find it here. ;>(

I'm having this issue as well, I'm on a dedicated server and at approximately 2am sometimes a little later http becomes unresponsive.

It's been an ongoing issue for a month or so now, we recently enabled phpsuexec as my provider advised it would be easier to track the issue. They thought it could potentially be a DOS attack.

This evening/morning they assessed all the logs and claim the file wp-cron.php is the culprit as it constantly is on high load at around these times.

I was previously on an old version, about 3 years old and then upgraded to the latest stable version which is when this issue started to happen.

This is a small install but we seem to attract a ton of spam sometimes we get 40+ a day, if the site is turned totally off giving the HTTP response to try later then everything is fine. If I remove all spam blogs and prevent sign up but allow the site to stay live then its also fine and doesn't happen but if we have have some unchecked spam blocks and sign up is disabled then it can happen.

If the site is totally open and allows signups then its almost clock work.

But as I said, this has only happened since upgrading. Maybe this isn't a problem for some of the larger blogs such as Edu, the main WP site, etc because there servers would most likely be a higher spec than mine and have much more RAM to handle such surges of sploggers.

I've just deleted the file, I realise this means that http run cron jobs won't happen and future posts may not go live at the moment but it will test if this is the issue.

If this is the case, then is there a current or proposal or planned way of how to handle this?

I see this isn't really a new issue:

http://trinity777.wordpress.com/2008/10/28/wordpress-26-the-issue-of-wp-cronphp/

Perhaps a feature and php condition to prevent the file being run multiple times by a single IP address, or prevent the file from being run X amount of times per minute, hour, day.

I presume this file on an MU install is per blog and not a single load for the whole site?

Any advice or opinions would be appreciated. Perhaps there is already a fix?

Thanks :-)

Or set up blocks for the spammers before they even get to WP. ;)

I've tried a number of free methods which appear to work for a short while but then it happens again.

I presume you mean something along the lines like an IP block with .htaccess?

A project I work with called ImpressCMS a fork of XOOPS has a module called protector which detects crawlers, dos attacks and various other attacks. When it detects what it appears to be an attack it does as you choose from the admin preferences, options include:

Simple logging for admin
Temporary white page for the offender
Permanent IP ban to prevent further access

You can choose what to use per attack method. All data is stored in a trust path below the root to prevent http access. This is also automated meaning that if a new attack came at a time your not around it will handle it.

So far its prevented on one of our main sites a DOS attack and an SQL injection from a poorly coded module by a third party developer.

Is there anything similar for WP which I might have missed?

Thanks :-)

I've tried a number of free methods which appear to work for a short while but then it happens again.

Because the spammers read the forums too. ;) Then they code around it.

I presume you mean something along the lines like an IP block with .htaccess?

that's exactly the kind of thing I mean. :)