WordPress.org

Ready to get started?Download WordPress

Forums

Slow queries( SQL_CALC_FOUND_ROWS) bringing down site (25 posts)

  1. jaybook
    Member
    Posted 3 years ago #

    Hello,

    we are running a very large WordPress site on multiple Amazon EC2 instances.
    We have a very powerful database server (extra large, high-cpu), and six load balanced web servers.

    We have a problem where the database server normally runs at between 1% to 15% CPU, but then jumps to 100% CPU. If left unchecked, the database server comes to halt for a period of time, bringing down the site. We are using WP-Supercache to reduce the load on the database server, and the normal server response time is between 50 and 500 milliseconds).

    Using the monYOG tool (which analyzes MySQL's running processes and slow query log), I have found the following three queries which seem to be taking far too long. The problem is that nothing about them seems wrong, except that two of them are using SQL_CALC_FOUND_ROWS.

    If I google "wordpress SQL_CALC_FOUND_ROWS", I find plenty of articles indicating that a bug either in MySQL, or WordPress (or both) is responsible for site meltdowns because of queries using SQL_CALC_FOUND_ROWS.

    Here is one such article:
    http://ckon.wordpress.com/2009/07/22/wordpress-still-uses-the-nasty-sql_calc_found_rows/

    Here are the problematic queries:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE ?=? AND wp_posts.post_type = ? AND (wp_posts.post_status = ?) ORDER BY wp_posts.post_date DESC LIMIT ?, ?
    
    Avg. time: 41 seconds
    Max. time: 2:34 seconds
    Query Occurrence: 36%
    
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE ?=? AND wp_posts.post_type = ? AND (wp_posts.post_status = ?) ORDER BY wp_posts.ID ASC LIMIT ?, ?
    
    Avg. time: 47 seconds
    Max. time: 2:35 seconds
    Query Occurrence: 28%
    
    SELECT wp_posts.*
    FROM wp_posts
    WHERE ?=? AND YEAR(wp_posts.post_date)=?
    AND
    MONTH(wp_posts.post_date)=?
    AND
    DAYOFMONTH(wp_posts.post_date)=?
    AND
    wp_posts.post_name = ?
    AND wp_posts.post_type = ?
    ORDER BY wp_posts.post_date
    DESC
    
    Avg. time: 25 seconds
    Max. time: 1:34 seconds
    Query Occurrence: 14%

    Has anyone else had such problems? What are my options other than hacking the WordPress core to not use SQL_CALC_FOUND_ROWS, increasing the cache time to a much higher level, or switching the site into WP-Supercache's "lock down" mode?

    Any help at all would be greatly appreciated! Thank you.

  2. Melbourne Cup Sweep
    Member
    Posted 3 years ago #

    Hi Jaybook, I thought id have a go at helping you since I thought SQL_CALC was going to be the downfall of me for 6 months, hehe.

    I'm guessing you have a large database, have you? I think SQL_CALC hasn't been addressed in wordpress because the average size wordpress blog of maybe a few hundred posts and visitors a day would have no performance issues that would need addressing and unfortunately us few that do are are a small minority.

    I am no mysql and php guru but iv learnt that the SQL_CALC_FOUND_ROWS query does a full count scan in your database of your wp_posts to determine the number of rows that are in that part of the database before other queries are made.

    I have an article directory with 240,000 articles so obviously every time I had a page view this query would scan the whole 240,000 rows and a page loading on average took about 5.3 seconds. If I was lucky enough to have spikes in traffic id have the same issue as you, my server would overload and crash because the mysql queries where getting backed up in the database waiting for SQL_CALC queries to finish.

    The main offending SQL_CALC query is found in the /mp-includes/query.php file. I was excited to find a magical solution for the issue a few weeks back with a wordpress patch ticket found at http://core.trac.wordpress.org/ticket/10964.

    My results have been amazing, page load time went from average 5.7 seconds to 1.3 seconds. And slow query log file size dropped from 50Mb to 2Mb in growth a day. Obviously this is a vast improvement and since the wordpress platform is being used more and more as a general CMS, this fix would be gold to any webmaster that has a wordpress DB on the large side.

    I read that SQL_CALC is also found in the the admin files somewhere also, that explains why that is still loading slow.

    Now also when it comes to caching plugins, nothing comes close to W3 Total Cache found at http://wordpress.org/extend/plugins/w3-total-cache/, WP SuperCache only caches pages while W3 Total Cache caches everything including browser, pages, database queries, minifies & gzip html, objects and also CDN's like Amazon's service. Its used by the big wordpress sites like Mashable.com and it also has an awesome feature that allows you debug to see what queries each of your individual pages are making when you view the source code.

    Let me know how ya go with those suggestions and just let me know if you have any questions.

  3. jaybook
    Member
    Posted 3 years ago #

    Hello hayzie,

    thank you very much for your detailed reply and explanation. Yes, we have tens of thousands of posts on this site.

    I applied the patch to 2.9.1 to our 2.9.2 installation, and within a minute, the load average on the database server dropped from around 200 to 0.62. So obviously, there is a serious flaw in WordPress, or MySQL (or both), when it comes to sites with more than a thousand posts.

    I also deleted all revisions older than the current month from the database, which reduced the size of the wp_posts table by more than half. Storing revision data takes up more space than necessary. More information about that can be found here:
    http://lesterchan.net/wordpress/2008/07/17/how-to-turn-off-post-revision-in-wordpress-26/

    I still notice periodic spikes in traffic due to some SQL_CALC_FOUND_ROWS queries which have not been addressed by the patch. These are:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
    FROM wp_posts WHERE ?=? AND wp_posts.post_type = ? 
    AND (wp_posts.post_status = ?) 
    ORDER BY wp_posts.post_date 
    DESC LIMIT ?, ?

    Average time: 52 seconds

    SELECT SQL_CALC_FOUND_ROWS
    wp_posts.ID
    FROM wp_posts
    WHERE ?=?
    AND wp_posts.post_type = ? AND
    (wp_posts.post_status = ?)
    ORDER BY wp_posts.ID ASC LIMIT ?, ?

    Average time: 58 seconds

    SELECT YEAR(post_date) ASyear, MONTH(post_date) ASmonth`, count(ID) as posts
    FROM wp_posts
    WHERE post_type = ? AND post_status = ?
    GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC`

    Average time: 48 seconds

    Re: W3 Total Cache
    We tried W3 Total Cache with memcached, but it proved to be buggy / ineffective. For whatever reason, it was simply not caching the vast majority of queries. We replaced it with WP Super Cache (which duplicates cache files on each web server, instead of using a single memcached server), and the response time for pages improved to about 1/3rd or 1/4 of the original. This is totally the opposite of what I thought would be the case, and I'm not sure why it made such a difference.

    We are also using MySQL on EC2, with its data stored on an EBS volume ( as described here: http://aws.amazon.com/articles/1663?_encoding=UTF8&jiveRedirect=1 )
    Since EBS is essentially a networked file system, I'm not sure if that adds to the response time / locks associated with the use of SQL_CALC_FOUND_ROWS. We could use EC2 instance storage, which is much faster, but also volatile. All instance storage is permanently lost if the instance fails, or is terminated.

    The takeaway from this for me is that WordPress has some serious flaws in its core code that should be addressed. The code in query.php is extremely dense, and more complicated than it should be.

    Best Regards,
    - Jay

  4. Melbourne Cup Sweep
    Member
    Posted 3 years ago #

    Jay,

    Glad to hear the patch gave you a pretty dramatic drop in server load average. I to still get the SQL_CALC_FOUND_ROWS appearing in my slow log but only a fraction to what it is.

    I have determined that it only performs the query,

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')  ORDER BY wp_posts.post_date DESC LIMIT 0, 100
    
    SELECT FOUND_ROWS()

    when the homepage is loaded but not in post pages. I cant seem to track down why the query is run because it doesn't appear to be used since the results to that query are only available to the first SELECT query after the SELECT FOUND_ROWS().

    My first select query after SELECT FOUND_ROWS() is

    SELECT  wp_posts.* FROM wp_posts  WHERE 1 = 1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_posts.ID IN ( ID1,ID2,ID3... )  ORDER BY FIELD( wp_posts.ID,ID1,ID2,ID3... )

    Does that query use the SQL_CALC_FOUND_ROWS results? I'm not sure.

    The revision surely does waste a lot of space as I realized when I first started investigating my server load issues. Just wondering what query you used to delete your revisions. There are a lot of posts on the web that quote the wrong query as DELETE FROM wp_posts WHERE post_type = “revision”;. This query only deletes the revisions from wp_posts and not the meta data associated with those revisions.

    This should be used.

    DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);'

    If you used the first one, you can delete the revision postmeta with

    DELETE pm
    FROM wp_postmeta pm
    LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
    WHERE wp.ID IS NULL

    and can clean up relationships with

    DELETE FROM wp_term_relationships
    WHERE NOT EXISTS (
            SELECT * FROM wp_posts
            WHERE wp_term_relationships.object_id = wp_posts.ID
    );

    #WARNING for anyone reading this, backup your database before performing any DELETE query in phpmyadmin, believe me, I made the mistake of not doing it and it sucks when things go wrong.

  5. rainer23
    Member
    Posted 3 years ago #

    After all those years isnt there meanwhile a plugin doing this job of clearing the database cache of unwanted entries?

  6. B03S
    Member
    Posted 3 years ago #

    Can someone post a fully patched query.php (i'm running WP 3.0.1) or explain how to patch it myself.
    On http://core.trac.wordpress.org/ticket/10964 there are a few diffs and patches but i've no clue on which and how to apply them.

    Thx

  7. B03S
    Member
    Posted 3 years ago #

    Just noticed i'm running 3.0.4 instead of 3.0.1 :)
    And also found out how to patch things using the patch command. Just need to know which patch I can apply to the 3.0.4 installation.

  8. cryan360
    Member
    Posted 3 years ago #

    I also need a patch for 3.0.3 or 3.0.4. I replaced the query.php with the file list but it just gave me an error. Can someone update the patch and/or give instructions on how to implement it correct?

    This is a major issue and I need help getting this fixed as soon as possible. Thank you.

  9. kndy71
    Member
    Posted 3 years ago #

    I was also using W3 Total Cache (I was using it for nearly 8 months) but I started to notice significant slowdown this week that I originally thought it was our host. After two days of not knowing the problem, I turned of W3 and notice speed came back up. I have since reverted back to WPSuperCache. As Jay mentioned, it was becoming buggy and ineffective.

  10. k-factor
    Member
    Posted 3 years ago #

    I think I'm having this same issue on a less robust site. About 13,000 posts total, and a hundred or so categories. After the recent 3.1 upgrade, I instantly noticed a performance hit on two of our more query-intensive pages - pages that were quick-loading before, even without caching. Analyzing with query tools, it's pretty clear that SQL_CALC_FOUND_ROWS is a culprit.

    One query that reads like this:

    $my_query1 = new WP_Query(array('category__and' => $myarray,'orderby' => title,'order' => ASC, 'post_status'=>publish, 'showposts' => 250,'ignore_sticky_posts' => 1));

    results in:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.*
    FROM wp_posts
    WHERE 1=1
    AND ( wp_posts.ID IN
    (
    SELECT object_id
    FROM wp_term_relationships
    WHERE term_taxonomy_id IN (3,6)
    GROUP BY object_id
    HAVING COUNT(object_id) = 2
    ))
    AND wp_posts.post_type = 'post'
    AND (wp_posts.post_status = 'publish')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_title ASC LIMIT 0, 250
    
    and:
    
    SELECT term_taxonomy_id
    FROM wp_term_taxonomy
    WHERE taxonomy = 'category'
    AND term_id IN (6,3)

    with a combined execute time of over 10 seconds.

    Once cached, of course the pages load quickly. But the CPU maxing out for one measley SQL query and one page-load is pretty ridiculous. Executed on their own in a query browser, the same queries are instantaneous, but obviously PHP and WordPress itself are involved in the mix here too.

    I'm at a loss for now. I'm by no means an expert PHP/MySQL/Wordpress coder, but I've lived and learned a bit. I would welcome any suggestions from anyone having better luck with this.

  11. Robert Sundelin
    Member
    Posted 3 years ago #

    I have the exact same problem. Large site, ~200k posts.

    Patching query.php seemed to help a lot up until 3.0.4, but since upgrading to 3.1 we're having big problems with mysql performance - even with a patched query.php (from http://core.trac.wordpress.org/ticket/10964).

    Like for k-factor above, cached pages are delivered fine, but the CPU often is maxing out for a single query.

    I'm at a loss.

  12. rainer23
    Member
    Posted 3 years ago #

    This doesn't make sense since all big newspapers and magazines run with the same phpmyadmin database system. If thousands of newspapers can do it this must be common knowledge how to manage large databases. Might be rather something like, yes we can, but not for free?

  13. rainer23
    Member
    Posted 3 years ago #

    This doesn't make sense since all big newspapers and magazines run with the same phpmyadmin database system. If thousands of newspapers can do it this must be common knowledge how to manage large databases. Might be rather something like, yes we can, but not for free?

  14. k-factor
    Member
    Posted 3 years ago #

    I've rolled back to 3.02. After some more analysis I'm beginning to think that another problem may reside in category__and. And potentially aggravated by the fact that our site has so many categories. Running a sampling of our sql queries as generated by WP using category__and (feeding it two category IDs only), each query takes around 5 seconds to execute in the mysql workbench. Running these same queries against the pre-3.1 DB results in instant execution. Not sure what changed, but this has got to be a bug.

  15. Robert Sundelin
    Member
    Posted 3 years ago #

    Yeah, it has got to be a bug. The database server has gone absolutely haywire since upgrading to 3.1, patch or no patch.

  16. sonnycool
    Member
    Posted 3 years ago #

    same boat like you guy, but i run 2.9.2 with 400k post and 1.4gb db ....

    site running is ok, only problem i facing now is i can make new post, whenever i try to login wp-admin -> post/edit, then my browser is freeze, you can see from my task manager ram usage, jump from 200 - 900 and till moving until it crash ...
    any solution ?

  17. kraciboy
    Member
    Posted 3 years ago #

    Same problem here, we're working with the latest version of WordPress (3.1) and our database got almost 23k of post. When we make a new post on my WordPress site still can't save anything because the server is overloaded, i deceide to use mysql-slow.log to see what happen and know what queries is overloading my site. Here you can see one of the slow-querie in my site (this is one line, but all lines are equal except the ID's)

    # Time: 110314 22:26:58
    # User@Host: user[user] @ localhost []
    # Query_time: 7  Lock_time: 0  Rows_sent: 5  Rows_examined: 154948
    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND wp_posts.ID NOT IN (308327) AND ( wp_term_relationships.term_taxonomy_id IN (1,8,22,59) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5;

    Anyone know how to fix this problem on 3.1? i followed this modification in query.php http://core.trac.wordpress.org/ticket/10964 but we lost pagination and the problem persist, nothing happen, i think maybe does not work correctly on WordPress 3.1

    Thank you
    Greetings!

  18. rainer23
    Member
    Posted 3 years ago #

    Seems like older posts have to be transfered into a separate archive.
    Simulate the scenario of a wordpress blog having 1 mio posts, all stored within the same database. Not sure if an opensource database is able to handle such load? Limited memory cache of your webspace might be another traffic bottleneck. Thats why big portals set up their own server.

  19. basdog22
    Member
    Posted 3 years ago #

    Hello all!
    I have created a plugin that creates archives from posts in a separate table making the posts table a lot more smaller. It has some side-effects no auto paging (but this can be resolved with some coding) and the calendar and monthly archives widgets don't work as supposed to.
    You can read more about it here: http://jeez.eu/2011/03/14/qoolarchives-an-archives-wordpress-plugin.html

    Anyone interested to help making it better?

  20. jokerempire
    Member
    Posted 3 years ago #

    Hey basdog22 - I'm using your plugin on a site with ~ 340k posts and it changed my load-time from ~ 15 sec for the homepage to 1.5sec - I have to say I've made some small changes to the plugin and I've imported my posts manually to the archive table. See your paypal donations account for my skype ID and get in touch so we can talk about improving the plugin, I see great potential :-)

  21. Jens Wedin
    Member
    Posted 2 years ago #

    I also got this problem when going from 2.9.2 to 3.2.1. I wonder if there is a patched query.php that I could use as I don't get have to patch the file?

  22. graffics
    Member
    Posted 2 years ago #

    I am currently using 3.1 with 50k posts and running into the same problem, this process is staying open for way to long! Has anyone found a fix to this?

  23. b0b_
    Member
    Posted 2 years ago #

    Ok. After a few days of googling and trials, i finally figured how to fix SQL_CALC_FOUND_ROWS without changing wp core functions.

    The only part of wp core in which occurs SQL_CALC_FOUND_ROWS, is line 2603 of the module query.php. Using a filter on wp_query (inserted in a function module or in a plugin), called on pre_get_posts hook, you can avoid query function to access the part of code in which $found_rows is set.

    That'all. Clean, effective and simple.

    add_filter('pre_get_posts', 'optimized_get_posts', 100);
    function optimized_get_posts() {
    	global $wp_query;
    	$wp_query->query_vars['no_found_rows'] = 1;
     	return $wp_query;
    }

    The query now takes about 0.015 secs (instead of 0.56 secs before).

    Let me know if this works for you too.
    Cheers

  24. b0b_
    Member
    Posted 2 years ago #

    EDIT: after this workaround, pagination doesn't seem to work.
    To fix the issue simply add a count query like this:

    add_filter('pre_get_posts', 'optimized_get_posts', 100);
    function optimized_get_posts() {
    	global $wp_query, $wpdb;
    
    	$wp_query->query_vars['no_found_rows'] = 1;
    	$wp_query->found_posts = $wpdb->get_var( "SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')" );
    	$wp_query->found_posts = apply_filters_ref_array( 'found_posts', array( $wp_query->found_posts, &$wp_query ) );
    	$wp_query->max_num_pages = ceil($wp_query->found_posts / $wp_query->query_vars['posts_per_page']);
    
     	return $wp_query;
    }

    In fact, when you set the no_found_rows to 1, wp core doesn't calculate the total posts number, and for that, the pagination results broken.

    Setting proper query_vars to the correct count(*) value (that is more effective and "speedy" than SQL_CALC_FOUND_ROWS), fixes the issue.

    Cheers (again)

  25. janiu
    Member
    Posted 2 years ago #

    Thanks b0b_! Your solution seems to be working except for one thing. When I look in New Relic for PHP errors, I can see that this function is creating lots of "Division by zero" errors. Any ideas on that?

Topic Closed

This topic has been closed to new replies.

About this Topic