MySQL Optimization - Detailed explanation of safely shutting down MySQL instances

Shutdown process:

• 1. Initiate shutdown and send SIGTERM signal

• 2. Yes If necessary, create a new shutdown thread

If the shutdown is initiated by the client, a dedicated shutdown thread will be created

If it is received directly If the SIGTERM signal is turned off, a thread specifically responsible for signal processing will be responsible for the shutdown work, or a new independent thread will be created to take charge of this matter

When an independent shutdown thread cannot be created (for example, insufficient memory), MySQL Server will Issue a warning message similar to the following:

Error: Can't create thread to kill server

• 3. MySQL Server no longer responds to new connection requests

Close TCP/IP network monitoring, close Unix Socket and other channels

• 4. Gradually close the current connection and transaction

Idle connections will be terminated immediately;

Currently there are transactions and SQL active connections, they will be marked as killed, and their status will be checked regularly so that they can be closed at the next check; ( Refer to KILL syntax)

If there is currently an active transaction, the transaction will be rolled back. If non-transaction tables are also modified in the transaction, the modified data cannot be rolled back, and only part of the changes may be completed;

If it is the Master in the Master/Slave replication scenario, the processing of the replication thread is the same as that of the ordinary thread;

If it is the Slave in the Master/Slave replication scenario, it will Close the IO and SQL threads in turn. If these two threads are currently active, they will also be marked with the killed mark and then shut down;

On the Slave server, the SQL thread is allowed to directly stop the current SQL operation. (To avoid replication problems), and then close the thread;

In MySQl 5.0.80 and previous versions, if the SQL thread happens to be executing a transaction in the middle, the transaction will be rolled back; from 5.0. Starting from 81, it will wait for all operations to end unless the user initiates a KILL operation.

When the Slave's SQL thread is forced to KILL when performing operations on non-transaction tables, it may cause inconsistency between the Master and Slave data;

• 5. The MySQL Server process is shut down All threads, close all storage engines;

Refresh all table caches, close all open tables;

Each storage engine is responsible for related shutdown operations, such as MyISAM All operations waiting to be written will be flushed; InnoDB will flush the buffer pool to disk (starting from MySQL 5.0.5, if innodb_fast_shutdown is not set to 2), record the current LSN into the table space, and then close all internal thread.

• 6. MySQL Server process exits

About the KILL command

Starting from 5.0, KILL supports specifying two CONNECTION | QUERY Optional options:

• KILL CONNECTION is the same as the original one, stops rolling back the transaction, closes the thread connection, and releases related resources;

• KILL QUERY only stops the operation currently submitted by the thread, and the others remain unchanged;

After submitting the KILL operation, a special kill flag will be set on the thread. It usually takes a while to actually shut down the thread, because the kill flag bit is only checked under specific circumstances:

• 1. When executing a SELECT query, in an ORDER BY or GROUP BY loop , each time after reading some row record blocks, the kill mark bit will be checked. If it is found to exist, the statement will terminate;

• #2. When executing ALTER TABLE, each time from the original table After reading some row record blocks, the kill mark bit will be checked. If it is found to exist, the statement will terminate and delete the temporary table;

• 3. When executing UPDATE and DELETE, every time some row record blocks are read, After the row record block is updated or deleted, the kill flag bit will be checked. If it is found to exist, the statement will terminate and the transaction will be rolled back. If the operation is on a non-transaction table, the changed data will not be rolled back;

• 4. The GET_LOCK() function returns NULL;

• 5. The INSERT DELAY thread will quickly add new records in the memory and then terminate;

• 6. If the current thread holds a table-level lock, it will be released and terminated;

• 7. If the thread's write operation call is waiting for release disk space, it will directly throw a "disk space full" error and then terminate;

• 8. When the MyISAM table is KILLed when executing REPAIR TABLE or OPTIMIZE TABLE, it will cause this The table is damaged and unavailable, and the guide will be used to repair it again.

Some suggestions for safely shutting down MySQL

If you want to safely shut down the mysqld service process, it is recommended to follow the steps below:

• 0. Use an account with the highest authority such as SUPER and ALL to connect to MySQL. It is best to use unix socket to connect;

• 1. In versions 5.0 and above, set innodb_fast_shutdown = 1 , allowing InnoDB to be shut down quickly (without full purge, insert buffer merge), if it is to upgrade or downgrade the MySQL version, do not set it;

• 2. Set innodb_max_dirty_pages_pct = 0 to let InnoDB Flush all dirty pages to disk;

• 3. Set max_connections and max_user_connections to 1, which means that in the end, no new connections are allowed to be created except for your current connection;

• 4 , close all inactive threads, that is, the thread ID whose status is Sleep and Time is greater than 1;

• 5. Execute SHOW PROCESSLIST to confirm whether there are active threads, especially if Table lock threads, such as SELECT with large data sets, large-scale UPDATE, or DDL execution, must be particularly cautious;

• 6. Execute SHOW ENGINE INNODB STATUS to confirm The value of History list length is low (generally less than 500), that is, there are very few unPURGEd transactions, and it is confirmed that the values ​​of Log sequence number, Log flushed up to, and Last checkpoint at are the same, that is, all LSNs Checkpoints have been made;

• #7. Then perform the FLUSH LOCKAL TABLES operation, refresh all table caches, and close the open tables (the function of LOCAL is that this operation does not record BINLOG) ;

• 8. If it is a SLAVE server, it is best to close IO_THREAD first, wait for all RELAY LOGs to be used up, and then close SQL_THREAD to avoid SQL_THREAD being terminated when executing large transactions. Be patient. After all applications are completed, if you must force shutdown, it is best to wait for the large transaction to end before closing SQL_THREAD;

• 9. Finally, execute mysqladmin shutdown.

• 10. In an emergency, you can set innodb_fast_shutdown = 1, and then directly execute mysqladmin shutdown, or even directly call kill or kill -9 at the operating system layer to kill the mysqld process (in Some transactions may be lost when innodb_flush_log_at_trx_commit = 0), but when the mysqld process is started again, CRASH RECOVERY will be performed, which needs to be weighed.

Having said so much, in fact, it is enough to execute mysqladmin shutdown under normal circumstances. If blocking occurs, refer to the above content for analysis and solution.

The above is the detailed content of MySQL Optimization - Detailed explanation of safely shutting down MySQL instances. For more information, please follow other related articles on the PHP Chinese website!