MySQL Server parameter--innodb engine

MySQL server parameters are many, if you want to see the specific meaning of a parameter, you can use mysqld--verbose--help | Grep

The parameters in the InnoDB storage engine start with InnoDB:

Innodb_buffer_pool_size:

This parameter defines the maximum memory buffer size for the table and index data of the InnoDB storage engine, and unlike the MyISAM storage engine, MyISAM's key_buffer_size caches only the index keys, and Innodb_buffer_pool_ Size is also for the data Block and index block cache, this feature is the same as Oracle, the higher the value, the less disk IO required to access the data in the table, on a dedicated database server, you can set this parameter to the size of the machine memory 80%. However, it is not recommended to set it too large because the competition for memory in the object may cause memory scheduling on the operating system.

Innodb_flush_log_at_trx_commit:

Innodb_additional_mem_pool_size:

This parameter is the size of the memory pool used by the InnoDB storage engine to store the database structure and other internal data structures, with a default value of 1M and the more application tables, the more memory needs to be allocated here. If InnoDB runs out of memory in the pool, InnoDB starts allocating memory from the operating system and writes a warning message to the MySQL error log. There is no need to allocate very large space for this buffer, and the buffer pool size is relatively stable in the case of relatively stable application.

Innodb_lock_wait_timeout:

MySQL can automatically monitor the deadlock caused by the row lock and handle the corresponding, but the deadlock caused by the table lock can not be automatically monitored, so this parameter is mainly used to wait for a specified time to roll back after a similar situation. The system defaults to 50 seconds.

Innodb_support_xa:

This parameter sets whether distributed transactions are supported, and the default value is on or 1, which means that distributed transactions are supported. If you confirm that the application does not require the use of distributed transactions, you can turn off this parameter, reduce the number of disk refreshes, and get better innodb performance.

Innodb_log_buffer_size:

This parameter is the size of the log cache, and the default setting is generally sufficient to meet server performance requirements in the case of moderate-intensity write loads and shorter transactions. If there is a peak update operation or a large load, you should consider increasing its value. If the setting is too high, memory is wasted because it refreshes every second, so there is no need to set the memory space required for more than a second. Usually set to 8~16MB is sufficient. The smaller the system, the smaller the value. The default is 8M.

Innodb_log_file_size:

This parameter means the size of each log file in a log group. This parameter is important in the case of high write loads, especially large data sets, the higher the value, the higher the performance, but the side effect is that the recovery time increases when a system disaster occurs. 5.5 Default is 5mb,5.6 default is 50Mb.

MySQL Server parameter--innodb engine