I have a number of WordPress websites and ran into a problem at Hostgator since I have a website with a large amount of posts. Here’s what they sent me, which is interesting (I had to fix a little bit of their English, of course):
As MySQL will begin to see performance problems when working with tables which contain over 100,000 rows, we suggest the optimization of the affected databases via the removal of out of date or non critical data from the affected tables or via the addition of indexes in order to help reduce the overall number of rows that must be read when querying the affected tables.
In addition, WordPress starts becoming inefficient at handling the number of posts somewhere between 1500 and 2500 posts and at over about 5000-6000 comments, give or take a little depending on plugin configuration. Once those numbers are doubled, it becomes exceedingly inefficient, performing very poorly and using a lot of CPU resources to process the results coming back from MySQL.
In looking through your WordPress site, you are exceeding those numbers, and this has resulted in increased CPU load, which has contributed to the high resource usage of your site. As such, we would also suggest pruning out of date or non critical posts and comments as well as the affected script is currently seen to contain 6,583 posts and 15,166 comments.
What they want me to do is prune database tables that are too large.
And then here’s some more useful information that I found:
High Traffic Tips For WordPress (codex.wordpress.org)
Like any other web application, WordPress is only able to handle as much traffic as the hardware which runs it can support.
There are two main barriers that can prevent your site from functioning under a high volume of traffic:
Processor Limitations
Network Limitations
[…]
MySQL connection rates can be improved by either adjusting MySQL settings, or providing more memory and processing power to the overworked server. Additionally, using query caching and proper indexing can help to improve MySQL performance. There is no single solution for every case, as all sites operate differently.
Make WordPress Run Better
With a few maintenance tasks, you can clean up WordPress a bit to make it run more efficiently.
This page has some resources that are good. One to look at here is “Remove WordPress Post Revisions from the Database” since that will bring down the size of the database. This will also help with the size of your backups, too:
Stupid WordPress Tricks (perishablepress.com)
And then this plugin seems to be rated pretty high, so I’m going to try it:
WPDBSpringClean (wordpress.org)
The WPDBSpringClean plugin identifies unused, WordPress DB tables which have been left over from old plugins you have uninstalled on your site and it then gives you the option of deleting these tables.
Due to regular insertions and deletions in the various DB tables on your system, these tables can quite often hold allocated but unused space. Consequently this can make your DB tables inefficient, fragmented and unoptimized. This plugin will identify unoptimized tables and will allow you to optimize them by deleting the allocated unused space within a particular table. The plugin also optionally allows you to specify search criteria such as the minimum amount of overhead per table and minimum unused space for a table.
If I find more things that help, I’ll post them here. If anyone has some good tips, please post them in the comments.
