My Wordpress website got a "error creating link with database" massage.
My host informed me it had been because my "user" had a lot of database connections which were open at the same time. This triggered a mistake making additional connections, and therefore the massage.
It has been remedied by killing deadlocked database connections. There have been numerous connections copying data to temporary tables, however the deadlock was triggered with a large group of searches awaiting one update.
Can someone show me how this may have happened, and just how to prevent it?
(p.s: that Wordpress installation has over 2000 posts)
You will find two options you might want to take a look at,
Persistent connections attempts to make use of the same reference to the MySQL server again and again if available (the bond isn't closed between PHP demands).
MySQL max_connections enables to improve the quantity of possible connections recognized through the server.
Inside a hosting that is shared atmosphere this behavior will occur eventually as the blog begins seeing increased traffic - the specifics you pointed out seem like they might be associated with poorly-written WordPress plug ins (for performance's sake, make certain all of your plug ins up-to-date together with the WordPress core).
You could also be thinking about WP Super Cache should you haven't already.
A lot of database connections can happen if you have a lot of database connections being used (clearly), that's more and more people running queries in your site at any given time compared to max permitted connections. The number of connections does your mysql server allow?
Are you currently using mysql_pconnect() or simply mysql_connect()? Using the former the bond will remain open for extended and also you cannot pressure it to shut.
I am unfamiliar with wordpress particularly, here is my originate from a DB perf tuning perspective:
First, I'd search directly into realise why the update takes such a long time. Possibly there's a poor query plan which requires tuning your DB's indexing strategy.
When the update can't be increased, and you're simply willing for the searches to potentially read data that is not fully committed (which can be suitable for your blog, although not a cpa application for instance), you'll be able to alter the Chooses to incorporate NOLOCK hints to prevent obstructing around the update.
See this SO question for more information
One factor I have seen help a good deal with Wordpress and database speed would be to clean your database of publish and page revisions. Wordpress looks after a full copy of every edit revision, with 2000 posts, your database might be huge. Run this being an SQL query in phpmyadmin to obvious revisions. I have seen databases drop 75% in dimensions and run considerably faster after clearing revisions. Alter the table prefix should you transformed it whenever you installed Wordpress, and operate a backup in advance.
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 once you run that question to complete clearing the revisions, either in the dropdown menu in phpmyadmin to optimize the entire database, or by another query only for the posts table:
OPTIMIZE TABLE wp_posts;
You'll be able to prevent publish/page revisions from accumulating again with the addition of this line to wordpress-config.php to prevent revisions:
define ('WP_POST_REVISIONS', FALSE);
Or this line to choose the amount of revisions to help keep:
If you can get your MySQL config file, consider tuning MySQL for better performance.