Discussion on the high load of a single MySQL Server

A friend of the MySQL Single-host load was too high. He asked me, "hi, I want to ask what emergency 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. Ask: Well, no. If load is high due to business reasons, I will give my own suggestions: 1. The concurrency is too high. 2. The business reason is the crontab task. You can stop it when you stop it. ask another question: it is not the reason for SQL. Generally, the database machine load is high because of io, most of these results from cpu usage are due to busy business processing. However, I replied: 1 well, I have not encountered any increase in I/O cpu because it is not slow SQL, it may be because our standalone server has a very good performance. [Be Honest, especially for technical discussions] 2. For io and cpu, there are transaction operations. 3. Check whether the database parameter settings are reasonable, if the parameter is reasonable, it is estimated that it is the bottleneck of the single-host performance. A friend asked: I have never encountered any problems .... so I have never encountered any problems that need to be handled by the load high of the db machine? My reply: I have encountered a lot of problems, most of which are caused by slow SQL statements. Q: Well, I'm not in a rush? My reply: 1. Well, no. We will perform stress tests before we apply them in advance. Peak Test 2 will not exceed the estimated peak value. 3. Your single load is too high, aren't all mysql instances? 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. The business cannot be stopped at this time, and the 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, hoping to bring him some help groups: 1. Before the new app, do a good job of db stress testing and peak testing. 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.