Mysql Innodb Engine Optimization

Introduced:
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
Founder of Innodb: Heikki Tuuri
Heikki Tuuri in the InnoDB bug community is also very active, if you encounter a bug can also directly refer to the community, get the author's answer.

Why study InnoDB's tuning:
For now: InnoDB is the biggest performance design for MySQL when it comes to processing huge amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine. InnoDB is favored in large-volume websites or applications.
On the other hand, InnoDB also can ensure the consistency of master and slave data in the database copy operation.

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, introduce 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 this value to innodb_buffer_pool_size=64m
Setup method:
innodb_buffer_pool_size=4g
The use of this parameter assignment value can be based onshow InnoDB status\g;In the
———————-
BUFFER POOL and MEMORY
———————-
Total memory allocated 4668764894;
To confirm the use of the situation.
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:
Useshow 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;

Summarize:
This is only a list of the important parameters of the InnoDB section, not considered to be the overall tuning of MySQL. MySQL parameters are generally divided into: global parameters, specific engine parameters. For global parameters, refer to the MySQL tuned ppt of Http://imysql.cn/2007_12_08_optimize_mysql_under_linux Yejr.

Mysql Innodb Engine Optimization