The cheapest throughput win I ever shipped was deleting a line of ORM configuration. The service was a read-heavy API doing about 9,000 queries per second, p99 latency at 210 milliseconds, CPU at seventy percent on the primary, and the InnoDB history list climbing past two million every afternoon like clockwork. The ORM was setting autocommit=0 at pool checkout — because transactions are safer, said the commit message from years earlier — and nothing ever committed after a SELECT-only request. Every pooled connection held one transaction, and one read view, open for hours. Two changes: autocommit back to 1, and explicit commits in the two code paths that genuinely needed transactions. p99 fell to 140 milliseconds and the history list flatlined under five thousand.
InnoDB has a genuine fast path for transactions it knows are read-only, and a separate, widely confused behavior around read views and purge. The first saves CPU; the second is what was actually eating my afternoons. Both are worth understanding precisely, because the ORM defaults in most stacks get at least one of them wrong.
What does InnoDB skip for a read-only transaction?
Transaction ID allocation and registration in the read-write transaction list — the bookkeeping that makes snapshot creation expensive at high concurrency. MVCC consistent reads need a read view: a snapshot of which transaction IDs were in flight when the read began, built by scanning the registry of active read-write transactions while holding the trx_sys mutex. A read-write transaction must allocate a trx_id and put itself on that list. A transaction known to be read-only skips both steps: it gets no trx_id, never registers, and therefore never inflates the list every other reader must scan. Three ways InnoDB knows: you said START TRANSACTION READ ONLY; the statement is an autocommit non-locking SELECT — autocommit on, no FOR UPDATE; or the session has transaction_read_only enabled. At thousands of mostly-read transactions per second on a many-core box, the read-write list is the difference between snapshot creation that scales and a trx_sys mutex that shows up in every wait profile. The read-only transactions still get a consistent snapshot; they just stop paying the registration tax to take it.
What does START TRANSACTION READ ONLY actually buy you?
A guarantee plus the fast path from the first statement: writes to persistent tables error out immediately — session temporary tables stay writable, since nobody else's consistency depends on them — and InnoDB never considers registering the transaction as read-write. A plain START TRANSACTION is lazily read-optimized in 8.0 — the trx_id is allocated only if and when the transaction actually writes — so the explicit marker is partly about intent. Intent has teeth: a write inside an explicit read-only transaction fails with an error instead of silently promoting, which is exactly what you want on connections routed to replicas, where a stray write is data drift, not a slow query. Setting the default for a whole session is one line:
-- every transaction in this session defaults to read-only
SET SESSION transaction_read_only = ON;
-- or per transaction
START TRANSACTION READ ONLY;
SELECT order_id, status FROM orders WHERE customer_id = 4812;
COMMIT;
-- what is running read-write versus read-only right now?
SELECT trx_id, trx_is_read_only, trx_autocommit_non_locking,
trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.innodb_trx
ORDER BY trx_started;The INNODB_TRX columns trx_is_read_only and trx_autocommit_non_locking make the fast path observable — a healthy read-heavy instance shows most active transactions flagged one way or the other, and a long age_seconds on any of them is a smell. Note the isolation-level interaction: under REPEATABLE READ, the read view comes from the first consistent read in the transaction, regardless of read-only marking. The fast path changes what the snapshot costs to create and register. It does not change how long the snapshot lives.
Why does autocommit=0 still pin purge for read-only sessions?
Because the open read view — not the trx_id — is what pins purge, and with autocommit=0 the first SELECT's snapshot stays open until somebody commits. Under REPEATABLE READ, the consistent-read snapshot is established at the first SELECT and held for the life of the transaction. Purge can only discard undo row versions that no open read view could still need, so a three-hour-old read view freezes purge at that point: the history list grows, undo tablespaces swell, and every consistent read on a churned row pays more as version chains lengthen. This is the confusion to stamp out: the read-only fast path saves transaction bookkeeping CPU, and it changes nothing about undo retention. An autocommit=0 session doing nothing but SELECTs is cheap in the trx_sys sense and expensive in the purge sense at the same time. With autocommit=1 each SELECT's snapshot closes when the statement ends, so purge is pinned for milliseconds at a time; READ COMMITTED likewise refreshes the read view per statement. The mechanics of the backlog itself — history list length, purge threads, the lag that follows — are in InnoDB history list and purge lag, and the hunting side is long transaction detection. My API's two-million history list was not a purge problem at all. It was four hundred pooled connections each politely holding a door open.
How do transaction_read_only, read_only, and super_read_only differ on replicas?
read_only and super_read_only are server-wide write gates; transaction_read_only is a per-session default for the fast path — different layers, constantly confused. read_only=ON refuses writes from ordinary users while still allowing privileged accounts and, critically, the replication applier threads, so a replica keeps applying changes. super_read_only=ON goes further: it blocks privileged users too, and setting it also sets read_only — though clearing it later leaves read_only set, a small asymmetry that has confused more than one failover runbook. Neither gate has anything to do with transaction bookkeeping; they are safety rails against misrouted writes and split-brain accidents. transaction_read_only, by contrast, is the optimizer-facing hint that marks a session's transactions as read-only for the fast path, and it is the one your replica-routed connection pools should set at initialization. My provisioning rule: replicas get super_read_only at boot, and replica pools get transaction_read_only at checkout. The first makes an errant write to a persistent table impossible — temporary-table operations and the applier threads pass through, as they must; the second makes an accidental write loud and every read cheaper.
How do you measure the win on read-heavy workloads?
Watch the read-write transaction count, the trx_id counter rate, and the history list length before and after the change — the improvement shows up in all three, and in latency. The observation kit:
-- history list pressure: the purge backlog in one number
SELECT NAME, COUNT
FROM information_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';
-- how many active transactions are read-write right now?
SELECT trx_is_read_only, COUNT(*)
FROM information_schema.innodb_trx
GROUP BY trx_is_read_only;
-- the oldest open transactions, the ones pinning purge
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;SHOW ENGINE INNODB STATUS tells the same story in prose: the Trx id counter line advancing, the TRANSACTIONS section enumerating what is registered, and the History list length line doing its afternoon climb. My A/B on the API was blunt: same traffic replayed through a staging pool, commit-after-read enabled. Throughput rose about twelve percent and p99 dropped from 210 to 140 milliseconds — modest, and mostly the trx_sys mutex relief. The dramatic change was purge: history list from a two-million daily peak to under five thousand steady-state, undo tablespace growth flat, and the evening latency wobble gone. Expect the fast path itself to matter most at very high concurrency on big hardware; on smaller instances the purge effect dominates, because one zombie read view hurts everyone, not just its own queries.
Where do ORMs sabotage the fast path?
At pool checkout and at request boundaries: frameworks that set autocommit=0 or wrap every request in a transaction, then never commit the read-only requests. The recurring patterns I have audited: a JDBC pool configured with autoCommit=false as the default because some write path needs it, so every read path inherits an open transaction; middleware that opens a transaction at the start of every HTTP request including GETs; session-per-request patterns where the transaction spans template rendering and outbound API calls, stretching a five-millisecond read view past a hundred milliseconds; and the simplest one, code that commits writes but lets SELECT-only requests return with the transaction open, leaving the pool to hold the read view until the next borrower arrives. The fixes are boring and total: commit or roll back unconditionally at request end — a rollback closes the read view just as well — keep autocommit on for read paths and reserve explicit transactions for writes, and route reporting queries to READ COMMITTED sessions or to replicas so their read views are short by construction. If your framework cannot guarantee a commit after reads, prefer autocommit plus explicit transactions where they matter. A transaction you forgot to close is not safety; it is a lease on history.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. History list length, read-view age, the read-write transaction mix in INNODB_TRX — these are exactly the signals the MySQL work is designed to graph, because a purge backlog is always a symptom and the cause is usually one connection holding a door open. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first monitoring runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.