點擊下載
本文文檔

當前位置：首頁 - 科技 - 知識百科 - 正文

初識MySQL調優之性能監控

來源：懂視網責編：小采時間：2020-11-27 16:51:02

初識MySQL調優之性能監控

初識MySQL調優之性能監控:mysql教程欄目介紹認識性能監控。MySQL優化主要從其邏輯架構的核心層-分析、優化、執行階段著手。實驗過程中，我是用的MySQL版本是5.7，關于MySQL的快速安裝，可參考如何在CentOS7下快速安裝MySQL5.7。MySQL調優，我們先從性能監控開始，找點感覺。使用sh

推薦度：

點擊下載本文 文檔為doc格式

導讀初識MySQL調優之性能監控:mysql教程欄目介紹認識性能監控。MySQL優化主要從其邏輯架構的核心層-分析、優化、執行階段著手。實驗過程中，我是用的MySQL版本是5.7，關于MySQL的快速安裝，可參考如何在CentOS7下快速安裝MySQL5.7。MySQL調優，我們先從性能監控開始，找點感覺。使用sh

實驗過程中，我是用的MySQL版本是5.7，關于MySQL的快速安裝，可參考如何在CentOS7下快速安裝MySQL5.7。

MySQL調優，我們先從性能監控開始，找點感覺。

使用`show profile`

show profile是MySQL的查詢剖析工具。

mysql> show variables like '%profil%';
+------------------------+-------+| Variable_name | Value |
+------------------------+-------+| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+3 rows in set (0.00 sec)復制代碼

可以看到此工具默認是OFF（禁用）的，可以通過服務器變量在當前會話級別動態的修改

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)復制代碼

當設置完成之后，在服務器上執行的所有語句，都會測量其耗費的時間和其他一些查詢執行狀態變更相關的數據。

我們來創建一個表來演示一下：

mysql> create database mytest;
Query OK, 1 row affected (0.01 sec)

mysql> use mytest;
Database changed
mysql> create table stu(id int(8), name varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu(id, name) values(1, 'Jack Ma');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(id, name) values(1, 'Pony');
Query OK, 1 row affected (0.03 sec)復制代碼

查詢一下stu表：

mysql> select * from stu;
+------+---------+| id | name |
+------+---------+| 1 | Jack Ma |
| 2 | Pony |
+------+---------+2 rows in set (0.00 sec)復制代碼

這個2 rows in set (0.00 sec)就是用命令行執行一個sql后給出的提示，而且還有執行時間0.00秒。

在mysql的命令行模式下只能顯示兩位小數的時間，可以使用如下命令查看具體的執行時間

mysql> show profile;
+----------------------+----------+| Status | Duration |
+----------------------+----------+| starting | 0.000061 |
| checking permissions | 0.000009 |
| Opening tables | 0.000019 |
| init | 0.000020 |
| System lock | 0.000009 |
| optimizing | 0.000005 |
| statistics | 0.000014 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.000121 |
| end | 0.000012 |
| query end | 0.000011 |
| closing tables | 0.000011 |
| freeing items | 0.000021 |
| cleaning up | 0.000021 |
+----------------------+----------+復制代碼

這樣我們可以清晰地看到，Starting、System lock系統鎖、Sending data等等一系列操作分別用了多少時間。

通過上面的演示，我們可以看到，show profile一執行完就給我們返回每項的執行時間，并沒有顯示是針對哪條sql的，其實它就是列出了最近一條你執行的sql語句的執行時間。

那一定有可以看之前執行過的多條sql語句的情況吧？是的，show profiles

`show profiles`

mysql> show profiles;
+----------+------------+------------------------------------------------+| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+| 1 | 0.02907350 | create table stu(id int(8), name varchar(20)) |
| 2 | 0.00337800 | create database mytest |
| 3 | 0.02786850 | SELECT DATABASE() |
| 4 | 0.00065300 | show databases |
| 5 | 0.00086700 | show tables |
| 6 | 0.06554900 | create table stu(id int(8), name varchar(20)) |
| 7 | 0.00079850 | insert into stu(1, 'Jack') |
| 8 | 0.06901975 | show create table stu |
| 9 | 0.00008800 | insert into stu(1, 'Jack') |
| 10 | 0.00616000 | insert into stu(id, name) values(1, 'Jack Ma') |
| 11 | 0.03119675 | insert into stu(id, name) values(1, 'Pony') |
| 12 | 0.03124900 | update stu set id=2 where name='Pony' |
| 13 | 0.00036975 | select * from stu |
| 14 | 0.00034925 | select * from stu |
+----------+------------+------------------------------------------------+14 rows in set, 1 warning (0.00 sec)復制代碼

列出了執行過的sql語句執行時間，我們可以挑一個查詢來單獨看，比如我想看insert into stu(id, name) values(1, 'Pony')這條語句的情況，可以用下面的操作：

mysql> show profile for query 11;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000072 |
| checking permissions | 0.000009 |
| Opening tables | 0.000021 |
| init | 0.000018 |
| System lock | 0.000008 |
| update | 0.000094 |
| end | 0.000005 |
| query end | 0.030815 |
| closing tables | 0.000036 |
| freeing items | 0.000081 |
| cleaning up | 0.000039 |
+----------------------+----------+11 rows in set, 1 warning (0.00 sec)復制代碼

也就是說可以根據Query_ID結合show profile來看。

MySQL官網上，關于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
}復制代碼

show profile、for query n前面已經說過了，下面我列一下每個type是什么意思。

all 顯示所有性能信息

block io 顯示塊io操作的次數

context switches 顯示上下文切換次數，被動和主動

cpu 顯示用戶cpu時間、系統cpu時間

IPC 顯示發送和接受的消息數量

Memory 暫未實現

page faults 顯示頁錯誤數量

source 顯示源碼中的函數名稱與位置

swaps 顯示swap的次數

實操一下看看

mysql> show profiles;
+----------+------------+-------------------------------+| Query_ID | Duration | Query |
+----------+------------+-------------------------------+| 1 | 0.00009625 | show variable like '%profi%' |
| 2 | 0.00373950 | show variables like '%profi%' |
| 3 | 0.00025825 | select * from stu |
+----------+------------+-------------------------------+3 rows in set, 1 warning (0.00 sec)復制代碼

我們針對Query_ID為3的進行查看

all

mysql> show profile all for query 3\G
*************************** 1. row *************************** Status: starting
 Duration: 0.000070
 CPU_user: 0.000000
 CPU_system: 0.000064
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: NULL
 Source_file: NULL
 Source_line: NULL*************************** 2. row *************************** Status: checking permissions Duration: 0.000011
 CPU_user: 0.000000
 CPU_system: 0.000009
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: check_access
 Source_file: sql_authorization.cc
 Source_line: 809*************************** 3. row *************************** Status: Opening tables
 Duration: 0.000021
 CPU_user: 0.000000
 CPU_system: 0.000021
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: open_tables
 Source_file: sql_base.cc
 Source_line: 5793*************************** 4. row *************************** Status: init Duration: 0.000019
 CPU_user: 0.000000
 CPU_system: 0.000019
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: handle_query
 Source_file: sql_select.cc
 Source_line: 128*************************** 5. row *************************** Status: System lock
 Duration: 0.000009
 CPU_user: 0.000000
 CPU_system: 0.000009
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: mysql_lock_tables
 Source_file: lock.cc
 Source_line: 330*************************** 6. row *************************** Status: optimizing Duration: 0.000005
 CPU_user: 0.000000
 CPU_system: 0.000004
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: optimize
 Source_file: sql_optimizer.cc
 Source_line: 158*************************** 7. row *************************** Status: statistics
 Duration: 0.000014
 CPU_user: 0.000000
 CPU_system: 0.000014
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: optimize
 Source_file: sql_optimizer.cc
 Source_line: 374*************************** 8. row *************************** Status: preparing Duration: 0.000011
 CPU_user: 0.000000
 CPU_system: 0.000010
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: optimize
 Source_file: sql_optimizer.cc
 Source_line: 482*************************** 9. row *************************** Status: executing Duration: 0.000004
 CPU_user: 0.000000
 CPU_system: 0.000004
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: exec
 Source_file: sql_executor.cc
 Source_line: 126*************************** 10. row *************************** Status: Sending data
 Duration: 0.000050
 CPU_user: 0.000000
 CPU_system: 0.000050
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: exec
 Source_file: sql_executor.cc
 Source_line: 202*************************** 11. row *************************** Status: end
 Duration: 0.000005
 CPU_user: 0.000000
 CPU_system: 0.000005
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: handle_query
 Source_file: sql_select.cc
 Source_line: 206*************************** 12. row *************************** Status: query end
 Duration: 0.000008
 CPU_user: 0.000000
 CPU_system: 0.000008
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: mysql_execute_command
 Source_file: sql_parse.cc
 Source_line: 4956*************************** 13. row *************************** Status: closing tables
 Duration: 0.000008
 CPU_user: 0.000000
 CPU_system: 0.000008
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: mysql_execute_command
 Source_file: sql_parse.cc
 Source_line: 5009*************************** 14. row *************************** Status: freeing items Duration: 0.000013
 CPU_user: 0.000000
 CPU_system: 0.000013
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: mysql_parse
 Source_file: sql_parse.cc
 Source_line: 5622*************************** 15. row *************************** Status: cleaning up Duration: 0.000014
 CPU_user: 0.000000
 CPU_system: 0.000013
 Context_voluntary: 0Context_involuntary: 0
 Block_ops_in: 0
 Block_ops_out: 0
 Messages_sent: 0
 Messages_received: 0
 Page_faults_major: 0
 Page_faults_minor: 0
 Swaps: 0
 Source_function: dispatch_command
 Source_file: sql_parse.cc
 Source_line: 193115 rows in set, 1 warning (0.00 sec)復制代碼

查詢最后的\G的目的是格式化顯示一下查詢結果。

這里我們可以看到，show profile all可以看到所有的性能信息。

block io

mysql> show profile block io for query 3;
+----------------------+----------+--------------+---------------+| Status | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+| starting | 0.000070 | 0 | 0 |
| checking permissions | 0.000011 | 0 | 0 |
| Opening tables | 0.000021 | 0 | 0 |
| init | 0.000019 | 0 | 0 |
| System lock | 0.000009 | 0 | 0 |
| optimizing | 0.000005 | 0 | 0 |
| statistics | 0.000014 | 0 | 0 |
| preparing | 0.000011 | 0 | 0 |
| executing | 0.000004 | 0 | 0 |
| Sending data | 0.000050 | 0 | 0 |
| end | 0.000005 | 0 | 0 |
| query end | 0.000008 | 0 | 0 |
| closing tables | 0.000008 | 0 | 0 |
| freeing items | 0.000013 | 0 | 0 |
| cleaning up | 0.000014 | 0 | 0 |
+----------------------+----------+--------------+---------------+15 rows in set, 1 warning (0.00 sec)復制代碼

最后兩列就表示塊io操作的次數。

下面只把各個type的語法列出來，演示效果不在此贅述了。

show profile context switches for query 3

show profile cpu for query 3

show profile ipc for query 3

show profile page faults for query 3

show profile source for query 3

show profile swaps for query 3

show profile用起來感覺很爽啊，他能幫我們看到sql語句各部分的執行時間，這對我們定位線上sql執行慢的問題很有幫助啊，but，官網上有這么一段話：

Note

The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead;

注意

不推薦使用SHOW PROFILE和SHOW PROFILES語句，并將在以后的MySQL版本中將其刪除。

改用Performance Schema；

好吧，你說刪除就刪除。但是在MySQL刪除show profile之前，我們還是可以用的，畢竟很香嘛。

既然后續版本會用Performance Schema，那么我們還看下它是何方神圣吧！

Performance Schema

MySQL的performance schema用于監控MySQL server在一個較低級別的運行過程中的資源消耗、資源等待等情況。使用performance schema可以更加容易的監控MySQL。

在MySQL的5.7版本中，性能模式是默認開啟的，如果想要顯式的關閉的話需要修改配置文件，不能直接進行修改，會報錯Variable 'performance_schema' is a read only variable。

mysql> show variables like 'performance_schema';
+--------------------+-------+| Variable_name | Value |
+--------------------+-------+| performance_schema | ON |
+--------------------+-------+mysql> set performance_schema=0;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable復制代碼

在配置文件中修改performance_schema的屬性值，on表示開啟，off表示關閉

[mysqld]
performance_schema=ON復制代碼

看下performance_schema數據庫有哪些功能表:

mysql> use performance_schema;
mysql> show tables;復制代碼

一共有87張表，分別存儲著相關的信息。

可以通過show create table tablename來查看創建表的時候的表結構：

mysql> show create table threads\G
*************************** 1. row *************************** Table: threadsCreate Table: CREATE TABLE `threads` ( `THREAD_ID` bigint(20) unsigned NOT NULL, `NAME` varchar(128) NOT NULL, `TYPE` varchar(10) NOT NULL, `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL, `PROCESSLIST_USER` varchar(32) DEFAULT NULL, `PROCESSLIST_HOST` varchar(60) DEFAULT NULL, `PROCESSLIST_DB` varchar(64) DEFAULT NULL, `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL, `PROCESSLIST_TIME` bigint(20) DEFAULT NULL, `PROCESSLIST_STATE` varchar(64) DEFAULT NULL, `PROCESSLIST_INFO` longtext, `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL, `ROLE` varchar(64) DEFAULT NULL, `INSTRUMENTED` enum('YES','NO') NOT NULL, `HISTORY` enum('YES','NO') NOT NULL, `CONNECTION_TYPE` varchar(16) DEFAULT NULL, `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf81 row in set (0.00 sec)復制代碼

我一看到threads這個表名，還有THREAD_ID、PROCESSLIST_ID等等字段，就感覺這些比較重要啊有木有！

再來看一下setup_consumers這個表：

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)復制代碼

這個consumer總感覺似曾相識啊！

其實，想要搞明白后續的內容，我們需要理解兩個基本概念：

instruments 生產者，用于采集mysql中各種各樣的操作產生的事件信息，對應配置表中的配置項我們可以稱為監控采集配置項。

consumers 消費者，對應的消費者表用于存儲來自instruments采集的數據，對應配置表中的配置項我們可以稱為消費存儲配置項。

performance_schema表的分類

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

--語句事件記錄表，這些表記錄了語句事件信息，當前語句事件表events_statements_current、歷史語句事件表events_statements_history和長語句歷史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表還可以根據帳號(account)，主機(host)，程序(program)，線程(thread)，用戶(user)和全局(global)再進行細分)show tables like '%statement%';--等待事件記錄表，與語句事件類型的相關記錄表類似：show tables like '%wait%';--階段事件記錄表，記錄語句執行的階段事件的表show tables like '%stage%';--事務事件記錄表，記錄事務相關的事件的表show tables like '%transaction%';--監控文件系統層調用的表show tables like '%file%';--監視內存使用的表show tables like '%memory%';--動態對performance_schema進行配置的配置表show tables like '%setup%';復制代碼

來，那一個事務相關的來瞅一眼：

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)復制代碼

performance_schema的簡單配置與使用

數據庫剛剛初始化并啟動時，并非所有instruments(事件采集項，在采集項的配置表中每一項都有一個開關字段，或為YES，或為NO)和consumers(與采集項類似，也有一個對應的事件類型保存表配置項，為YES就表示對應的表保存性能數據，為NO就表示對應的表不保存性能數據)都啟用了，所以默認不會收集所有的事件。

可能你需要檢測的事件并沒有打開，需要進行設置，可以使用如下兩個語句打開對應的instruments和consumers（行計數可能會因MySQL版本而異)。

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

mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 267 rows affected (0.03 sec)
Rows matched: 321 Changed: 267 Warnings: 0復制代碼

打開等待事件的保存表配置開關，修改setup_consumers配置表中對應的配置項

mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0復制代碼

當配置完成之后可以查看當前server正在做什么。

可以通過查詢events_waits_current表來得知，該表中每個線程只包含一行數據，用于顯示每個線程的最新監視事件：

釋義：

該信息表示線程id為3的線程正在等待buf_dblwr_mutex鎖，等待時間為69917。
屬性說明：
	id:事件來自哪個線程，事件編號是多少
	event_name:表示檢測到的具體的內容
	source:表示這個檢測代碼在哪個源文件中以及行號
	timer_start:表示該事件的開始時間
	timer_end:表示該事件的結束時間
	timer_wait:表示該事件總的花費時間
注意：_current表中每個線程只保留一條記錄，一旦線程完成工作，該表中不會再記錄該線程的事件信息。復制代碼

_history表中記錄每個線程應該執行完成的事件信息，但每個線程的事件信息只會記錄10條，再多就會被覆蓋，（下面的演示可以看到每個線程只有10條記錄）。_history_long表中記錄所有線程的事件信息，但總記錄數量是10000，超過就會被覆蓋掉

mysql> 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 |
+-----------+----------+------------------------------------------+------------+| 3 | 4042 | wait/synch/mutex/innodb/buf_pool_mutex | 47785 |
| 3 | 4043 | wait/synch/mutex/innodb/flush_list_mutex | 43761 |
| 3 | 4044 | wait/synch/mutex/innodb/buf_pool_mutex | 58851 |
| 3 | 4045 | wait/synch/mutex/innodb/buf_dblwr_mutex | 73438 |
| 3 | 4046 | wait/synch/mutex/innodb/buf_pool_mutex | 222829 |
| 3 | 4047 | wait/synch/mutex/innodb/buf_pool_mutex | 42755 |
| 3 | 4048 | wait/synch/mutex/innodb/flush_list_mutex | 44767 |
| 3 | 4049 | wait/synch/mutex/innodb/buf_pool_mutex | 59857 |
| 3 | 4050 | wait/synch/mutex/innodb/buf_dblwr_mutex | 72432 |
| 3 | 4041 | wait/synch/mutex/innodb/buf_pool_mutex | 243452 |
| 17 | 2442 | wait/synch/mutex/innodb/sync_array_mutex | 74947 |
| 17 | 2440 | wait/synch/mutex/innodb/sync_array_mutex | 82492 |
| 17 | 2439 | wait/synch/mutex/innodb/sync_array_mutex | 458233 |
| 17 | 2438 | wait/synch/mutex/innodb/log_sys_mutex | 239428 |
| 17 | 2437 | wait/synch/mutex/innodb/sync_array_mutex | 85510 |
| 17 | 2446 | wait/synch/mutex/innodb/sync_array_mutex | 84504 |
| 17 | 2445 | wait/synch/mutex/innodb/sync_array_mutex | 77462 |
| 17 | 2444 | wait/synch/mutex/innodb/log_sys_mutex | 238422 |
| 17 | 2443 | wait/synch/mutex/innodb/sync_array_mutex | 83498 |
| 17 | 2441 | wait/synch/mutex/innodb/log_sys_mutex | 229368 |
| 19 | 7281 | wait/synch/mutex/innodb/flush_list_mutex | 43761 |
+-----------+----------+------------------------------------------+------------+21 rows in set (0.00 sec)復制代碼

summary表提供所有事件的匯總信息，該組中的表以不同的方式匯總事件數據（如：按用戶，按主機，按線程等等）。

例如：要查看哪些instruments占用最多的時間，可以通過對events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列進行查詢（這兩列是對事件的記錄數執行COUNT（*）、事件記錄的TIMER_WAIT列執行SUM（TIMER_WAIT）統計而來）

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/synch/mutex/innodb/buf_pool_mutex | 3357 |
| wait/synch/mutex/innodb/log_sys_mutex | 3357 |
| wait/synch/mutex/innodb/sync_array_mutex | 2241 |
| wait/synch/mutex/innodb/flush_list_mutex | 2238 |
| wait/io/file/sql/FRM | 1590 |
| wait/synch/mutex/innodb/log_flush_order_mutex | 1119 |
| wait/synch/mutex/innodb/log_sys_write_mutex | 1119 |
| wait/synch/mutex/innodb/fil_system_mutex | 1119 |
| wait/synch/mutex/innodb/dict_sys_mutex | 1119 |
| wait/synch/mutex/innodb/buf_dblwr_mutex | 1119 |
+-----------------------------------------------+------------+10 rows in set (0.10 sec)復制代碼

instance表記錄了哪些類型的對象會被檢測。這些對象在被server使用時，在該表中將會產生一條事件記錄。

例如，file_instances表列出了文件I/O操作及其關聯文件名

mysql> select * from file_instances limit 10;
+----------------------------------------+--------------------------------------+------------+| 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 | 3 |
| /var/lib/mysql/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 3 |
+----------------------------------------+--------------------------------------+------------+10 rows in set (0.00 sec)復制代碼

常用配置項的參數說明

啟動選項

performance_schema_consumer_events_statements_current=TRUE#是否在mysql server啟動時就開啟events_statements_current表的記錄功能(該表記錄當前的語句事件信息)，啟動之后也可以在setup_consumers表中使用UPDATE語句進行動態更新setup_consumers配置表中的events_statements_current配置項，默認值為TRUEperformance_schema_consumer_events_statements_history=TRUE#與performance_schema_consumer_events_statements_current選項類似，但該選項是用于配置是否記錄語句事件短歷史信息，默認為TRUEperformance_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語句進行動態更新全局配置項
默認值為TRUEperformance_schema_consumer_statements_digest=TRUE是否在MySQL Server啟動時就開啟events_statements_summary_by_digest 表的記錄功能，啟動之后也可以在setup_consumers表中使用UPDATE語句進行動態更新digest配置項
默認值為TRUEperformance_schema_consumer_thread_instrumentation=TRUE是否在MySQL Server啟動時就開啟

events_xxx_summary_by_yyy_by_event_name表的記錄功能，啟動之后也可以在setup_consumers表中使用UPDATE語句進行動態更新線程配置項
默認值為TRUEperformance_schema_instrument[=name]
是否在MySQL Server啟動時就啟用某些采集器，由于instruments配置項多達數千個，所以該配置項支持key-value模式，還支持%號進行通配等，如下:# [=name]可以指定為具體的Instruments名稱（但是這樣如果有多個需要指定的時候，就需要使用該選項多次），也可以使用通配符，可以指定instruments相同的前綴+通配符，也可以使用%代表所有的instruments## 指定開啟單個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表查詢這些選項指定的值。復制代碼

系統變量

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來講可以更精準地區分不同的部分。
*/復制代碼

比較有意義：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_id = 1553;復制代碼

關于Performance Schema的東西，我們還可以參考官網繼續進行詳細的了解。

我們來思考一下，否可以做一個系統出來，通過查詢Performance Schema的表的一些信息，并將其展示到web端，這樣我們就可以進行可視化監控了？

show processlist

使用show processlist查看連接的線程個數，來觀察是否有大量線程處于不正常的狀態或者其他不正常的特征。

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+----------+------------------+| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------------------+---------+------+----------+------------------+| 6 | root | localhost | performance_schema | Query | 0 | starting | show processlist |
+----+------+-----------+--------------------+---------+------+----------+------------------+1 row in set (0.00 sec)復制代碼

屬性說明：

id session id

user 操作的用戶

host 操作的主機

db 操作的數據庫

command 當前狀態

sleep：線程正在等待客戶端發送新的請求

query：線程正在執行查詢或正在將結果發送給客戶端

locked：在mysql的服務層，該線程正在等待表鎖

analyzing and statistics：線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃

Copying to tmp table：線程正在執行查詢，并且將其結果集都復制到一個臨時表中

sorting result：線程正在對結果集進行排序

sending data：線程可能在多個狀態之間傳送數據，或者在生成結果集或者向客戶端返回數據

info 詳細的sql語句

time 相應命令執行時間

state 命令執行狀態

小結

MySQL調優之性能監控：

show profile 使用show profile查詢剖析工具，可以指定具體的type
performance schema 使用performance schema來更加容易的監控mysql
show processlist 使用show processlist查看連接的線程個數，來觀察是否有大量線程處于不正常的狀態或者其他不正常的特征

更多相關免費學習推薦：mysql教程(視頻)

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

初識MySQL調優之性能監控

推薦度：

點擊下載本文 文檔為doc格式

標簽：監控監視 mysql

熱門焦點

初識MySQL調優之性能監控

初識MySQL調優之性能監控

使用`show profile`

`show profiles`

Performance Schema

performance_schema表的分類

performance_schema的簡單配置與使用

常用配置項的參數說明

比較有意義：performance_schema實踐操作

show processlist

小結

初識MySQL調優之性能監控

最新推薦

猜你喜歡

熱門推薦

初識MySQL調優之性能監控

初識MySQL調優之性能監控

使用show profile

show profiles

Performance Schema

performance_schema表的分類

performance_schema的簡單配置與使用

常用配置項的參數說明

比較有意義：performance_schema實踐操作

show processlist

小結

初識MySQL調優之性能監控

最新推薦

猜你喜歡

熱門推薦

使用`show profile`

`show profiles`