Hi,
we use high end server (128g ram, ssd, 10 cores etc) and
we host a single site but with very large database (900k articles – 10.2 g).
Database (mariadb) is optimized very good as we have experience from many servers and php is 7.3.
Site nowadays have small traffic but still cpu usage is quite high (>50%)
and is from mariadb.
Investigated the issue and seems to be from a td_composer query, maybe was not designed for so big sites.
Here is the query monitor report
Query is
SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
FROM wp_posts
WHERE post_type = ‘attachment’
ORDER BY post_date DESC
caller is
wp_enqueue_media()
and Plugin: td-composer
It seems that as it is a query without indexes and queries whole post table
is slow for a 900k articles site?
Also quering for a result with years, months for every attachment of site not sure what profit has.
Please do not post with the usual links for speeding up the site, everything is optimal at server and site level, i think
question is clear.
Thanks!
At first page a slow query is :
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’))
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20
It makes a query to posts table (as i wrote it has 900k articles)
in order to find last 20 (20 is the number we set to appear)
The is searching whole table for published posts and takes last 20, so i assume
will be slow for any big site, maybe had to be optimized to check last days only?
thanks again
Hi,
I’ve made some investigations for these queries and these are not from our theme, are from WordPress
-> https://i.imgur.com/VLk8Ye8.png -> https://i.imgur.com/3ppn11T.png -> https://i.imgur.com/NxlIFTa.png -> https://i.imgur.com/YOJBr2P.png
-> https://www.screencast.com/t/hYrDlUEtsho
If you have many articles you’ll need to make some special queries for what you want to extract from database. For example on the last query you can get the last 20 publish articles, if there is no index on post_date, the query will be slow if there is set an index the query will be fast.
Also our theme dose not have specialize queries, it is using the layers provide by the WordPress -> https://i.imgur.com/rU4w88X.png now if you have a big database as in this case the CMS need to be adapted, find the slow queries and optimize them.
Also you can check some similar topic from WordPress forum -> https://wordpress.stackexchange.com/questions/166448/how-to-optimize-wp-site-for-millions-of-posts maybe this will help you.
Thank you for your understanding!
Thank you for detailed and helpful reply Calin!
>>>For example on the last query you can get the last 20 publish articles, if there is no >>index on post_date, the query will be slow if there is set an index the query will be >>fast.
So all in all as we are using the template with latest articles (put 20), it calls the query from wp-core so actually in order to fix something need to make better the native wp queries for it?
Hi,
This is a general limitation of WordPress, and not the theme so any modification to the actual query has to be made on the WordPress level. It will not matter what theme you use if the WordPress query is not optimized for such large amounts of content.
You will need coding experience to be able to use the solution provided in the example from the previous reply:
The pre_get_posts action hooks into this process, allowing you to change the query before it is passed to WP_Query->get_posts().
This however is not covered by the theme support as it falls beyond the scope of the theme
Thank you for understanding.
