What gets recorded
The sailfish_* tables, where each counter comes from, and how deltas survive the ways performance_schema resets.
performance_schema keeps cumulative counters that start at zero when the server boots. Sailfish reads them every five minutes and stores the difference since the last read, in tables in your own database. Nothing is sent anywhere else.
The tables#
| Table | One row per |
|---|---|
sailfish_snapshots | Collection: time, server uptime, interval, whether MySQL restarted, granularity |
sailfish_tables, sailfish_indexes | Table or index, with columns, size, first_seen_at, dropped_at, and last_used_at or last_scanned_at |
sailfish_digests | Statement shape, with its normalised text and a sample |
sailfish_errors | Error number the server raised, with its name, SQL state and when it was first and last seen |
sailfish_servers | The MySQL server: one row |
sailfish_server_settings | The server's global variables as last read, and the SET that last changed each one a SET has: one row |
sailfish_setting_changes | Setting whose value moved between two reads: the old and new value, when, and what moved it. Kept for good. |
sailfish_table_sizes | Table per day: rows, data, index and free bytes |
sailfish_*_samples | Object per snapshot in which the object moved. The server writes one every snapshot, with gauges such as connections open, and sailfish_transaction_samples counts the transactions committed and rolled back. |
sailfish_long_transactions | Finished transaction that ran at least transactions.long_seconds: how long, committed or rolled back, statements, access mode, isolation level, account and program. Never its text. |
sailfish_redis_servers | Redis server read: its cursor, version, persistence and replication as of the latest read, and with Redis 7 each command's percentiles |
sailfish_redis_samples | Redis server per snapshot: its counters as deltas, and gauges such as memory and clients as read |
sailfish_redis_db_samples | Redis database per snapshot: keys, and keys with a TTL |
sailfish_redis_command_samples | Redis command per snapshot in which it ran: calls, time, and rejected and failed calls |
sailfish_redis_changes | Redis restart, other server behind the connection, or failover, with the key count either side |
sailfish_cache_keys | Group of cache keys in a store, with the entries and bytes it held at the latest collection. Never a key itself. |
sailfish_cache_samples | Group of cache keys per snapshot in which it was used: hits, misses, writes, generations and their time, the longest, bytes written, generations twice at once, and entries that ended, read or not |
sailfish_events | Stretch of time a detector's condition held for one subject |
An index nothing read, on a table nothing inserted into or deleted from, writes no sample, so a quiet schema costs almost nothing to record. An index's inserts and deletes are taken from its table, because performance_schema never counts inserts against an index although every insert and delete writes every index.
Where each figure comes from#
-
Index and table reads from
table_io_waits_summary_by_index_usageandtable_io_waits_summary_by_table. -
Table lock waits from
table_lock_waits_summary_by_table. These are the locks the SQL layer takes on a table (LOCK TABLES, and the handler lock every statement takes), not InnoDB's row locks. - Errors from
events_errors_summary_global_by_error, server-wide. - Statement digests from
events_statements_summary_by_digest. -
Finished transactions from
events_transactions_history_longwhile it is on, otherwiseevents_transactions_history. See transactions below. - Row-lock waits and the rest of the server's counters from its global status.
-
Settings from
global_variables, withvariables_infoandpersisted_variablesbeside it. See settings below. -
Redis, when a connection is named, from
INFO all, its keyspace and commandstats sections included. See Redis. - The cache, when turned on, from the application's own cache events, buffered in Redis until a collection takes them. See Cache.
Table sizes and row counts are read once a day, by the first collection of the day and by the one after a new
table turns up. They are read with information_schema_stats_expiry off for that one query, so they are
current rather than a day-old cached figure. That costs InnoDB a statistics call per table, which is why they are
not read every five minutes. Row counts are InnoDB's estimates.
Transactions#
Every collection reads the transactions performance_schema keeps after they finish. It counts committed against
rolled back per interval, and keeps those that ran at least transactions.long_seconds (5 by default)
for 30 days. It reads only those that ended since the previous read, by the server's uptime, and tells the few read
twice apart by THREAD_ID and EVENT_ID, so a transaction is never counted twice. The first
read is a baseline, as for every counter, and a server that restarted has its history read whole.
The history comes from one of two tables, and the Transactions page says which:
-
events_transactions_history_long, the last 10,000 across the server, while it is on. MySQL leaves it off, but it writes one row per transaction, so it costs little. The Instrumentation page has the SQL and themy.cnfline. -
events_transactions_historyotherwise, on by default, which keeps only the last 10 transactions of each session and drops them when the session disconnects. A busy pooled connection loses most of its transactions between two collections, and a connection that lasts one request, as PHP-FPM's do, loses all of them. The counts are then a sample, and most long transactions go unseen.
Who ran a transaction is read from its session, so it is known only if the session was still connected when the
history was read. Its statements are counted from events_statements_history_long while it is on,
otherwise from events_statements_history, which keeps only the last 10 of each session. A native
prepared statement takes three of them, and a transaction whose START TRANSACTION is gone says it ran
at least so many.
Settings#
Every collection reads each global variable from global_variables, with variables_info and
persisted_variables beside it. That is a few hundred rows, all performance_schema's, so it needs no
grant beyond the rest. Each value is kept in sailfish_server_settings, and one that moved since the
previous read is a change, kept in sailfish_setting_changes with what moved it:
-
A
SET.variables_infohas anotherSET_TIMEfor it than the previous read saw, and names who ran it, dated by when they ran it. The change says whether a restart undoes it: onlySET PERSISTkeeps a runtime change, unless an option file sets the same value. -
A restart. MySQL came back with another value, from an option file, from
mysqld-auto.cnf, or its default, as aSETthat was never persisted does. It is dated when MySQL booted, and a valueSET PERSISTleft for the restart names who persisted it. -
No
SETof its own. MySQL does not say what moved it: aSETof another setting, assuper_read_onlyturns onread_onlyandcharacter_set_servermovescollation_server, or MySQL or a plugin, as Group Replication setsread_onlywhile it elects a primary. It is dated by the read that saw it.
Two SETs between collections are one change, from the value before the first to the value after the
second, by whoever ran the second, and a SET to the value a setting already had is no change. MySQL
moves SET_TIME for a SET SESSION as well, so a global change to a variable the application
also sets per connection, as Laravel sets sql_mode, may be credited to whoever set it last in their
session.
SET_HOST is the client's host name, which MySQL doesn't know under skip_name_resolve, as
the official Docker image runs, so often only the user is known. A variable that turns up or goes away, as an
upgrade or a plugin adds and removes them, is not a change, and nothing on the first read is.
gtid_executed, gtid_purged and gtid_owned, which MySQL changes with every
transaction, are not read.
A value that may be a secret is never kept, only a fingerprint of it, keyed with APP_KEY. The names
covered, and how to add more, are in
configuration.
The server-wide charts mark the latest 100 changes in the window, with one marker for any before them. A table's or an index's charts mark none. Changes usually come a few a month, so they are kept for good.
Surviving resets#
The first collection only records a baseline, since counters that have been running since boot don't describe any interval. From the second one on, deltas survive each way performance_schema counters reset:
| Reset | How Sailfish sees it | What it does |
|---|---|---|
| Server restart | Uptime went down, or the server booted later than it had at the previous collection | Starts a new generation. Any cursor from an earlier one is measured from zero, including one for a table nothing opens until hours after the restart. |
| TRUNCATE of a summary table, or a recreated row | A counter went backwards | Resets the whole row |
| Dropped and recreated table or index | A different object behind the same name | Discards the old cursor |
| Digest table reset | A new FIRST_SEEN |
Resets the digest |
FLUSH STATUS |
Some global counters went backwards and others did not | Counts each counter that went backwards from zero, on its own |