"Original" MySQL performance optimization-i/o related configuration parameters

This article describes the I/O-related parameter configurations for the two storage engines of the InnoDB and MyISAM.

1.InnoDB I/O related configuration

InnoDB is a transactional storage engine that, in order to reduce the IO overhead incurred when committing transactions, InnoDB uses a write log, that is, when a transaction commits, it is written to the transaction log, not every time the modification or data is flushed to the data file, in order to improve the performance of the IO. Because transactions are modified so that data and index files are usually mapped to a random location in the tablespace, refreshing the data changes to the data file generates a lot of random io, while the logging is sequential Io, and once the transaction log is securely written to disk, the data is persisted even if the change has not yet been written to the data file. If the server goes down at this time, we can still use the transaction log to recover the committed transaction. The following are some of the configuration parameters related to the InnoDB storage engine:

Innodb_log_file_size: Control the size of a single transaction log file (if the business is busy can be set larger, you can generally record the business one hours to generate a transaction log);

Innodb_log_files_in_group: The number of control transaction log files;

Instead of writing each commit directly to the log file, the transaction log is written to the transaction log buffer before being flushed to disk. The innodb_log_buffer_size controls the size of the transaction log buffers and usually does not need to set the log buffer very large, because in general, at least one second, the transaction log cache will be refreshed, so that the buffer as long as the transaction can be kept for at least one second is sufficient, Of course, a little larger is not a disadvantage, usually 32m~128m such a configuration is possible.

Therefore, the total size of the transaction log = Innodb_log_files_in_group * innodb_log_file_size.

Innodb_flush_log_at_trx_commit the log to disk configuration, there are three options:

(1) 0: Log writes once per second to the operating system cache and flush log to disk (does not do anything when the transaction commits, this setting will lose at least one second of the transaction when MySQL crashes);

(2) 1[default]: Execute log write cache on each transaction commit, and FULSH log to disk (this is the safest setting, no transaction is lost, but performance is not ideal);

(3) 2[recommendation]: Each transaction commits, the execution log data is written to the cache, each second executes flush log to disk. (The completion of a transaction persistence is divided into two steps: first from the MySQL log buffer to the operating system cache, and then from the operating system cache to disk);

2.MyISAM I/O related configuration

MyISAM is also a common storage engine, MySQL related system table is the use of MyISAM storage engine, MyISAM I/O related parameters are configured as follows:

Delay_key_write:off flushes the dirty blocks in the key buffer to the disk after each write operation;

On use deferred refresh only for tables that have the Delay_key_write option specified in the key table;

All of the MyISAM tables are written using the delay key;

"Original" MySQL performance optimization-i/o related configuration parameters