實驗過程中，我是用的MySQL版本是5.7，關于MySQL的快速安裝，可參考 如何在CentOS7下快速安裝MySQL5.7。
MySQL調優，我們先從性能監控開始，找點感覺。
show profileshow 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 profilesmysql> 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的進行查看
allmysql> 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 iomysql> 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 3show profile cpu for query 3show profile ipc for query 3show profile page faults for query 3show profile source for query 3show profile swaps for query 3show 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，那么我們還看下它是何方神圣吧！
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總感覺似曾相識啊！
其實，想要搞明白后續的內容，我們需要理解兩個基本概念：
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)復制代碼
數據庫剛剛初始化并啟動時，并非所有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來講可以更精準地區分不同的部分。 */復制代碼
基本了解了表的相關信息之后，可以通過這些表進行實際的查詢操作來進行實際的分析。
--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查看連接的線程個數，來觀察是否有大量線程處于不正常的狀態或者其他不正常的特征。
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 當前狀態
info 詳細的sql語句
time 相應命令執行時間
state 命令執行狀態
MySQL調優之性能監控：
show profile 使用show profile查詢剖析工具，可以指定具體的typeperformance schema 使用performance schema來更加容易的監控mysqlshow processlist 使用show processlist查看連接的線程個數，來觀察是否有大量線程處于不正常的狀態或者其他不正常的特征
更多相關免費學習推薦：mysql教程(視頻)
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 m.alabama37th.com 版權所有
違法及侵權請聯系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務所王興未律師提供法律服務
