mysql重新開機的問題

一.問題描述

今天直接重新開機了使用者中心庫的mysql程序，資料庫起來後，一直報錯Too many connections。機器負載飙升到100了。

登陸到mysql中，發現很多連接配接的狀态都是logging slow query，分析慢查詢日志,發現那段時間内慢查詢有7萬多。

二.原因分析

應該是資料庫重新開機後，需要重裝裝載資料到記憶體中，導緻所有的語句執行都很慢，這樣就堆積了很多請求，最後超過了最多連接配接數。

運作個10分鐘左右，等熱點資料都裝載到記憶體了，資料庫請求就恢複正常了。

如果把慢查詢的時間設定為1s，不知道是否還會出現logging slow query 狀态的sql？

對于以後類似的操作，要評估一下buffer 重建的時間，buffer設定過大也會導緻重新開機所需的時間更長。

三.附上show processlist相關的狀态

在Show processlist輸出中的Status項: 5.1手冊中沒有的或者翻譯有問題的，都附帶5.5原文說明：

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.

這個狀态當線程建立一個表（包括内部臨時表）時，在這個建表功能結束時出現。即使某些錯誤導緻建表失敗，也會使用這個狀态。

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

當計算MyISAM表索引分布時。（比如進行ANALYZE TABLE時）

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

這個線程檢查伺服器是否有具有執行該語句的所需權限。

The thread is performing a table check operation.

線程正在執行表檢查操作。

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

線程處理一個指令，并正準備釋放記憶體和重置某些狀态變量。

The thread is flushing the changed table data to disk and closing the used tables. 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.

線程正在将變更的表中的資料重新整理到磁盤上并正在關閉使用過的表。這應該是一個快速的操作。如果不快，則應該檢查硬碟空間是否已滿或者硬碟IO是否達到瓶頸。

The thread is converting an internal temporary table from a MEMORY table to an on-disk MyISAM table.

線程将一個内部臨時表轉換為磁盤上的MyISAM表。

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

線程正在處理一個ALTER TABLE語句。這個狀态發生在新的表結構已經建立之後，但是在資料被複制進入之前。

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

如果一個語句有不同的ORDER BY和GROUP BY條件，資料會被複制到一個臨時表中并且按組排序。

The server is copying to a temporary table in memory.

線程将資料寫入記憶體中的臨時表。

The server is copying to a temporary table on disk. The temporary result set has become too large (see Section 8.4.3.3, “How MySQL Uses Internal Temporary Tables”). Consequently, the thread is changing the temporary table from in-memory to diskbased format to save memory.

線程正在将資料寫入磁盤中的臨時表。臨時表的結果集過大（大于tmp_table_size）。是以，線程将臨時表由基于記憶體模式改為基于磁盤模式，以節省記憶體。

The thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.

線程正在對一個MyISAM表執行ALTER TABLE ... ENABLE KEYS語句。

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

線程正在使用内部臨時表處理一個SELECT 操作。

The thread is creating a table. This includes creation of temporary tables.

線程正在建立一個表，包括建立臨時表。

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.

線程正在建立一個臨時表在記憶體或者磁盤上。如果這個表建立在記憶體上但是之後被轉換到磁盤上，這個狀态在運作Copying to tmp table on disk 的時候保持。

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

線程正在執行多表删除的第一部分，隻從第一個表中删除。并且儲存列和偏移量用來從其他（參考）表删除。

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

線程正在執行多表删除的第二部分，并從其他表中删除比對的行。

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

線程正在執行ALTER TABLE ... DISCARD TABLESPACE 或 ALTER TABLE ... IMPORT TABLESPACE語句。

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

這個狀态出現在結束時，但是在對ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, 或者 UPDATE 語句進行清理之前。

The thread has begun executing a statement.

該線程已開始執行一條語句。

The thread is executing statements in the value of the init_command system variable.

線程正在執行處于init_command系統變量的值中的語句。

The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

線程已經執行了指令。在這個狀态中涉及的查詢緩存可以得到一些釋放。這個狀态通常後面跟随cleaning up狀态。

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.

線程正在執行FLUSH TABLES 并且等待所有線程關閉他們的表。

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

伺服器正在準備進行自然語言全文檢索。

This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. 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

? Freeing memory buffers, including for blobs

這個狀态出現線上程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE語句之前。伺服器在這種狀态下進行的操作，包括：重新整理全日志、Innodb日志，和一些查詢緩存清理操作。

對于end狀态，可能會發生下列操作：

在表中的資料變更之後移除查詢緩存。

将事務寫入全日志。

釋放記憶體緩沖區，包括大的二進制資料塊。

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.

程式對線程發送了KILL語句，并且它應該放棄下一次對KILL标記的檢查。這個标記在每一個MySQL的主要循環中被檢查，但在某些情況下，它可能需要令線程在很短的時間内死亡。如果這個線程被其他線程鎖住了，這個KILL操作會在其他線程釋放鎖的瞬時執行。

The query is locked by another query.

As of MySQL 5.5.3, this state was removed because it was equivalent to the Table lock state and no longer appears in SHOW PROCESSLIST output.

這個查詢被其他查詢鎖住了。

在MySQL 5.5.3版本，這個狀态被移除了。因為它相當于表鎖狀态，并且不再出現在SHOW PROCESSLIST輸出中。

The thread is writing a statement to the slow-query log.

這個線程正在将語句寫入慢查詢日志。

This state is used for the SHOW PROCESSLIST state.

沒有操作的狀态。

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

線程連接配接的初始狀态。直到用戶端已經成功驗證。

The server is enabling or disabling a table index.

伺服器啟用或禁用表索引。

The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_open_cache value is large enough.

線程正試圖打開一張表。這應該是非常快的過程，除非打開受到阻止。一個ALTER TABLE 或LOCK TABLE語句能夠阻止打開一張表直到語句運作結束。有必要檢查table_open_cache的值是否足夠大。

The server is performing initial optimizations for a query.

伺服器執行查詢的初步優化。

This state occurs during query optimization.

在查詢優化過程中出現這個狀态。

The thread is removing unneeded relay log files.

線程正在移除不必要的中繼日志檔案。

This state occurs after processing a query but before the freeing items state.

這個狀态出現在處理一個查詢之後，但是在freeing items狀态之前。

The server is reading a packet from the network.

伺服器正在從網絡閱讀資料包。

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

查詢正在使用SELECT DISTINCT，這種情況下MySQL不能在早期階段優化掉一些distinct操作。是以，MySQL需要一個額外的階段，在将結果發送到用戶端之前删除所有重複的行。

The thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.

線程正在移除一個内置臨時表，在執行一條SELECT語句之後。 如果沒有臨時表産生，那麼這個狀态不被使用。

The thread is renaming a table.

線程正在重命名一張表。

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

線程正在處理ALTER TABLE語句，建立新的表，并且重命名它來代替原有的表。

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.

線程獲得了表鎖，但是在取得表鎖之後才發現該表的底層結構已經發生了變化。線程釋放這個鎖，關閉表，并試圖重新打開該表。

The repair code is using a sort to create indexes.

修複代碼正在使用一個分類來建立索引。

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

線程完成一個多線程的MyISAM表的修複。

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

修複代碼正在通過索引緩存一個接一個地使用建立索引。這比通過分類修複要慢很多。

The thread is rolling back a transaction.

線程正在復原一個事務

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

對于MyISAM表的類似repair或analysis操作，線程在.MYI檔案的頭部儲存一個新表的狀态。狀态資訊包括行數、自增數、索引分布等等。

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

線程正在進行第一階段，在更新前尋找所有比對的行。如果update正在更改用于查找相關行的索引，則必須這麼做。

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

線程正在讀取和處理一條SELECT語句的行，并且将資料發送至用戶端。由于在此期間會執行大量的磁盤通路（讀操作），這個狀态在一個指定查詢的生命周期中經常是耗時最長的。

The thread is beginning an ALTER TABLE operation.

線程正開始進行一個ALTER TABLE操作。

The thread is doing a sort to satisfy a GROUP BY.

線程正在執行一個由GROUP BY指定的排序。

The thread is doing a sort to satisfy a ORDER BY.

線程正在執行一個由ORDER BY指定的排序。

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

線程正在對索引頁進行排序，為了對MyISAM表進行操作時獲得更優的性能。

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

對于一個SELECT語句，這與建立排序索引相似，但是是對非臨時表。

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

伺服器計算統計去規劃一個查詢。如果一個線程長時間處于這個狀态，這個伺服器的磁盤可能在執行其他工作。

The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock

(not waiting for it).

這個線程正在請求或者等待一個内部的或外部的系統表鎖。如果這個狀态是由于外部鎖的請求産生的，并且你沒有使用多個正在通路相同的表的mysqld伺服器，那麼你可以使用--skip-external-locking選項禁用外部系統鎖。然而，外部系統鎖預設情況下禁用，是以這個選項可能不會産生效果。對于SHOW PROFILE，這個狀态意味着線程正在請求鎖。（而非等待）

The next thread state after System lock. The thread has acquired an external lock and is going to request an internal table lock.

This state was replaced in MySQL 5.5.6 with Waiting for table level lock.

系統鎖定後的下一個線程狀态。線程已獲得外部鎖并且将請求内部表鎖。

這個狀态在MySQL 5.5.6版本中被Waiting for table level lock取代。

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

線程尋找更新比對的行并進行更新。

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

線程正在執行多表更新的第一部分，隻從第一個表中更新。并且儲存列和偏移量用來從其他（參考）表更新。

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

線程正在執行多表更新的第二部分，并從其他表中更新比對的行。

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

線程正在請求或等待一個GET_LOCK()調用所要求的咨詢鎖。對于SHOW PROFILE，這個狀态意味這線程正在請求鎖。（而非等待）

The thread has invoked a SLEEP() call.

線程調用了一個SLEEP()。

A statement that causes an explicit or implicit commit is waiting for release of a read lock. This state was removed in MySQL 5.5.8; Waiting for commit lock is used instead.

一個顯式或隐式語句在送出時等待釋放讀鎖。這個狀态在MySQL 5.5.8版本中被移除，以Waiting for commit lock代替。

A statement that causes an explicit or implicit commit is waiting for release of a read lock or FLUSH TABLES WITH READ LOCK) is waiting for a commit lock. This state was added in MySQL 5.5.8.

同上，這個狀态于MySQL 5.5.8版本加入。

FLUSH TABLES WITH READ LOCK) is waiting for a global read lock.

等待全局讀鎖。

The thread is waiting for a global read lock obtained by another thread (with FLUSH TABLES WITH READ LOCK) to be released.This state was removed in MySQL 5.5.8; Waiting for global read lock or Waiting for commit lock are used instead.

等待釋放讀鎖。

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

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

In MySQL 5.5.6, Waiting for table was replaced with Waiting for table flush.

線程獲得一個通知，底層表結構已經發生變化，它需要重新打開表來擷取新的結構。然而，重新打開表，它必須等到所有其他線程關閉這個有問題的表。

這個通知産生通常因為另一個線程對問題表執行了FLUSH TABLES或者以下語句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

The server is waiting to acquire a lock, where lock_type indicates the type of lock:

? Waiting for event metadata lock (added in MySQL 5.5.8)

? Waiting for global metadata lock (replaced by Waiting for global read lock in MySQL 5.5.8)

? Waiting for global read lock (added in MySQL 5.5.8)

? Waiting for schema metadata lock

? Waiting for stored function metadata lock

? Waiting for stored procedure metadata lock

? Waiting for table level lock

? Waiting for table metadata lock

? Waiting for trigger metadata lock (added in MySQL 5.5.8)

等待各個種類的表鎖。

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

一個普通的狀态，線程正在等待一個條件為真。沒有特定的狀态資訊可用。

The thread has issued a FLUSH TABLES WITH READ LOCK statement to obtain a global read lock and is waiting to obtain the lock. This state was removed in MySQL 5.5.8; Waiting for global read lock is used instead.

線程發出了一個FLUSH TABLES WITH READ LOCK語句來擷取一個全局讀鎖，并且等待獲得這個鎖。這個狀态在MySQL 5.5.8被移除，使用Waiting for global read lock 來代替。

The server is writing a packet to the network.