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

Analysis and Optimization of CPU High problems in MYSQL Database Service

MySQL service performance monitoring analysis and optimization is an eternal topic, as performance testers sometimes have to stand on the DBA point of view for appropriate analysis and optimization, which is also the way for performance testers to survive and develop for a long time. The monitoring and analysis of the use of resources is the primary task of performance fault analysis. Within the database server, if the operation performed is severely affected by any of the memory, CPU, or disk throughput, it can be considered a bottleneck.

Therefore, it is a very valuable and meaningful activity to understand how the server runs and where resource depletion is used to diagnose problems, as shown in the following examples.

These monitoring analysis optimization methods and other details we will conduct practical testing, monitoring and analysis, practical understanding and learning, especially where the resource utilization problems are spent, in the performance actual combat class of the college, in order to improve students' awareness of performance monitoring and analysis.

1. Identify bottlenecks

When analyzing performance problems, you first need to identify the bottleneck, and then resolve the bottleneck. There are usually two reasons for bottlenecks, one is due to misconfiguration, the other is to meet the performance or scalability limitations of software or hardware, such as SQL syntax problems.

2. Identify the bottleneck of CPU

In the previous chapter, we talked about "MYSQL database service disk IO high problem analysis and optimization" mentioned IO problem fault analysis and solution, and this chapter is mainly to explain CPU problem location analysis and solution.

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

3. Analysis of the root causes of bottleneck.

MYSQL itself often leads to high CPU utilization, including inefficient queries, hash joins or multi-table merge joins, unreasonable parameter settings, and so on.

4. The case is illustrated as follows

In the test scenario, during the stress test, when a bank system logs in and exits, SQL query is required because users need to query the corresponding customer-related transaction information. When LR concurrently has 100 users, the response time is more than 5 seconds, and a timeout error message is found by manual login. At this time, the CPU utilization is monitored by top command to exceed 90%, as shown in the following figure:

4.1 Front-end page response timed out:

4.2 Database server resource utilization

4.3Analysis of LR response time index

4.4 MYSQL syntax analysis

During the monitoring process, it is found that several SQL grammars are scanned in full table mode, resulting in high response time and CPU utilization. One of the SQL is as follows

4.6. Optimization method

After indexing the fields that need to be retrieved from the table, the performance indicators are shown in the following figure

At this time, 100 users are also concurrent, as shown in the following chart of response time before and after indexing:

SQL retrieves the data path:

It is found that although the index is established and the response time is reduced to less than 2 seconds, the cpu resource utilization of the database server is still more than 70%, which is on the high side. It is found that the cache hit rate is not high. After properly resizing for query_cache, the cpu utilization of mysql database is less than 30% and the response time is less than 1 second, as shown in the following figure.

Response time metrics are as follows:

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.