) MySQL InnoDB Engine Optimization

Introduction:

InnoDB provides MySQL with a transaction security (acid-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an oracle-style non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.

Founder of InnoDB: Heikki tuuri

Heikki tuuri is also very active in the bug community of InnoDB. If you encounter a bug, you can directly mention the community and get answers from the author.

Why do you want to learn how to optimize InnoDB:

Currently, InnoDB is designed to provide the maximum performance for MySQL to process massive data volumes. Its CPU efficiency may be unmatched by any other disk-based relational database engine. InnoDB is favored for websites or applications with large data volumes.

On the other hand, InnoDB can ensure data consistency between master and slave in Database Replication operations.

Parameter Optimization content:

1. Memory Utilization

2. Daily Value Control

3. file IO allocation and space occupation

4. Other Related Parameters

1. Memory utilization:

First, we will introduce one of the most important InnoDB parameters:

Innodb_buffer_pool_size

This parameter is similar to key_buffer_size of MyISAM, but it is also different. This parameter is mainly used to cache indexes and data of InnoDB tables. It is the primary parameter for InnoDB acceleration and optimization.

This parameter is used to allocate memory. The default value of this parameter is 8 MB, which is a very small value. If it is a dedicated DB server, it can account for 70%-80% of the memory. This parameter cannot be changed dynamically. Therefore, you need to consider the allocation. If the allocation is too large, swap will take up too much, resulting in slow MySQL query. If your data is small, you can allocate about 10% of your data size as the value of this parameter. For example, if the data size is 50 m, innodb_buffer_pool_size = 64 m is assigned to this value.

Setting method:

Innodb_buffer_pool_size = 4G

The usage of this parameter allocation value can be determined by

----------------------

Buffer Pool and memory

----------------------

Total memory allocated 4668764894;

Confirm the usage.

Second:

Innodb_additional_mem_pool:

Role: used to store the internal directory of InnoDB

This value does not need to be allocated too much, and the system can automatically tune it. No need to set too high. Generally, it is enough to set 16 MB for big data. If there are more tables, you can increase the size as appropriate. If this value is automatically increased, it will be displayed in the error log.

Allocation principles:

Use show InnoDB status \ G; to check the status of the running dB (refer to the buffer pool and memory segment), and then adjust it to the appropriate value.

----------------------

Buffer Pool and memory

----------------------

Total memory allocated 4668764894; in additional pool allocated 16777216

Reference: In additional pool allocated 16777216

You can adjust the parameters as needed.

Setting method:

Innodb_additional_mem_pool = 16 m

2. About the daily value:

Innodb_log_file_size

Purpose: specify the size of the daily value.

Allocation principle: the size of several daily value members is almost the same as your innodb_buffer_pool_size. The maximum size of each log is 4 GB. Generally, it is better to limit the size of several log files to 2 GB. The specific situation also depends on the transaction size and data size.

Note: the size of the value allocation has a great relationship with the database write speed, transaction size, and recovery after an exception or restart.

Setting method:

Innodb_log_file_size = 256 m

Innodb_log_files_in_group

Purpose: specify the number of daily value groups you have.

Allocation principle: Generally we can use 2-3 daily value groups. The default value is two.

Setting method:

Innodb_log_files_in_group = 3

Innodb_log_buffer_size:

Role: buffer the transaction in the memory.

Allocation principle: the limit is 2-8 m. This value does not need to be too large. The memory in it is generally written to the disk once a second. The specific write method is related to your transaction commit method. It is generally appropriate to specify a maximum of 3 m for Oracle and other databases.

Reference: innodb_ OS _log_written (available in show global status)

If this value increases too fast, you can add innodb_log_buffer_size as appropriate.

In addition, if you need to process the Dali text or blob field, you can consider adding the value of this parameter.

Setting method:

Innodb_log_buffer_size = 3 m

Innodb_flush_logs_at_trx_commit

Role: control the transaction commit Method

Allocation principle: this parameter has only three values, 0, 1, and 2. Check the acceptable level. The default value is 1. Do not change the master database.

A transaction with higher performance can be set to 0 or 2, but will be lost for one second.

Note:

The setting of this parameter has a great impact on the performance of InnoDB.

When the value is 1: The transaction log of InnoDB writes the Daily Value file after each commit, and refreshes the daily value to the disk. This can avoid losing any transaction.

When the value is 2: log buffering is written to a file for each commit, but disk operations are not refreshed for log files, the log file is refreshed once per second when the value is 2. However, it should be noted that, due to process calling problems, it cannot be guaranteed that 100% occurs every second. Thus, the performance is the fastest. However, the transaction in the last second will be deleted only when the operating system crashes or powers down.

When the value is 0, the log buffer is written to the log file once per second, and the disk operation is refreshed for the log file, but no operation is performed on the commit of a transaction. The crash of the mysqld process will delete the last second of the transaction before the crash.

From the above analysis, when this value is not 1, it can achieve better performance, but there will be a loss in the case of exceptions, so it should be measured according to your own situation.

Setting method:

Innodb_flush_logs_at_trx_commit = 1

3. file IO allocation and space occupation

Innodb_file_per_table

Purpose: Enable each InnoDB table to have its own independent tablespace. For example, after deleting a file, you can recycle that part of the space.

Allocation principle: not used only. However, DB still needs a public tablespace.

Setting method:

Innodb_file_per_table = 1

Innodb_file_io_threads

Role: number of file read/write Io. This parameter only works on Windows. In Linux, the value is equal to 4.

Setting method:

Innodb_file_io_threads = 4

Innodb_open_files

Purpose: restrict the data of tables that InnoDB can open.

Allocation principle: if there are many tables in the database, add this rule. This value is 300 by default.

Setting method:

Innodb_open_files = 800

Please add table_cache as appropriate

4. Other Related Parameters

An important parameter is described here:

Innodb_flush_method

Role: an I/O model used by InnoDB to deal with systems

Allocation principle: Do not set for Windows.

UNIX can be set to fsync () or o_sync/o_dsync.

If the system can disable the system cache, it will be disabled.

Linux: o_direct

Directly write data to the disk. The system cache is disabled.

Setting method:

Innodb_flush_method = o_direct

Innodb_max_dirty_pages_pct

Purpose: control the percentage of InnoDB dirty pages in the buffer. The value ranges from 1 to 100. The default value is 90.

Another use of this parameter: When the memory allocation of InnoDB is too large and swap usage is serious, you can adjust this value appropriately to release the swap space. Meaning: the maximum value is 90%, and the minimum value is 15%. Too large. During each update in the cache, too many data pages need to be changed. The data pages are too small and the update operation is too slow.

Setting method:

Innodb_max_dirty_pages_pct = 90

The super permission is required for dynamic changes:

Set global innodb_max_dirty_pages_pct = 50;