sys Schema Views, User/Host Activity, I/O, Statements, Memory, and Index Usage

Learning outcomes

Performance Schema is precise but low-level. MariaDB’s sys schema provides human-friendly views and helper routines over Performance Schema and Information Schema. That convenience is valuable only if you verify the schema exists on the target server and understand the source tables behind a result. A sys view can summarize a symptom; it does not turn a heuristic into a safe production action.

1. Verify sys before using it

SELECT VERSION() AS server_version;SELECT SCHEMA_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME='sys';SELECT TABLE_NAME, TABLE_TYPEFROM information_schema.TABLESWHERE TABLE_SCHEMA='sys'ORDER BY TABLE_NAMELIMIT 30;

If no sys row appears, do not copy queries that assume it exists. Continue with Performance Schema/Information Schema directly or install/upgrade according to the exact MariaDB package/version documentation. If it exists, inspect actual views rather than relying on a MySQL sys-schema tutorial.

DROP DATABASE IF EXISTS servicehub17;CREATE DATABASE servicehub17 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub17;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  closed_at DATETIME(6) NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer(customer_id)) ENGINE=InnoDB;CREATE TABLE ticket_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  ticket_id BIGINT NOT NULL,  event_type VARCHAR(40) NOT NULL,  event_at DATETIME(6) NOT NULL,  payload VARCHAR(500) NULL,  INDEX ix_ticket_time(ticket_id, event_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,closed_at)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 500)SELECT 1000 + (n % 80),       CASE WHEN n % 7 = 0 THEN 'closed' WHEN n % 3 = 0 THEN 'waiting' ELSE 'open' END,       1 + (n % 5),       NOW(6) - INTERVAL n MINUTE,       CASE WHEN n % 7 = 0 THEN NOW(6) - INTERVAL (n-2) MINUTE ELSE NULL ENDFROM seq;INSERT INTO ticket_events(ticket_id,event_type,event_at,payload)SELECT ticket_id,       CASE WHEN ticket_id % 4=0 THEN 'comment' ELSE 'status_change' END,       opened_at + INTERVAL 30 SECOND,       RPAD('x', 120, 'x')FROM tickets;

2. User and host activity: convenience over multiple sources

sys.user_summary and sys.host_summary aggregate statement, connection, I/O, and memory information into a compact diagnostic surface. Use them to choose where to investigate next, not to label a client “bad.”

SELECT * FROM sys.user_summary ORDER BY statement_latency DESC LIMIT 10;SELECT * FROM sys.host_summary ORDER BY statement_latency DESC LIMIT 10;

The formatted views favor human readability. Many sys views also have x$... counterparts with raw numeric values intended for tooling. When building collectors, prefer raw units so parsers do not need to interpret formatted strings such as “23.4 MiB” or “4.7 ms.”

SELECT * FROM sys.host_summary_by_file_io LIMIT 10;SELECT * FROM sys.`x$host_summary_by_file_io` LIMIT 10;

Host attribution is useful for finding a noisy application tier, but NAT, proxies, connection pooling, and local background threads can change what “host” means. Record the deployment topology before treating host aggregation as user identity.

3. Statements and I/O: use sys to shortlist, then prove

USE servicehub17;SELECT COUNT(*) FROM tickets WHERE status='open';SELECT * FROM tickets WHERE customer_id=1021 ORDER BY opened_at DESC LIMIT 10;SELECT query, db, exec_count, total_latency, avg_latency, rows_examined, rows_sentFROM sys.statement_analysisWHERE db='servicehub17'ORDER BY total_latency DESCLIMIT 10;

statement_analysis is a convenient projection of digest data. Once a query shape matters, go back to the source digest row and the actual SQL plan. Formatted latency is useful to humans, but change-control decisions should preserve raw values, execution counts, and the observation window.

SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR,       SUM_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub17'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;EXPLAINSELECT * FROM servicehub17.ticketsWHERE customer_id=1021ORDER BY opened_at DESCLIMIT 10;

If the query is expensive because it returns thousands of rows, an index may not solve the user-facing problem. If the plan scans due to a missing composite index, the plan evidence—not the sys ranking alone—supports the hypothesis.

4. Memory and index usage: useful signals with sharp edges

SELECT * FROM sys.memory_global_total;SELECT * FROM sys.memory_by_user_by_current_bytesORDER BY current_allocated DESCLIMIT 10;SELECT * FROM sys.schema_index_statisticsWHERE table_schema='servicehub17'ORDER BY rows_selected DESC;

Memory instrumentation depends on Performance Schema instrumentation state. If a view returns little data, verify the source instruments before concluding memory use is negligible.

SELECT *FROM sys.schema_unused_indexesWHERE object_schema='servicehub17';SHOW CREATE TABLE servicehub17.tickets;SHOW INDEX FROM servicehub17.tickets;

Before dropping an index, check primary/unique constraints, foreign-key requirements, production query corpus, rare jobs, write cost, replica/reporting workloads, and rollback time. Then test the candidate change in a representative environment.

5. Wrong approach: “sys said unused, so drop it now”

Imagine ix_customer shows no reads in a short quiet lab window. Dropping it immediately can turn customer-history lookups into scans.

ALTER TABLE servicehub17.tickets DROP INDEX ix_customer;EXPLAINSELECT * FROM servicehub17.ticketsWHERE customer_id=1021ORDER BY opened_at DESCLIMIT 10;

The plan may now scan substantially more rows. The repair is not “sys was wrong”; the mistake was treating a bounded observation as complete workload knowledge.

ALTER TABLE servicehub17.ticketsADD INDEX ix_customer(customer_id);EXPLAINSELECT * FROM servicehub17.ticketsWHERE customer_id=1021ORDER BY opened_at DESCLIMIT 10;

6. Production judgment and cleanup

Use sys for fast triage, especially when a human needs readable summaries. Use Performance Schema for the raw evidence and exact collection semantics underneath it. For automation, prefer raw x$ views or source tables and explicitly version your collector queries.

DROP DATABASE IF EXISTS servicehub17;