sys and INFORMATION_SCHEMA

The sys Schema and INFORMATION_SCHEMA

INFORMATION_SCHEMA describes objects and engine state: TABLES sizes (723 MB of data and 157 MB of indexes for page_views), INNODB_METRICS, INNODB_BUFFER_POOL_STATS (SHOW and DESCRIBE). The sys schema turns it and the Performance Schema into readable views. After the Performance Schema load:

Slowest statement shapes and indexes nobody usedSQL

SELECT query, exec_count, avg_latency, rows_examined_avg, full_scan
FROM sys.statements_with_runtimes_in_95th_percentile WHERE db = 'shop' LIMIT 1\G
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';

Output

*************************** 1. row ***************************
            query: SELECT COUNT ( * ) FROM `page_ ... r_id` = ? AND `viewed_at` >= ?
       exec_count: 96
      avg_latency: 1.17 s
rows_examined_avg: 3000000
        full_scan: *
+---------------+-------------+--------------+
| object_schema | object_name | index_name   |
+---------------+-------------+--------------+
| shop          | order_items | product_id   |
| shop          | orders      | customer_id  |
| shop          | page_views  | idx_referrer |
| shop          | products    | category_id  |
+---------------+-------------+--------------+

Three of the unused indexes back foreign keys, and counting starts at server startup, which may miss a monthly report. Make a candidate invisible first (Prefix and Invisible Indexes). sys.waits_global_by_latency ranked innodb_temp_file I/O second: the on-disk GROUP BY of Temp Tables and Sorts.

MySQLTuner 9,480 (github.com/major/MySQLTuner-perl (https://github.com/major/MySQLTuner-perl 9,480 ), GPL-3.0) reads the same counters and prints advice. Version 2.9.2 (19 August 2026) is newer than Ubuntu 26.04 225 's 2.8.29, so download mysqltuner.pl from the repository. bench.cnf holds [client] credentials; the grep keeps four of its warnings:

Selected MySQLTuner 2.9.2 findingsSQL

perl mysqltuner.pl --defaults-file bench.cnf --noprettyicon --nogood --noinfo 2>/dev/null \
  | grep -E 'Slow queries|Sorts requiring|TLS/SSL is|pool instances:'

Output

[!!] Slow queries: 51% (1K/2K)
[!!] Sorts requiring temporary tables: 387% (1K temp sorts / 355 sorts)
[!!] InnoDB buffer pool instances: 1
[!!] TLS/SSL is disabled. Connections are unencrypted.

The slow share reflects the long_query_time = 0 capture of Slow Query Log. The full report also advised a larger sort_buffer_size, which Temp Tables and Sorts found slower, and it printed Current connection is encrypted above the TLS line. One pool instance is the 9.7 default on four CPUs. Treat its tuning advice as hypotheses to test.

MENU

sys and INFORMATION_SCHEMA