InnoDB configuration parameters that can improve mysql Performance

As InnoDB is a robust transactional storage engine that has been used for more than 10 years, some heavyweight Internet companies (Yahoo, Google Netease, Taobao) are also frequently used.

I often use InnoDB in my daily work. Now I will list some parameters that can improve the performance of InnoDB.
1. innodb_additional_mem_pool_size
In addition to caching table data and indexes, you can allocate cache for other internal items required for the operation to improve InnoDB performance. These memories can be allocated through this parameter. We recommend that you set this parameter to at least 2 MB. In fact, you need to increase the number of InnoDB tables of the Project accordingly.
2. innodb_data_pool_size
This parameter is similar to MySQL's key_buffer parameter, but is used in InnoDB tables. This parameter determines how much memory is reserved to cache table data and indexes. Like key_buffer, higher settings will improve performance, which can be 70-80% of the server's memory.
3. innodb_data_file_path
The parameter name differs from the actual usage. It not only specifies the path of all InnoDB data files, but also specifies the initial size allocation, specifies whether to increase the file size when the maximum allocation and the threshold are exceeded. The general format of this parameter is as follows:
Path-to-datafile: size-allocation [: autoextend [: max-size-allocation]
For example, if you want to create a data file sales, the initial size is 100 MB, and you want to reach the current size limit each time, automatically increase by 8 MB (8 MB is the default extended size when autoextend is specified ). however, if you do not want the file to exceed 1 GB, you can use the following Configuration:
Innodb_data_home_dir =
Innodb_data_file_path =/data/sales: 100 M: autoextend: 8 M: max: 1 GB
If this file is limited to 1 GB, you can add another data file, as shown below:
Innodb_data_file_path =/data/sales: 100 M: autoextend: 8 M: max: 1 GB; innodb_data_file_path =/data2/sales2: 100 M: autoextend: 8 M: max: 2 GB
In these examples, the inndb_data_home_dir parameter is set to null because the final data file is located in a separate location (/data/AND/data2 /). if you want all InnoDB data files to be in the same location, you can use innodb_data_home_dir to specify the common location, and then specify the file name through inndo_data_file_path. If these values are not defined, a sales is created in datadir.
4 innodb_data_home_dir
This parameter specifies the public part of the path for creating the InnoDB tablespace. By default, this is the default data of MySQL, which is specified by the MySQL parameter datadir.
5. innodb_file_io_threads
This parameter specifies the number of file I/O threads available in the InnoDB table. MySQL developers recommend that you set this parameter to 4 on non-Windows platforms.
6. innodb_flush_log_at_trx_commit
If this parameter is set to 1, logs are written to the disk after each transaction is committed. To provide performance, it can be set to 0 or 2, but it must bear the risk of data loss in the event of a fault. 0 indicates that the transaction log is written to the log file, and the log file is refreshed to the disk once per second. 2 indicates that the transaction log is written at the time of submission, but the log file is refreshed to the disk every time.
7. innodb_log_archive
Because MySQL currently uses its own log file to restore the InnoDB table, this parameter can be set to 0
8. innodb_log_arch_dir
MySQL currently ignores this parameter, but it will be used in future versions. Currently, it should be set to the same value as innodb_log_group_home_dir
9. innodb_log_buffer_size
This parameter determines the memory size used by some log files, in MB. A larger buffer zone can improve performance, but unexpected faults will cause data loss. We recommend that you set this parameter to 1-8 MB for MySQL developers.
10. innodb_log_file_size
This parameter determines the size of the data log file, in MB. Larger settings can improve performance, but also increase the time required to recover the faulty database.
11. innodb_log_files_in_group
To improve performance, MySQL can write log files to multiple files cyclically. Recommended to 3 M
12. innodb_log_group_home_dir
This parameter determines the location of files in the log file group. The number of files in the log group is determined by innodb_log_files_in_group. The default location is MySQL datadir.
13. innodb_lock_wait_timeout
InnoDB has its built-in Deadlock Detection Mechanism, which can cause unfinished transaction rollback. However, if InnoDB is used with the lock tables Statement of MyISAM or a third-party transaction engine, InnoDB cannot identify the deadlock. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer indicating how long MySQL will wait before allowing other transactions to modify the data that is eventually rolled back by the transaction (in seconds)
14. skip-innodb
Enabling this parameter can prevent inclusion of InnoDB table drivers. We recommend this setting when InnoDB tables are not used.