미스터 역마살
Mysql 프로파일링 본문
728x90
반응형
Mysql 프로파일링
mysql은 기본적으로 비활성화(OFF)되어 있으므로 프로파일링을 진행하기 위해서는 활성화 작업을 해야함
show variables like 'profiling%';
* 결과
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
set profiling = 'ON';프로파일링 수행할 SQL 출력
SELECT 사원번호
FROM 사원
WHERE 사원번호 = 100000;
* 결과
+----------+
| 사원번호 |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
show profiles;
* 결과
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 1 | 0.00055779 | SELECT 사원번호 FROM 사원 WHERE 사원번호 = 100000 |
+----------+------------+---------------------------------------------------------+
1 rows in set (0.000 sec)특정 쿼리 ID에 대해서만 프로파일링 된 상세내용을 확인하고자 한다면 쿼리 ID를 입력하여 다음과 같은 문법으로 결과를 확인함
show profile for query 1;
* 결과
+------------------------+----------+
| Status | Duration |
+------------------------+----------+
| Starting | 0.000116 |
| checking permissions | 0.000009 |
| Opening tables | 0.000039 |
| After opening tables | 0.000008 |
| System lock | 0.000008 |
| table lock | 0.000022 |
| init | 0.000037 |
| Optimizing | 0.000029 |
| Statistics | 0.000096 |
| Preparing | 0.000006 |
| Unlocking tables | 0.000020 |
| Preparing | 0.000024 |
| Executing | 0.000006 |
| Sending data | 0.000020 |
| End of update loop | 0.000013 |
| Query end | 0.000005 |
| Commit | 0.000006 |
| closing tables | 0.000004 |
| Unlocking tables | 0.000003 |
| closing tables | 0.000008 |
| Starting cleanup | 0.000003 |
| Freeing items | 0.000007 |
| Updating status | 0.000064 |
| Reset for next command | 0.000006 |
+------------------------+----------+
24 rows in set (0.005 sec)
프로파일링 선택 가능한 출력정보
show profile all for query 1;
* 결과
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
| starting | 0.000093 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1122 |
| starting | 0.000010 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | launch_hook_trans_begin | rpl_handler.cc | 1124 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | check_access | sql_authorization.cc | 2207 |
| Opening tables | 0.000052 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | open_tables | sql_base.cc | 5605 |
| init | 0.000011 | 0.015625 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 684 |
| System lock | 0.000025 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_lock_tables | lock.cc | 329 |
| optimizing | 0.000018 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 282 |
| statistics | 0.000118 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 504 |
| preparing | 0.000015 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | JOIN::optimize | sql_optimizer.cc | 588 |
| executing | 0.000013 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SELECT_LEX_UNIT::ExecuteIterat | sql_union.cc | 1084 |
| end | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Sql_cmd_dml::execute | sql_select.cc | 737 |
| query end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4703 |
| waiting for handler commit | 0.000009 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ha_commit_trans | handler.cc | 1589 |
| closing tables | 0.000008 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_execute_command | sql_parse.cc | 4754 |
| freeing items | 0.000064 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | mysql_parse | sql_parse.cc | 5435 |
| cleaning up | 0.000018 | 0.000000 | 0.000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | dispatch_command | sql_parse.cc | 2217 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+--------------------------------+----------------------+-------------+
17 rows in set, 1 warning (0.00 sec)
====================================================================================
* SQL
show profile cpu for query 1;
* 결과
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000093 | 0.000000 | 0.000000 |
| Executing hook on transaction | 0.000003 | 0.000000 | 0.000000 |
| starting | 0.000010 | 0.000000 | 0.000000 |
| checking permissions | 0.000007 | 0.000000 | 0.000000 |
| Opening tables | 0.000052 | 0.000000 | 0.000000 |
| init | 0.000011 | 0.015625 | 0.000000 |
| System lock | 0.000025 | 0.000000 | 0.000000 |
| optimizing | 0.000018 | 0.000000 | 0.000000 |
| statistics | 0.000118 | 0.000000 | 0.000000 |
| preparing | 0.000015 | 0.000000 | 0.000000 |
| executing | 0.000013 | 0.000000 | 0.000000 |
| end | 0.000003 | 0.000000 | 0.000000 |
| query end | 0.000002 | 0.000000 | 0.000000 |
| waiting for handler commit | 0.000009 | 0.000000 | 0.000000 |
| closing tables | 0.000008 | 0.000000 | 0.000000 |
| freeing items | 0.000064 | 0.000000 | 0.000000 |
| cleaning up | 0.000018 | 0.000000 | 0.000000 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)
====================================================================================
* SQL
show profile block io for query 1;
* 결과
+--------------------------------+----------+--------------+---------------+
| Status | Duration | Block_ops_in | Block_ops_out |
+--------------------------------+----------+--------------+---------------+
| starting | 0.000093 | NULL | NULL |
| Executing hook on transaction | 0.000003 | NULL | NULL |
| starting | 0.000010 | NULL | NULL |
| checking permissions | 0.000007 | NULL | NULL |
| Opening tables | 0.000052 | NULL | NULL |
| init | 0.000011 | NULL | NULL |
| System lock | 0.000025 | NULL | NULL |
| optimizing | 0.000018 | NULL | NULL |
| statistics | 0.000118 | NULL | NULL |
| preparing | 0.000015 | NULL | NULL |
| executing | 0.000013 | NULL | NULL |
| end | 0.000003 | NULL | NULL |
| query end | 0.000002 | NULL | NULL |
| waiting for handler commit | 0.000009 | NULL | NULL |
| closing tables | 0.000008 | NULL | NULL |
| freeing items | 0.000064 | NULL | NULL |
| cleaning up | 0.000018 | NULL | NULL |
+--------------------------------+----------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
728x90
'Database > MYSQL' 카테고리의 다른 글
| binlog (0) | 2022.08.10 |
|---|---|
| Mysql 쿼리 힌트 (0) | 2022.08.09 |
| My sql 실행 계획 (2/2) (0) | 2022.08.08 |
| My SQL 실행 계획 (1/2) (0) | 2022.08.08 |
| Mysql Audit Log (0) | 2022.08.03 |
'Database/MYSQL' Related Articles
more
Comments