Comments on: How to calculate a good InnoDB log file size

By: Eric Thirolle

Eric Thirolle — Wed, 17 Jun 2020 21:46:44 +0000

for MySQL 5+, here is a one-liner to collect the value of MB per minute written to the InnoDB log:

select VARIABLE_VALUE from information_schema.global_status where variable_name=’Innodb_os_log_written’ INTO @log1; select sleep(60); select VARIABLE_VALUE from information_schema.global_status where variable_name=’Innodb_os_log_written’ INTO @log2; select (@log2 – @log1) / 1024 / 1024 as MB_per_min;

By: CESAR MURILO DA SILVA JUNIOR

CESAR MURILO DA SILVA JUNIOR — Fri, 08 Nov 2019 14:18:44 +0000

My innodb_log_file_size is 50MB. The calculation you presented resulted in 0.71658Mb / s * 60 = 43Mb. Does this mean I can leave it as it is? Is log churn by default 1 hour? How do I check turnover time?

By: CESAR MURILO DA SILVA JUNIOR

CESAR MURILO DA SILVA JUNIOR — Fri, 08 Nov 2019 13:59:31 +0000

In reply to CESAR MURILO DA SILVA JUNIOR. I already solved by adding backslash before G, as the friend above said.

By: CESAR MURILO DA SILVA JUNIOR

CESAR MURILO DA SILVA JUNIOR — Fri, 08 Nov 2019 13:58:27 +0000

In reply to Richlv. Thank you, it helped me.

By: CESAR MURILO DA SILVA JUNIOR

CESAR MURILO DA SILVA JUNIOR — Fri, 08 Nov 2019 13:56:01 +0000

How do I do this calculation in MariaDB 10.0.38?

show engine innodb statusG select sleep(60); show engine innodb statusG;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘statusG select sleep(60)’ at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘statusG’ at line 1

By: Jette

Jette — Thu, 27 Jul 2017 19:37:21 +0000

In reply to Sheeri K. Cabral. I guess this has changed since 2008. These days the two files always has the same mtime. So I guess your "trick" doesn't work anymore. Quote: The first log file will always have the checkpoint info written to it, so that is why even if ib_logfile1 is the “active” logfile, you will still see writes to ib_logfile0. https://www.pythian.com/blog/ib_logfiles-mtime-mystery/

By: Richlv

Richlv — Thu, 22 Sep 2016 14:53:06 +0000

looks like formatting in the example commands got wrong – in “show engine innodb statusG select sleep(60); show engine innodb statusG”, add a backslash before each “G”

By: Bjoern

Bjoern — Thu, 26 Mar 2015 10:01:16 +0000

I have a similar problem as Adrián
following this I would need 8GB log_file_size

181.91 M/s

By: Adrián

Adrián — Fri, 17 Oct 2014 06:06:07 +0000

Do you think that 5 MB or 64 MB is a value for real world workload ? These are my numbers;
select (283659589588754 – 283659393725516)/1024/1024 as MB_per_min;
+————–+
| MB_per_min |
+————–+
| 186.78973961 |
+————–+

This is 10 GB for one our… 🙁 I am reading this because I am hitting this problem at backup: https://www.percona.com/forums/questions-discussions/percona-xtrabackup/14029-backup-fails-with-log-block-numbers-mismatch

By: Glas

Glas — Wed, 08 Oct 2014 22:23:01 +0000

How long can the db survive with “InnoDB: ERROR: the age of the last checkpoint is…which exceeds the log group capacity…”?
It is something that should be fixed immediately or there is just a performance problem?

By: No2 Maximus benefits

No2 Maximus benefits — Thu, 21 Aug 2014 04:06:10 +0000

Thanks for finally taslking about >How to calculate a good InnoDB log file size
– MySQL Performance Blog

By: anonymous

anonymous — Fri, 30 May 2014 19:52:33 +0000

@Jack Tors you’re supposed to look at the DIFFERENCE between these numbers after 60 seconds, not the current literal values.

By: Jack Tors

Jack Tors — Fri, 14 Feb 2014 15:05:45 +0000

mysql> pager grep sequence;
PAGER set to ‘grep sequence’
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 49 2161386250

You also state that you can get the same information with “show global status”
Innodb_os_log_written | 3067036160

That’s not the same information, in fact its an entirely different number.
I wouldn’t trust this blog, especially since there is no mention of what problems will be caused by changing the logfile size.

By: Andrija

Andrija — Fri, 14 Feb 2014 09:19:26 +0000

Hi, when examining our server (bloated drupal installation with a lot of data), I got over 400MB writen per minute (yes, not an error, this is what Drupal does when to bloated with high number of visits…)

My innodb log size is 256MB right now (well, 2 files of 256M)…Does it make any sense that try 1GB size, or more ? I know recovery time will be much greater…
Thanks

By: Baron

Baron — Fri, 14 Jun 2013 19:05:37 +0000

According to this link, https://mariadb.atlassian.net/browse/MDEV-4662 Jesper is correct and I am wrong: when InnoDB warns about not enough space in the logs, it’s overwritten its last checkpoint. Thanks to Jeremy Cole for finding out the truth.

By: M

Wed, 22 May 2013 18:38:12 +0000

Scratch my previous comment, this is seemingly not repeatable today: (despite the fact that i ran this test several times over the course of ten minutes yesterday, maybe there is some cleanup thread that can do this under certain conditions)

# ls -l /var/lib/mysql/ib_logfile* && date && mysql -BNe’show engine innodb status\G’ | grep sequence && sleep 60 && date && mysql -BNe’show engine innodb status\G’ | grep sequence && ls -l /var/lib/mysql/ib_logfile*
-rw-rw—- 1 mysql mysql 536870912 May 22 13:33 /var/lib/mysql/ib_logfile0
-rw-rw—- 1 mysql mysql 536870912 May 22 12:55 /var/lib/mysql/ib_logfile1
Wed May 22 13:34:00 CDT 2013
Log sequence number 18342929533
Wed May 22 13:35:00 CDT 2013
Log sequence number 18343465682
-rw-rw—- 1 mysql mysql 536870912 May 22 13:35 /var/lib/mysql/ib_logfile0
-rw-rw—- 1 mysql mysql 536870912 May 22 12:55 /var/lib/mysql/ib_logfile1

By: M

Wed, 22 May 2013 03:31:54 +0000

It seems that in 5.5 the second log file is touched even if a rotation has not occurred, that is to say that the ls -l approach is misleading.

Sample:

# ls -l /var/lib/mysql/ib_logfile* && date && mysql -BNe’show engine innodb status\G’ | grep sequence && sleep 60 && date && mysql -BNe’show engine innodb status\G’ | grep sequence && ls -l /var/lib/mysql/ib_logfile*
-rw-rw—- 1 mysql mysql 536870912 May 21 22:23 /var/lib/mysql/ib_logfile0
-rw-rw—- 1 mysql mysql 536870912 May 21 22:23 /var/lib/mysql/ib_logfile1
Tue May 21 22:23:15 CDT 2013
Log sequence number 17750417191
Tue May 21 22:24:15 CDT 2013
Log sequence number 17750981011
-rw-rw—- 1 mysql mysql 536870912 May 21 22:24 /var/lib/mysql/ib_logfile0
-rw-rw—- 1 mysql mysql 536870912 May 21 22:24 /var/lib/mysql/ib_logfile1

Of course it is worth reiterating the point made by AFTAB, crash recovery is far cheaper in modern versions of InnoDB:

https://blogs.oracle.com/mysqlinnodb/entry/innodb_recovery_is_now_faster

And from the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-recovery.html

“If you kept the size of your redo log files artificially low because recovery took a long time, you can consider increasing the file size.”

By: Jayaram

Jayaram — Wed, 28 Nov 2012 12:30:20 +0000

hello Sheeri K. Cabral s.

My log files are looking below

ls -lrth ib_logfile*
-rw-rw—-. 1 mysql mysql 256M Nov 28 12:26 ib_logfile0
-rw-rw—-. 1 mysql mysql 256M Nov 28 12:26 ib_logfile1

Shall I need to split the log files

By: Bro

Bro — Fri, 13 Jul 2012 13:54:15 +0000

Tien Phan, I just come from ZaZhopinsk. How do I install mysql on my Pentium Pro 133Mhz database server?

By: Tien Phan

Tien Phan — Wed, 02 May 2012 02:54:06 +0000

Hi!

I come from Viet Nam. Now i want to check capacity (free space & used space) of datafile on InnoDB (MySQL). How to command?
I just research for MySQL, hope you help me. Big thanks!