Detailed description and optimization of MySQLInnoDB storage engine parameters

Innodb_data_home_dir this is the directory sharing setting of the InnoDB table. If not? My. cnf? And InnoDB uses the datadir directory of MySQL as the default directory. If an empty string is set, you can set the absolute path in innodb_data_file_path. Innodb_data_file_path specifies the path and size of the data file separately.

Innodb_data_home_dir this is the directory sharing setting of the InnoDB table. If not? My. cnf? And InnoDB uses the datadir directory of MySQL as the default directory. If an empty string is set, you can set the absolute path in innodb_data_file_path. Innodb_data_file_path specifies the path and size of the data file separately.

Innodb_data_home_dir	This is the directory sharing settings for the InnoDB table. If not? My. cnf? And InnoDB uses the datadir directory of MySQL as the default directory. If an empty string is set, you can set the absolute path in innodb_data_file_path.
Innodb_data_file_path	Specify the path and size of the data file separately. The complete path of the data file is composed of innodb_data_home_dir and the set value. The file size is specified in MB. Therefore, "M" is required after the file size is specified ". InnoDB also supports the abbreviation "G", 1G = 1024 M. Starting from 3.23.44, you can set the data file size to be greater than 4 GB on operating systems that support large files. In other operating systems, data files must be smaller than 2 GB. The total data file size must be at least 10 MB. In the MySQL-3.23, this parameter must be in? My. cnf? . This is not required in MySQL-4.0.2 and later versions, and the system creates a 16 MB auto-extended data file ibdata1 under the datadir directory of MySQL by default. You can also use a RAW raw disk partition (raw devices) as a data file.
Innodb_mirrored_log_groups	The number of copies of Log File groups set to protect data. The default value is 1. In my. cnf? Set in numeric format.
Innodb_log_group_home_dir	Path of the InnoDB log file. Must be Set innodb_log_arch_dir to the same value. If it is not explicitly specified, two 5 MB data files are created in the datadir directory of MySQL by default? Ib_logfile ...? File.
Innodb_log_files_in_group	The number of log files in the log group. InnoDB writes files in a circular mode (circular fashion. Value 3 is recommended. In? My. cnf? Set in numeric format.
Innodb_log_file_size	The size (in MB) of each log file in the log group ). If n is the number of log files in the log group, the ideal value is 1 MB to 1/n of the buffer pool (buffer pool) size set below. A large value can reduce the number of times the buffer pool is refreshed, thus reducing disk I/O. However, a large log file means that it takes a longer time to recover data during the crash. The total log files must be less than 2 GB, and 3.23.55 and 4.0.9 must be less than 4 GB. In my. cnf? Set in numeric format.
Innodb_log_buffer_size	The buffer size before InnoDB writes logs to log disk files. The ideal value is 1 M to 8 M. Large log buffering allows transactions to be committed only to transactions without saving logs into the disk ). Therefore, if a large transaction is processed, setting a large log buffer can reduce disk I/O. In? My. cnf? Set in numeric format.
Innodb_flush_log_at_trx_commit	Usually set to 1, which means that the log has been written to the disk before the transaction is committed, the transaction can run longer, and the repair capability after the service crashes. If you are willing to compromise this security, or if you are running a relatively small transaction, you can set it to 0 to reduce the disk I/O of log files. This option is set to 0 by default.
Innodb_log_arch_dir	The directory where fully written log files wocould be archived if we used log archiving. The parameters set here must be The same as innodb_log_group_home_dir. This parameter can be ignored from 4.0.6.
Innodb_log_archive	The value is always 0. Since the recovery from the backup (recovery) is suitable for MySQL to use its own log files, archive InnoDB log files is usually no longer needed. This option is set to 0 by default.
Innodb_buffer_pool_size	InnoDB is used to buffer data and index memory at high speed. Larger settings can reduce disk I/O when accessing data. You can set it to 80% of the physical memory on a dedicated database server. Do not set it too large, because competition for physical memory usage may affect page calls of the operating system. In? My. cnf? Set in numeric format.
Innodb_additional_mem_pool_size	InnoDB is used to store the size of data dictionary information and memory pools of other internal data structures. The ideal value is 2 M. If there are more tables, you need to reassign them here. If InnoDB uses up all the memory in this pool, it allocates memory from the operating system and writes error messages to the MySQL error log. In? My. cnf? Set in numeric format.
Innodb_file_io_threads	File I/O threads in InnoDB. It is usually set to 4, but a larger value can be set in Windows to increase disk I/O. In? My. cnf? Set in numeric format.
Innodb_lock_wait_timeout	Before rolling back, InnoDB transactions will wait for the timeout time (in seconds ). InnoDB automatically checks its own transaction deadlocks when the table is locked and the transaction is rolled back. If you use the lock tables command, or use another transaction-safe table processor (transaction safe table handlers than InnoDB) in the same transaction, a deadlock that InnoDB cannot notice may occur. In this case, timeout will be used to solve this problem. The default value of this parameter is 50 seconds. In? My. cnf? Set in numeric format.
Innodb_flush_method	This parameter is only related to Unix. The default value of this parameter is fdatasync. Another setting item is O_DSYNC. . This only affects the log file dump, and fsync is used to dump data in Unix. The InnoDB version starts from 3.23.40b and specifies fdatasync in Unix. Specify the O_DSYNC Method for fsync and O_DSYNC. Because there are still some problems in some Unix environments, 'data' versions is not used.
Innodb_force_recovery	Warning: this parameter can only be used when you want to dump data from a corrupted database! The value range may be 1-6. View the following section 'forcing recovery 'to understand the specific meaning of this parameter. A value greater than 0 indicates the security of InnoDB to prevent users from modifying data. This parameter is available starting from 3.23.44. In? My. cnf? Set in numeric format.
Innodb_fast_shutdown	InnoDB does not clear the insert buffer before it is disabled. This operation may take several minutes, but in extreme cases it may take several hours. If this parameter is set to 1, InnoDB will skip this process and close it directly. This parameter is available starting from 3.23.44 and 4.0.1. Starting from 3.23.50, the default value of this parameter is 1.
Innodb_thread_concurrency	InnoDB tries to make the operating system process used by the InnoDB service less than or equal to the value set here. The default value of this parameter is 8. If the computer system performance is low or innodb_monitor shows a multi-thread waiting signal, set this value to a smaller value. If your computer system has many processor and disk systems, you can set this value a little higher to make full use of your system resources. We recommend that you set the value to the number of processors + number of disks. This parameter is available starting from 3.23.44 and 4.0.1. In? My. cnf? Set in numeric format.

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 a non-locked read consistent Oracle wind grid 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, so 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 the value assigned by this parameter 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 statusG; to check the status of the running DB (refer to the buffer pool and memory segments), 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=16M

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=256Minnodb_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=3innodb_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 BLOG field, you can consider adding the value of this parameter.

Setting method:

innodb_log_buffer_size=3Minnodb_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=1innodb_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=4innodb_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_DIRECTinnodb_max_dirty_pages_pct1: controls 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: 1innodb_max_dirty_pages_pct = 90

The Super permission is required for dynamic changes:

set global innodb_max_dirty_pages_pct=50;

Summary:

This is only an important parameter for listing Innodb and cannot be considered as an overall optimization for MySQL. MySQL parameters are generally divided into global parameters, specific engine parameters.

Source: http://www.linuxde.net/2013/04/13431.html

Innodb_data_home_dir this is the directory sharing setting of the InnoDB table. If not? My. cnf? And InnoDB uses the datadir directory of MySQL as the default directory. If an empty string is set, you can set the absolute path in innodb_data_file_path. Innodb_data_file_path specifies the path and size of the data file separately. The complete path of the data file is composed of innodb_data_home_dir and the set value. The file size is specified in MB. Therefore, "M" is required after the file size is specified ". InnoDB also supports the abbreviation "G", 1G = 1024 M. Starting from 3.23.44, you can set the data file size to be greater than 4 GB on operating systems that support large files. In other operating systems, data files must be smaller than 2 GB. The total data file size must be at least 10 MB. In the MySQL-3.23, this parameter must be in? My. cnf? . This is not required in MySQL-4.0.2 and later versions, and the system creates a 16 MB auto-extended data file ibdata1 under the datadir directory of MySQL by default. You can also use a RAW raw disk partition (raw devices) as the data file innodb_mirrored_log_groups. The default value is 1. In my. cnf? Set in numeric format. Path of the innodb_log_group_home_dir InnoDB log file. It must be set with the same value as innodb_log_arch_dir. If it is not explicitly specified, two 5 MB data files are created in the datadir directory of MySQL by default? Ib_logfile ...? File. The number of log files in the innodb_log_files_in_group log group. InnoDB writes files in a circular mode (circular fashion. Value [...]