利用Profiling的 MySQL 的用法
|
这篇文章将为大家详细讲解有关MySQL如何使用profiling，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 Mysql SQL优化工具我们常使用explain去解析sql的执行，根据执行计划去评估sql的性能消耗瓶颈，而MYSQL Profiling提供我们详细的SQL执行过程中的cpu/io/swap/memory等使用情况以及每个过程执行时间消耗。 主要用途为1：查看SQL执行消耗瓶颈位置2、查看sql的执行过程，每步操作在具体哪个源码文件的什么位置 这里简单介绍下其使用方式： profiling在mysql 5.0.37版本以后支持，在mysql5.7后可以通过performance_schema替代（25.18.1 Query Profiling Using Performance Schema），但通过session级别的追踪比较方便 1、相关变量 (root:localhost:Wed Nov 15 16:32:50 2017)[performance_schema]> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | ##是否支持profile功能 | profiling | ON | ##是否开启profile ，0|off表示关闭，1|on表示开启 | profiling_history_size | 15 | ##展示的历史sql数，默认是最近的15条，最大值是100 +------------------------+-------+ 2、查看语法 可以通过help show profiles查看帮助文档 show profiles可以查看历史执行最近的15条sql 点击(此处)折叠或打开 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 3、使用实例 1）开启profling (root:localhost:Wed Nov 15 16:37:00 2017)[dbtest]> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) (root:localhost:Wed Nov 15 16:37:14 2017)[dbtest]> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec) 2）查看所有profiling记录的sql (root:localhost:Wed Nov 15 16:37:16 2017)[dbtest]> show profiles; +----------+------------+--------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------+ | 1 | 0.00089900 | show variables like '%profil%' | +----------+------------+--------------------------------+ 3）查看指定profiling记录的sql (root:localhost:Wed Nov 15 16:39:14 2017)[dbtest]> show profile for query 2 ; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000103 | | checking permissions | 0.000008 | | Opening tables | 0.000060 | | init | 0.000023 | | System lock | 0.000011 | | optimizing | 0.000007 | | statistics | 0.000016 | | preparing | 0.000015 | | executing | 0.000007 | | Sending data | 0.000063 | | end | 0.000004 | | query end | 0.000010 | | closing tables | 0.000012 | | freeing items | 0.000016 | | logging slow query | 0.000003 | | logging slow query | 0.000070 | | cleaning up | 0.000014 | +----------------------+----------+ 4）查看指定profiling记录的sql，并且显示cpu/block io/的步骤消耗信息 (root:localhost:Wed Nov 15 16:43:47 2017)[dbtest]> show profile cpu ,block io for query 2; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000103 | NULL | NULL | NULL | NULL | | checking permissions | 0.000008 | NULL | NULL | NULL | NULL | | Opening tables | 0.000060 | NULL | NULL | NULL | NULL | | init | 0.000023 | NULL | NULL | NULL | NULL | | System lock | 0.000011 | NULL | NULL | NULL | NULL | | optimizing | 0.000007 | NULL | NULL | NULL | NULL | | statistics | 0.000016 | NULL | NULL | NULL | NULL | | preparing | 0.000015 | NULL | NULL | NULL | NULL | | executing | 0.000007 | NULL | NULL | NULL | NULL | | Sending data | 0.000063 | NULL | NULL | NULL | NULL | | end | 0.000004 | NULL | NULL | NULL | NULL | | query end | 0.000010 | NULL | NULL | NULL | NULL | | closing tables | 0.000012 | NULL | NULL | NULL | NULL | | freeing items | 0.000016 | NULL | NULL | NULL | NULL | | logging slow query | 0.000003 | NULL | NULL | NULL | NULL | | logging slow query | 0.000070 | NULL | NULL | NULL | NULL | | cleaning up | 0.000014 | NULL | NULL | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+ 5）查看指定profiling记录的sql，并且显示每步源码文件信息 (root:localhost:Wed Nov 15 16:44:09 2017)[dbtest]> show profile source for query 2; +----------------------+----------+-----------------------+------------------+-------------+ | Status | Duration | Source_function | Source_file | Source_line | +----------------------+----------+-----------------------+------------------+-------------+ | starting | 0.000103 | NULL | NULL | NULL | | checking permissions | 0.000008 | check_access | sql_parse.cc | 5635 | | Opening tables | 0.000060 | open_tables | sql_base.cc | 5029 | | init | 0.000023 | mysql_prepare_select | sql_select.cc | 1051 | | System lock | 0.000011 | mysql_lock_tables | lock.cc | 304 | | optimizing | 0.000007 | optimize | sql_optimizer.cc | 138 | | statistics | 0.000016 | optimize | sql_optimizer.cc | 381 | | preparing | 0.000015 | optimize | sql_optimizer.cc | 504 | | executing | 0.000007 | exec | sql_executor.cc | 110 | | Sending data | 0.000063 | exec | sql_executor.cc | 187 | | end | 0.000004 | mysql_execute_select | sql_select.cc | 1106 | | query end | 0.000010 | mysql_execute_command | sql_parse.cc | 5307 | | closing tables | 0.000012 | mysql_execute_command | sql_parse.cc | 5383 | | freeing items | 0.000016 | mysql_parse | sql_parse.cc | 6676 | | logging slow query | 0.000003 | log_slow_do | sql_parse.cc | 2077 | | logging slow query | 0.000070 | log_slow_do | sql_parse.cc | 2078 | | cleaning up | 0.000014 | dispatch_command | sql_parse.cc | 1878 | +----------------------+----------+-----------------------+------------------+-------------+ 17 rows in set, 1 warning (0.00 sec) 6）查看指定profiling记录的sql，并且显示所有的步骤消耗信息 (root:localhost:Wed Nov 15 16:40:34 2017)[dbtest]> show profile all for query 2 \G *************************** 1. row *************************** Status: starting Duration: 0.000103 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: NULL Source_file: NULL Source_line: NULL *************************** 2. row *************************** Status: checking permissions Duration: 0.000008 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: check_access Source_file: sql_parse.cc Source_line: 5635 *************************** 3. row *************************** Status: Opening tables Duration: 0.000060 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: open_tables Source_file: sql_base.cc Source_line: 5029 *************************** 4. row *************************** Status: init Duration: 0.000023 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_prepare_select Source_file: sql_select.cc Source_line: 1051 *************************** 5. row *************************** Status: System lock Duration: 0.000011 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_lock_tables Source_file: lock.cc Source_line: 304 *************************** 6. row *************************** Status: optimizing Duration: 0.000007 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: optimize Source_file: sql_optimizer.cc Source_line: 138 *************************** 7. row *************************** Status: statistics Duration: 0.000016 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: optimize Source_file: sql_optimizer.cc Source_line: 381 *************************** 8. row *************************** Status: preparing Duration: 0.000015 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: optimize Source_file: sql_optimizer.cc Source_line: 504 *************************** 9. row *************************** Status: executing Duration: 0.000007 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: exec Source_file: sql_executor.cc Source_line: 110 *************************** 10. row *************************** Status: Sending data Duration: 0.000063 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: exec Source_file: sql_executor.cc Source_line: 187 *************************** 11. row *************************** Status: end Duration: 0.000004 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_execute_select Source_file: sql_select.cc Source_line: 1106 *************************** 12. row *************************** Status: query end Duration: 0.000010 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 5307 *************************** 13. row *************************** Status: closing tables Duration: 0.000012 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_execute_command Source_file: sql_parse.cc Source_line: 5383 *************************** 14. row *************************** Status: freeing items Duration: 0.000016 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: mysql_parse Source_file: sql_parse.cc Source_line: 6676 *************************** 15. row *************************** Status: logging slow query Duration: 0.000003 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: log_slow_do Source_file: sql_parse.cc Source_line: 2077 *************************** 16. row *************************** Status: logging slow query Duration: 0.000070 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: log_slow_do Source_file: sql_parse.cc Source_line: 2078 *************************** 17. row *************************** Status: cleaning up Duration: 0.000014 CPU_user: NULL CPU_system: NULL Context_voluntary: NULL Context_involuntary: NULL Block_ops_in: NULL Block_ops_out: NULL Messages_sent: NULL Messages_received: NULL Page_faults_major: NULL Page_faults_minor: NULL Swaps: NULL Source_function: dispatch_command Source_file: sql_parse.cc Source_line: 1878 17 rows in set, 1 warning (0.00 sec) 关于“MySQL如何使用profiling”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
