MySQL Query Profile MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.
详细可以参见官方文档:Using the New MySQL Query Profiler
启动
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
测试查询
mysql> select count(*) from client where broker_id=2;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row in set (0.00 sec)
查看profiles
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)
查看单条profile
mysql> show profile for query 1;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| (initialization) | 0.00006300 |
| Opening tables | 0.00001400 |
| System lock | 0.00000600 |
| Table lock | 0.00001000 |
| init | 0.00002200 |
| optimizing | 0.00001100 |
| statistics | 0.00009300 |
| preparing | 0.00001700 |
| executing | 0.00000700 |
| Sending data | 0.00016800 |
| end | 0.00000700 |
| query end | 0.00000500 |
| freeing items | 0.00001200 |
| closing tables | 0.00000800 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)
mysql> alter table t engine=myisam;
Query OK, 112050 rows affected (0.64 sec)
Records: 112050 Duplicates: 0 Warnings: 0
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00044700 | select count(*) from client where broker_id=2 |
| 2 | 0.00003400 | set profiling=0 |
| 3 | 0.00007400 | set profiling=1 |
| 4 | 0.63789700 | alter table t engine=myisam |
| 5 | 0.00004000 | set profiling=0 |
+----------+------------+-----------------------------------------------+
6 rows in set (0.00 sec)
mysql> show profile for query 4;
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| (initialization) | 0.00002900 |
| checking permissions | 0.00000800 |
| init | 0.00004000 |
| Opening table | 0.00009400 |
| System lock | 0.00000500 |
| Table lock | 0.00000700 |
| setup | 0.00004200 |
| creating table | 0.00195800 |
| After create | 0.00010900 |
| copy to tmp table | 0.52264500 |
| rename result table | 0.11289400 |
| end | 0.00004600 |
| query end | 0.00000700 |
| freeing items | 0.00001300 |
+----------------------+------------+
14 rows in set (0.00 sec)
查看cpu资源等信息
mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+------------+------------+------------+
| (initialization) | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock | 0.00000700 | 0.00000000 | 0.00000000 |
| setup | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table | 0.00195800 | 0.00000000 | 0.00100000 |
| After create | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table | 0.11289400 | 0.00199900 | 0.00000000 |
| end | 0.00004600 | 0.00000000 | 0.00000000 |
| query end | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+
14 rows in set (0.00 sec)
其他属性列表
* ALL - displays all information
* BLOCK IO - displays counts for block input and output operations
* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
* IPC - displays counts for messages sent and received
* MEMORY - is not currently implemented
* PAGE FAULTS - displays counts for major and minor page faults
* SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
* SWAPS - displays swap counts
设定profiling保存size
mysql> show variables where variable_name='profiling_history_size'; # 默认15条
关闭
mysql> set profiling=0;
相关推荐
MySQL 的 SQL 語法調整主要都是使用 EXPLAIN , 但是這個並沒辦法知道詳細的...MySQL Query Profile 詳細介紹可見: Using the New MySQL Query Profiler (2007.04.05 發表) 效能分析主要分下述三種(轉載自上篇): Bottl
要使用该功能,mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检查。 profiling 功能可以了解到cpu io 等更详细的信息。 show profile 的格式如下: SHOW PROFILE [type [, type] ... ] [FOR ...
使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 SQL 的性能问题所在。 最近遇到一个查询比较慢的sql语句,用了子查询,大概...
一 mysql awr报告(简单的AWR,mysqltuner.pl,tuning-primer.sh pt-query-digest,pt-variable-advisor) 二 性能分析工具介绍(mytop,innotop,orzdba,tcpdump,pt-query-digest,tbdba-slow-picker.pl,iotop,io-...
文章目录1、SQL分析、调优和排查的大体流程2、show profile是什么3、show profile分析步骤(1)是否支持,看看当前的MySQL版本是否支持(2)开启功能,默认是关闭,使用前需要开启(3)运行SQL(4)查看结果,show ...
pt-config-diff pt-heartbeat pt-query-digest pt-table-checksum pt-deadlock-logger pt-index-usage pt-show-grants pt-table-sync pt-diskstats pt-ioprofile pt-sift pt-table-usage pt-duplicate-key-checker ...
1. show variables like ‘%profiling%’;(查看profiling信息) ...4. show profile for query 4; show profile 的格式如下: SHOW PROFILE [type [, type] … ] [FOR QUERY n] [LIMIT row_
Diagnose bad SQL query performance and discover ways to optimize it Grasp modern SQL techniques to optimize complex SQL queries Identify and simplify overly complex JavaScript code Explore and ...
数据库管理系统检查命令清单全文共2页,当前为第1页。数据库管理系统检查命令清单全文共2页,当前为第1页。数据库管理系统测评检查命令 数据库管理系统检查命令清单全文共2页,当前为第1...') AS query FROM mysql.user
pt-ioprofile 点杀 pt-mext pt-mongodb-query-digest pt-mongodb-summary pt-mysql-summary pt在线模式更改 pt-pmp pt-query-digest pt-show-grants pt-sift pt-slave-delay pt从属查找 pt-slave-restart...
│ │ │ ├─mysql │ │ │ ├─mysqli │ │ │ ├─oci8 │ │ │ ├─odbc │ │ │ ├─postgre │ │ │ └─sqlite │ │ ├─fonts │ │ ├─helpers │ │ ├─language │ │ │ └─english │ │ └─...
8.9 Using dtuple for Flexible Access to Query Results 8.10 Pretty-Printing the Contents of Database Cursors 8.11 Establishing Database Connections Lazily 8.12 Accessing a JDBC Database from a ...