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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
3
  • I suppose it also really depends upon the workload. Our workload is almost exclusively "appends" and lots of reads, with a few updates here and there. We have arranged for our biggest tables (most records) to be nearly exclusively append-only. I'm not sure if that changes anything. Thanks for the Percona reference.
    – Christopher Schultz
    Feb 13, 2018 at 14:37
  • Regardless of the type of updates you are doing, InnoDB will write everything to the transaction log first for durability, and over time move pages out to the actual data files. The size of the transaction log is the amount of liberty you give InnoDB to moves those pages out when it sees fit. Unless you are concerned with 1) disk space consumed by a large log 2) recovery time in the event of a crash or 3) have a modest # of write transactions, then 10GB+ for a transaction log is perfectly reasonable for a 64GB server.
    – atomic77
    Feb 14, 2018 at 3:59
  • Okay. Unfortunately for me, I'm still on MySQL 5.5 for my master db, which means changing the log file size requires a few more steps. I'd just like to plan for me future :)
    – Christopher Schultz
    Feb 14, 2018 at 13:46
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.

Improve this answer
2
  • Dumb question... how can I determine when the file is rotated? I'm still at the (surely way-too-low) default file size of 5MiB and I believe I'm seeing it rotated more than once per minute. Is there a way to be sure of that rollover schedule, or do I just have to look at the output of stat and watch for a timestamp change. One file should constantly be written-to while the other is stagnant, right?
    – Christopher Schultz
    Feb 18, 2018 at 3:10
  • @ChristopherSchultz - The above computation takes that into account. I don't know of a way to get a timestamp -- perhaps the time on the files iblog*? 5M is too small. Hardly anyone goes about 4GB.
    – Rick James
    Feb 18, 2018 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.