wp admin - Too many datadabse connections - upper ceiling mysql

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.

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.

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.

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.

• wp-admin
• mysql
• errors
• production
• vps

Share Improve this question edited Feb 14, 2019 at 8:30 edtamil asked Feb 14, 2019 at 6:52 edtamiledtamil 3144 bronze badges 12

• Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, df -h for a linux/unix free space list by device, for server connection limit tuning analysis. – Wilson Hauck Commented Mar 4, 2019 at 23:14
• 1 Thanks for your concern, we now upgraded mysql to 4 core 16GB; the problem seems to be persistant, from what we could diagnose is that the hosting apache server first hits an abnormal ram consumption (possibly due to memory leak), or can the problem be attributed to bad bots. – edtamil Commented Mar 18, 2019 at 11:58
• 1 We are trying to get clearance from the company end to retrive and post the informations you've requested. Thanks for your interest in this – edtamil Commented Mar 18, 2019 at 11:59
• 1 Thanks very much for your interest in this William, we are just seeking permission from the department; and moreover we will have to modify the rds - aws for getting a note on queries run.. When we do the same , we'll update this thread Again thanks for your interest in this – edtamil Commented Jun 5, 2019 at 15:14
• 1 We have enabled the rds enhanced monitoring and we do often find that the tables are locked for long periods of time; so that reasonably other queries are queued. We run all tables as myisam so perhaps write function is prone to lock the entire table, or perhaps our wp-option table needs and index. In the coming weeks, we will work upon the same.. Many thanks for contributing to this thread – edtamil Commented Jul 25, 2019 at 15:35

 |  Show 7 more comments

1 Answer 1

Sorted by: Reset to default 0

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.

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.