sys
A collection of views, functions, and procedures to help MySQL administrators gain insight into database usage.
What is sys?
The MySQL sys schema is a built-in diagnostic tool that provides a comprehensive set of views, functions, and procedures for monitoring and analyzing MySQL server performance. It helps administrators understand database activity, identify bottlenecks, and optimize system resources by presenting data from the Performance Schema and Information Schema in a more accessible format.
MySQL database administrators and performance engineers who need to monitor, troubleshoot, and optimize MySQL server performance. It's particularly useful for those managing production databases who require actionable insights without writing complex diagnostic queries.
Developers choose the sys schema because it transforms low-level performance data into human-readable insights, eliminating the need for complex manual queries. Its dual-format views (human-readable and tool-friendly x$ versions) and configurable behavior through the sys_config table provide flexibility that raw Performance Schema queries lack.
Overview
The MySQL sys schema
Use Cases
Best For
- Monitoring MySQL server performance metrics like statement activity, file I/O, connections, and memory usage aggregated by host, user, or thread.
- Analyzing InnoDB-specific performance including buffer pool usage, lock waits, and table/index-level statistics.
- Diagnosing database bottlenecks and identifying long-running queries or lock contention through stored procedures and detailed views.
- Generating performance snapshots and diagnostic reports for troubleshooting and capacity planning.
- Customizing performance monitoring behavior through configuration options like statement truncation length and diagnostic output settings.
- Providing both human-readable formatted outputs and raw data outputs for integration with monitoring tools.
Not Ideal For
- Teams needing real-time graphical dashboards or GUI-based monitoring tools, as the sys schema is text-based and requires SQL queries.
- Environments using MySQL versions older than 5.6 or newer than 5.7 without official updates, due to limited version support in the provided install files.
- Projects with extremely tight resource constraints where the overhead of additional schema objects could negatively impact server performance.
- Administrators who prefer integrated cloud provider monitoring (e.g., AWS CloudWatch for RDS) over database-level diagnostic tools that require manual setup.
Pros & Cons
Pros
Provides aggregated metrics like host_summary for statement activity, file I/O, and connections, eliminating the need for complex manual queries against Performance Schema.
Offers both human-readable formatted views (e.g., host_summary) and raw x$ versions for tool integration, as shown in the README's structure examples.
Includes specialized views like innodb_buffer_stats_by_schema and innodb_lock_waits, giving detailed insights into buffer pool usage and lock contention.
Features a sys_config table to customize settings such as statement truncation length and diagnostic output, allowing tailored performance analysis.
Cons
Changes to sys_config table values are cached per session and don't take effect until re-login, hindering dynamic adjustments during live troubleshooting.
The README warns that objects have dependencies requiring careful loading order, and generating custom SQL files for environments like RDS adds setup steps.
Officially supports only MySQL 5.6 and 5.7 via provided scripts, leaving users on other versions to manually adapt or seek unofficial patches.
Adding numerous views, functions, and procedures can introduce query execution overhead, especially on servers already under high load.
Frequently Asked Questions
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.