6

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)

Tal Galili
  • 24,605
  • 44
  • 129
  • 187

5 Answers5

9

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.

markratledge
  • 17,322
  • 12
  • 60
  • 106
6

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.

danlefree
  • 478
  • 2
  • 12
2

There are two options you may want to look at,

Persistent connections tries to use the same connection with the MySQL server over and over if available (the connection is not closed between PHP requests).

MySQL max_connections allows to increase the amount of possible connections accepted by the server.

Déjà vu
  • 28,223
  • 6
  • 72
  • 100
1

I'm not familiar with wordpress specifically, so here is my take from a DB perf tuning perspective:

First, I would dig in to understand why the update is taking so long. Perhaps there is a bad query plan which requires tuning your DB's indexing strategy.

If the update cannot be sped up, and you are willing for your lookups to potentially read data that isn't fully committed (which might be ok for a blog, but not an accounting application for example), then you can change the SELECTs to include NOLOCK hints to avoid blocking on the update.

See this SO question for more info

Community
  • 1
  • 1
RyanHennig
  • 1,072
  • 9
  • 12
1

Too many database connections can occur when you have too many database connections in use (obviously), that is more people running queries on your site at a time than the max allowed connections. How many connections does your mysql server allow?

Are you using mysql_pconnect() or just mysql_connect()? With the former the connection will stay open for longer and you cannot force it to close.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405