MySQL Slow Queries – Assitance Needed & Greatly Appreciated

  • The last three days my blog has been racking up tons of MySQL slow queries which has caused BlueHost to suspend my account each evening. The quieries seem to be all over the board in terms of which databases and tables are causing them, so it appears to be a general WordPress issue. BlueHost support had me install wp-cache and later wp-super-cache plug-ins to help fix the problem, but each time it seems to temporarily work but before I know it I’m being bombarded again.

    Below are 4 sample queries I’ve received recently, but there have been several other different types. We’ve recently (in the past 3 weeks) received an increase in traffic (up from ~17k pageviews daily to ~26k) but nothing has changed in the past two days.

    Any help is EXTREMELY appreciated. Thanks in advance

    # Thu Apr 23 10:00:49 2009
    # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use thisyoun_wrdp1;
    SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’

    # Thu Apr 23 10:01:18 2009
    # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use thisyoun_wrdp1;
    SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_post_ID = 10642 AND comment_parent = 0 AND comment_approved = ‘1’ AND comment_date_gmt < ‘2009-04-23 14:20:10’

    # Thu Apr 23 10:01:24 2009
    # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use thisyoun_wrdp1;
    SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN (‘link_category’) AND tt.count > 0 ORDER BY t.name ASC

    # Thu Apr 23 10:01:24 2009
    # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    SET NAMES ‘utf8’

Viewing 3 replies - 1 through 3 (of 3 total)

  • Hi mate,
    I would be really interested to hear on what you did to improve those ‘slow mysql queries’.
    The site I manage goes to about ~15K to 20K pageviews daily and it seems to go ok usually, but if we break a big story and there is a massive spike, the site goes down (even though we run WP_Super Cahce) & the sql db clogs up; the hosting admin has to restart the mysql process.

    Any tips?

    raf_siliconvalley

    (@raf_siliconvalley)

    @thisyoun – I am seeing the same thing as you, eerily almost the exact same queries. I have tuned mysql and see no reason the server would ever be loaded given current load (<100 queries per second).

    I have other WP deployments that have MORE load and have never seen this (yet).

    In my slow query log, initially there was no lock_time (i.e == 0) but after a few hours, it starts to increase, so far a high of 53s!

    Finally, the queries have no apparent pattern in terms of data size or index size.

    I noticed WP uses MyISAM — so table locking possibly explains some of the lock_time, but why all these lock_time == 0?? What is slowing these queries. Especially since many of them have stats like:
    Query_time: 17 Lock_time: 0 Rows_sent: 1 Rows_examined: 5

    I’m not really sure what is causing the problematic queries, but try reviewing the following optimization tips and see if they help you. They worked wonders for me. https://wordpress.shadowlantern.com/?p=7

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘MySQL Slow Queries – Assitance Needed & Greatly Appreciated’ is closed to new replies.