Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
I found that the /var/lib/mysql/ folder weighs as much as 289GB. As I understand it, it is difficult to call the norm.
How to fix it?
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-binor--disable-log-binoption at startup. If either of these options is specified and--log-binis also specified, the option specified later takes precedence. When binary logging is disabled, thelog_binsystem variable is set toOFF.
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.
{site}-bin.006029. (006029, 006030, 006031) - different combinations of numbersdu 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.duwill tell us which.