Get network issues from WhatsUp Gold. A description of the problem. The machine load soared to Log on to the mysql, found that many connection status is logging slow query, analysis of slow query log, found that time slow query more than 7.
Analysis of the causes of two. Should the database after the restart, you need to reinstall the loading data into memory, causes all of the statements executed very slowly, so the accumulation of a lot of request, finally exceeded the maximum number of connections. Running 10 minutes, and other hot data are loaded into memory, the database request returned to normal. If the slow query time is set to 1s, do not know whether there will be logging slow query SQL? For the later similar operation, to assess the time of buffer reconstruction, buffer set is too large will lead to restart the time required for longer.
Attach the show processlist state Show processlist Status entry in the Show processlist output: After create This occurs when the thread creates a table including internal temporary tables , at the end of the function that creates the table. This state is used even if the table could not be created due to some error. This state when the thread creates a table including internal temporary tables , appear in the table at the end of.
Even created due to some error table failure, also can use this status. This thread is to check whether the server has the necessary permissions to execute the statement. Checking table The thread is performing a table check operation. The thread is executing table to check operation.
A command processing, and is ready to release the memory and reset some state variables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use. The thread is to change the data in the table is flushed to disk and closing the used tables. This should be a quick operation. If not, you should check your hard disk is not full or hard drive IO meets the bottleneck.
This state occurs after the table with the new structure has been created but before rows are copied into it. This state occurs after the table with the new structure has been created, but be replicated in the data before entering the. Copying to tmp table The server is copying to a temporary table in memory. Thread writes data to a temporary table in memory. Copying to tmp table on disk The server is copying to a temporary table on disk.
The temporary result set has become too large see Section 8. Consequently, the thread is changing the temporary table from in-memory to diskbased format to save memory. The thread is to write data to a temporary table in the disk. So, the thread will be temporary table by the memory based model is modified based on the disk mode, in order to save memory.
This includes creation of temporary tables. The thread is to create a table, including the creation of a temporary table. Creating tmp table The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an ondisk table, the state during that operation will be Copying to tmp table on disk.
The thread is creating a temporary table in memory or on disk. But if the table is created in the memory after being converted to the disk, the state run Copying to TMP table on disk maintain. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other reference tables.
The thread is executing the first part multi table delete, delete only from the first table. And save the column and the offset from the other for reference table delete. The thread is executing the second part multi table deleted, and deleting the matched rows from the other tables.
The thread has been started to execute a statement. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up. The thread has executed a command. Involved in this state in the query cache can get some release.
This is usually followed by cleaning up state. The server is ready for natural language text retrieval. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations. For the end state, the following operations could be happening: Removing query cache entries after data in a table is changed? Writing an event to the binary log? The server was in such a state of operation, including: For the end state, may occur following operation: After the data in the table changes to remove query cache.
Writes the transaction log. Release the memory buffer, including binary large data blocks. Killed Someone has sent a KILL statement to the thread and it should abort next time it checks the kill flag.
The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock. The flag is checked in each major loop in MySQL, but in some cases, it may be necessary to the death of Ling Xiancheng in a very short period of time. If the thread is other threads are locked, the KILL will release the lock of the instantaneous in other threads to execute.
Locked The query is locked by another query. As of MySQL 5. The query is locked by another query. In the MySQL 5. This thread is writing a statement slow query log.
The initial state for a connection thread until the client has been authenticated successfully. The thread connecting the initial state. Until the client has been authenticated successfully. Server enable or disable the table index.
Opening tables, Opening table The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. The thread is trying to open a table. This is should be very fast procedure, unless prevents opening.
Execute preliminary optimization query server. Query optimization in this state occurs in the process of. Purging old relay logs The thread is removing unneeded relay log files. The thread is removing unnecessary relay log file. This state occurs after processing a query, but before the freeing items state.
Reading from net The server is reading a packet from the network. The server is reading a packet from the network. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client. Therefore, MySQL requires an additional stage, to send the results to delete all rows to repeat the client before. This state is not used if no temporary table was created. If there is no temporary table generation, this state is not used.
The thread is renaming a table. Reopen tables The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it. A thread acquires a table lock, but at the bottom of table lock after the discovery of the structure of the table has been changed.
Thread releases the lock, close the table, and tried to re open the table. Repair by sorting The repair code is using a sort to create indexes. The repair code is the use of a classification index creation. Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting. The repair code is through an index buffer one by one using the create index. This is much slower than repair by sorting.
Rolling back The thread is rolling back a transaction.