用 MySQL 5.6 的 Performance Schema 觀察系統效能

Percona 寫的「MySQL query digest with Performance Schema」這篇提到了 MySQL 5.6 的 Performance Schema 裡的 events_statements_summary_by_digest 相當好用,實際在系統上翻了翻發現算是非常實用的資料。

首先先看這個表格實際的內容,由於文字塞不動,就改用圖片了:

可以試著用 SELECT * FROM performance_schema.events_statements_summary_by_digest LIMIT 1 G 之類的指令看到裡面的值,像是這樣:(裡面有些欄位名稱我換掉了,換掉的部份用刪節號標示)

                SCHEMA_NAME: kkbox
                     DIGEST: 490a2e363ba7840843733e219175e2a7
                DIGEST_TEXT: SELECT * FROM `table1` WHERE TYPE = ? AND `column1` IN (?) AND STATUS IN (...) ORDER BY STATUS DESC , `created_at` DESC , `id` DESC 
                 COUNT_STAR: 299179761
             SUM_TIMER_WAIT: 215069693134746000
             MIN_TIMER_WAIT: 130241000
             AVG_TIMER_WAIT: 718864000
             MAX_TIMER_WAIT: 54442047235000
              SUM_LOCK_TIME: 21915487179000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1240784631
          SUM_ROWS_EXAMINED: 2499118409
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 2630
             SUM_SORT_RANGE: 299196698
              SUM_SORT_ROWS: 1240808755
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-09-17 20:41:15
                  LAST_SEEN: 2015-10-15 01:06:10

其中 DIGEST_TEXT 是 SQL query,可以看到 IN 裡面的東西會被整合起來,而 COUNT_STAR 是次數,後面的 AVG_TIMER_WAIT 單位是 10-12 秒,除以 109 後才會變成 ms。

裡面的資訊對於 DBA 在 tune 效能時應該是很有用...

Leave a Reply

Your email address will not be published. Required fields are marked *