Performance Schema ---Sys56工具_performance schema下载-优快云博客

本文链接：https://blog.youkuaiyun.com/w18704622664/article/details/44176327

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: 0
rows_examined_avg: 0
       tmp_tables: 0
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: e51be358a1cbf99c1acab35cc1c6b683
       first_seen: 2015-02-10 13:30:57
        last_seen: 2015-03-10 15:41:55
1 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: 0
rows_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: 26657012
rows_examined_avg: 26657012
       first_seen: 2015-03-08 11:30:02
        last_seen: 2015-03-08 11:30:02
           digest: 7f898a15e3310816582f924af05483e0
2 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.52
io_write_requests: 40810690
         io_write: 625.27 GiB
 io_write_latency: 00:09:20.00
 io_misc_requests: 876070
  io_misc_latency: 22.44h
1 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: 6064498
total_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: 40812693
total_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: 27149763
total_written: 416.14 GiB
    avg_write: 16.07 KiB
        total: 421.23 GiB
    write_pct: 98.79
3 rows in set (0.00 sec)