0

I manage a small site that has a very active comment area. The site is on shared hosting. There is a daily post entry, then a robust comment area. It is not untypical to have more than 100 comments on a day's post.

The result is that loading or refreshing the current day's page is very slow - up to or more than 30 seconds. Sometimes a page load will time out with a 500 error. Other times a comment 'submit' will result in a 500 error, and the comment is lost.

The site gets under 5K visits a day, with less than 30 concurrent users during the day. Some of those visits are individuals re-loading the page to see the latest comments.

Lately, the search engines have been indexing the site. I see this in the GA real-time monitor as page loads from past years. Each search index page load also displays that day's comments, which put an additional load on the comment query for that page.

The daily comments area is a popular feature of the site, so do not want to disable that. The increased costs in changing from a shared server to a dedicated server are not possible (there is no revenue generated for the site). Blocking specific search engines via the robots.txt or htaccess files won't work - the search engine locations are variable and change. The theme uses the standard loop for displaying the comments.

I tried a caching plugin (WP Cache), but it didn't seem to reduce the page load time. (Not to mention that it shortened the 'remember me' in the comment entry form to 1 day, which irritated the commenters and me.)

So what is the best way to increase the 'efficiency' of the site's page load time with the active discussion environment on the site?

Added

I have used the Query Monitor plugin to look at query times. It still seems that the queries related to the comments table are the main issue in slowing down page response. (Page response takes about 8-10 seconds to show a page with comments.)

Splitting the comments into pages irritated the regular visitors on the site. And it didn't seem to improve page load time.

Would partitioning the comments table help? Only the comments from the last 5 days need optimization. I don't worry about search indexing time to get old posts. Just the response time for looking at the latest post.

Or is there an additional index that might be added to the comments table to make it's queries more efficient?

4
  • 1
    You might start with a couple of steps like enabling Query Monitor and using the Network tab to identify any bottlenecks - expensive or repeated queries, or resources that aren't loading quickly. Maybe there's a different theme you can use, or plugins you can cut back on, or custom code you can add to reduce the number or type of queries. Some more ideas: foliovision.com/2012/01/wordpress-performance-tips-big-sites
    – WebElaine
    May 12, 2022 at 18:55
  • Interesting info in the links, but those pages are over 10 years old. Looking for a solution that might be more current and more applicable to the latest WP versions. Thanks. May 13, 2022 at 2:44
  • The comments table hasn't changed much - the SQL optimizations there are still applicable today.
    – WebElaine
    May 13, 2022 at 15:10
  • See additional info added to the question. May 23, 2022 at 1:45

1 Answer 1

1

The solution to the issue of slow queries on a site that has a very active comment 'gang' (there are over 150K comments on that site, with about 50-100 added each day) (sort of a long answer, but the details might help others):

The first step was to ask the hosting company to move the shared hosting to another, more efficient server. Since this site has been around for many years, it's possible it was still on it's original server, with more powerful servers available after the sites' hosting was started. This is just speculation, though.

But a move to a newer server improved overall response times, so pages wouldn't time out, but database queries will still slow. According to the WP Query plugin, the typical page load of the current page had about 80-90 queries, with a query time of about 11-15 seconds. These numbers were consistent over several days.

So, the next step was to see if the SQL server could be faster. Note that the database had already been optimized. An caching did not help, because of the number of comments added each day. (In fact, adding caching slowed things further, as the page was never 'static' enough for caching to be effective.)

The hosting company investigated, and (after suggesting the standard 'optimize your database' answer), determined that the new server was in a data center on one side of the country (US), and the SQL server in a different data center on the opposite side of the country.

The hosting company moved the database to a newer, more powerful SQL server in the same data center as the hosting (shared) server.

This improved query response times from 11-15 seconds down to 1-3 seconds; a significant improvement.

To sum up:

  • a more power shared server was used
  • the database was moved to a SQL server in the same data center as the shared server

After one day on the new SQL server, the changes have apparently resolved the issue of slow page loads, slow queries, and timeouts (including 500 errors).

1
  • DB server on the other side of the world from the app server? sounds like a good reason to leave the hosting company just for having people that can imagine this kind of setting (at least until light speed will be proven to not be constant). But on the useful side, if you have 80 queries it means your caching sucks... don't think there is any plugin to optimize this but typically you can cache all comments already displayed once (as they do not change) and fetch only newer comments. As comment table has the post ID as an index I find it hard to believe the issue is specific to comment but... Mar 23 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.