之前寫過了一個mysql performane schema 的一個介紹，接著因為各種原因，放下了這塊，

這幾天就又撿起來了，就雜七雜八的再寫一點吧：

Mysql Performance Schema
DBA/李思亮
我們使用mysql 5.5 也有一段時間了，對於performance schema 引擎的使用問題，
一直沒有開展起來，主要原因是資料太少。這段時間花了一些時間專門的關注了
一下，形成本文件：
Mysql 5.5 版本是第一個版本，本文提到的部分資料可能不適用，其中包括了
mysql 5.6 的一些情況。
從資料庫內部講這些表關注了資料庫的4 個模組
分別是：
1. Mutex （互斥體，鎖相關)
2. RWLOCKs (讀寫鎖）
3. File I/O （檔案io）
4. Thread （執行緒）
包括了全部的mutex 42 個， rwlocks 10 個，以及6 種型別的執行緒（thread），文
件io 在分為資料(data) , 日誌（log），臨時檔案（temp file i/o）。
Performance schema 記錄的資訊，比較底層，如果想深入的研究，需要對mysql
的internal 有一定的瞭解，本篇，我們不關注原始碼級的資訊，僅從一個使用者
的角度來看，如何使用performance schema 為資料庫最佳化提供一些建議。
Performance schema 下面的表：
mysql.cnt_it.sock@performance_schema> show tables ;
+----------------------------------------------+
| Tables_in_performance_schema |
+----------------------------------------------+
| cond_instances |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_instances |
| file_summary_by_event_name |
| file_summary_by_instance |
| mutex_instances |
| performance_timers |
| rwlock_instances |
| setup_consumers |
| setup_instruments |
| setup_timers |
| threads |
+----------------------------------------------+
17 rows in set (0.00 sec)
這些表基本可以分為4部分
一類是instance 表，
Instance 表記錄的是那種型別的物件被探測或者記錄。
instance 表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE '%instances';
+------------------+
| TABLE_NAME |
+------------------+
| cond_instances |
| file_instances |
| mutex_instances |
| rwlock_instances |
+------------------+
記錄各種等待事件涉及到的例項：主要是3 類: cond (容器？）mutex（互
斥鎖），rwlock （讀寫鎖）
這些表是隻讀的。
一類是Setup 表
Setup 表記錄了配置資訊，以及監控的引數等等。
setup 表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE 'setup%';
+-------------------+
| TABLE_NAME |
+-------------------+
| setup_consumers |
| setup_instruments |
| setup_timers |
+-------------------+
setup_consumers 描述各種事件
setup_instruments 描述這個資料庫下的表名以及是否開啟監控。
setup_timers 描述監控選項已經取樣頻率的時間間隔
這個要多說一點目前performance-schema 只支援'wait' 時間的監控，程式碼樹
上wait/ 下的函式都可以監控到。
文件上說了只有'wait' 事件的檢測，有沒有其他的選項呢？
看看原始碼：
static row_setup_timers all_setup_timers_data[COUNT_SETUP_TIMERS]=
{
{
{ C_STRING_WITH_LEN("wait") },
&wait_timer
}
};
THR_LOCK table_setup_timers::m_table_lock;
int table_setup_timers::update_row_values(TABLE *table,
const unsigned char *,
unsigned char *,
Field **fields)
{
Field *f;
longlong value;
DBUG_ASSERT(m_row);
for (; (f= *fields) ; fields++)
{
if (bitmap_is_set(table->write_set, f->field_index))
{
switch(f->field_index)
{
case 0: /* NAME */
my_error(ER_WRONG_PERFSCHEMA_USAGE, MYF(0));
return HA_ERR_WRONG_COMMAND;
case 1: /* TIMER_NAME */
value= get_field_enum(f);
if ((value >= FIRST_TIMER_NAME) && (value <=
LAST_TIMER_NAME))
*(m_row->m_timer_name_ptr)= (enum_timer_name) value;
else
return HA_ERR_WRONG_COMMAND;
break;
default:
DBUG_ASSERT(false);
}
}
}
return 0;
}
程式碼裡寫死了，只有'wait' 一個值，不排除以後的版本會增加新的關鍵字，但至
少目前就只有一個啦。
並且這個表的name 欄位是不允許修改的的。下面的修改的方法裡沒有做任何處
理，涉及到name 欄位的修改，直接報錯。
mysql> SELECT * FROM setup_timers;
+------+------------+
| NAME | TIMER_NAME |
+------+------------+
| wait | CYCLE |
+------+------------+
只有timer_name 可以update 這是一個enum 欄位。
一類是event 表
Event 表記錄了各種事件，提供了當前事件（current），歷史事件（history）以
及history_long 事件，history 表記錄了每個執行緒的最近的10 個事件，history_long
記錄了10000 個事件。這兩個歷史表都是先進先出（FIFO)的規則。
效能事件表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE '%current';
+----------------------+
| TABLE_NAME |
+----------------------+
| events_waits_current |
+----------------------+
記錄當前正在發生的等待事件，這個表是隻讀的表，不能update ，delete ，但是
可以truncate
具體欄位是什麼意思就自己去查doc 了，這裡不說了。
效能歷史表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND (TABLE_NAME LIKE '%history' OR TABLE_NAME LIKE
'%history_long');
+---------------------------+
| TABLE_NAME |
+---------------------------+
| events_waits_history |
| events_waits_history_long |
+---------------------------+
這些表與前面的效能表的結構是一致的， history 表只保留每個執行緒（thread）的
最近的10 個事件， history_long 記錄最近的10000 個事件。
新事件入表，如果舊錶滿了，就會丟棄舊的資料，標準的先進先出（FIFO) 這倆
表也是隻讀表，只能truncate
還有一類是summary 表
Summary 表是對event 的統計資料。
事件彙總表：
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'performance_schema'
-> AND TABLE_NAME LIKE '%summary%';
+----------------------------------------------+
| TABLE_NAME |
+----------------------------------------------+
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_global_by_event_name |
| file_summary_by_event_name |
| file_summary_by_instance |
+----------------------------------------------+
按照相關的標準對進行的事件統計表，
events_waits_summary_global_by_event_name 在mysql5.5.7 以前叫：
EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
表也是隻讀的，只能turcate
對於performance schema 下的每個表的詳細定義，以及欄位的解讀，請參考手冊：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-table-descriptions.html
大小寫問題
另外一個問題，performance schema 庫下的表，或欄位名，在查詢的時候，建議
全部小寫，原因是5.5.5 以前是大寫，導致在某些平臺上
lower_case_table_names =1 出現問題，5.5.8 後全部改為小寫字母。
啟動設定問題
Performance schama 預設是不啟用的，啟動的引數為靜態引數，需要在my.cnf 裡
設定performance_schema=1,或者設定命令列引數。
當伺服器啟動起來的時候，會在err 日誌裡記錄一條資訊：
“100407 16:13:02 [Note] Buffered information: Performance schema enabled.”
如果是關閉的則會顯示另外一個資訊：
“100407 16:13:02 [Note] Buffered information: Performance schema disabled (reason:
start parameters)”
還有中情況，設定了引數但是performance schema 啟動失敗，這種情況極少發生，
日誌裡的資訊：
“Performance schema disabled (reason: init failed)”，啟動失敗情況下，performacne
schema 會關閉自己，並且mysql 資料庫會繼續啟動，起結果就跟關閉啟動引數是
一樣的。
相應的系統引數：
mysql.cnt_it.sock@performance_schema> show global variables like
'%performance_schema%' ;
+---------------------------------------------------+---------+
| Variable_name | Value |
+---------------------------------------------------+---------+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+---------------------------------------------------+---------+
詳解這些引數：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-system-variables.html
參考mysql 的官方文件。
對應的狀態引數：
mysql.cnt_house.sock@performance_schema> show global status like
'%performance_schema%' ;
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
+------------------------------------------+-------+
14 rows in set (0.00 sec)
引數詳解：
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-status-variables.html
這些狀態值，顯示的是因為記憶體吃緊，或者系統太忙，導致的一些沒有performance
schema 記錄下來的事件。
對於performance schema 執行狀態如何？
可以使用命令show engine status 來看：
mysql.cnt_house.sock@performance_schema> show engine performance_schema
status \G
*************************** 1. row ***************************
Type: performance_schema
Name: events_waits_current.row_size
Status: 136
*************************** 2. row ***************************
Type: performance_schema
Name: events_waits_current.row_count
Status: 3000
*************************** 3. row ***************************
Type: performance_schema
Name: events_waits_history.row_size
Status: 120
*************************** 4. row ***************************
Type: performance_schema
Name: events_waits_history.row_count
Status: 10000
*************************** 5. row ***************************
Type: performance_schema
Name: events_waits_history.memory
Status: 1200000
*************************** 6. row ***************************
Type: performance_schema
Name: events_waits_history_long.row_size
Status: 120
*************************** 7. row ***************************
Type: performance_schema
Name: events_waits_history_long.row_count
Status: 10000
*************************** 8. row ***************************
Type: performance_schema
Name: events_waits_history_long.memory
Status: 1200000
*************************** 9. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).row_size
Status: 248
*************************** 10. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).row_count
Status: 200
*************************** 11. row ***************************
Type: performance_schema
Name: (pfs_mutex_class).memory
Status: 49600
*************************** 12. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).row_size
Status: 296
*************************** 13. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).row_count
Status: 30
*************************** 14. row ***************************
Type: performance_schema
Name: (pfs_rwlock_class).memory
Status: 8880
*************************** 15. row ***************************
Type: performance_schema
Name: (pfs_cond_class).row_size
Status: 216
*************************** 16. row ***************************
Type: performance_schema
Name: (pfs_cond_class).row_count
Status: 80
*************************** 17. row ***************************
Type: performance_schema
Name: (pfs_cond_class).memory
Status: 17280
*************************** 18. row ***************************
Type: performance_schema
Name: (pfs_thread_class).row_size
Status: 136
*************************** 19. row ***************************
Type: performance_schema
Name: (pfs_thread_class).row_count
Status: 50
*************************** 20. row ***************************
Type: performance_schema
Name: (pfs_thread_class).memory
Status: 6800
*************************** 21. row ***************************
Type: performance_schema
Name: (pfs_file_class).row_size
Status: 240
*************************** 22. row ***************************
Type: performance_schema
Name: (pfs_file_class).row_count
Status: 50
*************************** 23. row ***************************
Type: performance_schema
Name: (pfs_file_class).memory
Status: 12000
*************************** 24. row ***************************
Type: performance_schema
Name: mutex_instances.row_size
Status: 136
*************************** 25. row ***************************
Type: performance_schema
Name: mutex_instances.row_count
Status: 1000000
*************************** 26. row ***************************
Type: performance_schema
Name: mutex_instances.memory
Status: 136000000
*************************** 27. row ***************************
Type: performance_schema
Name: rwlock_instances.row_size
Status: 200
*************************** 28. row ***************************
Type: performance_schema
Name: rwlock_instances.row_count
Status: 1000000
*************************** 29. row ***************************
Type: performance_schema
Name: rwlock_instances.memory
Status: 200000000
*************************** 30. row ***************************
Type: performance_schema
Name: cond_instances.row_size
Status: 88
*************************** 31. row ***************************
Type: performance_schema
Name: cond_instances.row_count
Status: 1000
*************************** 32. row ***************************
Type: performance_schema
Name: cond_instances.memory
Status: 88000
*************************** 33. row ***************************
Type: performance_schema
Name: threads.row_size
Status: 504
*************************** 34. row ***************************
Type: performance_schema
Name: threads.row_count
Status: 1000
*************************** 35. row ***************************
Type: performance_schema
Name: threads.memory
Status: 504000
*************************** 36. row ***************************
Type: performance_schema
Name: file_instances.row_size
Status: 624
*************************** 37. row ***************************
Type: performance_schema
Name: file_instances.row_count
Status: 10000
*************************** 38. row ***************************
Type: performance_schema
Name: file_instances.memory
Status: 6240000
*************************** 39. row ***************************
Type: performance_schema
Name: (pfs_file_handle).row_size
Status: 8
*************************** 40. row ***************************
Type: performance_schema
Name: (pfs_file_handle).row_count
Status: 32768
*************************** 41. row ***************************
Type: performance_schema
Name: (pfs_file_handle).memory
Status: 262144
*************************** 42. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.row_size
Status: 48
*************************** 43. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.row_count
Status: 360000
*************************** 44. row ***************************
Type: performance_schema
Name: events_waits_summary_by_thread_by_event_name.memory
Status: 17280000
*************************** 45. row ***************************
Type: performance_schema
Name: (pfs_table_share).row_size
Status: 488
*************************** 46. row ***************************
Type: performance_schema
Name: (pfs_table_share).row_count
Status: 50000
*************************** 47. row ***************************
Type: performance_schema
Name: (pfs_table_share).memory
Status: 24400000
*************************** 48. row ***************************
Type: performance_schema
Name: (pfs_table).row_size
Status: 72
*************************** 49. row ***************************
Type: performance_schema
Name: (pfs_table).row_count
Status: 100000
*************************** 50. row ***************************
Type: performance_schema
Name: (pfs_table).memory
Status: 7200000
*************************** 51. row ***************************
Type: performance_schema
Name: performance_schema.memory
Status: 394468704
51 rows in set (0.00 sec)
三個命令：
Show engine innodb status
Show engine innodb mutex
Show engine performacne_schema status
統計事件/物件可以分為幾類：
1）wait/io io 等待事件
2）wait/io/file 檔案io等待，一般是指等待檔案操作完成。
3）wait/sync 等待同步物件，內容比較廣泛，他的time_wait 時間包括了，
請求對一個物件加鎖，因為阻塞而導致的等待時間。
4）wait/sync/cond 主要用於執行緒間同步，引起的等待事件。
5）wait/sync/mutex 對資源訪問的的互斥體的請求等待，mutex 對資源的
獨佔式訪問，的一種鎖結構。
6）wait/sync/rwlock 對變數訪問、修改的鎖等待。主意是對變數，主要用
於執行緒安全的變數訪問。
這些表的上的操作限制：
對於setup 表上，有些欄位是可以update 的，用於控制監控的開啟關閉。
對於event_wait_* 這樣的表式可以truncate 的，但是不能delete，
update。可以在特定的診斷任務開始前，清空原來的資料。
對於summary 的表，也可以執行truncate 操作，但是隻是把summary的統
計欄位置0或者null ，而不會刪除裡面的行記錄。
用performance schema 診斷效能問題的一般步驟：
1.執行測試case
2.開啟performance schema 收集資訊
3.排除沒有干擾的因素，並關閉對應的收集器，例如確定file i/o 不是問題的根本
原因則可以關閉file io 收集，truncate event 表，然後從新收集效能資料。
4.重複1-3，越來越接近根本原因，也可以從events_waits_history_long
表的統計分析，問題部分會越來越集中。
5.一旦確定了問題的原因，就可以著手解決問題，最佳化效能。
6.mutex_instances.LOCKED_BY_THREAD_ID 和
rwlock_instances.WRITE_LOCKED_BY_THREAD_ID
這兩個欄位對於發現效能瓶頸，或則死鎖是非常重要的。
1）假設thread1 在等待某個mutex
2）那麼可以看看thread1 在等待什麼資源。
SELECT * FROM events_waits_current WHERE THREAD_ID =
thread_1;
根據其中的OBJECT_INSTANCE_BEGIN 去關聯mutex_instances 表確
定是什麼當前資源被那個執行緒佔有，
mysql.cnt_it.sock@performance_schema> SELECT * FROM
mutex_instances where OBJECT_INSTANCE_BEGIN = 15806424;
+------------------------------------+------------------
-----+---------------------+
| NAME | OBJECT_INSTANCE_BEGIN |
LOCKED_BY_THREAD_ID |
+------------------------------------+------------------
-----+---------------------+
| wait/synch/mutex/sql/LOG::LOCK_log |
15805160 | 1305458 |
+------------------------------------+------------------
-----+---------------------+
1 row in set (0.02 sec)
3）。我們就可以去看看這執行緒當前正在做什麼。
mysql.cnt_it.sock@performance_schema> select * from
events_waits_current where thread_id =1305458 \G
*************************** 1. row
***************************
THREAD_ID: 1305458
EVENT_ID: 76094
EVENT_NAME:
wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond
SOURCE: log.cc:5535
TIMER_START: 8958895120369531348
TIMER_END: NULL
TIMER_WAIT: NULL
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 15806424
NESTING_EVENT_ID: NULL
OPERATION: timed_wait
NUMBER_OF_BYTES: NULL
FLAGS: 0
1 row in set (0.00 sec)
這是一個示例，我們測試環境，捕捉不到，我就拿了一個bin log 的寫執行緒。
Sql 示例：
1等待事件的top N
mysql> SELECT EVENT_NAME, SUM(TIMER_WAIT), COUNT(*), SOURCE
-> FROM EVENTS_WAITS_HISTORY_LONG
-> WHERE EVENT_NAME LIKE “%innodb%”
-> GROUP BY SOURCE
-> ORDER BY SUM(TIMER_WAIT) DESC;
2平均等待時間top N :
mysql> SELECT EVENT_NAME, SUM(TIMER_WAIT)/count(*), source
-> FROM EVENTS_WAITS_HISTORY_LONG
-> WHERE EVENT_NAME LIKE “%innodb%”
-> GROUP BY source
-> ORDER BY SUM(TIMER_WAIT) / COUNT(*) DESC;
EVENTS_WAITS_HISTORY 每個執行緒10 行記錄，
EVENTS_WAITS_HISTORY_LONG 表預設是10000 行記錄，如果認為尺寸不夠
大，可以透過修改引數：
“performance_schema_events_waits_history_size”
“performance_schema_events_waits_history_long_size”
這兩個變數來調整， history_long 表的最大尺寸是100W 行記錄。
3最熱的mutex ：
這裡的時間單位是微秒（pico-second，或者說是cpu 頻率週期）
mysql> SELECT EVENT_NAME, COUNT_STAR,SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%innodb%'
order BY COUNT_STAR DESC;
從下面的結果，我們可以大體瞭解下，我們電腦網計數器的情況：
Buff pool 和日誌和undo 是比較忙的，需要最佳化的。
*************************** 1. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/kernel_mutex
COUNT_STAR: 183768448476
SUM_TIMER_WAIT: 4527315253253694
AVG_TIMER_WAIT: 24635
*************************** 2. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/buf_pool_mutex
COUNT_STAR: 15121853525
SUM_TIMER_WAIT: 3285302101091527
AVG_TIMER_WAIT: 217255
*************************** 3. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/log_sys_mutex
COUNT_STAR: 7142949915
SUM_TIMER_WAIT: 1559525178971936
AVG_TIMER_WAIT: 218330
*************************** 4. row
***************************
EVENT_NAME:
wait/synch/mutex/innodb/log_flush_order_mutex
COUNT_STAR: 6649384948
SUM_TIMER_WAIT: 691589270730534
AVG_TIMER_WAIT: 104008
*************************** 5. row
***************************
EVENT_NAME: wait/synch/mutex/innodb/trx_undo_mutex
COUNT_STAR: 3615217180
SUM_TIMER_WAIT: 112854228995684
AVG_TIMER_WAIT: 31216
PERFORMANCE SCHEMA 的效能問題：
這部分沒有做過測試，資料來自網上
全部預設開啟performance schema 大約會有8% 左右的效能消耗，
如果開啟了performance schema ，但是在setup 表中關閉事件記錄，大約
會有4% 左右的效能損失。
我們的建議：
安裝mysql 的時候，開啟performance schema 引數，
並在setup 表中關閉事件記錄，這樣便於在遇到嚴重問題，需要開啟效能日誌
的時候，需要重啟mysql

mysql performance schema 第2部分

相關文章