Unlock MySQL 5.7 Performance with the New sys Schema: Views, Functions, and Real‑World Examples

What is the sys schema?

The sys schema, introduced in MySQL 5.7.7, is a system database that bundles a collection of views, functions, and stored procedures designed to make MySQL easier to use. It aggregates information from the performance schema and information schema, presenting answers to common operational questions such as which statements use temporary tables, which user generates the most I/O, which threads consume the most memory, and which indexes are unused.

Views, Functions, and Stored Procedures

All objects can be listed with a simple query (image omitted for brevity). The schema contains many views—only sys_config is an InnoDB table—plus helper functions and procedures.

Views

Views are named to indicate their purpose (e.g., host_summary_by_file_io). Two naming styles exist: a human‑readable form that formats time and byte units, and a machine‑oriented form prefixed with x$ for easier tool processing. Example definition of host_summary_by_file_io shows a complex SQL that would be difficult to interpret without sys schema’s abstraction.

Functions

The schema provides utility functions such as sys.format_time for formatting time units, along with several other helpful functions (see screenshot).

Stored Procedures

Most procedures start with the ps_ prefix, indicating they control performance schema statistics. For example, ps_truncate_all_tables truncates all summary tables in the performance schema.

Practical Examples

Three common use cases illustrate the schema’s convenience:

Finding redundant indexes : Previously required Percona’s pt-duplicate-key-checker; now a simple query against sys.schema_redundant_indexes returns the information.

Identifying unused indexes : Earlier you had to query the information schema; now sys.schema_unused_indexes provides the result directly.

Detecting statements that performed full table scans : The view statements_with_full_table_scans lists such queries without extra tooling.

Potential Future Additions

Several useful pieces of information are currently missing from the sys schema but could be added, such as:

Tables without a primary key.

Creators of temporary tables.

Users who have not properly closed database connections.

These suggestions are illustrated with screenshots of the corresponding queries.

Conclusion

The article provides a comprehensive overview of the sys schema’s purpose, components, and practical benefits, demonstrating how it simplifies performance analysis and reduces reliance on external tools. Readers are encouraged to explore the provided views, functions, and procedures to deepen their understanding of MySQL diagnostics.