Optimizing InnoDB Configuration Variables •Increase I/O capacity to avoid backlogs If throughput drops periodically because of  InnoDB  checkpoint operations, consider increasing the value of the  innodb_io_capacity  configuration option. Higher values cause more frequent flushing, avoiding the backlog of work that can cause dips in throughput. •Lower I/O capacity if flushing does not fall behind If the system is not falling behind with  InnoDB  flushing operations, consider lowering the value of the innodb_io_capacity  configuration option. Typically, you keep this option value as low as practical, but not so low that it causes periodic drops in throughput as mentioned in the preceding bullet. In a typical scenario where you could lower the option value, you might see a combination like this in the output from  SHOW ENGINE INNODB STATUS : •History list length low, below a few thousand. •Insert buffer merges close to rows inserted. •Modified pages in buffer pool consistently well below  innodb_max_dirty_pages_pct  of the buffer pool. (Measure at a time when the server is not doing bulk inserts; it is normal during bulk inserts for the modified pages percentage to rise significantly.) • Log sequence number - Last checkpoint  is at less than 7/8 or ideally less than 6/8 of the total size of the  InnoDB  log files. •Store system tablespace files on Fusion-io devices You can take advantage of a doublewrite buffer-related I/O optimization by storing the files that contain the doublewrite storage area on Fusion-io devices that support atomic writes. (Prior to MySQL 8.0.20, the doublewrite buffer storage are resides in the system tablespace data files. As of MySQL 8.0.20, the storage area resides in doublewrite files. See Section 15.6.4, “Doublewrite Buffer” .) When doublewrite storage area files are placed on Fusion-io devices that support atomic writes, the doublewrite buffer is automatically disabled and Fusion-io atomic writes are used for all data files. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an  innodb_flush_method  setting of  O_DIRECT  is recommended. Note Because the doublewrite buffer setting is global, the doublewrite buffer is also disabled for data files that do not reside on Fusion-io hardware. •Disable logging of compressed pages When using the  InnoDB  table compression feature, images of re-compressed pages are written to the redo log when changes are made to compressed data. This behavior is controlled by innodb_log_compressed_pages , which is enabled by default to prevent corruption that can occur if a different version of the  zlib  compression algorithm is used during recovery. If you are certain that the zlib  version is not subject to change, disable  innodb_log_compressed_pages  to reduce redo log generation for workloads that modify compressed data. 8.5.9 Optimizing InnoDB Configuration Variables Different settings work best for servers with light, predictable loads, versus servers that are running near full capacity all the time, or that experience spikes of high activity. Because the  InnoDB  storage engine performs many of its optimizations automatically, many performance- tuning tasks involve monitoring to ensure that the database is performing well, and changing configuration 1755