Query analytics

The Queries tab of every MySQL and MariaDB database shows which statements cost it the most — by an Impact Index, total time, calls, average time, rows examined, missing indexes and errors — over any window in the last 30 days, with the database’s statement load and average latency. The agent on the database’s active collector reads MySQL’s own statement summary every 5 minutes and sends what ran since the read before, each statement under a keyed hash. Read Database monitoring first for collectors, grants and privacy modes.

What the tab shows

Part	Shows
Window	The page’s time range on a compact picker — 1h, 3h, 12h, 1d, 5d or Custom, a length of your own or a range on the calendar.
Charts	Statement load: statement time per second, roughly the average number of statements running. Average latency (ms): total statement time divided by calls. Both over the same window.
Not tracked	When MySQL’s digest table was full: how many statement runs it could not track in the window. See Statements not tracked.
Top statements	The 50 statements leading the window in the chosen order, with Statement, Schema, Impact Index, Calls, Total time, Avg time, Rows examined, Rows sent, No index and Errors. All other statements is the last row. Its caption names the order, then the window’s start and end: Top statements by total time, highest first, …
Statement details	Click a statement’s text, or its chart button, for its drawer: its calls and average latency over the window, every counter, and when it was first and last seen.

Impact Index is how much of the database’s statement time a statement takes, from 0 to 100, with a bar: its share of the window’s total statement time across all statements, weighted up by how often it ran without an index — up to double, for a statement that never used one — and capped at 100. In full: min(100, 100 × its total time ÷ the window's total time × (1 + its no-index runs ÷ its calls)), to one decimal. It reads — for a statement with no call in the window, and on the All other statements row.
Rows examined is how many rows the server read to answer the statement; Rows sent, how many it returned. Each is the window’s total, with its average per call beneath, such as 1,204 per call. Many rows examined for each row sent means MySQL reads many rows to return a few: the classic sign of a missing index. An UPDATE or a DELETE usually sends no row.
No index is how many runs used no index, and their share of calls.
Schema is the statement’s default schema, the one selected when it ran; — when there was none.
All other statements is the window’s totals less the rows above it, so the list adds up. It has no statement count.
No statements in this window is what the tab says when nothing ran in it.

Sorting the list

Each sortable column has a sort icon in its header: Impact Index, Calls, Total time, Avg time, Rows examined, No index and Errors. Rows sent is not sortable.

Click a column to sort by it, highest first.
Click the sorted column to flip its order, between highest first and lowest first.
The list opens on Total time, highest first. The order is kept in the tab’s address (?sort= and order=), so a sorted list is a link you can share.
Every statement in the window is ranked before the 50 are kept, so lowest first lists the 50 lowest. All other statements still holds the rest: the window’s totals less the rows listed.
No index sorts by the count of runs that used no index, not by their share.

Under the list the tab says WatchTower15’s own monitoring statements are listed like any other: on an idle database they lead the list. The collector runs its own fixed list of statements, SHOW GLOBAL STATUS among them, every 15 seconds, and the reads on this page every 5 minutes, so where your own traffic is light they lead by calls and total time.

The drawer charts Calls and Average latency (ms) in 5-minute buckets for a window of up to 6 hours, hourly beyond. Its counters are calls, total, average and lock time, rows examined, sent and affected, rows examined per row sent (— when no row was sent), errors and warnings, temporary tables and temporary disk tables, full joins and full scans, sort merge passes, and the runs that used no index or no good index. First seen and Last seen are when WatchTower15 first and last received figures for the statement.

The Layers available card on the database’s Info tab shows the layer on its L5 Query analytics row: Collected, with a sparkline of the last hour’s statement load, or why not. The Overview’s Monitoring coverage gauge counts it: half while it is over the digest limit.

What is read — and what is sent

Every 5 minutes the active collector reads one table, performance_schema.events_statements_summary_by_digest, in which MySQL sums every statement it has run, one row per normalised statement and default schema:

SELECT SCHEMA_NAME, DIGEST, COUNT_STAR, SUM_TIMER_WAIT, SUM_LOCK_TIME, SUM_ERRORS, SUM_WARNINGS,
       SUM_ROWS_AFFECTED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED, SUM_CREATED_TMP_DISK_TABLES,
       SUM_CREATED_TMP_TABLES, SUM_SELECT_FULL_JOIN, SUM_SELECT_SCAN, SUM_SORT_MERGE_PASSES,
       SUM_NO_INDEX_USED, SUM_NO_GOOD_INDEX_USED, FIRST_SEEN
  FROM performance_schema.events_statements_summary_by_digest LIMIT 20001

In Clear names mode it also reads the normalised text of the statements it sends, one at a time:

SELECT LEFT(DIGEST_TEXT, 4096) FROM performance_schema.events_statements_summary_by_digest
 WHERE SCHEMA_NAME <=> ? AND DIGEST = ?

Never read: a statement’s literals, QUERY_SAMPLE_TEXT (a real run, with its values), SQL_TEXT, the statement history tables (events_statements_history and events_statements_history_long), the processlist, EXPLAIN plans, wait events, locks and the slow query log, which the database’s Logs tab reads on its own. WatchTower15 never empties the table.

What leaves your server, for each 5-minute interval:

The figures of at most 70 statements: the 50 that took the most time in the interval, and up to 5 more each that led by calls, rows examined, no-index use and errors. For each: calls, total and lock time, rows examined, sent and affected, errors, warnings, temporary tables, full joins, full scans, sort merge passes, and no-index and no-good-index use — counted for that interval only.
One All other statements row, summing every other statement, and the count of statement runs MySQL could not track.
Each statement as a key: a keyed hash of MySQL’s digest with the database’s redaction key, such as #4e1f0a…, and its schema as its own key. MySQL’s own digest never leaves your server: it is an unkeyed hash, so anyone who guessed a statement’s shape could confirm it.
Three series, in every privacy mode: statement load, average latency, and digests lost — statement runs per second that MySQL could not give a row of their own.
Statement text only in Clear names mode: see Statement text.

FIRST_SEEN stays on your server. The agent compares each read with the one before it, and uses FIRST_SEEN to tell when MySQL started a statement’s row afresh, so a MySQL restart or an emptied table is counted from zero again, never as a negative spike.

How the list adds up

A statement’s figures on the tab are the sum of the intervals in which it was sent. In an interval where it was not among those sent, it counts in All other statements instead. So a statement that leads every interval is counted in full, while one that only now and then makes an interval’s top shows less than it ran. Every interval also sends the leaders by calls, rows examined, no-index use and errors, so sorting by any of them lists the statements that lead there, not only the costliest by time.

Statement text

Only in Clear names mode. In Hashed and Counts only the text is never read.
Normalised by MySQL: literals are ?, and lists of values are (...).
At most 4,096 bytes, cut on a character boundary and marked Truncated. MySQL itself keeps the first 1,024 bytes of a statement by default (performance_schema_max_digest_length) and ends a longer one with ...: those are marked truncated too.
Sent once, then again once a day while the statement is among those sent, so a text that went missing comes back within a day.
Kept in WatchTower15’s database only, never with the statement statistics: see Erasure. On your server, a text waits in the database’s latest snapshot in the agent’s spool until WatchTower15 has stored it.

Turning on performance_schema

Query analytics needs performance_schema, MySQL’s own instrumentation. Turning it on is your choice: WatchTower15 never changes it for you, nor its consumers, instruments or sizes. Health, workload, storage and schema history do not need it.

Where the database runs	By default	To turn it on
Amazon RDS for MySQL, RDS for MariaDB	Off on the default parameter group	A custom DB parameter group with `performance_schema` set to `1`, and a reboot
Amazon Aurora MySQL	Off on the default parameter group	The same, in the DB parameter group of the cluster’s instances, and a reboot of each
Self-managed MySQL 5.7, 8.0, 8.4	On	Nothing, unless it was switched off: `performance_schema = ON` and a restart
Self-managed MariaDB	Off	`performance_schema = ON` and a restart

To check, run SELECT @@performance_schema; as any user: 1 is on.

Amazon RDS and Aurora

performance_schema is a static parameter, and RDS’s default parameter groups cannot be changed. So:

Create a DB parameter group for the instance’s engine and version, or use one of your own.
Set performance_schema to 1 in it.
Attach it to the instance. On Aurora, attach it to every instance of the cluster, so that it stays on after a failover.
Reboot the instance. The new group and its value both take effect at that reboot.

If the instance runs Performance Insights, RDS may already have switched performance_schema on: check it as above, or on the Queries tab.

Self-managed MySQL and MariaDB

In the server’s configuration, under [mysqld]:

[mysqld]
performance_schema = ON

Then restart MySQL or MariaDB.

What it costs

Throughput: about 0.4–2.4 % with the default instruments, by MySQL’s own measurement.
Memory: MySQL allocates it as its tables fill, and frees it only at a restart.
A reboot or restart to turn it on, and to change any of its sizes.

The statement digest consumer

With performance_schema on, MySQL fills the digest table through its statements_digest consumer, which is on by default. If it was switched off, the table stops growing and the tab says the digest table is empty. Turn it back on with performance_schema_consumer_statements_digest — a parameter on RDS and Aurora, an option under [mysqld] on your own server — and a reboot or restart.

The grant: tier B

Query analytics reads the digest table with tier B of the grant script:

GRANT SELECT ON performance_schema.events_statements_summary_by_digest TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';

The script includes it, so a database set up with the whole script needs nothing more. Without it the tab shows this GRANT to run. When both the grant and performance_schema are missing, the tab asks for the grant first. Revealing a statement’s key on your host needs tier B too.

When it runs

Read	When
The first read	Once the active collector finds `performance_schema` on and the digest table readable. It is a baseline and shows nothing: statements appear from the next read, 5 minutes later.
Every 5 minutes	Each read counts what ran since the one before.
Postponed under pressure	While 32 or more threads are running. A read runs at the latest 6 hours after the one before, whatever the load.
A baseline again	After a gap of more than 7 hours, and whenever collection starts afresh: a new active collector (a handover, or after Resume), an agent restart or update, or any change to the database’s setup, a switch of privacy mode included. Statements resume 5 minutes later; what ran between the last read and the baseline is not counted.

Short. The read is capped at 5 seconds on the server, and takes a few tens of milliseconds at 10,000 statements. In Clear names mode the text reads stop once a pass has used 3 seconds; the rest follow with the next passes.
One at a time. An agent reads one database’s digest table at a time, across all the databases it collects.
A failed read — a MySQL error such as a revoked grant, or the 5-second cap — skips that interval, and the next read comes 5 minutes later. It never marks the database down.
Gaps. The charts have a gap while reads are postponed or skipped. The top statements do not: the next read counts everything since the one before.

Statements not tracked

MySQL keeps a fixed number of rows in its digest table, performance_schema_digests_size, which it sizes itself: usually 10,000. Once the table is full, MySQL counts every run of a statement that has no row of its own together, in one row without a digest. The tab’s header then says, for example:

1,204 statement runs were not tracked in this window: the digest table is full.

It counts statement runs, not distinct statements. They are in neither the list nor All other statements; the digests lost series shows the same thing as a rate.

To make room, raise performance_schema_digests_size — up to 20,000, the most WatchTower15 reads from one database. It is static: a restart, or on RDS and Aurora the parameter group and a reboot. Above 20,000, WatchTower15 stops reading the table: see The limits.

The limits

Two limits keep the agent’s memory on your server small.

Limit	Above it
20,000 statements per database: the rows its digest table returns	That database’s reads stop. Set `performance_schema_digests_size` to 20,000 or less and restart MySQL (on RDS and Aurora: the parameter group and a reboot).
80,000 statements per agent: across the databases one server collects as their active collector	The databases are taken in the order of their uuids. Each keeps its reads while it and the databases before it hold 80,000 statements or fewer; any that no longer fits is skipped. Lower `performance_schema_digests_size` on some of them, or have another of your servers collect some of them (see Collector pools).

Above either limit, the L5 row reads Over the digest limit, and the tab explains both limits with their remedies.

The count is the rows each read returns, not the configured size: a table sized for 10,000 that holds 3,000 statements counts 3,000.
The uuid is the one in the address of the database’s page. When a database’s table grows, the databases after it that no longer fit are skipped at once.
It clears at the next read that fits. That read is a baseline, so statements resume 5 minutes later.
Memory. The agent keeps about 142 bytes per statement between reads: about 2.8 MB at 20,000 statements, about 11 MB at 80,000. In Clear names mode up to 200 statement texts may also wait to be sent, under 1 MB. With one read at a time, its worst case stays well inside the agent service’s 192 MB memory limit.
Everything else carries on: health, workload, storage and schema history are not affected by either limit.

When the tab has no statements

The tab says	The L5 row reads	What to do
Query analytics needs `performance_schema`, which is off on this database.	Needs performance_schema	Turn it on: on RDS and Aurora, a custom parameter group and a reboot; on your own server, the configuration and a restart.
The monitoring user cannot read statement digests.	Needs tier B	Run the tier B `GRANT` the tab shows. Statements appear within about 10 minutes.
The statement digest table has not grown for over an hour.	Digest table not growing	`performance_schema` is on, but the digest table has not grown for an hour of reads — although the collector’s own statements alone make it grow. Its `statements_digest` consumer is probably off.
No statements yet. The agent on <server> predates query analytics.	Needs the agent update	Agents update themselves within minutes of a release. Statements appear about 10 minutes after the updated agent restarts: its first read is a baseline.
More statement digests than WatchTower15 reads.	Over the digest limit	The database is over the 20,000-statement limit, or past the agent’s 80,000: see The limits. Statements return about 10 minutes after the first read that fits.

Names, keys and revealing a key

Statements and schemas follow the database’s privacy mode:

Mode	The Queries tab shows
Hashed (default)	Each statement as its key, such as `#4e1f0a…`, with Statement text needs clear names, and each schema as its key.
Clear names	Each statement’s normalised text, and its schema’s name where the Storage & growth tab knows it (its key otherwise).
Counts only	The two charts alone, under Counts only: no statement is listed, and no per-statement figure is sent.

Switching from Clear names to Hashed deletes every stored statement text; switching to Counts only also deletes the statement list.
Switching back from Counts only shows the statistics sent before the switch again, by key, until they expire. Text and first and last seen return only for the statements seen since.
Accept new key gives every statement and schema a new key: their figures carry on under the new keys, and those before stay under the old keys until they expire.

Revealing a key on your host

Click a statement’s key, then Reveal on the host, for the command to run on one of the database’s collectors — the active one or the standby — as root:

sudo /var/lib/wt15-server-agent/wt15-server-agent db reveal <database-uuid> <key>

For a statement’s key it reads the digest table on your host, which needs tier B, hashes every statement in it with the redaction key on that server, and prints the match’s schema and normalised text:

statement (schema `shop`)
SELECT `o` . `id` , `o` . `status` FROM `orders` `o` WHERE `o` . `customer_id` = ? ORDER BY `o` . `created_at` DESC LIMIT ?

It sends nothing to WatchTower15.

Several schemas. A statement that ran in several schemas lists them all, for example statement (schemas `crm`, `shop`; no default schema).
No text. It prints (MySQL keeps no text for it) when MySQL holds none.
No match. It prints no object with this key when the statement is no longer in the table, after a MySQL restart for example.
Nothing to reveal for All other statements, the statement runs not tracked, or the — of a statement run with no default schema: they name no single statement or schema.
Schema keys are found as on the Storage & growth tab: see Revealing a key on your host.
One connection, with the collector’s credentials. If MySQL refuses it with error 1203 or 1226 (the monitoring user’s connections are all in use), try again in a minute.

The window and retention

The tab shares the database page’s time range with the Overview, Logs and Incidents: changing it on one changes it on the others. It opens on the last 3 hours, like the rest of the page, and reloads every 5 minutes while the range is relative. Statement statistics are kept for 30 days. A range that starts earlier — one set on the Overview, for example — is shown from 30 days back, with the notice Statement statistics are kept for 30 days: this tab shows them from <date>. The charts are cut to the same 30 days.

Each interval counts at its end, so a window shows the intervals that ended in it. A read postponed by pressure covers a longer interval, which counts whole at its end.

Erasure

What	Where	Deleted
Statement statistics: the figures per statement and interval, keyed by hashes, never statement text	WatchTower15’s statistics store, apart from your logs	30 days after they were collected, and not earlier: not when you delete the database, switch its privacy mode or accept a new key
Statement text, only in Clear names mode	In WatchTower15’s database only	With the database, or when you switch it to another privacy mode
The statement list: each statement’s keys, its text and when it was first and last seen	In WatchTower15’s database	With the database, or when you switch to Counts only. A statement unseen for 30 days is removed, and at most 5,000 are kept per database: the least recently seen go first.

The statement load, average latency and digests-lost series are kept with the database’s other metrics. Statement statistics are not logs: they never appear in Logs and never count towards your log usage. Query analytics adds nothing to a database’s price. See GDPR & compliance and the Data Processing Agreement.

Alarms

No alarm watches statements, statement load or the statement runs not tracked.

Permissions

database.view reads the tab, the drawer and the Info tab’s L5 row; the tab has no action. Revealing a key needs root on a collector, not a WatchTower15 permission. See Roles & permissions.