Home / server / Questions / 941323
In Process
the_nuts
Asked: 2018-11-24 02:34:46 +0800 CST

Can I put the MySQL binlogs on a slow disk?

  • 772

We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days.

Does writing the MySQL binlogs to a slower disk (like a remote directory) slow down the MySQL performance?

mysql

2 Answers

  1. Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.

    I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.

    You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.

    1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.

    2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.

    3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.

    4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)

    5. Purge all log files up to but not including the target file.

    If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.

    • 7

  2. From MySQL manual:

    By default, the binary log is synchronized to disk at each write (sync_binlog=1). If sync_binlog was not enabled, and the operating system or machine (not only the MySQL server) crashed, there is a chance that the last statements of the binary log could be lost. To prevent this, enable the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. See Section 5.1.8, “Server System Variables”. The safest value for sync_binlog is 1 (the default), but this is also the slowest.

    The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.

    Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.

    Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.

    • 5