在对数据库进行性能调优的时候,除了参数、配置的调整以外,SQL调优也是重要的手段,同时也是收益最大的一环。
当DBA对业务库进行sql调优的时候,如何做到有的放矢,投入产出受益最大?足够详细的SQL性能统计无疑是最重要的信息。
下面我们先来看下不同数据库提供的sql性能统计信息:
Oracle可以通过直接查询v$表得到,下面的columns列表是我们常用的一些统计:
- select sql_id,
- sql_text,
- sql_fulltext,
- sharable_mem,
- persistent_mem,
- runtime_mem,
- sorts,
- fetches,
- executions,
- parse_calls,
- disk_reads,
- direct_writes,
- buffer_gets,
- application_wait_time,
- concurrency_wait_time,
- cluster_wait_time,
- user_io_wait_time,
- rows_processed,
- cpu_time,
- elapsed_time
- from v$sql_area
这里边包括了几类统计,SQL内存使用的统计、parse的统计、物理/逻辑IO的统计、cpu时间、等待时间等时间统计。
DBA可以根据这些统计信息进行有针对性的调优:
1. 通过show profiles来查询统计信息
在MySQL 5.6版本之前,还保留着show profiles的方式,后续版本逐步被performance_schema来替换了。
使用方法如下:
- mysql> SET profiling=1;
- mysql> select 1, sleep(1);
- mysql> show profile cpu, block io for query 1;
- +----------------------+----------+----------+------------+--------------+---------------+
- | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
- +----------------------+----------+----------+------------+--------------+---------------+
- | starting | 0.000100 | NULL | NULL | NULL | NULL |
- | checking permissions | 0.000014 | NULL | NULL | NULL | NULL |
- | Opening tables | 0.000024 | NULL | NULL | NULL | NULL |
- | init | 0.000020 | NULL | NULL | NULL | NULL |
- | optimizing | 0.000008 | NULL | NULL | NULL | NULL |
- | executing | 0.000022 | NULL | NULL | NULL | NULL |
- | User sleep | 1.000090 | NULL | NULL | NULL | NULL |
- | end | 0.000021 | NULL | NULL | NULL | NULL |
- | query end | 0.000009 | NULL | NULL | NULL | NULL |
- | closing tables | 0.000010 | NULL | NULL | NULL | NULL |
- | freeing items | 0.000055 | NULL | NULL | NULL | NULL |
- | logging slow query | 0.000008 | NULL | NULL | NULL | NULL |
- | cleaning up | 0.000012 | NULL | NULL | NULL | NULL |
- +----------------------+----------+----------+------------+--------------+---------------+
- 13 rows in set (0.00 sec)
show profile的语法如下:
- 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
从结果集可以看到每一块操作的CPU时间,block IO情况。
但这种适合拿单个SQL进行分析,使用上的便捷性比较差。
2. 通过performance_schema的digest统计
MySQL之前的版本不支持绑定变量,导致SQL语句太多,相同业务的SQL汇总统计比较麻烦。
从MySQL 5.6开始,在performance_schema中支持了对SQL statement的digest进行统计。
performance_schema.events_statements_summary_by_digest表根据digest进行汇总统计,DBA可以直接访问这个内存表得到SQL的统计信息。
首先,需要打开performance_schema,然后系统就会自动为SQL statement生成digest,并记录统计信息。
例如:
- mysql> select 1, sleep(1);
- +---+----------+
- | 1 | sleep(1) |
- +---+----------+
- | 1 | 0 |
- +---+----------+
- 1 row in set (1.00 sec)
- mysql> select * from events_statements_summary_by_digest\G;
- *************************** 1. row ***************************
- SCHEMA_NAME: performance_schema
- DIGEST: bb80cc862a205b471ce0f0ff2605a9a0
- DIGEST_TEXT: SELECT ? , `sleep` (?)
- COUNT_STAR: 1
- SUM_TIMER_WAIT: 1000577972000
- MIN_TIMER_WAIT: 1000577972000
- AVG_TIMER_WAIT: 1000577972000
- MAX_TIMER_WAIT: 1000577972000
- SUM_LOCK_TIME: 0
- SUM_ERRORS: 0
- SUM_WARNINGS: 0
- SUM_ROWS_AFFECTED: 0
- SUM_ROWS_SENT: 1
- SUM_ROWS_EXAMINED: 0
- SUM_CREATED_TMP_DISK_TABLES: 0
- SUM_CREATED_TMP_TABLES: 0
- SUM_SELECT_FULL_JOIN: 0
- SUM_SELECT_FULL_RANGE_JOIN: 0
- SUM_SELECT_RANGE: 0
- SUM_SELECT_RANGE_CHECK: 0
- SUM_SELECT_SCAN: 0
- SUM_SORT_MERGE_PASSES: 0
- SUM_SORT_RANGE: 0
- SUM_SORT_ROWS: 0
- SUM_SORT_SCAN: 0
- SUM_NO_INDEX_USED: 0
- SUM_NO_GOOD_INDEX_USED: 0
- FIRST_SEEN: 2015-11-06 11:15:52
- LAST_SEEN: 2015-11-06 11:15:52
- 2 rows in set (0.00 sec)
DBA可用通过这个表的统计信息,来有的放矢的进行SQL调优。
然而performance_schema中digest的限制:
针对以上的一些限制,MySQL5.7最新GA的版本,进行了一些改进。
另外,针对限制2 Percona有一些工具pt-query-digest, 并建立一些digest历史表,进行分析,有兴趣的可以使用尝试一下。可以参考: mysql-query-digest-with-performance-schema
用户可以针对performance_schema提供的digest的功能,根据需求进行一些开发和扩展,比如定期历史保存、建立SQL性能基线、或者更进一步如果能修改源码,可以为digest增加更多的merics等。
来源: http://mysql.taobao.org/monthly/2015/11/02/