InnoDB configuration parameters that can improve Mysql performance _mysql

And since InnoDB is a robust transactional storage engine that has a history of more than more than 10 years, some heavyweight internet companies (Yahoo,google NetEase, Taobao) often use

My daily work also often contacts InnoDB, now InnoDB part of the parameters can improve the performance of the enumeration
1. Innodb_additional_mem_pool_size
In addition to caching table data and indexes, you can increase the performance of InnoDB by allocating caching for other internal items that are required for the operation. The memory can be allocated through this parameter. It is recommended that this parameter be set at least to 2MB, in fact, it is necessary to increase accordingly according to the number of InnoDB tables in the project
2.innodb_data_pool_size
This parameter is similar to the Key_buffer parameter for MySQL, but is specific to the InnoDB table. This parameter determines how much memory to reserve to cache table data and indexes. As with Key_buffer, higher settings improve performance, which can be server memory 70-80%
3.innodb_data_file_path
The name of the parameter is somewhat different from the actual use, which specifies not only the path to all InnoDB data files, but also the initial size allocation, the maximum allocation, and whether the file should be increased when the threshold is exceeded. The general format of this parameter is as follows:
Path-to-datafile:size-allocation[:autoextend[:max-size-allocation]]
For example, suppose you want to create a data file of sales with an initial size of 100MB, and you want to automatically increase the 8MB (8MB is the default extension size when you specify Autoextend) each time the current size limit is reached. However, you do not want this file to exceed 1GB, you can use the following configuration:
Innodb_data_home_dir =
Innodb_data_file_path =/DATA/SALES:100M:AUTOEXTEND:8M:MAX:1GB
If this file is increased to a predetermined 1G limit, you can add another data file, as follows:
Innodb_data_file_path =/data/sales:100m:autoextend:8m:max:1gb;innodb_data_file_path =/data2/sales2:100M: Autoextend:8m:max:2gb
Note that in these examples, the Inndb_data_home_dir parameter is initially set to NULL because the final data file is in a separate location (/data/and/data2/). If you want all innodb data files to be in the same location, you can use the Innodb_ Data_home_dir to specify a common location, and then specify the file name by 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 to create the InnoDB tablespace, which by default is the default data for MySQL, specified by the MySQL parameter DataDir
5. Innodb_file_io_threads
This parameter specifies the number of file I/O threads available to the InnoDB table, and the MySQL developer recommends that this parameter be set to 4 in a non-Windows platform
6. Innodb_flush_log_at_trx_commit
If this parameter is set to 1, the log is written to disk each time the transaction is committed. To provide performance, you can set to 0 or 2, but assume the risk of losing data in the event of a failure. A setting of 0 indicates that the transaction log is written to the log file, and the log files are flushed to disk once per second. A set of 2 indicates that the transaction log will be written to the log at the time of submission, but the log file is flushed to disk once.
7.innodb_log_archive
Because MySQL is currently using 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 releases. Currently, it should be set to the same value as Innodb_log_group_home_dir
9.innodb_log_buffer_size
This parameter determines the amount of memory used by some log files, in M. A larger buffer can improve performance, but unexpected failures will lose data. MySQL developer recommends setting to 1-8m
innodb_log_file_size
This parameter determines the size of the data log file, in M, the larger setting can improve performance, but it also increases the time required to recover the failed database
11.innodb_log_files_in_group
To improve performance, MySQL can write log files to multiple files in a circular fashion. Recommended set to 3M
Innodb_log_group_home_dir
This parameter determines the location of the files in the log file group, and the number of files in the log group is determined by Innodb_log_files_in_group, which is set to default to MySQL DataDir
13.innodb_lock_wait_timeout
InnoDB has its built-in deadlock detection mechanism that can cause unfinished transactions to roll back. However, if you combine InnoDB with a MYISAM lock tables statement or a Third-party transaction engine, InnoDB cannot recognize the deadlock. To eliminate this possibility, you can set Innodb_lock_wait_timeout to an integer value indicating how long the MySQL waits (in seconds) before allowing other transactions to modify the data that is eventually rolled back by the transaction.
14.skip-innodb
Enabling this parameter prevents inclusion of InnoDB table drivers and is recommended when not using the InnoDB table