dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

by Céline Perves -
Number of replies: 4
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Hello,

In moodle 3.5.x we would like to activate postgres db persistance but in the connect function code we found :

file pgsql_native_moodle_database.php

```

if (empty($this->dboptions['dbpersist'])) {
    $this->pgsql = pg_connect($connection, PGSQL_CONNECT_FORCE_NEW);
} else {
    $this->pgsql = pg_pconnect($connection, PGSQL_CONNECT_FORCE_NEW);
}

```

Wich seems to indicate that even with persistant connections, connection will be forced to renew

Is it the case?

If yes, is there a reason for that?

Thank in advance

Céline

Average of ratings: Useful (1)
In reply to Céline Perves

Re: dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I think the thing most people use for connection pooling with Moodle + Postgres is something called 'pgbouncer'. https://www.pgbouncer.org/
Average of ratings: -
In reply to Tim Hunt

Re: dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

by Céline Perves -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers
Thank you, we are going to study and test that solution since we have performances troubles due to too much connections due to full remote courses in our University due to sanitary context. We experiment various peak frequency during the day

Average of ratings: -
In reply to Céline Perves

Re: dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
One thing to check is: You have a certain number of Apache (or whatever) web servers and each of them allows up to a certain number of active 'workers'. E.g. if you have 2 web servers with up to 150 workers each, then you need to make sure that you Postgres server is set to allow more than 300 active connections. And, you should probably make it something more like 350 so that other things like server admins, monitoring tools, can connect at the same time.

Also, probably the best thing youc an do to improve Postgres performance is to ensure the server has enough RAM, and Postgres is configured to use it. On our server, we bascially have enough so that essentially the whole DB (except the log table) can be cached in RAM.
Average of ratings: -
In reply to Tim Hunt

Re: dbpersist parameter not seems to be effective with postgres because of PGSQL_CONNECT_FORCE_NEW

by Céline Perves -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers
Hello,
I checked the workers and they are ok 12 backends with MaxRequestWorkers set to 250 and max_connections set to 3000
We improve CPU, RAM and everything possible and then observe the reaction, yesterday we experiment peaks but this time database server did not broke
Need tnow to try pgbouncer when it will be possible
Thanks for your precious advices
Céline
Average of ratings: Useful (1)