Comment Table Size Results in Slow Page Loads and 500 Errors

Question

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?

Answer 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).