Mysql connection pooling/persistent connections with php-fpm

[Gatak]

Is it possible to enable persistent connections and connection pooling with Nextcloud as described on PHP: Connections - Manual

I notice that I get warnings on reaching max connections on my mariadb instance while browsing my Nextcloud, especially during thumbnail creation.

Nextcloud 24.0.4
PHP 8.1.8
Gentoo Linux Kernel 5.19.2

I found another thread from 2017 with the same question, but for PostgreSQL: Persistent database (postgresql) connections

[Retidurc]

I did a very simple PR that may interest you: Expose Doctrine support for persistent connections in PDO in Nextcloud settings
The DBAL used by nextcloud have pooling/persistent connection support. Nextcloud actually don’t set that parameter, this patch just expose the parameter in config.php

I use this modification on my own server, so do a few friend of mine. ATM, nothing weird happened.

[Gatak]

Thanks! Have you made any benchmarks or comparisons against the default settings?

[Retidurc]

Kinda, it’s in no way a proper benchmark, but I see connect:db consistently going from 3ms to 0.3ms while using the profiler app

The profiler induces a latency in everything so I guess prod env won’t see that much of a difference. Unless it’s a very busy instance ?
It should be noted : my instance is using postgress, wich have a more important connexion cost than mysql. So your instance may not see the same benefit.

The main difference I see, while the instance is under heavy load, is on the postgres process, using less cpu.

I need to setup a proper benchmarking system but i’m not sure how.

[Gatak]

Hi,

How did you enable the persistent connections? I am using PHP fpm 8.1. There is no longer a mysql.allow_persistent setting in php.ini

I have this:

[MySQLi]
mysqli.max_persistent = -1
mysqli.allow_persistent = 1
mysqli.allow-local-infile = 1
mysqli.max_links = -1
mysqli.cache_size = 20000
mysqli.default_port = 3306
mysqli.default_socket = /run/mysqld/mysqld.sock
mysqli.default_host = localhost
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = On

[mysqlnd]
mysqlnd.collect_statistics = On
mysqlnd.collect_memory_statistics = off

[Pdo_mysql]
pdo_mysql.cache_size = 2000
pdo_mysql.default_socket=/run/mysqld/mysqld.sock

In config.php i tried various variants of 'dbpersistent' => '',

I’m using NC 25.0.1.1

[Retidurc]

Your php.ini is correctly set, but as of NC 25, the option is not availlable yet on nextcloud side,( i guess I missed the merge window)ù
You have to patch lib/private/DB/ConnectionFactory.php like so Expose Doctrine support for persistent connections in PDO in Nextcloud settings by Retidurc · Pull Request #32899 · nextcloud/server · GitHub

[Gatak]

Thanks.

i added the $connectionParams['persistent'] = true; but can’t say it makes any difference. I see loads active connections, especially when browsing photos. How do you check if pooling is active ?

According to phpinfo.php there are no active persistent connections.