Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to solve the paralysis of the database of the HIS system. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

System environment

First of all, let's take a look at the system configuration and current situation. Why is this customer classic? That's because this customer has reached the point where it can be slow, and where it shouldn't be, it's slow!

First of all, this is a hospital HIS system, how slow is it? Almost all of the functions are slow, whether it is payment, doctor's order, or prescribing medicine. But the slow phenomenon only occurs in the morning peak!

Let's take a look at the system configuration:

The database version is SQL SERVER 2008R2, the amount of data is about 1 multi-T, the server 64CPU, 128 GB memory, the server only runs the database.

At first glance, the server is really a little old, the amount of data is also large, memory and CPU are obviously not enough!

Database index

So let's take another look at some appearances of the database:

Requests per second:

Statement execution:

Waiting situation:

Waiting time:

CPU metrics:

Some indicators of memory:

Disk queue:

There are still many indicators that will not be displayed one by one.

When you see these basic indicators, what can you see but slow? What's the problem? How to solve it quickly? Can there be an optimization step in front of us?

Optimization stage 1 (general optimization)

Most of the time, the reason why the system is slow should be investigated. Is it so slow when it comes online? That's impossible. The manufacturer can't deliver it at all! So the question is, when did it start to slow down? What adjustments have been made to the system?

At the beginning of a simple survey, I was given less than half a day of research, and the basic problem I learned was that the system had added a lot of functions in the last month, and there were many other system interfaces online!

So directly engage in new functions, new program interface statements? I think this is not the case, from a database practitioner, to see such a system must first solve the problem of large-scale waiting! From personal experience, many systems waiting for a large area to solve the system will be greatly improved and improved!

At the beginning of the stage of cooperating with some conventional tuning methods, it mainly creates indexes with high overhead and high impact on a large area of the system, adjusts system parameters, optimizes tempDB, starts snapshot reading, etc.

Expected:

Generally speaking, the previous round of optimization of the system will be significantly improved. I think the system will be significantly faster after this round, the statement CPU will drop to about 70%, and the memory pressure will be reduced.

Results:

Confident, I went to various departments the next day. Some functions are still timed out or slow. CPU is still over 90%, and the memory pressure is still obvious. However, according to the collected data, the number of long-term statements has been greatly reduced, and the system is waiting for congestion to improve significantly.

Before optimization

After optimization

Before optimization

After optimization

Optimization phase 2 (for statements)

Once again, we analyze the system to solve the large area statement blocking, and find that the current situation is mainly as follows:

IO pressure due to insufficient memory.

The system CPU is still high.

Some function statements are still slow and consume a lot of resources.

Investigate the system again:

Which functions are slow and what statements are executed.

The interface statement of the system is a problem.

What other statements in the system that consume high resources and whether they can be optimized?

After the research, I encountered the most common problem: slow sentences due to the program! When many people see this, they will say that the program can be changed as soon as it is slow, so what's the problem? The problem is that you do the optimization directly and tell other developers that your program is too bad and must be changed! How would you react if you were a program developer?

He will say: sorry, the influence is too big to change!

Then the optimization project will fail, or you may have to pay a higher price to get around this problem.

In the analysis, it is found that the program uses a large number of custom functions, and anyone with some experience should know that there is no way to use an index to find a statement using a function on a filtered column, which is relative to hundreds or even tens of millions of tables with single table data. what a disaster! But can not rashly highlight the modification of the program, then how can it be optimized? After a general analysis, it is concluded that the program is mainly consumed in several parts:

The sentences of some business functions are slow.

Interface statements are slow (mainly views, which can be called by other programs).

And the reporting program.

If the program cannot be changed, try to add the plan wizard to change the execution of the statement.

Modify the interface view for the second part, including replacing functions, adding indexes, etc.

For the third part of the report, this thing can not be optimized in a short time, so add a snapshot to the original mirror scheme to achieve simple read-write separation and direct separation.

The effect of statement optimization:

Before optimization

After optimization

Before optimization

After optimization

Expected:

90% of the statements with high consumption have been optimized, the system should be able to get up quickly, and the CPU and memory indicators should be normal!

Results:

Statement consumption and time have come down, the system card slow phenomenon has been significantly improved, but CPU is still more than 90%, memory pressure is still obvious, disk queue is still very high! System performance problems still exist.

Optimization stage 3 (in-depth index analysis)

After the first two stages of optimization, the general department will be significantly improved, and the index is normal, which is also mentioned earlier, slow has been solved, so why the CPU, memory pressure has not been alleviated? Is it true that 64CPU and 128 GB of memory can't be supported? Need to add memory for CPU? Do you want to do load balancing? All kinds of splits?

CPU analysis

First of all, I analyzed the CPU pressure, combining the CPU consumption of statements and the appearance of CPU, a large part of it should not be consumed by statement execution! So it is true that there are no other programs on the server, where are the CPU resources?

Look at this counter:

The peak period of compilation times of SQL reaches more than 2000 times per second! It has been written in many books, and I believe many viewers also know that non-parameterization of sentences will cause pressure on CPU. This is a vivid example! Then the solution is also relatively rough, the program can not be modified, then turn on the forced parameterization on the database.

Take a look at the effect:

I don't think there's anything more to say!

Memory analysis

If you see the phenomenon of CPU, then the memory problem can also be solved. With so many compiled ad hoc queries, first of all, take a look at the data cached in memory:

SQLOPTIMIZER Singlepage accounts for more than 80 gigabytes, while the cache in the query data page is only 20 gigabytes, and it is still being compressed, so it is strange that there is no pressure on memory! The SQLOPTIMIZER Singlepage tried and could not be released through the operation of DBCC FREExxxxx, so the SQL service was restarted directly in the middle of the night! The SQL service that has not been restarted for nearly 2 years is just in my hands!

Restart the back page life cycle:

Memory problem, I do not know if it is a small BUG of Microsoft, query plan cache personal understanding will not always squeeze the data cache, the customer's database does not have a patch, but refer to the 08 patch also did not find the related problem fix.

Also ask the friends who have met or know to give me some tips!

Expected:

Statements have been optimized, blocking has been resolved, CPU, memory, disk pressure is gone, the system must be getting up soon!

Results:

The system is getting up!

Summary

The article only briefly describes the optimization process of a hospital's HIS system, of course, the work of a week only through an article to write the details of the whole process must not be so detailed, but also hope to see the officials forgive me!

The whole optimization process is that the program only modifies 2 statements, and the rest is completed by database optimization. And did not add any hardware resources! (link to the tool used in this article: http://www.grqsh.com/product_Expert.html)

The optimization process is mainly divided into:

Overall survey of the system: slow communication with department users, recent changes in the system, and data collection.

General optimization: adjust the configuration of database parameters, add indexes, and solve blocking.

Research again: system slow function, slow sentence.

For sentence optimization: insufficient writing, missing index, prompt, planning wizard, etc.

Is the overall pressure relieved: if there is still a lot of pressure to find a bottleneck, can it be solved? If it can not be solved, consider adding hardware or choosing separation, separation and other options.

After reading the above, do you have any further understanding of how to solve the database paralysis of HIS system? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.