Increasing innodb_log_file_size

Increasing innodb_log_file_size: "We have servers that run with innodb_log_file_size=256M and some of these servers do a lot of disk writes per second. I want to know whether performance will improve with a larger value for innodb_log_file_size and setup two test servers that used 256M and 512M for it and then ran a mirror of the production workload on them.

The results are interesting. The benefit varies from significant to not much depending on how you measure. With this change the write rate was reduced:

• 4.5% as measured by iostat w/s
  
• 13% as measured by iostat wsec/s
  
• 18% as measured by Innodb pages written.
  

I then added the my.cnf variable innodb_flush_neighbors_on_checkpoint to MySQL. There are several conditions under which InnoDB writes dirty pages. One reason for pages to be flushed is page preflush which is done to enforce the fuzzy checkpoint constraint. The oldest LSN for a dirty page must not be too close to the start of the current group of log files. InnoDB submits async write requests to enforce this. The Facebook MySQL patch adds statistics to SHOW INNODB STATUS that reports on the source of page writes and for my servers page preflush is the common cause. Other causes are too many dirty pages and moving pages from the LRU to the free list.

When a dirty page is to be written, InnoDB submits async write requests for that page and all other dirty pages from the same extent. This is done to reduce the disk seek overhead. This will also increase the rate at which pages are written to disk. In the case of page preflush, the impact can be significant. When there are too many dirty pages or not enough pages on the free list, InnoDB submits a fixed number of write requests. But when page preflush is done, InnoDB must submit async write requests for all pages with a modified LSN that is too small. So the extra writes done for pages in the same extent can lead to a large number of write requests.

With a modified binary I set skip_innodb_flush_neighbors_on_checkpoint for the server using innodb_log_file_size=512M. With this change the write rate was reduced:

• 2% as measured by iostat w/s
  
• 18% as measured by iostat wsec/s
  
• 26% as measured by Innodb pages written.
  

I don't know why the iostat rate for w/s was not reduced more when using skip_innodb_flush_neighbors_on_checkpoint.

The setting skip_innodb_flush_neighbors_on_checkpoint is also likely to be useful when flash is used, but that is another discussion.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"