How to find out what Nextcloud has been doing with database - excessive load

ℹ️ Support
1

[details=“Support intro”]

My personal Nextcloud database is replicated to a backup server. The backup server handles a number of web sites, some with quite a bit of activity. Just recently, following an upgrade from Debian 10 to 11, the mysql-binlog files were filling up in around 15 minutes. This looked to be attributable to Nextcloud, as stopping Nextcloud resulted in each binlog file lasting around 10 times as long.

How can I find out what Nextcloud is doing that causes so much binlog activity?

Nextcloud version 20.0.5
Operating system and version Debian 11
Apache 2.4.48-3.1+deb11u1
PHP version 7.4.21

2

Every MySQL query is written to the MySQL binlog. Would look what kind of queries are executed by Nextcloud (e.g.: show processlist;) or you just read your binlog.

According to MySQL:

For example, to display the contents of the binary log file named binlog.000003 , use this command:

mysqlbinlog binlog.0000003
3

Thanks. The binlog contains numerous repeats of things like this (with large sections of what looks like base 64 replaced by …)

START TRANSACTION
/*!*/;
# at 89099
# at 89525
#210929 11:14:47 server id 12  end_log_pos 89525 CRC32 0x6e8f94ad       Annotate_rows:
#Q> UPDATE `oc_authtoken` SET `password` = 'PKzov0asSAeYWL3EXGx1FHtYU8+wHOzXe3I5gL+Kr+REdbUR7WGfAiv+Rj/l7btZAT9D74ZsWjvTRgC1H9pJ2yKzIVicvFRNhAR+afSdNoZ4PKM2hLLwVcoTX5YzlSTsrSqpzW9aq8dlUfNFd+DkNNbBTh
Hs60zofH/+eGp1HAsOcWQDF4G5m6ctwlCVsbev9V+9+Tf0uOPzuZQJIbSD6OAZ1C5yG0CIkvymAcJ51WW4z9g2OLTyYYFIA0rOk/ezc+eSFRxqJuPiQE72nDlFlsvtcXTGPnZVOGcvHpfkQCw9v+Bf43ZDnQfT78ymyv60EGa6GpATMuUbezCGdwxtLg==' WHERE 
`id` = 1198
#210929 11:14:47 server id 12  end_log_pos 89612 CRC32 0xcf2a7502       Table_map: `nextcloud`.`oc_authtoken` mapped to number 15988
# at 89612
#210929 11:14:47 server id 12  end_log_pos 98910 CRC32 0xbd2609bc       Update_rows: table id 15988 flags: STMT_END_F

BINLOG '
lzxUYRMMAAAAVwAAAAxeAQAAAHQ+AAAAAAEACW5leHRjbG91ZAAMb2NfYXV0aHRva2VuABAIDw/8
/A8CAgMD/AP8/AIBCwAB/AMEBCADBAQEyL8CdSrP
lzxUYRgMAAAAUiQAAF6CAQAAAHQ+AAAAAAEAEP////8ADK4EAAAAAAAABQBwZWRkeQUAcGVkZHlY
...
Qm9qdTc1bkprUjZRZmpGZmNLQkNESgp6d0lEQVFBQgotLS0tLUVORCBQVUJMSUMgS0VZLS0tLS0K
AgAAvAkmvQ==
'/*!*/;
# Number of rows: 1
# at 98910
#210929 11:14:47 server id 12  end_log_pos 98941 CRC32 0x8f42ecbc       Xid = 7547379
COMMIT/*!*/;
# at 98941
#210929 11:14:47 server id 12  end_log_pos 98983 CRC32 0x187d2cba       GTID 0-12-2068246 trans
/*!100001 SET @@session.gtid_seq_no=2068246*//*!*/;

Unfortunately, I don’t know what this is about!