本文共 5801 字，大约阅读时间需要 19 分钟。
mysql可以用自带的profile功能查看执行语句需要哪些系统资源,比如cpu,内存,磁盘io之类的,功能默认是关闭状态需手动开启,profile对管理或者开发dba都有很大的帮助.
mysql dba技术群 378190849
武汉-linux运维群 236415619
1.查看profile功能的状态
[root@tong1 ~]# /usr/local/mysql-5.6.23/bin/mysql -u root -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5499 Server version: 5.6.23-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%pro%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | have_profiling | YES | | profiling | OFF | --默认是关闭状态 | profiling_history_size | 15 | | protocol_version | 10 | | proxy_user | | | slave_compressed_protocol | OFF | | stored_program_cache | 256 | +---------------------------+-------+ 7 rows in set (0.00 sec) mysql> set @@profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | ON | | profiling_history_size | 15 | +------------------------+-------+ 3 rows in set (0.00 sec) mysql> select * from t; +------+ | a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+-----------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------+ | 1 | 0.00051675 | show variables like '%profil%' | | 2 | 0.00052600 | show variables like '%profiling%' | | 3 | 0.00010600 | SELECT DATABASE() | | 4 | 0.00025225 | show databases | | 5 | 0.00012550 | show tables | | 6 | 0.00021525 | select * from t | +----------+------------+-----------------------------------+ 6 rows in set, 1 warning (0.00 sec) mysql> show profile for query 6; --查看Query_ID为6的查询语句所消耗的资源+----------------------+----------+
| Status | Duration | +----------------------+----------+ | starting | 0.000058 | | checking permissions | 0.000007 | | Opening tables | 0.000020 | | init | 0.000015 | | System lock | 0.000010 | | optimizing | 0.000004 | | statistics | 0.000012 | | preparing | 0.000011 | | executing | 0.000002 | | Sending data | 0.000037 | | end | 0.000004 | | query end | 0.000007 | | closing tables | 0.000008 | | freeing items | 0.000010 | | cleaning up | 0.000012 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec) mysql> show profile BLOCK IO,CPU,MEMORY for query 6; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000058 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.000037 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 15 rows in set, 1 warning (0.00 sec) mysql> create table t2(a int); Query OK, 0 rows affected (0.30 sec) mysql> show profiles; +----------+------------+-----------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------+ | 1 | 0.00051675 | show variables like '%profil%' | | 2 | 0.00052600 | show variables like '%profiling%' | | 3 | 0.00010600 | SELECT DATABASE() | | 4 | 0.00025225 | show databases | | 5 | 0.00012550 | show tables | | 6 | 0.00021525 | select * from t | | 7 | 0.00023775 | help 'show profile' | | 8 | 0.07420075 | insert into t values(2) | | 9 | 0.00017425 | create table t1(a int) | | 10 | 0.29320100 | create table t2(a int) | +----------+------------+-----------------------------------+ 10 rows in set, 1 warning (0.00 sec) mysql> show profile BLOCK IO,CPU,MEMORY for query 10; --查看Query_ID为10的查询语句所消耗的资源+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000059 | 0.000000 | 0.000000 | 0 | 0 | | creating table | 0.259481 | 0.002000 | 0.000000 | 0 | 280 | | After create | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.033376 | 0.000000 | 0.000000 | 0 | 8 | | closing tables | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000160 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 9 rows in set, 1 warning (0.00 sec) mysql>