Unlocking MySQL sys Schema: A DBA’s Guide to Views, Performance, and Oracle‑like Insights

Why the sys Schema Exists

MySQL’s data dictionary evolved from information_schema (MySQL 4.1) to performance_schema (MySQL 5.5) and finally to the sys schema introduced in MySQL 5.7.7. The sys schema bundles stored procedures, functions, and views that present system metadata in a way that feels familiar to Oracle DBAs, hinting at future AWR‑like tools.

Key Benefits for DBAs

Consolidates information that previously required joining information_schema and performance_schema into ready‑made views, making diagnostics more visual.

Exposes features that were unavailable or hard to query in earlier MySQL versions.

Helps understand MySQL internals by exposing table relationships, though only at a high level.

Object Distribution Inside sys

The schema contains many objects: views, tables, functions, stored procedures, and triggers. The only real table is sys_config; everything else appears as a view when you run SHOW TABLES.

sys_config Table

Stores basic parameters (ranges, bases) and defines several triggers that cascade changes on DML operations. Its role mirrors Oracle’s dba_hist_wr_control used by AWR.

x$ Views

Prefixed with x$, these are raw, unformatted memory tables similar to Oracle’s v$ views. For example, the definition of x$memory_by_thread_by_current_bytes shows picosecond‑precision timestamps, whereas higher‑level views round to seconds.

Session View Example

The session view reproduces the output of SHOW PROCESSLIST. Its definition references sys.processlist, which in turn pulls data from several performance_schema tables and the x$memory_by_thread_by_current_bytes view, illustrating the layered architecture.

Classification of sys Views

host_summary – server‑level metrics (e.g., host_summary_by_file_io).

user_summary – per‑user metrics.

InnoDB – InnoDB‑specific statistics (e.g., innodb_buffer_stats_by_schema).

IO – I/O statistics (e.g., io_global_by_file_by_bytes).

memory – memory usage (e.g., memory_by_host_by_current_bytes).

schema – schema‑level stats (e.g., schema_table_lock_waits).

session – session‑level views ( session, session_ssl_status).

statement – statement‑level analysis (e.g., statements_with_errors_or_warnings).

wait – wait‑event stats (currently limited to IO/file and lock/table).

Oracle’s Wait Interface (OWI) follows a similar philosophy, offering increasingly granular wait‑event data across versions.

InnoDB Views in Action

Example: two sessions lock the same row.

-- Session 1
START TRANSACTION;
UPDATE test SET id = 100;

-- Session 2
UPDATE test SET id = 102;

Without sys, you would query information_schema.innodb_locks and innodb_trx or run SHOW ENGINE INNODB STATUS. Using sys.innodb_lock_waits returns a single row showing the blocking transaction and provides the exact KILL command to terminate it.

Schema‑Level Views

Useful views include: schema_auto_increment_columns – detects potential overflow of auto‑increment columns. schema_unused_indexes – lists indexes that are never used. schema_tables_with_full_table_scans – identifies tables that trigger full scans. schema_redundant_indexes – finds duplicate indexes and even provides DROP statements.

Sample output of schema_unused_indexes and schema_tables_with_full_table_scans is shown in the original screenshots.

Statement‑Level Views

These help analyse query performance, e.g., statements_with_sorting, statements_with_temp_tables, and statements_with_errors_or_warnings. Screenshots illustrate execution counts, total latency, and sorting details.

Backing Up and Rebuilding sys

The version view reveals the sys component version. To export, run either:

mysqlpump sys > sys_dump.sql
# or
mysqldump --databases --routines sys > sys_dump.sql

Restoring is as simple as mysql < sys_dump.sql.

Conclusion

The sys schema offers a consolidated, Oracle‑inspired window into MySQL’s internal state, covering host, user, InnoDB, IO, memory, schema, session, statement, and wait metrics. Its views simplify performance diagnostics, lock analysis, and schema health checks, while backup procedures treat it as an independent component.