High DB load after 0.1.11 & 0.1.12 updates
-
Hi – I’m seeing a high DB load on all 3 sites on which I have decent-comments installed. This seems to have appeared after the 0.1.11 update and carried on into 0.1.12. I’ve rolled back to 0.1.10 to mitigate against the problem temporarily.
All sites have a large number of historical comments, close to a million on the busiest one, around 600,000 on the one with the least.The query which is slow is this one:
SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = ‘1’ ) AND wp_posts.post_status IN (‘publish’) AND comment_type != ‘pingback’ AND comment_type != ‘trackback’ ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ;
The problem possibly looks to be with the “order by” clause. Removing one or other of the “order by” elements speeds things up so there may be an issue compounded with the 2 fields specified together?
I’ve created various single-field indexes in the past to workaround some problems but, before I look at creating another index to speed this query up, I just want to ask whether whether it may be better to modify the query in the plugin? I can imagine a number of sites with a lot of comments may suddenly start to see issues.
I’m using Mysql 5.7.40, so the latest version of 5.7 by the looks of it (yes I should upgrade one day…!).-
This topic was modified 2 years, 3 months ago by
Rufus McDufus.
-
This topic was modified 2 years, 3 months ago by
-
It’s worth pointing out that I suspect I may not be seeing these issues if I were using MariaDB (or maybe MySQL 8.x?) as it seems better at forming an optimal query execution plan.
-
This reply was modified 2 years, 3 months ago by
Rufus McDufus.
Hi there!
In the latest version, the plugin, if needed, just adds to the query that WordPress builds itself (instead of building its own). But for the query you outline, it doesn’t add anything at all. So this is not something we could approach from within the plugin, but would require optimizations around the structures used by default.
One thing that could help is to create an index on the post_status column of the posts table:
-- Create a text index on the post_status column -- First, to avoid this: ERROR 1067 (42000): Invalid default value for 'post_date' SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; -- ... and now create the test index: CREATE INDEX test_post_status ON wp_posts (post_status(10)); -- Later, to remove the test index ... -- DROP INDEX test_post_status ON wp_posts;
On an example database with 22787 posts and 47457 comments I tried this and get an improvement on the query – without the added index:
mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ; +----+-------------+-------------+------------+-------+----------------------------------------------------------------+------------------+---------+----------------------+-------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+-------+----------------------------------------------------------------+------------------+---------+----------------------+-------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | wp_posts | NULL | index | PRIMARY | type_status_date | 177 | NULL | 22787 | 10.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | wp_comments | NULL | ref | comment_post_ID,comment_approved_date_gmt,woo_idx_comment_type | comment_post_ID | 8 | wordpress.wp_posts.ID | 4 | 83.45 | Using where | +----+-------------+-------------+------------+-------+----------------------------------------------------------------+------------------+---------+----------------------+-------+----------+-----------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
Compare to after the extra index has been added:
mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ; +----+-------------+-------------+------------+------+----------------------------------------------------------------+------------------+---------+----------------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+----------------------------------------------------------------+------------------+---------+----------------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | wp_posts | NULL | ref | PRIMARY,test_post_status | test_post_status | 42 | const | 3440 | 100.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | wp_comments | NULL | ref | comment_post_ID,comment_approved_date_gmt,woo_idx_comment_type | comment_post_ID | 8 | wordpress.wp_posts.ID | 4 | 83.45 | Using where | +----+-------------+-------------+------------+------+----------------------------------------------------------------+------------------+---------+----------------------+------+----------+----------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
Which means that for the wp_posts table, without the extra index, it expects to visit all 22787 rows and return around 10% (filtered) of them. Not good.
With the extra index added, it expects to visit 100% (filtered) of 3440 rows instead of the whole set. Ok, that’s an improvement.
But then it still does a temporary table and filesort to sort those results obtained, and from what I can tell, that’s the actual bottleneck. So even though such an index would help, the whole thing drags in thick mud with the sorting.
Anyone with a good idea on what to do to improve that part?
Many thanks for you research on this. I can see in hindsight it looks like a core WP query anyway. I’ll give this index a go and see what happens…
Given this a go. I’m still getting a full table scan with the new index, but it’s taking 6 seconds as opposed to 30+ before so looks like it’s using it (yes I can see from the “explain” that it probably is).
These were the indexes on wp_posts before creating the new one. This site has been around a long time so there’s a lot of historical fluff that has built up. including indexes by the looks of it!
mysql> show indexes from wp_posts; +----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | wp_posts | 0 | PRIMARY | 1 | ID | A | 17065 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_date | 1 | post_type | A | 14 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_date | 2 | post_status | A | 24 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_date | 3 | post_date | A | 17065 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_date | 4 | ID | A | 17065 | NULL | NULL | | BTREE | | | | wp_posts | 1 | post_parent | 1 | post_parent | A | 948 | NULL | NULL | | BTREE | | | | wp_posts | 1 | post_author | 1 | post_author | A | 36 | NULL | NULL | | BTREE | | | | wp_posts | 1 | post_name | 1 | post_name | A | 17065 | 191 | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified | 1 | post_type | A | 14 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified | 2 | post_status | A | 24 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified | 3 | post_modified_gmt | A | 17065 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified_no_id | 1 | post_type | A | 14 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified_no_id | 2 | post_status | A | 24 | NULL | NULL | | BTREE | | | | wp_posts | 1 | type_status_modified_no_id | 3 | post_date_gmt | A | 17065 | NULL | NULL | | BTREE | | | | wp_posts | 1 | post_status | 1 | post_status | A | 6 | NULL | NULL | | BTREE | | | +----------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 15 rows in set (0.00 sec)
This is the query before the index was created:
mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ; +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | wp_comments | NULL | ref | comment_post_ID,comment_approved_date_gmt,comment_type | comment_approved_date_gmt | 82 | const | 969865 | 100.00 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | wp_posts | NULL | eq_ref | PRIMARY,post_status | PRIMARY | 8 | biasedbb_live.wp_comments.comment_post_ID | 1 | 80.12 | Using where | +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
And after:
mysql> EXPLAIN SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE ( comment_approved = "1" ) AND wp_posts.post_status IN ("publish") AND comment_type != "pingback" AND comment_type != "trackback" ORDER BY wp_comments.comment_date_gmt DESC, wp_comments.comment_ID DESC LIMIT 0,10 ; +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | wp_comments | NULL | ref | comment_post_ID,comment_approved_date_gmt,comment_type | comment_approved_date_gmt | 82 | const | 969867 | 100.00 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | wp_posts | NULL | eq_ref | PRIMARY,post_status,test_post_status | PRIMARY | 8 | biasedbb_live.wp_comments.comment_post_ID | 1 | 83.64 | Using where | +----+-------------+-------------+------------+--------+--------------------------------------------------------+---------------------------+---------+-------------------------------------------+--------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
6 seconds is still too long though. I’m not sure why it’s fine with Decent Comments 0.1.10 and not 0.1.11 & 0.1.12 though. Could it be something else?
Thanks again for your help.-
This reply was modified 2 years, 3 months ago by
Rufus McDufus.
-
This reply was modified 2 years, 3 months ago by
Rufus McDufus.
Thanks for the follow-up! I finally got a chance to look and think about this a bit more today … I can think of a way to make the query faster, if we would reduce the set it runs on.
First, when we want to get the most recent comments, we don’t really need to ORDER BY comment_date_gmt DESC and comment_ID DESC, we only need to use the latter:
SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE (comment_approved = '1') AND wp_posts.post_status IN ('publish') AND comment_type != 'pingback' AND comment_type != 'trackback' ORDER BY wp_comments.comment_ID DESC LIMIT 0 , 10;
… this yields a slightly faster query.
But the main issue of the query is the set of comments it looks at. So why don’t we reduce it?
For example, we could obtain the ID of the 100th most recent approved comment and use that as a threshold for the comments to consider:
SELECT comment_ID FROM wp_comments WHERE comment_approved = '1' ORDER BY wp_comments.comment_ID DESC LIMIT 1 OFFSET 100;
Now if we add this into our query:
SELECT wp_comments.comment_ID FROM wp_comments JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID WHERE comment_ID > (SELECT comment_ID FROM wp_comments WHERE comment_approved = '1' ORDER BY wp_comments.comment_ID DESC LIMIT 1 OFFSET 100) AND (comment_approved = '1') AND wp_posts.post_status IN ('publish') AND comment_type != 'pingback' AND comment_type != 'trackback' ORDER BY wp_comments.comment_ID DESC LIMIT 0 , 10;
… the time it takes to retrieve the comments with this is below 10% of the time it takes to process the previous query without a cutoff.
Something to keep in mind, the queries are not exactly equivalent, because the threshold query doesn’t care about whether the threshold ID obtained is from a comment that relates to a post that is published/publicly visible or not. One could make it more resilient by using a higher value instead of 100 if needed.
I’m not sure if this should go into the plugin itself or just filter the comment query as a little add-on plugin crafted based on the above.
The trade-off with the threshold bit is between being sound and fast versus complete and slow … what I’m sure about is that those queries shouldn’t be allowed to take seconds, whether you have 10 or a million comments on your site. So I’d go with sound and fast π
Got a chance to test out the queries (on 970k comments). Results were the same for all 3 queries. I’m just using the Ubuntu/Linux “time” utility here in front of the query from the command line.
Time to execute the troublesome original query:
real 0m36.527s
user 0m0.005s
sys 0m0.007sTime to execute first proposed query solution in your post above:
real 0m0.014s
user 0m0.002s
sys 0m0.010sTime to execute second proposed query:
real 0m0.011s
user 0m0.003s
sys 0m0.005sThat’s a big difference!
-
This reply was modified 2 years, 3 months ago by
Rufus McDufus.
Wow yes, that’s a huge difference!
Now I really wonder where this should go, because this case considers a particular order … if the queries should be optimized in general, I think these optimizations should go into WordPress core. In lack thereof, going the plugin way with filters hooked on comments queries could be a solution. And the third option would be to try to optimize those queries via the plugin itself.
-
This reply was modified 2 years, 3 months ago by
- The topic ‘High DB load after 0.1.11 & 0.1.12 updates’ is closed to new replies.