时间：2021-07-01 10:21:17 帮助过：14人阅读
24 MySQL sys框架
24.1 sys框架的前提条件
24.2 使用sys框架
24.3 sys框架进度报告
24.4 sys框架的对象
24.4.1所有sys下的对象
24.4.2 sys框架的表和触发器
24.4.2.1 sys_config
24.4.3 性能框架视图
24.4.4 sys框架存储过程
24.4.5 sys框架存储函数
在开始使用sys框架之前，有一些前提条件，sys需要mysql 5.6或者更高版本。因为sys是performance_schema的替代方案，performance_schema必须启动，sys才能够使用。
为了完全访问sys，需要有以下权限：
其他权限：
特定的performance_Schema消费者和记录点要启动：
你可以使用sys下面的存储过程启动所有这些选项：
CALL sys.ps_setup_enable_instrument(‘wait‘);
CALL sys.ps_setup_enable_instrument(‘stage‘);
CALL sys.ps_setup_enable_instrument(‘statement‘);
CALL sys.ps_setup_enable_consumer(‘current‘);
CALL sys.ps_setup_enable_consumer(‘history_long‘);
注意点：
对于很多sys的使用，默认的性能框架数据收集就能够满足，启动所有的记录点和消费者会对性能有一点影响，所以最好只启动你要的配置。通过这个函数也可以返回默认配置：
CALL sys.ps_setup_reset_to_default(TRUE);
查看sys版本和mysql版本：
mysql> USE sys;
Database changed
mysql> SELECT * FROM version;
+-------------+-----------------+
| sys_version | mysql_version |
+-------------+-----------------+
| 1.5.0 | 5.7.9-debug-log |
+-------------+-----------------+
Sys框架下包含了很多视图合计了性能框架的表。很多这些视图成对出现的，比如一个成员的名字和另外一个一样，只是加了x$的前缀。比如host_summary_by_file_io有个名字一样的x$host_summary_by_file_io，2个显示的单位不同。
mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host | ios | io_latency |
+------------+-------+------------+
| localhost | 67570 | 5.38 s |
| background | 3468 | 4.18 s |
+------------+-------+------------+
mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host | ios | io_latency |
+------------+-------+---------------+
| localhost | 67574 | 5380678125144 |
| background | 3474 | 4758696829416 |
+------------+-------+---------------+
没有x$前缀的表更容易读。带x$和不带x$显示的数值是一样大的用来用具获取和处理这些数据。
可以使用show 语句或者information_schema的查询获取对象的定义比如：
mysql> SHOW CREATE VIEW session;
mysql> SHOW CREATE FUNCTION format_bytes;
mysqldump，mysqlpump默认不导出sys框架，导出sys需要显示设置：
mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql
导入sys结构：
mysql < sys_dump.sql
Mysql 5.7.9，在sys下提供了长运行事务的进度报告：
Processlist
session
x$processlist
x$session
假设请求的记录点和消费者已经启动了，这些视图的progress列显示了完成的百分比。
stage进度报告要启动events_stages_current消费者，还有一些需要启动的记录点信息：
stage/sql/Copying to tmp table
stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
stage/innodb/alter table (log apply table)
stage/innodb/alter table (merge sort)
stage/innodb/alter table (read PK 和 internal sort)
stage/innodb/buffer pool load
对于stage不支持简历和完成工作报告，如果请求记录点和消费者没有启动，progress列为null。
Table 24.1 sys Schema Tables 和 Triggers
|
Table or Trigger Name |
Description |
|
sys_config |
sys schema configuration options |
|
sys_config_insert_set_user |
sys_config insert trigger |
|
sys_config_update_set_user |
sys_config update trigger |
Table 24.2 sys Schema Views
|
View Name |
Description |
|
host_summary, x$host_summary |
Statement activity, file I/O, 和 connections, grouped by host |
|
host_summary_by_file_io, x$host_summary_by_file_io |
File I/O, grouped by host |
|
host_summary_by_file_io_type, x$host_summary_by_file_io_type |
File I/O, grouped by host 和 event type |
|
host_summary_by_stages, x$host_summary_by_stages |
Statement stages, grouped by host |
|
host_summary_by_statement_latency, x$host_summary_by_statement_latency |
Statement statistics, grouped by host |
|
host_summary_by_statement_type, x$host_summary_by_statement_type |
Statements executed, grouped by host 和 statement |
|
innodb_buffer_stats_by_schema, x$innodb_buffer_stats_by_schema |
InnoDB buffer information, grouped by schema |
|
innodb_buffer_stats_by_table, x$innodb_buffer_stats_by_table |
InnoDB buffer information, grouped by schema 和 table |
|
innodb_lock_waits, x$innodb_lock_waits |
InnoDB lock information |
|
io_by_thread_by_latency, x$io_by_thread_by_latency |
I/O consumers, grouped by thread |
|
io_global_by_file_by_bytes, x$io_global_by_file_by_bytes |
Global I/O consumers, grouped by file 和 bytes |
|
io_global_by_file_by_latency, x$io_global_by_file_by_latency |
Global I/O consumers, grouped by file 和 latency |
|
io_global_by_wait_by_bytes, x$io_global_by_wait_by_bytes |
Global I/O consumers, grouped by bytes |
|
io_global_by_wait_by_latency, x$io_global_by_wait_by_latency |
Global I/O consumers, grouped by latency |
|
latest_file_io, x$latest_file_io |
Most recent I/O, grouped by file 和 thread |
|
memory_by_host_by_current_bytes, x$memory_by_host_by_current_bytes |
Memory use, grouped by host |
|
memory_by_thread_by_current_bytes, x$memory_by_thread_by_current_bytes |
Memory use, grouped by thread |
|
memory_by_user_by_current_bytes, x$memory_by_user_by_current_bytes |
Memory use, grouped by user |
|
memory_global_by_current_bytes, x$memory_global_by_current_bytes |
Memory use, grouped by allocation type |
|
memory_global_total, x$memory_global_total |
Total memory use |
|
metrics |
Server metrics |
|
processlist, x$processlist |
Processlist information |
|
ps_check_lost_instrumentation |
Variables that have lost instruments |
|
schema_auto_increment_columns |
AUTO_INCREMENT column information |
|
schema_index_statistics, x$schema_index_statistics |
Index statistics |
|
schema_object_overview |
Types of objects within each schema |
|
schema_redundant_indexes |
Duplicate or redundant indexes |
|
schema_table_lock_waits, x$schema_table_lock_waits |
Sessions waiting for metadata locks |
|
schema_table_statistics, x$schema_table_statistics |
Table statistics |
|
schema_table_statistics_with_buffer,x$schema_table_statistics_with_buffer |
Table statistics, including InnoDB buffer pool statistics |
|
schema_tables_with_full_table_scans,x$schema_tables_with_full_table_scans |
Tables being accessed with full scans |
|
schema_unused_indexes |
Indexes not in active use |
|
session, x$session |
Processlist information for user sessions |
|
session_ssl_status |
Connection SSL information |
|
statement_analysis, x$statement_analysis |
Statement aggregate statistics |
|
statements_with_errors_or_warnings, x$statements_with_errors_or_warnings |
Statements that have produced errors or warnings |
|
statements_with_full_table_scans, x$statements_with_full_table_scans |
Statements that have done full table scans |
|
statements_with_runtimes_in_95th_percentile,x$statements_with_runtimes_in_95th_percentile |
Statements with highest average runtime |
|
statements_with_sorting, x$statements_with_sorting |
Statements that performed sorts |
|
statements_with_temp_tables, x$statements_with_temp_tables |
Statements that used temporary tables |
|
user_summary, x$user_summary |
User statement 和 connection activity |
|
user_summary_by_file_io, x$user_summary_by_file_io |
File I/O, grouped by user |
|
user_summary_by_file_io_type, x$user_summary_by_file_io_type |
File I/O, grouped by user 和 event |
|
user_summary_by_stages, x$user_summary_by_stages |
Stage events, grouped by user |
|
user_summary_by_statement_latency, x$user_summary_by_statement_latency |
Statement statistics, grouped by user |
|
user_summary_by_statement_type, x$user_summary_by_statement_type |
Statements executed, grouped by user 和 statement |
|
version |
Current sys schema 和 MySQL server versions |
|
wait_classes_global_by_avg_latency, x$wait_classes_global_by_avg_latency |
Wait class average latency, grouped by event class |
|
wait_classes_global_by_latency, x$wait_classes_global_by_latency |
Wait class total latency, grouped by event class |
|
waits_by_host_by_latency, x$waits_by_host_by_latency |
Wait events, grouped by host 和 event |
|
waits_by_user_by_latency, x$waits_by_user_by_latency |
Wait events, grouped by user 和 event |
|
waits_global_by_latency, x$waits_global_by_latency |
Wait events, grouped by event |
|
x$ps_digest_95th_percentile_by_avg_us |
Helper view for 95th-percentile views |
|
x$ps_digest_avg_latency_distribution |
Helper view for 95th-percentile views |
|
x$ps_schema_table_statistics_io |
Helper view for table-statistics views |
|
x$schema_flattened_keys |
Helper view for schema_redundant_indexes |
Table 24.3 sys Schema Stored Procedures
|
Procedure Name |
Description |
|
create_synonym_db() |
Create synonym for schema |
|
diagnostics() |
Collect system diagnostic information |
|
execute_prepared_stmt() |
Execute prepared statement |
|
ps_setup_disable_background_threads() |
Disable background thread instrumentation |
|
ps_setup_disable_consumer() |
Disable consumers |
|
ps_setup_disable_instrument() |
Disable instruments |
|
ps_setup_disable_thread() |
Disable instrumentation for thread |
|
ps_setup_enable_background_threads() |
Enable background thread instrumentation |
|
ps_setup_enable_consumer() |
Enable consumers |
|
ps_setup_enable_instrument() |
Enable instruments |
|
ps_setup_enable_thread() |
Enable instrumentation for thread |
|
ps_setup_reload_saved() |
Reload saved Performance Schema configuration |
|
ps_setup_reset_to_default() |
Reset saved Performance Schema configuration |
|
ps_setup_save() |
Save Performance Schema configuration |
|
ps_setup_show_disabled() |
Display disabled Performance Schema configuration |
|
ps_setup_show_disabled_consumers() |
Display disabled Performance Schema consumers |
|
ps_setup_show_disabled_instruments() |
Display disabled Performance Schema instruments |
|
ps_setup_show_enabled() |
Display enabled Performance Schema configuration |
|
ps_setup_show_enabled_consumers() |
Display enabled Performance Schema consumers |
|
ps_setup_show_enabled_instruments() |
Display enabled Performance Schema instruments |
|
ps_statement_avg_latency_histogram() |
Display statement latency histogram |
|
ps_trace_statement_digest() |
Trace Performance Schema instrumentation for digest |
|
ps_trace_thread() |
Dump Performance Schema data for thread |
|
ps_truncate_all_tables() |
Truncate Performance Schema summary tables |
|
statement_performance_analyzer() |
Report of statements running on server |
|
table_exists() |
Whether a table exists |
Table 24.4 sys Schema Stored Functions
|
Function Name |
Description |
|
extract_schema_from_file_name() |
Extract schema name from file path name |
|
extract_table_from_file_name() |
Extract table name from file path name |
|
format_bytes() |
Convert byte value to value with units |
|
format_path() |
Replace data 和 temp-file directories in path name with symbolic values |
|
format_statement() |
Truncate long statement to fixed length |
|
format_time() |
Convert picoseconds value to value with units |
|
list_add() |
Add item to list |
|
list_drop() |
Remove item from list |
|
ps_is_account_enabled() |
Check whether account instrumentation is enabled |
|
ps_is_consumer_enabled() |
Check whether consumer is enabled |
|
ps_is_instrument_default_enabled() |
Check whether instrument is enabled |
|
ps_is_instrument_default_timed() |
Check whether instrument is timed |
|
ps_is_thread_instrumented() |
Check whether thread is instrumented |
|
ps_thread_account() |
Return account for thread ID |
|
ps_thread_id() |
Return thread ID for connection ID |
|
ps_thread_stack() |
Return event information for thread ID |
|
ps_thread_trx_info() |
Return transaction information for thread ID |
|
quote_identifier() |
Return string as quoted identifier |
|
sys_get_config() |
Return sys schema configuration option |
|
version_major() |
MySQL server major version number |
|
version_minor() |
MySQL server minor version number |
|
version_patch() |
MySQL server patch release version number |
Sys_config表列：
为了最小化直接读取sys_config表的影响，sys框架下的函数用来检查用户定义的变量和相关的名字，这个名字使用变量以@sys.为前缀。如果当前会话有用户定义的变量部位null，那么就优先使用变量上的长度。否则就读取表上的值：
mysql> SET @stmt = ‘SELECT variable, value, set_time, set_by FROM sys_config‘;
mysql> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
mysql> SET @sys.statement_truncate_len = 32;
mysql> SELECT format_statement(@stmt);
+-----------------------------------+
| format_statement(@stmt) |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+
之后会话中的会继续使用变量的32，而不是使用表里面的64。
为了停止变量的使用可以使用以下语句，取消或者关闭当前会话：
mysql> SET @sys.statement_truncate_len = NULL;
mysql> SELECT format_statement(@stmt);
+----------------------------------------------------------+
| format_statement(@stmt) |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
变量可以在会话结束前生效，如果sys_config在会话中被修改，这个修改不会体现在会话上，除非会话结束。
Sys_config和变量的配置值：
其他选项可以通过sys_config表添加。比如diagnostics(),execute_prepared_stmt()的调试选项，但是不是sys_config的默认有的选项：
mysql> INSERT INTO sys_config (variable, value) VALUES(‘debug‘, ‘ON‘);
修改debug信息：
mysql> UPDATE sys_config SET value = ‘OFF‘ WHERE variable = ‘debug‘;
mysql> SET @sys.debug = NULL;
有一些在global_status和innodb_status中有重复的指标，metrics视图会进行消除。
mysql> tee diag.out;
mysql> CALL diagnostics(120, 30, ‘current‘);
mysql> notee;
The extract_schema_from_file_name() Function
根据文件路径获取对应的数据库名
The extract_table_from_file_name() Function
根据文件路径获取表名
The format_bytes() Function
给字节数，转化为可读的格式
The format_path() Function
文件路径格式化
The format_statement() Function
格式化语句输出，输出长度和statement_truncate_len配置有关。
The format_time() Function
时间格式化
The list_add() Function
增加一个由逗号隔开的队列中。
The list_drop() Function
从逗号隔开的队列的队列中删除一个元素
The ps_is_account_enabled() Function
判断账号是否启动
The ps_is_consumer_enabled() Function
判断消费者是否启动
The ps_is_instrument_default_enabled() Function
判断记录点是否默认启动
The ps_is_instrument_default_timed() Function
给定记录点默认是否是被计时的。
The ps_is_thread_instrumented() Function
判断连接id对应的性能框架记录点启动
The ps_thread_account() Function
给定连接id，判断线程启动的用户。
The ps_thread_id() Function
给定连接id返回线程id
The ps_thread_stack() Function
给定线程id，返回json格式的语句，stages，events的stack
The ps_thread_trx_info() Function
返回线程id的事务和已经执行的语句
The quote_identifier() Function
引用分隔符
The sys_get_config() Function
获取sys_config表的数据
The version_major() Function
mysql主版本
The version_minor() Function
mysql次版本
The version_patch() Function
mysql补丁号
25 MySQL sys框架
标签：内存 setup pad 账号 性能 metadata desc 用户 lob