Performance Schema使用簡介(一)

沃趣科技MySQL高階資料庫專家   董紅禹

---

Performance Schema簡介

Oracle DBA都應該知道 Oracle中提供了大量的檢視供DBA們排查問題使用，並且有等待事件幫助大家快速定位問題屬於哪一類。MySQL 中也有Performance Schema幫助大家去分析排查問題，並且在5.7中增加了Sys Schema，將Performance Schema和information_schema的資訊格式化後，供大家更方便的分析問題。 

這裡先介紹先Performance Schema的使用方式，便於後面大家更好的去使用Sys Schema。

Performance Schema開啟方式

• 開啟Performance Schema庫

在配置檔案中新增performance_schema=on

• 設定instruments及consumers開啟 

開啟performance_schema=on之後我們還需要進行設定instruments及consumers 

instruments通俗講就是監控項可以透過setup_instruments表設定需要開啟對哪些項監控進行統計 

consumers就是控制是否將監控到的結果進行記錄

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

UPDATE setup_consumers SET ENABLED = 'YES';

如果不開啟instruments和consumers則相應的事件檢測則不能使用，也就不能收集相應的等待事件和效能的統計。 

當然我們也可以單獨設定某一個instruments的開啟和關閉

UPDATE setup_instruments SET ENABLED = 'NO'

WHERE NAME = 'wait/io/file/sql/binlog';

為了防止重啟後恢復預設配置可以新增到配置檔案中設定

performance-schema-instrument='instrument_name=value'

performance-schema-consumer-consumer_name=value 

instrument_name和consumer_name

可以透過查詢setup_instruments和setup_consumers;檢視

setup_consumers中的層級關係

mysql> select * from setup_consumers;

+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+--------------------------------+---------+

12 rows in set (0.00 sec)

setup_consumers中的事件表有層級之分 ，我們可以透過上面看到有 global、thread、current、history、history_long，他們之間的層級如下:

global_instrumentation

thread_instrumentation
  events_waits_current
    events_waits_history
    events_waits_history_long
  events_stages_current
    events_stages_history
    events_stages_history_long
  events_statements_current
    events_statements_history
    events_statements_history_long

statements_digest

也就是說，如果設定了global的才會去檢查thread的有沒有被設定，如果thread的設定了才會去檢查current的有沒有被設定，如果global的沒有被設定則thread和current都不去檢查，相應的資訊也不會記錄到對應的表中。 

如果global_instrumentation沒有被設定則對應的表中都不會記錄任何資訊。

 
這裡的history、history_long可以透過引數設定大小,因為是記憶體表，如果太大會佔用很多記憶體。

setup_instruments中的型別

mysql> select name,count(*) from setup_instruments group by left(name,5);

+---------------------------------+----------+
| name                            | count(*) |
+---------------------------------+----------+
| idle                            |        1 |
| stage/sql/After create          |      108 |
| statement/sql/select            |      168 |
| wait/synch/mutex/sql/PAGE::lock |      279 |
+---------------------------------+----------+

4 rows in set (0.00 sec)

我們可以看到setup_instruments中的型別有四種(5.6版本)。 

具體的解釋大家可以在官網中檢視:

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html

簡單使用介紹

簡單介紹幾個語句用於排查SQL語句的效率

• 查詢沒有使用到索引或者索引效率低下的語句:

SELECT OBJECT_SCHEMA,  THREAD_ID TID, SUBSTR(SQL_TEXT, 1, 50) SQL_TEXT, ROWS_SENT RS, ROWS_EXAMINED RE,CREATED_TMP_TABLES,NO_INDEX_USED,NO_GOOD_INDEX_USED FROM performance_schema.events_statements_history WHERE (NO_INDEX_USED=1 OR NO_GOOD_INDEX_USED=1) and sql_text  NOT LIKE '%performance_schema%'\G

• 檢視哪些索引沒有被使用過

SELECT

OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'

ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

• 檢視SQL語句在哪個階段消耗最大

SELECT eshl.event_name, sql_text, eshl.timer_wait/1000000000000 w_s

FROM performance_schema.events_stages_history_long eshl
JOIN performance_schema.events_statements_history_long esthl
ON (eshl.nesting_event_id = esthl.event_id)

WHERE eshl.timer_wait > 1*10000000000\G

總  結

此次簡單介紹了Performance Schema的使用，後面會逐步介紹使用Performance Schema的一些實戰功能。

---