Skip to content

Slow and error logs

The Logs tab of every MySQL and MariaDB database shows its slow query log, the statements slower than long_query_time, and its error log, as events you can filter, group and chart, with an alarm on errors. On a database you run yourself, the agent on the server the database runs on reads the log files. On Amazon RDS and Aurora, the database’s active collector reads them through the RDS API, with its own instance role. Each record is parsed on that server, and only keys, numbers and codes leave it. Read Database monitoring first for collectors, grants and privacy modes.

The tab sits after Advisor and before Alarms. It shows one log at a time: Slow queries first, or Error log. An address ending in /logs?log=error opens the error log.

Part Shows
Window The database page’s time range on a compact picker: 1h, 3h, 12h, 1d, 5d or Custom. The Overview, Queries and Incidents tabs share it, so changing it on one changes it on the others. While the range is relative, the tab reloads every minute.
The two counts At the head of the tab, over the window: Slow statements logged and Error-log errors, with Over and the window under them. They come from the readers’ counts, in every privacy mode.
The reader’s state When a log is not being read, a notice above its chart names the state and its fix, with a link to Info → Logs: see When a log is not read.
The histogram Slow statements logged, or Error-log lines by severity (Error, Warning, Note and system, stacked), per bucket: a minute for a window up to 6 hours, 5 minutes up to a day, an hour up to a week, 3 hours beyond. Summarised statements count too.
The lines The window’s slow statements or error-log records, newest first, 100 at a time, with Older lines for the next 100. Never in Counts only mode.

The two counts explain themselves under their value:

  • Slow statements logged: Statements slower than long_query_time, from the slow log while it is on. The Overview’s Slow queries (/s) chart is MySQL’s Slow_queries counter, which counts them whether the log is on or not.
  • Error-log errors: [ERROR] lines and crashes in the error log over the window. Warnings, notes and system lines are listed in the Error log below.

Log lines are kept for 30 days. A window that starts earlier is shown from 30 days back, with Log lines are kept for 30 days: this tab shows them from <date>; a window that ends before then shows No log lines in this window. Times are shown in your display timezone, which the Date & Time column names.

The filters are Minimum query time (s), Schema and Statement (the schemas and statements met in the window’s lines), and Events or By statement. They are remembered per database, for you, in this organisation; the window is not one of them.

View Columns
Events Date & Time, Query time, Lock time, Rows examined, Rows sent, Statement, Schema, Account. A slow statement is dated when it started, as MySQL records it.
Summarised statements, under the events A storm’s or the daily budget’s per-statement summaries: Minute, Statement, Events, Total time, Max, Rows examined, Schema and Why (Storm or Daily budget).
By statement The window’s statements, events and summaries added up, the most total time first, up to 100: Statement, Events, Total time, Max, Rows examined, Schema.

The Statement cell shows:

  • in Clear names mode, the statement’s normalised text, cut to one line with all of it on hover, and Truncated when it was cut at 4,096 bytes;
  • otherwise its statement key, such as #4e1f0a…, with Text needs clear names;
  • Queries statement, a link to the same statement on the Queries tab, or No Queries statement, whose hover says why: see Linking to the Queries tab;
  • Admin command: and its name (Quit, Ping, Prepare, Close stmt, Reset stmt, Change user, Binlog Dump, or other) for an administrator command the slow log recorded;
  • Other statements for a minute’s summary of the statements beyond its first 20.

The Schema cell shows the schema’s name in Clear names mode where the Storage & growth tab knows it, its key otherwise, and — for a statement with no default schema. The Account cell shows the database user that ran the statement in Clear names mode, its key otherwise.

With no slow statement in the window, the list says No slow statements in this window. and The slow log records statements slower than long_query_time (1 s on this database).

The filters are Severity (Error, Warning, Note, System) and Code: a MySQL code such as MY-010584, or crash.

Column Shows
Date & Time The time written on the line.
Severity Error, Warning, Note or System. A crash is an Error.
Code MySQL 8.0 and 8.4’s code, linking to MySQL’s server error reference for the database’s version (8.0’s, or 8.4’s for 8.4 and later); crash for the crash banner (mysqld got signal <N>); Other codes on a count line. MySQL 5.7 and MariaDB write no code: —.
Subsystem MySQL 8.0 and 8.4’s subsystem, such as InnoDB, Repl or Server; Server for a crash.
Message In Clear names mode, Message (scrubbed): the first line’s message, its values replaced with ?. Otherwise its message key, with Text needs clear names. Under it, +N continuation lines, not sent when the record went on over more lines.

A storm’s or the budget’s count line reads, for example, 37 more lines counted, not listed: more than 300 error-log lines in that minute.

The same events can be read on the Logs page, beside your servers’ and log sources’ lines: choose the database, or All databases, under Databases in Sources. See Databases as a source.

  • The same rows, worded the same way: slow statements, summaries, error-log records and count lines, with the statement’s text, the schema’s and the account’s names and the scrubbed message in Clear names mode, and their keys in Hashed mode. Queries statement, a code’s link and Reveal on the host work there as they do here, on the row’s own database.
  • Levels. An error-log record takes the Logs page’s level from its severity: Error is error (a crash too), Warning warning, Note info and System notice. A slow statement or a summary has no level and shows slow, so any level filter leaves it out. The Database log filter picks Slow queries, Error log or both.
  • Counts only. No event is sent, so there is nothing to list: Sources marks such a database Counts only, and choosing it says that it keeps counts only, and that its counts are charted on this tab.
  • Search, the stream filter and the live tail. The log store holds keys, numbers and codes, never a text, and an event has no stream, so a search or a stream filter leaves the database events out, with a note saying so. The Live tail is off while a database is chosen: events arrive once a minute, dated by the database’s own clock.
  • Clicking a row opens this tab on its log, with the Logs page’s time window as the database page’s time range.
  • Its histogram can differ from this tab’s. The Logs page counts the events stored in the log store, a summary or count line as the events it stands for. This tab’s histograms and counts are the readers’ counts, which also count what was not stored: a record dropped rather than risk sending a value, or an event the log store did not take.
  • Late events. An event reaches the log store up to a minute after its time, and on RDS and Aurora an error line up to about 6 minutes after. A list already scrolled past that time shows it on the next load.
  • Both permissions. The Logs page needs logs.view, and its Databases group database.view too. database.view alone reads this tab.
  • Never log volume. Wherever you read them, these events are included in the database’s price, and never count toward your log usage or limits.
Where the database runs Read by From
Self-managed (on EC2) The agent on the server the database runs on, its host server The files the database names in slow_query_log_file and log_error, or, when log_error is stderr, the journal of the database’s systemd unit
RDS and Aurora The database’s active collector The instance’s slowquery/ and error/ log files, through rds:DescribeDBLogFiles and rds:DownloadDBLogFilePortion

WatchTower15 never changes a log setting, and nothing is exported to CloudWatch Logs. The paths come only from what the database reports: the agent reads only those files, and only a regular file inside the directory the database named; it never searches the server for logs.

Reading starts at the end of each log, the first time a reader sees it: lines written before then are not shown, and cannot open an incident. Each log is read every minute, from where the last read WatchTower15 accepted stopped, so a restart of the agent loses nothing: at most 4 MB of a file a minute, and on RDS and Aurora at most five 1 MB pages a minute across both logs. A log that has not been read to its end for an hour, or that has more than 64 MB unread, is skipped to its end: those lines are not sent.

On a self-managed database, the logs are read by the host server, whatever its role in the pool:

  1. The database names its host server: setup step 2, Which server runs this database? On a socket or loopback it is required anyway. Over the private network it is optional for collection, but the logs need it.
  2. The host server is in the database’s collector pool (step 3): it reads with the same credential source and redaction key as the collectors, so its keys are theirs.
  3. Its probe has passed: it connects with the database’s key, as a collector must.
  4. Its agent reads logs: it updates itself within minutes of a release.

The host does not need to be the active collector. A failover to another collector does not move the reading: the logs stay with the host. On MySQL 8.0 and 8.4 with tier B, a host that is not the active collector opens a short connection of its own to key its statements: one of the monitoring user’s three connections, beside the active collector’s and the standby’s probe.

A database in a container is supported when its log directory is mounted at the same path on the host: the database reports its paths as the container sees them, and the agent reads that path on the host. For example, with the database’s logs in /var/log/mysql:

Terminal window
docker run … -v /var/log/mysql:/var/log/mysql …
  • Set log_error to a file in that directory. A container’s standard error (what docker logs shows) is not read.
  • Run the container in UTC, or in the host’s time zone. MariaDB writes its error log’s times with no zone. The agent reads such a time as UTC when the database reports a system_time_zone of UTC (or GMT, unless the host’s own zone uses GMT), and otherwise in the host’s time zone. In any other zone, the times are off by the difference, and a line that seems more than 5 minutes ahead is dropped. MySQL writes its times with their zone.

A path the host cannot read shows File not on this host, with the path.

The error log is on everywhere. The slow log is off by default, on MySQL and MariaDB alike, and on RDS and Aurora. Turning it on is your choice: WatchTower15 never changes it. The Info tab’s Logs card shows the values the database reports.

MySQL 8.0 and 8.4, as an administrator:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;

MySQL 5.7 and MariaDB, as an administrator, and in the server’s configuration, so a restart keeps it:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
[mysqld]
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_output = FILE
  • log_output must include FILE, its default (FILE,TABLE works too). TABLE alone writes the statements to mysql.slow_log, raw, with their values, and WatchTower15 never reads that table.
  • On MariaDB, log_slow_query, log_slow_query_file and log_slow_query_time are the same settings under their MariaDB names; either name works.
  • A changed long_query_time applies to new connections. A pool’s open connections keep the threshold they started with until they reconnect.

The default parameter groups cannot be edited, and their log_output is TABLE, so the files WatchTower15 reads are not written. So:

  1. Create a DB parameter group for the instance’s engine and version, or use one of your own.
  2. Set slow_query_log to 1, log_output to FILE and long_query_time (for example 1 or 2) in it.
  3. Attach it to the instance. On Aurora, attach it to every instance of the cluster, so the log stays on after a failover.
  4. Reboot the instance. A newly attached group takes effect at the reboot. In a group the instance already uses, the three parameters apply without one.

The error log needs nothing: RDS writes it on every instance.

  • Start at 1 or 2 seconds. Every statement slower than it is written to the file, so a lower value costs your database more I/O and gives you more events. 0 writes every statement: don’t.
  • The Overview’s Slow queries (/s) chart and the Database slow queries high alarm count MySQL’s Slow_queries, against the same threshold. Lowering it raises them too.
  • log_queries_not_using_indexes writes statements that use no index however fast they run; leave it off unless that is what you want to see.
  • A burst of slow statements is summarised beyond 600 a minute, so a low threshold never floods the tab.
  • Severities. WatchTower15 reads Error, Warning, Note and System lines. MySQL 8.0 and 8.4 write errors and warnings by default (log_error_verbosity = 2); notes need 3.
  • log_error names a file, or stderr. On a self-managed database logging to standard error, the host server reads the journal of the database’s systemd unit (mysql, mysqld or mariadb); with no such unit, set log_error to a file.
  • log_error_services (MySQL 8.0 and 8.4) must name log_sink_internal, as its default does. WatchTower15 reads the traditional lines that sink writes; a JSON or system-log sink alone is not read.
  • The record’s other lines — an InnoDB dump, a stack trace, a crash report — are counted on the record (+N continuation lines, not sent) and never sent.

The active collector reads the log files with its own instance role, so that role needs two more read actions: the second statement of the read policy, WT15DatabaseLogsRead. WatchTower15 is never granted anything.

- Sid: WT15DatabaseLogsRead
Effect: Allow
Action:
- rds:DescribeDBLogFiles
- rds:DownloadDBLogFilePortion
Resource: "arn:aws:rds:*:*:db:*"
  • A role that carries only the policy’s first statement is refused these two calls: both logs then read Permission denied. Attach the full policy to the role again; its name, wt15-cloud-metrics-read, is the same. The Info tab’s Logs card shows the policy beside a denied log, with Copy.
  • The logs are read from the instance cloud metrics match, so the policy’s first statement is needed too. Until the database is matched, the logs read Cloud layer unavailable, with the cloud layer’s reason and fix.
  • Both actions act on DB instances only. You may narrow the resource to your instances’ ARNs.

Error lines arrive up to about 6 minutes late

Section titled “Error lines arrive up to about 6 minutes late”

RDS writes the error log to its file every 5 minutes, and the collector reads every minute, so error lines, and the error-log alarm, arrive up to about 6 minutes after they are written. The Info tab’s Logs card says so on every RDS and Aurora database.

Every minute, for each database, the active collector makes:

  • two DescribeDBLogFiles calls, one for slowquery/ and one for error/;
  • DownloadDBLogFilePortion only for a log whose file changed since its last read, in pages of up to 1 MB, at most 5 a minute across both logs.

An idle database makes two calls a minute, 2,880 a day. The calls are free. Each is a read management event in your CloudTrail. A refused call is retried every hour and on Test again, never every minute. Only the active collector calls: a standby or a candidate never reads the logs.

The collector’s agent log says what it called, once an hour per database:

database <uuid>: RDS log calls in the last hour: DescribeDBLogFiles <n>, DownloadDBLogFilePortion <n>, throttled <n>

Each log follows one current file: the one whose name ends in .log, and on the error log of RDS for MySQL mysql-error-running.log. When RDS rotates a log, the rest of the old file is read where the API allows it, then the new file from its start. A cluster writer endpoint follows its writer: after a failover, the new writer’s files are read from their end. After a change of active collector, the new one carries on from where the last one stopped.

The logs follow the database’s privacy mode:

Mode Slow queries Error log Kept in WatchTower15’s database
Hashed (default) Statement, schema and account keys; the numbers Time, severity, code, subsystem and message key Nothing
Clear names The same, with the statement’s normalised text, the schema’s name and the account’s user name The same, with the scrubbed message The statement text, the scrubbed message and the user name
Counts only The histogram and the count only The histogram and the count only Nothing
  • In every mode, the log store holds keys, numbers and codes only. The texts of Clear names mode are kept in WatchTower15’s database, never in the log store, and each is sent once a day per key.
  • Switching away from Clear names deletes the stored messages and user names at once, and the statement texts as the Queries tab does.
  • In Counts only mode no event is sent. The readers still report every minute, so the Logs card’s states hold, and the tab shows the histograms and the counts with Counts only. This database’s privacy mode sends no log event, so there is no list to show. Lines recorded before a switch to Counts only are not listed while it holds, and are listed again after a switch back, until they expire.
  • The account’s user name is the database user the slow log names first in # User@Host. The client’s host and address never leave your server.

Each slow statement is identified by a statement key: a keyed hash, made with the database’s redaction key on your server, as on the Queries tab. Two kinds of key exist:

Key Made from Used on
MySQL’s MySQL’s own statement digest, keyed exactly as the Queries tab keys it MySQL 8.0 and 8.4, with tier B
The agent’s The agent’s normalised text of the statement MySQL 5.7, MariaDB, a database without tier B, a slow-log record holding several statements, and a statement MySQL cannot digest

How MySQL’s key is made. The agent writes the statement with each literal replaced by a fixed value of its type ('' for a string, 0 for a number, DATE '1970-01-01' for a date) and asks your database for its digest, SELECT STATEMENT_DIGEST(?), STATEMENT_DIGEST_TEXT(?), with that text as a bound parameter: it is never run, and no value of yours reaches the function. The answers are kept on the agent for an hour, so a statement seen again costs no call, and there are at most 200 calls a minute for each database. MySQL’s digest never leaves your server, nor any other unkeyed hash of a statement.

A statement with MySQL’s key has the same key as the statement on the Queries tab, so Queries statement opens that statement’s details there, with its figures where query analytics has them. A statement with the agent’s key has no Queries statement: the key is of the agent’s own text, not of MySQL’s digest, so no Queries statement carries it. Its cell says No Queries statement, and its hover says why.

MySQL writes use <schema>; into the slow log only when a statement’s default schema differs from the last one written to the file, by any session. So the log cannot tell a statement run with no default database from one run in the previous record’s schema: such a statement is shown in that schema, and its Queries statement link, if it has one, may find no statement (the Queries tab records it with no default schema). A restart or FLUSH SLOW LOGS starts the file again with no schema. MariaDB names the schema on every record, so it is not affected.

Click a schema’s key, or a statement’s MySQL key, then Reveal on the host, for the command to run as root on one of the database’s collectors. A row in the Logs explorer gives the command for that row’s database:

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

It works as on the Queries tab: see Revealing a key on your host. It sends nothing to WatchTower15. A statement with the agent’s key, an account key and a message key have no host command: Clear names mode shows their text.

Statement, schema, account and message keys are all made with the redaction key, so Accept new key gives every one a new key. Grouping By statement, and the Schema and Statement filters, split at the switch: lines recorded before it keep their old keys until they expire. In Clear names mode the texts are sent again for the new keys, and the old keys’ texts are removed 30 days after they were last seen.

Limit Slow log Error log
Each minute, sent one by one 600 statements 300 lines
The rest of that minute Summarised per statement: events, total and longest query time, rows examined; at most 20 statements and Other statements Counted by severity and code; at most 10 codes and Other codes
Each UTC day, sent one by one 20,000 statements 5,000 lines
Past the day’s budget, until midnight UTC Per-statement summaries only Counts only

The tab says when it shows either:

  • 4,210 more events summarised at 09:42: more than 600 slow statements in that minute, so the rest are counted per statement below.
  • Daily budget reached: this database logged 20,000 slow statements in one UTC day, so from <time> until midnight UTC the rest are kept as per-statement summaries only.

Summarised and counted events still count in the histograms, the two counts and the alarm.

What is not sent, and counted. A record the reader cannot normalise safely, such as a statement cut inside a value, is dropped whole rather than risk sending a value; so is a header line it does not know, such as MariaDB’s # explain: lines. Both are counted on the database as dropped, and never sent.

Every database gets Database error-log errors, one of its default alarms:

Fires when Default
The error log records an error, an [ERROR] line or a crash, in the last 5 minutes warning
  • Warnings and notes never fire it, nor System lines. A restart is Database restarted’s.
  • Only lines written after the log is first read can open an incident: reading starts at the end of the file.
  • On RDS and Aurora it is up to about 6 minutes late, as the error lines are.
  • While the error log is not read, it is not evaluated: its tile reads No data, reported only while the error log is read (Info → Logs). Otherwise it reads No errors or Errors logged.
  • ⚠️ An incident already open stays open while its reader is stopped — the host not eligible, the file not on the host, an agent update needed — and resolves once the error log is read again and 5 minutes pass with no error.

Tune it like any alarm on the database’s Alarms tab, where it sits under Logs. See Tuning an alarm.

You can also ship a database’s log files as server log files, on the Logs page. The two are separate, and can run side by side:

Slow and error logs (this page) Log files shipped by the agent
Where The database’s Logs tab, and Logs with the database chosen as a source Logs
What Parsed records: keys, numbers and codes, values removed on your server The raw lines, values included
Time The time written in the log The time the agent read each new line
Price Included in the database’s price; never counts toward your log usage or limits Billed per GB as log volume, and counts toward your ingestion limit
Alarm Database error-log errors None on the lines’ content

Database log events expire from the live store 30 days after they are recorded, and from its daily backups within a further 30 days.

What Where Deleted
Log events: keys, numbers and codes, never a value WatchTower15’s log store, apart from your logs 30 days after they were recorded, as above: not earlier when you delete the database or switch its privacy mode
Scrubbed messages and user names, Clear names only WatchTower15’s database only With the database, when you switch to another privacy mode, or 30 days after their key was last seen; at most 5,000 of each are kept per database, the least recently seen going first
Statement text, Clear names only WatchTower15’s database, shared with the Queries tab As on the Queries tab
Where each log was read to, and the day’s counts WatchTower15’s database With the database

See GDPR & compliance and the Data Processing Agreement.

The database’s Info tab has a Logs card, on every database. Its line says who reads the logs: Slow and error logs, read from the files on the database’s host by its own agent, or, on RDS and Aurora, read through the RDS API by the active collector with its own instance role. One row per log:

Column Shows
Log Slow query log or Error log
Setting The values the database reports, such as slow_query_log = ON · long_query_time = 1 · log_output = FILE, or log_error = /var/log/mysql/error.log, and the file the reader follows; not reported yet before the first report
Read by The host server, as <server> (the host server’s agent), or the active collector, as <server> (the active collector), through the RDS API, linking to the server’s page
State Reading, or the state below
Fix What to do, or —
Last report When the reader last reported

Test again (database.edit) asks every candidate to probe again within about 15–35 seconds, and the log readers report with them: use it once you have fixed a host’s probe or the role’s policy. On RDS and Aurora the card ends with the 6-minute line, and beside a denied log it shows The read policy and its cost.

Each log shows the first of these that applies, in this order: its host and agent, then whether its reader reports, then the database’s settings, then what the reader found. The Logs tab shows the same state and fix above the log’s chart.

State Means Fix
Reading The log is read. —
No host server A self-managed database with no host server. Choose the server it runs on: Info → Edit setup, step 2, Which server runs this database? Its agent reads the log files.
Host not in the pool The host server is not one of the database’s collectors. Add it to the collectors (Info → Edit setup, step 3): the host reads the logs and needs the credential source and the redaction key.
Waiting for the first report The host’s first probe of the database has not passed yet, or no report has arrived; on RDS and Aurora, no active collector yet. None: the first report arrives within a couple of minutes.
Host not eligible The host’s probe failed, with its reason (a key mismatch, credentials, grants). Fix it on the host, then Test again.
Agent update needed The reader’s agent predates log reading. None: it updates itself within a few minutes of a release.
Not reporting No report from the reader for more than 3 minutes: No report from <server> since <time>. Check that its agent is running and connected (systemctl status wt15-server-agent): reading resumes from where it stopped when it reports again.
Slow log off slow_query_log is off. MySQL 8.0 and 8.4: SET PERSIST slow_query_log = ON (and long_query_time, for example 1). MySQL 5.7 and MariaDB: SET GLOBAL slow_query_log = ON, and in the server’s configuration. RDS and Aurora: slow_query_log = 1 in a custom DB parameter group. See Turning the slow log on.
log_output is TABLE (or NONE) A self-managed database’s log_output without FILE. Set log_output = FILE (or FILE,TABLE). mysql.slow_log holds raw statements and is never read.
log_output is TABLE, on RDS and Aurora The instance’s parameter group writes no file, as the default group does. Set slow_query_log = 1 and log_output = FILE in a custom DB parameter group. The default group cannot be edited, and associating a new group takes a reboot.
Sink not supported: <sink> log_error_services does not name log_sink_internal, so the error log file gets no traditional lines, the only ones WatchTower15 reads. Add log_sink_internal to log_error_services.
Statement digests unavailable The reader cannot reach the database to key its statements, with the reason (for example too_many_connections or auth_failed). Slow statements wait 5 minutes, then are dropped and counted. Fix the reason: the host’s credentials, or free a connection (the monitoring user may hold 3: the active collector’s, the standby’s probe and the host’s). It clears at the next digest call that succeeds.
File not on this host The database reported a path the reader cannot read, shown with the path. Mount a container’s log directory at the same path on the host.
stderr, no systemd unit log_error is stderr, and no mysql, mysqld or mariadb unit runs on the host. Set log_error to a file.
Permission denied The collector’s role was refused rds:DescribeDBLogFiles and rds:DownloadDBLogFilePortion. Attach the full read policy, with its WT15DatabaseLogsRead statement. Retried every hour, and on Test again.
Cloud layer unavailable Cloud metrics has not matched the database to an RDS instance, with the cloud layer’s state and fix. The logs are read from the instance it matches, so nothing is read until it does. As the cloud layer’s message says.

database.view reads the Logs tab and the Info tab’s Logs card; the tab has no action. Reading the events on the Logs page needs logs.view too (In the Logs explorer). database.edit presses Test again. Revealing a key needs root on a collector, not a WatchTower15 permission. See Roles & permissions.