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 and its settings: one row |
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. |
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. - Row-lock waits and the rest of the server's counters from its global status.
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.
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 |
What a collection reads is recorded on its snapshot. The first collection after an upgrade that reads something new takes a baseline of it, rather than counting everything since boot as one interval.