Database Topic Archive
InnoDB internals, replication, locking, query performance, and MySQL operations notes.
On a binlog-enabled MySQL server, every durable InnoDB commit is written to two logs and synced to storage at least twice. Here is how binlog group commit amortizes that cost, what the 1,1 settings really guarantee versus 2,0, and where…
PK-less InnoDB tables silently tax replication and tooling. GIPK mode fixes them at CREATE TABLE time, if you know what my_row_id changes and what it quietly breaks.
Two sessions inserting different rows can still deadlock under REPEATABLE READ. Gap locks, next-key locks, insert intention locks, and how to read the deadlock report.
Foreign keys feel free until the parent row gets hot. InnoDB's FK checks take shared locks on the parent, cascades multiply your lock footprint, and the contention never names the FK as the cause.
Doublewrite really does write every flushed page twice, and the honest cost lands somewhere between unmeasurable and painful depending on your flush pattern. Here is how to measure it on your own hardware instead of quoting folklore.
A full disk turns MySQL into a stalled or aborted server, and the wrong rm makes it unrecoverable. This is the playbook: what actually grows, what you can purge safely, and the emergency order of operations.
ORDER BY col1 ASC, col2 DESC forced a filesort for two decades of MySQL. Since 8.0, a composite with a real descending part resolves it straight from the index — if the directions match exactly.
The ORM-generated subquery in FROM looked harmless until it materialized two million rows into a temp table. How MySQL decides between merging and materializing a derived table, and the rewrites that actually fix it.
Raising max_connections is the most common MySQL tuning move and usually the wrong one. Here is how thread-per-connection actually costs you, why Threads_running is the real signal, and how to size app pools and ProxySQL with actual math.
The CLONE plugin turned replica provisioning from a multi-tool XtraBackup ritual into two SQL statements. Field notes on setup, the remote clone gotchas, and how to watch progress while it runs.