TiDB data freshness check and SQL alerts

Connect TiDB

The form has five fields: host, port, user, password and db. It is the MySQL form, and its port defaults to 3306, so change it to the MySQL-protocol port of your TiDB deployment. There is no SSL toggle on the form and no tunnel, and arcodash refuses hosts that resolve to private or loopback addresses and publishes no fixed outbound IPs, so TiDB needs a public endpoint. Connections time out after 5 seconds. Setup and testing are covered in Connectors overview.

A read-only user uses ordinary account statements:

CREATE USER 'arcodash_ro' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON shop.* TO 'arcodash_ro';

With TiDB’s default NO_AUTO_CREATE_USER mode, GRANT does not create a missing user with an empty password. Create the user first, as above.

What a distributed database changes

TiDB runs the MySQL dialect on top of separate storage nodes, and three consequences reach a monitoring query.

IDs don’t tell you the newest row. TiDB’s own compatibility page says AUTO_INCREMENT values increase within one TiDB server but are not necessarily sequential across servers, and recommends AUTO_RANDOM to avoid write hotspots (MySQL compatibility). AUTO_RANDOM IDs are random and unique, not increasing (AUTO_RANDOM). So MAX(id) is not a freshness signal here. Measure a timestamp.

No stored routines, triggers or events. The same page lists them as unsupported. A freshness check cannot live inside the database as a scheduled event; it has to be a query run from outside, which is how arcodash runs it.

Case and defaults. The default collation is utf8mb4_bin, which compares case-sensitively, and explicit_defaults_for_timestamp is fixed on. A WHERE status = 'Paid' will not match 'paid'. If you are moving a MySQL check over, run it against TiDB and compare the count before trusting it.

A freshness check

A check is a scheduled query that returns one row and one number. The monitor reads that first row and compares the column with your threshold. TiDB supports the MySQL 8.0 date and time functions (date and time functions), so the MySQL form works:

SELECT COALESCE(
         TIMESTAMPDIFF(MINUTE, MAX(loaded_at), UTC_TIMESTAMP()),
         999999
       ) AS minutes_since_last_row
FROM events;

Alert when minutes_since_last_row is > 60. The COALESCE turns an empty table into a large number, since a NULL never crosses a threshold. This matters most when TiDB is the target of a replication tool such as TiDB Data Migration or TiCDC: the target table can look healthy while the stream stalls, and the newest loaded_at is the only honest sign. Put the query on a schedule.

Volume and metric checks

Row volume over the last day:

SELECT COUNT(*) AS rows_last_24h
FROM events
WHERE loaded_at >= UTC_TIMESTAMP() - INTERVAL 24 HOUR;

Alert with < and a floor under a normal day, or use a baseline monitor. A validity check on the same table:

SELECT COUNT(*) AS dup_orders
FROM (SELECT order_id FROM orders GROUP BY order_id HAVING COUNT(*) > 1) t;

Alert when dup_orders is > 0. A duplicate check against the data is cheap insurance after a migration or a replication restart. Index the timestamp column, since a scan of a large table touches many storage nodes.

Stale reads for heavy tiles

TiDB can read a snapshot slightly in the past, which spares the cluster on an expensive dashboard query. The documented form puts AS OF TIMESTAMP on the table:

SELECT DATE(created_at) AS day, COUNT(*) AS orders
FROM orders AS OF TIMESTAMP NOW() - INTERVAL 10 SECOND
GROUP BY DATE(created_at)
ORDER BY day;

That page suggests reading data from more than about 5 seconds ago, and notes that a timestamp ahead of the latest allocation, or older than the garbage-collection safe point, errors, so keep the offset small. Do not use it on a freshness check: a check that reads the past reports a staleness you added yourself. There is also a session variable, tidb_read_staleness, but arcodash opens a fresh connection per run, so use the per-table clause. See stale read for the limits.

Dashboard tiles

Daily orders over the last 30 days:

SELECT DATE(created_at) AS day, COUNT(*) AS orders
FROM orders
WHERE created_at >= UTC_TIMESTAMP() - INTERVAL 30 DAY
GROUP BY DATE(created_at)
ORDER BY day;

Load lag by hour, for spotting where a stream pauses:

SELECT DATE_FORMAT(loaded_at, '%Y-%m-%d %H:00') AS hr, COUNT(*) AS n
FROM events
WHERE loaded_at >= UTC_TIMESTAMP() - INTERVAL 48 HOUR
GROUP BY hr
ORDER BY hr;

Runs keep at most 10,000 rows and stop after 300 seconds (Limits), so aggregate in SQL. arcodash is hosted only, with a 14 day free trial and then from $59 a month for unlimited seats.

The MySQL connector page has the shared query patterns, and MariaDB is the other MySQL-compatible option.

docs

TiDB setup in the docs

Connection fields, an example configuration and caveats.