2

I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it.

When I set global sync_binlog = 1, the server I/O goes very high and after some hours the server crashes. In the graph bellow, at 15:20 the server crashes. Then from 15:20 til ~15:40 it is recovering a MyISAM table I have with more then 120 million rows. Then at 15:40 it gets back to normal operation. The boot configuration for sync_binlog is 0

At 16:15, I set global sync_binlog = 1, and the I/O goes up again. At 16:45, I set global sync_binlog = 1 and the I/O goes down.

Why this is happening? Is there some way to avoid this high I/O and also the crash?

enter image description here

  • up for this thread. How many threads do you have per minute? We have at least 800 threads per second, is it recommended to have the sync_binlog = 0 or 20? thanks. Reyam – Reyam Melandrew Cruz Jul 22 '19 at 19:16
3

Please keep in mind what MySQL Documentation says on sync_binlog:

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Apparently, you are writing lots of commit groups in one write operation to the binary log.

  • Setting sync_binlog to 0 lets the OS flush commit groups to disk (on its own time)
  • Setting sync_binlog to 1 lets mysqld flush commit groups to disk on each write

You could set sync_binlog > 1 to throttle mysqld's flush of the binlog.

You should expect setting sync_binlog to create more disk I/O, not less.

If your DB Server is a bare-metal server, you may need to check your disk controller and either change the batteries or get a better disk controller.

UPDATE 2015-04-02 10:57 EST

You just mentioned in your comment that you are using SSDs. I wrote a post over a year ago (MySQL on SSD - what are the disadvantages?) stating that SSDs are not all that good for sequential writes. In your case, the sequential writes are happening on the binlogs. SSD write performance is the same regardless of sequential writes and random writes. I would consider moving your binary logs to HDD rather than on SSD.

  • In fact increasing sync_binlog to 20 solved the problem. But anyway, why having it at 1 was causing the database crash? I could live with high I/O if it didn't cause database crash – Daniel Cukier Apr 2 '15 at 3:52
  • Why ??? With sync_binlog = 1 every time it is time to do a single write to the binlog, it is flushed. Now, with sync_binlog = 20, every 20 times it writes to the binlog, it is flushed. – RolandoMySQLDBA Apr 2 '15 at 3:55
  • Yes, it has a lot of writes, ok, but why crashing? By crashing I mean the service is going down. And this happens randomly after some hours – Daniel Cukier Apr 2 '15 at 12:49
  • Are you using Amazon EC2 ??? – RolandoMySQLDBA Apr 2 '15 at 13:34
  • Google cloud instances – Daniel Cukier Apr 2 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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