MySQL运维与监控之：`MySQL`的`sys`库：其在简化性能监控中的视图和函数。

好的，下面是关于MySQL的sys库的讲座文章：

MySQL运维与监控之：MySQL的sys库：其在简化性能监控中的视图和函数

大家好，今天我们来聊聊MySQL的sys库。对于MySQL DBA和开发者来说，性能监控和优化永远是绕不开的话题。sys库的出现，极大地简化了MySQL的性能监控过程，它提供了一系列预定义的视图和函数，帮助我们更方便地了解MySQL服务器的运行状态。

什么是sys库？

sys库是在MySQL 5.7.7版本中引入的，它是一个包含了一系列视图和存储过程的数据库。这些视图和存储过程基于performance_schema库，将复杂的性能数据转化为更易于理解和使用的形式。sys库的主要目标是：

• 简化性能监控： 将performance_schema的数据进行聚合和分析，提供友好的视图。
• 提供诊断工具： 提供存储过程，用于执行常见的诊断任务。
• 默认启用： 默认安装并启用，开箱即用。

简单来说，sys库可以看作是performance_schema库的一个高级封装，它隐藏了performance_schema库的复杂性，让我们可以更专注于分析性能问题。

sys库的依赖

sys库依赖于 performance_schema 库。如果 performance_schema 未启用，sys 库将无法正常工作。 确保 performance_schema 已启用，可以通过以下SQL语句检查：

SELECT @@performance_schema;

如果结果为 0，则需要启用 performance_schema。 启用方法是在 my.cnf 或 my.ini 配置文件中添加或修改以下配置：

performance_schema=ON

修改后重启MySQL服务。

sys库的视图

sys库提供了大量的视图，涵盖了MySQL服务器的各个方面，例如：

• CPU使用情况
• 内存使用情况
• I/O使用情况
• 锁等待情况
• SQL执行情况
• 连接信息
• 错误信息

下面我们介绍一些常用的视图，并通过示例演示如何使用它们。

1. host_summary_by_statement_type

这个视图按主机汇总了语句类型（例如SELECT、INSERT、UPDATE、DELETE）的执行情况，包括总执行次数、总延迟等。可以帮助我们了解哪些主机上的哪些类型的语句执行频率最高。

SELECT
    host,
    statement,
    COUNT(*) AS exec_count,
    SUM(total_latency) AS total_latency,
    AVG(total_latency) AS avg_latency
FROM sys.host_summary_by_statement_type
WHERE statement IN ('select', 'update', 'insert', 'delete')
ORDER BY total_latency DESC
LIMIT 10;

这个查询会返回执行SELECT、UPDATE、INSERT、DELETE语句的总延迟时间最长的10个主机。

2. memory_global_by_current_bytes

这个视图显示了全局内存使用情况，按当前使用的字节数排序。可以帮助我们了解哪些组件占用了最多的内存。

SELECT
    event_name AS memory_consumer,
    CURRENT_NUMBER_OF_BYTES AS current_bytes,
    CURRENT_NUMBER_OF_BYTES / 1024 / 1024 AS current_mb
FROM sys.memory_global_by_current_bytes
ORDER BY CURRENT_NUMBER_OF_BYTES DESC
LIMIT 10;

这个查询会返回占用内存最多的10个组件，以MB为单位显示。

3. io_global_by_wait_by_bytes

这个视图显示了全局I/O等待，按等待的字节数排序。可以帮助我们了解哪些操作导致了I/O瓶颈。

SELECT
    event_name AS io_wait,
    SUM(count_star) AS total_waits,
    SUM(sum_timer_wait) AS total_latency,
    SUM(sum_number_of_bytes) AS total_bytes
FROM sys.io_global_by_wait_by_bytes
ORDER BY total_bytes DESC
LIMIT 10;

这个查询会返回等待I/O时间最长的10个事件，并显示总等待次数、总延迟和总字节数。

4. processlist

这个视图提供了类似于SHOW PROCESSLIST的信息，但提供了更详细的信息，例如执行的SQL语句、连接的客户端信息、执行时间等。

SELECT
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM sys.processlist
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 10;

这个查询会返回当前正在执行的10个非睡眠状态的进程的信息，按执行时间降序排列。

5. statements_with_errors_or_warnings

这个视图显示了执行过程中出现错误或警告的SQL语句。可以帮助我们快速定位到可能存在问题的SQL语句。

SELECT
    db,
    full_scan,
    exec_count,
    errors,
    warnings,
    query
FROM sys.statements_with_errors_or_warnings
ORDER BY errors DESC, warnings DESC
LIMIT 10;

这个查询会返回出现错误或警告最多的10个SQL语句，并显示数据库、是否全表扫描、执行次数、错误数、警告数和SQL语句本身。

6. innodb_buffer_stats_by_table

这个视图显示了InnoDB缓冲池中各个表的统计信息，包括占用页数、数据页数、索引页数等。可以帮助我们了解哪些表占用了最多的缓冲池资源。

SELECT
    object_schema AS table_schema,
    object_name AS table_name,
    allocated,
    data,
    pages,
    pages_data,
    pages_dirty,
    rows_cached
FROM sys.innodb_buffer_stats_by_table
WHERE object_schema != 'mysql'
ORDER BY allocated DESC
LIMIT 10;

这个查询会返回占用InnoDB缓冲池最多的10个表，并显示分配的字节数、数据字节数、总页数、数据页数、脏页数和缓存的行数。

7. user_summary_by_statement_type

这个视图按用户汇总了语句类型（例如SELECT、INSERT、UPDATE、DELETE）的执行情况，包括总执行次数、总延迟等。可以帮助我们了解哪些用户执行哪些类型的语句频率最高。

SELECT
    user,
    statement,
    COUNT(*) AS exec_count,
    SUM(total_latency) AS total_latency,
    AVG(total_latency) AS avg_latency
FROM sys.user_summary_by_statement_type
WHERE statement IN ('select', 'update', 'insert', 'delete')
ORDER BY total_latency DESC
LIMIT 10;

这个查询会返回执行SELECT、UPDATE、INSERT、DELETE语句的总延迟时间最长的10个用户。

8. schema_table_statistics

这个视图按schema和table汇总了统计信息，包括行数、数据大小、索引大小等。可以帮助我们了解哪些表占用了最多的存储空间。

SELECT
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length,
    data_free
FROM sys.schema_table_statistics
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY data_length DESC
LIMIT 10;

这个查询会返回占用存储空间最多的10个表，并显示行数、数据大小、索引大小和碎片大小。

9. wait_classes_global_by_avg_latency

这个视图显示了全局等待事件的平均延迟，按平均延迟排序。可以帮助我们了解哪些等待事件导致了性能瓶颈。

SELECT
    event_name,
    total,
    total_latency,
    avg_latency,
    max_latency
FROM sys.wait_classes_global_by_avg_latency
ORDER BY avg_latency DESC
LIMIT 10;

这个查询会返回平均延迟最高的10个等待事件，并显示总次数、总延迟、平均延迟和最大延迟。

10. x$host_summary_by_file_io

这个视图展示了按主机进行的文件 I/O 统计信息，包括 I/O 次数、延迟等。这个视图需要开启 performance_schema 的相关 instrument。

SELECT host, io_count, total_latency, avg_latency
FROM sys.x$host_summary_by_file_io
ORDER BY total_latency DESC
LIMIT 10;

重要提示： sys库中以x$开头的视图是未格式化的原始视图，通常包含更详细的信息，但可读性较差。建议优先使用非x$开头的视图。

sys库的函数

sys库还提供了一些函数，用于格式化和转换数据。下面我们介绍一些常用的函数。

1. format_bytes(bytes)

将字节数格式化为人类可读的字符串，例如 "1.23 MB"。

SELECT sys.format_bytes(123456789);

2. format_time(microseconds)

将微秒数格式化为人类可读的字符串，例如 "1.23 s"。

SELECT sys.format_time(1234567);

3. ps_thread_id()

返回当前线程的performance_schema线程ID。

SELECT sys.ps_thread_id();

4. sys_get_config(variable_name)

获取MySQL服务器配置变量的值，返回值是字符串类型。

SELECT sys.sys_get_config('innodb_buffer_pool_size');

5. sys_get_available_memory()

获取系统可用的内存大小，返回值是字节数。

SELECT sys.sys_get_available_memory();

使用sys库进行性能诊断的示例

下面我们通过一个示例演示如何使用sys库进行性能诊断。假设我们发现MySQL服务器的响应速度变慢了，我们可以按照以下步骤进行诊断：

1. 检查当前活动进程： 使用sys.processlist视图查看当前正在执行的进程，找出执行时间较长的SQL语句。

   SELECT * FROM sys.processlist WHERE time > 10;

2. 检查慢查询： 使用sys.statements_with_errors_or_warnings视图查看执行过程中出现错误或警告的SQL语句，以及执行时间较长的SQL语句。

   SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY lock_time DESC LIMIT 10;

3. 检查I/O等待： 使用sys.io_global_by_wait_by_bytes视图查看I/O等待情况，找出导致I/O瓶颈的操作。

   SELECT * FROM sys.io_global_by_wait_by_bytes ORDER BY total_bytes DESC LIMIT 10;

4. 检查内存使用： 使用sys.memory_global_by_current_bytes视图查看内存使用情况，找出占用内存最多的组件。

   SELECT * FROM sys.memory_global_by_current_bytes ORDER BY current_bytes DESC LIMIT 10;

5. 检查InnoDB缓冲池： 使用sys.innodb_buffer_stats_by_table视图查看InnoDB缓冲池中各个表的统计信息，找出占用缓冲池最多的表。

   SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

通过以上步骤，我们可以快速定位到导致性能问题的根源，并采取相应的优化措施。

sys库的优点

• 易于使用： 提供了友好的视图和函数，简化了性能监控过程。
• 开箱即用： 默认安装并启用，无需额外配置。
• 功能强大： 涵盖了MySQL服务器的各个方面，提供了丰富的性能数据。
• 社区支持： 拥有庞大的用户社区，可以方便地获取帮助和支持。

sys库的局限性

• 依赖performance_schema： 如果performance_schema未启用，sys库将无法正常工作。
• 性能开销： 启用performance_schema会带来一定的性能开销，特别是对于高并发的系统。但是，通常情况下，这种开销是可以接受的。
• 数据量： performance_schema 收集的数据量很大，可能会占用大量的存储空间。需要定期清理历史数据。

总结

sys库是MySQL DBA和开发者进行性能监控和优化的利器。它通过提供一系列预定义的视图和函数，将复杂的性能数据转化为更易于理解和使用的形式，帮助我们快速定位到性能问题的根源，并采取相应的优化措施。 掌握 sys 库的使用，能够显著提升 MySQL 数据库的运维效率和性能优化能力。

最后的话

希望今天的讲解对大家有所帮助。 熟练运用 sys 库，让你的 MySQL 性能监控工作事半功倍。 持续学习和实践，让数据库性能不再是难题。