[MySQL] Innodb parameter optimization, mysqlinnodb

[MySQL] Innodb parameter optimization, mysqlinnodb

Innodb_buffer_pool_size

The innodb_buffer_pool_size parameter is used to set the Innodb primary Buffer (Innodb_Buffer_Pool), that is, the primary cache space for caching user tables and index data, which has the greatest impact on the overall performance of Innodb.

For a single host for MySQL and assuming that only the innodb engine is used, we recommend that you set this parameter to about 75% of the logistics memory.

After the system is launched, we can further analyze the real-time status information about the Buffer Pool provided by the Innodb Storage engine to determine whether the Buffer Pool usage of Innodb in the system is normal and efficient:

mysql> show status like 'Innodb_buffer_pool_%';+-----------------------------------------+---------------+| Variable_name                           | Value         |+-----------------------------------------+---------------+| Innodb_buffer_pool_pages_data           | 999020        | | Innodb_buffer_pool_pages_dirty          | 47643         | | Innodb_buffer_pool_pages_flushed        | 474668167     | | Innodb_buffer_pool_pages_LRU_flushed    | 365125        | | Innodb_buffer_pool_pages_free           | 0             | | Innodb_buffer_pool_pages_made_not_young | 0             | | Innodb_buffer_pool_pages_made_young     | 203410903     | | Innodb_buffer_pool_pages_misc           | 49552         | | Innodb_buffer_pool_pages_old            | 368697        | | Innodb_buffer_pool_pages_total          | 1048572       | | Innodb_buffer_pool_read_ahead_rnd       | 0             | | Innodb_buffer_pool_read_ahead           | 66348855      | | Innodb_buffer_pool_read_ahead_evicted   | 3716819       | | Innodb_buffer_pool_read_requests        | 3215992991498 | | Innodb_buffer_pool_reads                | 65634998      | | Innodb_buffer_pool_wait_free            | 651           | | Innodb_buffer_pool_write_requests       | 21900970785   | +-----------------------------------------+---------------+

From the above values, we can see that there are a total of 1048572 pages, of which 999020 pages are stored, and there is no free page.
There are 3215992991498 read requests, of which 65634998 are not in the buffer pool, that is, 65634998 are read by the physical disk, therefore, we can easily conclude that the Read hit rate of the Innodb Buffer Pool is: (3215992991498-65634998)/3215992991498 * 100% = 99.998%.

Innodb_buffer_pool_instances

This parameter divides innodb_buffer_pool into different instances. Each instance has independent LRU, FLUSH, FREE, and mutex control.

For the relatively large innodb_buffer_pool_size, we recommend that you set multiple instances to avoid contention for memory locks.

Innodb_log_file_size

Set the innodb redo log file size. In terms of performance, the larger the log file, the better, the lower the buffer pool checkpoint frequency. However, in the official MySQL version, innodb_log_files_in_group * innodb_log_files_in_group cannot exceed 4 GB.

The larger the log file, the longer the recovery time after the MySQL instance crash. However, the master and slave databases are usually configured in the Generation system. Therefore, this factor can be ignored.

In general, in my personal maintenance environment, I prefer to set transaction logs to three groups, and set each log to 256 MB. The overall effect is not bad.

Innodb_log_buffer_size

As the name suggests, this parameter is used to set the Innodb Log Buffer size. The default value is 1 MB. The main function of Log Buffer is to Buffer Log data and improve the I/O performance of Log writing. Generally, if your system is not "with a very high write load and a large transaction volume", the size within 8 MB is enough.

We can also analyze the Log usage through the performance statistics provided by the system status parameters:

mysql> show status like 'innodb_log%';+---------------------------+------------+| Variable_name             | Value      |+---------------------------+------------+| Innodb_log_waits          | 0          | | Innodb_log_write_requests | 3486920147 | | Innodb_log_writes         | 352577360  | +---------------------------+------------+

If Innodb_log_waits is not equal to 0, it indicates that the Log Buffer write wait has occurred, and innodb_log_buffer_size may be too small.

Innodb_thread_concurrency

This parameter indicates the maximum thread concurrency of innodb. It is recommended to set it to 0, which means innodb controls it by itself. However, it has been proved that innodb will improperly control the concurrency when it is too large, mySQL hang may die, so it is generally recommended that the number of CPU cores (excluding hyper-threading)

Innodb_io_capacity

Indicates the maximum number of data pages processed by I/O devices per second. If the hard disk performance is good, it can be larger (such as 1000 ).

Innodb_max_dirty_pages_pct

Indicates that the proportion of dirty pages refreshed by innodb from the buffer does not exceed this value. The dirty pages of each checkpoint are refreshed as follows: innodb_max_dirty_pages_pct * innodb_io_capacity

Innodb_flush_method

It is used to enable Innodb to open and synchronize data files and log files, but it is only valid on Linux and Unix systems. When we set it to O_DSYNC, the system will open and refresh the log file in O_SYNC mode, and use fsync () to open and refresh the data file. When it is set to O_DIRECT, the data file is opened through O_DIRECT (Solaris for directio (), and fsync () is used to refresh the data and log files.
In general, the different settings of innodb_flush_method mainly affect the differences in the operating system I/O excuses that Innodb calls when performing IO operations on different operating platforms. Different I/O operation interfaces have different data processing methods, so there are also some differences in processing performance. In general, if our disk uses RAID cards for hardware-level RAID, we recommend that you use O_DIRECT to improve IO performance to a certain extent. However, if RAID Cache is insufficient, you still need to be cautious.

Innodb_file_per_table

It is generally recommended to enable this function because different tablespaces allow you to flexibly set the addresses of Data Directories to avoid IO competition in shared tablespaces.

Innodb_flush_log_at_trx_commit

Innodb_flush_log_at_trx_commit = 0. The Log Thread in Innodb writes data in the log buffer to the file every one second, and notifies the file system to perform the flush operation for file synchronization, make sure that the data has been written to the physical file on the disk. However, the end of each transaction (commit or rollback) does not trigger the Log Thread to write data in the log buffer to the file. Therefore, when it is set to 0, when MySQL Crash and OS Crash or the host is powered off, the most extreme case is that the data changes within one second are lost.

Innodb_flush_log_at_trx_commit = 1, which is also the default setting of Innodb. At the end of each transaction, Log Thread is triggered to write data in log buffer to a file and notify the file system to synchronize the file. This setting is the most secure and ensures that no submitted data is lost whether it is MySQL Crash, OS Crash, or host power failure.

Innodb_flush_log_at_trx_commit = 2. When we set it to 2, Log Thread writes data to the transaction Log at the end of each transaction, however, the write operation only calls the file write operation of the file system. Our file systems all have a caching mechanism, so this write of Log Thread cannot ensure that the content has been written to the physical disk to complete the persistent action. When will the file system synchronize the cached data to the Log Thread of the physical disk file. Therefore, when set to 2, MySQL Crash does not cause data loss, but the amount of data that may be lost after the OS Crash or host power failure is completely controlled on the file system.

From the above analysis, we can see that when innodb_flush_log_at_trx_commit is set to 1, it is the safest. However, because I/O synchronization operations are the most, the performance is also the worst among the three settings. If it is set to 0, there is one synchronization every second, and the performance is relatively high. If set to 2, the performance may be the best of three. However, it may be that the most data is lost after a Crash occurs. How to set the settings depends on the specific scenario. In general, if we cannot accept data loss at all, we will definitely sacrifice some performance in exchange for data security, and set it to 1. If we can lose a small amount of data (for example, within 1 second), we can set it to 0. Of course, if you think our OS is stable enough, host hardware equipment, and host power supply system is safe enough, we can also set innodb_flush_log_at_trx_commit to 2 to maximize the overall performance of the system.

Transaction-isolation

For highly concurrent applications, in order to ensure data consistency as much as possible and avoid data inconsistency caused by concurrency, the higher the transaction isolation level, the better. However, for Innodb, the higher the transaction isolation level, the higher the implementation complexity, the more things to do, and the worse the overall performance.

Therefore, we need to analyze the logic of our own application system and select the acceptable minimum transaction isolation level. To achieve the highest performance while ensuring data security consistency.
Although the default transaction isolation level of the Innodb Storage engine is repeatable read, in most of our application scenarios, we only need to READ the committed transaction isolation level to meet the requirements.

Sync_binlog

Indicates the number of binlog to disk refreshed each time.

For the core system, we need to adopt the dual-1 mode, that is, innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1, which ensures data consistency between the master and slave databases without data loss.

MySQL Innodb parameter settings

1. Your command is correct.
2. Are you sure your mysql can still be used normally.
3. It is very likely that you accidentally modify a configuration in the configuration file.

Mysql Optimization

1. Optimize the SQL statement and find the slow query SQL statement from the slow query log.
2. Partition large tables
3. For the table engine, increase the corresponding configuration parameters (innodb is innodb_buffer_pool_size, and myisam is key_buffer)
4. read/write splitting
5. database/table sharding
6. Upgrade the hardware, create a disk array, or use ssd instead.

(1 ). in terms of database design, this is the responsibility of DBA and impact ect. A database with a good design structure should be de-normalized when necessary (I don't know what the Chinese translation is ), some data redundancy is allowed to avoid JOIN operations to improve query efficiency.
(2 ). in terms of system architecture design, the table is hashed, and massive data is hashed into several different tables. fast and slow tables: only the latest data is retained. Slow tables are archived in history. cluster, Master server Read & write, slave server read only, or N servers, each machine is a Master
(3). (1) and (2) better than PHP Programmer's requirements. It doesn't matter. check whether there is any less index.
(4 ). write efficient SQL statements to see if there are any inefficient SQL statements, such as generating full connections to cartesian products, a large number of Group By and order by statements, and no limit. when necessary, encapsulate the database logic in the stored procedure of the DBMS. cache query results and explain each SQL statement
(5). All the results are required. Only necessary data is obtained from the database. For example, you can query the number of comments of an article, select count (*)... where article_id =? You can. Do not select *... where article_id =? Then msql_num_rows.
Send only required SQL statements. For example, if you modify only the title when modifying an article, update... set title =? Where article_id =? Do not set content =? (Large text)
(6). Use different storage engines when necessary. For example, InnoDB can reduce deadlocks. HEAP can increase the query speed by an order of magnitude.