4 ms·

Can you explain why turning up innodb_log_file_size is good?

• ryanworl 9y ago
  https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/ https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb... Not who you're responding to, but this is a good overview.
  • falcolas 9y ago
    A good overview indeed; now imagine the figures on the right are 128mb instead of 2gb, and the attendant costs/times scaled up accordingly.
• falcolas 9y ago
  In short, the default is 128MB, which results in block-the-world dirty page flushes when it fills due to write activity. By going to 2G (I frequently recommend 15 when you have a lot of memory), you can buffer a lot more writes during times of peak load, and the idle flushing of dirty pages can generally keep up. The practical effect of changing this value: a 2xlarge RDS host goes from 85% CPU utilization to 10%, with faster response times, for a moderate write load. The downside, and why it's not the default for RDS, is that it eats your RDS storage by 2x the value set.
  • bulldoa 9y ago
    ah that makes sense, is there a innodb_log_file_size equivalent in postgres btw?
    • anarazel 9y ago
      Yes. Before 9.5 it was named checkpoint_segments, now it's called min_wal_size (reserved, won't go below) and max_wal_size (soft max space limit, will checkpoint more frequently).