Very slow database queries causing db errors

(@iso50)

Over the past couple months I’ve been having some strange issues with very long database queries which end up crashing the db and turning up the “error: unable to connect to database” message. Rebooting the db container I’m on (Mediatemple is my host) usually clears it up. Any help or ideas would be greatly appreciated.

The site is http://blog.iso50.com

Here is the db log of queries taking over 1 second which was created right after one such crash:

Created 10:00 PM 08/13/2009
### 427 Queries
### Total time: 34357.839755, Average time: 80.4633249531616
### Taking 5.289689 to 203.258867 seconds to complete
### Rows analyzed 0 - 312
SET timestamp=XXX;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'XXX';

SET timestamp=1250225980;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

### 281 Queries
### Total time: 22646.863787, Average time: 80.5938213060498
### Taking 5.212969 to 243.049226 seconds to complete
### Rows analyzed 0 - 312
# User@Host: dbXXX[dbXXX] @ [XXX.XXX.XXX.XXX]

# User@Host: db26001[db26001] @ [70.32.68.20]

### 152 Queries
### Total time: 13694.990108, Average time: 90.098619131579
### Taking 5.212969 to 243.049226 seconds to complete
### Rows analyzed 68 - 312
SET timestamp=XXX;
SELECT option_name, option_value FROM wp_options;

SET timestamp=1250225990;
SELECT option_name, option_value FROM wp_options;

### 17 Queries
### Total time: 1020.516299, Average time: 60.0303705294118
### Taking 16.693573 to 124.666902 seconds to complete
### Rows analyzed 0 - 0
SET timestamp=XXX;
UPDATE <code>wp_options</code> SET <code>option_value</code> = 'XXX' WHERE <code>option_name</code> = 'XXX';

SET timestamp=1250224965;
UPDATE <code>wp_options</code> SET <code>option_value</code> = '301766' WHERE <code>option_name</code> = 'akismet_spam_count';

### 4 Queries
### Total time: 351.330548, Average time: 87.832637
### Taking 54.716655 , 58.861937 , 116.705437 , 121.046519 seconds to complete
### Rows analyzed 0, 0, 0 and 0
SET timestamp=XXX;
INSERT INTO wp_fs_visits (ip, url, time_begin, time_last) VALUES ('XXX','XXX',XXX,XXX);

SET timestamp=1250224965;
INSERT INTO wp_fs_visits (ip, url, time_begin, time_last) VALUES ('67.175.246.73','/feed/',1250224844,1250224844);

### 3 Queries
### Total time: 269.326101, Average time: 89.775367
### Taking 74.740510 , 94.089207 , 100.496384 seconds to complete
### Rows analyzed 0, 0 and 0
SET timestamp=XXX;
DELETE FROM wp_options WHERE option_name = 'XXX';

SET timestamp=1250225570;
DELETE FROM wp_options WHERE option_name = '_transient_doing_cron';

### 2 Queries
### Total time: 133.638565, Average time: 66.8192825
### Taking 56.902287 , 76.736278 seconds to complete
### Rows analyzed 0 and 0
SET timestamp=XXX;
UPDATE wp_fs_visits SET time_last=XXX,url='XXX' WHERE ip='XXX' AND time_last > XXX;

SET timestamp=1250225570;
UPDATE wp_fs_visits SET time_last=1250225493,url='/feed/' WHERE ip='209.20.77.74' AND time_last > 1250221893;

### 1 Query
### Total time: 83.756655, Average time: 83.756655
### Taking 83.756655 seconds to complete
### Rows analyzed 0
use dbXXX_wp;
SET timestamp=XXX;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'XXX';

use db26001_wp;
SET timestamp=1250223480;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Viewing 15 replies - 1 through 15 (of 18 total)

1 2 →

laviolette99
(@laviolette99)

14 years, 8 months ago

Hi,

I have the same type of slow queries. Have you found anything on this?

Cheers!

peps974
(@peps974)

14 years, 6 months ago

I have experienced the same issue lately, slow query that crashes the db… forced to reboot it!

Any tips, suggestions on how to fix it?

tydende
(@tydende)

14 years, 5 months ago

anybody find a solution to this? i’m having the same problem. my slow queries all look something like….

# Thu Apr 23 04:24:20 2009
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT option_value FROM wp_options WHERE option_name = ‘id_blogID’ LIMIT 1

radalin
(@radalin)

14 years, 4 months ago

I nearly have the same type of problem the exact same queries (WHERE autoload = ‘yes’) seems to fail all the time. It does not cause any db errors because my hosting company does stop the query.

Is anyone found a solution for it yet? I have the latest version of wordpress (2.8.6) for the moment

icc97
(@icc97)

14 years, 3 months ago

I too have experienced the same problem. This was the first slow query: `use wordpress;
UPDATE wp_options SET option_value = ‘1262430076’ WHERE option_name = ‘_transient_doing_cron’;`

Robert S
(@rschilt)

14 years, 3 months ago

Have any of you DB repair and cleanup utilities across your tables?

R

icc97
(@icc97)

14 years, 3 months ago

Hi rschilt, I did have some defragmented tables, but I did a defragment of the tables about three months ago. I don’t know how much of a problem that should cause. Certainly the queries shouldn’t start taking 10 – 120s.

I should also mention that the slow running queries were almost all SELECTs on the wp_options table, e.g.:
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

My problem is also made worse by having a phpbb forum that uses up database connections.

I’ve been monitoring the database constantly using mysqltuner for months now. I also started using query cache two months ago to prevent some regular crashes. As well I’m using wp-cache.

Robert S
(@rschilt)

14 years, 3 months ago

Three things:

1) You need to run Repair and Optimise regularly – at least weekly on busy sites.

2) Having multiple caching plugins running can in fact slow down performance rather than speed it up.

3) If you deactivate all plugins (after running Repair and Optimise) do you still experience same performance issues?

R

icc97
(@icc97)

14 years, 3 months ago

Hi rschilt,

Thanks for the suggestions.

1) I’ve optimised of all tables using this defrag script. Assuming your talking about the REPAIR TABLE command the MySQL doc implies that is only needed for drastic problems not for regular maintenance.

2) I only have the one WP-Cache plugin, the query cache is the MySQL query cache not a wordpress plug-in. I only started using WP-Cache as a last resort as the crashes were becoming weekly.

3) I ideally don’t want to de-activate all plug-ins, but I’ve de-activated the simple tags plug-in which is suspected of causing problems.

s_ha_dum
(@apljdi)

14 years, 3 months ago

How big is your wp_options table? None of those queries should be taking that much time unless 1) the table is broken (but if you’ve optimized and repaired as rschilt suggested that shouldn’t be it), 2) the table is staggeringly huge, or 3) the server is seriously overloaded.

As for repairing the table, I think this line– ‘Normally, you should never have to run REPAIR TABLE’– is possibly a bit too much wishful thinking on the part of the MySql team or maybe it turns on a peculiar conception of ‘normally’. I’ve more than once had to run that command, though rarely which is perhaps what is meant. You can run check table first to see if the table is broken.
icc97
(@icc97)

14 years, 3 months ago
Hi aplji,

The wp_options table is just over 1MB (297 rows) in size, and the check table command returns an ‘OK’ status. The server suffers from only having 500MB of RAM, so it runs out of memory fairly quickly, but it does not explain why some of these queries are taking so long. Here is the initial slow queries (anything longer than 2 seconds):

# Time: 100102 11:01:22
# User@Host: XXX[XXX] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET NAMES ‘utf8’;
# Time: 100102 11:01:24
# User@Host: XXX[XXX] @ localhost []
# Query_time: 8 Lock_time: 2 Rows_sent: 0 Rows_examined: 0
use wordpress;
UPDATE wp_options SET option_value = ‘1262430076’ WHERE option_name = ‘_transient_doing_cron’;
# Time: 100102 11:01:26
# User@Host: XXX[XXX] @ localhost []
# Query_time: 10 Lock_time: 0 Rows_sent: 232 Rows_examined: 297
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;
# User@Host: XXX[XXX] @ localhost []
# Query_time: 8 Lock_time: 2 Rows_sent: 0 Rows_examined: 0
SELECT option_value FROM wp_options WHERE option_name = ‘sociable_useiframe’ LIMIT 1;
# User@Host: XXX[XXX] @ localhost []
# Query_time: 9 Lock_time: 1 Rows_sent: 232 Rows_examined: 297
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;
# Time: 100102 11:01:27
# User@Host: XXX[XXX] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;
# User@Host: XXX[XXX] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET NAMES ‘utf8’;
# Time: 100102 11:01:28
# User@Host: XXX[XXX] @ localhost []
# Query_time: 10 Lock_time: 6 Rows_sent: 232 Rows_examined: 297
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;
# Time: 100102 11:01:31
# User@Host: XXX[XXX] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (16);
# Time: 100102 11:01:36
# User@Host: XXX[XXX] @ localhost []
# Query_time: 20 Lock_time: 2 Rows_sent: 232 Rows_examined: 297
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;

Nothing special except for these slow queries remain and get steadily longer. As a result the max connections (set at 100) is reached in about 10 minutes. The initial slowdown co-incides with some hacker blindly various mysqladmin URLs.

I have the following plug-ins active if it helps:
- Akismet
- All in One SEO Pack
- Breadcrumb NavXT
- Dagon Design Sitemap Generator
- Exclude Pages from Navigation
- Google XML Sitemaps
- Role Manager
- Ryans Suckerfish WordPress Dropdown Menu
- Simple Tags (now de-activated)
- Sociable
- TinyMCE Advanced
- wp-cache
- WP-CMS Post Control
Robert S
(@rschilt)

14 years, 3 months ago

@icc97,

I ideally don’t want to de-activate all plug-ins, but I’ve de-activated the simple tags plug-in which is suspected of causing problems.

I fully understand why you would not want to deactivate plugins (permanently) but sometimes we need to do this to assist with troubleshooting (eg: in your case now).

I have had several performance issues over the past couple of years and I could have sworn black and blue that it must have been my host – only to learn that the issues were being caused by a plugin or two.

Can I suggest that you put your blog in Maintenance mode (should not take more than 5 mins) – deactivate all plugins – test performance. This will allow you and folks assisting you with this issue to eliminate any rogue plugins from being at fault.

R
icc97
(@icc97)

14 years, 3 months ago
Hi rschilt,

The problem is that the performance is fine 99% of the time and I don’t have any tools for stress testing our site.

However I’ll deactivate all but the most fundamental plug-ins for our site:
- Akismet
- Exclude Pages from Navigation
- Ryans Suckerfish WordPress Dropdown Menu
Robert S
(@rschilt)

14 years, 3 months ago

@icc97,

With all respect – I think you have missed the point. Deactivating ALL plugins (not just a few) to resolve performance issues is an invaluable tool to assist with the process of elimination.

Good luck anyway!

R

s_ha_dum
(@apljdi)

14 years, 3 months ago

1mb in the options table shouldn’t be a problem. I have half that RAM on my development server and it works fine but on a production server that could be a real problem. The really weird thing about those slow queries is that none of those are the kind of thing that should be giving you trouble. And if fact, none of those ever show up in my slow query log– even on my very slow server. You are running a few plugins. rschilt is on the right track here. Seems to me that something has to be eating up your server resources and the plugins are the first place to look.