NC 33: no persistent connections with MySQL

ℹ️ Support
, ,
1

There’s a few threads on persistent database connections on Nextcloud, but all are closed. I’m running vanilla Nextcloud 33 as shipped with Fedora, with a Redis server for caching, and a MySQL database, all on the same host.

I’ve verified via PHPINFO that MySQL has persistent connections enabled as per mysqli configuration (although that doesn’t seem to matter since pdo_mysql has no setting for such a thing and neither does pdo_mysql which appears to be using mysqlnd).

config.php in fact has dbpersistent set to true.

And yet, PHPINFO does not show me any open connections, and neither does my server when I look at netstat -tnp output (there ought to be some open connections after refreshing a few Nextcloud pages).

What should I look into, in order to enable persistent connections? Thanks in advance.

The Basics

  • Nextcloud Server version (e.g., 29.x.x):
    • 33.0.6
  • Operating system and version (e.g., Ubuntu 24.04):
    • Fedora 43
  • Web server and version (e.g, Apache 2.4.25):
    • Apache / PHP-FPM
  • Reverse proxy and version _(e.g. nginx 1.27.2)
    • not relevant
  • PHP version (e.g, 8.3):
    • PHP 8.4.3
  • Is this the first time you’ve seen this error? (Yes / No):
    • not an error
  • When did this problem seem to first start?
    • forever
  • Installation method (e.g. AlO, NCP, Bare Metal/Archive, etc.)
    • Fedora RPMs
  • Are you using CloudfIare, mod_security, or similar? (Yes / No)
    • no

Log entries

There are no relevant log entries.

Configuration

Nextcloud

The output of occ config:list system or similar is best, but, if not possible, the contents of your config.php file from /path/to/nextcloud is fine (make sure to remove any identifiable information!):

{
    "system": {
        "log_type": "file",
        "log_type_audit": "file",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "updatechecker": false,
        "check_for_working_htaccess": false,
        "asset-pipeline.enabled": false,
        "assetdirectory": "\/var\/lib\/nextcloud",
        "preview_libreoffice_path": "\/usr\/bin\/libreoffice",
        "apps_paths": [
            {
                "path": "\/usr\/share\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/lib\/nextcloud\/apps",
                "url": "\/apps-appstore",
                "writable": true
            }
        ],
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "33.0.6.2",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "htaccess.RewriteBase": "\/",
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "tls",
        "mail_sendmailmode": "smtp",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "587",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "enabledPreviewProviders": [
            "OC\\Preview\\BMP",
            "OC\\Preview\\GIF",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\Krita",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\MP3",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\PNG",
            "OC\\Preview\\TXT",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\Movie",
            "OC\\Preview\\PDF",
            "OC\\Preview\\Image",
            "OC\\Preview\\HEIC"
        ],
        "preview_max_x": 1024,
        "preview_max_y": 1024,
        "theme": "",
        "app_install_overwrite": [
            "news",
            "caniupdate",
            "inventory",
            "files_markdown",
            "mindmap_app",
            "facerecognition",
            "files_scripts",
            "otpmanager",
            "files_mindmap"
        ],
        "memcache.local": "\\OC\\Memcache\\APCu",
        "default_phone_region": "CH",
        "maintenance_window_start": 4,
        "memories.db.triggers.fcu": true,
        "memories.exiftool_no_local": true,
        "memories.vod.path": "\/var\/lib\/nextcloud\/apps\/memories\/bin-ext\/go-vod-amd64",
        "memories.vod.ffmpeg": "\/usr\/bin\/ffmpeg",
        "memories.vod.ffprobe": "\/usr\/bin\/ffprobe",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memories.viewer.high_res_cond_default": "always",
        "dbpersistent": true,
        "loglevel": 2
    }
}
2

Hi @Rudd-O,

this is not a bug in NC 33. dbpersistent does work with MySQL, the problem is that you are looking at the wrong extension with a tool that cannot show live persistent connections in the first place.

Nextcloud does not talk to MySQL through mysqli, it uses pdo_mysql via Doctrine DBAL. So mysqli.allow_persistent in phpinfo is simply the wrong knob, it will always look empty because that extension is not the one in use. And PDO has no php.ini equivalent to allow_persistent, persistence there is set programmatically through the PDO::ATTR_PERSISTENT attribute. That is exactly what dbpersistent does. You can follow the whole chain in the code:

and the config.sample.php entry

spells it out: the persistent option from Doctrine DBAL, which in turn uses PDO::ATTR_PERSISTENT from the PDO driver.

phpinfo cannot help you here either way, it only reports module and ini state, never the live reuse of a connection, so a persistent PDO handle is invisible there by nature.

To actually see it you have to keep in mind how PDO persistence behaves: the connection is held per PHP-FPM worker process, not per request and not shared between workers. A worker keeps its handle open and reuses it across the requests it serves, so what you can observe is at most about one open php-fpm to 3306 socket per live worker, never one per request. And it only lasts as long as the worker does, pm.max_requests recycling, an ondemand pool, idle timeouts or an FPM reload all tear it down again. cron.php runs as CLI, a fresh process every run, so nothing persists across cron there at all.

The honest test is with ss, watching whether the connection stays open after a request has finished instead of closing right away:

watch -n1 "ss -tnp state established '( dport = :3306 )' | grep php-fpm"

If a small, stable set of ESTABLISHED sockets lingers between requests, roughly the number of warm workers, it is doing its job.

One thing worth double-checking on your side, whether you really want it: on Nextcloud persistent DB connections buy you very little and tend to accumulate idle connections across all workers and pools, which is a fast way into max_connections and “Too many connections”. Plenty of instances run better without it, and if raw connection overhead is the concern a DB-side pooler or simply a higher max_connections is the more robust answer.

h.t.h.

ernolf

3

I don’t see those connections to port 3306 at all. I did that test before and I redid it just now. No connections to port 3306.

Could it be because the library is “intelligently” falling back to using an UNIX domain socket when the host is localhost? I see a number of steady open connections (about 8) to mysql.sock when using netstat -n, which dwindle to zero all php-fpm workers are stopped. That’s weird – my configuration parameters are clearly using localhost (with an empty dbport but that is not actually used) and not the UNIX domain socket.

Update: it seems so. From PHP: PDO_MYSQL DSN - Manual :

image
image1028×193 23.2 KB

4

Yes, that is exactly it, and it is not an “intelligent fallback”, it is the documented MySQL client convention: the hostname localhost is special-cased to connect through the local UNIX socket, while every other value, including 127.0.0.1, goes over TCP. mysqlnd / PDO_MYSQL follow that rule, so your empty dbport never comes into play at all.

Mea culpa, sorry. The empty dbport in your config was already the tell: no port almost always means a default localhost setup, and localhost means the socket, so the ss ... dport = :3306 I gave you could never show anything, it only catches TCP. But you have figured it out yourself now. This is how I should have handed you that command:

watch -n1 "ss -xp | grep -i mysql"

And your netstat observation is already the proof: about 8 steady connections to mysql.sock that persist between requests and drop to zero only when the php-fpm workers stop. That is precisely persistent PDO handles held per worker. dbpersistent is working.

Glad it is sorted.

ernolf