Emergency Measures to Prevent database from being killed

After the development is launched, the concurrency pressure will be high in the morning, and the database may be killed due to an SQL problem. For details, refer to "one quotation mark causes the website to fail for one hour". at this time, you can use pt-kill to kill,

After the development is launched, the concurrency pressure will be high in the morning, and the database may be killed due to an SQL problem. For details, refer to "one quotation mark causes the website to fail for one hour". at this time, you can use pt-kill to kill,

After the development is launched, the concurrency pressure will be high in the morning, and the database may be killed due to an SQL problem. For details, refer to "one quotation mark causes the website to fail for one hour". in this case, you can use pt-kill to kill the virus, but the number of connections is too high and the CPU load is very high. This tool cannot be processed at all. The database is paralyzed at this time. What should I do?

First, make sure that the database cannot be killed. First, let the database pressure drop. Here, we need to make some restrictions.

We first use iptables to block port 3306, first reduce the pressure, and then tail-n 100 mysql. slow to view the slow log, find the time-consuming SQL statement, and notify the development.

#! /Bin/bash # ensure that the local machine can access MySQLiptables-s localhost-a input-p tcp -- dport 3306-j ACCEPT # ensure that the monitoring machine can access MySQL to avoid HA switching. Iptables-s -a input-p tcp -- dport 3306-j ACCEPT # block all IP addresses of application connections iptables-a input-p tcp -- dport 3306-j DROP

Next, we will modify the parameter to limit the number of concurrent database connections and InnoDB concurrent request processing.

Set global max_connections = 210; set global max_user_connections = 200; set global innodb_thread_concurrency = 8;

Then open port 3306 in iptables-F to observe the running status and CPU status of the database. If the pressure is still high, reduce the number of connections and finally wait for the development of the rollback code, ensure that the database is not down.

This article is from the "hechun's technical column" blog. Be sure to keep this source