Advisor

The Advisor tab of every MySQL and MariaDB database lists what is worth a look in its configuration, its schema, its workload and its security: tables without a primary key, redundant and unused indexes, statements that run without an index, a buffer pool too small for the data, a TLS certificate about to expire, and more — 21 checks in all. Each finding gives its evidence in numbers, a recommendation, and a link to its section on this page. Once a day, and when you press Run now, the agent on the database’s active collector reads a small set of facts from the database and sends them; WatchTower15 runs the checks on them within about 5 minutes. Read Database monitoring first for collectors, grants and privacy modes.

Recommendations, never changes

WatchTower15 never changes your database. The collector’s session is read-only and runs a fixed list of statements; a finding only recommends. Every fix on this page is yours to make, when you choose, and a finding you accept can be dismissed with a reason.

What the tab shows

Part	Shows
Header	The open findings by severity — Critical, Warning and Info — and when the facts were collected and evaluated: Facts collected 04:12 BST, evaluated 04:14 BST. Before their evaluation it reads …, not evaluated yet, or · the newest facts are evaluated within about 5 minutes. When a later pass was skipped, when and why: Last attempt 04:05 skipped: busy (or under pressure, or a catalogue read was stuck). Run now, if you can edit the database.
Filters	Open, Dismissed or Resolved; Severity (All severities, Critical, Warning, Info); Group (All groups, Configuration, Schema, Workload, Hardening). They are kept in the tab’s address, so a filtered view is a link you can share, and they stay in Counts only mode.
Checks	The checks with findings, under their groups, each with its severity, its title and its count. Expand one for its recommendation, How to fix it (its section on this page) and its findings, 20 a page, in the columns Object, Evidence and First seen (Resolved under that filter), with Dismiss or Restore.
Not evaluated (n)	Each check that could not be judged, with why and what enables it. See When a check is not evaluated.
Managed by Amazon RDS (n), Not applicable (n)	The checks that do not apply to this database, with why. Managed by Amazon RDS covers Aurora MySQL too. See Where each check applies.
Passed (n)	The checks that were evaluated and found nothing.

• The object is This database for a check on the whole database, or the table, index, statement or account the finding is about: its name in Clear names mode, otherwise its key.
• And 37 more not listed: WatchTower15 keeps up to 200 findings per check. under a check means it fires for more objects than it lists. See Findings.
• A check that is not evaluated but still holds findings says so above them: Not evaluated at the last evaluation: its reason and what enables it, then These findings are as last evaluated. See When a check is not evaluated.
• No open findings. is what the tab says when nothing fires; No dismissed findings. and No finding resolved in the last 30 days. under the other two filters.

Banners above the checks explain a database that cannot be checked right now:

Banner	Means
The first facts pass is due around 14:20. It runs 10–15 minutes after a collector becomes active; findings follow within about 5 minutes. Then daily at 04:00 on <collector>’s clock.	The collector became active minutes ago.
The first facts pass was skipped at 04:12: … It is retried every 30 minutes; findings follow within about 5 minutes of a pass that runs.	It was skipped for the reason shown. See Skipped passes.
Waiting for the first facts pass.	No facts have arrived yet: the first pass runs 10–15 minutes after the agent starts collecting.
No advisor facts yet. The agent on <collector> (<version>) predates the advisor. Update it: its first facts pass runs 10–15 minutes after it restarts.	Agents update themselves within minutes of a release.
This database is paused. These findings are as of <time>. Resume it to collect facts again (Collectors tab).	The findings are as they were last evaluated, and Run now is disabled.
The redaction key changed. Checks on tables, indexes, statements and accounts wait until the new key is accepted on the Info tab; their findings stay as they were. Instance checks go on.	See Names, keys and revealing a key.
Counts only. Checks on tables, indexes and accounts show a count, never which objects; checks on statements are not evaluated. To see the objects, switch the privacy mode to Hashed or Clear names in Setup → Credentials.	See Names, keys and revealing a key.

On the Info tab

The Layers available card on the database’s Info tab shows the layer’s state on its L6 Advisor row. The Overview’s Monitoring coverage gauge counts it as 1 while it reads Collected, half while it reads Partial, and 0 otherwise.

The L6 row reads	Means
Collected	Every check that applies is evaluated.
Partial: 13 of 19 checks — needs performance_schema, the optional account grant	Some checks are not evaluated for a reason you can change — a grant, performance_schema or its table I/O instrumentation, Counts only, or a new redaction key. The first figure is the checks evaluated, the second the checks that apply to this database, and the row names what enables the rest.
First pass due around 14:20	The first facts pass has not run yet.
Skipped at 04:12: busy; retried every 30 minutes	The first pass was skipped, for the reason shown.
Waiting for the first facts pass	No facts have arrived yet.
Needs the agent update	The active collector’s agent predates the advisor.

The four groups

Group	Looks at	Checks
Configuration	Server settings, against 7 days of the database’s own metrics where they matter	Buffer pool, connections, temporary tables, redo log, binary log retention, durability, table open cache
Schema	Tables and indexes, from InnoDB’s catalogue and index statistics	No primary key, redundant index, unused index, row versions, statement digest table
Workload	Statements, from 7 days of query analytics	Without an index, far more rows examined than returned, joins without an index
Hardening	Security settings, accounts and the TLS certificate	Unencrypted connections, LOAD DATA LOCAL, file import and export, writable replica, anonymous or any-host accounts, TLS certificate

Every check has a severity, as alarms do: Critical, Warning or Info. Only the TLS certificate check reaches Critical.

How the facts pass runs

When it runs

Pass	When
The first pass	10–15 minutes after a server becomes the database’s active collector: when collection starts, after an agent update, at a handover, or after a change to the database’s setup. A new database has its findings within the hour. It does not run again when the database already has facts less than 24 hours old.
Every day	At about 04:00 on the collector’s clock, in its own time zone: at a time between 03:30 and 04:30 that stays the same for each database, so one collector’s databases do not all run at once. Skipped when the database already has facts less than 12 hours old — after a Run now, for example.
Run now	Within a few minutes: see Run now.

• Evaluated within about 5 minutes. WatchTower15 runs the checks on new facts every 5 minutes. Between 01:00 and 03:00 UTC it runs none: facts that arrive then are evaluated from 03:04 UTC, and the tab says when.
• Once a day. The facts change only with a pass, so a finding you fix stays open until the next one: press Run now to see it resolve within minutes. A check on 7 days of metrics can take up to 7 days to clear after a fix that changes only the workload; each check says.

Skipped passes

A pass that cannot run now is skipped and retried every 30 minutes. The facts from the last pass that ran stay on the tab, with the skipped attempt beside them: Last attempt 04:05 skipped: busy.

The header says	The banner says	Means
under pressure	the database was under pressure	32 or more threads were running. A daily pass runs at the latest 6 hours after the first skip, whatever the load.
busy	the database was busy (its monitoring user had no connection free)	MySQL refused the pass’s connection with error 1203, or another short read held the collector’s spare connection for more than 10 seconds.
a catalogue read was stuck	a catalogue read was stuck	MySQL 5.7, Aurora MySQL 2.x and MariaDB only: an earlier read was still waiting on the server while DDL ran. Retried until it has ended.

A pass whose own connection cannot open at all is retried every 30 minutes too.

MySQL 5.7, Aurora MySQL 2.x and MariaDB. Reading InnoDB’s catalogue can wait for as long as tables are being created, altered or dropped. While DDL runs, the pass leaves the catalogue out — the checks on tables and indexes read Schema changes were running — but reads and sends everything else, and is retried every 30 minutes until the catalogue answers. At the latest 24 hours after the DDL began, the catalogue is read anyway.

Run now

Run now asks the active collector for a facts pass at once. It needs database.edit.

• Within a few minutes. The button reads Running… and the tab says Requested 10:02: the facts pass runs within a few minutes, findings follow within about 5 minutes. — or, between 01:00 and 03:00 UTC, findings follow after the first evaluation after 03:04 UTC, in your time zone. A pass can wait behind other databases’ passes on the same agent, so it may not start at once.
• One an hour. A request is refused within 60 minutes of the last one, whatever its outcome. The tab then also gives the last run’s outcome: Last run at 10:03., The last run was skipped at 10:03: …, or that it expired before the collector ran it.
• It expires. A request the collector has not picked up within 15 minutes expires.
• Audited. Each request is recorded on the database’s Audit tab as Advisor run requested.

While it cannot run, Run now is disabled, and says why. In this order:

The tab says	When
Resume the database first: a paused database collects nothing.	The database is paused.
The database is not collecting: Run now needs an active collector.	It has no active collector, or is not Active.
Update the agent on <collector> first: it predates the advisor.	The active collector’s agent predates the advisor.
Requested 10:02: the facts pass runs within a few minutes, findings follow within about 5 minutes.	A request is already waiting for its pass: the button reads Running….
Available again at 11:02: one run an hour.	A run was requested in the last hour.

Short and bounded

• Its own short connection. A pass opens one read-only connection of its own beside the collector’s, and closes it when it ends. The monitoring user’s three connections leave room for it.
• 5 seconds a statement, 30 seconds a pass. Every statement is capped at 5 seconds on the server, and the whole pass at 30. A read that reaches its cap is tried again at the next pass; the others still count.
• One at a time. An agent runs one facts pass or schema pass at a time, across all the databases it collects.
• Never in the way. The pass runs apart from the collector’s own reads, which never wait for it, and never marks the database down.
• The catalogue limits. The checks on tables and indexes follow the database’s table limit, the same one the Storage tab uses: 2,048 tables unless support raises it, at most 4,096. As backstops they also stop above 50,000 tables and partitions, 250,000 indexes or 500,000 index columns. Above any of these, those checks are not evaluated, and every other check carries on.
• Memory. The agent keeps what it reads in compact form: within 24 MiB at those limits.

What is read — and what is sent

Read	For	Needs
InnoDB’s catalogue: its tables, indexes and index columns — information_schema.INNODB_TABLES, INNODB_INDEXES and INNODB_FIELDS; INNODB_SYS_TABLES, INNODB_SYS_INDEXES and INNODB_SYS_FIELDS on MySQL 5.7 and MariaDB	Tables without a primary key, redundant indexes, row versions	PROCESS (tier A)
performance_schema.table_io_waits_summary_by_index_usage: the indexes no statement has used, and whether table I/O is counted at all	Unused indexes	Tier B, and performance_schema on
mysql.innodb_index_stats: the size of each unused index	Unused indexes	Tier C — without it, no size
Six columns of mysql.user	Anonymous and any-host accounts	The optional account grant
The server certificate’s dates, from the pass’s own TLS handshake	The TLS certificate	Nothing

The other checks read the variables, status counters and replica state the collector already reads for its other layers, and the database’s metrics and query analytics that WatchTower15 already holds: nothing more is read on your server for them. Never read: a row of your tables, a password hash, a view’s definition.

What leaves your server, once per pass:

• Lists, never the catalogue. For each list — tables without a primary key, redundant indexes, tables near the row-version limit, unused indexes, accounts — at most the first 200 objects by key, and how many there are in all.
• Keys. Each table, index and account as a keyed hash under the database’s redaction key, such as #3f9a1c…. Names travel only in Clear names mode.
• Flagged accounts only. Anonymous accounts and accounts open to any host (%), each with its authentication plugin, TLS requirement, password expiry and lock state. On MySQL, locked accounts — roles among them — are left out; on MariaDB, roles are.
• The certificate’s dates, and how each read ended.
• In Counts only mode, each list’s count alone.

Findings

Status	Means
Open	The check fires for the object.
Dismissed	You accepted it, with a reason. It stays dismissed while the check keeps firing for the object.
Resolved	The check was evaluated and no longer fires for the object. Resolved findings are kept for 30 days.

• One finding per object. A finding is a check and its object: the database, a table, an index, a statement or an account. It keeps its First seen for as long as it fires; one that resolves and later fires again opens anew.
• Up to 200 per check. A check holds at most 200 open and dismissed findings; the rest are counted, as And 37 more not listed. The agent sends the first 200 objects by key, so the same ones are listed each day; the checks on statements keep the costliest first. A finding beyond the 200 is never resolved as if it were fixed.
• A check that is not evaluated leaves its findings as they were, open or dismissed, until it is evaluated again. It never resolves one.
• Resolving takes a pass. Fix, then press Run now.

Dismissing a finding

Dismiss (with database.edit) opens Dismiss finding, which asks Why is it acceptable?: a reason is required, up to 500 characters. The finding stays dismissed while it keeps firing, and is cleared when it resolves. The reason is kept in the database’s Audit tab. A dismissed finding:

• leaves the open counts, and is listed under Dismissed with who dismissed it, when and why;
• stays dismissed while the check keeps firing for its object, and is cleared when it resolves: if it fires again later, it opens anew;
• opens again with Restore.

Each dismissal and restore is recorded on the database’s Audit tab — Advisor finding dismissed, Advisor finding restored — with the check, the object’s key and the reason, never a name. A dismissal is lost when its finding is deleted: after Accept new key, or a switch to or from Counts only (see Names, keys and revealing a key).

Findings are not alarms

A finding raises no incident and sends no notification: no e-mail, Slack message or webhook. Findings show on the Advisor tab only. What needs watching around the clock is an alarm: connections near max_connections, for example, are both an alarm (live, at 80 % and 95 %) and a check (the 7-day peak, at 85 %).

When a check is not evaluated

A check that cannot be judged is listed under Not evaluated with why and, when it is yours to change, what enables it. It leaves its findings as they were: when it still holds some, they keep their place under the check, below Not evaluated at the last evaluation: its reason and what enables it, and These findings are as last evaluated. While a check waits on something you can change, the L6 row reads Partial.

The tab says	What to do
The monitoring user lacks the grant this check reads with.	Grant what the check names: PROCESS (tier A) for the checks on tables and indexes, tier B for unused indexes and the checks on statements, the optional account grant for accounts. See The grant script.
performance_schema is off on this database.	Turn it on: on RDS and Aurora, a custom parameter group and a reboot; on your own server, the configuration and a restart.
Table I/O is not instrumented: every index would look unused.	Enable the wait/io/table/sql/handler instrument and your tables’ instrumentation, in performance_schema.setup_instruments and setup_objects. Both are on by default with performance_schema, so they were switched off.
Query analytics is not available for this database.	See When the tab has no statements.
Not evaluated in Counts only: no statement is kept.	Switch the privacy mode to Hashed or Clear names, in Setup → Credentials.
The facts were collected with another redaction key: per-object checks wait for Accept new key.	Accept new key on the Info tab, if the key changed on purpose. See Privacy mode and the redaction key.
The database has been up for less than 7 days: index usage is counted since it started.	Nothing: it is evaluated once the server has run for 7 days.
The database has been up for less than a day: its table cache counters are too new to judge.	Nothing: the table open cache check is evaluated once the server has run for 24 hours.
Less than a day of metrics: evaluated once a day of samples exists.	Nothing: the checks on 7 days of metrics never stretch a first day into a week.
The database’s sizes are not measured yet.	Nothing: it is evaluated once Storage & growth has measured them.
Schema changes were running: the catalogue is read again every 30 minutes, and anyway 24 hours after they began.	Nothing. MySQL 5.7, Aurora MySQL 2.x and MariaDB only: see Skipped passes.
The read did not finish in time: it is retried at the next facts pass.	Nothing, unless it keeps happening on a very large catalogue.
The read failed: it is retried at the next facts pass.	Nothing.
The database holds more tables than its table limit, as on the Storage tab: the schema checks are off.	Ask support to raise the database’s table limit (at most 4,096 tables), or the checks on tables and indexes return once the database holds fewer.
The database’s catalogue is past WatchTower15’s backstops (50,000 tables and partitions, 250,000 indexes or 500,000 index columns): the schema checks are off.	Nothing raises the backstops: the checks on tables and indexes return once the catalogue is back under them.
The metrics store did not answer: evaluated again with the next facts.	Nothing.
Waiting for the first facts pass.	Nothing: it runs 10–15 minutes after the collector becomes active.
The agent on the active collector needs updating.	Nothing: agents update themselves within minutes of a release.

Managed by Amazon RDS, and not applicable

Managed by Amazon RDS lists the checks on settings that Amazon RDS sets for you on RDS for MySQL, Aurora MySQL and RDS for MariaDB: binary log retention and secure_file_priv. Not applicable lists the checks that have nothing to check on this database:

The tab says	Means
This engine has no such setting.	The engine has nothing to check: row versions on MariaDB.
Not available on this version.	Row versions before MySQL 8.0.29.
Aurora keeps this in its storage layer.	The redo log and commit durability on Aurora MySQL.
Aurora readers are read-only by design.	A writable replica, on Aurora MySQL.
The collector connects without TLS.	The TLS certificate, over a Unix socket or with TLS off.

Where each check applies

Yes means evaluated. Self-managed covers both ways of running a database on EC2. The versions are those WatchTower15 supports: Amazon RDS for MySQL 5.7, 8.0 and 8.4; Aurora MySQL 2.x and 3.x; RDS for MariaDB 10.6 and 10.11; self-managed MySQL 5.7, 8.0 and 8.4 and MariaDB 10.11.

Check	RDS for MySQL	Aurora MySQL	RDS for MariaDB	Self-managed MySQL 5.7	Self-managed MySQL 8.0, 8.4	Self-managed MariaDB
Buffer pool is small for the data	Yes	Yes	Yes	Yes	Yes	Yes
Connections near max_connections	Yes	Yes	Yes	Yes	Yes	Yes
Many temporary tables go to disk	Yes	Yes	Yes	Yes	Yes	Yes
Redo log smaller than an hour of writes	Yes	Not applicable	Yes	Yes	Yes	Yes
Binary logs kept for less than a day	Managed by Amazon RDS	Managed by Amazon RDS	Managed by Amazon RDS	Yes	Yes	Yes
Durability relaxed	Yes	Not applicable	Yes	Yes	Yes	Yes
Table open cache too small	Yes	Yes	Yes	Yes	Yes	Yes
Table without a primary key	Yes¹	Yes¹	Yes	Yes	Yes¹	Yes
Redundant index	Yes¹	Yes¹	Yes	Yes	Yes¹	Yes
Unused index	With performance_schema¹	With performance_schema¹	With performance_schema	Yes	Yes¹	With performance_schema
Table near the row-version limit	8.0.29 and later, 8.4	3.x on MySQL 8.0.29 and later	Not applicable	Not applicable	8.0.29 and later, 8.4	Not applicable
Statement digest table full	With query analytics	With query analytics	With query analytics	With query analytics	With query analytics	With query analytics
The three workload checks	With query analytics	With query analytics	With query analytics	With query analytics	With query analytics	With query analytics
Unencrypted connections allowed	Yes	Yes	Yes	Yes	Yes	Yes
LOAD DATA LOCAL allowed	Yes	Yes	Yes	Yes	Yes	Yes
File import and export not restricted	Managed by Amazon RDS	Managed by Amazon RDS	Managed by Amazon RDS	Yes	Yes	Yes
Writable replica	Yes	Not applicable	Yes	Yes	Yes	Yes
Anonymous or any-host account	With the account grant	With the account grant	With the account grant; locking not visible	With the account grant	With the account grant	With the account grant; locking not visible
TLS certificate expiring	Yes	Yes	Yes	Over TCP with TLS	Over TCP with TLS	Over TCP with TLS

• ¹ Partitioned tables are not seen by the no-primary-key, redundant-index and unused-index checks on MySQL 8.0 and 8.4 — self-managed, Amazon RDS for MySQL and Aurora MySQL 3.x: see Schema checks.
• performance_schema is off by default on RDS, Aurora and MariaDB, and on by default on MySQL: see Turning on performance_schema.
• Query analytics needs performance_schema and tier B, and is not kept in Counts only mode: see Query analytics.

The optional account grant

The account check reads six columns of mysql.user with a grant of its own, which the grant script in Setup offers last: The last grant is optional: it lets the advisor check for anonymous accounts and accounts open to any host. It reads six columns of mysql.user, never a password hash; without it, that check is not evaluated. For MariaDB it adds: MariaDB keeps account locking beside the password hash, so a locked account is still reported: dismiss it with a reason. Run it, with the same user and host as the script’s other grants, only if you want that check.

MySQL 5.7, 8.0 and 8.4, Amazon RDS for MySQL and Aurora MySQL:

-- Optional: account checks (the advisor). Six columns of mysql.user, never a password hash.
-- Locked accounts are left out (MySQL 8 roles are locked accounts).
GRANT SELECT (User, Host, plugin, account_locked, password_expired, ssl_type) ON mysql.user TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';   -- optional: account checks

MariaDB, and Amazon RDS for MariaDB:

-- Optional: account checks (the advisor). Six columns of mysql.user, never a password hash.
-- MariaDB's mysql.user has no account_locked, so locking is not visible; roles (is_role) are left out.
GRANT SELECT (User, Host, plugin, password_expired, ssl_type, is_role) ON mysql.user TO 'wt15_monitor'@'<collectors-subnet>/<netmask>';   -- optional: account checks

• Never a password hash. Neither grant names authentication_string or MariaDB’s Password, and MySQL refuses a column the grant does not name (error 1143): with this grant, a password hash cannot be read.

• What the check reads is these six columns, once per pass:

  SELECT User, Host, plugin, account_locked, password_expired, ssl_type FROM mysql.user   -- MySQL
  SELECT User, Host, plugin, password_expired, ssl_type, is_role FROM mysql.user          -- MariaDB

  Only the flagged accounts leave your server.

• MariaDB: locking is not visible. MariaDB keeps an account’s lock beside its password hash, in mysql.global_priv, which WatchTower15 never reads. A locked any-host account is reported all the same, and its evidence says that locking is not visible: dismiss it with a reason.

• When it takes effect. The collector sees the grant within about 5 minutes, and the check runs at the next facts pass: press Run now for it at once.

• Without it, nothing about accounts is read, and the check is listed under Not evaluated with Run the optional account grant from the grant script. Revoking it later leaves the check’s findings as they were.

• Account names reach WatchTower15 only in Clear names mode, as keyed hashes otherwise. See GDPR & compliance and the Data Processing Agreement.

Names, keys and revealing a key

Tables, indexes, statements and accounts follow the database’s privacy mode:

Mode	The Advisor tab shows
Hashed (default)	Each table, index and account as its key, such as #3f9a1c…, and each statement as its key, as on the Queries tab.
Clear names	Their names, and each statement’s normalised text, as on the Queries tab. Names are kept in WatchTower15’s database only.
Counts only	One finding per check on tables, indexes or accounts, with a count — 237 tables without a primary key — never which. The checks on statements and the statement digest check are not evaluated.

• Switching from Hashed to Clear names: the names arrive with a new first pass, 10–15 minutes after the switch. Until then the findings show their keys.
• Switching away from Clear names deletes every stored name at once.
• Switching to Counts only deletes the findings about tables, indexes, statements and accounts, with their dismissals. The counts follow from a first pass 10–15 minutes later.
• Switching back from Counts only deletes the count findings. The findings about each object follow from a first pass 10–15 minutes later.
• Accept new key: The advisor’s findings about tables, indexes, statements and accounts are deleted and return, under the new keys, with the next facts pass: their dismissals are lost. Press Run now for that pass at once. The checks on the database itself keep their findings.

Revealing a key on your host

Click a key, then Reveal on the host, for the command to run on the database’s active collector, as root:

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

It prints the name behind the key on your terminal, and sends nothing to WatchTower15.

• A table key is found as on the Storage & growth tab: see Revealing a key on your host.
• An index key is looked up in InnoDB’s index statistics (tier C), or, without them, in InnoDB’s catalogue (PROCESS).
• An account key is looked up in mysql.user, which needs the optional account grant.
• A statement key is found as on the Queries 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, as they can be during a pass), try again in a minute.

Pauses, handovers and deleting

• Paused. No pass runs, the findings stay as they were last evaluated, and Run now is disabled. The tab keeps them under the paused banner; Resume is on the Collectors tab.
• A handover. The new active collector runs its first pass 10–15 minutes after it takes over, unless the database has facts less than 24 hours old. The findings are the database’s, not the collector’s: they carry on.
• Needs the agent update. A collector whose agent predates the advisor runs no pass. Agents update themselves within minutes of a release; the first pass runs 10–15 minutes after the updated agent restarts.
• Kept. WatchTower15 keeps the latest facts of each database, each open and dismissed finding for as long as it fires, and each resolved finding for 30 days.
• Deleting the database deletes its facts and findings, names included. See Pausing, deleting, billing.

The advisor adds nothing to a database’s price.

Configuration checks

Buffer pool is small for the data

Warning · one finding for the database

What it checks. Both at once: innodb_buffer_pool_size is less than half of the database’s InnoDB data — its Tables and General tablespaces, as Storage & growth measures them — and, over the last 7 days, fewer than 99 % of InnoDB’s page reads were served from the buffer pool: the hit ratio, 1 less the reads from disk divided by the read requests. The evidence shows the buffer pool’s size and its share of the data, and the 7-day hit ratio.

Why it matters. InnoDB reads every page through the buffer pool. When the pages your queries need do not fit in it, they are read from disk again and again: queries slow down, and the disk works harder for them.

How to fix it. Raise innodb_buffer_pool_size towards the size of the data your queries read often; on a server that runs only the database, 50–75 % of its memory is common. It can change while the server runs — SET PERSIST innodb_buffer_pool_size = … on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file on MySQL 5.7 and MariaDB. On RDS and Aurora, set it in the parameter group, or choose an instance class with more memory. The check clears at the next pass once the buffer pool holds half the data.

When to dismiss it. When most of the data is rarely read — an archive, a history table — and the hit ratio is good enough for your workload, or when the server’s memory serves other processes too.

Where it does not apply. It is evaluated on every provider and version. It waits for the database’s sizes and for a first day of metrics.

Connections near max_connections

Warning · one finding for the database

What it checks. The most connections open at once over the last 7 days, as the collector sampled them, reach 85 % of max_connections or more. The evidence shows that peak, its share of max_connections, and Max_used_connections: the most connections at once since the server started.

Why it matters. When every connection is taken, the server refuses the next client with Too many connections (error 1040) — usually at a traffic peak, when it matters most.

How to fix it. Raise max_connections (each connection takes memory, so check the server has it to spare; on RDS and Aurora, in the parameter group), or lower what the applications hold open: a connection pool sized to what they need, shorter idle timeouts (wait_timeout), connections closed after use. Raising max_connections clears the check at the next pass; a lower peak clears it once the 7 days no longer hold the old one.

When to dismiss it. When the peak is a fixed-size pool you sized on purpose just below max_connections.

Where it does not apply. It is evaluated on every provider and version. The Database connections high alarm watches the same thing live, at 80 % and 95 %.

Many temporary tables go to disk

Info · one finding for the database

What it checks. Over the last 7 days the database created 10,000 or more internal temporary tables — for sorts, GROUP BY, DISTINCT, UNION and derived tables — and 25 % or more of them on disk. The evidence shows both counts, the share on disk, and tmp_table_size and max_heap_table_size.

Why it matters. A temporary table on disk is far slower than one in memory, and adds disk I/O to every statement that needs one.

How to fix it. Look first at the statements that sort or group large results: on the Queries tab, a statement’s drawer shows its temporary tables and temporary disk tables. An index that serves the ORDER BY or GROUP BY often avoids the temporary table altogether; on MySQL 5.7 and MariaDB, a result with TEXT or BLOB columns always goes to disk, so leave them out where you can. Then, if memory allows, raise the in-memory limits: on MySQL 5.7 and MariaDB, tmp_table_size and max_heap_table_size together, since the smaller of the two applies; on MySQL 8.0 and 8.4, tmp_table_size caps each temporary table (from 8.0.28) and temptable_max_ram the memory they share. A fix clears the check as the 7-day window moves past the old figures.

When to dismiss it. When reports or batch jobs sort large results on purpose, on a server that cannot spare more memory.

Where it does not apply. It is evaluated on every provider and version. It waits for a first day of metrics.

Redo log smaller than an hour of writes

Info · one finding for the database

What it checks. The redo log holds less than InnoDB writes to it in one busy hour: the 95th percentile, over the last 7 days, of each hour’s redo. Its capacity is read per version:

• MySQL 8.0.30 and later, and 8.4: the capacity in effect, Innodb_redo_log_capacity_resized;
• MySQL 5.7, and 8.0 before 8.0.30: innodb_log_file_size × innodb_log_files_in_group;
• MariaDB: innodb_log_file_size, its one redo file.

The evidence shows the capacity and the busy hour’s redo.

Why it matters. InnoDB can reuse redo space only once the changes it records are written to the data files. A redo log that fills in less than an hour forces frequent, aggressive flushing, and writes stall while it waits.

How to fix it. Size the redo log to hold at least an hour of writes at the busiest time:

• MySQL 8.0.30 and later, and 8.4: innodb_redo_log_capacity, which can change while the server runs (SET PERSIST innodb_redo_log_capacity = …);
• MySQL 5.7, and 8.0 before 8.0.30: innodb_log_file_size (× innodb_log_files_in_group), in the configuration file, and a restart;
• MariaDB: innodb_log_file_size — on 10.11 with SET GLOBAL and the configuration file, on 10.6 with a restart.

On RDS, set it in the parameter group. A larger redo log takes disk space: check the volume has it. The check clears at the next pass.

When to dismiss it. When the busy hour is a rare batch whose flushing you accept, or the disk cannot spare space for a larger redo log.

Where it does not apply. Aurora MySQL: Aurora keeps the redo log in its storage layer. Everywhere else it is evaluated, on every version, once a first day of metrics exists.

Binary logs kept for less than a day

Info · one finding for the database

What it checks. The binary log is on, and binary logs are kept for more than 0 and less than 24 hours. The retention is read per version:

• MySQL 8.0 and 8.4: binlog_expire_logs_seconds when it is above 0, otherwise, on 8.0, expire_logs_days. From 8.0.29, with binlog_expire_logs_auto_purge OFF nothing is purged, which passes.
• MySQL 5.7: expire_logs_days, in whole days, so it never fires.
• MariaDB: binlog_expire_logs_seconds when it is above 0, otherwise expire_logs_days, which may be a fraction of a day.

A retention of 0 means binary logs are never purged: that passes too.

Why it matters. A replica that stops or falls behind for longer than the retention cannot catch up — its source has purged the logs it needs — and must be rebuilt. A point-in-time recovery needs the binary logs since the backup it starts from.

How to fix it. Keep them for at least a day: binlog_expire_logs_seconds = 86400 or more — SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file on MariaDB. Check the disk has room for a day of binary logs.

When to dismiss it. When no replica reads this server’s binary log and you never recover from it.

Where it does not apply. Managed by Amazon RDS on RDS for MySQL, Aurora MySQL and RDS for MariaDB, which keep binary logs for as long as mysql.rds_set_configuration says, for example CALL mysql.rds_set_configuration('binlog retention hours', 24);.

Durability relaxed

Info · one finding for the database

What it checks. innodb_flush_log_at_trx_commit is not 1, or the binary log is on and sync_binlog is not 1. The evidence shows both values.

Why it matters. With innodb_flush_log_at_trx_commit at 0 or 2, InnoDB writes its redo to disk about once a second instead of at each commit: a crash of MySQL (at 0) or of the server (at 2) can lose up to about a second of committed transactions. With sync_binlog other than 1, the binary log can lose transactions the data kept, so replicas and point-in-time recovery no longer match it.

How to fix it. Set both to 1. Both can change while the server runs: SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file otherwise, the parameter group on RDS. Each commit then waits for a disk write: on a busy server, watch the write latency afterwards.

When to dismiss it. When you relaxed them on purpose, for throughput, and losing the last second of commits in a crash is acceptable: a replica you can rebuild, a cache, a staging database.

Where it does not apply. Aurora MySQL: Aurora’s storage layer makes each commit durable. Everywhere else it is evaluated, on every version.

Table open cache too small

Info · one finding for the database

What it checks. Since the server started, with 24 hours of uptime or more, the table open cache overflowed (Table_open_cache_overflows above 0), and misses are 1 % or more of its lookups (Table_open_cache_misses against hits and misses together). The evidence shows table_open_cache, the hits, misses and overflows, and the uptime.

Why it matters. A miss opens the table again — its file and its definition — which costs time and, with many connections, contention. Overflows mean the cache was full and closed tables to make room.

How to fix it. Raise table_open_cache until Table_open_cache_overflows stops growing. It can change while the server runs (SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file otherwise; the parameter group on RDS). Open tables hold file descriptors, so open_files_limit may need raising too, with a restart. The counters run since the server started, so the check clears once the misses’ share falls, or after a restart.

When to dismiss it. When the server holds far more tables than it uses at once — thousands of per-customer tables, each read rarely — and the misses are expected.

Where it does not apply. It is evaluated on every provider and version, from 24 hours after the server started.

Schema checks

The checks on tables and indexes read InnoDB’s catalogue, so they cover InnoDB tables, each partitioned table counted once.

Partitioned tables on MySQL 8.0 and 8.4. On MySQL 8.0 and 8.4, information_schema.INNODB_INDEXES lists no index of a partitioned table, so the Advisor cannot report a partitioned table as having no primary key, or any of its indexes as redundant or unused, on those versions (MySQL 5.7 and MariaDB are not affected, and row versions are reported on every version). This includes Amazon RDS for MySQL 8.0 and 8.4 and Aurora MySQL 3.x.

Table without a primary key

Warning · one finding per table

What it checks. InnoDB tables that InnoDB clusters on its hidden row id (GEN_CLUST_INDEX): tables with no primary key and no unique index on NOT NULL columns. A table without a primary key but with such a unique index is clustered on it, and is not reported. A partitioned table counts once, on MySQL 5.7 and MariaDB only (see partitioned tables on MySQL 8.0 and 8.4).

Why it matters. Without a primary key, InnoDB gives each row a hidden row id, drawn from one counter that every such table on the server shares. With row-based replication, a replica applies each change by finding its row — without a key, by scanning the table for each changed row, so one large UPDATE can hold a replica back for hours. Group Replication refuses such tables, and online schema-change tools need a key.

How to fix it. Add a primary key: an existing unique column, or a new one, for example:

ALTER TABLE `shop`.`order_audit` ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

On MySQL 8.0.23 and later, add INVISIBLE after AUTO_INCREMENT so that SELECT * does not return the new column. The ALTER rebuilds the table: run it at a quiet time, or with an online schema-change tool. On MySQL 8.0.30 and later, sql_generate_invisible_primary_key = ON gives every new table one. The check clears at the next pass.

When to dismiss it. A small table always read whole, or a staging table one job fills and empties, where neither replication nor a key matters.

Where it does not apply. It is evaluated on every provider and version, but on MySQL 8.0 and 8.4, RDS and Aurora MySQL 3.x included, it cannot see a partitioned table (see above). It needs PROCESS (tier A).

Redundant index

Info · one finding per index

What it checks. A secondary, non-unique B-tree index whose columns are the first columns of another B-tree index on the same table — the primary key, a unique index or another secondary index: (customer_id) beside (customer_id, created_at), for example. Of two identical indexes, the one created later is reported. Never reported: the primary key, unique indexes, full-text and spatial indexes, and an index with a functional part. The evidence names the index that covers it and its kind: primary, unique or secondary.

InnoDB’s catalogue shows neither an index’s prefix lengths (name(10)) nor its sort order (DESC): check both before you drop it.

Why it matters. Every index is updated on every write to its columns, and takes disk and buffer pool memory. One whose columns another index already starts with rarely serves a query the other cannot.

How to fix it. Check with SHOW CREATE TABLE that the two indexes match in prefix lengths and order, and that no statement names the index in a hint (FORCE INDEX, USE INDEX): such a statement fails once the index is gone. To try it first, hide the index from the optimiser — ALTER TABLE … ALTER INDEX … INVISIBLE on MySQL 8.0 and later, ALTER TABLE … ALTER INDEX … IGNORED on MariaDB 10.6 and later — then drop it:

ALTER TABLE `shop`.`orders` DROP INDEX `idx_customer`;

The check clears at the next pass.

When to dismiss it. When the shorter index is kept on purpose — it is smaller, so a busy query that needs only its columns reads less — or when the two differ in prefix length or order.

Where it does not apply. It is evaluated on every provider and version, but on MySQL 8.0 and 8.4, RDS and Aurora MySQL 3.x included, it cannot see a partitioned table’s indexes (see above). It needs PROCESS (tier A).

Unused index

Info · one finding per index

What it checks. A secondary, non-unique index that no statement has used to find rows since the server started, by performance_schema’s index-usage counts, on a server that has run for 7 days or more. The evidence shows the index’s size (with tier C) and the uptime. The primary key and unique indexes are never reported: they enforce a rule even when no query reads them.

These counts are this server’s only. A replica keeps its own, and a restart sets them back to zero.

Why it matters. As for a redundant index: it slows every write and takes disk and memory, for no query.

How to fix it. Check first that nothing else needs it: a replica (its reads are not counted here), a job that runs monthly or yearly, a failover. Then hide it from the optimiser — INVISIBLE on MySQL 8.0 and later, IGNORED on MariaDB 10.6 and later — for a full cycle of your jobs, and drop it. The check clears at the next pass.

When to dismiss it. When a rare job, a replica or a failover uses it: Used by the month-end report, for example.

Where it does not apply. It needs performance_schema on — off by default on RDS, Aurora and MariaDB — and tier B. It waits until the server has run for 7 days, and is not evaluated while table I/O is not instrumented, when every index would look unused. It is evaluated on every provider and version where those hold, but on MySQL 8.0 and 8.4, RDS and Aurora MySQL 3.x included, it cannot see a partitioned table’s indexes (see above).

Table near the row-version limit

Warning · one finding per table

What it checks. Tables whose TOTAL_ROW_VERSIONS (in information_schema.INNODB_TABLES) is 50 or more. From MySQL 8.0.29, each instant ADD COLUMN or DROP COLUMN adds a row version, and a table holds at most 64. The evidence shows the table’s count.

Why it matters. At 64, the next ADD COLUMN or DROP COLUMN cannot be instant: it rebuilds the whole table — or, with ALGORITHM=INSTANT, is refused. On a large table that is hours of I/O, at the moment you expected none.

How to fix it. Rebuild the table at a quiet time, which sets its row versions back to 0:

ALTER TABLE `shop`.`carts` FORCE;   -- or OPTIMIZE TABLE `shop`.`carts`;

Reads and writes carry on while it runs, but it reads and writes the whole table. The check clears at the next pass.

When to dismiss it. When the table no longer gets instant column changes.

Where it does not apply. MySQL 5.7, MySQL 8.0 before 8.0.29, Aurora MySQL 2.x, Aurora MySQL 3.x on a MySQL version before 8.0.29, and MariaDB, which keeps no row-version count. It is evaluated on MySQL 8.0.29 and later and 8.4 — self-managed, RDS for MySQL and Aurora MySQL alike — and needs PROCESS (tier A).

Statement digest table full

Info · one finding for the database

What it checks. In the last 24 hours, statement runs went untracked because performance_schema’s statement digest table was full — the runs the Queries tab counts as not tracked — or MySQL reported digests lost. The evidence shows the runs not tracked and performance_schema_digests_size.

Why it matters. Once the table is full, MySQL counts every new statement together in one row: the Queries tab cannot tell them apart, and the workload checks cannot judge them.

How to fix it. Raise performance_schema_digests_size, up to 20,000: the most WatchTower15 reads from one database. It needs a restart — on RDS and Aurora, the parameter group and a reboot. Above 20,000 the statement reads stop: see The limits. The check clears at the first pass after a whole day without untracked runs.

When to dismiss it. When the untracked runs are few and a restart is not worth it.

Where it does not apply. It is evaluated wherever query analytics is collected, on every provider and version: not in Counts only mode, nor while query analytics is not available.

Workload checks

These three checks judge the statements query analytics collects, over the last 7 days:

• Only the statements it sends: those that lead a 5-minute interval, counted for the intervals in which they were sent (see How the list adds up). A statement that never leads is never judged, and each finding’s evidence says so.
• The 5,000 costliest at most, by total time. A statement beyond them is neither reported nor resolved.
• Not in Counts only mode, nor while query analytics is not available.

Each finding is about one statement: its normalised text in Clear names mode, otherwise its key, as on the Queries tab. Each check fires for a statement for as long as its 7 days hold what it looks for, so a fix clears it as the window moves on.

Statement runs without an index

Info · one finding per statement

What it checks. Over the last 7 days the statement ran 1,000 times or more, and used no index in half of its runs or more: MySQL’s count of runs that read a table in full. The evidence shows its calls, its runs without an index and their share.

Why it matters. A statement that scans a table reads every row of it each time: it slows as the table grows, and takes CPU and I/O from everything else.

How to fix it. Run EXPLAIN on it: the table it scans shows type: ALL. Add, or extend, the index its WHERE, JOIN or ORDER BY needs.

When to dismiss it. When the table is small — reading a few hundred rows in full can be cheaper than an index — or the statement must read every row, as an export does.

Where it does not apply. It is evaluated wherever query analytics is collected, on every provider and version.

Statement examines far more rows than it returns

Info · one finding per statement

What it checks. Over the last 7 days the statement examined 1,000 rows or more for each row it returned (having returned at least one), and took 5 % or more of the time all statements spent on the database. The evidence shows rows examined and sent, rows examined per row sent, and its share of the time.

Why it matters. Rows examined are read, and mostly thrown away: the statement does far more work than its result needs, and takes a large share of the database’s time doing it.

How to fix it. Look at its plan with EXPLAIN. A more selective index — one whose first columns match the WHERE’s equalities — or a query that filters earlier usually fixes it.

When to dismiss it. An aggregate that must read many rows to return a few — a report summing a month — when its cost is accepted, or served better by a summary table of your own.

Where it does not apply. It is evaluated wherever query analytics is collected, on every provider and version.

Statement joins without an index

Info · one finding per statement

What it checks. Over the last 7 days, 100 or more of the statement’s runs joined a table without an index — MySQL’s full-join count — and they make up 1 % of its runs or more. The evidence shows the full joins and their share of the runs.

Why it matters. Without an index on the join column, MySQL reads the whole joined table for each row, or batch of rows, of the other: the cost multiplies with both tables’ sizes.

How to fix it. Run EXPLAIN on it: the table it scans shows type: ALL, often with Using join buffer. Index that table’s join column.

When to dismiss it. When the joined table holds a handful of rows.

Where it does not apply. It is evaluated wherever query analytics is collected, on every provider and version.

Hardening checks

Unencrypted connections allowed

Info · one finding for the database

What it checks. require_secure_transport is OFF, so a client may connect over TCP without TLS unless its own account requires it.

Why it matters. Without TLS, statements and their results cross the network in clear, for anyone on the path to read.

How to fix it. Once every client connects with TLS, set require_secure_transport = ON: SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file on MySQL 5.7 and MariaDB, the parameter group on RDS and Aurora. Connections over a Unix socket are not affected. WatchTower15’s own monitoring user already requires TLS over TCP. The check clears at the next pass.

When to dismiss it. When every account that connects over the network already requires TLS (REQUIRE SSL), or every client connects with it. RDS’s and Aurora’s default parameter groups leave require_secure_transport OFF, so most RDS databases show this finding.

Where it does not apply. It is evaluated on every provider and version, as Info on RDS and Aurora too.

LOAD DATA LOCAL allowed

Warning · one finding for the database

What it checks. local_infile is ON, so clients may use LOAD DATA LOCAL INFILE. It is ON by default on MySQL 5.7 and MariaDB, and OFF on MySQL 8.0 and later.

Why it matters. LOAD DATA LOCAL INFILE makes a client send a file from its own machine to the server — and the server names the file. A client connected to a compromised or impersonated server, or an application open to SQL injection, can be made to send any file it can read.

How to fix it. Set local_infile = OFF: SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file on MySQL 5.7 and MariaDB, the parameter group on RDS and Aurora. The check clears at the next pass.

When to dismiss it. When an application loads files this way, from clients you control.

Where it does not apply. It is evaluated on every provider and version.

File import and export not restricted

Warning · one finding for the database

What it checks. secure_file_priv is empty, so LOAD DATA INFILE, SELECT … INTO OUTFILE and LOAD_FILE() can read and write files anywhere the server’s own user can. Set to a directory, they are limited to it; set to NULL, they are disabled: neither fires. It is empty by default on MariaDB and on some MySQL builds.

Why it matters. An account with the FILE privilege — or a statement injected through one — can then read the server’s files, its configuration and keys included, and write files wherever the server can.

How to fix it. Set secure_file_priv to a dedicated directory, or to NULL. It cannot change while the server runs: set it under [mysqld] in the configuration file, and restart. Revoke FILE from every account that does not need it. The check clears at the pass after the restart.

When to dismiss it. When only administrators you trust hold FILE.

Where it does not apply. Managed by Amazon RDS on RDS for MySQL, Aurora MySQL and RDS for MariaDB: RDS sets it, and grants FILE to no one. It is evaluated on every self-managed version.

Writable replica

Warning · one finding for the database

What it checks. The collector saw replication running on the server — a replication channel with both its threads running — and read_only is OFF. A server that is not a replica passes.

Why it matters. Any account that can write can then change the replica’s data by mistake: it no longer matches its source, and replication can stop on a duplicate-key or missing-row error.

How to fix it. Set read_only = ON on the replica, and on MySQL super_read_only = ON too, which also holds back administrators. Replication itself still applies the source’s changes. Use SET PERSIST on MySQL 8.0 and 8.4, SET GLOBAL and the configuration file on MySQL 5.7 and MariaDB, and the replica’s parameter group on RDS. MariaDB has no super_read_only. The check clears at the next pass.

When to dismiss it. When the replica is writable on purpose — it keeps reporting tables of its own, for example.

Where it does not apply. Aurora MySQL, whose readers are read-only by design. Everywhere else it is evaluated, on every version.

Anonymous or any-host account

Warning for an anonymous account, Info for an any-host account · one finding per account

What it checks. With the optional account grant: an anonymous account, with an empty user name (Warning), or an account whose host is %, which can connect from any host (Info), that is not locked. Only the host % itself counts: 10.0.1.% or %.example.com do not. On MySQL, locked accounts — roles among them — are never reported. On MariaDB locking is not visible, so a locked account is reported too, and its evidence says that locking is not visible. The evidence also shows the account’s authentication plugin, its TLS requirement and whether its password has expired.

Why it matters. An anonymous account lets anyone who can reach the server connect without a user name. An any-host account can be tried from every address the network lets through, so its password is all that protects it.

How to fix it. Drop an anonymous account; limit an any-host account to the hosts that need it, or lock it if it is not used:

DROP USER ''@'localhost';                    -- an anonymous account
RENAME USER 'app'@'%' TO 'app'@'10.0.1.%';   -- keeps its grants
ALTER USER 'app'@'%' ACCOUNT LOCK;           -- an account that is not used

The check clears at the next pass.

When to dismiss it. When the network already limits who can reach the server, such as a security group, and the account requires TLS. On RDS and Aurora, the master user is created with host %: dismiss its finding with a reason, such as RDS master user, reachable only through the database’s security group. On MariaDB, dismiss an account you know is locked.

Where it does not apply. It is evaluated wherever the optional account grant is held, on every provider and version, RDS and Aurora included. On MariaDB, self-managed or on RDS, locking is not visible.

TLS certificate expiring

Warning under 30 days, Critical under 7 · one finding for the database

What it checks. The earlier of two dates is less than 30 days away (Warning) or less than 7 (Critical): the expiry of the server certificate, and — when the collector verifies the certificate — the earliest expiry of the certificate authorities in the chain it verified. The dates are read at each facts pass’s own TLS handshake, so a renewed certificate clears the finding at the next pass, or at once with Run now. The evidence shows both dates, which one expires first, the days left, and when they were read.

Why it matters. After that date, every client that verifies the certificate refuses to connect — the collector included, and the database turns Failing with tls_failed. A certificate authority that expires before the certificate it signed, as in a rotation, breaks verification the same way.

How to fix it. Renew the server certificate, or its certificate authority, before the date, and load it: ALTER INSTANCE RELOAD TLS on MySQL 8.0.16 and later, FLUSH SSL on MariaDB 10.4 and later, a restart on MySQL 5.7. On RDS and Aurora, move the instance or cluster to a current certificate authority, and check that your clients trust it. Then press Run now.

When to dismiss it. Rarely: for a server you will retire before the date.

Where it does not apply. A database the collector reaches over a Unix socket, or without TLS. When the collector encrypts without verifying the certificate, only the server certificate’s date is read. It is evaluated everywhere else, on every provider and version.

Permissions

database.view reads the tab, its findings and the Info tab’s L6 row. database.edit runs Run now, Dismiss and Restore. Revealing a key needs root on the collector, not a WatchTower15 permission. See Roles & permissions.