MySQL Server IO 100% Analysis and Optimization Solution

Preface

During the stress test, if the most direct performance problem caused by resource usage bottlenecks is that the business transaction response time is too long and the TPS gradually decreases, etc. In problem location analysis, the top priority is usually to monitor server resource utilization, such as using TOP or nmon to check CPU and memory usage, and then troubleshoot IO problems, such as network IO and disk IO problems. If it is a disk IO problem, the general problem is SQL syntax problem, MYSQL parameter configuration problem, or the server's own hardware bottleneck leading to IOPS throughput rate problem.

This article mainly introduces the analysis and optimization plan of MySQL server IO 100%. Let's take a look at the detailed introduction.

【question】

There is a MySQL 5.6.21 database instance that is mainly write-based, and the IO %util is close to 100%

High write IOPS

【Analysis process】

1. Through the iotop tool, you can see the mysql thread with the highest current IO consumption

2. Check the stack of thread 49342, and you can see that the redo log is being refreshed, corresponding to file 9.

3. File No. 9 corresponds to the first file of the redo log

Why does the MySQL process frequently refresh the redo log file? We need to analyze it in combination with the redo log flushing strategy. The key is the innodb_flush_log_at_trx_commit parameter.

The default value is 1, which is the safest. However, it may also have a significant performance impact when write pressure is high. Each time a transaction is committed, MySQL writes the data in the log buffer to the log file and flushes it to disk.

Considering the write scenario of this cluster, most of the writes are small transactions, and each transaction commit triggers a disk flush. In this scenario, the optimization effect of increasing innodb_log_buffer_size and innodb_log_file_size is not obvious.

【Optimization plan】

1. At the application level, for systems with heavy write pressure, a single insert statement can be optimized into a small batch of insert statements. This will reduce the number of transaction commits and redo log flushes, and theoretically improve performance.

2. At the MySQL level, for log-type systems, if a small amount of data loss is allowed in the event of downtime, the innodb_flush_log_at_trx_commit parameter can be adjusted to 2.

When set to 2, only write operations are performed when the transaction is committed, and only the system page cache is guaranteed to be written. Therefore, transactions will not be lost if the instance crashes, but transactions may be lost if the system crashes.

In the test on this server, when the parameter is adjusted to 2, the IO request drops from 200M/S to about 10M/S, and the pressure is reduced by more than 10 times.

3. At the system level, replace the disk with a better performance

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.