InnoDB configuration parameter that can improve mysql performance
- 2020-05-10 23:01:46
- OfStack
Since InnoDB is a robust transactional storage engine, it has a history of more than 10 years, and is often used by some heavyweight Internet companies (Yahoo,Google, Netease, Taobao)
I often contact InnoDB in my daily work. Now I will list the parameters that can improve the performance of InnoDB1
1. innodb_additional_mem_pool_size
In addition to caching table data and indexes, you can increase InnoDB's performance by allocating caches for other internal items required for operations. The memory can be allocated with this parameter. It is recommended that this parameter be set to at least 2MB, in fact, depending on the number of InnoDB tables in the project
2.innodb_data_pool_size
This parameter is similar to the key_buffer parameter of MySQL, but is specific to the InnoDB table. This parameter determines how much memory is reserved to cache table data and indexes. As with key_buffer1, higher Settings can improve performance and can be 70-80% of a server's memory
3.innodb_data_file_path
The name of the parameter is a bit different from what it actually does. It 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 beyond the initial allocation limit. The 1 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 sales with an initial size of 100MB, and you want to automatically increase 8MB (8MB is the default extension size when you specify autoextend) every time the current size limit is reached. However, if you don't 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 added to the scheduled 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 empty 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 specify the common location using innodb_data_home_dir, and then specify the file name using inndo_data_file_path. If these values are not defined, an 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 is the default data for MySQL by default, as specified by the MySQL parameter datadir
5. innodb_file_io_threads
This parameter specifies the number of I/O threads available for the InnoDB table, and MySQL developers recommend setting this parameter to 4 on non-Windows platforms
6. innodb_flush_log_at_trx_commit
If this parameter is set to 1, the log will be written to disk after each committed transaction. To provide performance, you can set it to 0 or 2, but at the risk of losing data in the event of a failure. Setting to 0 means that the transaction log is written to the log file, which is flushed to disk once per second. Setting it to 2 means that the transaction log will be written to the log at commit time, but the log file will be flushed to disk once each time.
7.innodb_log_archive
Since MySQL is currently using its own log files to restore the InnoDB table, this parameter can be set to 0
8.innodb_log_arch_dir
MySQL currently ignores this parameter, but will use it in future versions. For now, 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 of some log files in M. Larger buffers can improve performance, but unexpected failures can cause data to be lost.MySQL developers recommend Settings between 1-8M
10. innodb_log_file_size
This parameter determines the size of the data log file in M. Larger Settings can improve performance, but can also increase the time required to recover the failed database
11.innodb_log_files_in_group
To improve performance, MySQL can loop log files to multiple files. The recommended setting is 3M
12. innodb_log_group_home_dir
This parameter determines the location of the files in the log file group. The number of files in the log file group is determined by innodb_log_files_in_group. This location is set to datadir of MySQL by default
13.innodb_lock_wait_timeout
InnoDB has a built-in deadlock detection mechanism that can cause unfinished transactions to be rolled back. However, if InnoDB USES MyISAM's lock tables statement or a third party transaction engine in conjunction with InnoDB, InnoDB cannot recognize deadlocks. To eliminate this possibility, innodb_lock_wait_timeout can be set to an integer value indicating how long (seconds) MySQL will wait before allowing other transactions to modify the data that is eventually rolled back by the transaction
14.skip-innodb
Enabling this parameter prevents the inclusion of the InnoDB table driver, which is recommended when not using the InnoDB table
I often contact InnoDB in my daily work. Now I will list the parameters that can improve the performance of InnoDB1
1. innodb_additional_mem_pool_size
In addition to caching table data and indexes, you can increase InnoDB's performance by allocating caches for other internal items required for operations. The memory can be allocated with this parameter. It is recommended that this parameter be set to at least 2MB, in fact, depending on the number of InnoDB tables in the project
2.innodb_data_pool_size
This parameter is similar to the key_buffer parameter of MySQL, but is specific to the InnoDB table. This parameter determines how much memory is reserved to cache table data and indexes. As with key_buffer1, higher Settings can improve performance and can be 70-80% of a server's memory
3.innodb_data_file_path
The name of the parameter is a bit different from what it actually does. It 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 beyond the initial allocation limit. The 1 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 sales with an initial size of 100MB, and you want to automatically increase 8MB (8MB is the default extension size when you specify autoextend) every time the current size limit is reached. However, if you don't 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 added to the scheduled 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 empty 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 specify the common location using innodb_data_home_dir, and then specify the file name using inndo_data_file_path. If these values are not defined, an 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 is the default data for MySQL by default, as specified by the MySQL parameter datadir
5. innodb_file_io_threads
This parameter specifies the number of I/O threads available for the InnoDB table, and MySQL developers recommend setting this parameter to 4 on non-Windows platforms
6. innodb_flush_log_at_trx_commit
If this parameter is set to 1, the log will be written to disk after each committed transaction. To provide performance, you can set it to 0 or 2, but at the risk of losing data in the event of a failure. Setting to 0 means that the transaction log is written to the log file, which is flushed to disk once per second. Setting it to 2 means that the transaction log will be written to the log at commit time, but the log file will be flushed to disk once each time.
7.innodb_log_archive
Since MySQL is currently using its own log files to restore the InnoDB table, this parameter can be set to 0
8.innodb_log_arch_dir
MySQL currently ignores this parameter, but will use it in future versions. For now, 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 of some log files in M. Larger buffers can improve performance, but unexpected failures can cause data to be lost.MySQL developers recommend Settings between 1-8M
10. innodb_log_file_size
This parameter determines the size of the data log file in M. Larger Settings can improve performance, but can also increase the time required to recover the failed database
11.innodb_log_files_in_group
To improve performance, MySQL can loop log files to multiple files. The recommended setting is 3M
12. innodb_log_group_home_dir
This parameter determines the location of the files in the log file group. The number of files in the log file group is determined by innodb_log_files_in_group. This location is set to datadir of MySQL by default
13.innodb_lock_wait_timeout
InnoDB has a built-in deadlock detection mechanism that can cause unfinished transactions to be rolled back. However, if InnoDB USES MyISAM's lock tables statement or a third party transaction engine in conjunction with InnoDB, InnoDB cannot recognize deadlocks. To eliminate this possibility, innodb_lock_wait_timeout can be set to an integer value indicating how long (seconds) MySQL will wait before allowing other transactions to modify the data that is eventually rolled back by the transaction
14.skip-innodb
Enabling this parameter prevents the inclusion of the InnoDB table driver, which is recommended when not using the InnoDB table