MySQL database service disk IO high problem analysis and optimization

MYSQLDatabase service DiskIOhigh problem analysis and optimization

     in the process of stress testing, if the most direct performance problem caused by the resource use bottleneck is that the business transaction response time is too large,TPSgradually reduced and so on. In the case of problem locating analysis, the most priority is to monitor server resource utilization, such as firstTOPorNmonand so ViewCPU, memory usage, and then in the troubleshootingIOissues, such as networkIO, DiskIOthe problem. if it is a diskIOproblem, the general question isSQLgrammatical problems,MYSQLparameter configuration problem, the server's own hardware bottleneck causesIOPSthroughput rate issues.

today's main explanation MYSQL incorrect configuration of parameters causes the disk to be in high concurrency IO , and the MySQL overall monitoring optimization scheme will be followed by a "How to lightweight MySQL service performance monitoring" article.

1 , turn on disk caused by log trace IO problem

      For example:MySQLThe log includes the error log (errorlog), update the log (Updatelog), the binary log (Binlog), query log (QueryLog), slow query log (Slowquerylog) and other, under normal circumstances, few systems in the production system or in a stress test environment will always open the query log. Because the query log opens, it willMySQLeach of the articles executed inQueryare recorded in the log, the system will bring a largerIOBurden, and the actual benefits are not very large.

2 , SQL Writing problems causes disk IO High

     For example, when you were working on a project, you saw a database diskIOhigh usage rate, front-end query business TransactionsLoadRunnerShow things response time is too long, through the monitoring tool to grab the correspondingSQL, through the planning analysis, found that theSQLused indistinctmultiple tables are associated with large tables and then usedOrder by, the final displayTenData in the intermediate process, using a temporary table and putting the data in a temporary table, the memory is set to a small amount, and then placed on the disk causesIOtoo high.

           remarks Span style= "font-size:16px;font-family: Song Body" >: mysql in execution sql query may use temporary tables, temporary table storage, mysql The memory temp table is created first, but the memory temp table exceeds the configured value, mysql The memory temp table is exported to the disk temporary table;

3 , MYSQL parameter configuration issues

MYSQLThe default configuration performance is low, and you can only incrementally optimize database performance by attempting to adjust the parameter configuration concurrently. .at the end of the year according to the company's requirements to help a banking system to do performance testing, because the test environment hardware resources are limited, I and the company applied for a few outdated notebooks, and then according to the production environment software version and other configuration requirements, to simulate the performance test environment, the basic software includes:MYSQL5.6,centos7.2,TOMCAT7,JDK1.7,Redis. Lenovo is used.L421Notebook whenMYSQLdatabase server,L440whenTomcatApplication Server, stress test toolLoadRunner, concurrent users -, stress testing Business Scenario: User login exit, related ticket information query, electronic bill of exchange transaction process, in the stress test process found that some of the transactions in -when the user is concurrently, the database diskI0usage is high, especially write operations are always high, such as test login exit transactions, monitored database diskIOrate has been high, the following case analysis explained:

Before optimization

During stress testing, database disk IO utilization is greater than 75%, response time 1.6 seconds, database resource usage monitored by nmon, as with figure II:

Figure One:

Figure II

After optimization

Database Server Resource utilization:

Figure Four

Figure Five

1.3 Optimize content

by optimizing innndb and other parameters affecting IO and memory, the performance problem is solved obviously, and the parameters content is optimized, for example: Innodb_write_io_threads, Innodb_read_io_threads,

Innodb_flush_log_at_trx_commi The parameter configuration of InnoDB engine optimization IO subsystem is several.

MySQL database service disk IO high problem analysis and optimization