1

The site had 504 errors, and as I found it was due to lack of memory. The site itself weighs 54GB, and the disk space is 500GB.

enter image description here

I found that the /var/lib/mysql/ folder weighs as much as 289GB. As I understand it, it is difficult to call the norm.

enter image description here

How to fix it?

5
  • What is in the directory? (Or rather, what is in the directory that is more than say 100 MB per file?) May 23, 2023 at 19:44
  • From the strange, it is the set of files {site}-bin.006029. (006029, 006030, 006031) - different combinations of numbers
    – Steve Dekart
    May 23, 2023 at 19:56
  • Those are binary logs. See my answer here for an explanation of what those logs are for, and how to manage them: dba.stackexchange.com/a/320454/2703 May 23, 2023 at 20:31
  • @BillKarwin How to reduce the number of generated binlogs. They are created every 5-7 minutes for 1 GB May 23, 2023 at 21:07
  • Please provide du mysql (when sitting above that directory). Bill suspects it is binlogs in the directory. Depending on max_binlog_size, each file might be 1GB or 100MB. Or maybe some subdirectory (aka DATABASE) is huge. du will tell us which.
    – Rick James
    May 23, 2023 at 22:26

1 Answer 1

0

You asked:

How to reduce the number of generated binlogs. They are created every 5-7 minutes for 1 GB

Binary logs are produced on the source instance relative to the rate of INSERT/UPDATE/DELETE you execute. The more writes to your database, the faster binary logs accumulate.

First: Ask yourself if you actually need the binary logs at all. If you disable writes to the binary log, this would of course eliminate them by 100%.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_log-bin

To disable binary logging, you can specify the --skip-log-bin or --disable-log-bin option at startup. If either of these options is specified and --log-bin is also specified, the option specified later takes precedence. When binary logging is disabled, the log_bin system variable is set to OFF.

In MySQL 5.x, disabling it is is done differently: remove (or at least comment out) the log_bin variable in your my.cnf and restart the MySQL Server.

Second: If you decide you do need the binary logs, you could reduce the size by using binlog_format=STATEMENT or binlog_format=MIXED. There are advantages to the ROW based binary log format, and I prefer it when it's possible. But this log format often causes the logs to be larger.

Third: if you do need the binlogs to be in ROW format, you could use binlog_row_image=MINIMAL which means to omit columns that aren't changing from each logged event for an UPDATE or DELETE event. All columns are still needed in the log for INSERT events.

Fourth: Why are you writing to the database so much? Could your application be redesigned to make some of the updates in-memory only in cache? That way only a fraction of the data changes would be committed to the database, and the rate of binary log growth would be lower.

Fifth: If you can't make any of the other changes, then increase the size of your storage volume, until it accommodates the needs of your binary logs. If you've got a lot of traffic you do need the infrastructure resources to support it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.