In this lesson, you will learn how to leverage the sys schema to quickly identify configuration misalignments that cause performance bottlenecks.
The Power of the Sys Schema
While the performance_schema provides raw, granular telemetry, it can be overwhelming to parse. The sys schema acts as a high-level abstraction layer, designed specifically for DBAs to pinpoint common "gotchas" in server configuration without running dozens of complex JOINs.
When we talk about configuration misalignments, we are usually looking for cases where your instance’s settings (like those for the buffer pool or thread cache) deviate from the actual workload behavior.
Identifying Misalignments
One of the most common configuration issues is inefficient memory allocation. For instance, you might have allocated a large innodb_buffer_pool_size, but if your sys.innodb_buffer_stats_by_table shows that very few pages are being cached, you have a configuration mismatch.
You can use the sys.schema_redundant_indexes view to identify another common misalignment: indexes that exist but are never used by the query optimizer. These consume memory and slow down write throughput via the Change Buffer:
-- Identify redundant indexes that bloat the schema
SELECT * FROM sys.schema_redundant_indexes;
Similarly, if you suspect your thread handling is misaligned, you can check sys.metrics to see if thread creation is spiking, which might indicate that your thread_cache_size is too low compared to the application's connection churn:
-- Check for thread creation spikes
SELECT variable_value
FROM sys.metrics
WHERE variable_name = 'Threads_created';
By querying these consolidated views, you don't just see individual numbers; you see the impact of your settings. If sys.io_global_by_file_by_latency shows high latency on system tablespaces, you know your storage configuration (e.g., redo log placement or Doublewrite Buffer settings) is not aligned with your disk’s I/O capabilities.
Instead of hunting through hundreds of lines of SHOW VARIABLES, use these views to identify where the engine's behavior contradicts your intended tuning strategy.
Key Takeaway: The sys schema transforms complex performance data into actionable insights, allowing you to bridge the gap between static configuration variables and dynamic database performance.