Comments on: How to calculate a good InnoDB log file size – recap https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap Blog by Shlomi Noach Thu, 06 Nov 2014 17:44:44 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: WagnerBianchi.com » Blog Archive » Working with MySQL on SSD https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-287299 Thu, 06 Nov 2014 17:44:44 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-287299 […] was configured used a large value and it was annoying me  a little. After fiding the Shlomi Noach blog entry with a good query to check the size of transactions that populate the log buffer, its seems very […]

]]> By: Rick James https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-121367 Tue, 02 Oct 2012 23:22:31 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-121367 I attended Oracle’s MySQL Connect last weekend. One tidbit I picked up about 5.6 is…
There is less need now to avoid extra-large log files. Apparently “recover” has been made more efficient. Also, log files can now be bigger than 4GB.

More details:
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_log_file_size

No need to every have more than 2 log_files_in_group.

]]> By: McAuley https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-121215 Mon, 01 Oct 2012 20:01:27 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-121215 @Rick James, yes .. I tested your calc on several of our servers and it looks to be a lot more reasonable. Both the “uptime” and the “Innodb_os_log_written” are now part of the INNODB GLOBAL STATUS. My tests using your formula averaged about 42Mb, instead of the 902+ GIG that this other formula rendered.

]]> By: McAuley https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-121213 Mon, 01 Oct 2012 19:26:47 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-121213 Yeah, when I get a result like this:
9464024862720 9025597.4MB
it makes me question the accuracy of this kind of calc. I think looking at the “rollover” of the log files in relation to their size works better for me.

]]> By: Rick James https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-74155 Tue, 06 Mar 2012 22:16:23 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-74155 That’s nice for instantaneous (one minute’s worth), but you might be in the middle of a spike or a lull. Instead, I use this (in newer versions of MySQL):

Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group

That gives a value for the log file size, assuming that rolling over once an hour is a good target.

I question the need for log files bigger than 4GB. I checked 213 machines here, one machine “wants” 5GB, it was the only one over 3GB.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-61283 Fri, 02 Dec 2011 06:11:24 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-61283 @George,
I suppose I would have to live with it 😀
Seriously, Percona Server supports > 4G worth of transaction log files.
Perhaps you can tune you app so as to produce less transactions; i.e. group more statements into transactions (may not reduce overall size, but will help out with flushes), work out smarter queries, take out logging from the database…

]]> By: George https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-61209 Thu, 01 Dec 2011 15:40:02 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-61209 Thanks for the snippet. Very handy.

What would you do if you got this result though? Seems a 36G log file might be a bad thing to have.

+——————————————+———————————————+
| estimated_innodb_os_log_written_per_hour | estimated_innodb_os_log_written_per_hour_mb |
+——————————————+———————————————+
| 38325872640 | 36550.4MB |
+——————————————+———————————————+

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-26236 Wed, 05 Jan 2011 05:24:10 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-26236 My best guess is that you’re NOT using 5.1. Perhaps just the 5.1 client.

Try:
mysql> SELECT VERSION();

]]> By: sonny https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-26189 Tue, 04 Jan 2011 21:13:52 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-26189 GLOBAL_STATUS table is not exist in INFORMATION_SCHEMA database. using 5.1. Am I mising something…

Pls help.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap/comment-page-1#comment-5061 Wed, 21 Oct 2009 04:19:54 +0000 https://shlomi-noach.github.io/blog/?p=895#comment-5061 Hi Arjen,

Thanks. I have an awk ‘pager’ for that, but it’s really ugly…

]]>