A text teaches you database performance tuning (attached to a large hospital real case)

a text teaches you database performance tuning (attached to a large hospital real case) Preface

An engineer at Microsoft once had a very figurative metaphor for performance tuning: Peeling onions. I also very much agree, let us to a layer of a layer of the outside its mysterious veil.

six major factors

The following are the most common diagrams we use to analyze database performance issues for our customers.

After reading this picture, you have a basic concept of performance tuning. In general, we will analyze them in the following order:

Hardware capabilities

System size

Database internal factors

Software Environment

These 4 order can be adjusted or exchanged, but for the performance optimization of the system must start from the global. Never go deep into the optimization of an SQL statement, because you might spend a lot of time optimizing a SQL from 20s to 1s, but the entire system's card slow still exists.

The last is the business model and architecture, code design.

Practical Cases

No nonsense, open the whole, directly on the dry.

Date: January 2018 One day

Event: A hospital customer 4 o'clock in the afternoon suddenly appear large area of card slow. The whole system has serious problems, information center phone explosion, hospital engineers unprepared.

Fortunately we have a ' camera ' in the database, and the following is a look at what is sent from the surveillance video. And then solve them.

Hardware Capabilities
CPU

CPU usage is below 20% in the time period of the problem, normal.

Memory

The memory usage is normal, as shown in the image below.

Page life cycle

Available memory

IO

Io queue average is very low, about 15.48 has a instantaneous high, you can note that this period of time there is no batch write.

Overall, hardware resources are sufficient.

System Size

When the problem occurs, the batch request per second is not an upward trend, but it has declined. This is because the system congestion, waiting, affecting the system's throughput.

Database internal factors
wait

Slow Statement

From the trend graph of sessions and slow statements, it is possible to see that the time of the problem and the customer's description are exactly the same, and we can conclude that the accident is indeed slow in the database.
What causes the slow

Check the time period of the statement in the run, you can find about 15.58 in the afternoon, the database began to appear more and more cmemthread waiting.

Up to 1900 pages and 16.08 minutes, there were up to 100 concurrent simultaneous occurrences of cmemthread waiting

what is cmemthread waiting

Microsoft's official Description: Occurs when a task is waiting for a thread-safe memory object. The wait time may increase when multiple tasks attempt to allocate memory from the same memory object to cause contention.

This description is very obscure, and it is still completely unknown how the wait type is going to be, and how to deal with such problems.

In fact, the official description is a memory contention problem, but in fact the key to this problem is the contention of multiple tasks, which is actually the problem of concurrent execution.
Scene