MySQL InnoDB performance optimization

Recommended Parameters:

max_connections=800

key_buffer_size=512m
query_cache_size=128m
sort_buffer_size=64m
table_open_cache=512

Sort_buffer_size = 2M
Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Myisam_sort_buffer_size = 64M

innodb_buffer_pool_size=10g
Innodb_flush_logs_at_trx_commit=1
Innodb_file_per_table=1
innodb_open_files=4096

Expire_logs_days =//binary Log-day, Bin-log
Log-bin=mysql-bin

Parameter tuning content:
1. Memory utilization aspects
2. Daily Value Control
3. File IO allocation, space occupancy
4. Other relevant parameters

1. Memory utilization:
first describes the most important parameters of a InnoDB:
innodb_buffer_pool_size
This parameter is similar to the key_buffer_size of MyISAM, but it is also different. This parameter mainly caches the index of the InnoDB table, the data, and the buffer when the data is inserted. Optimize the primary parameters for InnoDB acceleration.
The principle of allocating memory for this parameter: This parameter is assigned by default to only 8M, which can be said to be a very small value. If it is a dedicated DB server, then he can account for 70%-80% of the memory. This parameter cannot be changed dynamically, so the allocation needs to be considered more. The allocation is too large, will make the swap occupied too much, resulting in MySQL query extra slow. If your data is small, then the assignable is your data size + 10% or so as the value of this parameter. For example, if the data size is 50M, assign innodb_buffer_pool_size=64m
Setting method to this value:
innodb_buffer_pool_size=4g
The use of this parameter assignment value can be based on show InnoDB status\g;
----------------------
BUFFER POOL and memory
----------------------
Total Memory allocated 4668764894;
to confirm usage.

The second one:
Innodb_additional_mem_pool:
Function: The internal directory used to store InnoDB
This value is not allocated too large, the system can be automatically tuned. Do not set too high. Usually the larger data set 16M enough, if the table is more, can be appropriately increased. If this value is automatically incremented, it will be displayed in the error log.
Distribution principle:
With show InnoDB status\g; To see what state the DB is running in (refer to the buffer POOL and memory segment), then you can adjust to the appropriate value.
----------------------
BUFFER POOL and MEMORY
----------------------
Total memory Allocated 4668764894; In additional pool allocated 16777216
Reference: In additional pool allocated 16777216
Depending on your parameter situation, you can adjust it appropriately.
Setup method:
innodb_additional_mem_pool=16m

2. Regarding the value of the day:
innodb_log_file_size
Function: Specifies the size of the day value
Allocation principle: Several day value member sizes add up to almost equal to your innodb_buffer_pool_size. The upper limit is a maximum of 4G per day value. General Control the addition of several log files is better than the size of 2G. You need to look at the size of your transaction and the size of your data.
Note: The size of this value allocation is very much related to the database write speed, transaction size, and recovery after an abnormal restart.
Setup method:
innodb_log_file_size=256m

Innodb_log_files_in_group
Role: Specifies that you have several day value groups.
Distribution principle: Generally we can use 2-3 Day value groups. The default is two.
Setup method:
Innodb_log_files_in_group=3

Innodb_log_buffer_size:
Function: A buffer in which transactions are stored.
Distribution principle: Control in 2-8m. This value doesn't have to be too much. The memory inside of him is usually written to disk once a second. The specific write method is related to how your transaction is submitted. This is understood in databases such as Oracle, which is generally the most appropriate 3M designation.
Reference: Innodb_os_log_written (show global status can be obtained)
If this value grows too fast, you can increase the innodb_log_buffer_size appropriately.
In addition, if you need to deal with Dali's text, or BLOB fields, you can consider increasing the value of this parameter.
Setup method:
innodb_log_buffer_size=3m

Innodb_flush_logs_at_trx_commit
Role: Control how transactions are submitted
Allocation principle: This parameter has only 3 values, 0,1,2 Please confirm the level you can accept. The default is 1, please do not change the main library.
A higher performance can be set to 0 or 2, but a second transaction is lost.
Description:
The setting of this parameter has a great effect on the performance of InnoDB, so give more instructions here.
When this value is 1 o'clock: InnoDB's transaction log writes a daily value file after each commit, and flushes the daily value to disk. This can be done without losing a single transaction.
When this value is 2 o'clock: at each commit, the log buffer is written to the file, but the log file does not flush with the disk operation, and the refresh of the log file at a value of 2 also occurs once per second. However, it is important to note that due to the problem of process invocation, 100% per second is not guaranteed to occur. Thus, the performance is the fastest. But the operating system crashes or the power is dropped to delete the last second of the transaction.
When this value is 0 o'clock: The log buffer is written once per second to the log file, and the log file is refreshed with disk operations, but no action is taken on a transactional commit. The crash of the MYSQLD process deletes the last second of the transaction before the crash.

From the above analysis, when this value is not 1 o'clock, you can achieve better performance, but encounter anomalies will have a loss, so it needs to be measured according to their own situation.

Setup method:
Innodb_flush_logs_at_trx_commit=1

3. File IO allocation, space occupancy
innodb_file_per_table
Function: To make each InnoDB table, there is a self-contained table space. If you delete a file, you can reclaim that part of the space.
Allocation principle: Only use is not used. But DB also needs to have a common table space.
Setup method:
Innodb_file_per_table=1

innodb_file_io_threads
Role: File read/write Io, this parameter only works on Windows. On Linux, it will only equal 4.
Setup method:
Innodb_file_io_threads=4

Innodb_open_files
Function: Restricts the data of the table that the InnoDB can open.
Allocation principle: If there are many tables in the library, please add this. This value is 300 by default.
Setup method:
innodb_open_files=800
Please increase table_cache appropriately.

4. Other relevant parameters
Here is a more important parameter:
Innodb_flush_method
Role: An IO model for InnoDB and system dealings
Allocation principle: Windows does not have to be set.
UNIX can be set: Fsync () or O_sync/o_dsync
If the system can disable the system's cache, then ban him.
Linux can choose: O_direct
Write directly to disk and disable the system cache.
Setup method:
Innodb_flush_method=o_direct

innodb_max_dirty_pages_pct
Function: Control the dirty page of the InnoDB in the buffer under that percentage, the value is in the range 1-100, the default is 90.
Another use of this parameter: when the memory allocation of the INNODB is too large, resulting in a significant swap occupancy, you can appropriately reduce the adjustment of this value, so that the swap space to release. Build Justice: This value is maximum at 90%, minimum at 15%. Too large, each update in the cache requires too many data pages, too small, the data page is too small, the update operation is too slow.
Setup method:
Innodb_max_dirty_pages_pct=90
Dynamic changes require Super permissions:
Set global innodb_max_dirty_pages_pct=50;

By default, the parameters of the InnoDB are set very small and are not enough in the production environment
For example, the two most important parameters
Innodb_buffer_pool_size default is 8M
Innodb_flush_logs_at_trx_commit The default setting is 1, which is synchronous flush log (so to understand)
Innodb_buffer_pool_size: This is the most important setting for InnoDB, which has a decisive impact on InnoDB performance. The default setting is only 8M, so the default database setting below InnoDB performance is poor. You can set 60-80% memory on a database server that has only the InnoDB storage engine. To be more precise, set the memory size to 10% larger than InnoDB tablespaces under the memory capacity allowed.
Innodb_data_file_path: Specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file will allow automatic expansion. Thus, when the space is exhausted, the auto-expansion data file will automatically grow (in 8MB) to accommodate the additional data. For example: Innodb_data_file_path=/disk1/ibdata1:900m;/disk2/ibdata2:50m:autoextend two data files on different disks. The data is first placed in the ibdata1, and when it reaches 900M, the data is placed in the IBDATA2. Once the 50MB,IBDATA2 is reached, it will automatically grow in 8MB units. If the disk is full, you need to add a data file to the other disk.
Innodb_data_home_dir: Place table space Data directory, default in MySQL data directory, set to and MySQL installation file different partition can improve performance.
Innodb_log_file_size: This parameter determines the recovery speed. If it's too big, recovery will be slow, too small. Impact query performance, generally take 256M to balance performance and recovery speed
。
Innodb_log_buffer_size: Disk speed is very slow, directly log writes to the disk will affect the performance of InnoDB, this parameter sets the size of the log buffer, generally 4M. If you have a large blob operation, you can increase it appropriately.
innodb_flush_logs_at_trx_commit=2: This parameter sets the processing of log information in memory when the transaction commits.
1) = 1 o'clock, when each transaction commits, the log buffer is written to the log file, and the log file is refreshed with the disk operation. Truly ACID. Slow speed.
2) = 2 o'clock, when each transaction commits, the log buffer is written to the file, but the log file is not refreshed with disk operations. Only the operating system crashes or loses power to delete the last second of the transaction, or the transaction will not be lost.
3) = 0 o'clock, the log buffer is written to the log file once per second, and the log file is refreshed with disk operations. The crash of any mysqld process will delete the last second of the transaction before the crash
Innodb_file_per_table: You can store each InnoDB table and its index in its own file.
Transaction-isolation=read-comitted: If the application can run at the Read-commited isolation level, this setting will have some performance gains.
Innodb_flush_method: How to set InnoDB Sync io:
1) default– use Fsync ().
2) O_sync open files in SYNC mode, usually slower.
3) O_direct, use DIRECT IO on Linux. Can significantly increase the speed, especially on RAID systems. Avoid additional data duplication and double buffering (MySQL buffering and OS buffering).
Innodb_thread_concurrency:innodb kernel The maximum number of threads.
1) Minimum set to (Num_disks+num_cpus) * *.
2) You can disable this restriction by setting the 1000来 to

MySQL InnoDB performance optimization