MySQL 變數及效能狀態檢視知識技巧

1995年首發以來，MySQL不僅自己“星途坦蕩”，成為了現今最流行的關係型資料庫，同時還衍生出很多備受矚目的分支，如在資料庫領域撕開缺口併成功佔據一席之地的 MariaDB 及 Percona。

今天，我們就來談談如何檢視MySQL效能狀態並最佳化。

如何檢視檢視MySQL效能?

現在大家對MySQL的監控通常有兩種做法，一是連線到mysql資料庫內部，使用show status，show variables，flush status 來檢視mysql的各種效能指標;二是直接使用mysqladmin檢視其效能指標。

方法一的使用：

• 檢視MySQL伺服器配置資訊 ：mysql> show variables;

• 檢視MySQL伺服器執行的各種狀態值 ：mysql> show global status;

• 慢查詢：mysql> show variables like '%s%';

• mysql> show global status like '%slow%';

• 連線數：mysql> show variables like 'max_connections';

• key_buffer_size 是對MyISAM表效能影響最大的一個引數mysql> show variables like 'key_buffer_size';

• 臨時表：mysql> show global status like 'created_tmp%';

• 檢視open table ：mysql> show global status like 'open%tables%';

• 程式使用情況：mysql> show global status like 'Thread%';

• 查詢快取(query cache) ：mysql> show global status like 'qcache%';

• 檔案開啟數(open_files) ：mysql> show global status like 'open_files';

• 表鎖情況 ：mysql> show global status like 'table_locks%';

• 表掃描情況 ：mysql> show global status like 'handler_read%';

方法二的使用：

UserParameter=mysql.uptime,mysqladmin -uroot status|cut -f2 -d":"|cut -f1 -d"T"

mysqladmin兩個引數，status，extended-status

shell > mysqladmin -uroot -ppassword variables status

其中包含的資訊如下：

• Uptime: 4557887 #mysql執行的秒數

• Threads: 1 #連線數

• Questions: 1684130 #The number of questions (queries) from clients since the server was started.

• Slow queries: 0 #The number of queries that have taken more than long_query_time seconds

• Opens: 221872 #The number of tables the server has opened.

• Flush tables: 1 #The number of flush-*, refresh, and reload commands the server has executed.

• Open tables: 64 #The number of tables that currently are open.

• Queries per second avg: 0.369 #從上次執行開始計算，每秒鐘平均查詢次數

關鍵的MySQL統計指標

當資料庫出現查詢緩慢或者無法響應查詢的情況時，應該怎麼辦呢?我們可以透過監控與效能及資源利用率相關的指標，來查詢出現這個問題的原因，避免依賴資料庫效能的相關元件也產生影響。

MySQL使用者可以接觸到的效能指標有幾百個，本文介紹四個比較關鍵常用的指標，查詢吞吐量、查詢執行效能、連線情況和緩衝池使用情況。

查詢吞吐量： MySQL內部有一個名為 Questions 的計數器，客戶端每傳送一個查詢語句，其值就會加一，可利用其來衡量查詢吞吐量，SHOW GLOBAL STATUS LIKE "Questions";

查詢執行效能： 監控查詢延遲的方式有很多，例如透過 MySQL 內建的指標，或者透過查詢效能模式。 MySQL 5.6.6 版本開始預設啟用，MySQL 的 performance_schema 資料庫中的表格儲存著伺服器事件與查詢執行的低水平統計資料。

連線情況： MySQL的文件指出，健壯的伺服器應該能夠處理成百上千的連線數，可透過SHOW VARIABLES LIKE 'max_connections';的形式來檢視。

緩衝池使用情況： MySQL 預設的儲存引擎 InnoDB 使用了一片稱為緩衝池的記憶體區域，用於快取資料表與索引的資料。如果資料庫效能開始下滑，而磁碟 I/O 在不斷攀升，擴大緩衝池往往能帶來效能回升。