MySQL database service CPU high problem analysis and optimization

MYSQLDatabase ServicesCPUhigh problem analysis and optimization

MySQL Service performance monitoring analysis and optimization is the eternal theme, as a test staff to stand in the DBA point of view to conduct appropriate analysis and optimization, which is the performance tester can survive long-term development of the road. The use of resource monitoring and analysis is the fundamental task of performance failure analysis. Inside the database server,you can treat it as a bottleneck if you perform an operation that is severely impacted by any of the memory, CPU, or disk throughput.

Therefore, understanding how the server operates, resource loss in which aspects of the problem of fault diagnosis is very valuable and meaningful activities, the specific cases are as follows.

These monitoring and analysis optimization methods and other details we in the quality of the performance of the school class in the actual practice of real-time test monitoring and analysis of practical understanding of learning, especially the use of resources where the problem is spent, the classroom will operate the project case simulation to explain, to improve the students on performance monitoring and analysis of cognition.

1. Identify bottlenecks

When you analyze a performance issue, you first need to identify the bottleneck and then resolve the bottleneck. There are usually two types of bottlenecks, one is due to misconfiguration, and the other is to limit the performance or scalability of software or hardware, such as SQL syntax issues.

2. Identify CPU bottlenecks

In the previous chapters we talked about the MYSQL database service disk IO high problem analysis and optimization, there is a reference to the IO problem analysis and solution, and this chapter is mainly to explain the CPU problem location analysis and solutions.

CPU time overhead is the most expensive and valuable server resource, and the overall performance of the system is often very sensitive to CPU usage. For example, users can often detect high CPU usage, such as slow response times, timeouts, and so on.

3. bottlenecks root Cause analysis

MYSQL It often results in high CPU usage, including queries that perform poor efficiency, hash joins or multiple table merge joins, unreasonable parameter settings, and so on.

4. The case description is as follows

test scenario, in the stress test a bank system login exit, because the user login needs to query the corresponding customer related transaction information, and need to involve SQL Query, when the LR concurrent with the user, the response time of more than 5 seconds, manual login found a time-out error message, the top command to monitor the CPU Usage rate exceeds 90%, such as:

4.1 Front-End page Response timeout:

4.2 Database Server Resource Usage

analysis of 4.3 LR response time Index

4.4 MYSQL Syntax Analysis

During the monitoring process, we found several SQL syntax is a full-table scan, resulting in a high response time and CPU usage, with one SQL as follows

4.6. Optimization Method

At this point, the table needs to retrieve the fields indexed after the performance indicators such as

This is also a 100 user concurrency, such as the index before and after the response time chart:

SQL To retrieve the data path:

found that although the index, the response time reduced to less than 2 seconds, but the database Server CPU resource utilization is still more than 70%, high, then found that the cache hit rate is not high, appropriate sizing for Query_cache, MySQL database CPU utilization of 30% The following and response times are less than 1 seconds, as in.

The response time metrics are as follows:

MySQL database service CPU high problem analysis and optimization