The MySQL sys schema is a collection of SQL views, functions, and procedures designed to provide MySQL administrators with deeper insights into database usage and performance. It leverages the Performance Schema to offer detailed diagnostics on statement execution, I/O, memory usage, lock waits, and more, aiding in performance tuning and troubleshooting.

How It Works

The sys schema primarily consists of views that query and aggregate data from the Performance Schema tables. It also includes helper functions for formatting data (like bytes, time, and paths) and stored procedures for managing Performance Schema configuration and performing diagnostic tasks. This approach centralizes complex Performance Schema queries into easily accessible SQL objects.

Quick Start & Requirements

• Installation: Load the schema by executing sys_*.sql files (e.g., sys_56.sql, sys_57.sql) using the MySQL client. A generate_sql_file.sh script is provided to create a single, customizable SQL file.
• Prerequisites: MySQL 5.6 or later. Performance Schema must be enabled (performance_schema = ON). Some functions and views may require specific Performance Schema instruments and consumers to be enabled.
• Resources: Installation is a SQL execution; runtime analysis depends on Performance Schema data volume and the complexity of the queried views.

Highlighted Details

• Provides over 200 views and functions for comprehensive MySQL diagnostics.
• Includes procedures for managing Performance Schema setup (enabling/disabling instruments, consumers, threads) and performing automated diagnostics.
• Offers functions for formatting raw data (bytes, time, paths) and statement text for better readability.
• Facilitates analysis of statement performance, I/O bottlenecks, lock contention, and unused indexes.

Maintenance & Community

The sys schema is an official MySQL project, maintained by the MySQL engineering team. It is part of the broader MySQL ecosystem.

Licensing & Compatibility

The project is licensed under the GPLv2.

Limitations & Caveats

• Performance Schema must be enabled for most sys schema functionality.
• The accuracy of some diagnostic views (e.g., unused indexes) depends on the server running for a representative workload period.
• Some procedures require SUPER privileges.