[MySQL] InnoDB parameter optimization

Innodb_buffer_pool_size

The innodb_buffer_pool_size parameter is used to set the size of InnoDB's primary buffer (Innodb_buffer_pool), which is the most significant cache space for cached user tables and index data, and has the greatest impact on InnoDB overall performance.

For a single host used for MySQL, and assuming that only the InnoDB engine is used, it is generally recommended that this parameter is about 75% of the logistics memory.

Once the system is online, we can further analyze the real-time status of the buffer pool provided by the InnoDB storage engine to determine whether the InnoDB buffer pool usage in the system is normal and efficient:

Mysql> Show status like ' innodb_buffer_pool_% '; +-----------------------------------------+---------------+| variable_name | Value |+-----------------------------------------+---------------+| Innodb_buffer_pool_pages_data | 999020 | | Innodb_buffer_pool_pages_dirty | 47643 | | innodb_buffer_pool_pages_flushed | 474668167 | | innodb_buffer_pool_pages_lru_flushed | 365125 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_made_not_young | 0 | | Innodb_buffer_pool_pages_made_young | 203410903 | | Innodb_buffer_pool_pages_misc | 49552 | | Innodb_buffer_pool_pages_old | 368697 | | Innodb_buffer_pool_pages_total | 1048572 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 66348855 | | innodb_buffer_pool_read_ahead_evicted |  3716819     | | innodb_buffer_pool_read_requests | 3215992991498 | | Innodb_buffer_pool_reads | 65634998 | | Innodb_buffer_pool_wait_free | 651 | | innodb_buffer_pool_write_requests | 21900970785 | +-----------------------------------------+---------------+

From the above values we can see a total of 1,048,572 pages, where the data has 999,020 pages, and there is no page with the Free State.
Read Requests 3,215,992,991,498 times, of which 65,634,998 times the requested data is not in the Buffer pool, that is to say 65,634,998 times by reading the physical disk to read the data, so it is easy to draw the InnoDB Buffer pool The read hit rate is about: (3215992991498-65634998)/3215992991498* 100% = 99.998%.

Innodb_buffer_pool_instances

This parameter divides the Innodb_buffer_pool into different instance, each instance independent LRU, FLUSH, free, independent mutex control.

For larger innodb_buffer_pool_size, it is recommended to set multiple instances to avoid contention for memory locks.

Innodb_log_file_size

Setting the size of the InnoDB redo log file, from a performance standpoint, the larger the log files, the better the buffer pool checkpoint can be reduced, but in the official version of MySQL, innodb_log_files_in_group* Innodb_log_files_in_group cannot exceed 4G.

The larger the log file, the longer it will take to recover the MySQL instance after crash, but the general generation system will configure the master-slave library, so this factor can be ignored.

In general, in my personal maintenance environment, the comparison is biased to set the transaction log to 3 groups, each log is set to 256MB size, the overall effect is good.

Innodb_log_buffer_size

As the name implies, this parameter is used to set the InnoDB log Buffer size, the system default is 1MB. The main function of log buffer is to buffer log data and improve the IO performance of write log. In general, if your system is not "write load is very high and large transaction majority", the size within 8MB is completely enough.

We can also use the performance statistics provided by the System State parameter to analyze log usage:

Mysql> Show status like ' innodb_log% '; +---------------------------+------------+| Variable_name             | Value      |+---------------------------+------------+| Innodb_log_waits          | 0          | | innodb_log_write_requests | 3486920147 | | Innodb_log_writes         | 352577360  | +---------------------------+------------+

If innodb_log_waits is not equal to 0, it indicates that a write wait has occurred in log buffer, indicating that innodb_log_buffer_size may be too small.

Innodb_thread_concurrency

This parameter represents InnoDB maximum thread concurrency, the official recommendation is set to 0, indicated by the InnoDB own control, but the practice proves that when the concurrency is too large, InnoDB himself will control improper, may lead to MySQL hang dead, so generally recommended CPU core number (without Hyper-threading)

Innodb_io_capacity

Indicates that the IO device handles the upper limit of the data page per second, and if the hard disk performance is better, it can be larger (such as 1000).

innodb_max_dirty_pages_pct

Indicates that InnoDB does not exceed this value for flushing dirty pages from buffer, each time the dirty page of checkpoint is refreshed as: innodb_max_dirty_pages_pct*innodb_io_capacity

Innodb_flush_method

Used to set the way InnoDB opens and synchronizes data files and log files, but only works on Linux & Unix systems. When we set it to O_dsync, the system opens and refreshes the log file in O_sync mode, opening and refreshing the data file via Fsync (). When set to O_direct, the data file is opened by O_direct (Directio () on Solaris) and the data and log files are refreshed with Fsync ().
In general, the different settings of Innodb_flush_method primarily affect the difference in operating system IO excuses invoked by the InnoDB when IO operations are performed under different operating platforms. and the different IO operation interface has certain difference to the data processing way, therefore the processing performance also has the certain difference. In general, if our disk is hardware-level raid through a RAID card, it is recommended that you use O_direct to improve IO performance to some extent, but you still need to be cautious if the raid Cache is not enough.

Innodb_file_per_table

It is generally recommended to turn on because different table spaces can flexibly set the address of the data directory, avoiding the IO competition generated by shared tablespace.

Innodb_flush_log_at_trx_commit

Innodb_flush_log_at_trx_commit = log Thread in 0,innodb writes data from log buffer to a file every 1 seconds, and notifies the file system to flush operations for file synchronization. Ensure that the data is actually written to the physical file above the disk. However, the end of each transaction (commit or rollback) does not trigger the log Thread to write the data in log buffer to the file. Therefore, when set to 0, when MySQL Crash and OS Crash or host power outage, the most extreme situation is the loss of 1 seconds of data changes.

Innodb_flush_log_at_trx_commit = 1, which is also the default setting for InnoDB. The log Thread is fired at the end of each transaction to write the data in log buffer to the file and notify the file system to synchronize the files. This setting is the safest setting to ensure that no data has been submitted, whether it is a MySQL Crash or OS Crash or a host power loss.

Innodb_flush_log_at_trx_commit = 2, when we set it to 2, the log Thread writes the data to the transaction log at the end of each transaction, but the write here is simply a file-write operation that invokes the file system. And our filesystem has a caching mechanism, so this write to the log Thread does not guarantee that the content is actually written to the physical disk to complete the persisted action. When will the file system synchronize this data in the cache to the physical Disk file log Thread is completely unaware. So, when set to 2, MySQL Crash does not cause data loss, but OS Crash or the amount of data that may be lost after a host power outage is fully controlled on the file system.

From the above analysis, we can see that when the Innodb_flush_log_at_trx_commit is set to 1 is the safest, but because the IO synchronization operation is also the most, so performance is the worst of the three kinds of settings. If set to 0, there is one synchronization per second, with a relatively high performance. If set to 2, it is possible that performance is the best of the three. However, it is also possible that the most data loss occurs after crash. In the end how to set the settings, you will be based on specific scenarios to analyze. In general, if the loss of data is completely unacceptable, then we will definitely sacrifice some performance for the security of the data, and choose Set to 1. And if we can lose a very small amount of data (say, within 1 seconds), then we can set it to 0. Of course, if you think our OS is stable enough, the host hardware, and the host's power supply is also safe enough, we can also set the Innodb_flush_log_at_trx_commit to 2 so that the overall performance of the system as high as possible.

Transaction-isolation

For high concurrency applications, in order to ensure the consistency of the data as much as possible, to avoid concurrency may result in inconsistent data problems, the higher the transaction isolation level, the better. However, for InnoDB, the higher the level of transaction isolation used, the greater the complexity will naturally be, the more things you need to do, and the worse your overall performance will be.

Therefore, we need to analyze the logic of our own application system and choose the minimum transaction isolation level that can be accepted. To achieve the highest performance while ensuring data security consistency.
Although the default transaction isolation level for the INNODB storage engine is repeatable read, in fact most of our scenarios require only the transaction isolation level of read commited to meet the requirements.

Sync_binlog

Represents the number of Binlog to disk per flush.

For the core system, we need to adopt the dual 1 mode, namely: Innodb_flush_log_at_trx_commit=1, sync_binlog=1, so as to ensure that the main repository data consistent, no data loss.

[MySQL] InnoDB parameter optimization