mysql profiling_MySQL profiling使用

news/2024/7/5 23:27:48

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

Lz6HYRMON+i8twpc18liHNyXr0pZsHaUPaNZ4LqVpcs4ocAN3vsf5F4q41LKQrq61v0PWogkCHre3GgjkeRh2wAA0EPjHtx7ZeqKY+sV6UBxIUfegBJ89tObnoPbv0XBdbL2PGDJo1Ij975q0m40P2rNrqysjDURPM8Krn2rXITiwyKWfVVmBC9EGBaCCVz6G62RoPmZJwAAAC8sgQvXoPhaAThE8Yp5p8BdeDlcibbHDgIAAEgQuBeHZy2Norgzh8ACAABMCAL34oy6wwwAAADgLCBwAQAAAOBSIHABAAAA4FIgcAEAAADgUiBwAQAAAOBSIHABAAAA4FIgcAEAAADgUiBwAQAAAOBSIHABAAAA4FIgcAEAAADgUiBwAQAAAOBSIHABAAAA4FIgcAEAAADgSvwPEmzCn9HhIh8AAAAASUVORK5CYII=

点击(此处)折叠或打开

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27067062/viewspace-2147303/，如需转载，请注明出处，否则将追究法律责任。


http://www.niftyadmin.cn/n/2496179.html

相关文章

关于PDF展示解决方案

关于PDF展示解决方案

三种解决方案:1.使用UIWebView2.使用QLPreviewController不支持http scheme URL,必须先将pdf文件下载到本地,然后在进行加载.3.使用CGContextDrawPage转载于:https://www.cnblogs.com/Sunnyheart/p/5364835.html
阅读更多...
mysql必知必会目录_《MySQL必知必会》目录介绍

mysql必知必会目录_《MySQL必知必会》目录介绍

《MySQL必知必会》目录介绍目　　录第1章　了解SQL... 11.1　数据库基础..... 11.1.1　什么是数据库..... 21.1.2　表..... 21.1.3　列和数据类型..... 31.1.4　行..... 41.1.5　主键..... 41.2　什么是SQL... 51.3　动手实践..... 61.4　小结..... 7第2章　MySQL简介...... 82…
阅读更多...
swift基础知识

swift基础知识

let 声明常量var 声明变量 ?可以为空 ！必须为所声明类型 swift中文教程：http://c.biancheng.net/cpp/swift/jiaocheng/转载于:https://www.cnblogs.com/to-creat/p/5368033.html
阅读更多...
python io编程

python io编程

1.IO编程IO在计算机中指Input/Output，也就是输入和输出。由于程序和运行时数据是在内存中驻留，由CPU这个超快的计算核心来执行，涉及到数据交换的地方，通常是磁盘、网络等，就需要IO接口。比如你打开浏览器，访…
阅读更多...
liunx 每天自动备份mysql数据库_Linux下每天自动备份mysql数据库

liunx 每天自动备份mysql数据库_Linux下每天自动备份mysql数据库

/usr/bin为mysql安装目录建备份文件夹：mkdir mysql_data_bak建脚本文件：touch autobackupmysql.sh打开文件vi autobackupmysql.sh在脚本中加入如下内容：filenamedate %Y%m%d/usr/bin/mysqldump -opt mysql -u root -proot|gzip >/mysql_d…
阅读更多...
2017-05-26

2017-05-26

2019独角兽企业重金招聘Python工程师标准>>> 1.缓存Cache1.1.操作系统磁盘缓存(减少磁盘机械操作)1.2.数据库缓存(减少文件系统I/0)1.3.应用程序缓存(减少对数据库的查询)1.4.Web服务器缓存(减少应用服务器请求)1.5.客户端浏览器缓存(减少对网站的访问)2.缓存一般需…
阅读更多...
mysql和运维_数据库服务运维（Sql与Mysql）

mysql和运维_数据库服务运维（Sql与Mysql）

释放双眼，带上耳机，听听看~！1 安装ubuntu，我们可以使用 apt 安装 MySQL。该命令会安装 MySQL 服务器，客户端和一些其它的组件。命令如下：# 在安装 mysql-server 的时候 mysql-client 与 mysql-comman 会推荐…
阅读更多...
python 进程线程

python 进程线程

1.多进程要让Python程序实现多进程（multiprocessing），我们先了解操作系统的相关知识。Unix/Linux操作系统提供了一个fork()系统调用，它非常特殊。普通的函数调用，调用一次，返回一次，但是fork()调…
阅读更多...
最新文章

Copyright @ 2022~2023