一、sys schema的演进
MySQL的数据字典经历了几个阶段的演进:
MySQL 4.1 – 提供了information_schema数据字典，一些基础元数据可以通过SQL来查询得到。
MySQL 5.5 – 提供了performance_schema性能引擎，可以通过参数performance_schema来开启/关闭，说实话，看起来是有些难度。
MySQL 5.7 – 提供了sys schema，这个新特性包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息，当然自MySQL 5.7.7推出以来，让很多MySQL DBA不大适应，而我看到这个sys库的时候，第一感觉是越发和Oracle像了，不是里面的内容像，而是很多设计的方式越来相似。所以按照这种方式，我感觉离AWR（Oracle数据库运行状态表）这样的工具推出也不远了。
对于实时全面的抓取性能信息，MySQL依旧还在不断进步的路上。因为开源，所以有很多非常不错的工具，产品推出。myawr算是其中的一个，现在看来当初的设计方式和现在sys库很有相似之处，感兴趣的可以自行搜索查看。
sys库在MySQL 5.7中是默认存在的，在MySQL 5.6版本以上可以手动导入，项目github地址：https://github.com/mysql/mysql-sys。下面开始sys库学习，以下基本MySQL 5.7.10版本。
另外，如果要充分使用sys系统库的功能，则必须启用某些performance_schema的instruments和consumers，如下：
* 所有wait instruments
* 所有stage instruments
* 所有statement instruments
* 对于所启用的类型事件的instruments，还需要启用对应类型的consumers(xxx_current和xxx_history_long)，要了解某存储过程具体做了什么事情可能通过show create procedure procedure_name;语句查看
您可以使用sys系统库本身来启用所有需要的instruments和consumers：
* 启用所有wait instruments：CALL sys.ps_setup_enable_instrument(‘wait’);
* 启用所有stage instruments：CALL sys.ps_setup_enable_instrument(‘stage’);
* 启用所有statement instruments：CALL sys.ps_setup_enable_instrument(‘statement’);
* 启用所有事件类型的current表：CALL sys.ps_setup_enable_consumer(‘current’);
* 启用所有事件类型的history_long表：CALL sys.ps_setup_enable_consumer(‘history_long’);
注意，performance_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用上述所提及的所有instruments和consumers会对性能产生一定影响，因此最好仅启用所需的配置。如果你在启用了一些默认配置之外的配置，则可以使用存储过程：CALL sys.ps_setup_reset_to_default(TRUE); 来快速恢复到performance_schema的默认配置。
另外，sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定影响（主要体现在performance_schema功能实现的性能开销），在不明需求的情况下，不建议开放这些功能来作为常规的监控手段使用。
二、sys schema的借鉴意义
sys schema的数据主要源自performance_schema。其目标是把查询performance_schema的复杂度降低，让DBA能更好地利用这个库里的数据，更快地了解MySQL的运行情况。sys schema包含了一些列视图、函数和存储过程，sys schema用以帮助DBA和开发分析定位为题。
对于sys Schema，我觉得对DBA来说，有几个地方值得借鉴。
1. 原本需要结合information_schema，performance_schema查询的方式，现在有了视图的方式，把一些优化和诊断信息信息通过视图的方式汇总起来，显示更加直观。
2. sys schema的有些功能在早期版本可能无从查起，或者很难查询，现在这些因为新版本的功能提炼都做出来了。
3. 如果想好好掌握这些视图的内涵，可以随时查看表的关联关系，对于理解MySQL的运行原理和问题的分析大有帮助，当然这个地方只能点到为止。
按照这种情况，没准以后会直接把sys完全独立出来，替代information_schema，performance_schema，没准以后还会出更丰富的功能，类似Oracle中的免费的statspack，还有闭源的AWR，实时的性能数据抓取，自动性能分析和诊断，自动优化任务等，当然只是我的猜想，根据我的认知，Oracle里也是这么走过来的。
sys schema这么niubility，那么它究竟对我们DBA来说，作用有什么？或者说sys schema的一些主要用途。
- 哪个用户或者来自哪个IP的客户端使用了最多的资源？
- 数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？
- 数据库中哪些SQL被频繁执行？
- 哪个文件产生了最多的IO，读多，还是写的多？
- 哪个表上的IO请求最多？
- 哪个表被访问的最多？
- 哪些语句延迟比较严重？
- 哪些SQL执行了全表扫描或执行了排序操作？
- 哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表？
- 哪个表占用了最多的buffer pool？
- 每个库（database）占用多少buffer pool？
- 每个连接分配多少内存？
- MySQL自增长字段的最大值和当前已经使用到的值？
- MySQL索引使用情况？有哪些冗余索引和无用索引？
- MySQL内部有多个线程在运行？
等等其他…
三、sys schema下的对象分布情况
sys下的对象分布其实信息量很大，除了我们关心的视图和表以外，还有函数，存储过程和触发器。这些信息可以通过sys下的视图schema_object_overview来查看，这个视图中有所有数据库的表信息、索引信息、视图、触发器、存储过程等。下面只查看一下sys库相关信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from schema_object_overview where db='sys'; +-----+---------------+-------+ | db | object_type | count | +-----+---------------+-------+ | sys | BASE TABLE | 1 | | sys | INDEX (BTREE) | 1 | | sys | TRIGGER | 2 | | sys | FUNCTION | 21 | | sys | PROCEDURE | 26 | | sys | VIEW | 100 | +-----+---------------+-------+ 6 rows in set (0.05 sec) |
通过上面的信息，我们可以看出sys库下表只有一个，那就是sys_config，使用命令show tables显示出来的除了这个表其余都是视图。
这个表有什么特别之处呢？
该表包含sys系统库的配置选项，每个配置选项一行记录。该表是innodb表，可以通过客户端更新此表来持久化配置，server重启不会丢失。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from sys_config; +--------------------------------------+-------+---------------------+--------+ | variable | value | set_time | set_by | +--------------------------------------+-------+---------------------+--------+ | diagnostics.allow_i_s_tables | OFF | 2016-07-20 08:14:45 | NULL | | diagnostics.include_raw | OFF | 2016-07-20 08:14:45 | NULL | | ps_thread_trx_info.max_length | 65535 | 2016-07-20 08:14:45 | NULL | | statement_performance_analyzer.limit | 100 | 2016-07-20 08:14:45 | NULL | | statement_performance_analyzer.view | NULL | 2016-07-20 08:14:45 | NULL | | statement_truncate_len | 64 | 2016-07-20 08:14:45 | NULL | +--------------------------------------+-------+---------------------+--------+ 6 rows in set (0.00 sec) |
可以看到里面是一个基础参数的设置，比如一些范围，基数的设置。而且值得一提的是这个表里设置了几个触发器，对这个表的DML操作都会触发里面的数据级联变化。
1. 介绍一下这张表字段含义
variable：变量名称。
value：配置选项值。
set_time：该行配置修改的时间。
set_by：该行配置信息修改者，如果从被安装没有修改过，那么这个数据应该为NULL。
为了减少对sys_config表直接读取的次数，sys系统库中的视图、存储过程在需要使用到这些配置选项时，会优先检查这些配置选项对应的用户自定义配置选项变量(用户自定义配置选项变量与该表中的配置选项都具有相同的名称，例如：表中的diagnostics.include_raw选项，对应的自定义配置选项变量是@sys.diagnostics.include_raw)。如果用户定义的配置选项变量存在于当前会话作用域中并且是非空的，那么sys系统库中的函数、存储过程将优先使用该配置选项变量值。否则，该sys 系统库函数和存储过程将使用sys_config表中的配置选项值(从表中读取配置选项值之后，会将sys_config表中的配置选项时同时更新到用户自定义配置选项变量中，以便在同一会话后续对该值的引用时使用变量值，而不必再次从sys_config表中读取)。
示例：statement_truncate_len配置选项控制format_statement()函数返回的语句的最大长度。默认值为64。如果要临时将当前会话的值更改为32，可以设置对应的@sys.statement_truncate_len用户定义的配置选项变量：set @sys.statement_truncate_len = 32。，如果想要恢复使用表的默认值，只需要将这个会话值设置为null；set @sys.statement_truncate_len = null。或者结束当前会话（结束会话会使得用户定义的变量被销毁）重新开启一个新的会话。
2. 介绍一下变量的含义
diagnostics.allow_i_s_tables
默认为OFF ，如果开启表示允许diagnostics()存储过程在调用时会扫描INFORMATION_SCHEMA.TABLES表找到所有的基表与STATISTICS表执行联结查询，扫描每个表的统计信息。如果基表非常多，该操作可能比较昂贵。
diagnostics.include_raw
默认为OFF ，如果此选项为ON，则diagnostics()存储过程的输出信息中会包括metrics视图中的原始输出信息（该存储过程中会调用metrics视图）。
ps_thread_trx_info.max_length
由ps_thread_trx_info()函数生成的JSON输出结果的最大长度。默认值为65535字节。
statement_performance_analyzer.limit
不具有内置限制的视图在没有加limit限制时，返回的最大行数。默认值为100（例如，statements_with_runtimes_in_95th_percentile视图具有内置限制，即只返回平均执行时间为占总执行时间分布的95百分位数的语句）。
statement_performance_analyzer.view
给statement_performance_analyzer()存储过程当作入参使用的自定义查询或视图名称（statement_performance_analyzer()存储过程由diagnostics()存储过程内部调用）。如果该选项值包含空格，则将其值解释为查询语句。否则解释为视图名称，且这个视图必须是提前创建好的用于查询performance_schema.events_statements_summary_by_digest表的视图。如果statement_performance_analyzer.limit配置选项值大于0，则statement_performance_analyzer.view配置选项指定的查询语句或视图中不能有任何LIMIT子句(因为statement_performance_analyzer.limit选项在statement_performance_analyzer()存储过程中是作为一个条件判断值决定是否要添加一个LIMIT子句，如果你再自行添加一个LIMIT会导致语法错误)。statement_performance_analyzer.view配置选项默认值为NULL。
statement_truncate_len
控制format_statement()函数返回的语句文本的最大长度。超过该长度的语句文本会被截断，只保留该配置选项定义的长度文本。默认值为64字节。
3. 介绍一下表的两个触发器
sys_config_insert_set_user
当对sys_config表执行INSERT语句添加配置选项行时，sys_config_insert_set_user触发器会将sys_config表的set_by列设置为当前用户名。
sys_config_update_set_user
当对sys_config表执行UPDATE语句添加配置选项行时，sys_config_update_set_user触发器会将sys_config表的set_by列设置为当前用户名。
四、sys schema视图的两种形式
其实sys库中，对于每种视图都有两种形式，一种便于人阅读，一种便于工具处理（以”x$”开头）
- 字母开头的视图
英文字母开头的，这种是适合人类常规阅读，显示结果是格式化后的数据
- x$视图
x$视图是什么意思，通过Oracle的角度来看，就很容易理解，意思是相通的。在Oracle中，数据字典分为两种类型，一类是数据字典表，像dba_tables这样的，基表都是tab$这种的表，数据是存放在系统表空间system下的，这些信息在MySQL中就有些类似information_schema下的数据字典，而另外一类数据字典是动态性能视图，Oracle是以v$开头的，比如v$session，它的基表是x$开头的“内存表”，在MySQL sys中也是类似的意思，只是这些信息MySQL都毫无保留的开放出来了。按照官方的说法，x$的信息是没有经过格式化的，比如下面的两个视图对比。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> desc x$waits_by_host_by_latency; +---------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+-------+ | host | varchar(60) | YES | | NULL | | | event | varchar(128) | NO | | NULL | | | total | bigint(20) unsigned | NO | | NULL | | | total_latency | bigint(20) unsigned | NO | | NULL | | | avg_latency | bigint(20) unsigned | NO | | NULL | | | max_latency | bigint(20) unsigned | NO | | NULL | | +---------------+---------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> desc waits_by_host_by_latency; +---------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+-------+ | host | varchar(60) | YES | | NULL | | | event | varchar(128) | NO | | NULL | | | total | bigint(20) unsigned | NO | | NULL | | | total_latency | text | YES | | NULL | | | avg_latency | text | YES | | NULL | | | max_latency | text | YES | | NULL | | +---------------+---------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) |
五、sys schema下的视图分类
sys库能够帮助DBA快捷的了解到MySQL的一些信息，比如哪些语句使用了临时表、哪个SQL没有使用索引、哪个schema中有冗余索引、查找使用全表扫描的SQL、查找用户占用的IO等。
sys下的视图分了哪些层面呢。我简单来总结一下，大体分为一下几个层面：
1）”host”开头的视图：以主机层面为视角，统计相关的信息
host_summary
按照主机分组统计的语句延迟（执行）时间、次数、相关的文件I/O延迟、连接数和内存分配大小等摘要信息，数据来源：performance_schema.accounts、sys.x$host_summary_by_statement_latency、sys.x$host_summary_by_file_io
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select * from host_summary limit 1\G *************************** 1. row *************************** host: 10.0.30.50 statements: 411636958 statement_latency: 1.82 w statement_avg_latency: 2.67 ms table_scans: 15809097 file_ios: 397787718 file_io_latency: 2.78 h current_connections: 14 total_connections: 216159 unique_users: 1 current_memory: -133785841 bytes total_memory_allocated: 3.37 TiB |
host：客户端连接的主机名或IP。
statements：当前主机执行的语句总数。
statement_latency：语句等待时间（延迟时间）。
statement_avg_latency：执行语句平均延迟时间。
table_scans：语句的表扫描总次数。
file_ios：文件I/O事件总次数。
file_io_latency：文件I/O事件总延迟时间（执行时间）。
current_connections：当前连接数。
total_connections：总链接数。
unique_users：不同（去重）用户数量。
current_memory：当前账户分配的内存。
total_memory_allocated：该主机分配的内存总数。
host_summary_by_file_io
按主机（与用户账号组成中的host值相同）分组统计的文件I/O的IO总数和IO延迟时间，默认按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name表，调用了sys.format_time()自定义函数、sum()聚合函数对查询结果进行求和运算并转换时间单位。
|
1 2 3 4 5 |
mysql> select * from host_summary_by_file_io limit 1\G *************************** 1. row *************************** host: 10.106.68.10 ios: 538565604 io_latency: 5.13 h |
host：客户端连接的主机名或IP。
ios：文件I/O事件总次数，即可以认为就是io总数。
io_latency：文件I/O事件的总等待时间（执行时间）。
host_summary_by_file_io_type
按照主机和事件名称分组的文件I/O事件次数、延迟统计信息，默认按照主机和总I/O延迟时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。
|
1 2 3 4 5 6 7 |
mysql> select * from user_summary_by_file_io_type limit 1\G *************************** 1. row *************************** user: background event_name: wait/io/file/innodb/innodb_data_file total: 62133548 latency: 1.41 h max_latency: 5.13 s |
host：客户端连接的主机名或IP。
event_name：IO事件名称。
total：该主机发生的事件。
total_latency：该主机发生IO事件总延迟时间。
max_latency：该主机IO事件中最大的延迟时间。
host_summary_by_stages
按照主机和事件名称分组的阶段事件总次数、总执行时间、平均执行时间等统计信息，默认按照主机和总的延迟（执行）时间降序排序。数据来源：performance_schema.events_stages_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。
|
1 2 3 4 5 6 7 |
mysql> select * from host_summary_by_stages limit 1\G *************************** 1. row *************************** host: 10.0.8.97 event_name: stage/innodb/alter table (read PK and internal sort) total: 168 total_latency: 16.74 s avg_latency: 99.66 ms |
host：客户端连接的主机名或IP。
event_name：阶段事件名称。
total：阶段事件总发生次数。
total_latency：阶段事件总延迟(执行)时间。
avg_latency：阶段事件平均延迟(执行)时间。
host_summary_by_statement_latency
按照主机和事件名称分组的语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息，默认按照总延迟（执行）时间降序排序。数据来源：performance_schema.events_statements_summary_by_host_by_event_name。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select * from host_summary_by_statement_latency limit 1\G *************************** 1. row *************************** host: 10.0.30.71 total: 207 total_latency: 6.92 w max_latency: 4.18 w lock_latency: 199.00 us rows_sent: 101 rows_examined: 1004 rows_affected: 0 full_scans: 2 |
host：客户端连接的主机名或IP。
total：语句总执行次数。
total_latency：语句总延迟（执行）时间。
max_latency：主机最大的延迟时间。
lock_latency：等待锁的锁延迟时间。
rows_sent：该主机通过语句返回的总行数。
rows_examined：在存储引擎上通过语句返回的行数。
rows_affected：该主机通过语句影响的总行数。
full_scans：全表扫描的语句总数。
host_summary_by_statement_type
按照主机和语句分组的当前语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息（与performance_schema.host_summary_by_statement_latency 视图比起来，该视图只返回执行时间不为0的统计信息，且多了一个statement字段显示语句事件名称层级中的最后一部分字符），数据来源：performance_schema.events_statements_summary_by_host_by_event_name。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from host_summary_by_statement_type limit 1\G *************************** 1. row *************************** host: 10.0.30.50 statement: update total: 3546680 total_latency: 1.48 w max_latency: 3.30 m lock_latency: 6.51 m rows_sent: 0 rows_examined: 21537124693 rows_affected: 850043 full_scans: 0 |
host：客户端连接的主机名或IP。
statement：最后的语句事件名称。
total：语句总执行次数。
total_latency：语句总延迟（执行）时间。
max_latency：语句单个最大延迟（执行）时间。
lock_latency：语句总锁延迟（执行）时间。
rows_sent：语句返回给客户端的总数据行数。
rows_examined：语句从存储引擎层读取的总数据行数。
rows_affected：语句执行时受影响（DML会返回数据发生变更的受影响行数，select等不会产生数据变更的语句执行时不会有受影响行数返回）的总数据行数。
full_scans：全表扫描的语句事件总数。
2）”user”开头的视图：以用户层面为视角，统计相关信息
user_summary
查看活跃连接中按用户分组的总执行时间、平均执行时间、总的IOS、总的内存使用量、表扫描数量等统计信息，默认按照总延迟时间(执行时间)降序排序。数据来源：performance_schema.accounts、sys.x$user_summary_by_statement_latency、sys.x$user_summary_by_file_io、sys.x$memory_by_user_by_current_bytes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select * from user_summary limit 1\G *************************** 1. row *************************** user: kupaiuser statements: 344970230352 statement_latency: 1061.23 w statement_avg_latency: 1.86 ms table_scans: 10394546976 file_ios: 329858093840 file_io_latency: 16.62 w current_connections: 173 total_connections: 10298380 unique_hosts: 88 current_memory: 4.27 TiB total_memory_allocated: 3.51 PiB |
user：客户端访问用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为’background’,如果为前台线程，则该字段对应具体的用户名。
statements：对应用户执行的语句总数量。
statement_latency：对应用户执行的语句总延迟时间(执行时间)。
statement_avg_latency：对应用户执行的语句中，平均每个语句的延迟时间(执行时间)(SUM(stmt.total_latency/SUM(stmt.total))。
table_scans：对应用户执行的语句发生表扫描总次数。
file_ios：对应用户执行的语句产生的文件I/O事件总次数。
file_io_latency：对应用户执行的语句产生的文件I/O事件的总延迟时间(执行时间)。
current_connections：对应用户的当前连接数。
total_connections：对应用户的历史总连接数。
unique_hosts：对应用户来自不同主机(针对主机名去重)连接的数量。
current_memory：对应用户的连接当前已使用的内存分配量。
total_memory_allocated：对应用户的连接的历史内存分配量。
user_summary_by_file_io
按照用户分组的文件I/O延迟时间、IOS统计信息，默认按照总文件I/O时间延迟时间(执行时间)降序排序。数据来源：performance_schema.events_waits_summary_by_user_by_event_name
|
1 2 3 4 5 |
mysql> select * from user_summary_by_file_io limit 1\G *************************** 1. row *************************** user: kupaiuser ios: 3748393364 io_latency: 1.32 d |
user_summary_by_file_io_type
按照用户和事件类型(事件名称)分组的文件I/O延迟和IOS统计信息，默认情况下按照用户名和总文件I/O时间延迟时间(执行时间)降序排序。数据来源：performance_schema.events_waits_summary_by_user_by_event_name
|
1 2 3 4 5 6 7 |
mysql> select * from user_summary_by_file_io_type limit 1\G *************************** 1. row *************************** user: background event_name: wait/io/file/innodb/innodb_data_file total: 62133548 latency: 1.41 h max_latency: 5.13 s |
user_summary_by_stages
按用户分组的阶段事件统计信息，默认情况下按照用户名和阶段事件总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_stages_summary_by_user_by_event_name
user_summary_by_statement_latency
按照用户分组的语句统计信息，默认情况下按照语句总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_statements_summary_by_user_by_event_name
user_summary_by_statement_type
按用户和语句事件类型（事件类型名称为语句事件的event_name截取最后一部分字符串，也是语句command类型字符串类似）分组的语句统计信息，默认情况下按照用户名和对应语句的总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_statements_summary_by_user_by_event_name
PS：以用户视角统计的信息与以主机视角几乎相同，包括表结构，所以很多表字段意义参照以主机视角相关表。
3）”innodb”开头的视图，以InnoDB引擎层面为视角，统计innodb buffer及row lock相关信息
innodb_buffer_stats_by_schema
这个表是通过数据库统计innodb引擎的innodb缓存。
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from innodb_buffer_stats_by_schema limit 1\G *************************** 1. row *************************** object_schema: kupai_notice allocated: 2.17 GiB data: 1.39 GiB pages: 142454 pages_hashed: 142454 pages_old: 142454 rows_cached: 3899147 |
object_schema：数据库名称。
allocated：分配给当前数据库的总的字节数。
data：分配给当前数据库的数据字节数。
pages：分配给当前数据库的总页数。
pages_hashed：分配给当前数据库的hash页数。
pages_old：分配给当前数据库的旧页数。
rows_cached：当前数据库缓存的行数。
innodb_buffer_stats_by_table
|
1 2 3 4 5 6 7 |
mysql> select * from innodb_buffer_stats_by_table limit 1; +---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+ | kupai | user_wallet_log_0 | 466.53 MiB | 369.34 MiB | 29858 | 29858 | 29858 | 1849086 | +---------------+-------------------+------------+------------+-------+--------------+-----------+-------------+ 1 row in set (8.28 sec) |
object_schema：数据库名称。
object_name：表名称。
allocated：分配给表的总字节数。
data：分配该表的数据字节数。
pages：分配给表的页数。
pages_hashed：分配给表的hash页数。
pages_old：分配给表的旧页数。
rows_cached：表的行缓存数。
innodb_lock_waits
这个表其实从视图的语句来看就是information_schema这个数据库中的innodb_locks、innodb_trx这两个表的整合，能够更清晰的显示当前实例的锁情况。并且连kill语句都给你提供好了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> select * from innodb_lock_waits\G *************************** 1. row *************************** wait_started: 2017-02-07 17:07:37 wait_age: 00:00:04 wait_age_secs: 4 locked_table: `zabbix`.`users` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 76084547 waiting_trx_started: 2017-02-07 17:07:37 waiting_trx_age: 00:00:04 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 517195 waiting_query: select * from users for update waiting_lock_id: 76084547:133:3:2 waiting_lock_mode: X blocking_trx_id: 76084495 blocking_pid: 517153 blocking_query: NULL blocking_lock_id: 76084495:133:3:2 blocking_lock_mode: X blocking_trx_started: 2017-02-07 17:07:35 blocking_trx_age: 00:00:06 blocking_trx_rows_locked: 37 blocking_trx_rows_modified: 0 sql_kill_blocking_query: KILL QUERY 517153 sql_kill_blocking_connection: KILL 517153 |
wait_started：锁等待发生的时间。
wait_age：锁已经等待了多长时间。
wait_age_secs：以秒为单位显示锁已经等待的时间（5.7.9中添加此列）。
locked_table：被锁的表。
locked_index：被锁住的索引。
locked_type：锁类型。
waiting_trx_id：正在等待的事务ID。
waiting_trx_started：等待事务开始的时间。
waiting_trx_age：已经等待事务多长时间。
waiting_trx_rows_locked：正在等待的事务被锁的行数量。
waiting_trx_rows_modified：正在等待行重定义的数量。
waiting_pid：正在等待事务的线程id。
waiting_query：正在等待锁的查询。
waiting_lock_id：正在等待锁的ID。
waiting_lock_mode：等待锁的模式。
blocking_trx_id：阻塞等待锁的事务id。
blocking_pid：正在锁的线程id。
blocking_query：正在锁的查询。
blocking_lock_id：正在阻塞等待锁的锁id。
blocking_lock_mode：阻塞锁模式。
blocking_trx_started：阻塞事务开始的时间。
blocking_trx_age：阻塞的事务已经执行的时间。
blocking_trx_rows_locked：阻塞事务锁住的行的数量。
blocking_trx_rows_modified：阻塞事务重定义行的数量。
sql_kill_blocking_query：kill语句杀死正在运行的阻塞事务（在mysql5.7.9中被加入）。
sql_kill_blocking_connection：kill语句杀死会话中正在运行的阻塞事务（在mysql5.7.9中被加入）。
产生锁等待之后，只有1行信息，就是告诉你产生了阻塞，现在可以使用kill的方式终止会话，kill语句都给你提供好了。当然默认事务还是有一个超时的设置，可以看到确实是select * from users for update阻塞了。已经因为超时取消。
InnoDB相关的视图不多，只有3个，不过都蛮实用的。
4）”io”开头的视图：以I/O层面为视角，统计I/O相关的信息
io_by_thread_by_latency
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from io_by_thread_by_latency limit 3; +------------------------+-----------+---------------+-------------+-------------+-------------+-----------+----------------+ | user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id | +------------------------+-----------+---------------+-------------+-------------+-------------+-----------+----------------+ | page_cleaner_thread | 72755710 | 1.17 h | 4.52 us | 179.60 us | 5.13 s | 13 | NULL | | srv_master_thread | 13562381 | 42.06 m | 2.09 us | 1.12 ms | 4.19 s | 18 | NULL | | mysql_slave@10.0.8.153 | 130378553 | 25.82 m | 377.78 ns | 13.09 us | 175.79 ms | 12648817 | 12648792 | +------------------------+-----------+---------------+-------------+-------------+-------------+-----------+----------------+ 3 rows in set (0.07 sec) |
这个过程主要信息是通过IO的消耗展示IO等待的时间。
user：对于当前线程来说，这个值是线程被分配的账户，对于后台线程来讲，就是线程的名称。
total：IO事件的总数。
total_latency：IO事件的总延迟。
min_latency：单个最小的IO事件延迟。
avg_latency：平均IO延迟。
max_latency：最大IO延迟
thread_id：线程ID。
processlist_id：对于当前线程就是此时的ID，对于后台就是null。
io_global_by_file_by_bytes
io_global_by_file_by_latency
io_global_by_wait_by_bytes
io_global_by_wait_by_latency
latest_file_io
5）”memory”开头的视图：以内存层面为视角，统计分析内存使用情况相关信息。
memory_by_host_by_current_bytes
|
1 2 3 4 5 6 7 8 |
mysql> select * from memory_by_host_by_current_bytes limit 1\G *************************** 1. row *************************** host: 10.0.30.59 current_count_used: 13345623 current_allocated: 50.57 GiB current_avg_alloc: 3.97 KiB current_max_alloc: 50.29 GiB total_allocated: 4.01 TiB |
memory_by_thread_by_current_bytes
memory_by_user_by_current_bytes
memory_global_by_current_bytes
memory_global_total
6）”schema”开头的视图：以schema层面为视角，统计schema相关信息
schema_auto_increment_columns
如果要查看一个列值溢出的情况，比如是否列的自增值会超出数据类型的限制，这个问题对很多MySQL DBA一直以来都是一个挑战，视图schema_auto_increment_columns就给你包装好了，直接用即可。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from schema_auto_increment_columns limit 1\G *************************** 1. row *************************** table_schema: kupai table_name: crowd_share column_name: crowd_share_id data_type: int column_type: int(11) is_signed: 1 is_unsigned: 0 max_value: 2147483647 auto_increment: 1482484122 auto_increment_ratio: 0.6903 1 row in set (0.09 sec) |
schema_index_statistics
用来统计此实例的所有索引的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from schema_index_statistics limit 1\G *************************** 1. row *************************** table_schema: kupai table_name: bk_crowd_funding_base_info index_name: crowd_funding_id rows_selected: 2303447 select_latency: 1.12 d rows_inserted: 0 insert_latency: 0 ps rows_updated: 705819 update_latency: 39.76 s rows_deleted: 0 delete_latency: 0 ps |
schema_object_overview
对所有scheme下的表、函数、存储过程、触发器、表、索引、视图等信息的统计。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from schema_object_overview limit 2\G *************************** 1. row *************************** db: information_schema object_type: SYSTEM VIEW count: 61 *************************** 2. row *************************** db: kupai object_type: BASE TABLE count: 59 2 rows in set (0.05 sec) |
schema_redundant_indexes
如果查看一些冗余的索引，可以参考schema_redundant_indexes，删除的SQL语句都给你提供好了。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from schema_redundant_indexes limit 1\G *************************** 1. row *************************** table_schema: kupai table_name: bid_feed redundant_index_name: index_feed_id redundant_index_columns: feed_id redundant_index_non_unique: 1 dominant_index_name: PRIMARY dominant_index_columns: feed_id dominant_index_non_unique: 0 subpart_exists: 0 sql_drop_index: ALTER TABLE `kupai`.`bid_feed` DROP INDEX `index_feed_id` |
schema_table_lock_waits
schema_table_statistics
表信息统计。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> select * from schema_table_statistics limit 1\G *************************** 1. row *************************** table_schema: kupai table_name: deposit_log_0 total_latency: 9.62 w rows_fetched: 881919283591 fetch_latency: 9.62 w rows_inserted: 521422 insert_latency: 1.23 m rows_updated: 521509 update_latency: 22.01 s rows_deleted: 1151 delete_latency: 11.43 ms io_read_requests: 6591 io_read: 223.07 KiB io_read_latency: 10.73 ms io_write_requests: 259404 io_write: 3.99 GiB io_write_latency: 7.99 s io_misc_requests: 257343 io_misc_latency: 38.03 s |
schema_tables_with_full_table_scans
如果要查看那些表走了全表扫描，性能情况，可以查看schema_tables_with_full_table_scans，查询结果如下，如果数据量本身很大，这个结果就会被放大，值得关注。
|
1 2 3 4 5 6 |
mysql> select * from schema_tables_with_full_table_scans limit 1\G *************************** 1. row *************************** object_schema: kupai object_name: deposit_log_0 rows_full_scanned: 881903154513 latency: 9.62 w |
schema_unused_indexes
如果一个表的索引没有使用到，以前pt工具也可以做一些分析，现在查个视图就搞定了。当然索引的部分，一方面和采样率也有关系，不是一个绝对的结果。查看schema_unused_indexes的结果如下：
|
1 2 3 4 5 |
mysql> select * from schema_unused_indexes limit 1\G *************************** 1. row *************************** object_schema: kupai object_name: bank_recharge index_name: unique |
schema_table_statistics_with_buffer
7）”session”开头的视图：以会话层面为视角，统计用户连接相关的信息
session
此视图就是用来存储show processlist信息。
session_ssl_status
此视图是用来记录当前连接线程中，哪些是使用ssl连接的。
8）”statement”开头的视图：以语句层面为视角，统计分析相关语句的信息。
statement_analysis
用来统计所有的SQL执行语句的，相同的语句在此表中只会记录一条，同时会记录这条SQL语句执行的最长时间和最短时间以及平均时间，第一次执行和最后一次执行的时间。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> select * from statement_analysis limit 1\G *************************** 1. row *************************** query: UPDATE `deposit_log_0` SET STA ... id` = ? AND `activity_id` = ? db: kupai full_scan: exec_count: 641059 err_count: 0 warn_count: 0 total_latency: 10.27 w max_latency: 2.07 m avg_latency: 9.69 s lock_latency: 1.05 m rows_sent: 0 rows_sent_avg: 0 rows_examined: 205389268882 rows_examined_avg: 320391 rows_affected: 640884 rows_affected_avg: 1 tmp_tables: 0 tmp_disk_tables: 0 rows_sorted: 0 sort_merge_passes: 0 digest: f8c9521186001fea7e545f54587b5aa2 first_seen: 2016-07-21 22:21:17 last_seen: 2017-03-28 17:18:11 1 row in set (0.08 sec) |
9）”statements”开头的视图：以语句层面为视角，统计分析出错的语句，进行全表扫描， 运行时间超长，排序等语句的信息
statements_with_errors_or_warnings
执行错误或有warning的语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from statements_with_errors_or_warnings limit 1\G *************************** 1. row *************************** query: NULL db: NULL exec_count: 247112393 errors: 57113 error_pct: 0.0231 warnings: 11383 warning_pct: 0.0046 first_seen: 2016-12-02 18:12:03 last_seen: 2017-02-07 17:51:01 digest: NULL |
statements_with_full_table_scans
执行全表扫描的语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> select * from statements_with_full_table_scans limit 1\G *************************** 1. row *************************** query: SELECT `a` . * FROM `user_wall ... `a` . `uid` != `b` . `buyer` db: kupai exec_count: 1 total_latency: 995.81 ms no_index_used_count: 1 no_good_index_used_count: 0 no_index_used_pct: 100 rows_sent: 2 rows_examined: 413447 rows_sent_avg: 2 rows_examined_avg: 413447 first_seen: 2016-11-26 16:51:26 last_seen: 2016-11-26 16:51:26 digest: d86b83615a995eb581b45c994a6a5fa0 1 row in set (0.82 sec) |
statements_with_runtimes_in_95th_percentile
查看执行时间比较长的SQL语句。
statements_with_sorting
查看语句的排序情况，资源使用情况，延时等都会提供出来。当然，在这里SQL语句做了删减，不过大体能看出语句的信息，执行次数和延时等都可以看到。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> select * from statements_with_sorting limit 1\G *************************** 1. row *************************** query: SELECT * FROM `deposit_log_0` ... ? ORDER BY `update_time` DESC db: kupai exec_count: 3378885 total_latency: 6.81 d sort_merge_passes: 0 avg_sort_merges: 0 sorts_using_scans: 3378897 sort_using_range: 0 rows_sorted: 2593891 avg_rows_sorted: 1 first_seen: 2016-07-21 17:48:59 last_seen: 2017-03-28 17:30:53 digest: 84d16db393d8d7da0213920c5a27e07b 1 row in set (0.10 sec) |
statements_with_temp_tables
对于SQL语句中生成的临时表可以查看statements_with_temp_tables ，比如某一个语句生成的临时表情况，都做了统计。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select * from statements_with_temp_tables limit 1\G *************************** 1. row *************************** query: SHOW FULL FIELDS FROM `kupai` . `bid_goods` db: kupai exec_count: 8923776 total_latency: 2.16 h memory_tmp_tables: 8923889 disk_tmp_tables: 8923893 avg_tmp_tables_per_query: 1 tmp_tables_to_disk_pct: 100 first_seen: 2016-09-05 00:13:42 last_seen: 2017-03-28 17:33:49 digest: e38e3ce8bf6664adc76060c06737ddd3 1 row in set (0.09 sec) |
PS：这部分内容对于分析语句的性能还是尤其有用的。
10）”wait”开头的视图：以wait层面为视角，统计相关信息
这个还是处于起步阶段，等待模型有待完善，目前只有基于io/file， lock/table， io/table这三个方面，提升空间还很大。
wait_classes_global_by_latency
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from wait_classes_global_by_avg_latency; +-----------------+---------------+---------------+-------------+-------------+-------------+ | event_class | total | total_latency | min_latency | avg_latency | max_latency | +-----------------+---------------+---------------+-------------+-------------+-------------+ | wait/io/file | 4088659552 | 1.45 d | 0 ps | 30.60 us | 5.13 s | | wait/io/table | 1056658649069 | 10.26 w | 52.95 ns | 5.87 us | 4.99 m | | wait/lock/table | 1332580115 | 39.67 m | 54.38 ns | 1.79 us | 14.75 ms | +-----------------+---------------+---------------+-------------+-------------+-------------+ 3 rows in set (0.30 sec) |
wait_classes_global_by_avg_latency
|
1 2 3 4 5 6 7 8 |
mysql> select * from wait_classes_global_by_latency; +-----------------+---------------+---------------+-------------+-------------+-------------+ | event_class | total | total_latency | min_latency | avg_latency | max_latency | +-----------------+---------------+---------------+-------------+-------------+-------------+ | wait/io/table | 1057998139244 | 10.26 w | 52.95 ns | 5.87 us | 4.99 m | | wait/io/file | 4092482847 | 1.45 d | 0 ps | 30.60 us | 5.13 s | | wait/lock/table | 1333956281 | 39.70 m | 54.38 ns | 1.79 us | 14.75 ms | +-----------------+---------------+---------------+-------------+-------------+-------------+ |
waits_by_host_by_latency
waits_by_user_by_latency
waits_global_by_latency
11）”metrics”开头的视图：数据库内部的统计值信息
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from metrics where Variable_name like "%memory%"; +----------------------------------------+-----------------+--------------------+---------+ | Variable_name | Variable_value | Type | Enabled | +----------------------------------------+-----------------+--------------------+---------+ | performance_schema_memory_classes_lost | 0 | Global Status | YES | | qcache_free_memory | 1031832 | Global Status | YES | | memory_current_allocated | -4737868380 | Performance Schema | YES | | memory_total_allocated | 105551221164031 | Performance Schema | YES | +----------------------------------------+-----------------+--------------------+---------+ 4 rows in set (0.12 sec) |
Variable_name：变量名称。
Variable_value：变量值。
Type：变量类型。
Enabled：此变量是否开启。
12）”processlist”开头的视图：线程相关的信息(包含内部线程及用户连接）
此表记录着内部线程和用户连接信息，平时我们show processlist只是打印了用户连接信息，能为我们提供的有用信息不多。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> select * from processlist limit 1\G *************************** 1. row *************************** thd_id: 1 conn_id: NULL user: sql/main db: NULL command: NULL state: NULL time: 21718327 current_statement: NULL statement_latency: NULL progress: NULL lock_latency: NULL rows_examined: NULL rows_sent: NULL rows_affected: NULL tmp_tables: NULL tmp_disk_tables: NULL full_scan: NO last_statement: NULL last_statement_latency: NULL current_memory: 0 bytes last_wait: NULL last_wait_latency: NULL source: NULL trx_latency: NULL trx_state: NULL trx_autocommit: NULL pid: NULL program_name: NULL 1 row in set (0.93 sec) |
thd_id：线程ID。
conn_id：连接ID，也就是show processlist显示的ID。
current_statement：当前正在执行的语句。
last_statement：此线程最后一次执行的语句。
13）”ps_”开头开头的视图：没有工具统计的一些变量（暂时没看出来存在的价值）
|
1 2 3 4 5 6 7 |
mysql> select * from ps_check_lost_instrumentation limit 1; +--------------------------------+----------------+ | variable_name | variable_value | +--------------------------------+----------------+ | Performance_schema_digest_lost | 455840097 | +--------------------------------+----------------+ 1 row in set (0.00 sec) |
六、sys schema备份和重建
最后来说说sys的备份和重建工作，如果查看sys的版本，可以使用视图version来得到。可见是把它当做一个独立的组件一样来维护的。
|
1 2 3 4 5 6 |
mysql> select * from version; +-------------+---------------+ | sys_version | mysql_version | +-------------+---------------+ | 1.5.0 | 5.7.10-log | +-------------+---------------+ |
如果要导出，可以使用mysqlpump sys > sys_dump.sql或者mysqldump –databases –routines sys > sys_dump.sql来得到sys的创建语句，如果需要重建则更简单mysql<sys_dump.sql 即可。
七、sys schema的应用
上面把几个基础的视图都介绍完了，接下来就可以回答上篇提出的那些问题了。
- 哪个用户或者来自哪个IP的客户端使用了最多的资源？
查看每个客户端IP过来的连接消耗了多少资源。
|
1 |
mysql> select * from host_summary; |
查看某个数据文件上发生了多少IO请求。
|
1 |
mysql> select * from io_global_by_file_by_bytes; |
查看每个用户消耗了多少资源。
|
1 |
mysql> select * from user_summary; |
查看总共分配了多少内存。
|
1 |
mysql> select * from memory_global_total; |
- 数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？
查看当前连接情况。
|
1 |
mysql> select host, current_connections, statements from host_summary; |
查看当前正在执行的SQL和执行show full processlist的效果相当。
|
1 |
mysql> select conn_id, user, current_statement, last_statement from session; |
- 数据库中哪些SQL被频繁执行？
执行下面命令查询TOP 10最热SQL。
|
1 |
mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10; |
- 哪个文件产生了最多的IO，读多，还是写的多？
|
1 |
mysql> select * from io_global_by_file_by_bytes limit 10; |
- 哪个表上的IO请求最多？
|
1 |
mysql> select * from io_global_by_file_by_bytes where file like '%ibd' order by total desc limit 10; |
- 哪个表被访问的最多？
先访问statement_analysis，根据热门SQL排序找到相应的数据表。
- 哪些语句延迟比较严重？
查看statement_analysis中avg_latency的最高的SQL。
|
1 |
mysql> select * from statement_analysis order by avg_latency desc limit 10; |
或者查看statements_with_runtimes_in_95th_percentile视图。
|
1 |
mysql> select * from statements_with_runtimes_in_95th_percentile; |
- 哪些SQL执行了全表扫描或执行了排序操作？
|
1 2 |
mysql> select * from statements_with_sorting; mysql> select * from statements_with_full_table_scans; |
- 哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表？
查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可。
|
1 |
mysql> select db, query, tmp_tables, tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20; |
也可以查看statements_with_temp_tables视图。
|
1 |
mysql> select * from statements_with_temp_tables\G |
- 哪个表占用了最多的buffer pool？
|
1 |
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10; |
- 每个库（database）占用多少buffer pool？
|
1 |
mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10; |
- 每个连接分配多少内存？
利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。
|
1 2 |
mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id; |
- MySQL自增长字段的最大值和当前已经使用到的值？
|
1 |
mysql> select * from schema_auto_increment_columns; |
- MySQL索引使用情况统计？
|
1 |
mysql> select * from schema_index_statistics; |
- MySQL有哪些冗余索引和无用索引？
|
1 2 |
mysql> select * from schema_redundant_indexes; mysql> select * from schema_unused_indexes |
- MySQL内部有多个线程在运行？
MySQL内部的线程类型及数量。
|
1 |
mysql> select user, count(*) from processlist group by user; |
八、操作风险
写到这里，其实我们只涉及到sys schema很少的一部分功能。估计MySQL的官方AWR也要快出现了，大家也可以期待一下（MySQL 8.0这块又进一步规范了一下）。
另外，建议尽量不要在线上大量部署通过查询sys或performance_schema，抑或通过information_schema中的表或试图来完成一些监控、巡检等工作，因为查询这些信息时，MySQL会消耗大量的资源去收集相关信息，严重的可能会导致业务请求被阻塞，从而引起故障。所以在使用时，必须了解清楚，谨慎操作。
<延伸>
