Performance Schema ---Sys56工具

Performance Schema是MySQL重要的性能监控工具，可以帮助我们找到MySQL的性能瓶颈以及一些其他的SLOW SQL等问题，能有效的帮助我们进行系统调优。下面从以下几个点对Performance schema以及sys56工具进行说明

• 获取数据库执行最多的SQL语句
• 单条执行时间最长的SQL
• 最频繁的表
• 从未被使用过的索引
• 文件IO消耗

---

Performance Schema功能简介

MySQL通过一个单独的performance schema存储引擎来实现MySQL运行过程中的性能点监控，文档中将这些性监控点命名为“instrucment”。通过
show variables like “performance_schema
可以查看是否开启了performance schema存储引擎。
performance schema虽然提供的性能数控丰富，但是不便于人类读。因此伟大的percona为我们提供了sys56工具，sys56包含了一堆的view、function以及存储过程方便我们使用

SYS56安装

下载 git clone https://github.com/hitYangfei/mysql-sys.git
导入sys56.sql即可
也可以去Percona的官网去下载最新版本，这是我的一个clone

数据库执行最多的SQL

mysql> select * from sys.statement_analysis limit 1\G*************************** 1. row ***************************            query: COMMIT                db: zabbix        full_scan:        exec_count: 2954549        err_count: 1       warn_count: 0    total_latency: 35.64h      max_latency: 29.45 s      avg_latency: 43.43 ms     lock_latency: 00:01:17.61        rows_sent: 0    rows_sent_avg: 0    rows_examined: 0rows_examined_avg: 0       tmp_tables: 0  tmp_disk_tables: 0      rows_sorted: 0sort_merge_passes: 0           digest: e51be358a1cbf99c1acab35cc1c6b683       first_seen: 2015-02-10 13:30:57        last_seen: 2015-03-10 15:41:551 row in set (0.01 sec)

通过查询statement_analysis视图即可。

数据库执行时间最长的SQL

mysql> select * from statements_with_runtimes_in_95th_percentile limit 2\G*************************** 1. row ***************************            query: INSERT INTO t1 VALUES (...)                db: test        full_scan:        exec_count: 3        err_count: 2       warn_count: 0    total_latency: 00:04:11.35      max_latency: 00:02:32.12      avg_latency: 00:01:23.78        rows_sent: 0    rows_sent_avg: 0    rows_examined: 0rows_examined_avg: 0       first_seen: 2015-03-08 11:32:43        last_seen: 2015-03-08 11:45:15           digest: a0583512c4eb718088979fe23a35a893*************************** 2. row ***************************            query: SELECT SQL_NO_CACHE * FROM `history`                db: zabbix        full_scan: *       exec_count: 1        err_count: 0       warn_count: 0    total_latency: 40.94 s      max_latency: 40.94 s      avg_latency: 40.94 s        rows_sent: 26657012    rows_sent_avg: 26657012    rows_examined: 26657012rows_examined_avg: 26657012       first_seen: 2015-03-08 11:30:02        last_seen: 2015-03-08 11:30:02           digest: 7f898a15e3310816582f924af05483e02 rows in set (0.80 sec)

最频繁的表

mysql> select * from schema_table_statistics limit 1\G*************************** 1. row ***************************     table_schema: zabbix       table_name: history    total_latency: 2.10h     rows_fetched: 115738265    fetch_latency: 00:10:37.09    rows_inserted: 107732087   insert_latency: 1.68h     rows_updated: 0   update_latency: 0 ps     rows_deleted: 83830680   delete_latency: 00:14:43.18 io_read_requests: 479411          io_read: 7.31 GiB  io_read_latency: 00:01:43.52io_write_requests: 40810690         io_write: 625.27 GiB io_write_latency: 00:09:20.00 io_misc_requests: 876070  io_misc_latency: 22.44h1 row in set (0.05 sec)

没有使用过的索引

mysql> select * from schema_unused_indexes limit 10;+---------------+--------------+----------------+| object_schema | object_name  | index_name     |+---------------+--------------+----------------+| zabbix        | acknowledges | acknowledges_1 || zabbix        | acknowledges | acknowledges_3 || zabbix        | acknowledges | PRIMARY        || zabbix        | actions      | actions_2      || zabbix        | actions      | PRIMARY        || zabbix        | alerts       | alerts_1       || zabbix        | alerts       | alerts_5       || zabbix        | alerts       | alerts_2       || zabbix        | alerts       | alerts_6       || zabbix        | alerts       | PRIMARY        |+---------------+--------------+----------------+10 rows in set (0.00 sec)

可以考虑是不是真的需要这些索引

文件IO消耗

mysql> select * from io_global_by_file_by_bytes limit 3\G*************************** 1. row ***************************         file: @@datadir/ibdata1   count_read: 691   total_read: 12.77 MiB     avg_read: 18.92 KiB  count_write: 6064498total_written: 1.15 TiB    avg_write: 204.34 KiB        total: 1.15 TiB    write_pct: 100.00*************************** 2. row ***************************         file: @@datadir/zabbix/history.ibd   count_read: 479383   total_read: 7.31 GiB     avg_read: 16.00 KiB  count_write: 40812693total_written: 625.30 GiB    avg_write: 16.07 KiB        total: 632.61 GiB    write_pct: 98.84*************************** 3. row ***************************         file: @@datadir/zabbix/history_uint.ibd   count_read: 333255   total_read: 5.09 GiB     avg_read: 16.00 KiB  count_write: 27149763total_written: 416.14 GiB    avg_write: 16.07 KiB        total: 421.23 GiB    write_pct: 98.793 rows in set (0.00 sec)

---

http://hi.baidu.com/ytjwt/item/13931fceb4c5db43a8ba9400#713670-tsina-1-34827-128ff9f28d958dae738be418601ffbcd