MySQL SHOW PROCESSLIST的執行狀態

本文主要給大家簡單講講MySQL SHOW PROCESSLIST的執行狀態，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL SHOW PROCESSLIST的執行狀態這篇文章可以給大家帶來一些實際幫助。

8.14.2 General Thread States

The following list describes thread State values that are associated withgeneral query processing and not more specialized activities such asreplication. Many of these are useful only for finding bugs in the server.

·         After create

This occurs when the thread creates a table (includinginternal 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.

·         Analyzing

The thread is calculating a MyISAM table key distributions (forexample, for ANALYZE TABLE).

·         checking permissions

The thread is checking whether the server has therequired privileges to execute the statement.

·         Checking table

The thread is performing a table check operation.

·         cleaning up

The thread has processed one command and is preparing tofree memory and reset certain state variables.

·         closing tables

The thread is flushing the changed table data to disk andclosing the used tables. This should be a fast operation. If not, verify that youdo not have a full disk and that the disk is not in very heavy use.

·         converting HEAP to MyISAM

The thread is converting an internaltemporary table from a MEMORY table toan on-disk MyISAM table.

·         preparing for alter table

The server is preparing to execute anin-place ALTER TABLE.

·         altering table

The server is in the process ofexecuting an in-place ALTER TABLE.

·         copy to tmp table

The thread is processing an ALTER TABLE statement. This stateoccurs after the table with the new structure has been created but before rowsare copied into it.

For a thread in this state, thePerformance Schema can be used to obtain about the progress of the copyoperation. See Section 25.11.5,“Performance Schema Stage Event Tables”.

·         committing alter table to storage engine

The server has finished anin-place ALTER TABLE and is committingthe result.

·         Copying to group table

If a statement has different ORDER BY and GROUP BY criteria, the rows are sorted bygroup and copied to a temporary table.

·         Copying to tmp table

The server is copying to a temporarytable in memory.

正在執行查詢，并將結果集復制到一個臨時表中。這種狀態一般要么是做GROUP BY操作，要么是文件排序操作或者UNION操作

·         Copying to tmp table on disk

The server is copying to a temporarytable on disk. The temporary result set has become too large (see Section 8.4.4,“Internal Temporary Table Use in MySQL”). Consequently, the threadis changing the temporary table from in-memory to disk-based format to savememory.

結果集過大，內存臨時表放不下，產生磁盤臨時表

·         Creating index

The thread is processing ALTER TABLE ... ENABLE KEYS fora MyISAM table.

·         Creating sort index

The thread is processing a SELECT that is resolved using an internaltemporary table.

·         creating table

The thread is creating a table. This includes creation oftemporary tables.

·         Creating tmp table

The thread is creating a temporary tablein memory or on disk. If the table is created in memory but later is convertedto an on-disk table, the state during that operation will be Copying to tmp table on disk.

·         deleting from main table

The server is executing the first part of a multiple-tabledelete. It is deleting only from the first table, and saving columns andoffsets to be used for deleting from the other (reference) tables.

·         deleting from reference tables

The server is executing the second part of amultiple-table delete and deleting the matched rows from the other tables.

·         discard_or_import_tablespace

The thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.

·         end

This occurs at the end but before thecleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

·         executing

The thread has begun executing a statement.

·         Execution of init_command

The thread is executing statements inthe value of the init_command systemvariable.

·         freeing items

The thread has executed a command. Somefreeing of items done during this state involves the query cache. This state isusually followed by cleaningup.

命令已經在執行正在進行清理、更新日志索引等。大量出現這個state則需要檢查是不是磁盤IO性能太差了

·         FULLTEXT initialization

The server is preparing to perform a natural-languagefull-text search.

·         init

This occurs before the initializationof ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server inthis state include flushing the binary log, the InnoDB log, and some query cache cleanupoperations.

For the end state, the following operations could be happening:

§ Removing query cache entries after data in a table ischanged

§ Writing an event to the binary log

§ Freeing memory buffers, including for blobs

·         Killed

Someone has sent a KILL statement to the thread and it should abortnext time it checks the kill flag. The flag is checked in each major loop inMySQL, but in some cases it might still take a short time for the thread todie. If the thread is locked by some other thread, the kill takes effect assoon as the other thread releases its lock.

·         logging slow query

The thread is writing a statement to the slow-query log.  記錄慢查詢的時候，會產生這個state

·         login

The initial state for a connection thread until theclient has been authenticated successfully.

·         manage keys

The server is enabling or disabling a table index.

·         NULL

This state is used for the SHOW PROCESSLIST state.

·         Opening tables

The thread is trying to open a table.This is should be very fast procedure, unless something prevents opening. Forexample, an ALTER TABLE or a LOCK TABLEstatementcan prevent opening a table until the statement is finished. It is also worthchecking that your table_open_cache valueis large enough.

·         optimizing

The server is performing initial optimizations for aquery.

·         preparing

This state occurs during query optimization.

·         Purging old relay logs

The thread is removing unneeded relay log files.

·         query end

This state occurs after processing aquery but before the freeingitems state.

·         Receiving from client

The server is reading a packet from theclient. This state is called Reading from net prior to MySQL 5.7.8.

·         Removing duplicates

The query was using SELECT DISTINCT in such a way that MySQL could notoptimize away the distinct operation at an early stage. Because of this, MySQLrequires an extra stage to remove all duplicated rows before sending the resultto the client.

·         removing tmp table

The thread is removing an internaltemporary table after processing a SELECT statement. This state is not used if notemporary table was created.

·         rename

The thread is renaming a table.

·         rename result table

The thread is processing an ALTER TABLE statement, hascreated the new table, and is renaming it to replace the original table.

·         Reopen tables

The thread got a lock for the table, but noticed aftergetting the lock that the underlying table structure changed. It has freed thelock, closed the table, and is trying to reopen it.

·         Repair by sorting

The repair code is using a sort to create indexes.

·         Repair done

The thread has completed amulti-threaded repair for a MyISAM table.

·         Repair with keycache

The repair code is using creating keysone by one through the key cache. This is much slower than Repair by sorting.

·         Rolling back

The thread is rolling back a transaction.

·         Saving state

For MyISAM table operations such as repair oranalysis, the thread is saving the new table state to the .MYI file header. State includes information such asnumber of rows, the AUTO_INCREMENT counter,and key distributions.

·         Searching rows for update

The thread is doing a first phase tofind all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to findthe involved rows.

·        Sendingdata【別被它的名字欺騙】

The thread is reading and processingrows for a SELECT statement, and sending data to the client.Because operations occurring during this state tend to perform large amounts ofdisk access (reads), it is often the longest-running state over the lifetime ofa given query.

所謂的“Sending data”并不是單純的發送數據，而是包括“收集 + 發送數據”。

這里的關鍵是為什么要收集數據，原因在于：mysql使用“索引”完成查詢結束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到磁盤中去找到“數據行”上將需要返回的數據讀取出來返回個客戶端。

·         Sending to client

The server is writing a packet to theclient. This state is called Writing to net prior to MySQL 5.7.8.

·         setup

The thread is beginning an ALTER TABLE operation.

·         Sorting for group

The thread is doing a sort to satisfya GROUP BY.

·         Sorting for order

The thread is doing a sort to satisfyan ORDER BY.

·         Sorting index

The thread is sorting index pages formore efficient access during a MyISAM table optimization operation.

·         Sorting result

For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

·         statistics

The server is calculating statistics to develop a queryexecution plan. If a thread is in this state for a long time, the server isprobably disk-bound performing other work.

·         System lock

The thread has called mysql_lock_tables() and the thread state has not beenupdated since. This is a very general state that can occur for many reasons.

For example, the thread is going torequest or is waiting for an internal or external system lock for the table.This can occur when InnoDB waitsfor a table-level lock during execution of LOCK TABLES. If thisstate is being caused by requests for external locks and you are not usingmultiple mysqld serversthat are accessing the same MyISAM tables, you can disableexternal system locks with the --skip-external-locking option.However, external locking is disabled by default, so it is likely that thisoption will have no effect. For SHOW PROFILE, this state means thethread is requesting the lock (not waiting for it).

·         update

The thread is getting ready to start updating the table.

·         Updating

The thread is searching for rows to update and isupdating them.

·         updating main table

The server is executing the first part of amultiple-table update. It is updating only the first table, and saving columnsand offsets to be used for updating the other (reference) tables.

·         updating reference tables

The server is executing the second part of amultiple-table update and updating the matched rows from the other tables.

·         User lock

The thread is going to request or iswaiting for an advisory lock requested with a GET_LOCK() call.For SHOW PROFILE, this state means thethread is requesting the lock (not waiting for it).

·         User sleep

The thread has invoked a SLEEP() call.

·         Waiting for commit lock

FLUSH TABLES WITH READ LOCK is waiting for acommit lock.

·         Waiting for global read lock

FLUSH TABLES WITH READ LOCK is waiting for aglobal read lock or the global read_only systemvariable is being set.

·         Waiting for tables

The thread got a notification that the underlyingstructure for a table has changed and it needs to reopen the table to get thenew structure. However, to reopen the table, it must wait until all otherthreads have closed the table in question.

This notification takes place if anotherthread has used FLUSH TABLES or one of the following statements onthe table in question: FLUSHTABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

·         Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads toclose their tables, or the thread got a notification that the underlyingstructure for a table has changed and it needs to reopen the table to get thenew structure. However, to reopen the table, it must wait until all otherthreads have closed the table in question.

This notification takes place if anotherthread has used FLUSH TABLES or one of the following statements onthe table in question: FLUSHTABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

等待其他會話關閉這個表的連接才能執行flush tables操作。

·         Waitingfor lock_type lock

The server is waiting to acquire a THR_LOCK lock or a lock from the metadatalocking subsystem, where lock_type indicates the type of lock.

This state indicates a wait for a THR_LOCK:

§ Waitingfor table level lock

These states indicate a wait for a metadata lock:

§ Waitingfor event metadata lock

§ Waitingfor global read lock

§ Waitingfor schema metadata lock

§ Waitingfor stored function metadata lock

§ Waitingfor stored procedure metadata lock

§ Waitingfor table metadata lock

§ Waitingfor trigger metadata lock

For information about table lock indicators,see Section 8.11.1, “InternalLocking Methods”. For information about metadata locking, see Section 8.11.4, “Metadata Locking”.To see which locks are blocking lock requests, use the Performance Schema locktables described at Section 25.11.12, “PerformanceSchema Lock Tables”.

·         Waiting on cond

A generic state in which the thread is waiting for acondition to become true. No specific state information is available.

·         Writing to net

The server is writing a packet to thenetwork. This state is called Sending to client as of MySQL 5.7.8.  將結果集通過網絡返回給客戶端

> alter table students add column c1 int ,ALGORITHM=inplace;  show profile過程如下：

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000060 |

| checking permissions           | 0.000004 |

| checking permissions           | 0.000003 |

| init                           | 0.000003 |

| Opening tables                 | 0.000023 |

| setup                          | 0.000021 |

| creating table                 | 0.060620 |

| After create                   | 0.000077 |

| System lock                    | 0.000006 |

| preparing for alter table      | 0.097194 |

| altering table                 | 0.027624 |

| committing alter table to stor | 0.166890 |

| end                            | 0.000019 |

| query end                      | 0.023681 |

| closing tables                 | 0.000010 |

| freeing items                  | 0.000015 |

| cleaning up                    | 0.000015 |

+--------------------------------+----------+

17 rows in set, 1 warning (0.00 sec)

alter table students add column c2 int ,ALGORITHM=COPY; show profile過程如下：

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000052 |

| checking permissions | 0.000003 |

| checking permissions | 0.000015 |

| init                 | 0.000002 |

| Opening tables       | 0.000047 |

| setup                | 0.000034 |

| creating table       | 0.026692 |

| After create         | 0.000011 |

| System lock          | 0.149286 |

| copy to tmp table    | 0.051415 |

| rename result table  | 0.212463 |

| end                  | 0.000025 |

| query end            | 0.022857 |

| closing tables       | 0.000013 |

| freeing items        | 0.000114 |

| cleaning up          | 0.000032 |

+----------------------+----------+

16 rows in set, 1 warning (0.00 sec)

其他會話flush tables with read lock時候，本會話等待更新數據的時候. show profile過程如下：

+------------------------------+------------+ 

| Status                       | Duration   |

+------------------------------+------------+

| starting                     |   0.000081 |

| checking permissions         |   0.000006 |

| Opening tables               |   0.000009 |

| Waiting for global read lock | 733.001740 |

| Opening tables               |   0.000441 |

| init                         |   0.000051 |

| System lock                  |   0.000027 |

| updating                     |   0.000086 |

| end                          |   0.000006 |

| query end                    |   0.000006 |

| closing tables               |   0.000020 |

| freeing items                |   0.000025 |

| cleaning up                  |   0.000019 |

+------------------------------+------------+

MySQL SHOW PROCESSLIST的執行狀態就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。