Moodle and postgres

Moodle and postgres

by Sven Laudel -
Number of replies: 2
Hello,

we are using Postgresql as database for our moodle installation.

We setup postgresql for accepting up to 300 connections (max_connections = 300).

Now it happened twice to us, that at night the database connections filled up and postgresql stopped accepting new connections. So moodle was not available anymore. The web-/application servers did not show any increased access activity before this happened.

Did anyone experience similar behaviour? My guess is, it could be a nightly cron job of moodle, which fills up the connections, but I don't know for sure.

What are the recommendations regarding max_connections for postgresql? Are there any guidlines how many connections are needed for how many concurrent users, just as a rule of thumb?

By the way, we are using Moodle as of version 3.7+ and have 30.000+ registered users with at least 10-15.000 active at least once a day.

Best reagrds
Sven

Average of ratings: -
In reply to Sven Laudel

Re: Moodle and postgres - Problems with something eating up all database connections

by Sven Laudel -

Tonight we experienced the problem again.

My guess it could be a cron job seemed to be wrong. After the last database problem I moved the cron job to a separate host with pgbouncer limiting the available database connections to around 50 for the cron job.

Before restarting the database to release the connections I've had a look into the database running "SELECT count(*) as anzahl,client_addr, state FROM pg_stat_activity where datname= 'moodle2db' group by client_addr, state order by client_addr;":

Connections
count client_addr state
64 app1 active
45 app1 idle
55 app2 active
24 app2 idle
2 cronhost active
68 app3 active
38 app3 idle
Making round about 300 connections, which is the limit for the db server.

I could also provide some info about the running/idling processes from pg_stat_activity if required.
Neither the web server access log nor the php logs show any suspicious activity.

So I would be happy about every help.
Regards Sven

Average of ratings: -