This page is about one file per table. I show you how InnoDB stores a table as its own primary-key index, and how two logs split the jobs: redo for crashes, binlog for replicas. After it you will be able to find any table's .ibd file, read checkpoint age, check binlog retention, and tell whether undo is piling up.
A relational database. Each table file is its primary-key index.
Good at: High-volume websites. Lookups by ID. Simple queries at huge scale. Operationally simple read replicas. Two logs: redo for crashes, binlog for replicas.
Runs as: one mysqld process, a thread per connectionSame three layers as every engine: process, memory, disk. The layer rule is written once on the PostgreSQL page: name the layer first, then diagnose inside it.
Request path
See the real thing: the official InnoDB Architecture diagram, Figure 17.1. One picture, in-memory structures on one side, on-disk structures on the other.
Same idea as PostgreSQL: exact names, one job per log, one check per incident. The rule is written once on the PostgreSQL page. This table adds the operational logs a DBA reads first: server, error, slow.
| Log | Exact file name | Where it sits (on-prem) | What it does | What to check in it |
|---|---|---|---|---|
| MySQL Redo log | #ib_redo0 · #ib_redo31 | #innodb_redo/ in the datadir (before 8.0.30: ib_logfile0/1 in the datadir root) | Physical record of page changes. Replayed at startup to rebuild dirty pages after a crash. | Checkpoint age in SHOW ENGINE INNODB STATUS (LOG section). Age near capacity means write stalls are coming. |
| MySQL Binary log | mysql-bin.000001, .000002, … | Datadir (path set by log_bin) | Logical record of every committed change. Replicas replay it. It is the second half of point-in-time recovery. | SHOW BINARY LOGS. Check total size and the oldest file. Retention must exceed the longest replica outage. |
| MySQL Error + slow logs | <hostname>.err · *-slow.log | Datadir / /var/log/mysql/ | Error log: startup, crashes, warnings. Slow log: statements over long_query_time, with rows examined. | Error log at the incident start. Slow log digested (pt-query-digest), ranked by total time, not by count. |
Same three outage rules as PostgreSQL: Safe live lever, Planned change, Do not touch. What each one means is written once on the PostgreSQL page.
| Parameter | What it does | During an outage |
|---|---|---|
| innodb_buffer_pool_size | Size of the page cache. The single most important MySQL setting. Typically 70 to 80% of RAM on a dedicated server. | Planned change. It is resizable online in 8.0 (SET GLOBAL), but resizing under load is slow and competes with the workload. Verify headroom first. Watch Innodb_buffer_pool_resize_status. |
| innodb_redo_log_capacity | Total redo log size (8.0.30 and later). It bounds checkpoint age headroom. | Safe live lever (dynamic). Use it when checkpoint age is pinned near capacity and writes are stalling. Raise it, then confirm age falls. |
| max_connections | Cap on concurrent connections. Each thread costs memory. | Short bridge only. It is dynamic, so a modest raise can stop "too many connections" errors while you fix the cause. The cause is almost always missing pooling (ProxySQL) or leaked connections. A big raise converts a connection incident into a memory incident. |
| innodb_flush_log_at_trx_commit | The durability dial. 1 means redo fsynced at every commit (safe). 2 means written to the OS, fsynced about once a second. 0 means about once a second entirely. | Do not relax it mid-outage to buy speed, unless the business explicitly accepts losing up to about 1 s of commits per crash. You would be trading tonight's latency incident for a future data-loss incident. |
Same rule as PostgreSQL: there is no hidden state. Every file here is inspectable with ls and du while the server runs. The full version is on the PostgreSQL page.
$ ls /var/lib/mysql ibdata1 # InnoDB system tablespace: # internal bookkeeping #innodb_redo/ # THE REDO LOG folder (8.0.30+; # before that: ib_logfile0/1 here) #ib_redo0 # ┐ 32 files, #ib_redo0 to #ib_redo31, #ib_redo31 # ┘ each 1/32 of innodb_redo_log_capacity mysql-bin.000001 # ┐ THE BINARY LOG: every change, mysql-bin.000002 # │ numbered, for replicas + PITR mysql-bin.index # ┘ list of binlog files that exist undo_001 undo_002 # undo tablespaces: old row versions #ib_16384_0.dblwr # doublewrite buffer: anti-torn-page mysql/ # system schema (users, privileges) shop/ # one folder per database you create users.ibd # ← table "users": data + primary-key # tree in ONE file orders.ibd <hostname>.err # the error log
Same pattern as PostgreSQL: five logs, one job each, and every operational budget is a time window. The full version is on the PostgreSQL page.
The other logs: PostgreSQL: WAL · MongoDB · WiredTiger: journal and oplog · the pattern behind all five, and where they break
Same rule as PostgreSQL: every mechanism above is observable on a running server in under two minutes. The full version is on the PostgreSQL page.
Run it locally: docker-compose-mysql.yml starts MySQL 8.4 on port 3306 with a shop database. docker compose -f docker-compose-mysql.yml up -d, then run the commands below against it.
-- where is everything? SHOW VARIABLES LIKE 'datadir'; SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; $ ls /var/lib/mysql/shop -- → users.ibd, orders.ibd, … -- the binary log SHOW BINARY LOGS; SHOW BINLOG EVENTS IN 'mysql-bin.000001'; $ mysqlbinlog mysql-bin.000001 | less -- redo log position + undo backlog SHOW ENGINE INNODB STATUS\G -- replica state and lag SHOW REPLICA STATUS\G
Problem. I add an index. The query ignores it. I run EXPLAIN first. It says full scan. That feels wrong. It is often right.
Mechanism. An index lookup reads pages one at a time, in key order. Those pages sit scattered across the .ibd file. Each one is a random read. A full scan reads the same file front to back, in one pass. One sequential pass can beat thousands of random reads. The planner compares those two costs. It picks the cheaper one. It skips the index when the query must return a large share of the table.
The planner estimates row counts from statistics. A bulk load can leave those statistics stale. The planner then guesses wrong. ANALYZE TABLE refreshes the statistics. Run it after a bulk load. Then read EXPLAIN again.
A secondary index does not hold the row. It holds the primary key value. Each match needs a second read into the clustered index. That read fetches the row from users.ibd. This is the bookmark lookup. One match costs two reads. A thousand matches cost two thousand reads.
Every secondary entry carries a copy of the primary key. A long string key makes every secondary entry longer. Longer entries mean more pages per index. Keep the primary key short. Use an integer id when you can.
Exact check. Run EXPLAIN. Then refresh statistics. Then check what the index holds.
Read EXPLAIN for the access type. A scan on a wide query is the planner doing its job. A scan on a narrow query means stale statistics or a missing index.
Writes started failing. Then the database refused writes entirely.
I broke the disk down by owner before touching anything. The binlog was the filler. The purge had not kept up, and the reason was not a dead replica. MySQL purges binlogs by time: binlog_expire_logs_seconds sets the age, 2592000 seconds (30 days) by default, auto-purge is on by default, and expired files go at startup and when the binlog is flushed. A replica that is disconnected holds nothing back. Only a connected replica that has not yet read a file keeps that one file. A disk full of binlogs means retention times write rate outgrew the disk, or retention was set to 0, or auto-purge was off, or a write burst filled it faster than purge could keep up. This incident does not record which of those it was, so check the retention value, the write rate, and the oldest file before naming the cause.
The fix is the purge, not the replica. Check every replica position with SHOW REPLICA STATUS, then free space with PURGE BINARY LOGS. Never rm old binlog files by hand. Set binlog_expire_logs_seconds to a value your write rate and disk can actually hold, and alert at 70 percent, not 98 percent. A replica that still needs a file you purged fails with error 1236 and must be rebuilt from a backup, which is why positions come first.
Real versions of this story: GitHub automated the failover with Orchestrator, then upgraded 1,200 hosts the same way, and Slack and Shopify both moved to Vitess. All four are on the Stories page. How to host MySQL on-prem, on EC2, in Kubernetes, or managed is on the Cloud page.
One command. It tells you how close the redo log is to stalling writes.