MS SQLやOracleにもProfile機能を利用して、パフォーマンスや問題確認を行う
機能がありますが、OSSのMYSQLにも同じようにProfile機能が存在してます。
本日は、Profile機能の簡単な確認。

===============================================================

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> SET profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>

===============================================================

profiling config


mysql> select * from Y2008 limit 1;
+----+----------+
| id | comment |
+----+----------+
| 1 | comment1 |
+----+----------+
1 row in set (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 1 | 0.02000000 | select * from Y2008 |
| 2 | 0.02000100 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 3 | 0.01000000 | SHOW VARIABLES like 'profile%' |
| 4 | 0.02000100 | SHOW VARIABLES like 'profi%' |
| 5 | 0.01000000 | SHOW VARIABLES LIKE 'profile%' |
| 6 | 0.00000000 | SHOW VARIABLES LIKE 'profile%' |
| 7 | 0.01000000 | SHOW VARIABLES LIKE 'profil%' |
| 8 | 0.00000000 | SHOW VARIABLES LIKE 'profil%' |
| 9 | 0.00000000 | select * from Y2008 limit 1 |
+----------+------------+--------------------------------------+
9 rows in set (0.01 sec)

mysql>

プロファイル履歴

mysql> SHOW PROFILE;
+——————–+———-+
| Status | Duration |
+——————–+———-+
| starting | 0.000000 |
| Opening tables | 0.000000 |
| System lock | 0.000000 |
| Table lock | 0.000000 |
| init | 0.000000 |
| optimizing | 0.000000 |
| statistics | 0.000000 |
| preparing | 0.000000 |
| executing | 0.000000 |
| Sending data | 0.000000 |
| end | 0.000000 |
| query end | 0.000000 |
| freeing items | 0.000000 |
| logging slow query | 0.000000 |
| cleaning up | 0.000000 |
+——————–+———-+
15 rows in set (0.00 sec)

mysql>

profile_2

profile_3


StatusとDurationを表示


mysql> SHOW PROFILE SOURCE;
+--------------------+----------+-----------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------+----------+-----------------------+--------------+-------------+
| starting | 0.000000 | NULL | NULL |NULL |
| query end | 0.000000 | mysql_execute_command | sql_parse.cc |4799 |
| freeing items | 0.000000 | mysql_parse | sql_parse.cc |5805 |
| logging slow query | 0.000000 | log_slow_statement | sql_parse.cc |1608 |
| cleaning up | 0.000000 | dispatch_command | sql_parse.cc |1575 |
+--------------------+----------+-----------------------+--------------+-------------+
5 rows in set (0.01 sec)

mysql> SHOW PROFILE BLOCK IO;
+--------------------+----------+--------------+---------------+
| Status | Duration | Block_ops_in | Block_ops_out |
+--------------------+----------+--------------+---------------+
| starting | 0.010000 | 0 | 0 |
| Opening tables | 0.000000 | 0 | 0 |
| query end | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+--------------+---------------+
6 rows in set (0.00 sec)
mysql>

mysql> SHOW PROFILE CONTEXT SWITCHES;
+--------------------+----------+-------------------+---------------------+
| Status | Duration | Context_voluntary | Context_involuntary |
+--------------------+----------+-------------------+---------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 1 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+-------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE CPU;
+--------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| starting | 0.000000 | 0.000000 | 0.000000 |
| freeing items | 0.000000 | 0.000000 | 0.000000 |
| logging slow query | 0.000000 | 0.000000 | 0.000000 |
| cleaning up | 0.000000 | 0.000000 | 0.000000 |
+--------------------+----------+----------+------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE IPC;
+--------------------+----------+---------------+-------------------+
| Status | Duration | Messages_sent | Messages_received |
+--------------------+----------+---------------+-------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+---------------+-------------------+
4 rows in set (0.01 sec)

mysql>

mysql> SHOW PROFILE MEMORY;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000000 |
| freeing items | 0.000000 |
| logging slow query | 0.000000 |
| cleaning up | 0.000000 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE PAGE FAULTS;
+--------------------+----------+-------------------+-------------------+
| Status | Duration | Page_faults_major | Page_faults_minor |
+--------------------+----------+-------------------+-------------------+
| starting | 0.000000 | 0 | 0 |
| freeing items | 0.000000 | 0 | 0 |
| logging slow query | 0.000000 | 0 | 0 |
| cleaning up | 0.000000 | 0 | 0 |
+--------------------+----------+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE SWAPS;
+--------------------+----------+-------+
| Status | Duration | Swaps |
+--------------------+----------+-------+
| starting | 0.000000 | 0 |
| freeing items | 0.000000 | 0 |
| logging slow query | 0.000000 | 0 |
| cleaning up | 0.000000 | 0 |
+--------------------+----------+-------+
4 rows in set (0.00 sec)

mysql>

======================================================
過去に取得した値を確認
======================================================


mysql> SHOW PROFILES;
+----------+------------+--------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------+
| 6 | 0.00000000 | SHOW VARIABLES LIKE 'profile%' |
| 7 | 0.01000000 | SHOW VARIABLES LIKE 'profil%' |
| 8 | 0.00000000 | SHOW VARIABLES LIKE 'profil%' |
| 9 | 0.00000000 | select * from Y2008 limit 1 |
| 10 | 0.00000000 | select * from Y2008 limit 1 |
| 11 | 0.00000000 | show processlist |
| 12 | 0.83001300 | SHOW LOCAL STATUS |
| 13 | 0.86001300 | SHOW LOCAL STATUS |
| 14 | 0.01000000 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 15 | 0.00000000 | SET tmp_table_size=64*1024*1024 |
| 16 | 0.00000000 | SHOW VARIABLES LIKE 'tmp_table_size' |
| 17 | 0.80001200 | SHOW LOCAL STATUS |
| 18 | 0.89001400 | SHOW LOCAL STATUS |
| 19 | 0.01000000 | SET tmp_table_size=16*1024*1024 |
| 20 | 0.00000000 | SHOW PROFILE CONTEXT SWITCHS |
+----------+------------+--------------------------------------+
15 rows in set (0.00 sec)

mysql>

mysql> SHOW PROFILE SOURCE FOR QUERY 20;
+--------------------+----------+--------------------+--------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+--------------------+----------+--------------------+--------------+-------------+
| starting | 0.000000 | NULL | NULL | NULL |
| freeing items | 0.000000 | mysql_parse | sql_parse.cc | 5805 |
| logging slow query | 0.000000 | log_slow_statement | sql_parse.cc | 1608 |
| cleaning up | 0.000000 | dispatch_command | sql_parse.cc | 1575 |
+--------------------+----------+--------------------+--------------+-------------+
4 rows in set (0.00 sec)

mysql>

profile_4


mysql> SET profiling=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql>


12.5.5.33. SHOW PROFILES Syntax

Comments are closed.

Post Navigation