MySQL InnoDB performance optimization

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

=========================================

Introduction:
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
2. Daily Value Control
3. File IO allocation, space Footprint
4. Other relevant parameters

1. Memory utilization:
first describes one of the most important parameters of 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.

Second:
innodb_additional_mem_pool:  
Function: The internal directory used to store the 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.
Assignment principle:
with show InnoDB status/g;   To see what the state of the running DB is (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.
Setting 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:
role: Buffering of transactions in memory.
Allocation principle: Control in 2-8m. This value is not 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.
Setting 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;

Summary:
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.

========================================

With this MySQL InnoDB tuning experience, there are some doubts about the non-conformance of MySQL's official recommendation configuration, worth thinking about and exploring:

1, should innodb_flush_method should not use O_direct?

All MySQL tuning recommendations say that if the hardware does not have pre-read functionality, then using O_direct will greatly reduce the performance of InnoDB because O_direct skips the operating system's filesystem disk Cache, allowing MySQL to read and write directly to the disk.

But in my practice, if you do not use O_direct, the operating system is forced to open up a large number of disk cache for InnoDB read and write cache, not only to improve read and write performance, but caused a sharp decline in read and write performance. and buffer pool data cache and operating system disk cache cache caused a double buffer waste, obviously from my practice, it is very wasteful.

Said O_direct caused the performance degradation of MySQL directly read and write disk, I think is completely unfounded. Because from the Javaeye database monitoring, the INNODB buffer pool hit rate is very high, more than 98%, the real disk operation is negligible. For 1% of disk operations to obtain disk Cache, and waste 98% of the double buffer memory space, both in terms of performance, or from the memory resource consumption, it is very unwise.

2, innodb_log_file_size should be bigger, or smaller?

All MySQL tuning recommendations say that the larger the innodb_log_file_size, the better, and avoid the flush operation of the unnecessary buffer pool.

In my practice, however, innodb_log_file_size is too large to significantly increase the log write operation of InnoDB and will cause the operating system to require more disk cache overhead.

So from my experience, Innodb_flush_method =o_direct is a must, and innodb_log_file_size is not too big.