MySQL 5.6 INNODB Storage Engine Parameters Accepted

Innodb_read_io_threads=8
Innodb_write_io_threads=8

This parameter can improve performance on Linux platforms based on further changes in CPU cores, and more read operations can increase the number of read_io_threads. You can see it in the file I/O section via show engine InnoDB Status\g, as follows:

FILE I/O
--------
I/O thread 0 state:waiting for completed AIO requests (insert buffer thread)
I/O thread 1 state:waiting for completed AIO requests (log thread)
I/O thread 2 state:waiting for completed AIO requests (read thread)
I/O thread 3 state:waiting for completed AIO requests (read thread)
I/O thread 4 state:waiting for completed AIO requests (read thread)
I/O thread 5 state:waiting for completed AIO requests (read thread)
I/O thread 6 state:waiting for completed AIO requests (read thread)
I/O thread 7 state:waiting for completed AIO requests (read thread)
I/O thread 8 state:waiting for completed AIO requests (read thread)
I/O thread 9 state:waiting for completed AIO requests (read thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)
I/O thread state:waiting for completed AIO requests (write thread)

Improved IO processing capabilities:

innodb_io_capacity=200

This parameter indicates how many dirty data will be written into the hard disk at one time, the normal single SATA can be set to 200, for the SSD can be set 3000, and the 5 disk composed of RAID5 can be set to 2000.

Added Adaptive Refresh Dirty page feature:

Innodb_adaptive_flushing

Since this parameter is on by default, it does not need to be set in MY.CNF.

mysql brushes the dirty pages in the following three scenarios:

1. When more than innodb_max_dirty_pages_pct set Value
   

2. Redo Log Ib_logfile file full

3. When the machine is idle

About innodb_max_dirty_pages_pct This parameter is recommended between 75-80, because if the set too large memory is also very large or the server pressure is very large (the database crashes when the recovery time increases, can improve performance), then the efficiency is very low, if the value set is too small, Then the pressure on the hard drive will increase (the crash recovery time is reduced, but the performance is reduced).

Can be viewed through show engine InnoDB status\g

Buffer Pool Size 65532
Buffer pool size, bytes 1073676288

The buffer pool sizex16x1024 equals buffer pool size, bytes, (buffersize) and then the number of modified DB pages multiplied by 16 and then 1024 (modiffiedsize), the MO Diffiedsize/buffersize * 100, find out if this number is greater than 20%, and then decrease the parameter value to reduce modified DB pages (this is the number of dirty pages), dynamic settings:

Set globalinnodb_max_dirty_pages_pct=5;

(But the individual thinks it will be affected by the innodb_adaptive_flushing parameter)

The innodb_buffer_pool_instance parameter can increase the number of buffer pool, but should be used when innodb_buffer_pool_size is greater than 1G. Inside the Percona, the innodb_buffer_pool_instances default should be 8. This is a little different from MySQL in the community. (Recommended twice times the number of CPU cores to adjust)

InnoDB Adaptive Hash Index, can be closed by skip_innodb_adaptive_hash_index, but it is not recommended to close if the single table size is smaller than the memory size

Improve memory allocation performance with Tcmalloc

Download Google-perftools 1.9 Compile and install:

./configure--enable-frame-pointers

Make && make install

To modify the/usr/local/mysql/bin/mysqld_safe file, add the following line to row 13th:

Export ld_preload=/usr/local/lib/libtcmalloc.so

Restart MySQL

innodb_purge_threads Default is 1, separate purge thread

Innodb_change_buffering with default All

Innodb_stats_on_metadata

InnoDB Update Metadata Statistics function, it is recommended to close, in fact, do not close will not have much impact.

Innodb_stats_on_metadata=off

Percona is off by default.

Innodb_strict_mode Strict Check mode, this parameter only prevents the problem from happening, does not have any effect to the performance.

This article is from the "Technical Blog" blog, please be sure to keep this source http://raytech.blog.51cto.com/7602157/1701814

MySQL 5.6 INNODB Storage Engine Parameters Accepted