MySQL Profile在5.7的簡單測試
MySQL Profile對於分析執行計劃的開銷來說，還是有一定的幫助，至少在分析一些效能問題的時候有很多的參考依據。
我在5.6, 5.7版本中進行了測試，沒發現差別，還是以5.7為例進行演示吧。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.10 |
+-----------+
1 row in set (0.00 sec)
傳統的使用Profile都是使用show profile這樣的命令方式，這個功能預設是關閉的。
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
這個地方可以看到有一個警告，我們看看是什麼警告。
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
原來這種方式已經過期了，新的功能是在performance_schema中開放。當然在5.6， 5.7版本中測試還是可用，我們先簡單瞭解一下，再來看performance_schema怎麼用。
Profile相關的幾個引數如下：
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
可以看到Profileing為OFF,當前預設值為0,代表的是一個意思。
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
have_profiling 用於控制是否開啟或者禁用Profiling
profiling_history_size是保留Profiling的數目
當然本質上，Profile的內容還是來自於information_schema.profiling
mysql> select * from information_schema.profiling\G
Empty set, 1 warning (0.00 sec)
這個地方還是有一個警告，還是過期的提示。
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'INFORMATION_SCHEMA.PROFILING' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
我們開啟profiling
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
檢視所有的profiles
mysql> show profiles;
+----------+------------+---------------+
| Query_ID | Duration | Query |
+----------+------------+---------------+
| 1 | 0.00018200 | show warnings |
+----------+------------+---------------+
1 row in set, 1 warning (0.00 sec)
我們順便執行一條SQL
mysql> select count(*)from information_schema.columns;
+----------+
| count(*) |
+----------+
| 3077 |
+----------+
1 row in set (0.07 sec)
然後再次檢視，就會看到query_ID會得到剛剛執行的語句。
mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+
| 1 | 0.00018200 | show warnings |
| 2 | 0.06627200 | select count(*)from information_schema.columns |
+----------+------------+------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
可以使用如下的方式來檢視profile的資訊，比如涉及CPU的明細資訊。
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
| checking permissions | 0.000053 | 0.000999 | 0.000000 |
| checking permissions | 0.000014 | 0.000000 | 0.000000 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
。。。。。
| closing tables | 0.000005 | 0.000000 | 0.000000 |
| freeing items | 0.000052 | 0.000000 | 0.000000 |
| cleaning up | 0.000023 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
100 rows in set, 1 warning (0.00 sec)
除此之外，還有哪些選項呢，可以自由選用。
上面的內容其實介於使用和過期之間，那麼我們來看看新版本中推薦的performace_schema是怎麼回事。
先切換到performance_schema下，這是MySQL新增的效能最佳化引擎，在5.6以前是關閉的，5。6,5.7中是預設開啟的，5.7切換的時候還會有一句提示。
mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
使用profile涉及幾個表，setup_actors,setup_instruments,setup_consumers
說白了都是配置，都是套路。
預設表setup_actors的內容如下：
mysql> SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
1 row in set (0.00 sec)
按照官方的建議，預設是啟用，可以根據需求禁用。
UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO'
WHERE HOST = '%' AND USER = '%';
禁用後的內容如下：
mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | NO | NO |
+------+------+------+---------+---------+
1 row in set (0.00 sec)
然後加入指定的使用者
INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','root','%','YES','YES');
加入成功後的資料內容如下：
mysql> select * from setup_actors;
+-----------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+-----------+------+------+---------+---------+
| % | % | % | NO | NO |
| localhost | root | % | YES | YES |
+-----------+------+------+---------+---------+
2 rows in set (0.00 sec)
好了，setup_actors的配置就這樣，另外兩個表的內容修改也是大同小異。
表 setup_consumers 描述各種事件，setup_instruments 描述這個資料庫下的表名以及是否開啟監控
我統計了一下，兩個表的預設資料還不少。
setup_instruments 1006 rows
setup_consumers 15 rows
我們按照官方的建議來修改，可以看到修改的不是一行，而是相關的很多行。
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
-> WHERE NAME LIKE '%statement/%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 192 Changed: 0 Warnings: 0
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
-> WHERE NAME LIKE '%stage/%';
Query OK, 119 rows affected (0.00 sec)
Rows matched: 128 Changed: 119 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
-> WHERE NAME LIKE '%events_statements_%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 3 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
-> WHERE NAME LIKE '%events_stages_%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
好了配置完成，我們來簡單測試一下怎麼用。
建立一個test資料庫。
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
切換到test資料庫
mysql> use test
Database changed
建立一個測試表test_profile，插入幾行資料。
mysql> create table test_profile as select * from information_schema.columns limit 1,5;
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
執行一下，我們根據這個語句來得到一些詳細的統計資訊。
mysql> select * from test.test_profile limit 1,2;
根據下面的語句查詢一個歷史表，從表名可以看出是和事件相關的，感覺越來越像Oracle了。
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
-> FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%limit 1,2%';
+----------+----------+-------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+-------------------------------------------+
| 4187 | 0.000424 | select * from test.test_profile limit 1,2 |
+----------+----------+-------------------------------------------+
1 row in set (0.00 sec)
我們透過上面的語句可以得到一個概覽，對應的事件和執行時間。
然後到stage相關的歷史表中檢視事件的詳細資訊，這就是我們期望的效能資料了。如此一來應該就明白上面的配置表中所要做的工作是什麼意思了。
mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
-> FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=4187;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000113 |
| stage/sql/checking permissions | 0.000008 |
| stage/sql/Opening tables | 0.000025 |
| stage/sql/init | 0.000062 |
| stage/sql/System lock | 0.000013 |
。。。
| stage/sql/freeing items | 0.000031 |
| stage/sql/cleaning up | 0.000002 |
+--------------------------------+----------+
15 rows in set (0.01 sec)
整體來看，看到這個特性的輸出，讓我忍不住想起了Oracle中的Datapump，因為輸出實在是太像了，很有條理嘛。
我在5.6, 5.7版本中進行了測試，沒發現差別，還是以5.7為例進行演示吧。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.10 |
+-----------+
1 row in set (0.00 sec)
傳統的使用Profile都是使用show profile這樣的命令方式，這個功能預設是關閉的。
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
這個地方可以看到有一個警告，我們看看是什麼警告。
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
原來這種方式已經過期了，新的功能是在performance_schema中開放。當然在5.6， 5.7版本中測試還是可用，我們先簡單瞭解一下，再來看performance_schema怎麼用。
Profile相關的幾個引數如下：
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
可以看到Profileing為OFF,當前預設值為0,代表的是一個意思。
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
have_profiling 用於控制是否開啟或者禁用Profiling
profiling_history_size是保留Profiling的數目
當然本質上，Profile的內容還是來自於information_schema.profiling
mysql> select * from information_schema.profiling\G
Empty set, 1 warning (0.00 sec)
這個地方還是有一個警告，還是過期的提示。
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'INFORMATION_SCHEMA.PROFILING' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
我們開啟profiling
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
檢視所有的profiles
mysql> show profiles;
+----------+------------+---------------+
| Query_ID | Duration | Query |
+----------+------------+---------------+
| 1 | 0.00018200 | show warnings |
+----------+------------+---------------+
1 row in set, 1 warning (0.00 sec)
我們順便執行一條SQL
mysql> select count(*)from information_schema.columns;
+----------+
| count(*) |
+----------+
| 3077 |
+----------+
1 row in set (0.07 sec)
然後再次檢視，就會看到query_ID會得到剛剛執行的語句。
mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+
| 1 | 0.00018200 | show warnings |
| 2 | 0.06627200 | select count(*)from information_schema.columns |
+----------+------------+------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
可以使用如下的方式來檢視profile的資訊，比如涉及CPU的明細資訊。
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
| checking permissions | 0.000053 | 0.000999 | 0.000000 |
| checking permissions | 0.000014 | 0.000000 | 0.000000 |
| checking permissions | 0.000006 | 0.000000 | 0.000000 |
。。。。。
| closing tables | 0.000005 | 0.000000 | 0.000000 |
| freeing items | 0.000052 | 0.000000 | 0.000000 |
| cleaning up | 0.000023 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
100 rows in set, 1 warning (0.00 sec)
除此之外，還有哪些選項呢，可以自由選用。
上面的內容其實介於使用和過期之間，那麼我們來看看新版本中推薦的performace_schema是怎麼回事。
先切換到performance_schema下，這是MySQL新增的效能最佳化引擎，在5.6以前是關閉的，5。6,5.7中是預設開啟的，5.7切換的時候還會有一句提示。
mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
使用profile涉及幾個表，setup_actors,setup_instruments,setup_consumers
說白了都是配置，都是套路。
預設表setup_actors的內容如下：
mysql> SELECT * FROM setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
1 row in set (0.00 sec)
按照官方的建議，預設是啟用，可以根據需求禁用。
UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO'
WHERE HOST = '%' AND USER = '%';
禁用後的內容如下：
mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | NO | NO |
+------+------+------+---------+---------+
1 row in set (0.00 sec)
然後加入指定的使用者
INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY)
VALUES('localhost','root','%','YES','YES');
加入成功後的資料內容如下：
mysql> select * from setup_actors;
+-----------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+-----------+------+------+---------+---------+
| % | % | % | NO | NO |
| localhost | root | % | YES | YES |
+-----------+------+------+---------+---------+
2 rows in set (0.00 sec)
好了，setup_actors的配置就這樣，另外兩個表的內容修改也是大同小異。
表 setup_consumers 描述各種事件，setup_instruments 描述這個資料庫下的表名以及是否開啟監控
我統計了一下，兩個表的預設資料還不少。
setup_instruments 1006 rows
setup_consumers 15 rows
我們按照官方的建議來修改，可以看到修改的不是一行，而是相關的很多行。
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
-> WHERE NAME LIKE '%statement/%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 192 Changed: 0 Warnings: 0
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
-> WHERE NAME LIKE '%stage/%';
Query OK, 119 rows affected (0.00 sec)
Rows matched: 128 Changed: 119 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
-> WHERE NAME LIKE '%events_statements_%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 3 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
-> WHERE NAME LIKE '%events_stages_%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
好了配置完成，我們來簡單測試一下怎麼用。
建立一個test資料庫。
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
切換到test資料庫
mysql> use test
Database changed
建立一個測試表test_profile，插入幾行資料。
mysql> create table test_profile as select * from information_schema.columns limit 1,5;
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
執行一下，我們根據這個語句來得到一些詳細的統計資訊。
mysql> select * from test.test_profile limit 1,2;
根據下面的語句查詢一個歷史表，從表名可以看出是和事件相關的，感覺越來越像Oracle了。
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
-> FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%limit 1,2%';
+----------+----------+-------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+-------------------------------------------+
| 4187 | 0.000424 | select * from test.test_profile limit 1,2 |
+----------+----------+-------------------------------------------+
1 row in set (0.00 sec)
我們透過上面的語句可以得到一個概覽，對應的事件和執行時間。
然後到stage相關的歷史表中檢視事件的詳細資訊，這就是我們期望的效能資料了。如此一來應該就明白上面的配置表中所要做的工作是什麼意思了。
mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
-> FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=4187;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000113 |
| stage/sql/checking permissions | 0.000008 |
| stage/sql/Opening tables | 0.000025 |
| stage/sql/init | 0.000062 |
| stage/sql/System lock | 0.000013 |
。。。
| stage/sql/freeing items | 0.000031 |
| stage/sql/cleaning up | 0.000002 |
+--------------------------------+----------+
15 rows in set (0.01 sec)
整體來看，看到這個特性的輸出，讓我忍不住想起了Oracle中的Datapump，因為輸出實在是太像了，很有條理嘛。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2126200/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql簡單效能測試MySql
- MySQL 5.5複製升級到5.7的一點簡單嘗試MySql
- MySQL索引條件下推的簡單測試MySql索引
- MySQL中的事務和鎖簡單測試MySql
- SQL PROFILE 測試SQL
- 玩轉 Github Profile Readme：單元測試Github
- MySQL InnoDB Cluster環境搭建和簡單測試MySql
- mysql的JDBC架包下載及簡單測試連線MySqlJDBC
- 簡單的 ping 測試
- try的簡單效能測試
- MySQL效能基準測試對比：5.7 VS 8.0MySql
- MySQL 5.7的主要特性簡介MySql
- 讓 API 測試變的簡單API
- Linux的OOMkiller簡單測試LinuxOOM
- Oracle RAC的TAF簡單測試Oracle
- sql trace 簡單測試SQL
- MySQL 5.7 InnoDB引擎簡介MySql
- 簡單的神經網路測試神經網路
- ios最簡單的測試打包方式iOS
- 閃迴歸檔的簡單測試
- 關於oracle session的簡單測試OracleSession
- pl/sql中的forall簡單測試SQL
- 簡單的hector連線casssandra測試
- 簡單的鍵值儲存測試
- 最簡單的jquery測試程式碼jQuery
- MySQL5.7 Galera Cluster安裝搭建及高可用測試MySql
- Oracle logmnr簡單測試Oracle
- mysqlimport匯入簡單測試MySqlImport
- （一）Jmeter簡單介面測試JMeter
- Windows IO 效能簡單測試Windows
- Windows 10 TechPreview簡單測試WindowsView
- Jmeter效能測試簡單使用JMeter
- 在MySQL中使用序列的簡單教程MySql
- MySQL刪除資料的簡單嘗試MySql
- 簡單的11步在Laravel中實現測試驅動開發Laravel
- 簡單的 11 步在 Laravel 中實現測試驅動開發Laravel
- 一個簡單的介面測試框架 demo框架
- mongoDB的主從複製簡單測試MongoDB