Thirty application accounts share one of the MariaDB primaries I run, and the question that comes up every time that box gets slow is always the same: who. Not which query — that is the slow log's job — but which account, which client host, which table. The night that sold me on userstat, the answer to "who is hammering the orders table" came back from a single information_schema query: a reporting login was responsible for eighty percent of all rows read on the server, and nobody had noticed because its queries were each individually fast. One SET GLOBAL turns that accounting on. It is the cheapest observability upgrade MariaDB offers, and almost nobody enables it.
What does userstat=ON actually collect?
Four information_schema tables, each also exposed as a SHOW command: USER_STATISTICS per database account, CLIENT_STATISTICS per client host, TABLE_STATISTICS per table, and INDEX_STATISTICS per index. The feature came from Google's production patch — Mark Callaghan's team, carried forward through Percona's userstatv2 and Ourdelta — and it has shipped in MariaDB since 5.2, living in the userstat plugin since 10.1.1. This is not exotic tooling; it is battle-tested accounting that predates most of performance_schema.
The column lists are where the value is. USER_STATISTICS gives you, per account: TOTAL_CONNECTIONS and CONCURRENT_CONNECTIONS, CONNECTED_TIME, BUSY_TIME and CPU_TIME, BYTES_RECEIVED and BYTES_SENT, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, command counters split into SELECT_COMMANDS, UPDATE_COMMANDS, and OTHER_COMMANDS, COMMIT_TRANSACTIONS and ROLLBACK_TRANSACTIONS, plus the security-flavored DENIED_CONNECTIONS, LOST_CONNECTIONS, and ACCESS_DENIED. TABLE_STATISTICS is narrower and sharper: TABLE_SCHEMA, TABLE_NAME, ROWS_READ, ROWS_CHANGED, and ROWS_CHANGED_X_INDEXES — the last being rows changed multiplied by indexes touched, which is write amplification made visible. INDEX_STATISTICS is the shortest: schema, table, index name, ROWS_READ. That one column is the unused-index detector, and we will get to it.
What does it cost, and how do you turn it on?
One dynamic variable, userstat, default OFF. SET GLOBAL userstat=ON starts collection immediately; write userstat=ON in the config file so it survives restarts. The overhead question deserves an honest answer rather than a slogan: collection is a handful of counter increments per statement plus some bookkeeping per connection, and on normal OLTP workloads I have never been able to measure it — but "cheap" is workload-dependent, and on a server doing fifty thousand microqueries a second, anything per-statement is worth a before-and-after benchmark on your own hardware. My experience says turn it on everywhere and stop thinking about it; my honesty says measure once on your busiest box first.
Two semantics to internalize. The tables are virtual information_schema tables backed by the plugin's in-memory counters, not a storage engine you can point at: they reset to zero on every server restart, and each can be reset independently with FLUSH USER_STATISTICS, FLUSH CLIENT_STATISTICS, FLUSH TABLE_STATISTICS, or FLUSH INDEX_STATISTICS. Raw cumulative values are therefore meaningless across restarts — always work with deltas between samples, and always record when the last restart or flush happened, or a monitoring graph will show you a cliff that is actually just a reboot. The second semantic is a feature: because the counters are independent of performance_schema, userstat keeps working on servers where performance_schema is dialed down or off, which describes a lot of production MariaDB.
How do you find the one account hammering a table?
Top by BUSY_TIME, then cross-reference. BUSY_TIME is the cumulative seconds the account's statements spent executing — the single best "who is loading this server" ranking — and ROWS_READ versus ROWS_CHANGED tells you whether the load is read or write shaped. From there, TABLE_STATISTICS names the victim.
-- who is loading the server right now? (sample deltas between runs)
SELECT USER, BUSY_TIME, CPU_TIME, ROWS_READ,
ROWS_INSERTED + ROWS_UPDATED + ROWS_DELETED AS ROWS_CHANGED,
SELECT_COMMANDS, UPDATE_COMMANDS, TOTAL_CONNECTIONS
FROM information_schema.USER_STATISTICS
ORDER BY BUSY_TIME DESC LIMIT 10;
-- which tables absorb the most read and write pressure?
SELECT TABLE_SCHEMA, TABLE_NAME, ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES
FROM information_schema.TABLE_STATISTICS
ORDER BY ROWS_READ DESC LIMIT 10;The incident from the opening read exactly like this: the reports account at eighty percent of server-wide ROWS_READ, and TABLE_STATISTICS showing nearly all of it concentrated on two fact tables — thousands of fast indexed lookups a minute, each cheap, the aggregate enormous. No slow query to optimize, no error to chase. The fix was organizational as much as technical: the reporting workload moved to a replica, and the account got a per-user MAX_STATEMENT_TIME ceiling for insurance. That is the pattern userstat is built for — the problem is not a query, it is a workload, and per-account attribution is what makes a workload visible. CLIENT_STATISTICS earns its keep the same way when accounts are shared: if every app logs in as appuser, the client-host rollup is what tells you which app server is generating the pressure.
Can INDEX_STATISTICS really find unused indexes?
Yes, with three caveats that keep you honest. The query itself is trivial — every index with zero reads since collection started is a candidate:
-- drop candidates: indexes never read since the counters started
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, ROWS_READ
FROM information_schema.INDEX_STATISTICS
WHERE ROWS_READ = 0
AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;Caveat one: the counters reset at restart and on FLUSH INDEX_STATISTICS, so "zero reads" means "zero reads since whenever" — you need the collection window to cover a full business cycle, month-end reports and quarterly rollups included, before an index is genuinely suspect. Caveat two: unique indexes and foreign-key supporting indexes earn their keep enforcing correctness on writes even when nothing ever reads them for lookups; check constraints before dropping anything. Caveat three: an index can be load-bearing exactly once a month for the report that would otherwise run for six hours. The workflow that works: collect candidates from a long window, verify against the slow log for any query that would regress, then drop during a maintenance window — and keep the DROP statements in the migration history so the reversal is one command. The same table, read differently, also surfaces write amplification: a table with a high ROWS_CHANGED_X_INDEXES and many zero-read indexes is paying index maintenance for nobody.
When do you use userstat versus performance_schema?
They answer different questions and I run both. performance_schema answers "which statements, which waits, which users right now" — per-digest latency, stage events, row-level lock detail — with real richness and real configuration surface. userstat answers "which accounts, clients, tables, and indexes carry the load, cumulatively" with zero query text, almost no tuning, and negligible overhead. Reach for userstat first when the question is attribution or capacity: who is growing, which table is trending, which index is dead weight. Reach for performance_schema, the slow log, or EXPLAIN when you have the who and need the what. They even interlock: userstat's MAX_STATEMENT_TIME_EXCEEDED column attributes statement-time kills per account, which is the fastest bridge from "queries are being guillotined" to "this account's queries are".
Where MonPG fits
Sampled deltas of these four tables, trended, are most of a workload-attribution product — per-account busy time, per-table read and write pressure, unused-index candidates flagged after a full collection window. That is precisely the signal class a monitoring tool should own, and it is on the list. The disclosure, as always in this series: I work on MonPG, which monitors PostgreSQL today; it does not monitor MariaDB yet. MariaDB support is on the way — in active development, with progress tracked on the coming-soon page — and per-account and per-table attribution of exactly this kind is in its design notes, because "who is hammering this table" should be a dashboard, not an incident-time query. Until the MariaDB side ships, userstat plus a cron snapshot is the toolkit. Running PostgreSQL too? That side is covered today — start with the PostgreSQL overview and how the engines compare, or browse the rest of the series on the blog.