實(shí)驗(yàn)過(guò)程中，我是用的MySQL版本是5.7，關(guān)于MySQL的快速安裝，可參考 如何在CentOS7下快速安裝MySQL5.7。
MySQL調(diào)優(yōu)，我們先從性能監(jiān)控開(kāi)始，找點(diǎn)感覺(jué)。
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)復(fù)制代碼
可以看到此工具默認(rèn)是OFF（禁用）的，可以通過(guò)服務(wù)器變量在當(dāng)前會(huì)話級(jí)別動(dòng)態(tài)的修改
mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)復(fù)制代碼
當(dāng)設(shè)置完成之后，在服務(wù)器上執(zhí)行的所有語(yǔ)句，都會(huì)測(cè)量其耗費(fèi)的時(shí)間和其他一些查詢執(zhí)行狀態(tài)變更相關(guān)的數(shù)據(jù)。
我們來(lái)創(chuàng)建一個(gè)表來(lái)演示一下：
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)復(fù)制代碼
查詢一下stu表：
mysql> select * from stu; +------+---------+| id | name | +------+---------+| 1 | Jack Ma | | 2 | Pony | +------+---------+2 rows in set (0.00 sec)復(fù)制代碼
這個(gè)2 rows in set (0.00 sec)就是用命令行執(zhí)行一個(gè)sql后給出的提示，而且還有執(zhí)行時(shí)間0.00秒。
在mysql的命令行模式下只能顯示兩位小數(shù)的時(shí)間，可以使用如下命令查看具體的執(zhí)行時(shí)間
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 | +----------------------+----------+復(fù)制代碼
這樣我們可以清晰地看到，Starting、System lock系統(tǒng)鎖、Sending data等等一系列操作分別用了多少時(shí)間。
通過(guò)上面的演示，我們可以看到，show profile一執(zhí)行完就給我們返回每項(xiàng)的執(zhí)行時(shí)間，并沒(méi)有顯示是針對(duì)哪條sql的，其實(shí)它就是列出了最近一條你執(zhí)行的sql語(yǔ)句的執(zhí)行時(shí)間。
那一定有可以看之前執(zhí)行過(guò)的多條sql語(yǔ)句的情況吧？是的，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)復(fù)制代碼
列出了執(zhí)行過(guò)的sql語(yǔ)句執(zhí)行時(shí)間，我們可以挑一個(gè)查詢來(lái)單獨(dú)看，比如我想看insert into stu(id, name) values(1, 'Pony')這條語(yǔ)句的情況，可以用下面的操作：
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)復(fù)制代碼
也就是說(shuō)可以根據(jù)Query_ID結(jié)合show profile來(lái)看。
MySQL官網(wǎng)上，關(guān)于show profile語(yǔ)句：
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
}復(fù)制代碼show profile、for query n前面已經(jīng)說(shuō)過(guò)了，下面我列一下每個(gè)type是什么意思。
all 顯示所有性能信息
block io 顯示塊io操作的次數(shù)
context switches 顯示上下文切換次數(shù)，被動(dòng)和主動(dòng)
cpu 顯示用戶cpu時(shí)間、系統(tǒng)cpu時(shí)間
IPC 顯示發(fā)送和接受的消息數(shù)量
Memory 暫未實(shí)現(xiàn)
page faults 顯示頁(yè)錯(cuò)誤數(shù)量
source 顯示源碼中的函數(shù)名稱與位置
swaps 顯示swap的次數(shù)
實(shí)操一下看看
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)復(fù)制代碼
我們針對(duì)Query_ID為3的進(jìn)行查看
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)復(fù)制代碼
查詢最后的\G的目的是格式化顯示一下查詢結(jié)果。
這里我們可以看到，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)復(fù)制代碼
最后兩列就表示塊io操作的次數(shù)。
下面只把各個(gè)type的語(yǔ)法列出來(lái)，演示效果不在此贅述了。
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用起來(lái)感覺(jué)很爽啊，他能幫我們看到sql語(yǔ)句各部分的執(zhí)行時(shí)間，這對(duì)我們定位線上sql執(zhí)行慢的問(wèn)題很有幫助啊，but，官網(wǎng)上有這么一段話：
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語(yǔ)句，并將在以后的MySQL版本中將其刪除。
改用Performance Schema；
好吧，你說(shuō)刪除就刪除。但是在MySQL刪除show profile之前，我們還是可以用的，畢竟很香嘛。
既然后續(xù)版本會(huì)用Performance Schema，那么我們還看下它是何方神圣吧！
MySQL的performance schema用于監(jiān)控MySQL server在一個(gè)較低級(jí)別的運(yùn)行過(guò)程中的資源消耗、資源等待等情況。使用performance schema可以更加容易的監(jiān)控MySQL。
在MySQL的5.7版本中，性能模式是默認(rèn)開(kāi)啟的，如果想要顯式的關(guān)閉的話需要修改配置文件，不能直接進(jìn)行修改，會(huì)報(bào)錯(cuò)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復(fù)制代碼
在配置文件中修改performance_schema的屬性值，on表示開(kāi)啟，off表示關(guān)閉
[mysqld] performance_schema=ON復(fù)制代碼
看下performance_schema數(shù)據(jù)庫(kù)有哪些功能表:
mysql> use performance_schema; mysql> show tables;復(fù)制代碼
一共有87張表，分別存儲(chǔ)著相關(guān)的信息。
可以通過(guò)show create table tablename來(lái)查看創(chuàng)建表的時(shí)候的表結(jié)構(gòu)：
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)復(fù)制代碼我一看到threads這個(gè)表名，還有THREAD_ID、PROCESSLIST_ID等等字段，就感覺(jué)這些比較重要啊有木有！
再來(lái)看一下setup_consumers這個(gè)表：
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)復(fù)制代碼這個(gè)consumer總感覺(jué)似曾相識(shí)?。?/p>
其實(shí)，想要搞明白后續(xù)的內(nèi)容，我們需要理解兩個(gè)基本概念：
performance_schema庫(kù)下的表可以按照監(jiān)視不同的緯度就行分組。
--語(yǔ)句事件記錄表，這些表記錄了語(yǔ)句事件信息，當(dāng)前語(yǔ)句事件表events_statements_current、歷史語(yǔ)句事件表events_statements_history和長(zhǎng)語(yǔ)句歷史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表還可以根據(jù)帳號(hào)(account)，主機(jī)(host)，程序(program)，線程(thread)，用戶(user)和全局(global)再進(jìn)行細(xì)分)show tables like '%statement%';--等待事件記錄表，與語(yǔ)句事件類型的相關(guān)記錄表類似：show tables like '%wait%';--階段事件記錄表，記錄語(yǔ)句執(zhí)行的階段事件的表show tables like '%stage%';--事務(wù)事件記錄表，記錄事務(wù)相關(guān)的事件的表show tables like '%transaction%';--監(jiān)控文件系統(tǒng)層調(diào)用的表show tables like '%file%';--監(jiān)視內(nèi)存使用的表show tables like '%memory%';--動(dòng)態(tài)對(duì)performance_schema進(jìn)行配置的配置表show tables like '%setup%';復(fù)制代碼
來(lái)，那一個(gè)事務(wù)相關(guān)的來(lái)瞅一眼：
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)復(fù)制代碼
數(shù)據(jù)庫(kù)剛剛初始化并啟動(dòng)時(shí)，并非所有instruments(事件采集項(xiàng)，在采集項(xiàng)的配置表中每一項(xiàng)都有一個(gè)開(kāi)關(guān)字段，或?yàn)閅ES，或?yàn)镹O)和consumers(與采集項(xiàng)類似，也有一個(gè)對(duì)應(yīng)的事件類型保存表配置項(xiàng)，為YES就表示對(duì)應(yīng)的表保存性能數(shù)據(jù)，為NO就表示對(duì)應(yīng)的表不保存性能數(shù)據(jù))都啟用了，所以默認(rèn)不會(huì)收集所有的事件。
可能你需要檢測(cè)的事件并沒(méi)有打開(kāi)，需要進(jìn)行設(shè)置，可以使用如下兩個(gè)語(yǔ)句打開(kāi)對(duì)應(yīng)的instruments和consumers（行計(jì)數(shù)可能會(huì)因MySQL版本而異)。
打開(kāi)等待事件的采集器配置項(xiàng)開(kāi)關(guān)，需要修改setup_instruments配置表中對(duì)應(yīng)的采集器配置項(xiàng)
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復(fù)制代碼
打開(kāi)等待事件的保存表配置開(kāi)關(guān)，修改setup_consumers配置表中對(duì)應(yīng)的配置項(xiàng)
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復(fù)制代碼
當(dāng)配置完成之后可以查看當(dāng)前server正在做什么。
events_waits_current表來(lái)得知，該表中每個(gè)線程只包含一行數(shù)據(jù)，用于顯示每個(gè)線程的最新監(jiān)視事件：
釋義：
該信息表示線程id為3的線程正在等待buf_dblwr_mutex鎖，等待時(shí)間為69917。 屬性說(shuō)明： id:事件來(lái)自哪個(gè)線程，事件編號(hào)是多少 event_name:表示檢測(cè)到的具體的內(nèi)容 source:表示這個(gè)檢測(cè)代碼在哪個(gè)源文件中以及行號(hào) timer_start:表示該事件的開(kāi)始時(shí)間 timer_end:表示該事件的結(jié)束時(shí)間 timer_wait:表示該事件總的花費(fèi)時(shí)間 注意：_current表中每個(gè)線程只保留一條記錄，一旦線程完成工作，該表中不會(huì)再記錄該線程的事件信息。復(fù)制代碼
_history表中記錄每個(gè)線程應(yīng)該執(zhí)行完成的事件信息，但每個(gè)線程的事件信息只會(huì)記錄10條，再多就會(huì)被覆蓋，（下面的演示可以看到每個(gè)線程只有10條記錄）。_history_long表中記錄所有線程的事件信息，但總記錄數(shù)量是10000，超過(guò)就會(huì)被覆蓋掉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)復(fù)制代碼
summary表提供所有事件的匯總信息，該組中的表以不同的方式匯總事件數(shù)據(jù)（如：按用戶，按主機(jī)，按線程等等）。例如：要查看哪些instruments占用最多的時(shí)間，可以通過(guò)對(duì)events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列進(jìn)行查詢（這兩列是對(duì)事件的記錄數(shù)執(zhí)行COUNT（*）、事件記錄的TIMER_WAIT列執(zhí)行SUM（TIMER_WAIT）統(tǒng)計(jì)而來(lái)）
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)復(fù)制代碼
instance表記錄了哪些類型的對(duì)象會(huì)被檢測(cè)。這些對(duì)象在被server使用時(shí)，在該表中將會(huì)產(chǎn)生一條事件記錄。例如，file_instances表列出了文件I/O操作及其關(guān)聯(lián)文件名
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)復(fù)制代碼
performance_schema_consumer_events_statements_current=TRUE#是否在mysql server啟動(dòng)時(shí)就開(kāi)啟events_statements_current表的記錄功能(該表記錄當(dāng)前的語(yǔ)句事件信息)，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新setup_consumers配置表中的events_statements_current配置項(xiàng)，默認(rèn)值為T(mén)RUEperformance_schema_consumer_events_statements_history=TRUE#與performance_schema_consumer_events_statements_current選項(xiàng)類似，但該選項(xiàng)是用于配置是否記錄語(yǔ)句事件短歷史信息，默認(rèn)為T(mén)RUEperformance_schema_consumer_events_stages_history_long=FALSE#與performance_schema_consumer_events_statements_current選項(xiàng)類似，但該選項(xiàng)是用于配置是否記錄語(yǔ)句事件長(zhǎng)歷史信息，默認(rèn)為FALSE除了statement(語(yǔ)句)事件之外，還支持：wait(等待)事件、state(階段)事件、transaction(事務(wù))事件，他們與statement事件一樣都有三個(gè)啟動(dòng)項(xiàng)分別進(jìn)行配置，但這些等待事件默認(rèn)未啟用，如果需要在MySQL Server啟動(dòng)時(shí)一同啟動(dòng)，則通常需要寫(xiě)進(jìn)my.cnf配置文件中 performance_schema_consumer_global_instrumentation=TRUE 是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局對(duì)象計(jì)數(shù)統(tǒng)計(jì)和事件匯總統(tǒng)計(jì)信息表 ）的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新全局配置項(xiàng) 默認(rèn)值為T(mén)RUEperformance_schema_consumer_statements_digest=TRUE是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟events_statements_summary_by_digest 表的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新digest配置項(xiàng) 默認(rèn)值為T(mén)RUEperformance_schema_consumer_thread_instrumentation=TRUE是否在MySQL Server啟動(dòng)時(shí)就開(kāi)啟 events_xxx_summary_by_yyy_by_event_name表的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語(yǔ)句進(jìn)行動(dòng)態(tài)更新線程配置項(xiàng) 默認(rèn)值為T(mén)RUEperformance_schema_instrument[=name] 是否在MySQL Server啟動(dòng)時(shí)就啟用某些采集器，由于instruments配置項(xiàng)多達(dá)數(shù)千個(gè)，所以該配置項(xiàng)支持key-value模式，還支持%號(hào)進(jìn)行通配等，如下:# [=name]可以指定為具體的Instruments名稱（但是這樣如果有多個(gè)需要指定的時(shí)候，就需要使用該選項(xiàng)多次），也可以使用通配符，可以指定instruments相同的前綴+通配符，也可以使用%代表所有的instruments## 指定開(kāi)啟單個(gè)instruments--performance-schema-instrument= 'instrument_name=value'## 使用通配符指定開(kāi)啟多個(gè)instruments--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'## 開(kāi)關(guān)所有的instruments--performance-schema-instrument= '%=ON'--performance-schema-instrument= '%=OFF'注意，這些啟動(dòng)選項(xiàng)要生效的前提是，需要設(shè)置performance_schema=ON。另外，這些啟動(dòng)選項(xiàng)雖然無(wú)法使用show variables語(yǔ)句查看，但我們可以通過(guò)setup_instruments和setup_consumers表查詢這些選項(xiàng)指定的值。復(fù)制代碼
show variables like '%performance_schema%'; --重要的屬性解釋 performance_schema=ON /* 控制performance_schema功能的開(kāi)關(guān)，要使用MySQL的performance_schema，需要在mysqld啟動(dòng)時(shí)啟用，以啟用事件收集功能 該參數(shù)在5.7.x之前支持performance_schema的版本中默認(rèn)關(guān)閉，5.7.x版本開(kāi)始默認(rèn)開(kāi)啟 注意：如果mysqld在初始化performance_schema時(shí)發(fā)現(xiàn)無(wú)法分配任何相關(guān)的內(nèi)部緩沖區(qū)，則performance_schema將自動(dòng)禁用，并將performance_schema設(shè)置為OFF */ performance_schema_digests_size=10000 /* 控制events_statements_summary_by_digest表中的最大行數(shù)。如果產(chǎn)生的語(yǔ)句摘要信息超過(guò)此最大值，便無(wú)法繼續(xù)存入該表，此時(shí)performance_schema會(huì)增加狀態(tài)變量 */ performance_schema_events_statements_history_long_size=10000 /* 控制events_statements_history_long表中的最大行數(shù)，該參數(shù)控制所有會(huì)話在events_statements_history_long表中能夠存放的總事件記錄數(shù)，超過(guò)這個(gè)限制之后，最早的記錄將被覆蓋 全局變量，只讀變量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默認(rèn)為10000，5.6.6及其之后的版本默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10000 * 5.7.x版本中，默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10000 */ performance_schema_events_statements_history_size=10 /* 控制events_statements_history表中單個(gè)線程（會(huì)話）的最大行數(shù)，該參數(shù)控制單個(gè)會(huì)話在events_statements_history表中能夠存放的事件記錄數(shù)，超過(guò)這個(gè)限制之后，單個(gè)會(huì)話最早的記錄將被覆蓋 全局變量，只讀變量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其之前的版本默認(rèn)為10，5.6.6及其之后的版本默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10 * 5.7.x版本中，默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10 除了statement(語(yǔ)句)事件之外，wait(等待)事件、state(階段)事件、transaction(事務(wù))事件，他們與statement事件一樣都有三個(gè)參數(shù)分別進(jìn)行存儲(chǔ)限制配置，有興趣的同學(xué)自行研究，這里不再贅述 */ performance_schema_max_digest_length=1024 /* 用于控制標(biāo)準(zhǔn)化形式的SQL語(yǔ)句文本在存入performance_schema時(shí)的限制長(zhǎng)度，該變量與max_digest_length變量相關(guān)(max_digest_length變量含義請(qǐng)自行查閱相關(guān)資料) 全局變量，只讀變量，默認(rèn)值1024字節(jié)，整型值，取值范圍0~1048576 */ performance_schema_max_sql_text_length=1024 /* 控制存入events_statements_current，events_statements_history和events_statements_history_long語(yǔ)句事件表中的SQL_TEXT列的最大SQL長(zhǎng)度字節(jié)數(shù)。 超出系統(tǒng)變量performance_schema_max_sql_text_length的部分將被丟棄，不會(huì)記錄，一般情況下不需要調(diào)整該參數(shù)，除非被截?cái)嗟牟糠峙c其他SQL比起來(lái)有很大差異 全局變量，只讀變量，整型值，默認(rèn)值為1024字節(jié)，取值范圍為0~1048576，5.7.6版本引入 降低系統(tǒng)變量performance_schema_max_sql_text_length值可以減少內(nèi)存使用，但如果匯總的SQL中，被截?cái)嗖糠钟休^大差異，會(huì)導(dǎo)致沒(méi)有辦法再對(duì)這些有較大差異的SQL進(jìn)行區(qū)分。 增加該系統(tǒng)變量值會(huì)增加內(nèi)存使用，但對(duì)于匯總SQL來(lái)講可以更精準(zhǔn)地區(qū)分不同的部分。 */復(fù)制代碼
基本了解了表的相關(guān)信息之后，可以通過(guò)這些表進(jìn)行實(shí)際的查詢操作來(lái)進(jìn)行實(shí)際的分析。
--1、哪類的SQL執(zhí)行最多？SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--2、哪類SQL的平均響應(yīng)時(shí)間最多？SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--3、哪類SQL排序記錄數(shù)最多？SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--4、哪類SQL掃描記錄數(shù)最多？SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--5、哪類SQL使用臨時(shí)表最多？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返回結(jié)果集最多？SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--7、哪個(gè)表物理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、哪個(gè)表邏輯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、哪個(gè)索引訪問(wèn)最多？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、哪個(gè)索引從來(lái)沒(méi)有用過(guò)？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、哪個(gè)等待事件消耗時(shí)間最多？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的執(zhí)行情況，包括statement信息，stege信息，wait信息SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';--12-2、查看每個(gè)階段的時(shí)間消耗SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;--12-3、查看每個(gè)階段的鎖等待情況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;復(fù)制代碼
關(guān)于Performance Schema的東西，我們還可以參考官網(wǎng)繼續(xù)進(jìn)行詳細(xì)的了解。
我們來(lái)思考一下，否可以做一個(gè)系統(tǒng)出來(lái)，通過(guò)查詢Performance Schema的表的一些信息，并將其展示到web端，這樣我們就可以進(jìn)行可視化監(jiān)控了？
使用show processlist查看連接的線程個(gè)數(shù)，來(lái)觀察是否有大量線程處于不正常的狀態(tài)或者其他不正常的特征。
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)復(fù)制代碼
屬性說(shuō)明：
id session id
user 操作的用戶
host 操作的主機(jī)
db 操作的數(shù)據(jù)庫(kù)
command 當(dāng)前狀態(tài)
info 詳細(xì)的sql語(yǔ)句
time 相應(yīng)命令執(zhí)行時(shí)間
state 命令執(zhí)行狀態(tài)
MySQL調(diào)優(yōu)之性能監(jiān)控：
show profile 使用show profile查詢剖析工具，可以指定具體的typeperformance schema 使用performance schema來(lái)更加容易的監(jiān)控mysqlshow processlist 使用show processlist查看連接的線程個(gè)數(shù)，來(lái)觀察是否有大量線程處于不正常的狀態(tài)或者其他不正常的特征
更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2024 51dongshi.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬(wàn)商天勤律師事務(wù)所王興未律師提供法律服務(wù)
