2

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.

  • 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 Mar 4 at 23:14
  • Please post the data from your MySQL hosting server for suggestions to improve efficiency. – Wilson Hauck Mar 17 at 20:44
  • 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 Mar 18 at 11:58
  • 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 Mar 18 at 11:59
  • Looking forward to your posts to allow specific custom suggestions and questions once the data is available from the MySQL hosting server. After comment to @wilsonhauck letting me know data is available, please allow 48 hours for analysis to prepare suggestions/questions. – Wilson Hauck Mar 24 at 0:42
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.

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.

  • Thanks for the reply, we sure will look on it. – edtamil Jun 13 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.