Events
Twenty-one MySQL detectors, fourteen for Redis and two for the cache, from a session stuck behind a lock to a redundant index, with the threshold each one uses and how to change it.
sailfish:detect runs at the end of every collection. Each detector opens an event when its condition
starts to hold, keeps that one event open per subject (a table, an index, the server, an account, a setting, a Redis
command or a group of cache keys) while it does, and resolves it once it clears. Most read the recorded history. The
two under live sessions read the server as it stands when detection runs. Whether an
event is sent as it opens or in the daily digest is set per type, and so is its severity.
The Redis events need SAILFISH_REDIS_CONNECTION, and the
cache events SAILFISH_CACHE_ENABLED. Their thresholds are on the
Redis and
Cache pages.
The server#
| Event | When | Severity | Sent |
|---|---|---|---|
Connections running outconnections_saturated |
Any connection refused at max_connections in the last hour, or 80% of them open at a collection. | critical | Immediately |
Row-lock surgerow_lock_surge |
Time waiting for InnoDB row locks over the last hour is at least 3× the usual for that hour, and at least a minute. | critical | Immediately |
Slow-query surgeslow_query_surge |
Statements over long_query_time in the last hour are at least 3× the usual for that hour, and at least 60. | warning | Immediately |
Error surgeerror_surge |
One MySQL error, such as a deadlock (1213) or a lock wait timeout (1205), raised at least 3× as often as usual for that hour, and at least 100 times. An error the server never raised before counts from none. | warning | Immediately |
History list growinghistory_list_growing |
InnoDB's history list is at least 100,000 and longer than an hour ago, which means a transaction was left open. Needs the PROCESS privilege. | warning | Immediately |
Buffer pool missesbuffer_pool_misses |
Over the last hour, under 99% of reads served from memory, with 100,000 pages or more read from disk. Quiet after a restart, while the pool warms up. | warning | Daily digest |
Temp tables on disktmp_disk_surge |
Internal temporary tables written to disk over the last hour are at least 3× the usual for that hour, and at least 1,000. | warning | Daily digest |
Setting changesetting_change |
A global variable changed value. It says what changed it: a SET, naming who ran it and whether a restart undoes it; a restart that brought back the option file's value or the default; or no SET of its own, as when super_read_only turns on read_only. One event per setting, open for 24 hours after its latest change. | info | Daily digest |
Live sessions#
| Event | When | Severity | Sent |
|---|---|---|---|
Sessions blockedsessions_blocked |
A session has waited at least 30 seconds for a row or metadata lock that another session holds. The usual case is an ALTER TABLE stuck behind an idle transaction, with every query on the table queued behind the ALTER. One event per account holding others up, naming the session holding up the most, how long its transaction has been open, the tables and locks involved, and the KILL that ends it. Needs the events_waits_current consumer, which MySQL leaves off. | critical | Immediately |
Transaction left openidle_transaction |
A session has sat idle for at least a minute in a transaction that has been open for at least 5 minutes. One event per account, saying how many transactions it has open, whether anyone waits for their locks, and the KILL that rolls one back. It stays open while any of them does. | warning | Immediately |
Transactions#
| Event | When | Severity | Sent |
|---|---|---|---|
Rollback surgerollback_surge |
The share of finished transactions rolled back over the last hour is at least 3× the usual share for that hour, at least 5%, and at least 60 rollbacks. It is a share rather than a rate, so more traffic alone stays quiet. | warning | Immediately |
Long transactionlong_transaction |
A transaction that ran at least 5 seconds (transactions.long_seconds) finished in the last hour. One event for each account and program that ran one, open until an hour passes without another. | warning | Daily digest |
Tables and indexes#
| Event | When | Severity | Sent |
|---|---|---|---|
Table-scan surgescan_surge |
Rows read without an index over the last hour, by full scans or fetched by an index merge, are at least 3× the average for that hour over the previous 7 days, and at least 100,000, on a table of at least 1,000 rows. The usual is first grown by as much as the table's reads through an index grew, or one index's reads if those grew more, so more queries alone stay quiet. | critical | Immediately |
Index went coldindex_cold |
Read in the 14 days before the last 7, and not fetched since. | warning | Daily digest |
Never usedindex_never_used |
No fetch in 30 days of observed history. Says how big the index is, what its writes cost and whether another index covers it. | warning | Daily digest |
Redundant indexindex_redundant |
Another index makes it redundant: a duplicate, a left prefix of a wider one, or on InnoDB one spelling out the primary key every index already ends with. The index page shows how to drop it. | warning | Daily digest |
Table growthtable_growth |
Grew at least 1 GiB in the last 7 days, and at least 2× what it gained in an average week over the 4 before. | warning | Daily digest |
Unindexed referenceunindexed_reference |
*_id columns no index starts with, on a table of at least 10,000 rows that was read without an index in the last 7 days. A heuristic aimed at foreignId() without constrained(). | info | Daily digest |
No primary keyno_primary_key |
A table without one. | info | Daily digest |
Reclaimable spacetable_free_space |
An InnoDB table's tablespace is at least 25% free, and at least 1 GiB. | info | Daily digest |
Schema changeschema_change |
A table or index was added or dropped. | info | Daily digest |
Redis#
| Event | When | Severity | Sent |
|---|---|---|---|
Redis memory fullredis_memory_full |
Redis used at least 90% of maxmemory at a collection in the last hour. Turn it off for a Redis used only as an LRU cache, which runs full by design. | warning | Immediately |
Redis clients running outredis_clients_saturated |
Any connection refused at maxclients in the last hour, or 80% of them open at a collection. | critical | Immediately |
Redis persistence failingredis_persistence_failing |
The latest RDB snapshot or AOF write failed. | critical | Immediately |
Redis replica link downredis_replica_link_down |
The Redis read is a replica whose link to its master has been down for 2 minutes (30 during a full resync), so it serves stale data. | critical | Immediately |
Redis swappingredis_swapping |
Resident memory below 90% of used memory at two collections in a row: part of Redis is swapped out, and every command that touches it waits on disk. | critical | Immediately |
Redis miss surgeredis_miss_surge |
Misses for each hit over the last hour at least 3× the usual for that hour, at least 3,600 misses, and hits at least 25% below usual. Not misses alone: a queue worker that finds its queue empty misses twice, so more workers add misses without taking any hits away. | warning | Immediately |
Redis error surgeredis_error_surge |
Commands answered with an error over the last hour at least 3× the usual for that hour, and at least 100. Redis 6.2 and later. | warning | Immediately |
Redis commands droppedredis_commands_dropped |
Commands over the last hour at most a quarter of the usual for that hour and of the same hour a week before, on a Redis that usually runs at least 3,600 an hour: the application or its workers stopped talking to it. | warning | Immediately |
Redis dangerous commandredis_dangerous_command |
KEYS, FLUSHDB or FLUSHALL ran since the previous collection. It says how often, the databases that lost most of their keys, and for FLUSHDB that php artisan cache:clear runs it. No recovery is sent. | warning | Immediately |
Redis restartedredis_restarted |
Redis restarted in the last 24 hours. It says whether Redis came back without most of its keys, as one without persistence does, so the misses that follow read as a cold cache. | info | Daily digest |
Redis eviction surgeredis_eviction_surge |
Keys evicted over the last hour at least 3× the usual for that hour, and at least 3,600. | warning | Daily digest |
Redis keys without a TTLredis_keys_without_ttl_growing |
Keys without a TTL grew by at least 10,000 over the last 7 days, to at least 1.25× as many, with no one day half of it: Cache::forever() or a SET without EX leaking, not a bulk load. It says when memory reaches maxmemory at that pace. | warning | Daily digest |
Redis memory fragmentedredis_memory_fragmented |
Resident memory at least 1.5× used memory, and at least 256 MiB over it, at two collections in a row. A small Redis, which always has a high ratio, stays quiet. | warning | Daily digest |
Redis command slowerredis_command_slower |
A command's time per call over the last hour at least 3× its time per call at that hour on the previous 7 days, with at least 100 calls in each: a hash, set or list read whole that keeps growing, or larger values. | warning | Daily digest |
The cache#
| Event | When | Severity | Sent |
|---|---|---|---|
Cache stampedecache_stampede |
A group's keys were generated in two processes at once at least 10 times over the last hour, each generation taking at least 50 ms on average: whichever finished second did the work for nothing. It says how much time that wasted and suggests Cache::flexible(), or for a group already using it, that its keys were missed outright. | warning | Daily digest |
Cache entries unreadcache_unread |
At least half of a group's entries that expired or were forgotten or written over in the last 24 hours were never read, of at least 100. It says what generating them cost and how big they were. | info | Daily digest |
Detectors that need history#
The surges, the cold and never-used index detectors, table growth and unindexed references compare against what Sailfish has recorded, so they stay quiet until the history covers their window. Until then the Events page says how many more days each one needs. A surge needs 7 days, a cold index 21, a never-used one 30 and table growth 35. A long transaction, a setting change and the two live-session events need none.
The PRIMARY key is left out of the cold and never-used detectors. A table's clustered index is read through every other index, whatever its own counters say.
How the surges are measured#
Every surge compares the last hour with the average of the same hour of the day over the previous 7 days, so a nightly batch job that always scans at 02:00 is its own baseline. It fires when the hour is at least 3× that average and above a floor, so a table going from 10 rows scanned to 40 stays quiet. Each takes the same keys:
'scan_surge' => [
'enabled' => true,
'history_days' => 7,
'ratio' => 3,
'min_rows_per_hour' => 100_000,
'min_rows' => 1_000, // tables smaller than this are never flagged
],
'row_lock_surge' => [
'enabled' => true,
'history_days' => 7,
'ratio' => 3,
'min_per_hour' => 60_000, // milliseconds waited
],
'tmp_disk_surge' => [ /* ... */ 'min_per_hour' => 1_000 ],
'slow_query_surge' => [ /* ... */ 'min_per_hour' => 60 ],
The error surge is measured per error number, and takes two more keys to narrow it, each a list of error numbers. An application that relies on duplicate keys (1062) for its upserts might ignore that one:
'error_surge' => [
'enabled' => true,
'history_days' => 7,
'ratio' => 3,
'min_per_hour' => 100,
'only' => [], // default: every error
'ignore' => [], // e.g. [1062]
],
Table scans and traffic#
More traffic scans more rows without anything being wrong, so the table-scan surge allows for it. The usual scans
are first grown by as much as the table's reads through an index grew, or the reads through any one of its indexes
if those grew more, and the surge must add 2× the usual on top of that (ratio less one), as a query
that loses its index does even in a busy hour. More queries grow both alike and stay quiet. Less traffic is never
allowed for, so a quiet hour still needs 3× the usual scans.
What performance_schema counts as read without an index includes each row an index merge fetches by position after
reading its indexes, and those grow as the merged indexes' reads do. So an index merge that runs five times as often
stays quiet. Index reads under min_rows_per_hour at that hour grow nothing however much they are read
now, so a query that loses its index and falls back to merging indexes seldom read before still opens a surge.
Index reads are counted for the whole table, the primary key's included, so more lookups of one kind can hide a
lost index in another query for as long as they stay up. A secondary index no longer read at all still opens an
index went cold event after 7 quiet days. A table must hold at least min_rows to be flagged,
because a small lookup table is scanned by design. Its size is the daily reading, so a table filled since is
watched from its next reading, and an open surge resolves once traffic accounts for its scans.
Index thresholds#
'index_cold' => [
'enabled' => true,
'baseline_days' => 14, // read during these...
'quiet_days' => 7, // ...and not since
'min_baseline_reads' => 1,
],
'index_never_used' => [
'enabled' => true,
'days' => 30,
],
'index_redundant' => [
'enabled' => true,
'days' => 7, // window its writes are measured over
],
A never-used event says how big the index is, what its writes cost and whether another index covers it. A redundant index needs no history, since its definition is enough, and its page on the dashboard shows how to drop it.
Table thresholds#
'table_growth' => [
'enabled' => true,
'days' => 7,
'baseline_periods' => 4,
'ratio' => 2,
'min_bytes' => 1 << 30, // 1 GiB
],
'table_free_space' => [
'enabled' => true,
'min_share' => 0.25,
'min_bytes' => 1 << 30,
],
'unindexed_reference' => [
'enabled' => true,
'days' => 7,
'min_rows' => 10_000,
],
'schema_change' => [
'enabled' => true,
'open_hours' => 24, // informational: closes itself after a day
],
Server thresholds#
'connections_saturated' => [
'enabled' => true,
'ratio' => 0.8, // of max_connections open at a collection
],
'history_list_growing' => [
'enabled' => true,
'min_length' => 100_000,
],
'buffer_pool_misses' => [
'enabled' => true,
'hours' => 1,
'min_hit_ratio' => 0.99,
'min_disk_reads_per_hour' => 100_000,
],
'setting_change' => [
'enabled' => true,
'open_hours' => 24, // informational: open a day after the latest change
'ignore' => [], // setting names
],
A setting change event is opened per setting and stays open for open_hours after its latest change, so
a setting tuned three times in an afternoon is one event. A change after the event was sent opens another. A setting
in ignore, such as one a plugin keeps changing, opens no event and is left off the dashboard, though
its changes are still recorded. The first read after installing records the values and opens nothing.
Blocked sessions and transactions left open#
These two read what the server is doing when detection runs, as the Activity page does, rather than the recorded
history. Nothing recorded would show them. A metadata lock is not an InnoDB row lock, so an
ALTER TABLE stuck behind an idle transaction, and every query on its table queued behind it, adds
nothing to the time the row-lock surge measures. MySQL lets a metadata lock wait for lock_wait_timeout,
a year by default. The history list only grows long enough to notice after a long while, and needs
PROCESS to read.
'sessions_blocked' => [
'enabled' => true,
'min_seconds' => 30, // waited on the session holding the lock
],
'idle_transaction' => [
'enabled' => true,
'min_seconds' => 300, // the transaction open this long...
'min_idle_seconds' => 60, // ...and idle in it this long
],
Sessions blocked#
It opens one event for each account whose sessions others have waited for directly for at least
min_seconds, on a row lock or a metadata lock. A pool of connections is one alert, and a mute outlasts
the connections. The event says who the session holding up the most is, whether it sits idle in a transaction and
for how long, how many wait behind it, the tables and locks involved, an ALTER TABLE stuck behind it
with the queries queued behind the ALTER, and, while it is open, the KILL that ends it.
It names up to 10 of the other sessions holding them up and counts them all, since ending one alone frees nobody
waiting behind both. An ALTER TABLE waits for every transaction that used its table, so when those
belong to different accounts, it and its queue count in each account's event. The event stays open while one of
those sessions goes on holding anyone up in the same transaction or statement, or with a lock it already held,
even while it waits for someone else itself.
How long a session has waited comes from the events_waits_current consumer, which MySQL leaves off, and
no event opens without it. The event needs only the waits MySQL times by default, leaving mutexes and sockets
untimed, so it costs far less than the wait/% instruments the Activity page can use. The
Instrumentation page has the
UPDATE and the my.cnf line. Whether the session holding the lock is in a transaction comes
from events_transactions_current and the transaction instrument, both on by default.
Without them the event says it cannot tell an idle transaction from LOCK TABLES, rather than guessing.
Transaction left open#
It opens when a session has been idle for min_idle_seconds in a transaction open for
min_seconds. Five minutes is past any web request and most steps of a job, so what it finds was left
open rather than merely slow, and a minute idle is more than a long batch's pause between statements. It opens one
event for each account, counting its transactions, with whether anyone waits for their locks and, while it is open,
the KILL that rolls one back. It stays open while any of those transactions does.
What detection can see#
Detection runs every five minutes and has to find a session that has already waited 30 seconds. A wait of five and
a half minutes is always caught, and a shorter one only if a run falls late enough inside it. An
ALTER TABLE waiting on a metadata lock waits as long as it takes, so it is caught. A row lock wait gives
up at innodb_lock_wait_timeout, 50 seconds by default, so however long the lock is held, a run has to
fall between a waiter's 30th and 50th second to see it.
Sailfish stores its tables on the server it reads. While FLUSH TABLES WITH READ LOCK holds up every
write, it holds up Sailfish's too: collection waits with them, and no event opens or resolves until it has passed.
The Activity page, which writes nothing, shows who holds it in the meantime. Other locks wider than a table, such as
LOCK INSTANCE FOR BACKUP, stop only DDL, and open events as a table's locks do.
Statements kept in these two events have their values taken out, as the dashboard shows them to anyone without the
viewSailfishQueryText gate, because the events go out by mail, Slack and webhook. Both link to the
Activity page.
Transaction thresholds#
'rollback_surge' => [
'enabled' => true,
'history_days' => 7,
'ratio' => 3,
'min_share' => 0.05, // of finished transactions
'min_per_hour' => 60, // rollbacks
],
'long_transaction' => [
'enabled' => true,
'min_seconds' => null, // null: transactions.long_seconds
],
The rollback surge measures a share rather than a rate, as more traffic rolls back more transactions without anything going wrong. The share also holds up when the per-session history loses most transactions on a busy server. An hour that finished no transaction has no share and is left out of the usual.
A long transaction event is opened for each account and program that finished one, and stays open while they keep
finishing, so a job that runs a long transaction every few minutes is one event rather than one a transaction.
Accounts whose sessions had disconnected by the time the history was read share one event. min_seconds
can only raise the bar above transactions.long_seconds, since shorter transactions are not kept (see
configuration). Both events read the
transaction history, which may be a sample: see
what gets recorded.
Acknowledging and muting#
On the Events page, acknowledging an event stops its reminders. It still resolves, and a recovery is still sent, when its condition clears. Muting holds every message about it until the mute ends, or for good. A muted event stays muted if it clears and comes back.
To change what is sent for a type, rather than whether it is detected, see notifications.