Security experts explain Mysql fault diagnosis process _ MySQL

On the day of Mysql fault diagnosis, security experts attended the MS new product release conference and left the office at noon. when I was on my way to the conference room in a taxi, my colleagues called to say that the primary database had a write protection error.

This is incredible. all applications rely on this database. in my mind, do not miss anything. Otherwise, you will not be able to participate! So I told my colleagues on the phone to restart the mysql database and try again. Fortunately, the problem is solved after the restart.

As soon as the meeting is over, you can quickly find the cause of the fault. Here we will first describe the platform environment and clarify the logical relationship. This application is composed of a web front-end server, a tomcat application server, and a mysql server. all the systems are linux. Your request is sent to the front-end apache server. if the request page is. jsp, apache forwards the request to the tomcat server. tomcat then obtains data from the database or inserts a record into the database. This is a typical layer-3 application logic.

Log on to the mysql server, use the mysql client to connect to the mysql database, and run the command mysql> show processlist. No exception is found, and the load is low. It seems that there is no name here. Next, check the mysql error log and find the following exceptions:

080313 11:25:35 InnoDB: Error: cannot allocate 1064960 bytes
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 1233305429 bytes. Operating system errno: 12
InnoDB: Check if you shoshould increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
080313 11:26:08 [ERROR]/usr/local/mysql/bin/mysqld: Sort aborted
080313 11:26:19 InnoDB: Error: cannot allocate 1064960 bytes
InnoDB: memory with malloc! Total allocated memory

The error message is that the memory is basically exhausted and there is no space available for allocation. This determines what results in a huge load, resulting in the system memory being squeezed out. However, the database server is now stable and cannot be found for any reason.

I got the basic information and stopped for a moment. so I took the mail and sent an alert email. the content of the email was as follows:

* ***** Nagios 2.9 *****

Notification Type: PROBLEM

Service: check_load
Host: tomcat nch100
Address: 61.154.105.100
State: WARNING

Date/Time: Thu Mar 13 10:59:53 CST 2008

Additional Info:

WARNING-load average: 3.94, 8.56, 9.17

The alarm message indicates that the load on the host 61.154.105.100 is too large. This host is a tomcat server. it seems that the problem lies in it. in order to confirm your ideas in the near future, I will check the network traffic.

From the traffic diagram, we can see that the time when abnormal traffic is generated is exactly the same as the time when the alarm information is generated. then we can call our colleagues and ask, "What have you done on 61.154.105.100 ?", A: "an incorrect SQL statement is executed, and the SQL statement is canceled after the problem is found ". So far, find out the cause!