Optimization of MySQL in high memory and IO Utilization

Optimization of MySQL in high memory and IO Utilization

The following optimizations are based on some MySQL Optimizations in the CentOS system. We hope to continue to improve them if they are incomplete or controversial.

I. mysql-level optimization

1. Set innodb_flush_log_at_trx_commit to 2.

Set 0 to write the transaction log (ib_logfile0, ib_logfile1) to the log buffer per second, 1 to write from time to time, 2 to write the file system cache first, and then fl into the disk per second, the difference from 0 is that 2 won't lose data even if mysql crashes.

2. innodb_write_io_threads = 16 (this parameter must be added to the configuration file to restart the mysql instance)

The number of dirty page writing threads. Increasing this parameter can improve the write performance. This parameter is available only when mysql5.5.

3. Maximum dirty page percentage of innodb_max_dirty_pages_pct

When the percentage of dirty pages in the system exceeds this value, INNODB writes the updated data on the page to the disk file. The default value is 75, which is hard to achieve by the popular SSD hard drive. Adjustable between-80 based on actual conditions

4. innodb_io_capacity=5000

The number of dirty pages refreshed at one time when the dirty pages are refreshed from the buffer zone. We recommend that you set the following settings based on the disk IOPS:

SAS 200
SSD 5000
PCI-E 10000-50000

5. innodb_flush_method = O_DIRECT (this parameter must be effective after the mysql instance is restarted)

Control the Enable and fl modes of innodb data files and redo logs. There are three values: fdatasync (default), O_DSYNC, and O_DIRECT.

• Fdatasync mode: When writing data, the write step does not need to be written to the disk to complete (it may be written to the buffer of the operating system and the result will be returned to be completed). The actual completion is the flush operation, buffer is handed over to the operating system for flush, and the metadata information of the file also needs to be updated to the disk.
• O_DSYNC mode: log writing is completed in the write step, and data file writing is completed in the flush step through fsync.
• O_DIRECT mode: data file write operations are directly from mysql innodb buffer to the disk, but not through the operating system buffer, and the real completion is also in the flush step, logs still need to be buffered by OS.

Three modes, such as: the figure shows that the advantage of O_DIRECT over fdatasync is to avoid double buffering. the innodb buffer pool itself is a buffer zone and does not need to be written into the system buffer, however, the disadvantage is that it is directly written to the disk, so it is less efficient than sequential read/write of fdatasync.
In a large number of random write environments, O_DIRECT is more efficient than fdatasync. If there are more sequential writes, the default fdatasync is more efficient.

6. Set innodb_adaptive_flushing to ON (to make refreshing dirty pages smarter)

The number of dirty pages refreshed per second is affected. The rule is changed from "Refresh 100 dirty pages to disk when innodb_max_dirty_pages_pct is greater" to "judge the redo log generation speed through the buf_flush_get_desired_flush_reate function and determine the optimal number of dirty pages to be re "; A certain amount of dirty pages are refreshed even if the proportion of dirty pages is smaller than that of innodb_max_dirty_pages_pct.

7. Set innodb_adaptive_flushing_method to keep_average.

This variable affects checkpoint, calculates more evenly, adjusts the speed of dirty page refreshing, and flush necessary. (This variable is a variable under mysql-derived Percona Server, and does not exist in native mysql)

8. innodb_stats_on_metadata=OFF   

Disable the index statistics generated by accessing the following table of the information_schema database.

After the mysql instance is restarted, mysql will randomly retrieve io data and traverse all the tables for sampling statistics. This is not used in actual use. We recommend that you disable it.

9. innodb_change_buffering=all  

When the page corresponding to the updated/inserted non-clustered index data is not in memory (the update operation on Non-clustered index usually leads to random IO ), it will be placed in an insert buffer. When the page is read to the memory, the changes will be recorded in the merge page. When the server is idle, the background thread also performs merge operations.

Because we mainly use merge's advantages to reduce io, but do not modify fixed data multiple times in some scenarios, we do not need to enable change_buffering for update/insert operations, if it is enabled, it only occupies the space and processing capability of buffer_pool. This parameter must be configured based on the actual business environment.

10. innodb_old_blocks_time=1000

So that the length of the Block in the old sublist is 1 s and will not be transferred to the new sublist. This avoids the contamination of the Buffer Pool BP and can be considered as a long chain table. It is divided into two parts: young and old. By default, old accounts for 37% of the size (configured by innodb_old_blocks_pct ). The Page near the top indicates that it was recently accessed. The Page near the end indicates that it has not been accessed for a long time. The intersection of the two parts becomes the midpoint. Whenever a new Page needs to be loaded to BP, the page will be inserted to the midpoint location and declared as old-page. When the old page is accessed, It is promoted to the top of the linked list and marked as young.

Because the operation of table scan is to load page first, and then immediately trigger an access. Therefore, when innodb_old_blocks_time = 0, the pages required by table scan are not read as young pages and added to the top of the linked list. Some pages that are not frequently used will be squeezed out by BP, resulting in disk IO generated by subsequent SQL statements, resulting in slow response.

At this time, although the pages accessed by mysqldump will be constantly loaded at the top of the LRU, the high-frequency hotspot data access will seize the page to the top of the LRU at a faster speed. As a result, the page loaded by mysqldump will be quickly flushed and immediately evict (obsolete ). Therefore, time = 0 or 1000 does not have a great impact on access in such a stressful environment, because the data of dump cannot compete for hotspot data. Not only dump, but also big data operations.

Ii. mysql System Optimization

1. Disable numa = off, or change the policy to interleave to prevent unexpected swap

The numa policy introduces the concept of node. Each physical CPU is regarded as a node, and each node has a local memory, which is external access to other nodes.

NUMA memory allocation policies include localalloc (default), preferred, membind, and interleave.

• Localalloc specifies that the process requests to allocate memory from the current node;
• Preferred is relatively loose and specifies a recommended node to get the memory. If the recommended node does not have enough memory, the process can try another node.
• Membind can specify several nodes, and the process can only request memory allocation from these specified nodes.
• Interleave specifies that the process requests to allocate memory from a specified number of nodes in an intertwined manner using the Round-roll algorithm.

Each process (or thread) will assign a priority node, which may cause a problem for the system's default localalloc policy. For applications that occupy almost the entire system memory such as mysql, if Linux allocates a large resource to a node that is full of resources, there will be insufficient resources, this may cause swap of memory data on disks or discard the active data in buffer_pool. In actual tests, for example, there are two physical nodes node0 and node1. When the system load is very high, the node 0 resources are fully occupied. Although node1 still has some idle memory, however, the system will not use idle resources on node1.

Therefore, it is recommended that you disable numa or set a policy to allocate memory for a single instance such as mysql. However, the number of instances on a machine can be bound to one CPU core. Then we can make full use of the numa features to make it more efficient.

2. Add local ports to deal with a large number of connections

Echo '1970 100'>/proc/sys/net/ipv4/ip_local_port_range

This parameter specifies the port allocation range, which is a restriction on outbound access. Mysql listens to port 3306 by default, even if there are multiple request links, it will not be affected. However, because mysql is a high-memory, high-cpu, and high-io application, we do not recommend that you use mysql on the same machine. Even if the business volume is small, it is better to reduce the configuration of a single machine and achieve coexistence of multiple machines.

3. Increase the number of connections in the queue

Echo '200'>/proc/sys/net/ipv4/tcp_max_syn_backlog

The larger the number of queues for establishing connections, the better. But from another perspective, it is more appropriate to use the connection pool in the actual environment to avoid performance consumption caused by repeated connection establishment. With the connection pool, the number of connections is more controllable at the application level.

4. Set the link timeout time

Echo '10'>/proc/sys/net/ipv4/tcp_fin_timeout

This parameter is used to reduce the resource duration occupied by TIME_WAIT. Especially for servers with http transient links or mysql that do not use a connection pool, the effect is obvious.

Iii. Other optimization considerations

For high-DAU business mysql instances, we recommend that you do not compress the memory and use GB or higher memory. innodb makes good use of the memory advantages to improve mysql performance, we need to give him enough space to exert his performance. The disk IO performance is far slower than the memory processing speed. This is understandable. The optimization is to block IO operations to the memory and return them directly to the user.

Of course, when the memory is higher, we cannot cache all the data into the memory. In most actual businesses, we still rely on random I/O, for example, today's popular mobile games, it is also a business type with high random writing. Each cloud vendor also provides SSD or even higher PCIe Flash storage devices by default. Improving the disk I/O performance is also the second choice.

In addition, if the business is large enough, a single machine will inevitably be unable to support it. We need to consider database sharding and table sharding. What most product companies do is to divide databases by business. If a business is too large to be borne by a single machine, it needs to be operated by sub-tables and sub-databases. In fact, no matter whether the business is bigger or not, you should support database sharding and table sharding when creating a project (avoid using the auto-increment ID of the table as the Business ID as much as possible), can be divided into services (avoid transaction operations as much as possible, or even allow some acceptable sacrifices in the business, otherwise it is difficult to divide the business in the future), otherwise there is only an embarrassing scene of restructuring. The advantage of doing so is that, even if some wrong designs are not expected to be weekly and there is no time for reconstruction due to various business reasons, the single point of impact can be reduced through expansion and migration, then the optimization will be performed later.

Note that the existence of "killer" SQL statements will invalidate all the above optimizations. For example, you can search, sort, and compute tens of millions of table data without indexes. Therefore, you must enable the slow query log to troubleshoot all slow query statements.

The above are some of the la s for the actual use of mysql. If there are any shortcomings, I hope to add them and gradually improve them.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: