Too many datadabse connections – upper ceiling mysql

maybe this will help

https://tableplus.io/blog/2018/11/error-1040-too-many-connection-increase-max-connections-mysql.html

Thanks for your suggestion.
But our problem is that 95% of the times the no of connections to the database is below 30. And our server is all powerful to handle the same.
But the remaining periods when our bloggers work at admin side, the max connections shoot to 312 aka our upper ceiling.

Updating to a bigger mysql server would costs us around three times more,
moreover the current server configurations were sufficient enough when encountered around 5000 people realtime previously.

But at times when we begin to edit and publish posts, things go wrong.

Are we running out of 4gb ram in our db server ?
PS: Our site has been seeing tremendous growth the past few months and crawl rates are getting higher.

This might be a little involved but…

My first suggestion is to run HyperDB if it will work for you. HyperDB is a little more robust to begin with and opens up the door to running multiple replicated databases with sharding if/as needed.

You can have a Master Read/Write database, a pair or more read only databases, and a failback database.

Here’s just a little more about this https://codex.www.ads-software.com/HyperDB and you might need to hire a developer/consultant to help deploy this as it get’s pretty involved.

And here’s the article that first sparked my interest in this https://pantheon.io/docs/hyperdb/.

—

I run a few sites that pull in content from other websites and overtime that load became too much for the cheap hosts that fit my budgets. I developed what I call a ‘Binary Star’ system. I run several subsites on whatever server pleases me and offload everything I can to those boxes including new blog post creation.

In your case, I’d recommend running one server at your office (or a server/database server pair if you wish… hardware is cheap). The new server does need to be internet reachable. Your staff will do their content creation work there and not load the public facing production site.

When the bloggers are done, they will publish their work on that box and the production server will then pull a RSS feed and pull the new posts in. A Crontask will trigger the RSS feed. And certain settings on your local machine will ensure those are posted properly and in full.

This takes all that extra load off the production server and I’ll give you further details on that setup as needed. There’s a certain plugin called Feed WordPress that I use and it is excellent for this type of service.

—

This last little bit is where I’m heading and might really free things up for you overtime while speeding up your website but I’m just barely breaking the surface of this thing right now. I can’t help but feel this might be the future of my WordPress based endeavors.

https://www.ads-software.com/plugins/static-html-output-plugin/

Caveats: This WP2Static plugin is a work in progress and the author’s documentation is scanty still but I think he’s running a one-man shop and I sense he’s committed to this project. It’s at a point where I think it useable.

My only concern is it seems to need a manual intervention (called a ‘deploy’) but I’m looking into some way of causing that to happen via cron as soon as I can possibly get to it or else I’ll discuss this with the author.

So there you have it… Do as you wish and I’ll be more than happy to provide more info if you run into questions via this support forum.

Thanks for the suggestion @jnashhawkins ..
Hyper db is a valid option to consider.
But provided the plugin-version is outdated by a year and
with less than 10 active installation, is that worthy of risking a production
server. Or if there exists any descriptive cases of HyperDB installation and tutorials please do refer to us.

The concept of HyperDB seem to be exactly the option we might have been looking,
unluckily though it is never detailed by codex-wordpress. Our problem is more prevalent and exactly occurs during post editing and publishing.
One more important thing is that we have been into wordpress and over the year
we scaled massively, though we do have a software team, they are’nt much experienced in this PHP stuff either.
TL;DR
small team, simple solution, self manageable option
from where we come from, this case is either highly unlikely or excessively pricey remedy.

Though we are trying to be stable as long, also that now we got almost 1000 active connections and about 10000 posts, half a million attachments.
And the problems are increasingly unmanageable. But we look forward to have any sort of web management stubbornly within our company premises(rather than outsourcing) just as any sensible indie company would do. We look forward to the wordpress community for support.

One thing we could infer from the problem is that there was excessive memory leak
that peaks our max RAM value in apache file server. Causing the server to misbehave and shut down and open up infinite connection to DB server. During the times we don’t edit posts we are fine as heaven would be.
Also the question whether memory leak can be attributed to outdated plugins and themes (having depreciation error php7.2 as against 5.6) ?
PS: we upgraded to a even more powerful DB server and is highly capable this time

You had mentioned the cost of the DB server upgrade being three times the present cost which is why I didn’t go there. I see you have done that now.

If HyperDB worries you then I’d point out that there are some hints that that very plugin is what I’ve heard Automattic uses to power their the extensive needs of their WordPress dot com service. But they have inhouse staff who can tweak that plugin and I’d imagine, if that is what they are using, then there is some big tweaks made to it.

There was another developer working on a similar plugin sometime back but I’ve lost track of him.

The second part of my suggestion concerning the offline development, content creation server synced to the production box via RSS or other syndication mechanisms might be a great option.

I’d also encourage you to take a look at the ‘WP2Static’ solution to parcel out a bunch of your visitor side ‘load’ to another server or at least reduce your load on your present server. You will probably want to talk with the creator of that plugin and maybe some collaboration with him might be in order.

I do have a single site running WP2Static in a test mode and it automatically runs via Cron through WP-CLI but I’m waiting on a forthcoming incremental ‘deploy’.

With your high server load and huge posts numbers I would not recommend this without running a second or even a third server (or more) and that optional paid plugin feature to ‘deploy’

One other thought I had after revisiting this question which might help slightly is to look into a CDN service to reduce the server load which might free up some resources to deal with the DB side in a more timely fashion.

And going back full circle to the HyperDB plugin one more time…

There was a mention in an article (probably the above-referenced Pantheon article) mentioning there was little use to running HyperDB outside of a replicated DB system except HyperDB would retry the DB connections… maybe that right there might be all you really need.

So, to wind up, I’d probably look to running HyperDB on that production box regardless of my actual DB configuration to try to find a solution to my DB issues considering the retry capabilities of HyperDB. You’ll want to leave yourself ‘an out’ as a recovery option should that fail or cause more trouble.

If you decide to build a content creation server in house then I’d certainly go with four boxen and spread my DB load across that using hyperDB with a thought to ‘certifying’ or at least gaining some familiarity with that plugin in house.

I did experiment with HyperDB here back a year or more ago… and I have plans to run HyperDB in the near future on two projects I’m working on here.

Hope some of this helps you.