Measuring the amount of writes in InnoDB redo logs

Choosing a good InnoDB log file size is key to InnoDB write performance. This can is done by measuring the amount of writes in the redo logs. You can find a detailed explanation the this post.

To sum up, here is the main points:

• The redo logs should is large enough to store at a hour of logs at Peak-time
• You can either use the LSN in the SHOW ENGINE INNODB status OUTPUT or the Innodb_os_log_written global STATUS variable (if You be a Percona Server user, the LSN is also given by the innodb_lsn_current status variable)

While reviewing the recommendation I made for a customer, one of my colleagues told me I am wrong in my redo log size cal Culations. After each one of double checked the calculations, it turned out that we experienced something not expected:

• Using Innodb_os_log_written, I found that around 7.15 GB of redo logs were written per hour
• Using the LSN, my colleague found 2.70 Gb/hour (almost a 3x difference!)

Something was obviously wrong in our understanding of what to measure the amount of writes in the redo logs. Let's first has a look at the documentation says. It states that

• Innodb_os_log_written is the number of bytes written to the log file
• The LSN is an arbitrary, ever-increasing value [This] represents a point in time corresponding to operations recorded In the Redo log

What isn't obvious from the documentation are that while Innodb_os_log_written was incremented when the log file is written The LSN is incremented when the log buffer is written.

This is interesting. It means that the durability setting can skew the Results:if innodb_flush_log_at_trx_commit are set to 0, can accident Ally omit or add 1 second of write activity. Of course if you measure variations over 60s, this would not be explain a 3x difference with the LSN. It also means that if the write workload was very non uniform, you can easily get very different numbers if you were not tak ing measures exactly at the same time for the 2 methods.

However, the write workload had not so much variance in my case. I also ran a test with a constant write workload (a mono-threaded script this inserts one row at a time in a table, as FAs T as it can) and I ended up with the same result:numbers were very different between the 2 methods. Even stranger, the Innodb_os_log_written method consistently gave higher numbers than the LSN method, when we would has E Xpected the opposite.

It is time for digging into the source code. All the credits should actually is given to Alexey Kopytov, who isn't only took the time to read the code again and to make Tests, but who also caught something we all missed: writing to the redo logs and increasing the LSN has completely di Fferent logics.

The LSN simply shows the byte offset, so if you write a bytes to the log buffer, the LSN is increased by 100.
Writing to the redo logs are a much more complicated process:every write was a 512-byte write and there can be Overlapp ing writes. Not clear? Let's look at a example when Innodb_flush_log_at_trx_commit are set to 1 or 2 (again, thanks Alexey):

• Transaction 1 writ+es bytes to the log buffer
• At commit, InnoDB writes a 512-byte block @ Offset xxx and increments Innodb_os_log_written by bytes
• Transaction 2 writes bytes to the log buffer
• At commit, InnoDB appends those bytes to the same log block and overwrites the same 512-byte file block at offset xxx, Then increases Innodb_os_log_written by another bytes

At this point, the LSN have increased by Innodb_os_log_written by (a 3x difference!). This means, the documentation is Correct:innodb_os_log_written are the number of bytes written to the redo logs. But it does not reflect the growth of the redo logs.

trying to size the redo logs, looking at the LSN variations is a much better approximation than lookin G at the Innodb_os_log_written variations, which can is significantly far from the reality. However keep in mind, even the LSN is a approximate metric:if your write workload is non uniform and your sampling I Nterval too short, your may well underestimate or overestimate the growth of your redo logs.

• Innodb_os_log_written

  The number of bytes written to the InnoDB redo log files.

Reference:

http://www.percona.com/blog/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/

Http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Innodb_os_log_written

Measuring the amount of writes in InnoDB redo logs