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

Preface

Doing a good job of daily inspection is an important step in database management and maintenance, and it is necessary to register the date and result of each inspection, and a patrol report may be needed at the same time.

This series aims to solve some common problems:

I don't know what to inspect.

I don't know how to get a convenient physical examination.

Too many machines are troublesome for physical examination.

It is difficult to generate a report and cannot present the results visually.

Performance is one of the important indicators of the system, and the performance problem may be caused by a small point (such as parameter configuration, job configuration, structure design, software and hardware environment, etc.). The previous seven articles have inspected the system from different aspects, and this article will introduce the most important item (performance) in the series of inspections.

Performance baseline

There are many indicators of performance inspection, and the situation changes greatly, so we should establish a complete performance evaluation model for our own system. What is the performance evaluation model, that is, when the system enters a stable period and has been optimized, the system performance has reached a stable value, so we can use this stable project as the baseline for later patrol inspection, function launch, structure change and other operational performance comparison.

On the "physical examination items" page, we can use the historical version of a relatively stable physical examination project as the performance baseline, and in order to distinguish other projects, we can rename it to "performance baseline".

Comparison of overall indicators based on performance baseline

On the "Overview" page, we can view the performance status of the system as a whole, mainly from [query statement-execution time], [session-wait type], [check items], and compare it with the baseline. Check to see if the indicators in the inspection are quite different from the baseline indicators.

For example, when the number of slow statements in each time period in [query statement-execution time] is significantly higher than the baseline, it can indicate that the statements in the system may become slower. The pressure of CPU, memory, IO and other system indicators increases, indicating that the pressure on your system increases, which may be due to problems with new unoptimized statements or hardware resources.

Note: the collection time of baseline and patrol inspection should be the same or slightly different. If there is a big difference, it may not be illustrative.

Specific index counter

System counter

Database counter

Query statement

During the inspection, we can mainly focus on:

Number of slow sentences

Number of execution top 10

Duration top 10

CPU consumes top 10

Number of reads top 10

Number of writes top 10

Number of rows affected top 10

System waiting and blocking

During the inspection, we can mainly focus on:

The type and quantity distribution of waiting

Waiting time distribution

Number of waits top 10

Wait time top 10

CPU time top 10

Number of logical reads top 10

Number of physical reads top 10

Physics write top 10

Other indicators

There are many performance inspection indicators of the database, which can be selected according to the situation of your own system. Specific indicators and optimization methods can be found in: performance diagnosis series.

Summary

Some of the previous inspection items and configuration items, in the management of a more standardized system, generally there will not be any problems, and the performance is different, this is an item affected by many factors, and more prone to problems. At the same time, the performance of the total degree of the system is self-evident, to do a good job in front of the inspection is also a guarantee of stable performance.

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.