MySQL調優效能監控之performance schema

PHPer技術棧發表於2021-11-09

performance:效能

schema:圖(表)示,以大綱或模型的形式表示計劃或理論。

MySQL的performance schema 用於監控MySQL server在一個較低階別的執行過程中的資源消耗、資源等待等情況。

特點

1、提供了一種在資料庫執行時實時檢查server的內部執行情況的方法。performance_schema 資料庫中的表使用performance_schema儲存引擎。該資料庫主要關注資料庫執行過程中的效能相關的資料，與information_schema不同，information_schema主要關注server執行過程中的後設資料資訊

2、performance_schema通過監視server的事件來實現監視server內部執行情況， 事件就是server內部活動中所做的任何事情以及對應的時間消耗 ，利用這些資訊來判斷server中的相關資源消耗在了哪裡？一般來說，事件可以是函式呼叫、作業系統的等待、SQL語句執行的階段（如sql語句執行過程中的parsing 或 sorting階段）或者整個SQL語句與SQL語句集合。事件的採集可以方便的提供server中的相關儲存引擎對磁碟檔案、表I/O、表鎖等資源的同步呼叫資訊。
3、performance_schema中的事件與寫入二進位制日誌中(binary Log)的事件（描述資料修改的events）、事件計劃排程程式（這是一種儲存程式）的事件不同。performance_schema中的事件記錄的是server執行某些活動對某些資源的消耗、耗時、這些活動執行的次數等情況。
4、performance_schema中的事件只記錄在本地server的performance_schema中，其下的這些表中資料發生變化時不會被寫入binlog中，也不會通過複製機制被複制到其他server中。
5、當前活躍事件、歷史事件和事件摘要相關的表中記錄的資訊。能提供某個事件的執行次數、使用時長。進而可用於分析某個特定執行緒、特定物件（如mutex或file）相關聯的活動。
6、PERFORMANCE_SCHEMA儲存引擎使用server原始碼中的“檢測點”來實現事件資料的收集。對於performance_schema實現機制本身的程式碼沒有相關的單獨執行緒來檢測，這與其他功能（如複製或事件計劃程式）不同
7、收集的事件資料儲存在performance_schema資料庫的表中。這些表可以使用SELECT語句查詢，也可以使用SQL語句更新performance_schema資料庫中的表記錄（如動態修改performance_schema的setup_*開頭的幾個配置表，但要注意：配置表的更改會立即生效，這會影響資料收集）
8、performance_schema的表中的資料不會持久化儲存在磁碟中，而是儲存在記憶體中，一旦伺服器重啟，這些資料會丟失（包括配置表在內的整個performance_schema下的所有資料）
9、MySQL支援的所有平臺中事件監控功能都可用，但不同平臺中用於統計事件時間開銷的計時器型別可能會有所差異。

在mysql的5.7版本中，效能模式是預設開啟的，如果想要顯式的關閉的話需要修改配置檔案，不能直接進行修改，會報錯Variable ‘performance_schema’ is a read only variable。

–檢視performance_schema的屬性

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

–在配置檔案中修改performance_schema的屬性值，on表示開啟，off表示關閉
[mysqld]

performance_schema=ON

1、切換資料庫

use performance_schema;

2、檢視當前資料庫下表儲存的資訊

mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
                ......省略......
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |
+------------------------------------------------------+
87 rows in set (0.00 sec)

3、通過show create table tablename來檢視建立表時的表結構

mysql> show create table setup_consumers;
+-----------------+---------------------------------------------------------------------+
| Table           | Create Table                                                   
+-----------------+---------------------------------------------------------------------+
| setup_consumers | CREATE TABLE `setup_consumers` (
  `NAME` varchar(64) NOT NULL,
  `ENABLED` enum('YES','NO') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

4、兩個基本概念

instruments: 生產者，用於採集mysql中各種各樣的操作產生的事件資訊，對應配置表中的配置項我們可以稱為監控採集配置項。

consumers:消費者，對應的消費者表用於儲存來自instruments採集的資料，對應配置表中的配置項我們可以稱為消費儲存配置項。

performance_schema庫下的表可以按照監視不同的緯度就行分組。

1、語句事件記錄表

–語句事件記錄表，這些表記錄了語句事件資訊，當前語句事件表events_statements_current、歷史語句事件表events_statements_history和長語句歷史事件表events_statements_history_long、以及聚合後的摘要表summary，其中，summary表還可以根據帳號(account)，主機(host)，程式(program)，執行緒(thread)，使用者(user)和全域性(global)再進行細分)

mysql> show tables like '%statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (%statement%)         |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
| prepared_statements_instances                      |
+----------------------------------------------------+
11 rows in set (0.00 sec)

2、等待事件記錄表

–等待事件記錄表，與語句事件型別的相關記錄表類似：

mysql> show tables like '%wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (%wait%)         |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
| table_io_waits_summary_by_index_usage         |
| table_io_waits_summary_by_table               |
| table_lock_waits_summary_by_table             |
+-----------------------------------------------+
12 rows in set (0.00 sec)

3、階段事件記錄表

–階段事件記錄表，記錄語句執行的階段事件的表

mysql> show tables like '%stage%';
+------------------------------------------------+
| Tables_in_performance_schema (%stage%)         |
+------------------------------------------------+
| events_stages_current                          |
| events_stages_history                          |
| events_stages_history_long                     |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name    |
| events_stages_summary_by_thread_by_event_name  |
| events_stages_summary_by_user_by_event_name    |
| events_stages_summary_global_by_event_name     |
+------------------------------------------------+
8 rows in set (0.00 sec)

4、事務事件記錄表

–事務事件記錄表，記錄事務相關的事件的表

mysql> show tables like '%transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (%transaction%)         |
+------------------------------------------------------+
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+------------------------------------------------------+
8 rows in set (0.00 sec)

5、監控檔案系統層呼叫的表

–監控檔案系統層呼叫的表

mysql> show tables like '%file%';
+---------------------------------------+
| Tables_in_performance_schema (%file%) |
+---------------------------------------+
| file_instances                        |
| file_summary_by_event_name            |
| file_summary_by_instance              |
+---------------------------------------+
3 rows in set (0.00 sec)

6、監視記憶體使用的表

--監視記憶體使用的表
mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name    |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name    |
| memory_summary_global_by_event_name     |
+-----------------------------------------+
5 rows in set (0.00 sec)

7、配置表

–動態對performance_schema進行配置的配置表

mysql> show tables like '%setup%';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+----------------------------------------+
5 rows in set (0.00 sec)

資料庫剛剛初始化並啟動時，並非所有instruments(單詞意思是儀器,這兒理解為事件採集器，在採集器的配置表中每一項都有一個開關欄位，或為YES，或為NO)和consumers(與採集器類似，這兒理解為儲存器,也有一個對應的事件型別儲存表配置項，為YES就表示對應的表儲存效能資料，為NO就表示對應的表不儲存效能資料)都啟用了，所以預設不會收集所有的事件，可能你需要檢測的事件並沒有開啟，需要進行設定，可以使用如下兩個語句開啟對應的instruments和consumers（行計數可能會因MySQL版本而異)。

1、事件的採集器配置項開關

–開啟等待事件的採集器配置項開關，需要修改setup_instruments配置表中對應的採集器配置項

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';

–開啟等待事件的儲存表配置開關，修改setup_consumers配置表中對應的配置項

UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';

–當配置完成之後可以檢視當前server正在做什麼，可以通過查詢events_waits_current表來得知，該表中每個執行緒只包含一行資料，用於顯示每個執行緒的最新監視事件

select * from events_waits_current\G
*************************** 1. row ***************************
            THREAD_ID: 11
             EVENT_ID: 570
         END_EVENT_ID: 570
           EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
               SOURCE: 
          TIMER_START: 4508505105239280
            TIMER_END: 4508505105270160
           TIMER_WAIT: 30880
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
           INDEX_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 67918392
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: NULL

屬性說明

該資訊表示執行緒id為11的執行緒正在等待buf_dblwr_mutex鎖，等待事件為30880

id:事件來自哪個執行緒，事件編號是多少
event_name:表示檢測到的具體的內容
source:表示這個檢測程式碼在哪個原始檔中以及行號
timer_start:表示該事件的開始時間
timer_end:表示該事件的結束時間
timer_wait:表示該事件總的花費時間

注意

current表中每個執行緒只保留一條記錄，一旦執行緒完成工作，該表中不會再記錄該執行緒的事件資訊

2、history表:記錄每個執行緒應該執行完成的事件資訊

mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;
+-----------+----------+-----------------------------------------------+------------+
| thread_id | event_id | event_name                                    | timer_wait |
+-----------+----------+-----------------------------------------------+------------+
|        10 |     5571 | wait/synch/mutex/innodb/buf_pool_mutex        |     146784 |
|        10 |     5572 | wait/synch/mutex/innodb/buf_pool_mutex        |      18904 |
|        10 |     5573 | wait/synch/mutex/innodb/flush_list_mutex      |      91740 |
|        10 |     5574 | wait/synch/mutex/innodb/buf_pool_mutex        |      20016 |
|        10 |     5575 | wait/synch/mutex/innodb/buf_dblwr_mutex       |      30580 |
|        10 |     5566 | wait/synch/mutex/innodb/buf_pool_mutex        |     125656 |
|        10 |     5567 | wait/synch/mutex/innodb/buf_pool_mutex        |      19460 |
|        10 |     5568 | wait/synch/mutex/innodb/flush_list_mutex      |      76728 |
|        10 |     5569 | wait/synch/mutex/innodb/buf_pool_mutex        |      19460 |
|        10 |     5570 | wait/synch/mutex/innodb/buf_dblwr_mutex       |      38364 |
|        15 |    10030 | wait/synch/mutex/innodb/log_sys_mutex         |      20572 |
|        15 |    10029 | wait/synch/mutex/innodb/dict_sys_mutex        |      22796 |
|        15 |    10028 | wait/synch/sxlock/innodb/dict_operation_lock  |    1043056 |
|        15 |    10027 | wait/synch/mutex/innodb/row_drop_list_mutex   |     135664 |
|        15 |    10026 | wait/synch/mutex/innodb/flush_list_mutex      |     212392 |
|        15 |    10035 | wait/synch/mutex/innodb/flush_list_mutex      |     110644 |
|        15 |    10034 | wait/synch/mutex/innodb/log_flush_order_mutex |      24464 |
|        15 |    10033 | wait/synch/mutex/innodb/log_sys_mutex         |      21128 |
|        15 |    10032 | wait/synch/mutex/innodb/fil_system_mutex      |      78952 |
|        15 |    10031 | wait/synch/mutex/innodb/log_sys_write_mutex   |      20016 |
|        21 |     3361 | wait/synch/mutex/innodb/log_sys_mutex         |     131772 |
+-----------+----------+-----------------------------------------------+------------+
21 rows in set (0.00 sec)

history表中記錄每個執行緒應該執行完成的事件資訊，但每個執行緒的事件資訊只會記錄10條，再多就會被覆蓋，history_long表中記錄所有執行緒的事件資訊，但總記錄數量是10000，超過就會被覆蓋掉

3、summary:所有事件的彙總資訊

mysql> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name 
 ORDER BY COUNT_STAR DESC LIMIT 10;
+-----------------------------------------------+------------+
| EVENT_NAME                                    | COUNT_STAR |
+-----------------------------------------------+------------+
| wait/io/file/sql/FRM                          |       2944 |
| wait/synch/mutex/innodb/buf_pool_mutex        |       2808 |
| wait/synch/mutex/innodb/log_sys_mutex         |       2805 |
| wait/synch/mutex/innodb/flush_list_mutex      |       1871 |
| wait/synch/mutex/innodb/sync_array_mutex      |       1870 |
| wait/io/file/innodb/innodb_data_file          |       1619 |
| wait/synch/mutex/innodb/buf_dblwr_mutex       |        936 |
| wait/synch/mutex/innodb/dict_sys_mutex        |        935 |
| wait/synch/mutex/innodb/log_flush_order_mutex |        935 |
| wait/synch/mutex/innodb/fil_system_mutex      |        935 |
+-----------------------------------------------+------------+
10 rows in set (0.08 sec)

summary表提供所有事件的彙總資訊，該組中的表以不同的方式彙總事件資料（如：按使用者，按主機，按執行緒等等）。例如：要檢視哪些instruments佔用最多的時間，可以通過對events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列進行查詢（這兩列是對事件的記錄數執行COUNT（*）、事件記錄的TIMER_WAIT列執行SUM（TIMER_WAIT）統計而來）

4、instance表:記錄哪些型別的物件會被檢測

mysql> select * from file_instances limit 20; 
+------------------------------------------+--------------------------------------+------------+
| FILE_NAME                                     | EVENT_NAME                           | OPEN_COUNT |
+------------------------------------------+--------------------------------------+------------+
| /usr/share/mysql/english/errmsg.sys      | wait/io/file/sql/ERRMSG              |          0 |
| /usr/share/mysql/charsets/Index.xml      | wait/io/file/mysys/charset           |          0 |
| /var/lib/mysql/ibdata1                   | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/ib_logfile0               | wait/io/file/innodb/innodb_log_file  |          2 |
| /var/lib/mysql/ib_logfile1               | wait/io/file/innodb/innodb_log_file  |          2 |
| /var/lib/mysql/mysql/engine_cost.ibd     | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/gtid_executed.ibd   | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/help_category.ibd   | wait/io/file/innodb/innodb_data_file |          1 |
| /var/lib/mysql/mysql/help_keyword.ibd    | wait/io/file/innodb/innodb_data_file |          1 |
| /var/lib/mysql/mysql/help_relation.ibd   | wait/io/file/innodb/innodb_data_file |          1 |
| /var/lib/mysql/mysql/help_topic.ibd      | wait/io/file/innodb/innodb_data_file |          1 |
| /valib/mysql/mysql/innodb_index_stats.ibd| wait/io/file/innodb/innodb_data_file |          3 |
| /va/mysql/mysql/innodb_table_stats.ibd   | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/plugin.ibd          | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/server_cost.ibd     | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/servers.ibd         | wait/io/file/innodb/innodb_data_file |          3 |
| /va/mysql/mysql/slave_master_info.ibd    | wait/io/file/innodb/innodb_data_file |          3 |
| /va/mysql/mysql/slave_relay_log_info.ibd | wait/io/file/innodb/innodb_data_file |          3 |
| lib/mysql/mysql/slave_worker_info.ibd    | wait/io/file/innodb/innodb_data_file |          3 |
| /ib/mysql/mysql/time_zone.ibd            | wait/io/file/innodb/innodb_data_file |          3 |
+------------------------------------------+--------------------------------------+------------+
20 rows in set (0.00 sec)

instance表記錄了哪些型別的物件會被檢測。這些物件在被server使用時，在該表中將會產生一條事件記錄，例如，file_instances表列出了檔案I/O操作及其關聯檔名

1、啟動選項

performance_schema_consumer_events_statements_current=TRUE
是否在mysql server啟動時就開啟events_statements_current表的記錄功能(該表記錄當前的語句事件資訊)，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動態更新setup_consumers配置表中的events_statements_current配置項，預設值為TRUE

performance_schema_consumer_events_statements_history=TRUE
與performance_schema_consumer_events_statements_current選項類似，但該選項是用於配置是否記錄語句事件短歷史資訊，預設為TRUE

performance_schema_consumer_events_stages_history_long=FALSE
與performance_schema_consumer_events_statements_current選項類似，但該選項是用於配置是否記錄語句事件長曆史資訊，預設為FALSE

除了statement(語句)事件之外，還支援：wait(等待)事件、state(階段)事件、transaction(事務)事件，他們與statement事件一樣都有三個啟動項分別進行配置，但這些等待事件預設未啟用，如果需要在MySQL Server啟動時一同啟動，則通常需要寫進my.cnf配置檔案中
performance_schema_consumer_global_instrumentation=TRUE
是否在MySQL Server啟動時就開啟全域性表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全域性物件計數統計和事件彙總統計資訊表）的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動態更新全域性配置項
預設值為TRUE

performance_schema_consumer_statements_digest=TRUE
是否在MySQL Server啟動時就開啟events_statements_summary_by_digest 表的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動態更新digest配置項
預設值為TRUE

performance_schema_consumer_thread_instrumentation=TRUE
是否在MySQL Server啟動時就開啟

events_xxx_summary_by_yyy_by_event_name表的記錄功能，啟動之後也可以在setup_consumers表中使用UPDATE語句進行動態更新執行緒配置項
預設值為TRUE

performance_schema_instrument[=name]
是否在MySQL Server啟動時就啟用某些採集器，由於instruments配置項多達數千個，所以該配置項支援key-value模式，還支援%號進行通配等，如下:

指定開啟單個instruments

--performance-schema-instrument= 'instrument_name=value'

使用萬用字元指定開啟多個instruments

--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'

開關所有的instruments

--performance-schema-instrument= '%=ON'

--performance-schema-instrument= '%=OFF'

注意

這些啟動選項要生效的前提是，需要設定performance_schema=ON。另外，這些啟動選項雖然無法使用show variables語句檢視，但我們可以通過setup_instruments和setup_consumers表查詢這些選項指定的值。

2、系統變數

show variables like '%performance_schema%';
--重要的屬性解釋
performance_schema=ON
/*
控制performance_schema功能的開關，要使用MySQL的performance_schema，需要在mysqld啟動時啟用，以啟用事件收集功能
該引數在5.7.x之前支援performance_schema的版本中預設關閉，5.7.x版本開始預設開啟
注意：如果mysqld在初始化performance_schema時發現無法分配任何相關的內部緩衝區，則performance_schema將自動禁用，並將performance_schema設定為OFF
*/

performance_schema_digests_size=10000
/*
控制events_statements_summary_by_digest表中的最大行數。如果產生的語句摘要資訊超過此最大值，便無法繼續存入該表，此時performance_schema會增加狀態變數
*/
performance_schema_events_statements_history_long_size=10000
/*
控制events_statements_history_long表中的最大行數，該引數控制所有會話在events_statements_history_long表中能夠存放的總事件記錄數，超過這個限制之後，最早的記錄將被覆蓋
全域性變數，只讀變數，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本預設為10000，5.6.6及其之後的版本預設值為-1，通常情況下，自動計算的值都是10000 * 5.7.x版本中，預設值為-1，通常情況下，自動計算的值都是10000
*/
performance_schema_events_statements_history_size=10
/*
控制events_statements_history表中單個執行緒（會話）的最大行數，該引數控制單個會話在events_statements_history表中能夠存放的事件記錄數，超過這個限制之後，單個會話最早的記錄將被覆蓋
全域性變數，只讀變數，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本預設為10，5.6.6及其之後的版本預設值為-1，通常情況下，自動計算的值都是10 * 5.7.x版本中，預設值為-1，通常情況下，自動計算的值都是10
除了statement(語句)事件之外，wait(等待)事件、state(階段)事件、transaction(事務)事件，他們與statement事件一樣都有三個引數分別進行儲存限制配置，有興趣的同學自行研究，這裡不再贅述
*/
performance_schema_max_digest_length=1024
/*
用於控制標準化形式的SQL語句文字在存入performance_schema時的限制長度，該變數與max_digest_length變數相關(max_digest_length變數含義請自行查閱相關資料)
全域性變數，只讀變數，預設值1024位元組，整型值，取值範圍0~1048576
*/
performance_schema_max_sql_text_length=1024
/*
控制存入events_statements_current，events_statements_history和events_statements_history_long語句事件表中的SQL_TEXT列的最大SQL長度位元組數。 超出系統變數performance_schema_max_sql_text_length的部分將被丟棄，不會記錄，一般情況下不需要調整該引數，除非被截斷的部分與其他SQL比起來有很大差異
全域性變數，只讀變數，整型值，預設值為1024位元組，取值範圍為0~1048576，5.7.6版本引入
降低系統變數performance_schema_max_sql_text_length值可以減少記憶體使用，但如果彙總的SQL中，被截斷部分有較大差異，會導致沒有辦法再對這些有較大差異的SQL進行區分。 增加該系統變數值會增加記憶體使用，但對於彙總SQL來講可以更精準地區分不同的部分。
*/

配置表之間存在相互關聯關係，按照配置影響的先後順序，可新增為:

1、performance_timers表

中記錄了server中有哪些可用的事件計時器

mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE       |      3599393034 |                1 |             22 |
| NANOSECOND  |      1000000000 |                1 |             70 |
| MICROSECOND |         1000000 |                1 |             76 |
| MILLISECOND |            1037 |                1 |             76 |
| TICK        |             103 |                1 |           1800 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)

欄位解釋

timer_name:表示可用計時器名稱，CYCLE是基於CPU週期計數器的定時器
timer_frequency:表示每秒鐘對應的計時器單位的數量,CYCLE計時器的換算值與CPU的頻率相關、
timer_resolution:計時器精度值，表示在每個計時器被呼叫時額外增加的值
timer_overhead:表示在使用定時器獲取事件時開銷的最小週期值

2、setup_timers

表中記錄當前使用的事件計時器資訊

mysql> select * from setup_timers;
+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | NANOSECOND  |
| statement   | NANOSECOND  |
| transaction | NANOSECOND  |
+-------------+-------------+
5 rows in set, 1 warning (0.00 sec)

欄位解釋

name:計時器型別，對應某個事件類別
timer_name:計時器型別名稱

3、setup_consumers表

列出了consumers可配置列表項

mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | YES     |
| events_waits_history             | YES     |
| events_waits_history_long        | YES     |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+
15 rows in set (0.00 sec)

欄位解釋

NAME：consumers配置名稱
ENABLED：consumers是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。

4、setup_instruments 表

列出了instruments 列表配置項，即代表了哪些事件支援被收集：

SELECT * FROM setup_instruments;
1023 rows in set (0.00 sec)

欄位解釋

NAME：instruments名稱，instruments名稱可能具有多個部分並形成層次結構
ENABLED：instrumetns是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。如果設定為NO，則這個instruments不會被執行，不會產生任何的事件資訊
TIMED：instruments是否收集時間資訊，有效值為YES或NO，此列可以使用UPDATE語句修改，如果設定為NO，則這個instruments不會收集時間資訊

5、setup_actors表

初始內容是匹配任何使用者和主機，因此對於所有前臺執行緒，預設情況下啟用監視和歷史事件收集功能

mysql> SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

欄位解釋

HOST：與grant語句類似的主機名，一個具體的字串名字，或使用“％”表示“任何主機”
USER：一個具體的字串名稱，或使用“％”表示“任何使用者”
ROLE：當前未使用，MySQL 8.0中才啟用角色功能
ENABLED：是否啟用與HOST，USER，ROLE匹配的前臺執行緒的監控功能，有效值為：YES或NO
HISTORY：是否啟用與HOST， USER，ROLE匹配的前臺執行緒的歷史事件記錄功能，有效值為：YES或NO

6、setup_objects表

控制performance_schema是否監視特定物件。預設情況下，此表的最大行數為100行。

mysql> SELECT * FROM setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT       | mysql              | %           | NO      | NO    |
| EVENT       | performance_schema | %           | NO      | NO    |
| EVENT       | information_schema | %           | NO      | NO    |
| EVENT       | %                  | %           | YES     | YES   |
| FUNCTION    | mysql              | %           | NO      | NO    |
| FUNCTION    | performance_schema | %           | NO      | NO    |
| FUNCTION    | information_schema | %           | NO      | NO    |
| FUNCTION    | %                  | %           | YES     | YES   |
| PROCEDURE   | mysql              | %           | NO      | NO    |
| PROCEDURE   | performance_schema | %           | NO      | NO    |
| PROCEDURE   | information_schema | %           | NO      | NO    |
| PROCEDURE   | %                  | %           | YES     | YES   |
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
| TRIGGER     | mysql              | %           | NO      | NO    |
| TRIGGER     | performance_schema | %           | NO      | NO    |
| TRIGGER     | information_schema | %           | NO      | NO    |
| TRIGGER     | %                  | %           | YES     | YES   |
+-------------+--------------------+-------------+---------+-------+
20 rows in set (0.00 sec)

欄位解釋

OBJECT_TYPE：instruments型別，有效值為：“EVENT”（事件排程器事件）、“FUNCTION”（儲存函式）、“PROCEDURE”（儲存過程）、“TABLE”（基表）、“TRIGGER”（觸發器），TABLE物件型別的配置會影響表I/O事件（wait/io/table/sql/handler instrument）和表鎖事件（wait/lock/table/sql/handler instrument）的收集
OBJECT_SCHEMA：某個監視型別物件涵蓋的資料庫名稱，一個字串名稱，或“％”(表示“任何資料庫”)
OBJECT_NAME：某個監視型別物件涵蓋的表名，一個字串名稱，或“％”(表示“任何資料庫內的物件”)
ENABLED：是否開啟對某個型別物件的監視功能，有效值為：YES或NO。此列可以修改
TIMED：是否開啟對某個型別物件的時間收集功能，有效值為：YES或NO，此列可以修改

7、threads表

對於每個server執行緒生成一行包含執行緒相關的資訊

select * from threads; 26 rows in set (0.00 sec)

欄位解釋

THREAD_ID：執行緒的唯一識別符號（ID）
NAME：與server中的執行緒檢測程式碼相關聯的名稱(注意，這裡不是instruments名稱)
TYPE：執行緒型別，有效值為：FOREGROUND、BACKGROUND。分別表示前臺執行緒和後臺執行緒
PROCESSLIST_ID：對應INFORMATION_SCHEMA.PROCESSLIST表中的ID列。
PROCESSLIST_USER：與前臺執行緒相關聯的使用者名稱，對於後臺執行緒為NULL。
PROCESSLIST_HOST：與前臺執行緒關聯的客戶端的主機名，對於後臺執行緒為NULL。
PROCESSLIST_DB：執行緒的預設資料庫，如果沒有，則為NULL。
PROCESSLIST_COMMAND：對於前臺執行緒，該值代表著當前客戶端正在執行的command型別，如果是sleep則表示當前會話處於空閒狀態
PROCESSLIST_TIME：當前執行緒已處於當前執行緒狀態的持續時間（秒）
PROCESSLIST_STATE：表示執行緒正在做什麼事情。
PROCESSLIST_INFO：執行緒正在執行的語句，如果沒有執行任何語句，則為NULL。
PARENT_THREAD_ID：如果這個執行緒是一個子執行緒（由另一個執行緒生成），那麼該欄位顯示其父執行緒ID
ROLE：暫未使用
INSTRUMENTED：執行緒執行的事件是否被檢測。有效值：YES、NO
HISTORY：是否記錄執行緒的歷史事件。有效值：YES、NO *
THREAD_OS_ID：由作業系統層定義的執行緒或任務識別符號（ID）：

注意

在performance_schema庫中還包含了很多其他的庫和表，能對資料庫的效能做完整的監控，大家需要參考官網詳細瞭解。

基本瞭解了表的相關資訊之後，可以通過這些表進行實際的查詢操作來進行實際的分析。

--1、哪類的SQL執行最多？
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪類SQL的平均響應時間最多？
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪類SQL排序記錄數最多？
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪類SQL掃描記錄數最多？
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪類SQL使用臨時表最多？
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪類SQL返回結果集最多？
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪個表物理IO最多？
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪個表邏輯IO最多？
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪個索引訪問最多？
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪個索引從來沒有用過？
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;
--11、哪個等待事件消耗時間最多？
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某條SQL的執行情況，包括statement資訊，stege資訊，wait資訊
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、檢視每個階段的時間消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、檢視每個階段的鎖等待情況
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event

來源：www.cnblogs.com/Courage129/p/14188...

本作品採用《CC 協議》，轉載必須註明作者和本文連結

PHPer技術棧

MySQL調優效能監控之show profile
2021-11-09
MySql
Performance --- 前端效能監控
2019-06-05
ORM前端
MySQL Performance Schema
2023-12-12
MySqlORM
MySQL調優使用者監控之show processlist
2021-11-09
MySql
mysql監控、效能調優及三正規化理解
2014-12-15
MySql
前端效能監控-window.performance（轉）
2018-05-23
前端ORM
Flutter效能監控工具（2）— Performance Overlay
1970-01-01
FlutterORM
MySQL Performance Schema詳解
2022-03-15
MySqlORM
linux 效能監控分析以及調優(top)
2017-02-13
Linux
初探 performance – 監控網頁與程式效能
2015-09-04
ORM網頁
MySQL 5.7 Performance Schema 介紹
2016-05-12
MySqlORM
MySQL預設資料庫之performance_schema庫
2019-07-21
MySql資料庫ORM
mysql5.5 performance_schema 初探
2011-01-20
MySqlORM
jvm系列(五):tomcat效能調優和效能監控（visualvm）
2016-04-11
JVMTomcatLVM
MySQL管理之道，效能調優，高可用與監控（第二版）pdf下載
2021-12-18
MySql
mysql效能監控相關
2019-06-13
MySql
mysql效能監控指標
2012-06-01
MySql指標
MySQL5.6 PERFORMANCE_SCHEMA 說明
2018-09-19
MySqlORM
mysql performance schema 第2部分
2015-07-10
MySqlORM
MySQL 效能調優技巧
2017-12-06
MySql
Linux優化之IO子系統監控與調優
2014-09-22
Linux優化
Java生產環境效能監控與調優—基於JDK命令列工具的監控
2018-10-10
JavaJDK命令列
MySQL監控-Datadog資料庫監控調研
2021-11-24
MySql資料庫
Mysql效能監控視覺化
2021-01-10
MySql視覺化
MySQL 效能監控工具--mysqlreport
2010-12-20
MySql
效能監視器- Performance Monitor
2015-01-08
ORM
Mysql 效能調優一 1
2014-12-25
MySql
Mysql 效能調優一 2
2014-12-25
MySql
Mysql 效能調優一 3
2014-12-25
MySql
Mysql 效能調優二 1
2014-12-25
MySql
Mysql 效能調優二 2
2014-12-25
MySql
MySQL效能調優"經驗"
2012-09-08
MySql
zabbix和mysql performance monitor模板實現mysql資料庫的監控
2015-08-31
MySqlORM資料庫
MySQL管理之道_ 效能調優、高可用與監控（第2版）-by 賀春暢-讀書筆記
2017-08-30
MySql筆記
小花狸監控之MySQL
2015-03-26
MySql
MySQL監控工具之orzdba
2014-12-30
MySql
效能測試之Docker監控
2020-05-31
Docker
MYSQL和SQLServer效能監控指標
2018-05-14
MySqlServer指標

MySQL調優效能監控之performance schema

特點

1、切換資料庫

2、檢視當前資料庫下表儲存的資訊

3、通過show create table tablename來檢視建立表時的表結構

4、兩個基本概念

1、語句事件記錄表

2、等待事件記錄表

3、階段事件記錄表

4、事務事件記錄表

5、監控檔案系統層呼叫的表

6、監視記憶體使用的表

7、配置表

1、事件的採集器配置項開關

屬性說明

注意

2、history表:記錄每個執行緒應該執行完成的事件資訊

3、summary:所有事件的彙總資訊

4、instance表:記錄哪些型別的物件會被檢測

1、啟動選項

指定開啟單個instruments

使用萬用字元指定開啟多個instruments

開關所有的instruments

注意

2、系統變數

1、performance_timers表

欄位解釋

2、setup_timers

欄位解釋

3、setup_consumers表

欄位解釋

4、setup_instruments 表

欄位解釋

5、setup_actors表

欄位解釋

6、setup_objects表

欄位解釋

7、threads表

欄位解釋

注意

相關文章