The query that taught me about max_statement_time was a marketing report pointed at the production OLTP database. It joined orders against a half-million-row export with no usable index, ran for six hours overnight, and held a buffer pool's worth of pages hostage while the checkout path slowed to a crawl. Nobody noticed until morning because nothing on the server objected. MariaDB will happily run a terrible query forever unless you tell it not to — and the way you tell it is a built-in, zero-cost kill switch that most shops never configure: max_statement_time.
What does max_statement_time actually kill?
Almost any statement that runs longer than the limit you set, measured in wall-clock seconds. The value is a double, so 0.5 means five hundred milliseconds, and zero — the default — means no limit at all. When a statement exceeds the limit, the server aborts it and the client gets ERROR 1969, SQLSTATE 70100: query execution was interrupted, max_statement_time exceeded. The abort kills the statement, not the connection and not the enclosing transaction, which stays open for the application to handle or abandon.
Three honest caveats, all of which have bitten someone I know. First, the abort is not instantaneous: the server checks the timer at intervals during execution, so a query can overshoot — usually by a little, occasionally by a lot. Second, there are phases where the check path simply is not hit — a long storage-engine operation or a big internal sort can run well past the limit before the notice lands. A copy-algorithm ALTER TABLE is the classic case: it can sail past its budget and then, once killed, still has to roll back hours of table rebuild. Killed does not mean free. Third, statements running inside stored procedures are exempt from the guillotine entirely, which is a real difference from how people assume it works. The documentation is blunt that max_statement_time should not be your only protection against resource exhaustion, and it is right — think of it as a circuit breaker, not a load limiter.
One scoping note that matters on replicated setups: max_statement_time does not apply on replicas at all. MariaDB 10.10 added a separate slave_max_statement_time for the replica SQL thread, so on 10.11 and later you can bound the applier independently without touching the primary's behavior.
What are the four scopes you can set it at?
Server-wide, per session, per user, and per statement — and the right answer is almost never server-wide alone. A global limit is the blunt instrument: it catches the forgotten report, but it also caps your legitimate nightly batch. The layered approach I run is a generous global backstop, tighter per-user limits on accounts that historically misbehave, and per-statement limits at the handful of application endpoints known to be expensive.
-- server-wide backstop, live (persist it in the config file too)
SET GLOBAL max_statement_time = 300;
-- this connection only
SET SESSION max_statement_time = 30;
-- one statement only, no side effects on anything else
SET STATEMENT max_statement_time = 2 FOR
SELECT o.customer_id, SUM(o.total) FROM orders o GROUP BY o.customer_id;
-- per user: the reporting account gets sixty seconds, no more
GRANT SELECT ON analytics.* TO 'reports'@'%' WITH MAX_STATEMENT_TIME 60;
SELECT user, host, max_statement_time FROM mysql.user WHERE max_statement_time > 0;The per-user form is the one people miss: the limit is stored in the max_statement_time column of mysql.user and applies to every connection that account opens, which makes it perfect for BI tools and cron-driven report users that share one login. The per-statement form — SET STATEMENT ... FOR — is the scalpel: it bounds exactly one execution and touches nothing else, ideal for a known-heavy endpoint inside an application that otherwise needs room to work.
How is this different from MySQL's max_execution_time?
Different enough that assumptions from a MySQL background will mislead you. MySQL's max_execution_time is measured in milliseconds, applies to read-only SELECT statements only, and is most commonly set through the optimizer hint syntax. MariaDB's max_statement_time is measured in seconds, applies to any statement type — writes included, stored procedures excluded — and is set through variables, GRANT, and SET STATEMENT. MariaDB does not honor the MySQL hint form at all, which is a genuine migration gotcha: if your ORM or query builder emits MAX_EXECUTION_TIME hints, those hints are parsed as comments on MariaDB and you have no protection whatsoever. Teams moving from MySQL to MariaDB 10.11 need to re-express those limits, usually as per-user or per-session settings, and to re-think which statements need bounding — on MariaDB you can finally guillotine a runaway UPDATE, which MySQL still will not do for you.
How does it pair with the lock timeouts?
max_statement_time bounds total statement wall time; the lock timeouts bound specific kinds of waiting, and you need both because a statement can hurt people while doing no work at all. A transaction parked waiting on an InnoDB row lock is bounded by innodb_lock_wait_timeout — default fifty seconds, an eternity for OLTP; I run five to ten on user-facing workloads. A statement queued behind a metadata lock is bounded by lock_wait_timeout, whose default is a full day, which in practice means unbounded — set it to seconds or minutes, because a metadata lock queue behind one long ALTER is how you turn a schema change into a site outage. The three compose: the lock timeouts fire first for waiting, max_statement_time fires for everything that is actually executing, and together they keep any single statement from owning the server. Note that a killed lock-wait and a killed statement return different errors, which matters for how your application distinguishes "try again" from "give up".
Why do retry storms follow the guillotine, and what do you monitor?
Because the default application response to a database error is to retry immediately — and a retried runaway query is just the runaway again. I watched a dashboard endpoint with a thirty-second limit produce exactly that: every kill was followed by an instant retry, so the server ran back-to-back thirty-second full scans, forever, burning a core to produce nothing. The guillotine bounded each attempt and changed the shape of the incident not at all. The fixes are unglamorous: exponential backoff with a cap and jitter, a small retry budget, and treating repeated 1969s from the same endpoint as a bug report, not a blip. The kill switch buys you time to find the missing index or the cartesian join; it does not substitute for finding it.
-- how often is the guillotine firing, server-wide?
SHOW GLOBAL STATUS LIKE 'max_statement_time_exceeded';
-- and which accounts is it firing on? (requires userstat=ON)
SELECT USER, MAX_STATEMENT_TIME_EXCEEDED, TOTAL_CONNECTIONS, BUSY_TIME
FROM information_schema.USER_STATISTICS
ORDER BY MAX_STATEMENT_TIME_EXCEEDED DESC LIMIT 5;max_statement_time_exceeded is the counter to trend: a flat line is health, a step change after a deploy is a plan regression, and a steady climb is someone running reports against production with a retry loop. If you also enable userstat, the MAX_STATEMENT_TIME_EXCEEDED column in USER_STATISTICS attributes the kills per account, which turns "something is getting killed" into "the reports user is getting killed forty times an hour" in one query. That attribution is the difference between a mystery and a ticket.
Where MonPG fits
The monitoring shape is a trend on max_statement_time_exceeded, per-account attribution, and config auditing so the limits themselves do not quietly vanish in a config-file rewrite — a killed query is data about your schema, and it deserves the same retention as your latency graphs. The usual disclosure: I work on MonPG, which monitors PostgreSQL today and does not monitor MariaDB yet. MariaDB support is being built now, and the coming-soon page tracks it, with kill-counter trends and per-account runaway attribution on the design list precisely because they catch plan regressions before users do. Until the MariaDB side ships, the two queries above are the early-warning system. If PostgreSQL is also in your fleet — where statement_timeout plays the same role — that workflow is live today; see the PostgreSQL overview and the engine comparison, and read more on the blog.