MySQL – How to Calculate Checkpoint Age

MySQL

According to the Percona article MySQL checkpoint age may freeze it:
https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/

How can I calculate it?
How can I ensure that innodb_log_file_size of 134 mb is enough for me or I need to increase it?

This is the section LOG from SHOW ENGINE INNODB STATUS, but what is the correct formula?
( and how do I get megabytes to compare with innodb_log_file_size )

---
LOG
---
Log sequence number          26361002986468
Log buffer assigned up to    26361002986468
Log buffer completed up to   26361002986148
Log written up to            26361002986148
Log flushed up to            26361002902700
Added dirty pages up to      26361002986148
Pages flushed up to          26360959010673
Last checkpoint at           26360958543063
7934296064 log i/o's done, 669.00 log i/o's/second

Thanks.

P.S We are thinking should we increase innodb_capacity_io from default 200 to, lets say, 500 or 1000. ( we have enough iops available )

Best Answer

Those sequence numbers are necessarily large, monotonically increasing, numbers. They are not byte offsets into the log. You may find the number in the log, of you have fun with hex dumps. (I don't.)

That Percona blog, while good, is a decade old. A large log no longer delays restart as much as it once did.

Consider this for judging whether to increase (or decrease) the log_file_size:

Minutes between InnoDB log rotations = Uptime / 60 * innodb_log_file_size / Innodb_os_log_written

60 minutes is a good target, but it is not a precise target.

Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.

innodb_log_files_in_group traditionally has been 2. I suggest you use 2 unless you are on a new version of MariaDB, where it is no longer needed.

Related Question