Previously, we looked at MySQL's InnoDB Architecture and its components.
Building on that, this post explains how MySQL creates Connections, processes user requests, and how its main Threads work, assuming the InnoDB storage engine.
Connection creation
MySQL creates and handles Connections the same way regardless of the storage engine, not only with InnoDB.
MySQL Server runs as a single OS process, with multiple Threads working concurrently inside it. MySQL has no Thread implementation of its own, so it relies on the OS's Thread management.
When MySQL creates a Connection, the Connection Pool Layer allocates a Thread from the Thread Cache to build a User Session. Each Session (Thread) can run Query operations, and the Thread is kept until the user (Client) releases the Connection.
The figure below shows how a Client requests a Connection from MySQL Server and is allocated a Thread. The Client sends a Connection request to MySQL Server, and the request is simply a TCP/IP connection message.
1. The Client's Connection request is added to a Queue called Connection Requests and processed in order by the Receiver Thread. The Receiver Thread's job is to create a User Thread; the created User Thread then handles the user's subsequent requests.
2. The Receiver Thread creates the User Thread either by creating a new OS Thread or by reusing an unused OS Thread from the Thread Cache.
3. The User Thread checks the Client's privileges and credentials, then receives and processes the Client's request (Query).
📢 THD is an object of the THD Class allocated for each Connection (Thread). It maps 1:1 to a Thread, holds meta information such as the running Query and Open Tables, and is defined in sql_class.h. This object (THD) lives from when the Connection is established until it ends, and is not reused. Queries run on one Connection must use the same THD, and the memory used varies with the Query being run.
4. The User Thread processes the request (Query) through the SQL processing steps and, if needed, sends the results to the Client in several parts.
5. When the work is done, the User Thread waits for the next request.
6. When the Client sends a
COM_QUIT command or the sockets on both sides close, the Connection is released. When the Connection drops, the THD is deallocated and the User Thread is returned to the Thread Cache. If the Thread Cache has no free space, the Thread is terminated.
User request processing
When processing a user request (Query/Command), the User Thread fetches Data from MySQL's Data buffers or caches. If the Data is not in a buffer or cache, it reads the Data directly from files on disk.
User requests fall broadly into simple reads (SELECT) and changes (DML). Both go through the 2 steps below, and changes go through additional steps (3-5).
1. The User Thread searches MySQL's InnoDB Buffer Pool for the Data Page it needs.
2-1. If the Page is not in the Buffer Pool, it accesses the Tablespace file on disk and caches the Data into the Buffer Pool.
2-2. The Page cached in the Buffer Pool is returned to the User Thread. (If the Page is already in the Buffer Pool, step 2-1 is skipped and it is returned straight from memory.)
3. If the Page needs to be changed (DML), it is modified inside the Buffer Pool.
4-1. After the Page is modified, a Redo Log record is written to the Log Buffer.
4-2. When a transaction Commits or the Log Buffer fills up, it is Flushed to disk (Redo Log File).
5. Modified Pages in the Buffer Pool (Dirty Pages) are Flushed to disk through the checkpoint process described next.
CHECKPOINT
When a checkpoint occurs, changed Pages in the Buffer Pool are written down to disk to synchronize them. In the InnoDB storage engine, checkpoints fall into two types: Sharp Checkpoint and Fuzzy Checkpoint.
Sharp Checkpoint
It Flushes all Dirty Pages of Committed transactions to disk and records the LSN (Log Sequence Number) of the most recently Committed transaction. InnoDB performs a Sharp Checkpoint when the Database shuts down normally or when the Redo Log files must wrap around and be reused.
Fuzzy Checkpoint
Fuzzy Checkpoint is the commonly used checkpoint. Instead of Flushing all Dirty Pages to disk, it Flushes them to disk a little at a time and tracks the position. It completes by recording two LSNs (start point and end point).
Fuzzy Checkpoints occur in the following cases.
- Master Thread Checkpoint
The Master Thread checks the Flush List periodically, every 1 second or 10 seconds, and writes a certain proportion of Dirty Pages down to disk. This runs asynchronously.
- FLUSH_LRU_LIST Checkpoint
When the number of Free Pages drops to about 100 or fewer, meaning free space is short, it scans a set amount of the LRU List starting from the Old area and runs a checkpoint if there are Dirty Pages. Because the checkpoint target Pages come from the LRU List, this type is called FLUSH_LRU_LIST. Since version 5.6, it is performed by the Page Cleaner Thread, which scans the LRU List up to
innodb_lru_scan_depth.- Async/Sync Flush Checkpoint
This checkpoint type occurs when the Redo Log File is almost full and cannot be used. Some Pages must be forcibly Flushed to disk, after which the Redo Log File can be reused. Since MySQL 5.6, it is performed by the Page Cleaner Thread.
- Dirty page too much Checkpoint
This checkpoint type runs when there are too many Dirty Pages. It checks the ratio of Dirty Pages to Buffer Pool space and runs a checkpoint when the ratio reaches a certain value, which you can set with the
innodb_max_dirty_pages_pct System Variable.📢 The Buffer Block List types are as follows.
- Free List : List that manages Free Page information
- LRU List (Least Recently Used) : List of all Pages (managed by LRU)
- Flush List : List that manages changed Pages not yet written to disk
InnoDB Workflow
The Query Execution Engine calls the storage engine interface to run the Query according to the execution plan and process transactions.
📢 The numbers (1-7) in the figure above show the order of the overall process. We split it into the processing of changed Data (left side of the figure, Data Flow) and the processing of transaction logs (right side of the figure, Log Flow), and explain each.
Storing changed data
1-1. First, check whether the Data matching the Query condition is in the Buffer Pool.
1-2. If the Data is not in the Buffer Pool, an IO Thread loads it from the Data file on disk into the Buffer Pool.
2. Before changing the Data, the current Data is backed up.
- Storing the Data in the Undo Log makes transaction Rollback possible.
- Unneeded Undo Logs are removed by the Purge Thread.
3. After creating the Undo Log, the Data in the Buffer Pool is changed according to the Query. The changed Data is not Flushed to disk right away, and the changed Page is called a Dirty Page.
7. Later, only when a checkpoint is triggered by certain conditions is the Data in Dirty Pages Flushed to disk.
- First, the Pages changed in the Buffer Pool are gathered in the Doublewrite Buffer and written at once.
- Finally, each changed Page in the Buffer Pool is written to the Data file individually.
📢 The Doublewrite Buffer is an area that stores Pages Flushed from the Buffer Pool before they are written to the Data file. Despite the name Buffer, it actually lives on disk. If a problem occurs while writing a Page to the Data file, the Page can be recovered from the Doublewrite Buffer.
Transaction log management (Redo Log & Binlog)
InnoDB uses a write-ahead logging (WAL: Write Ahead Log) strategy, so it first records the Redo Log (the contents of the transaction) in the Log Buffer before writing Data down. It also writes the Binlog (including Data changes, elapsed time, and so on) for use in replication setups and point-in-time Data recovery.
When Redo Log and Binlog are Written&Flushed depends on System Variable values:
innodb_flush_log_at_trx_commit for the Redo Log and sync_binlog for the Binlog.To solve data consistency issues between the Redo Log and Binlog, MySQL uses 2PC (Two-Phase Commit Protocol), split into Prepare and Commit.
4-1. When Data in the Buffer Pool changes, a Redo Log record is created and added to the Log Buffer.
4-2. After the Redo Log is written, the transaction enters the prepare state, in which it can Commit at any time, and the Redo Log in the Log Buffer is Flushed to the Redo Log File on disk.
5-1. Then the Binary Log (binlog) for the Data changes is written to the Binlog Cache.
5-2. The written Binlog is synced to disk and the Binlog Cache is cleared.
6. Binlog-related information is recorded in the Redo Log File, then ACK and Commit are performed.
📢 In the OS, Buffer Data in user space generally cannot be written directly to disk and must go through the OS Cache. It is first written to the OS Cache, then Flushed to the log file on disk through a System Call.
System Variable
Parameter | Default Value | Range | Description |
innodb_lru_scan_depth | 1024 | (64-bit) 100 - 2**64-1
(32-bit) 100 - 2**32-1 | • A System Variable that affects the algorithm and heuristics of Flush operations on the InnoDB Buffer Pool.
• Specifies, per Buffer Pool instance, how far down the Buffer Pool LRU Page List the Page Cleaner Thread scans to find Dirty Pages to Flush. |
innodb_max_dirty_pages_pct | 90 | 0 - 99.99 | Makes the Buffer Pool Flush Data so that InnoDB's Dirty Page ratio does not exceed a certain value. |
innodb_flush_log_at_trx_commit | 1 | 0, 1, 2 | • This System variable controls when the transaction's Commit-related logs are Written&Flushed.
• The value changes both the risk of Data loss and I/O performance, so set it based on how important the Data is and on performance needs. |
sync_binlog | 1 | 0 - 4294967295 | • Controls how often MySQL Server syncs the Binary Log to disk.
• 0 : Disables disk syncing. It relies on the operating system to Flush to disk from time to time, like any other file. This gives the best performance but the highest risk of Data loss.
• 1 : Syncs to disk before a transaction Commits. This is the safest setting, but the extra disk I/O can hurt performance.
• N : With a value other than 0 or 1, it syncs to disk after N Binary Commit Groups have been collected. Higher values improve performance but increase the risk of Data loss. |
Related articles
