如何理解MySQL Profile在MySQL5.7的簡單測試

187次閱讀

共計 7659 個字符，預(yù)計需要花費 20 分鐘才能閱讀完成。

本篇文章給大家分享的是有關(guān)如何理解 MySQL Profile 在 MySQL5.7 的簡單測試，丸趣 TV 小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

MySQL Profile 對于分析執(zhí)行計劃的開銷來說，還是有一定的幫助，至少在分析一些性能問題的時候有很多的參考依據(jù)。
我在 5.6, 5.7 版本中進行了測試，沒發(fā)現(xiàn)差別，還是以 5.7 為例進行演示吧。
mysql select version();
+———–+
| version() |
+———–+
| 5.7.10 |
+———–+
1 row in set (0.00 sec)

傳統(tǒng)的使用 Profile 都是使用 show profile 這樣的命令方式，這個功能默認是關(guān)閉的。
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)
原來這種方式已經(jīng)過期了，新的功能是在 performance_schema 中開放。當(dāng)然在 5.6，5.7 版本中測試還是可用，我們先簡單了解一下，再來看 performance_schema 怎么用。
Profile 相關(guān)的幾個參數(shù)如下：
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, 當(dāng)前默認值為 0, 代表的是一個意思。
mysql SELECT @@profiling;
+————-+
| @@profiling |
+————-+
| 0 |
+————-+
1 row in set, 1 warning (0.00 sec)
have_profiling 用于控制是否開啟或者禁用 Profiling
profiling_history_size 是保留 Profiling 的數(shù)目

當(dāng)然本質(zhì)上，Profile 的內(nèi)容還是來自于 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)
除此之外，還有哪些選項呢，可以自由選用。

上面的內(nèi)容其實介于使用和過期之間，那么我們來看看新版本中推薦的 performace_schema 是怎么回事。
先切換到 performance_schema 下，這是 MySQL 新增的性能優(yōu)化引擎，在 5.6 以前是關(guān)閉的，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 的內(nèi)容如下：
mysql SELECT * FROM setup_actors;
+——+——+——+———+———+
| HOST | USER | ROLE | ENABLED | HISTORY |
+——+——+——+———+———+
| % | % | % | YES | YES |
+——+——+——+———+———+
1 row in set (0.00 sec)
按照官方的建議，默認是啟用，可以根據(jù)需求禁用。
UPDATE performance_schema.setup_actors SET ENABLED = NO , HISTORY = NO
WHERE HOST = % AND USER = %
禁用后的內(nèi)容如下：
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
加入成功后的數(shù)據(jù)內(nèi)容如下：
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 的配置就這樣，另外兩個表的內(nèi)容修改也是大同小異。
表 setup_consumers 描述各種事件，setup_instruments 描述這個數(shù)據(jù)庫下的表名以及是否開啟監(jiān)控
我統(tǒng)計了一下，兩個表的默認數(shù)據(jù)還不少。
setup_instruments 1006 rows
setup_consumers 15 rows
我們按照官方的建議來修改，可以看到修改的不是一行，而是相關(guān)的很多行。
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
好了配置完成，我們來簡單測試一下怎么用。
創(chuàng)建一個 test 數(shù)據(jù)庫。
mysql create database test;
Query OK, 1 row affected (0.00 sec)
切換到 test 數(shù)據(jù)庫
mysql use test
Database changed
創(chuàng)建一個測試表 test_profile，插入幾行數(shù)據(jù)。
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
運行一下，我們根據(jù)這個語句來得到一些詳細的統(tǒng)計信息。
mysql select * from test.test_profile limit 1,2;
根據(jù)下面的語句查詢一個歷史表，從表名可以看出是和事件相關(guān)的，感覺越來越像 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)
我們通過上面的語句可以得到一個概覽，對應(yīng)的事件和執(zhí)行時間。
然后到 stage 相關(guān)的歷史表中查看事件的詳細信息，這就是我們期望的性能數(shù)據(jù)了。如此一來應(yīng)該就明白上面的配置表中所要做的工作是什么意思了。
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)