MySQL系统库之sys

最新推荐文章于 2023-07-05 22:24:42 发布

morris131

最新推荐文章于 2023-07-05 22:24:42 发布

阅读量2.9k

点赞数 2

分类专栏： MySQL 文章标签：数据库 mysql sys 系统库性能优化

本文链接：https://blog.csdn.net/u022812849/article/details/116503432

版权

MySQL 专栏收录该内容

55 篇文章 15 订阅

订阅专栏

sys这个数据库主要是通过视图的形式把information_schema和
performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一
些性能信息。

sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下
所涉及的各项查询或多或少都会对性能有一定的影响。

在使用上：
sys系统库支持MySQL 5.6或更高版本，不支持MySQL5.5.x及以下版本。

sys库的启用

因为sys系统库提供了一些代替直接访问performance_schema的视图，所
以必须启用performance_schema（将performance_schema系统参数设置为ON），sys系统库的大部分功能才能正常使用。
同时要完全访问sys系统库，用户必须具有以下数据库的管理员权限。

当然sys系统库本身已经提供了启用所有需要的功能的存储过程，比如：

启用所有的wait instruments：

mysql> call sys.ps_setup_enable_instrument('wait');
+-------------------------+
| summary                 |
+-------------------------+
| Enabled 315 instruments |
+-------------------------+
1 row in set (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

启用所有事件类型的current表：

mysql> call sys.ps_setup_enable_consumer('current');
+---------------------+
| summary             |
+---------------------+
| Enabled 3 consumers |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

注意：performance_schema的默认配置就可以满足sys系统库的大部分数据
收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的
配置。

sys系统库的使用

如果使用了use语句切换默认数据库，那么就可以直接使用sys系统库下的
视图进行查询，就像查询某个库下的表一样操作。也可以使用 b_name.view_name、
db_name.procedure_name、db_name.func_name等方式，在不指定默认数据库的
情况下访问sys系统库中的对象（这叫作名称限定对象引用）。

mysql> select * from sys.version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.2       | 5.7.31-log    |
+-------------+---------------+
1 row in set (0.00 sec)

mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.2       | 5.7.31-log    |
+-------------+---------------+
1 row in set (0.00 sec)

在sys系统库下包含很多视图，它们以各种方式对performance_schema表
进行聚合计算展示。这些视图大部分是成对出现的，两个视图名称相同，但有一

个视图是带“x $前缀的，例如：host_summary_by_file_io和 x$ host_summary_by_file_io，代表按照主机进行汇总统计的文件 I/O 性能数据，
两个视图访问的数据源是相同的，但是在创建视图的语句中，不带“x $” 前缀的视图显示的是相关数值经过单位换算后的数据（单位是毫秒、秒、分钟、小时、天等），带 “ x$ ”前缀的视图显示的是原始的数据（单位是皮秒）。

mysql> show tables like '%session';
+--------------------------+
| Tables_in_sys (%session) |
+--------------------------+
| session                  |
| x$session                |
+--------------------------+
2 rows in set (0.00 sec)

查看慢 SQL 语句慢在哪里

如果我们频繁地在慢查询日志中发现某个语句执行缓慢，且在表结构、索引
结构、统计信息中都无法找出原因时，则可以利用sys系统库中的撒手锏：

sys.session视图结合performance_schema的等待事件来找出症结所在。那么
session视图有什么用呢？使用它可以查看当前用户会话的进程列表信息，看看

当前进程到底再干什么，注意，这个视图在 MySQL 5.7.9 中才出现。

首先需要启用与等待事件相关功能：

mysql> call sys.ps_setup_enable_instrument('wait');
+-----------------------+
| summary               |
+-----------------------+
| Enabled 8 instruments |
+-----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call sys.ps_setup_enable_consumer('wait');
+---------------------+
| summary             |
+---------------------+
| Enabled 2 consumers |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

然后在一个终端上模拟一个会话耗时的操作：

mysql> select sleep(30);

在另一个终端上查看其他会话阻塞在哪里：

mysql> select * from sys.session where command='query' and conn_id!=connection_id()\G;
*************************** 1. row ***************************
                thd_id: 30
               conn_id: 4
                  user: root@localhost
                    db: sys
               command: Query
                 state: User sleep
                  time: 3
     current_statement: select sleep(30)
     statement_latency: 2.25 s
              progress: NULL
          lock_latency: 0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 0 bytes
             last_wait: wait/synch/cond/sql/Item_func_sleep::cond
     last_wait_latency: Still Waiting
                source: 
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 1451
          program_name: mysql
1 row in set (0.38 sec)

查询表的增、删、改、查数据量和 I/O 耗时统计

mysql> select * from schema_table_statistics_with_buffer\G;
*************************** 1. row ***************************
              table_schema: sys
                table_name: sys_config
              rows_fetched: 1
             fetch_latency: 44.57 us
             rows_inserted: 0
            insert_latency: 0 ps
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 16
                   io_read: 147.85 KiB
           io_read_latency: 2.35 ms
         io_write_requests: 0
                  io_write: 0 bytes
          io_write_latency: 0 ps
          io_misc_requests: 17
           io_misc_latency: 9.46 ms
   innodb_buffer_allocated: 16.00 KiB
        innodb_buffer_data: 338 bytes
        innodb_buffer_free: 15.67 KiB
       innodb_buffer_pages: 1
innodb_buffer_pages_hashed: 0
   innodb_buffer_pages_old: 0
 innodb_buffer_rows_cached: 6
1 row in set (0.23 sec)

查看是否有事务锁等待

innodb_lock_wait视图通常由于分析事务锁（行锁）等待，注意必须要有事务正在等待该视图才会有值。

演示步骤：

步骤	会话1	会话2	会话3
1	begin;
2	select * from dept where id=1 for update;
3		begin;
4		select * from dept where id=1 for update;
5			select * from sys.innodb_lock_waits\G;

mysql> select * from sys.innodb_lock_waits\G;
*************************** 1. row ***************************
                wait_started: 2021-05-07 09:19:40
                    wait_age: 00:00:05
               wait_age_secs: 5
                locked_table: `test`.`dept`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 8967
         waiting_trx_started: 2021-05-07 09:19:40
             waiting_trx_age: 00:00:05
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 5
               waiting_query: select * from dept where id=1 for update
             waiting_lock_id: 8967:44:3:2
           waiting_lock_mode: X
             blocking_trx_id: 8966
                blocking_pid: 6
              blocking_query: NULL
            blocking_lock_id: 8966:44:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2021-05-07 09:19:21
            blocking_trx_age: 00:00:24
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 6
sql_kill_blocking_connection: KILL 6
1 row in set, 3 warnings (0.01 sec)

查看InnoDB缓冲池中热点库

使用innodb_buffer_stats_by_schema视图可按照schema分组查询InnoDB缓冲池的统计信息

mysql> select * from sys.innodb_buffer_stats_by_schema;
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | allocated  | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
| InnoDB System | 1.55 MiB   | 1.22 MiB  |    99 |            0 |         0 |        1229 |
| mysql         | 240.00 KiB | 1.46 KiB  |    15 |            0 |         0 |          21 |
| sys           | 16.00 KiB  | 338 bytes |     1 |            0 |         0 |           6 |
| test          | 16.00 KiB  | 27 bytes  |     1 |            0 |         0 |           1 |
+---------------+------------+-----------+-------+--------------+-----------+-------------+
4 rows in set (0.13 sec)

视图字段含义如下：

object_schema：对象所在schema，如果该表属于Innodb存储引擎，则该字段显示为InnoDB System，如果是其他引擎，则该字段显示为每个schema name（db名）。
allocated：当前已分配给schema的总内存字节数
data：当前已分配给schema的数据部分使用的内存字节总数
pages：当前已分配给schema内存总页数
pages_hashed：当前已分配给schema的自适应hash索引页总数
pages_old：当前已分配给schema的旧页总数（位于LRU列表中的旧块子列表中的页数）
rows_cached：buffer pool中为schema缓冲的总数据行数

查看冗余索引

使用MySQL5.7.9新增的sys.schema_redundant_indexes视图，其数据来源为sys.x$schema_flattened_keys。

mysql> select * from sys.schema_redundant_indexes;

查看未使用的索引

schema_unused_indexes视图可以查看未用过的索引，其数据来源为performance_schema.table_io_waits_summary_by_index_usage。该视图在数据库运行足够长时间后数据才有参考意义。

mysql> select * from sys.schema_unused_indexes;

查看磁盘文件产生的磁盘流量与读写比例

io_global_by_file_by_bytes视图可以按照文件路径+名称分组（磁盘文件名）查看全局IO字节数、读写文件IO事件数量统计信息，默认按总IO读写字节数进行降序排列。

mysql> select * from sys.io_global_by_file_by_bytes where file like '%dept%';
+------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| file                         | count_read | total_read | avg_read  | count_write | total_written | avg_write | total      | write_pct |
+------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
| @@basedir/data/test/dept.ibd |          4 | 112.00 KiB | 28.00 KiB |           0 | 0 bytes       | 0 bytes   | 112.00 KiB |      0.00 |
| @@basedir/data/test/dept.frm |          7 | 555 bytes  | 79 bytes  |           0 | 0 bytes       | 0 bytes   | 555 bytes  |      0.00 |
+------------------------------+------------+------------+-----------+-------------+---------------+-----------+------------+-----------+
2 rows in set (0.97 sec)

各个字段含义如下：

file：被操作的文件名
count_read：总共有多少次读
total_read：总共读了多少字节
avg_read：平均每次读多少字节
count_write：总共多少次写
total_written：总共写了多少字节
avg_write：平均每次写的字节
total：读和写总共的IO
write_pct：写占IO里的占比

查看哪些语句使用了全表扫描

statements_with_full_table_scans可查看全表扫描或者没有使用到最优索引的语句（经过标准化转化的语句文本），默认按照平均扫描次数百分比和语句总延迟时间（执行时间）降序排序。数据来源：performance_schema.events_statements_summary_by_digest。

mysql> select * from sys.statements_with_full_table_scans limit 1\G;
*************************** 1. row ***************************
                   query: SELECT `information_schema` .  ... . `STATISTICS` . `INDEX_NAME` 
                      db: test
              exec_count: 1
           total_latency: 8.56 ms
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 0
           rows_examined: 6
           rows_sent_avg: 0
       rows_examined_avg: 6
              first_seen: 2021-05-07 09:27:27
               last_seen: 2021-05-07 09:27:27
                  digest: ae5d4708e5973332bb9b3a04eb4ce19d
1 row in set (0.01 sec)

视图各字段含义如下：

query：经过标准化转换的语句字符串
db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
exec_count：语句执行的总次数
total_latency：语句执行的总延迟时间（执行时间）
no_index_used_count：语句执行没有使用索引扫描表（而是使用全表扫描）的总次数
no_good_index_used_count：语句执行没有使用到更好的索引扫描表的总次数
no_index_used_pct：语句执行没有使用索引扫描表(而是使用全表扫描)的次数与语句执行总次数的百分比
rows_sent：语句执行从表返回给客户端的总数据行数
rows_examined：语句执行从存储引擎检查的总数据行数
rows_sent_avg：每个语句执行从表中返回客户端的平均数据行数
rows_examined_avg：每个语句执行从存储引擎读取的平均数据行数
first_seen：该语句第一次出现的时间
last_seen：该语句最近一次出现的时间
digest：语句摘要计算的md5 hash值

查看哪些语句使用了文件排序

statements_with_sorting视图可查看执行了文件排序的语句，默认情况下按照语句总延迟时间（执行时间）降序排序，数据来源：performance_schema.events_statements_summary_by_digest。

mysql> select * from sys.statements_with_sorting limit 1\G;
*************************** 1. row ***************************
            query: SELECT `sys` . `format_path` ( ...  ) ) , ? ) ) AS `avg_write` , 
               db: test
       exec_count: 2
    total_latency: 2.12 s
sort_merge_passes: 0
  avg_sort_merges: 0
sorts_using_scans: 2
 sort_using_range: 0
      rows_sorted: 269
  avg_rows_sorted: 135
       first_seen: 2021-05-07 09:29:28
        last_seen: 2021-05-07 09:30:19
           digest: 8a0cfcb07bad7d1e9ef40b9d99ff2c5f
1 row in set (0.00 sec)

视图字段含义如下：

query：经过标准化转换的语句字符串
db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
exec_count：语句执行的总次数
total_latency：语句执行的总延迟时间（执行时间）
sort_merge_passes：语句执行发生的语句排序合并的总次数
avg_sort_merges：语句的平均排序合并次数 SUM_SORT_MERGE_PASSES/COUNT_STAR
sorts_using_scans：语句排序执行全表扫描的总次数
sort_using_range：语句排序执行范围扫描的总次数
rows_sorted：语句执行发生排序的总数据行数
avg_rows_sorted：语句的平均排序数据行数 SUM_SORT_ROWS/COUNT_STAR
first_seen：该语句第一次出现的时间
last_seen：该语句最近一次出现的时间
digest：语句摘要计算的md5 hash值

查看哪些语句使用了临时表

tatements_with_temp_tables 查看使用了临时表的语句，默认情况下按照磁盘临时表数量和内存临时表数量进行降序排序。数据来源：performance_schema.events_statements_summary_by_digest。

mysql> select * from sys.statements_with_temp_tables limit 1\G;
*************************** 1. row ***************************
                   query: SELECT `t` . `OBJECT_SCHEMA` A ... SCHEMA` , `t` . `OBJECT_NAME` 
                      db: test
              exec_count: 1
           total_latency: 11.48 ms
       memory_tmp_tables: 63
         disk_tmp_tables: 10
avg_tmp_tables_per_query: 63
  tmp_tables_to_disk_pct: 16
              first_seen: 2021-05-07 09:28:36
               last_seen: 2021-05-07 09:28:36
                  digest: e1f092285d953ee6a7a6cc32bac9dc42
1 row in set (0.00 sec)

视图字段含义如下：

query：经过标准化转换的语句字符串
db：语句对应的默认数据库，如果没有默认数据库，该字段为NULL
exec_count：语句执行的总次数
total_latency：语句执行的总延迟时间（执行时间）
memory_tmp_tables：语句执行时创建内部内存临时表的总数量
disk_tmp_tables：语句执行时创建的内部磁盘临时表的总数量
avg_tmp_tables_per_query：语句使用内存临时表的平均数量 SUM_CREATED_TMP_TABLES/COUNT_STAR
tmp_tables_to_disk_pct：内存临时表的总数量与磁盘临时表的总数量百分比，表示磁盘临时表的转换率 SUM_CREATED_TMP_DISK_TABLES/SUM_CREATED_TMP_TABLES
first_seen：该语句第一次出现的时间
last_seen：该语句最近一次出现的时间
digest：语句摘要计算的md5 hash值

morris131

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL系统库之sys

sys这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定的影响。在使用上：sys系统库支持MySQL 5.6或更高版本，不支持MySQL5.5.x及以下版本。sys库的启用因为sys系统库提供了一些代替直接访问performance_schema的视图，所以必须
复制链接

扫一扫