5

The question is simple. Does turning on binary logs in MySQL slow down performance considerably, i.e. will it write large amount of data to disk thereby causing high I/O, will it hog memory, does it have any implications for the CPU ?

Thanks

RolandoMySQLDBA
  • 3,155
Adergaard
  • 151
  • 1
  • 3

2 Answers2

4

Reference

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

malakrsnaslava
  • 2,813
  • 5
    Does that mean that percona performance blog is incorrect saying the following "But enabling the binary log reduces MySQL’s performance dramatically. " here www.percona.com/blog/2010/10/23/mysql-limitations-part-2-the-binary-log/ – Adergaard Dec 04 '14 at 15:58
  • define slightly , and define dramatically. Log will efect performance, that is for sure but I think no one can tell you how much. (I think it will be slightly, no metter what slightly means ;) ) – malakrsnaslava Dec 04 '14 at 16:03
  • 2
    I have 100 queries / second. If I'm correct about how bin_log works, it actually performs a write to disk for EACH of those. That's a humongous amount of writes. Sounds like it will come with an equally humongous cost in performance. I define humongous as somewhere between monster-super-duper-much and "damn, that's a lot". – Adergaard Dec 04 '14 at 16:09
  • Adergaard's comment links to https://www.percona.com/blog/2010/10/23/mysql-limitations-part-2-the-binary-log/ (there are non printable characters in his version) – Pierre-Alexis de Solminihac Aug 17 '17 at 15:07
3

If the binary logs and the MySQL data write to the same disk mount, then yes it will slow things down.

If you create a separate disk mount just for logs, then it will not slow down as much.

In Facebook, there is a Database Engineer who splits up the MySQL files into separate disks. In light of this example, you could just setup a second disk, map log-bin to that disk. That way, no wasted spindles writing log data on a data volume.

RolandoMySQLDBA
  • 3,155