Persistent database (postgresql) connections

ℹ️ Support
1

Hi, I’m trying to configure nextcloud with persistent connection to
postgresql database. I have configured the pgsql extension module in php (pgsql.ini) with theses options:

extension=pdo_pgsql.so
extension=pgsql.so

[PostgresSQL]
pgsql.allow_persistent = On
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = 30 
pgsql.max_links = 30 
pgsql.ignore_notice = 0
pgsql.log_notice = 0s

but I have seen that the tcp connection to the database it’s finish by the nextcloud node (client with respect the postgres server) and it’s not persistent. It’s necessary to configure another parameter in Nextcloud config.php file?

Thank you in advance.

Nextcloud version : 12.0.3
Operating system and version : Centos 7.4
Apache or nginx version : 2.4.6
PHP version : 5.6.31

2

I hate to do the ‘me too’ thing, but since this issue hasn’t been looked at in over a year I thought it worth while. It is adding some significant latency to my connections when Nextcloud attempts to connect to the backend DB. This is due to my firewall setup. In comparison I’m on version 13 with Apache and PHP 7.

I can see that Apache is configured for MPM prefork so I expect the PHP side of things can handle persistent connections. What I don’t know is if Nextcloud itself is trying to keep the DB connection persistent. Any feedback would be appreciated.

3

Hi, sorry to step in, but I feel I have something to contribute… :wink: - if I’m wrong, surely someone will correct me…

It looks like Nextcloud is using the “Doctrine framework” to handle the database access.

In this only recently support for persistent connections has been implemented. https://github.com/doctrine/dbal/pull/3515

a) I wonder whether/how this could be “pulled” into Nextcloud…
b) I stupidly tried to put a hardcoded

$driverOptions[PDO::ATTR_PERSISTENT] = true

in /3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOPgSql/Driver.php right at the entry of connect() - but after restart of php7.3-fpm this did eat up all available connections to postgres resulting in a 500 error - obviously new connections got established but not re-used. And in the log I got numerous

PDO::setAttribute(): SQLSTATE[HY000]: General error: PDO::ATTR_STATEMENT_CLASS cannot be used with persistent PDO instances at /var/www/nextcloud.egroc.de/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php#47

So I’m too dumb to understand whether this is because the Doctrine had been changed in other places as well (log indicating this…), or whether at the Nextcloud layer database connection persistence does break something.

Any comments welcome!

4

Hi,

I was looking for the same solution, but for MariaDB/MySQL. Hopefully someone can find a solution for Nextcloud.

https://help.nextcloud.com/t/mysql-connection-pooling-persistent-connections-with-php-fpm/144259/2

5

For future visitors of this thread, there’s https://docs.nextcloud.com/server/latest/admin_manual/configuration_database/linux_database_configuration.html#postgresql-database

I additionally put the line

  dbpersistent => true,

in my nextcloud’s config.php and I can definitely say that connections are staying open now, at least 2.