MySQL Storage Engine Tuning parameters

MySQL configuration parameter Optimization

This article comes from Dawson study notes, copyright http://wubx.net/All

MyISAM Storage Engine Optimization

The parameters involved are as follows:

Key_buffery_size

Concurrent_insert = 2 | Waays

Bulk_insert_buffer_size=8m

Myisam_recover_options=force

Myisam_recover_threads=1

myisam_sort_buffer_size=1g

Parameter explanation:

key_buffery_size      

It is mainly used to store the index information of the MyISAM table, and there is a special IO scheduling algorithm that will flush out the buffer if it is uncertain.

# in the MySQL 5.6 version of the default engine tuning in order to InnoDB, the temp table default engine is also the same, so the MyISAM engine basically adjusts key_buffer_size, but it can't be banned, because most of the MySQL library is MyISAM now.

Only a few tables are InnoDB, so many of them are MyISAM engines that can't be banned.

But for the 5.6 release, key_buffer_size to 8 m can be

bulk_inser_buffer_size = 8M ( default)

In the higher version of the default 8M, the low version of the default or 4M; So if the small can be changed a little bit, this value is usually the general SQL:

INSERT into TB (C1,C2,C3) VALUES (), () ....;

If the configuration is 1M, then insert executes 4M of data, so that will be an error, the default is not adjusted, but if you do the data collection and other scenarios, you need to increase it

For the MyISAM table may be broken, such as the situation, this can be the following several parameters to fight:

Myisam_recover_options=force

Myisam_recover_threads=1

myisam_sort_buffer_size=1g

This will be repaired automatically and is a fix for the MyISAM table

This article comes from Dawson study notes, copyright http://wubx.net/All

However, if you use Myisam_recover_options=force, you are likely to lose data because the MyISAM table does not guarantee data consistency, so losing data is not possible, so if the business data is very important, the security requirements are high, So try not to use MyISAM.

Since 5.5 The official will not do any upgrade and maintenance to MyISAM; After 5.6 The default engine is InnoDB. The temp table is also changed to InnoDB

This article comes from Dawson study notes, copyright http://wubx.net/All

InnoDB Engine Optimization

innodb_buffer_pool_size (interview must ask)

The main storage of heat data, according to page to store, page is the smallest unit, or even by paragraph to store

Recommended value: 50%-75% of physical memory recommended for private database server

If other services are available, then the "principle of 2/8" is recommended:

For example, 100G of data, at least 10% of active data, it is recommended that buffer pool points 20G

If this is a friend relationship system, where the data is almost hot, then 30%-50% of the memory is recommended

Another example of stealing food games, almost all can reach 90% of the heat data, then you know

innodb_buffer_pool_instances         

The main purpose is to facilitate the global lock of the buffer pool,

Generally set to 8, in the 5.5 version is divided into 4, to 5.6 version to be divided into 8, can not be changed to other values, because this is the result of the pressure measurement, 8 is likely to get the best performance

innodb_log_file_size                

Recommended to set as a percentage of the data page, such as page 15% buffer pool 100G, then the two sides to multiply the result is 15G, in fact, it is recommended that the data page configuration is equal, may not be configured so large, then will be File_ Size is set to 1g

So if we're going to use multiple log file, the following parameters will be involved :

Innodb_log_file_in_gourp

There are several log files that can be set, as to how to calculate:

Innodb Redo log file size with a total size of innodb_log_file_size *

Innodb_log_file_ingroup total size not less than 600M

Generally recommended 3 log file can be more than necessary

This article comes from Dawson study notes, copyright http://wubx.net/All

innodb_file_per_table          

Whether to set up a stand-alone table space

Closed before 5.6, open after 5.6, suggestion is on

Innodb_file_format # recommended designation as Barracuda

Innodb_flush_log_at_trx_commit

If the data is being guided, it may be 2 days or 3 days, and it may be that the result of setting this parameter to 1 causes

1 flush to disk for every transaction, high security, but lowest performance, often leading to slowest data

0 flushes a transaction into disk every second

2 General recommended values, about one transaction per second of the refresh and synchronization to disk, the actual write only to the operating system buffer, the operating system if the power loss will cause errors lost; #因为导数据都是人为参与的过程所以设置为2, to maximize the speed, if the error is done manually once, the recommended value

Innodb_flush_log_at_timeout ( introduced in 5.6)

• This parameter is used to control the log refresh once every n seconds (1-2,700 seconds). is an enhancement to group commit, the default is 1 seconds, 1 seconds to refresh and handled by Croup commit, can actually be done in 1-2,700 seconds, if the system is very high performance requirements, you can set this value larger, the throughput rate will be higher, because the group Commit work will be a bit better, if the synchronous real-time requirements are very high, then set a lower, the default value can be

is a relative relationship, if the tuning assembly gets a good performance, but the delay from the library may occur

If the adjustment is 3 seconds, then this 3 seconds to do a commit, then in the first 2 seconds to hang up, then in this 2 seconds, the data will be lost

Innodb_flush_method

With the specified data actually written to disk method, directly using the O_direct can

O_direct working on XFS or EXT4 is a good performance.

The biggest problem is the cache that O_direct uses to reduce the VFS level of the system, and the saved memory can be supplied with the buffer pool.

If you use Fsync to do this, it will occupy the VFS level of the cache, will occupy a lot of memory, if the buffer pool is large, it is easy to cause oom

So using O_direct to save memory provides more space for buffer pool

Innodb_flush_neighbors

The default is 0 after using this parameter will be near extent dirty page to refresh
For example, when the write time, will be dirty also check, this time will be the dirty page merged into sequential write that the adjacent extent also merge into

Again, for example, the first extent and the second extent are next to each other, then after moving here to find the second one also, then by the way the second also brush the past

if the SAS SATA disk is recommended for use with 1 However, there is no addressing delay for SSDs, so dirty page refreshes are not required, because it is hot data but is refreshed and then becomes cold data data will be loaded once

This article comes from Dawson study notes, copyright http://wubx.net/All

IO Related Optimizations

Innodb_io_capacity # Important

This parameter is the maximum number of InnoDB IO

In general, 10W ioPS is normal and later optimized: HDD 150 * disks, SSD 200.01 million

Like 6 bucks made RAID10, so this is 150*3.

The need to consider is 6 disk raid to consider how many fast disk IO capability, and then consider if the first RAID 1 can enhance the IO capability, if not upgrade then nothing is 0 can enhance the IO capability, so is 6 disk RAID10 can only get 3 disk IO capability

The following are additional parameters for capactiy

For example, the configuration here is 3 disk 450, but the peak will be higher, may reach 1000 or more, then you can specify the maximum value of the following parameters

Innodb_io_capacity_max # set io_capacity maximum value

Read-Write Related:

Innodb_write_io_threads

Innodb_read_io_threads

Recommended configuration values:

The same as the number of CPUs, such as the 16 core CPU is configured to 16 can be

If it's a 8-core, it's 8.

If the configuration is too high, it will cause the system to be very card, so stay consistent

Innodb_write_io_threshold

Allow Prefech multiple page to BP only once (0-64)

Innodb_random_read_ahead

Prefech to whether the BP function is turned on

Considering some of the sequential IO and data loading issues

For example, now read IO threshold

For example, in a user system to read a user's information, then the page will be loaded into the DP

Then it may be necessary to say whether to load the adjacent page, there is a problem if the user system in a page does not need to load the adjacent page directly to turn it off, then this parameter is absolutely 0 because no additional data required

However, if it is a friend relationship, the data read to this user through the program needs to be close to the user also read out and load then you can set this parameter a little more to read some of the more like 3

This parameter needs to be combined according to the business system. If I don't live, leave him.

This article from the "Mood is still" blog, reproduced please contact the author!

MySQL Storage Engine Tuning parameters