WordPress.org

Ready to get started?Download WordPress

Forums

Very slow database queries causing db errors (19 posts)

  1. iso50
    Member
    Posted 5 years ago #

    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';
  2. laviolette99
    Member
    Posted 5 years ago #

    Hi,

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

    Cheers!

  3. peps974
    Member
    Posted 4 years 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?

  4. tydende
    Member
    Posted 4 years 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

  5. radalin
    Member
    Posted 4 years 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

  6. icc97
    Member
    Posted 4 years 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';`

  7. Robert S
    Member
    Posted 4 years ago #

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

    R

  8. icc97
    Member
    Posted 4 years 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.

  9. Robert S
    Member
    Posted 4 years 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

  10. icc97
    Member
    Posted 4 years 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.

  11. s_ha_dum
    Member
    Posted 4 years 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.

  12. icc97
    Member
    Posted 4 years 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
  13. Robert S
    Member
    Posted 4 years 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

  14. icc97
    Member
    Posted 4 years 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
  15. Robert S
    Member
    Posted 4 years 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

  16. s_ha_dum
    Member
    Posted 4 years 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.

  17. icc97
    Member
    Posted 4 years ago #

    Hi @rschilt,

    Unfortunately I have no way to test performance the site with all plug-ins removed and its not acceptable to leave our production site without navigation (the remaining plugins) for however long it takes until it crashes again.

    The site will only crash when it comes under heavy load (typically a spammer/hacker of some form).

    Thank you for your help thus far, I'll let you know if the problems occur again. If they do then I'll remove the remaining plug-ins as a last ditch effort.

    @aplji indeed these queries typically never show up in my slow query log, its only after an attack that the queries slow down.

  18. s_ha_dum
    Member
    Posted 4 years ago #

    The site will only crash when it comes under heavy load (typically a spammer/hacker of some form).

    Ok. This changes things-- a lot. You don't have a database problem! You have a hacker problem. You need to be looking into protecting the site, not worrying about speeding up the queries. Look into plugins like Bad Behavior.

  19. icc97
    Member
    Posted 4 years ago #

    Thanks @apljdi, I'll give it a try.

Topic Closed

This topic has been closed to new replies.

About this Topic