Sailfish

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#

TableOne row per
sailfish_snapshotsCollection: time, server uptime, interval, whether MySQL restarted, granularity
sailfish_tables, sailfish_indexesTable or index, with columns, size, first_seen_at, dropped_at, and last_used_at or last_scanned_at
sailfish_digestsStatement shape, with its normalised text and a sample
sailfish_errorsError number the server raised, with its name, SQL state and when it was first and last seen
sailfish_serversThe MySQL server: one row
sailfish_server_settingsThe server's global variables as last read, and the SET that last changed each one a SET has: one row
sailfish_setting_changesSetting whose value moved between two reads: the old and new value, when, and what moved it. Kept for good.
sailfish_table_sizesTable per day: rows, data, index and free bytes
sailfish_*_samplesObject 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_transactionsFinished 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_serversRedis server read: its cursor, version, persistence and replication as of the latest read, and with Redis 7 each command's percentiles
sailfish_redis_samplesRedis server per snapshot: its counters as deltas, and gauges such as memory and clients as read
sailfish_redis_db_samplesRedis database per snapshot: keys, and keys with a TTL
sailfish_redis_command_samplesRedis command per snapshot in which it ran: calls, time, and rejected and failed calls
sailfish_redis_changesRedis restart, other server behind the connection, or failover, with the key count either side
sailfish_cache_keysGroup of cache keys in a store, with the entries and bytes it held at the latest collection. Never a key itself.
sailfish_cache_samplesGroup 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_eventsStretch 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_usage and table_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_long while it is on, otherwise events_transactions_history. See transactions below.
  • Row-lock waits and the rest of the server's counters from its global status.
  • Settings from global_variables, with variables_info and persisted_variables beside 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 the my.cnf line.
  • events_transactions_history otherwise, 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_info has another SET_TIME for 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: only SET PERSIST keeps 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 a SET that was never persisted does. It is dated when MySQL booted, and a value SET PERSIST left for the restart names who persisted it.
  • No SET of its own. MySQL does not say what moved it: a SET of another setting, as super_read_only turns on read_only and character_set_server moves collation_server, or MySQL or a plugin, as Group Replication sets read_only while 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:

ResetHow Sailfish sees itWhat 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

Install Sailfish today.

Checkout ends with your license key, and the installation guide takes it from there.