// you’re reading...


Understanding binary and relay log disk space management for MySQL


I’m writing this as an attempt to help non-database-administrators (nDBAs) understand how to triage disk space crises on MySQL hosts.

Here’s the scenario:
– One of your MySQL hosts is consuming disk space rapidly. You perform investigation at the operating system level and discover that the logs, not the data files are the source of the growth.

Why would logging consume disk space exponentially and data tables remain essentially static?
– If you’re running MySQL replication, the master and slave hosts both have multiple levels of logging which have dependencies. You might be running massive amounts of row-updates, which will be replicated. Updates don’t change the quantity of rows, but simply change the data in existing rows. However MySQL replication sees these executed statements first as completed on the master (master binary log), then pending on each slave (slave relay log), and finally completed on the slave (slave binary log). As each statement on a slave relay log completes execution, a statement is added to the slave binary log as a history item. After all the statements in a relay log flat file are successfully executed, that relay log file is no longer necessary and MySQL removes (deletes) it. Since MySQL replication is single threaded and the master is multi-threaded, it is possible for the master to execute SQL at a faster pace than the slave. This causes “replication lag” noted by the status “Seconds_Behind_Master:” measured in seconds.

How do I manage through temporary bursts in disk utilization?
By configuration, MySQL will store binary log information for a specified period of time before removing it. This configuration item is “expire_logs_days” and is found (on *nix systems) in /etc/my.cnf. There is a way to safely remove binary log files to reduce disk utilization, however the following process must be followed to ensure no data loss.

1. Identify your MySQL replication topology. Which hosts are masters, which are slaves. There may be multiples of each. You can identify a slave by running the command “SHOW SLAVE STATUS” and finding the line: “Master_Host:”. This will tell you the master host where the binary logs needed for this (slave) host are.

PAGER grep Master_Host;

2. Determine which binary log file is currently needed by each host.

PAGER grep Master_Log_File ;

3. Find the OLDEST binary log file on each master that is required. e.g. *nix bash shell and oldest file required is bin.001234:

grep bin /etc/my.cnf
cd /path/to/log/folder
ls -laht bin.001234

4. Let’s say your OLDEST REQUIRED file is ‘bin.001234’ and has a O/S time stamp of 2011 Sep 19 22:54. Run this command to remove all the binary log files older than the OLDEST REQUIRED binary log file.

PURGE BINARY LOGS BEFORE '2011-09-19 22:54:00';

5. At your system prompt, check your disk utilization. You should see a difference. E.g. *nix shell:

df -h

*Note: I’m suggesting the date form of the PURGE BINARY LOGS command because in my opinion it’s less error prone than the file name form. Your mileage may vary.

**Note: If you need to to manage space on a slave host with no daisy-chained slave hosts, then you can simply run the perform all these steps and purge logs before the latest binary log file.


No comments for “Understanding binary and relay log disk space management for MySQL”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: