性能优化重要的mysql profile功能

关键字：性能优化重要的mysql profile功能


mysql的sql语句优化都使用explain，但是这个没有办法知道详细的Memory/CPU等使用量
MySQL Query Profiler, 可以查询到此 SQL 语句会执行多少, 并看出 CPU/Memory 使用


量, 执行过程 System lock, Table lock 花多少时间等等.
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+

开启此功能
mysql>set profiling=1;
mysql> show variables like 'profiling%';
# 此命令会让mysql在 information_schema 的 database 建立一個 PROFILING 的

table 来记录.
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
profiling_history_size记录多少次查询

mysql> show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00018100 | show variables like 'profiling%'   |
|        2 | 0.00020400 | show variables like 'profiling%'   |
|        3 | 0.00007800 | set profiling=1                    |
|        4 | 0.00011000 | show variables like 'profiling%'   |
|        5 | 0.00002400 | select count(1) from `mrhao_stats` |
|        6 | 1.52181400 | select count(*) from `mrhao_stats` |
|        7 | 0.00026900 | show variables like 'profiling%'   |

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000003 |
| checking query cache for query | 0.000042 |
| Opening tables                 | 0.00001 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000025 |
| init                           | 0.000009 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000007 |
| preparing                      | 0.000007 |
| executing                      | 0.000004 |
| Sending data                   | 1.521676 |
| end                            | 0.000007 |
| query end                      | 0.000003 |
| storing result in query cache | 0.000002 |
| freeing items                  | 0.000006 |
| closing tables                 | 0.000004 |
| logging slow query             | 0.000002 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| (initialization)               | 0.000003 | 0        | 0          |
| checking query cache for query | 0.000042 | 0.001    | 0          |
| Opening tables                 | 0.00001 | 0        | 0          |
| System lock                    | 0.000004 | 0        | 0          |
| Table lock                     | 0.000025 | 0        | 0          |
| init                           | 0.000009 | 0        | 0          |
| optimizing                     | 0.000003 | 0        | 0          |
| statistics                     | 0.000007 | 0        | 0          |
| preparing                      | 0.000007 | 0        | 0          |
| executing                      | 0.000004 | 0        | 0          |
| Sending data                   | 1.521676 | 1.631752 | 0.036995   |
| end                            | 0.000007 | 0        | 0          |
| query end                      | 0.000003 | 0        | 0          |
| storing result in query cache | 0.000002 | 0        | 0          |
| freeing items                  | 0.000006 | 0        | 0          |
| closing tables                 | 0.000004 | 0        | 0          |
| logging slow query             | 0.000002 | 0        | 0          |
+--------------------------------+----------+----------+------------+
17 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

查询时间跟cpu的使用
mysql> select min(seq) seq,state,count(*) numb_ops,
    round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
    round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
    from information_schema.profiling
    where query_id = 7
    group by state
    order by seq;
关闭此功能

mysql> set profiling=0;
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+