3

In many answers, discussions, and comments, the prevailing wisdom seems to be to "set innodb_log_file_size to be 25% of the inndb_buffer_pool_size.

What about fairly large buffer pools? For example, I have a server with 64GiB of memory and my buffer pool is currently 38GiB. 25% of that is 9.5GiB. What are the risks of using a "large" innodb_log_file_size versus leaving it "too small"?

With 64GiB available, I'm likely to increase my innodb_buffer_pool_size to something greater than 40GiB, possibly higher. Something makes me nervous about a 10GiB+ log file size. Am I needlessly worrying?

Christopher Schultz
  • 327
  • 4
  • 14

2 Answers2

1

You have to find the right tradeoff between recovery time in the event of a crash and how much write throughput your database will be expected to support.

Percona has a great post discussing a similar scenario to yours, with a 64GB buffer pool, and about 2/5ths of the way into the article you can see a nice comparison he does between a 2GB and 15GB logfile.

15GB allows innodb enough log space to 'smoothly' flush pages out of the log, whereas with 2GB there is more pressure to keep the log from filling up, which ultimately hurts performance as pages have to be evicted. In the benchmarks they cite, performance is 20% lower as a result.

TL;DR you can't really go wrong with a larger log file, esp. if you have a slave to failover to in the event of a crash, but you can certainly hurt performance with one that is too small.

atomic77
  • 66
  • 4
1

Short answer The value of innodb_log_file_size does not matter much for most servers.

Long answer

Benchmarks tend to stress the server to the limit. If your application is stressed that much, it will keel over.

A better measure is

Uptime / 60 * innodb_log_file_size / Innodb_os_log_written

should be about 60 (minutes between log rotations). Flipping it around, this gives you a rough estimate of a good for innodb_log_file_size:

Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group

If you are off by a factor of 2 (or even 5), it does not matter much.

Rick James
  • 80,479
  • 5
  • 52
  • 119