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

This article mainly explains the "Analysis of database instance performance tuning sharp device Performance Insights", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "analyze database instance performance tuning sharp device Performance Insights"!

What is Performance Insights?

Aliyun RDS Performance Insights is a powerful tool of RDS CloudDBA products that focuses on performance tuning, load monitoring and correlation analysis of user database instances. It helps users quickly evaluate database load, source of resource waiting and corresponding SQL query statements in a simple and intuitive way, so as to guide users when, where and what actions to take to optimize data performance.

Several nouns explain Performance Insights: Chinese translation is called performance insight.

Active Session (AS): the number of active sessions in a RDS database system.

Average Active Session (AAS): the average number of active sessions in the RDS database over time.

The maximum number of CPU Cores that can be used by Max Vcores:RDS database instances.

AAS and MaxVcores to quantify system bottlenecks

At the beginning of the article, we hope to be able to explain a very important question: why can we use AAS (average number of active sessions) and RDS database instance MaxVcores as the basis for judging the bottleneck of the system? Our reasons are:

First of all, in the RDS database system, we think that the most important resource is the CPU resource, because all other resources need to be scheduled by CPU.

Secondly, the concurrent processing ability of CPU is related to the number of CPU Cores. Suppose that on a fairly small time slice, the bottleneck in CPU's ability to handle active sessions (AS) is the number of CPU Cores. That is, CPU can handle a maximum of active sessions equal to the number of Cores at the same time.

Therefore, we can use the quantitative comparison between the average number of active sessions (AAS) and the number of MaxVcores in the RDS database system as an important basis to determine whether there is a bottleneck in the system.

What can Performance Insights do?

Aliyun RDS Performance Insights can help our users quickly, easily and directly discover database instance load and SQL statements that cause performance problems. At present, the Performance Insights page carries our product ideas in three aspects:

Trend chart of key performance indicators: trend chart of key resource utilization.

Real-time AAS trend chart: the real-time trend of the average active session (Average Active Sessions) in the database instance.

Multi-dimensional load information: displays multi-dimensional instance load information.

Critical resource utilization trend chart

The trend chart of Aliyun's RDS Performance Insights key performance metrics can help customers find the source of instance load from a macro point of view, such as: is the CPU resource tight and the IOPS is too high? Is it that the network is too expensive, or is the number of active connections full?

Real-time AAS change trend chart

From the critical resource utilization trend chart section, we have a rough idea of the source of the instance load. Next, with this question in mind, let's take a look at the resource waiting for active sessions in the current example. So, at this point we can come to the second part of the page: the real-time AAS trend map.

From the real-time AAS trend diagram in Performance Insights, we can clearly find the resource waiting in the RDS instance. For example, in the picture above, we can analyze the following important information:

Between 10:25 and 10:57, the average number of active sessions is much larger than the number of instance CPU Cores 24 (several points are lower than CPU Cores), indicating that the database has faced a big system bottleneck.

From the AAS change trend chart, we are almost waiting for the resources marked in blue, that is, CPU resources.

Thus it can be seen that using the real-time AAS change trend diagram in Performance Insights, we can find the source of user RDS instance load, when and where resources are waiting, and the rule of change.

Multi-dimensional load details

Through the real-time AAS change trend diagram in Performance Insights, we have mastered the source of instance load, resource waiting and the law of change, and then the question that users should be most concerned about is: what are the specific query statements that cause the load of these instances? Which user caused it? Which connects to the host client? Which application database? We can use the multi-dimensional load information section to answer this series of questions.

From the bottom half of the screenshot above, we can easily find out the SQL query statements corresponding to the load associated with the changing trend of AAS, as well as the comparison of the contribution of each statement to AAS. Of course, you can also switch to Waits,Users,Hosts,Commands,Databases and Status according to your needs, which represent resource waiting, user, client host, command type, database, process status and other dimensions.

Performance Insights architecture

After understanding what Aliyun RDS Performance Insights can do, let's take a look at Performance Insights's design architecture diagram, which is briefly summarized in five words: four layers and two links.

Four-tier architecture

From top to bottom, the four-tier architecture of RDS Performance Insights is as follows:

Application layer: the front-end users are visible, carrying the ideas and logic of our products, and it is the product presentation visible to the end users.

Service layer: each system API coordinates to provide application data services for the application layer. Our product is the main business logic processing layer.

Data layer: real-time data processing platform, statistical summary, data flattening, real-time calculation, and finally persisted to the meta-database to provide data for the service layer.

Acquisition layer: collect valuable basic data from RDS instances and input data for the data layer.

Two links

From the perspective of data links, Performance Insights has two links:

Access link: data top-down request access, bottom-up data return.

Collection link: the whole life process of data from production to consumption, from statistical summary to final storage.

Typical case

Here are two typical cases to see how Performance Insights can help us diagnose and analyze database system bottlenecks, resource waiting and SQL queries.

Why is CPU 100%?

XXX point-in-time SQL query slows down

Why is CPU 100%?

In our years of expert service, the most common user question is "Why is my CPU 100%?" let's see how Performance Insights solves this problem.

Performance Insights screenshot

The following is a screenshot of the RDS instance and the Performance Insights page.

Analysis.

We analyze the following problems from the screenshot of the Performance Insights page:

In terms of resource utilization, CPU utilization and the number of active sessions increased significantly between 09:59 and 10:05. CPU utilization reached 100%, active session (Active Sessions) reached 400 +

In the changing trend of AAS, it is found that during this period, the system bottleneck is mainly concentrated in the waiting of CPU and Lock resources, and the total number of Active Sessions far exceeds that of CPU Cores (the CPU Cores of the instance is 16). There is a serious system bottleneck.

SQL statement details: it is very clear to see that the first SQL query statement is waiting for CPU resources, reaching 96 active sessions; the second is Lock resource waiting, reaching 79 sessions. You can click on the SQL statement to view the details.

XXX point-in-time SQL query slows down

In addition, one of the questions that users often encounter is "Why is my SQL query statement suddenly slow?"

Performance Insights screenshot

The user feedback of a RDS instance is around 16:05. The Update statement, which was originally executed very quickly, suddenly becomes very slow and returns to normal around 16:08. Here is the RDS instance.

From Performance Insights, we can analyze:

From the AAS trend chart, it is found that from about 16:05: 50 seconds, the system has a large number of active sessions waiting for Lock resources (the orange color area in the figure), reaching 33, far exceeding the number of CPU Cores.

From the SQL query at the bottom of the screenshot, it is found that the SQL statement waiting for the Lock resource (the first one, marked in orange) happens to be the Update operation statement in which the user complains about slowing down. So we can quickly conclude that the reason why this Update slows down is that the resource is locked, which leads to a long time waiting for the lock resource to be released, which lengthens the execution time, that is, the Update statement becomes slower.

From the AAS trend diagram, it is found that at about 16:07:20, the active process waiting for the lock resource disappears, and the performance of the Update statement returns to normal, indicating that the lock resource has been released.

Above, we can see from two specific user cases that Performance Insights can help users diagnose problems simply, easily and joyfully, correlate and analyze system bottlenecks, resource waiting and SQL queries, and achieve very good results.

The Future of Performance Insights

With the release of the first phase of Aliyun RDS Performance Insights, we have been able to help users quickly find RDS instance performance problems and specific SQL queries that cause performance problems. However, this is far from enough, we also need to help our customers solve problems automatically and intelligently.

From "what is" to "Why"

Currently, after users find the specific query SQL statement that causes performance problems through Aliyun RDS Performance Insights, the next natural question is, why does this query statement cause performance problems? Is the necessary index missing? Statistics tilt? Query data type conversion? Non-SARG query and so on? Next, we need to explore in depth why SQL causes performance problems.

From "Why" to "what to do"

When the user knows why there is a performance problem with the SQL statement, the next question is: what can I do to solve the performance problem? We need to tell the user exactly what to do to solve the performance problem.

From "what to do" to "automatic"

As users can solve the performance problems of SQL statements, the next most urgent need for users is: can Aliyun help us to find out in advance, intelligently and automatically solve these similar problems?

Thank you for your reading, the above is the content of "analyzing database instance performance tuning sharpener Performance Insights". After the study of this article, I believe you have a deeper understanding of the problem of analyzing database instance performance tuning sharpener Performance Insights, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.