MYSQL 如何监控SQL语句执行时消耗IO情况

[弘恩]

MYSQL 如何监控SQL语句执行时消耗IO情况

比方说,现在要执行
SELECT A.*
FROM TEST01 A ,TEST02 B
WHERE A.ID = B.ID
AND B.CREATETIME > '2013-05-01' ;

我怎么能获取该语句的IO消耗情况;

SQLSERVER 中可以用下面这种方法来监测 . 望大家能指点一二MYSQL的性能监控方面的知识.
SET STATISTICS IO ON ; -- IO 监测开启
SET STATISTICS TIME ON ; -- 开销时间开启
SELECT A.*
FROM TEST01 A ,TEST02 B
WHERE A.ID = B.ID
AND B.CREATETIME > '2013-05-01' ;

[ACMAIN_CHM]

12.5.5.33. SHOW PROFILES Syntax
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

The SHOW PROFILES and SHOW PROFILE statements display profiling information that indicates resource usage for statements executed during the course of the current session. 

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON: 

mysql> SET profiling = 1;

SHOW PROFILES displays a list of the most recent statements sent to the master. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling. 

All statements are profiled except SHOW PROFILES and SHOW PROFILE, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list. 

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES. 

The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows. 

By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, althought there might be some minor differences in interpretion for the two statements for some status values (see Section 7.5.6, “Examining Thread Information”). 

Optional type values may be specified to display specific additional types of information: 

ALL displays all information 

BLOCK IO displays counts for block input and output operations 

CONTEXT SWITCHES displays counts for voluntary and involuntary context switches 

CPU displays user and system CPU usage times 

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 is enabled per session. When a session ends, its profiling information is lost. 

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+

[wwwwb]

sql执行时间： SET profiling=1; select sql_no_cache count(*) from customer where locale ='ad2dadaasdadas1dasdpasd1'; SHOW PROFILES; 假设上述SQL语句Query_ID为1 SHOW PROFILE FOR QUERY 1;

[弘恩]

楼上，能用下面我这句话做一个简单的示例么？？ SELECT A.* FROM TEST01 A ,TEST02 B WHERE A.ID = B.ID AND B.CREATETIME > '2013-05-01' ;

引用 2 楼 wwwwb 的回复:

sql执行时间： SET profiling=1; select sql_no_cache count(*) from customer where locale ='ad2dadaasdadas1dasdpasd1'; SHOW PROFILES; 假设上述SQL语句Query_ID为1 SHOW PROFILE FOR QUERY 1;

[wwwwb]

自行修改一下嘛 SET profiling=1; SELECT A.* FROM TEST01 A ,TEST02 B WHERE A.ID = B.ID AND B.CREATETIME > '2013-05-01' ; SHOW PROFILES; 从上述结果里取得　Query_ID 假设上述SQL语句Query_ID为1 SHOW PROFILE FOR QUERY 1;