Too many datadabse connections – upper ceiling mysql

by

We run a high traffic blog with around 500 real-time users and 10000 posts.
We are hosted by self on VPS – 16gb, 4 core with separate mysql DB server – 4gb, 2 core; both SSD.
No problem with hosting server
But the database server at times running out of connections, though we have more than sufficient 312 max cconnections ceiling. We don’t have any stray plugins and our maximum execution time is around 5 minutes.
Anyone have any idea regarding what might be causing the issue.
peaks the maximum 312 connection-db
Database connections - peaks at working hours

More insights:
What we could correlate is that the days when our bloggers (max 10) work from our office under a single IP and slow -interrupted network connection this issue happens to be more frequent. The issue is more frequent at peak publishing hours than during idle hours.

Or is the database server running out of ram which is eventually causing the connections blackout. Search engine crawls usually drink more Cpu but the bots don’t establish more than 30 db connections and our servers are totally fine handling the bots.

Also prevelant when a single admin keep the posts in edit mode for long periods of time

wp config details

‘WP_MEMORY_LIMIT’, ‘256M;
‘AUTOSAVE_INTERVAL’, 160;
max_execution time 500

Our query monitor has some PHP 7.2 depreciation warnings though the theme is stable with some slow queries, also posts revisions are vital for us.

1 Answer
1

This by no means will be a definitive answer, but I had some tips that were longer than the comments would allow.

That high max execution time concerns me. The root of your problem probably has nothing to do with the number of connections to the DB and more to do with the amount of time these connections are alive.

Here’s a few things to look into:

Admin Heartbeat

You mentioned a large number of editors online at the same time. The Heartbeat API fires every 15 seconds and could write to the DB every time for every user, depending on your settings and the user’s activity.

Check your heartbeat interval and number of revisions stored. I see you already have the autosave interval tuned.

DB Updates triggered

Database writes are some of the most intensive for mysql. The more requests for writes, especially at the same time, the higher the load. If your write queue gets too long it can lead to lingering open connections and eventually it will max out your available connections.

  • When you update or publish posts what else happens beyond updating the post data?
  • Are you using a caching plugin that purges caches, possibly even transients in the DB?
  • Are you using an XML sitemap plugin that regenerates after publishing?
  • Are you using an SEO plugin that analyzes content?
  • Are you using a link tracker or analyzer?
  • All of these things (or many of them) could contribute to a flood of DB writes.

Reduce database load

Get an object cache storage solution in place if you don’t have one already. Redis or Memcache do wonders to reduce load on your database.

chart of performance metrics where Redis cache is introduced and reduces overall load

Check your options

The autoload column in the options table indicates whether the options will be read from the database on all requests. If this is large, it can lead to longer requests to the database. This is another place object caching can help. This may not contribute to your situation, but it’s worth looking into for performance concerns.

Leave a Comment