0

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 )

2
  • innodb_log_file_size is tricky to increase. First, what version are you using? And is it in a cloud?
    – Rick James
    Apr 15 at 18:32
  • Yes, MySQL AWS RDS 8.0.20 Apr 15 at 19:04
1

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.

8
  • Hi Rick. Tnx for the answer. In the reality, I still have a problem described here: dba.stackexchange.com/questions/289495/… You have left few comments there as well. So I am continuing to research what could be the issue and thought that to verify checkpoint age. Apr 15 at 19:07
  • To be honest, I am thinking should I increase innodb_capacity_io from default 200 to, lets say, 500 or 1000. ( we have enough iops available ) Apr 15 at 19:09
  • @user3489820 - Yes, raise it, especially if the disk is SSD, which is very likely to be the case on AWS. Also, AWS may not allow changing the log_file_size. Check with them; my info could be out of date.
    – Rick James
    Apr 15 at 19:17
  • Well, if I make Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total * 100, I will get 0.8%.... Whereas I can increase innodb_capacity_io, but it doesn't look it will help me. Correct my understanding if I am wrong please. Apr 15 at 19:36
  • @user3489820 - I recommend 30% for that formula. There is no need to try to improve on 0.8%. What is the value of your innodb_max_dirty_pages_pct?
    – Rick James
    Apr 15 at 21:07

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.