Unable to connect to the database — Too many connections

igor

I know you said there is a 30 limit per DB user and I thought I was pushing over the limit because I can get a surge of API requests sometimes, but yesterday I received this error from application where I'm the only one with access and this error has appeared on both Opal5 and Opal6 server at the same time (2min apart).

I get this error at least once a day for two weeks now. What is the solution here?

houdinihound

Is it possible your app isn't pooling connections or closing your connections properly after use?

You don't mention which db or how you're connecting, but for Postgres, I've found the query below useful to give me an idea of how my connections are being used and what state they're being left in.

SELECT * from pg_stat_activity WHERE datname = 'my_db_name';

Description of the columns returned: https://www.postgresql.org/docs/11/monitoring-stats.html

HTH,

igor

I'm talking about Maria DB. One app is custom Yii2 (PHP) application, other is osTicket (PHP). Both on Apache.

I guess it is always possible that apps are not closing connections properly, but they haven't changed from Webfaction where they did not have any issues. Error appeared on two different hosting accounts (and servers) at approximately the same time so I doubt it's the application.

sean

We think these connection problems are mostly a side-effect of our Apache PHP-CGI stack.

What happens is that on PHP-CGI each individual request is spawning a new PHP process with its own DB connection. If your site gets a ton of traffic all at once then it can quickly run out of available connections.

We're working on improving the PHP stack to mitigate this problem.

The connection error can also happen if you've set up a single DB user for all of your MariaDB databases. The connection limit is 30 per DB user (and we're not raising it) so if you've got a single DB user connecting to multiple databases, that user will run out of connections very quickly. If this sounds like you, then make a DB user for each of your app's databases and use that in your application config.

If you absolutely need a higher number of per-user DB connections then please use a private MariaDB: HOWTO install and run a private MariaDB server instance on Opalstack - Opalstack Community Forum

igor

OK, thank you for the explanation.

I'm using one DB user per database. As I already mentioned one of the apps has only one client user (me) accessing it, this client user is using ajax to pull data every 60 seconds, which is only 1 request and 1 db connection per minute. There are also a few 502 bad gateway errors in the mix.

This is how it looks since yesterday (times are UTC +1):

Error 500 is because of too many connections and 502 is bad gateway.

sean

igor the 502s should be resolved with the PHP stack improvements.