Display Popular Posts Performance Issue

Hi @longnha,

Please have a look at the Performance wiki page, it contains general recommendations / suggestions that may be of help.

Yeah, I’ve already applied the recommendation and the performance is good, except for when the cache expired and new query is made, it took way too long.

What “recommendation” did you apply exactly? The wiki page includes a few of them.

I have object caching enabled, using InnoDB.

The query, for example:

SELECT v.view_date, SUM(v.pageviews) AS pageviews
FROM wp_popularpostssummary v
INNER JOIN wp_posts p
ON v.postid = p.ID
WHERE (v.view_datetime BETWEEN '2024-03-09 00:00:00'
AND '2024-03-15 23:59:59')
AND p.post_type IN ('post')
AND p.post_status = 'publish'
AND p.post_password = ''
GROUP BY v.view_date
ORDER BY v.view_date DESC;

It takes over 1 min to load.

Given the information you have provided so far (just a couple of queries and that’s it, not even some actual data like hardware specs, database table sizes, etc.) if I had to take a guess then:

• you probably have a lot of data in the wp_popularpostssummary table and if so it might be a good idea to limit the amount of data that WordPress Popular Posts stores on your database
• or that you may have some other resource intensive stuff running on your site that are having an impact on performance, in which case you should review your site in depth to identify potential bottlenecks / issues
• or that your current hardware setup might no longer be enough for your site and needs upgrades
• or a combination of some / all of the above

(All these suggestions / recommendations are mentioned on the Performance wiki page I shared earlier by the way, I’m just summarizing it here.)

The wp_popularpostssummary is around 2G. So, I cleared the cache, set log limit to 7 days and enabled Sample Rate = 100. The page did load faster at first but as the data size increase, the load time is increased too.

The site is on dedicated server but I’m not familiar with mySQL so not sure how to tune it for faster JOIN query!

Also, I guess a lot of views are from Bots like Google. Can I exclude it from the count?

Thank you.

And it would speed everything up a bit if there is ability to exclude the comments from tracking as some sites does not have comment enabled.

The wp_popularpostssummary is around 2G. So, I cleared the cache, set log limit to 7 days and enabled Sample Rate = 100. The page did load faster at first but as the data size increase, the load time is increased too.

That’s to be expected. The server will always require time to load stuff from the database, and the database will continue to grow as you add more data to it over time.

Limiting WPP’s views data to last 7 days only should help though. If you don’t need older views data then you should be good to go now.

If you’re still seeing slow database responses after clearing WPP’s database tables then I would suggest reviewing your entire site, something else is slowing down your site’s performance.

I’m not familiar with mySQL so not sure how to tune it for faster JOIN query!

The query itself is already optimized, WPP’s database tables even use indexes to help speed things up.

If your database server is still struggling then you have a problem somewhere else (see previous comment).

And it would speed everything up a bit if there is ability to exclude the comments from tracking as some sites does not have comment enabled.

The plugin doesn’t “track” comments though? Comments are a built-in WordPress feature, and the plugin will use the existing comments data only when sorting popular posts by comments count.

If you’re sorting posts by views count (which is the default behavior) then comments data is irrelevant and shouldn’t impact performance.

Thank you for the info @hector.

Also, does the plugin count view from Bots such as GoogleBot?

Thanks.

It should exclude most bots AFAIK since the plugin updates views count via AJAX. Most bots out there won’t run JS code since all they care about is crawling pages to extract content.

Googlebot I believe is capable of running JS though so there might be some hits coming from it. I can’t say for sure. The plugin doesn’t really know whether a page view is generated by a human or a bot, that would imply some sort of data collection which isn’t great for GDPR for example.