MySQL Architecture

Next: monitoring-in-a-reliability-engineering-world

The architecture for MySQL looks like this:

The first layer handles connection pooling.

The second layer handles parsing the SQL given into an optimized form.

The third layer contains the storage engines. Storage engines have different drawbacks, but the most popular one in MySQL is InnoDB.

Optimizing

The optimizer might rewrite the query, determine the order it will read tables, which indexes to use, and so on. Hints may be provided to the Query optimizer.

One note: MySQL used to use a query cache to serve results. With the increase in concurrency, though, the query cache was deprecated in MySQL 5.7.20. This pattern is useful, so caching can be done in Memcached or Redis.

Concurrency Control

Read/Write Locks

Multiple readers are not problematic. There can be infinite readers as long as there are no writers.
If there is one writer, however, on every write, each read can be corrupted.
To handle this, we use Read/Write locks. Writers block all readers and writers, and readers block nobody. All readers get a shared lock, while all writers get an exclusive lock, which block the readers as well.

Lock Granularity

We don’t want to lock a whole table if we only want to edit a row.

MySQL storage engines offer a few solutions:

Table locks

Lock the whole table

Row locks

Row locks are implemented in the storage engine, and allow for multiple writers to a table, as long as they edit unique rows. They come with heavy overhead though, since R/W locks must be maintained for each row, and getting a lock can be very expensive.

Transactions

Transactions allow us to batch reads and writes, and follow ACID principles

Isolation Levels

Read Uncommitted: Readers can read from uncommitted transactions
Read Committed: Readers can read from committed transactions
Repeatable Read: Any read done after another will not have phantom reads
Serializable: All transactions must be partially ordered.

Transaction Logging

MySQL uses a Write ahead log, which means appending to durable storage, and then updating its in-memory representation. Afterwards, it will persist the change to disk. If MySQL crashes in between, it can redo the change by fetching it from the WAL.

Transactions in MySQL

By default, all commands are wrapped in a transaction and committed immediately.

To create a transaction, disable the AUTOCOMMIT variable by setting it to 0, or use BEGIN or START TRANSACTION.

Certain commands that affect the DDL (ALTER TABLE, LOCK TABLES) are immediately committed.

The isolation level can be changed by using SET TRANSACTION ISOLATION LEVEL.

Note: Since transactions are managed by storage engines, not MySQL, mixing storage engines during transactions can cause lost data during rollbacks.

You can explicitly lock a table in two-phase commit by using SELECT ... FOR SHARE or SELECT ... FOR UPDATE.

Multiversion Concurrency Control

MVCC is used in tandem with Row-level locking mechanisms for increasing concurrency. MVCC takes snapshots of the data as it existed in some point in time for writes. Thus, all reads can read the data that corresponds to the time they are in, so there’s no need for locks (since readers dont block writers or vice-versa). This comes with the caveat that MVCC only works with READ COMMITTED and REPEATABLE READ. SERIALIZABLE is not allowed because reads lock every row to create ordered reads.

Replication

Since MySQL is designed to accept writes on one node, the default way to increase reliability is through replication, which involves a leader node replicating writes to all the other nodes through binary log shipping.

Datafiles

In MySQL 5.X, you can query the information_schema to get data about tables. In MySQL 8.X+, all that information has been included in the tables .ibd file.

InnoDB

InnoDB is the default engine, which performs well for processing short-lived transactions that mainly complete instead of being rolled back. It uses MVCC, defaults to REPEATABLE READ, and prevents phantom reads by using a next-key locking strategy.

Its tables use a clustered index, so it provides fast primary key lookups, but secondary indexes contain the primary key columns, so secondary indexes are generally very large.

It supports hash indexes, read-ahead prefetching data from disk, and insert buffers for faster inserts.

It supports hot online backups through a variety of services as well.

MySQL 5.6 allowed for in place updates of tables as well.

MySQL 8 introduced atomic data definition changes. In MySQL 5, if DDL statements partially finished in a transaction, they would be partially applied.