What can cause "too many database connections"

Question

My Wordpress website got a "error establishing connection to database" massage.

My host informed me it was because my "user" had too many database connections that were open at once. This caused an error making additional connections, and thus the massage.

This has been corrected by killing deadlocked database connections. There were a number of connections copying data to temporary tables, but the deadlock was caused by a large set of lookups waiting for one update.

Can someone explain to me how this might have happened, and how to avoid it?

(p.s: that WP installation has over 2000 posts)

Answer 1

One thing I've seen help a great deal with WP and database speed is to clean your database of post and page revisions. WP keeps a full copy of each edit revision, and with 2000 posts, your database could be huge. Run this as an SQL query in phpmyadmin to clear revisions. I've seen databases drop 75% in size and run much faster after clearing revisions. Change the table prefix if you changed it when you installed WP, and run a backup beforehand.

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'

Then optimize tables after you run that query to finish clearing the revisions, either from the dropdown menu in phpmyadmin to optimize the whole database, or by another query just for the posts table:

OPTIMIZE TABLE wp_posts;

Then you can prevent post/page revisions from accumulating again by adding this line to wp-config.php to stop revisions:

define ('WP_POST_REVISIONS', FALSE);

Or this line to select the number of revisions to keep:

define('WP_POST_REVISIONS', 3);

If you have access to your MySQL config file, look into tuning MySQL for better performance with a utility like GitHub - major/MySQLTuner-perl.

Answer 2

In a shared hosting environment this behavior will occur sooner or later as your blog starts seeing more traffic - the specifics you mentioned sound like they may be related to poorly-written WordPress plugins (for performance's sake, make sure all your plugins updated along with the WordPress core).

You might also want to consider WP Super Cache if you haven't already.