DB logs are too big and crash Joomla

[okynnor]

Since moving from Joomla 4 to 5, I have noticed that the database uses too much space. This space is being used by the binary logs.

The files are so big, in the GB, rage for a small and fairly static site that it seems ridiculous. Is there a way to understand what these files are doing and if they are don't needed, how to turn them off so it doesn't keep using up disk space?

-rw-r----- 1 systemd-coredump systemd-coredump    2943832 Nov  4 20:05  binlog.000001
-rw-r----- 1 systemd-coredump systemd-coredump 1074001751 Nov 13 00:52  binlog.000002
-rw-r----- 1 systemd-coredump systemd-coredump  446938529 Nov 17 00:02  binlog.000003
-rw-r----- 1 systemd-coredump systemd-coredump     851972 Nov 17 00:36  binlog.000004
-rw-r----- 1 systemd-coredump systemd-coredump   30455069 Nov 18 02:46  binlog.000005
-rw-r----- 1 systemd-coredump systemd-coredump 1073955810 Nov 26 07:13  binlog.000006
-rw-r----- 1 systemd-coredump systemd-coredump  320594698 Nov 27 17:18  binlog.000007
-rw-r----- 1 systemd-coredump systemd-coredump   64679316 Nov 27 19:28  binlog.000008
-rw-r----- 1 systemd-coredump systemd-coredump 1073920488 Nov 29 04:36  binlog.000009
-rw-r----- 1 systemd-coredump systemd-coredump 1073940569 Nov 30 13:37  binlog.000010
-rw-r----- 1 systemd-coredump systemd-coredump  704218524 Dec  2 21:00  binlog.000011
-rw-r----- 1 systemd-coredump systemd-coredump   11268228 Dec  2 21:56  binlog.000012
-rw-r----- 1 systemd-coredump systemd-coredump        508 Dec  2 21:59  binlog.000013
-rw-r----- 1 systemd-coredump systemd-coredump    3484578 Dec  2 22:33  binlog.000014
-rw-r----- 1 systemd-coredump systemd-coredump        715 Dec  2 22:44  binlog.000015
-rw-r----- 1 systemd-coredump systemd-coredump   11120953 Dec  2 23:03  binlog.000016
-rw-r----- 1 systemd-coredump systemd-coredump      27415 Dec  2 23:16  binlog.000017
-rw-r----- 1 systemd-coredump systemd-coredump    3277912 Dec  2 23:28  binlog.000018
-rw-r----- 1 systemd-coredump systemd-coredump   19788753 Dec  2 23:57  binlog.000019

I've tried look in Joomla 5 administration to turn off logging but they don't seem to be related to the database itself.

Thanks.

[muhme]

I am a docker-joomla images consumer too, using JBT, so it is an interesting question for me as well.

From my observation, this issue does not seem to be related to the Joomla Docker image itself but rather to the database configuration:

• In my MySQL container, binary logging is enabled by default installation.
• In my MariaDB container, binary logging is not enabled by the default installation. You can verify this:

  SHOW VARIABLES LIKE 'log_bin';

• To compare and see the space waste on my installation, I measured the binary log file sizes while running Joomla System Tests
  • With Joomla 4.4-dev, the MySQL container generated 8 MB of binary logs
  • With Joomla 5.3-dev, the MySQL container generated 11 MB of binary logs

Since I am working in a test environment, I simple disabled binary logging in my MySQL container by modifying the my.cnf file:

[mysqld]
skip-log-bin

If you are running a test or non-replicated environment, you might also consider disabling binary logging. Alternatively, you could configure the binary logging settings to optimize performance and storage, for example:

[mysqld]
binlog_expire_logs_seconds = 86400  # Keep logs for 1 day
binlog_format = STATEMENT          # Use only statement-based logging
max_binlog_size = 100M             # Rotate logs at 100MB

[Llewellynvdm]

Is there something we can do to help with this?

But yes I would agree that Joomla (image) does not manage the DB container directly and you should be able to add your own config to your DB setup to mitigate this log issue.

[okynnor]

My site is production. Is there any use for these binary files?
Since I started this thread, there has been more storage used with one new binary file added each day.

I'm not an expert at this area. Would you please show me how to add these entries into the mysql configuration?

[muhme]

Assume your database container is named mysql then copy the MySQL configuration file to your local system with

docker cp mysql:/etc/my.cnf .

Now open my.cnf in your text editor, find line [mysqld] and after this line add:

binlog_expire_logs_seconds = 86400  # Keep logs for 1 day
binlog_format = STATEMENT          # Use only statement-based logging
max_binlog_size = 100M             # Rotate logs at 100MB

Copy modified my.cnf back in database container and restart mysql docker container:

docker cp my.cnf mysql:/etc/my.cnf
docker restart mysql

[okynnor]

In my Joomla docker container, there are two directories: db_data and Joomla_data
I went into db_data to add the lines you suggested to auto.cnf. It's the only cnf file.
saved and restarted the docker container stack.
It still created a new binlog file in the next number sequence.
I expected the behaviour to be was that the system would automatically delete all the binlogs that were larger than 1 day and were larger than 100M.

What am I missing?

I'm attaching a copy of my auto.cnf.

[muhme]

• no, auto.cnf is the wrong file, the added lines should be removed
• to find the right my.cnf file, you could run the following command, assuming your docker container name is mysql:

docker exec mysql /bin/bash -c "find  / -name my.cnf 2>/dev/null"