Discussion on the high load of a single MySQL Server

A friend asked me, "Hi, I want to ask what urgent measures do you take when the single-host load is too high? "

I asked him if he was a MySQL db server?

 

He said yes.

I give him the following suggestions:

1. Check whether the mysqld process causes high load?

2. If yes, check whether the current thread has a slow SQL statement.

 

My friend asked: Well, none of them. What if load is high due to business reasons?

 

I give my own suggestions:

1. The concurrency is too high.

2. The business reason is that the crontab task can be stopped.

 

My friend asked again: It's not the reason for SQL. Generally, the load (high) on DB machines is caused by Io and CPU. Most of these problems are caused by busy business processing.

My reply:

1. I haven't met the io cpu burst caused by slow SQL statements. It may be because our standalone server has a very good performance. [Be Honest, especially for technical discussions]

2 If I/O or CPU is used, there will also be transaction operations.

3. Check whether the database parameter settings are reasonable. If the parameters are reasonable, it is estimated that the performance bottleneck of a single machine is reached.

 

My friend asked: I have never met this, so I have never encountered a DB machine that needs to handle load High?

 

My reply:

Many of them occur, most of which are caused by slow SQL statements.

 

My friend asked: Well, I'm not in a rush?

 

My reply:

1. Well, no. We will perform stress testing and peak value testing before applying the application in advance.

2. The business volume will not exceed the estimated peak value.

3. Your single load is too high. Aren't multiple MySQL databases currently? Distributed, read/write load balancing, one load is too high, what about the other?

 

My friend asked: I suppose that a business suddenly has a high traffic volume (more than previously estimated) One day. At this time, I only access one master, but the business certainly cannot be stopped. The machine obviously cannot handle it, is there any way to handle the sharp increase in load? We don't mean it's distributed. Now all distributed systems have Server Load balancer, which is very nice to handle.

 

My reply:

1. At this time, the business cannot be stopped, and single-host hardware upgrade is not a feasible solution.

2. If you find that the CPU usage is too high, prepare a new database machine immediately. If the new database machine is ready before the explosion of a single database, add it directly to relieve the app access pressure.

3. This operation will not affect the business. We generally use VIP. It is very convenient to add a DB under VIP.

 

My friend asked again: I was wondering if this problem was really hard to handle?

 

I am wondering: Why are you not using VIP to access the DB?

The friend said: No, they all directly write the real IP address to connect to the application.

 

Speaking of this, if there is no VIP and it is a single dB, it will crash instantly. I can only say that my level is limited and there is no other trick for the moment. I only need to suspend the business and re-architecture the DB.

However, I still give him some suggestions and hope to bring him some help groups:

1. perform DB stress testing and peak testing before the new app.

2. Make the db ha solution and pass the test.

The IP address of the 3 dB app must be handled in a way similar to that of the VIP to facilitate expansion.

4 dB should be backed up, and check the backup validity at any time.

 

You have encountered a similar situation and have good experience. Please share it.