Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the analysis of MYSQL service disk IO high problems and optimization countermeasures, the knowledge here is slightly different from books, are professional and technical personnel in the process of contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

High problem Analysis and Optimization of MYSQL Database Service disk IO

In the process of stress testing, the most direct performance problems caused by bottlenecks in the use of resources are the large response time of business transactions and the gradual decrease of TPS. In general, the top priority for problem location analysis is to monitor the utilization of CVM resources. For example, use TOP or nmon to check the CPU and memory usage, and then troubleshoot IO problems, such as network IO and disk IO. If it is a disk IO problem, the general problem is the SQL syntax problem, the MYSQL parameter configuration problem, and the IOPS throughput problem caused by the hardware bottleneck of the server itself.

Today, the main purpose of this paper is to explain the disk IO problem caused by unreasonable MYSQL parameter configuration, and the article "how to lighten MYSQL service performance monitoring" will be sorted out after the optimization scheme of MYSQL overall monitoring.

1. Disk IO problems caused by opening log trace

For example: MySQL log includes error log (ErrorLog), update log (UpdateLog), binary log (Binlog), query log (QueryLog), slow query log (SlowQueryLog), etc. Under normal circumstances, in the production system or stress test environment, few systems will open the query log all the time. Because after the query log is opened, every Query executed in the MySQL will be recorded in the log, which will bring a greater IO burden to the system, but the actual benefit is not very great.

2. The problem of SQL writing results in high IO of disk.

For example, when I was working on a certain project, I saw that the utilization rate of database disk IO was on the high side, and the front-end query business transaction loadrunner showed that the response time of things was too long, and the corresponding SQL was crawled by monitoring tools. Through plan analysis, it was found that distinct was used in the SQL with multiple tables associated with large tables, and then order by was used to display 10 pieces of data, while temporary tables were used to generate intermediate process data for filtering. And put the data into the temporary table, the memory is just not set up, so put it on disk, resulting in high IO.

Note: MySQL may use temporary tables and store temporary tables when executing SQL queries. MySQL will first create memory temporary tables, but when memory temporary tables exceed the value specified by the configuration, MySQL will export memory temporary tables to disk temporary tables.

3. MYSQL parameter configuration

MYSQL default configuration performance is poor, can only try to adjust the parameter configuration to gradually optimize database performance. At the end of 2017, according to the company's requirements to help a banking business system do performance testing, because the test environment hardware resources are limited, I applied with the company for several outdated notebooks, and then according to the production environment software version and other configuration requirements, to simulate and build a performance test environment The basic software includes: MYSQL5.6, centos7.2, tomcat7, JDK1.7, redis. Lenovo L421 notebook is used when MYSQL database server, L440 when tomcat application server, stress test tool loadrunner, concurrent user 100, stress test business scenario: user login and exit, related bill information query, electronic money order transaction process, etc. During the stress test, it is found that when some transactions are concurrent among 50 users, the utilization rate of database disk I0 is high, especially the write operation has been very high. For example, for testing login and exit transactions, the disk IO rate of the monitoring database has been on the high side, as described in the following case study:

Before optimization

During the stress test, the database disk IO utilization is more than 75%, and the response time is 1.6 seconds. The database resource usage monitored by NMON is shown in figure 1 and figure 2 below:

Figure 1:

Figure 2

After optimization

Database server resource utilization:

Figure 4.

Figure 5

1.3 optimize content

After optimizing some parameters such as innndb that affect IO and memory, the performance problem is obviously solved, and the contents of the parameters are optimized, such as innodb_write_io_threads, innodb_read_io_threads,

Innodb_flush_log_at_trx_commi and other InnoDB engines optimize the parameter configuration of IO subsystem.

For the above analysis of MYSQL service disk IO high problems and optimization countermeasures, if you need to know more, you can continue to pay attention to the innovation of our industry, if you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.