High Performance MySQL [Ch.1: MySQL Architecture and History]
High Performance MySQL
- MySQL is three layers: connection handling, a server that parses and plans, and pluggable storage engines underneath.
- The optimizer never touches disk. It asks the engine for rows through a narrow API, one row at a time.
- Concurrency is a trade between lock granularity and bookkeeping. InnoDB pays for row locks and MVCC so readers rarely wait.
- Isolation levels decide which anomalies you accept. MySQL defaults to REPEATABLE READ, not READ COMMITTED.
Three layers, one boundary that matters
Picture a MySQL server as a stack. On top sits the plumbing any network service needs: accepting connections, authenticating them, handing each one a thread. In the middle is the part people mean when they say "MySQL": the parser, the optimizer, built-in functions, and everything that works the same regardless of how data is stored, such as views, triggers and stored routines.
At the bottom are storage engines. They store and fetch rows and nothing else; they do not parse SQL. The server talks to them through the handler API, a small vocabulary of calls like "position on this key", "give me the next row" and "insert this row". The engine is chosen per table, not per server, which is why one database can technically mix engines.
That handler API is exactly what QueryCrack measures. Every Handler_read_* counter is one of those calls from the server into InnoDB.
Connections and privileges
Each client connection is served by one thread inside the server process. Creating threads is not free, so servers and drivers keep them around: a thread cache on the server side, connection pools on the client side. Pools also act as a throttle, capping how many clients can hit the database at once.
Authentication (user, host, password, TLS certificate) only lets a connection in. What it may do afterwards is a separate decision made by the privilege system, down to the level of individual tables and columns. Granting a user read-only access to a single schema is an everyday use of it.
From SQL text to a plan
A query is parsed into a tree, checked, and then handed to the optimizer, which may rewrite it, reorder joins and choose indexes. The optimizer does not know how the engine lays out bytes; instead it asks the engine for statistics and cost estimates, and plans from those. EXPLAIN shows the result, and optimizer hints let you overrule it when you know better.
Older editions of the book describe a query cache that returned stored results for identical SELECT text. It scaled poorly under writes and was removed in MySQL 8.0, so caching results is now the application's job.
Locks and granularity
Any shared resource with concurrent readers and writers needs rules. The classic pair is a shared (read) lock, which many readers can hold at once, and an exclusive (write) lock, which blocks everyone else. Correctness is easy if you lock everything; performance comes from locking as little as possible.
| GRANULARITY | TRADE-OFF |
|---|---|
| Table locks | Cheap to manage, but one writer stalls every reader of the table. Taken by the server layer, and still used by many ALTER TABLE operations. |
| Row locks | Maximum concurrency, at the cost of tracking many locks. Implemented by the engine (InnoDB), not the server. |
InnoDB's row locks are subtler than "one row per statement". To prevent rows from appearing inside a range another transaction has already read, it also locks the gaps between index entries (gap and next-key locks). Two inserts into neighbouring gaps can therefore wait on each other, and occasionally deadlock.
Transactions and ACID
A transaction groups statements so they succeed or fail together. Moving stock from one warehouse row to another is two UPDATEs; without a transaction, a crash between them loses inventory. ACID names the guarantees that make this safe:
- Atomicity: all statements apply, or none do.
- Consistency: the database moves from one valid state to another.
- Isolation: in-flight changes are hidden from other transactions to a configurable degree.
- Durability: once committed, a change survives a crash. How strong that promise is (flush on every commit, replicate before acknowledging) is a cost you choose.
Deadlocks are a normal part of life with transactions. InnoDB detects cycles and rolls back the transaction that is cheapest to undo; applications should be ready to retry.
Isolation levels
| LEVEL | WHAT YOU CAN SEE |
|---|---|
READ UNCOMMITTED | Other transactions' uncommitted changes ("dirty reads"). Rarely worth it. |
READ COMMITTED | Only committed data, but the same query can return different rows twice in one transaction. The default in PostgreSQL. |
REPEATABLE READ | A stable snapshot for the whole transaction. MySQL's default; InnoDB's gap locks also block most phantom rows. |
SERIALIZABLE | Plain reads take shared locks, so transactions behave as if run one after another. Correct, and prone to lock waits. |
MVCC: readers that don't block
Row locks alone would still make readers wait on writers. InnoDB avoids most of that with multiversion concurrency control: a plain SELECT reads from a consistent snapshot instead of taking locks. Every row version records the transaction that wrote it, and older versions are reconstructed from the undo log when a snapshot needs them. Two transactions can therefore see different contents of the same table at the same moment, and both be right.
The price is storage and cleanup: undo history has to be kept until no snapshot needs it, which is why a transaction left open for hours makes the server slower for everyone. MVCC applies to READ COMMITTED and REPEATABLE READ; the other two levels do not use snapshots in the same way.
Storage engines: InnoDB and the rest
InnoDB is the default and the right answer almost always: transactional, crash-safe, row-locking, with online backups. It stores each table inside its primary-key B-Tree (a clustered index), so primary-key lookups are fast and every secondary index carries a copy of the primary key. Keep primary keys small.
MyISAM, the historical alternative, has table-level locks, no transactions and poor crash recovery. Its old selling points, full-text search and compressed read-only tables, are now covered by InnoDB too. When choosing an engine, the questions are the same as ever: do you need transactions, online backups and fast crash recovery? If yes, it is InnoDB. Mixing engines in one database is possible but invites strange edge cases.
How this shows up in QueryCrack
Each challenge runs on InnoDB through a connection that belongs to a least-privilege user: SELECT on one schema, nothing else. That privilege is enough to inspect the schema, so Run also accepts SHOW INDEX, DESCRIBE and EXPLAIN. The grader reads the handler counters before and after your query, so what you are optimizing is the number of calls the server layer makes into the storage engine. A clustered primary key makes some of those calls very cheap, which is the whole trick behind count_em_bugs.
Common questions
Is the optimizer part of InnoDB?
No. It lives in the server layer and works for any engine. It only asks the engine for statistics and for rows.
Why does an idle open transaction hurt performance?
Its snapshot pins old row versions in the undo log. Purge cannot discard them, the history grows, and reads have to walk longer version chains.
Should I change the isolation level for speed?
Only knowingly. READ COMMITTED reduces gap locking and can cut lock waits, but it changes what your queries are allowed to see. Measure first, and check the code that depends on repeatable reads.
Ten million rows, no index on the column you filter by, and a clustered primary key that quietly orders everything. Find the cheap path.
Source: Schwartz, Zaitsev and Tkachenko, High Performance MySQL, chapter 1 — MySQL Architecture and History, with notes updated for MySQL 8. These are our notes, not a substitute for the book. Chapter selection follows Sayed Alesawy's reading series.