Slow queries( SQL_CALC_FOUND_ROWS) bringing down site

  • jaybook

    (@jaybook)


    14 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:
    https://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.

Viewing 9 replies - 16 through 24 (of 24 total)

  • 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 https://core.trac.www.ads-software.com/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!

    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.

    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: https://jeez.eu/2011/03/14/qoolarchives-an-archives-wordpress-plugin.html

    Anyone interested to help making it better?

    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 ??

    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?

    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?

    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

    b0b_

    (@b0b_)

    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)

    janiu

    (@janiu)

    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?

Viewing 9 replies - 16 through 24 (of 24 total)
  • The topic ‘Slow queries( SQL_CALC_FOUND_ROWS) bringing down site’ is closed to new replies.