How to check hang of mysqld instance service

Many technical friends are confused about the dual-master replication mode of the mysql database architecture. How can I accurately determine whether the mysqld service can provide services normally and whether the service can be automatically determined and switched to the host? At the same time, the detection mechanism for mysqld services requires less resource consumption, simple and accurate judgment, and low development and maintenance costs. We have also made mistakes in the actual production environment detection process. For this reason, I wrote a short article to share my experiences, ideas, and practices with you, answer questions for more technical friends.

To automatically switch the master-slave server that provides Database Service requests, you must determine whether the mysql database instance in the dual-master replication architecture can normally provide service requests, the biggest headache is the hang of mysqld service. What is the appearance of mysqld hang? First, list the situations that have occurred to me and my friends in the circle:

● Modification operations on objects or data in the database cannot be performed, but query operations can be performed normally;

● Ability to query the objects or data of the system database (Remarks: mysql, information_schema), and not the objects and data of non-system databases;

● Only objects and data of a virtual database (Remark: information_schema) can be queried. Objects and data of other databases cannot be queried;

● You cannot query the objects or data of any database, but you can execute show processlist;

● You cannot query objects or data in any database or execute show processlist. However, you can perform some SHOW operations, such as show status;

● Other status information that has not been found;

Based on the above analysis and summary of the hang of mysqld service, we can find that there are some common features, which are summarized as follows:

● The mysqld service exists and can be pinged or telnet;

● The client can accept the requests sent from the client, but does not continue to process them. Instead, it stays in the current SQL Execution status where hang occurs;

● If you can execute show processlist, you can see that all SQL Execution statuses remain unchanged;

● The LOAD on the database server will suddenly drop, or even drop to 0, and the CPU and IO will be close to the LOAD-free status;

● When the mysqld service is hang, it is generally unable to modify the object or data of the database;

This document describes how mysqld accepts and processes service requests and database server status information when the mysqld service hang is in use. Since these features can be found, to check whether the mysqld service is still active or whether the network is connected, follow these steps:

● Ping or telnet the mysqld service port;

● Run the SHOW command;

● Perform the SELECT query operation;

Can the above three types of Detection Methods truly achieve accurate detection? The answer is: NO. You can only accurately monitor whether the mysqld process is alive, whether the network between the program and the database server is smooth, and whether the mysqld service can normally receive and complete processing requests, we cannot do this or partially. Based on the above analysis information and the effect of our nearly three years of implementation, we can modify the data in the database, it is the safest way to combine the program's judgment logic for data modification operations. The detailed steps are as follows:

● Detection frequency: perform an UPDATE operation on the detection table on the mysqld database instance that currently provides services every 10 S to check whether the database instance provides services normally;

● If the last database instance service detection operation does not normally return the UPDATE information, the database detection table UPDATE operation is performed every 1 s for a total of 2 tests;

● If the detection of the database instance service in the first two steps is complete and the database instance service currently providing the service has not recovered, UPDATE the database detection table every 5 ms for a total of three times, if no response is returned, the database instance cannot receive service requests normally;

The table structure and UPDATE operation SQL used to perform database instance service detection are:

 
 

  
  
CREATE TABLE monitor_db(  
  
  
      ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMNET,  
  
  
      CreateDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',  
  
  
      PRIMARY KEY(ID)  
  
  
      )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';  
  
  
INSERT INTO monitor_db VALUES(1,NOW()),(2,DATE_ADD(NOW(),INTERVAL -1 DAY)) 
 
 

● Update SQL statements for MySQL and earlier versions

 
 

  
  
UPDATE monitor_db SET CreateDate =NOW() WHERE ID=1; 
 
 

● Update SQL statements for MySQL and later versions

 
 

  
  
SET SESSION sql_log_bin=0;  
  
  
UPDATE monitor_db SET CreateDate=NOW() WHERE ID=1; 
 
 

Note:

For versions that support the MIXED and ROW replication modes, you must avoid the possibility that the modified data of the SQL statement for the master-slave update operation may be inconsistent during the MySQL dual-master replication process, this causes replication interruption. Therefore, the update operations detected by the database instance service are not recorded in the binary log file, that is, they are not copied to their respective slave servers.

In addition, it is recommended that you create a monitor_db table to the test database or a database similar to the test function. The storage engine must be InnoDB. The detection frequency can be determined based on your own data security requirements, and adjusted to acceptable.

If the mysqld service is hang, the method for closing the mysqld service normally is invalid. Only the mysqld service process is subjected to the kill-9 operation at the operating system level, and then the mysqld service instance is started, after the automatic rollback operation is completed, the startup is successful. We recommend that you do not use mysql5.0.82 or earlier versions. Some bugs may easily cause hang.