MySQL performance_schema checker

Run one read-only query and paste what it prints. The checker tells you which performance_schema consumers and instruments are off on this server, what each one would record, and the statements that turn them on now and after a restart.

For MySQL 8.0 and 8.4. The output is read in your browser and sent nowhere.

1. Run this query

As a user with SELECT on performance_schema, in the mysql client or any GUI.

SELECT 'consumer' AS kind, NAME AS name, ENABLED AS enabled, NULL AS matched, NULL AS timeable, NULL AS timed
  FROM performance_schema.setup_consumers
UNION ALL
SELECT 'instrument', p.name, SUM(i.ENABLED = 'YES'), COUNT(i.NAME), SUM(i.TIMED IS NOT NULL), SUM(i.TIMED = 'YES')
  FROM (SELECT 'wait/io/table/sql/handler' AS name UNION ALL SELECT 'wait/lock/table/sql/handler'
        UNION ALL SELECT 'error' UNION ALL SELECT 'statement/sql/%' UNION ALL SELECT 'transaction'
        UNION ALL SELECT 'wait/lock/metadata/sql/mdl' UNION ALL SELECT 'statement/com/Execute') AS p
  LEFT JOIN performance_schema.setup_instruments AS i ON i.NAME LIKE REPLACE(p.name, '_', '\_')
  GROUP BY p.name
UNION ALL
SELECT 'status', VARIABLE_NAME, VARIABLE_VALUE, NULL, NULL, NULL
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME IN ('Performance_schema_digest_lost', 'Uptime')
UNION ALL
SELECT 'statements', IF(EVENT_NAME = 'statement/com/Execute', 'prepared', 'text'), SUM(COUNT_STAR), NULL, NULL, NULL
  FROM performance_schema.events_statements_summary_global_by_event_name
  WHERE EVENT_NAME = 'statement/com/Execute' OR EVENT_NAME LIKE 'statement/sql/%'
  GROUP BY IF(EVENT_NAME = 'statement/com/Execute', 'prepared', 'text')
UNION ALL
SELECT 'server', 'version', VERSION(), NULL, NULL, NULL
UNION ALL
SELECT 'server', 'performance_schema', @@GLOBAL.performance_schema, NULL, NULL, NULL;

2. Paste the output

What it checks#

performance_schema records nothing unless two things are on: the consumer that stores a kind of event, and the instrument that produces it. Consumers form a hierarchy. Nothing records without global_instrumentation, and the per-thread tables such as events_statements_current also need thread_instrumentation. An instrument with a timer counts as ready only when it is timed as well as enabled, because a wait counted but not timed reads as taking no time at all.

The checker reports on each capability below, using the consumers and instruments that Sailfish needs to fill the same views. Tested against MySQL 8.0.46 · MySQL 8.4.11.

Index and table I/O#

Rows read, inserted, updated and deleted through each index, and through no index at all. It is how you find an index nothing uses and a table being scanned. Reads table_io_waits_summary_by_index_usage, table_io_waits_summary_by_table.

Table lock waits#

How long statements waited for table-level locks, per table. Reads table_lock_waits_summary_by_table.

Error counts#

How many times each server error was raised since startup, deadlocks and lock wait timeouts among them. Reads events_errors_summary_global_by_error.

Statement digests#

Statements grouped by their normalised text, with call counts, latency, rows examined and rows sent. Reads events_statements_summary_by_digest.

Running statements#

What each connection is executing right now, and for how long. It needs thread_instrumentation as well, because the per-thread event tables sit under it. Reads events_statements_current.

Open transactions#

Transactions still open and how long they have been, which is where a held lock usually comes from. Reads events_transactions_current.

Metadata lock waits#

Who holds and who waits on a table's metadata lock, the lock a migration's ALTER TABLE queues behind. Reads metadata_locks.

Digest coverage#

How many statements arrived as native prepared statements and how many as text. MySQL 8 gives a prepared statement no digest, so this is the share of traffic the digest table cannot describe. Reads events_statements_summary_global_by_event_name.

Digests and Laravel#

Laravel's MySQL connection sends native prepared statements unless you change it, and MySQL 8 gives those no digest. On a stock Laravel app the digest table describes little beyond SET NAMES and transaction statements. The checker counts the split from events_statements_summary_global_by_event_name and says when prepared statements are most of the traffic. The fix is one connection option.

Questions#

Is the query safe to run on a production server?

It is a single SELECT over performance_schema setup and summary tables, with no locks taken on your data. The user running it needs SELECT on performance_schema, which an application user granted only its own schema does not have: GRANT SELECT ON performance_schema.* TO the user.

Why does the fix come in two forms?

An UPDATE on setup_consumers or setup_instruments takes effect at once and is lost at the next restart. The my.cnf lines keep the same settings across restarts. Most people run the UPDATE now and add the lines so a restart does not quietly undo it.

What if performance_schema itself is off?

Then the setup tables are empty and nothing can be turned on at runtime: performance_schema is a read-only variable. It needs performance_schema=ON in the server configuration and a restart. On a managed database that is a parameter group or flag rather than a my.cnf file.

Why are my Laravel queries missing from the digest table?

Laravel's MySQL connection uses native prepared statements by default, and MySQL 8 gives a statement sent through the binary prepared-statement protocol no digest. On MySQL 8.0.46 and 8.4.11, 100 native prepared executions of a query left no digest row, and the same 100 with emulated prepares were all counted. Setting PDO::ATTR_EMULATE_PREPARES to true in the connection's options gives you digests.

Does it work with MariaDB?

Not reliably. MariaDB's performance_schema has a different set of consumers and instruments, and the checker was only tested against MySQL 8.0 and 8.4.