information_schema, performance_schema, SHOW Commands, and Metadata Discovery

Learning outcomes

An incident responder asks five ordinary questions: Which tables are InnoDB? Which indexes exist on work_orders? Which sessions have been idle for ten minutes? Which account has a privilege? Is Performance Schema collecting statement history? A risky operator answers by browsing the data directory and editing rows in mysql.user. MariaDB already exposes supported observability interfaces; the skill is choosing the right one and understanding its cost and stability.

SHOW statements are convenient human-facing administrative commands. INFORMATION_SCHEMA exposes relational metadata through SQL. PERFORMANCE_SCHEMA exposes low-level instrumentation when enabled and configured. The mysql system database stores server-owned privilege and internal state. The sys schema provides easier-to-read views over performance/metadata sources. These surfaces overlap but are not interchangeable.

1. Start with the question, not the favorite command

The same fact may appear in multiple places. Prefer the interface whose semantics fit your use case and whose stability/cost are documented. Monitoring tools should version-test their queries and degrade gracefully when an optional schema or column is unavailable.

2. SHOW is excellent for interactive administration

SHOW statements provide compact, familiar answers and often render information in a form designed for operators. They are ideal while exploring a server and for small scripts where the output contract is well understood. Examples include engines, plugins, variables, status, process list, grants, table definitions, columns, and indexes.

SHOW DATABASES;SHOW ENGINES;SHOW PLUGINS;SHOW GLOBAL VARIABLES LIKE 'version%';SHOW GLOBAL STATUS LIKE 'Threads%';SHOW FULL PROCESSLIST;USE servicehub;SHOW TABLES;SHOW CREATE TABLE work_orders;SHOW FULL COLUMNS FROM work_orders;SHOW INDEX FROM work_orders;SHOW GRANTS FOR CURRENT_USER;

SHOW CREATE TABLE is particularly valuable because it reveals the definition MariaDB actually stored after defaults and normalization. During migrations and incident review, save this output rather than assuming the original migration file exactly matches current schema state.

3. INFORMATION_SCHEMA turns metadata into relational queries

INFORMATION_SCHEMA lets you filter, join, aggregate, and export metadata with SQL. Use it for inventories and assertions across many objects. Remember that some row-count/cardinality values are estimates supplied by storage engines and some tables represent MariaDB-specific extensions beyond the SQL standard.

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_COLLATION,       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME;SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,       COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA,       CHARACTER_SET_NAME, COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME, ORDINAL_POSITION;SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX,       COLUMN_NAME, SUB_PART, INDEX_TYPE, CARDINALITYFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

The STATISTICS.CARDINALITY field is an estimate used in optimizer-related reasoning, not an exact count of distinct values. A monitoring script that interprets it as a billing-grade cardinality count would be using metadata outside its meaning.

4. Process metadata exposes session state—with privacy implications

INFORMATION_SCHEMA.PROCESSLIST and SHOW FULL PROCESSLIST expose running threads/sessions. Depending on privileges, an account may see only its own sessions or broader server activity. Statement text can contain business identifiers or literals, so incident exports deserve access control and redaction policies.

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,       LEFT(INFO, 200) AS infoFROM information_schema.PROCESSLISTWHERE ID <> CONNECTION_ID()ORDER BY TIME DESC;SELECT COMMAND, COUNT(*) AS sessionsFROM information_schema.PROCESSLISTGROUP BY COMMANDORDER BY sessions DESC;

Age alone is not root cause. An old sleeping connection can be harmless pool capacity; a short-running query can be blocked behind a metadata lock; a long query can be legitimate. Combine process-list evidence with transaction/lock views, application traces, and workload context before taking action.

5. Performance Schema is optional instrumentation, not a guaranteed always-on database

MariaDB implements Performance Schema as a specialized instrumentation mechanism exposed through the performance_schema database. Current MariaDB documentation notes that it can be disabled and is startup-controlled. Before querying tables such as threads or statement digests, inspect @@performance_schema and verify consumers/instruments.

SHOW VARIABLES LIKE 'performance_schema';SELECT SCHEMA_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME='performance_schema';SELECT TABLE_NAMEFROM information_schema.TABLESWHERE TABLE_SCHEMA='performance_schema'  AND TABLE_NAME IN (    'threads',    'events_statements_current',    'events_statements_summary_by_digest',    'metadata_locks'  )ORDER BY TABLE_NAME;

If it is disabled, do not “fix” the lesson by modifying the main server unexpectedly. Record the state and either use the disposable alternate instance to practice enabling it at startup or continue with INFORMATION_SCHEMA/SHOW. Observability design must match the deployed instrumentation budget.

6. When enabled, Performance Schema answers lower-level questions

Performance Schema can expose statement events, waits, locks, I/O, sockets, memory summaries, prepared statements, and thread/account dimensions. Consumers determine which event histories are retained, while instruments determine what is collected. An empty table can therefore mean “no events,” “consumer disabled,” or “instrumentation disabled”—diagnose collection configuration before concluding nothing happened.

SELECT NAME, ENABLEDFROM performance_schema.setup_consumersORDER BY NAME;SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME LIKE 'statement/%'ORDER BY NAMELIMIT 30;SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER,       PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMANDFROM performance_schema.threadsWHERE TYPE='FOREGROUND'ORDER BY PROCESSLIST_ID;

Exact table sets and columns evolve across MariaDB releases. Pin monitoring queries to tested server families, check feature availability, and prefer documented columns. Do not assume a MySQL Performance Schema dashboard can be pointed at MariaDB unchanged merely because many table names overlap.

7. sys schema provides curated views—not a new source of truth

MariaDB’s sys schema, available in modern releases, presents friendlier views and helper functions over Performance Schema and Information Schema. It can answer common operational questions with formatted latency/byte values and prebuilt joins. It is useful for humans and diagnostics, but it inherits the availability and instrumentation limits of its underlying sources.

SELECT SCHEMA_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME='sys';SELECT TABLE_NAMEFROM information_schema.VIEWSWHERE TABLE_SCHEMA='sys'ORDER BY TABLE_NAMELIMIT 30;-- If available:SELECT *FROM sys.schema_object_overviewWHERE db='servicehub'ORDER BY object_type;

The human-readable sys views and raw x$ views can serve different consumers. Monitoring systems that need stable machine-readable units should understand which form they query rather than parsing formatted strings.

8. The mysql system database is server-owned state

MariaDB stores privilege and other internal information in the mysql database. Modern MariaDB uses mysql.global_priv for global account properties, while mysql.user is retained as a compatibility view. The correct administrative pattern is CREATE USER, ALTER USER, GRANT, REVOKE, and supported statements—not direct UPDATE mysql.user ... edits copied from old tutorials.

SHOW CREATE USER;SHOW GRANTS;SELECT TABLE_NAME, TABLE_TYPEFROM information_schema.TABLESWHERE TABLE_SCHEMA='mysql'  AND TABLE_NAME IN ('global_priv','user','db','roles_mapping')ORDER BY TABLE_NAME;

Even read access to system tables can expose authentication metadata or privilege details. Restrict it. Internal layouts can change across versions, so application authorization logic should not depend on a private interpretation of mysql.global_priv JSON when supported privilege statements answer the business question.

9. Deliberately wrong approach: filesystem and internal-table archaeology

The wrong incident workflow is to infer table existence from files under datadir, edit system privilege tables directly, and scrape every Performance Schema table at one-second intervals. This bypasses storage-engine abstractions, risks corruption/security errors, and can create unnecessary monitoring overhead.

The repair is question-driven observability: use SHOW CREATE TABLE for stored DDL; INFORMATION_SCHEMA for inventory; process-list interfaces for sessions; supported account statements for privileges; Performance Schema only when enabled and needed; sys views for curated summaries; and logs for startup/runtime failures. Scope every query and validate it on the target release.

10. Hands-on metadata discovery lab

1. Run the interactive SHOW toolkit and save SHOW CREATE TABLE servicehub.work_orders.
2. Produce a table/column/index inventory using INFORMATION_SCHEMA.
3. Open two client sessions and identify both from PROCESSLIST.
4. Check @@performance_schema. If disabled, record that fact; if enabled, inspect consumers and foreground threads.
5. Detect the sys schema and query schema_object_overview if available.
6. Inspect your own grants using supported statements; do not modify any mysql system table directly.
7. Record one field that is an estimate or version-sensitive and explain why your automation should not treat it as an eternal contract.

Verification checklist

• The schema inventory is filtered to servicehub rather than scanning everything unnecessarily.
• You can explain the difference between SHOW INDEX and INFORMATION_SCHEMA.STATISTICS use cases.
• Performance Schema availability was feature-detected.
• No system table was updated directly.
• Sensitive process/account output was not copied into a public artifact.
• Your metadata automation has an exact MariaDB version assumption or feature detection.

11. Summary and bridge

MariaDB exposes layered metadata and observability surfaces. SHOW is convenient, INFORMATION_SCHEMA is relational inventory, PERFORMANCE_SCHEMA is configurable instrumentation, sys is curated diagnostics, and mysql is server-owned internal/account state. Choosing the interface based on the question is safer than treating one metadata source as universal.

The final lesson converts these tools into a baseline operating profile. You will locate and interpret error logs, set explicit time-zone/character-set/collation/SQL-mode assumptions, reproduce a reversible configuration mistake, and show how invisible locale defaults can alter application results and replication/migration behavior.