binlog files are growing excessively big and fast: 2 days of binlogs > 73G

[paradeiser]

Got this problem too. Increased the VM disk size already a month ago. It doubled in a month from 8 GB to 16 GB without adding any files. No external drive removed. Just a nextcloud instance sitting there. Was on 21.0.4 or so, just upgraded to 21.0.5 and now upgrading to 22.2.0

Basically this problem arose this summer with more or less the same files which were on this server all the time.

Edit:
It doesn't seem to be oc_filecache AFAIKS:

mysql> show table status like "oc_filecache";
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation   | Checksum | Create_options        | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| oc_filecache | InnoDB |      10 | Compressed | 350257 |            159 |    55934976 |               0 |     66527232 |   4194304 |         519199 | 2021-09-09 13:19:05 | 2021-11-09 11:52:31 | NULL       | utf8mb4_bin |     NULL | row_format=COMPRESSED |         |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
1 row in set (0.00 sec)

It seems that mysql's binlog keeps growing on my machine:

root@nextcloud:/var/lib/mysql# du -ch binlog.*
[...]
13G	total

I continue to investigate this and leave this note here since this can be a culprit as well.

Edit 2:
Yep, binlog seems to grow with the latest nextcloud releases at least on my machine.

root@nextcloud:/var/lib/mysql# ls -l binlog.0* | wc -l
148

I lowered the binlog expiration time from 30 days to one week:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

# expire logs after 7 days:
binlog_expire_logs_seconds = 604800

Restart the mysql service and voilà:

root@nextcloud:/var/lib/mysql# ls -l binlog.0* | wc -l
43

This lowered the footprint drastically:

root@nextcloud:/var/lib/mysql# du -ch binlog.*
[...]
3.6G	total

Originally posted by @cls-nebadje in #7312 (comment)

[paradeiser]

Posting from 16.04.2022

I have got the same problem:
binlog files are eating up all my diskspace until it generates an 'Internal Server Error'.

Now I set 'binlog_expire_logs_seconds = 172800'
these two days generate 44 GB of binlog files 😮

If I check these files using 'less' I see mostly ics calendar data. 🧐

Posting from 19.04.2022

Even tho I limited binlogs to 2 days the over all size is still growing (time intervals from file to file are getting shorter: currently about 4 min at binlog-files 101M):
Now at 65G for binlog files in total, 2 days

804K    /var/lib/mysql/#innodb_temp
207M    /var/lib/mysql/nextcloud
116K    /var/lib/mysql/sys
36K     /var/lib/mysql/mysql
1.6M    /var/lib/mysql/performance_schema
65G     /var/lib/mysql
65G     total

mysql database size: 148,4 MB
user files: ~2GB
binlog mostly show ics calendar data.

This is getting absurd… any tips how to contain this?
Upgrading the server hdd every couple of days doesn't seem to be the right way.

Thanks,
paradeiser

21.04.2022

today I'm at 73G (2 days of binlog files)

[solracsf]

Are you using replication? If not, what's the point of using binlogs?

https://simonauer.medium.com/disabling-mysql-binary-logging-c43ce9aeac2e

[paradeiser]

Thanks for your reply!

1. I assume there is a background for Nextclouds default mysqld setup I didn't want to alter at random,
   so I didn't change the standard out-of-the-box setup (aside from configuring cron job).
2. I assume this shouldn't happen anyway (this huge binlog files)

If it's safe to turn off, and it's not needed for Nextcloud "backup" app or any other built in service, I might just turn it off.
But that's probably not resolving the root issue - or is it supposed to be like that?

Is it safe just turning it off? (The VPS is also taking snapshots and backups on the hosting side, no replication).

Thanks, paradeiser

[solracsf]

If this is expected or not, i can't tell, but if there are many writes to the DB, binary log will grow fast for sure.

Is it safe just turning it off?

This goes under of system administration, but here you have an answer : https://dba.stackexchange.com/a/310083/246216
PS: Nextcloud uses InnoDB, that uses it's own redo/undo logs.

[szaimen]

Honestly this is a config issue. Just disable the binlogs and the problem is solved.

[csware]

Just turning the binlog off is not an option if you have a replication setup.

The binlog is full of statements such as "UPDATE oc_authtoken SET password = 'LOOOOONG BASE64' WHERE id = NN".

cf. issue #27603

[ceidem]

Just turning the binlog off is not an option if you have a replication setup.

The binlog is full of statements such as "UPDATE oc_authtoken SET password = 'LOOOOONG BASE64' WHERE id = NN".

cf. issue #27603

I am having the exact same problem and it just started when I upgraded to version 24. This is definitely not a config issue because same my.cnf file has been in use for 4+ years without having to resort to turning off my bin log. Granted I don't use it, so turning it off has stopped my disk space from disappearing, but something has happened.

[brjhaverkamp]

Can someone reopen this issue? I got the same after upgrading to NC 24.0.4 Yesterday. I have a flood of
Table_map : 'owncloud','oc_authtoken' mapped to number 186
messages with a large binary dump behind it.
This generates a 100MB binlog about every 10 minutes.
Nothing changed in my config. Just did the upgrade and updated some apps.

[lukasb314]

Since a few days I'm facing the exact same issue on 26.0.13.

I know it's outdated, but has anybody figured out the reason?

It can't be directly related with an update, since I did not update anything in the recent past.