資料庫核心月報－2015/11-MySQL·特性分析·StatementDigest

db匠發表於2016-05-23
資料庫MySql

背景

在對資料庫進行效能調優的時候，除了引數、配置的調整以外，SQL調優也是重要的手段，同時也是收益最大的一環。
當DBA對業務庫進行sql調優的時候，如何做到有的放矢，投入產出受益最大？足夠詳細的SQL效能統計無疑是最重要的資訊。

下面我們先來看下不同資料庫提供的sql效能統計資訊:

Oracle的sql效能統計

Oracle可以通過直接查詢v$表得到，下面的columns列表是我們常用的一些統計:

select sql_id,
     sql_text,
     sql_fulltext,
     sharable_mem,
     persistent_mem,
     runtime_mem,
     sorts,
     fetches,
     executions,
     parse_calls,
     disk_reads,
     direct_writes,
     buffer_gets,
     application_wait_time,
     concurrency_wait_time,
     cluster_wait_time,
     user_io_wait_time,
     rows_processed,
     cpu_time,
     elapsed_time
from v$sql_area

這裡邊包括了幾類統計，SQL記憶體使用的統計、parse的統計、物理/邏輯IO的統計、cpu時間、等待時間等時間統計。

DBA可以根據這些統計資訊進行有針對性的調優:

  1. CPU調優: 如果當前資料庫效能CPU是瓶頸，可以通過order by cpu_time，查詢出來top CPU的SQL進行調優；
  2. IO調優: 可以根據buffer_gets, disk_reads，user_io_wait_time 查詢top IO的SQL進行調優；
  3. 鎖爭用: 可以根據concurrency_wait_time，cluster_wait_time查詢top lock的SQL進行調優。

MySQL的SQL效能統計

1. 通過show profiles來查詢統計資訊
在MySQL 5.6版本之前，還保留著show profiles的方式，後續版本逐步被performance_schema來替換了。
使用方法如下:

mysql> SET profiling=1;
mysql> select 1, sleep(1);
mysql> show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000100 |     NULL |       NULL |         NULL |          NULL |
| checking permissions | 0.000014 |     NULL |       NULL |         NULL |          NULL |
| Opening tables       | 0.000024 |     NULL |       NULL |         NULL |          NULL |
| init                 | 0.000020 |     NULL |       NULL |         NULL |          NULL |
| optimizing           | 0.000008 |     NULL |       NULL |         NULL |          NULL |
| executing            | 0.000022 |     NULL |       NULL |         NULL |          NULL |
| User sleep           | 1.000090 |     NULL |       NULL |         NULL |          NULL |
| end                  | 0.000021 |     NULL |       NULL |         NULL |          NULL |
| query end            | 0.000009 |     NULL |       NULL |         NULL |          NULL |
| closing tables       | 0.000010 |     NULL |       NULL |         NULL |          NULL |
| freeing items        | 0.000055 |     NULL |       NULL |         NULL |          NULL |
| logging slow query   | 0.000008 |     NULL |       NULL |         NULL |          NULL |
| cleaning up          | 0.000012 |     NULL |       NULL |         NULL |          NULL |
+----------------------+----------+----------+------------+--------------+---------------+
13 rows in set (0.00 sec)

show profile的語法如下：

        SHOW PROFILE [type [, type] … ]  
            [FOR QUERY n]  
            [LIMIT row_count [OFFSET offset]]  
          
        type:  
            ALL  
          | BLOCK IO  
          | CONTEXT SWITCHES  
          | CPU  
      | IPC  
          | MEMORY  
          | PAGE FAULTS  
          | SOURCE  
          | SWAPS

從結果集可以看到每一塊操作的CPU時間，block IO情況。
但這種適合拿單個SQL進行分析，使用上的便捷性比較差。

2. 通過performance_schema的digest統計
MySQL之前的版本不支援繫結變數，導致SQL語句太多，相同業務的SQL彙總統計比較麻煩。
從MySQL 5.6開始，在performance_schema中支援了對SQL statement的digest進行統計。
performance_schema.events_statements_summary_by_digest表根據digest進行彙總統計，DBA可以直接訪問這個記憶體表得到SQL的統計資訊。

首先，需要開啟performance_schema，然後系統就會自動為SQL statement生成digest，並記錄統計資訊。
例如:

mysql> select 1, sleep(1);
+---+----------+
| 1 | sleep(1) |
+---+----------+
| 1 |        0 |
+---+----------+
1 row in set (1.00 sec)
mysql> select * from events_statements_summary_by_digestG;
*************************** 1. row ***************************
                SCHEMA_NAME: performance_schema
                     DIGEST: bb80cc862a205b471ce0f0ff2605a9a0
                DIGEST_TEXT: SELECT ? , `sleep` (?)
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 1000577972000
             MIN_TIMER_WAIT: 1000577972000
             AVG_TIMER_WAIT: 1000577972000
             MAX_TIMER_WAIT: 1000577972000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1
          SUM_ROWS_EXAMINED: 0
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: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-11-06 11:15:52
                  LAST_SEEN: 2015-11-06 11:15:52
2 rows in set (0.00 sec)

DBA可用通過這個表的統計資訊，來有的放矢的進行SQL調優。

然而performance_schema中digest的限制:

  1. 必須開啟performance_schema的所有功能才行，但performance_schema全部開啟會對效能產生一些影響；
  2. events_statements_summary_by_digest 表預設有200個的最大限制，但從MySQL 5.5開始，可以通過調整performance_schema_digests_size來修改。但如果表滿了的話，新來的digest的統計資訊，會被全部彙總到一個digest=NULL的記錄中；
  3. 對於每一個SQL statement，performance_schema會生成一個最大1024bytes的digest_text，超過的會被截斷。

針對以上的一些限制，MySQL5.7最新GA的版本，進行了一些改進。

另外，針對限制2 Percona有一些工具pt-query-digest, 並建立一些digest歷史表，進行分析，有興趣的可以使用嘗試一下。可以參考: mysql-query-digest-with-performance-schema

MySQL 5.7 performance_schema digest的改進

  1. SQL statement digest生成功能不必和performance_schema繫結，digest的功能的原始碼主要是這兩個檔案：PFS_digest.cc和PFS_digest.h。這兩個檔案從儲存引擎目錄storage/perfschema/ 移到了server目錄sql/下；
  2. 從MySQL 5.7.6開始，digest的最大長度由固定的1024bytes，變成了可變大小，由引數performance_schema_max_sql_text_length在系統啟動的時候初始化。

總結

使用者可以針對performance_schema提供的digest的功能，根據需求進行一些開發和擴充套件，比如定期歷史儲存、建立SQL效能基線、或者更進一步如果能修改原始碼，可以為digest增加更多的merics等。


相關文章