The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Storage and recovery

Buffer pool and pages

Why does reading one row read a whole 16KB page?

InnoDB reads and writes data on disk in blocks called pages (16KB by default), not row by row. A page that has been read is kept in memory in the buffer pool, and later reads come from memory instead of going to disk. When a row is changed, only the page in memory is changed, and it is written to disk later.

Partly checked against real execution · On MySQL 8.4.11, disk reads and writes were checked by execution for the six combinations in which nothing is evicted. The number of slots and the order of eviction are a conceptual model

Concepts in this lab

Buffer pool

The memory area where InnoDB keeps the pages it has read from disk. If a needed page is in the buffer pool, it is read from memory without going to disk. If it is not, it is read from disk and put into the buffer pool. The size is set with innodb_buffer_pool_size, and when the pool is full, pages that have not been used for a long time are evicted to make room. A disk is much slower than memory, so giving the buffer pool enough size to hold the frequently used data has a large effect on performance.

Page (InnoDB)

The basic unit in which InnoDB stores, reads, and writes data on disk. The default size is 16KB, and one page holds several rows together. Even when only one row is needed, the whole page that holds it is read, so the other rows on the same page are loaded into memory as well. Indexes are stored in pages too, not only the rows of a table. For this reason, reading rows that are stored close together reads less from disk, while reading scattered rows reads a different page for each row.

Dirty pages and flushing

A dirty page is a page in the buffer pool whose content has been changed but has not yet been written to the data file on disk. When InnoDB changes a row, it changes only the page in memory, and a background thread collects the changed pages and writes them to the data file later. This writing is called flushing. A commit does not wait for flushing, and if the server goes down in the meantime, the changes are applied again from the redo log to recover. To evict a dirty page from the buffer pool, it must first be written to disk.

Eviction policy

The rule that decides which data to remove when the memory of a cache is full. Examples are LRU, which removes the least recently used data, and LFU, which removes the least frequently used data. In Redis, you set it with the maxmemory-policy setting.

Undo log and redo log

An undo log records data as it was before a change. It is used to roll back a transaction, and in InnoDB it is also used to show past versions to other transactions (MVCC). A redo log records the changes themselves. If a failure occurs before committed changes are fully written to the data files, the changes are applied again at restart to recover them.

Try these

• Default settings — Row 2 is read for the first time, but the disk is not accessed. A commit does not write to the data file right away.
• Four pages in three slots — There is not enough room, so the page of row 1 is evicted, and the disk is accessed again when that page is read again.
• With plenty of slots — Every page that was read stays, so the last query is answered from memory.
• Frequently read pages stay — The page that is evicted is the one that has gone unused the longest.
• When a changed page is evicted — It must be written to disk first, and only then can it be removed from the buffer pool.
• With only two slots — Almost every query goes to disk.

Running with the default settings

1. The 16 rows of the item table are stored in the data file on disk in four pages (A, B, C, and D). A page is 16KB and holds 4 rows. The buffer pool (memory) does not hold any page of this table yet.
2. SELECT … WHERE id = 1: row 1 is in page A. Page A is not in the buffer pool, so the whole page A (16KB) is read from disk and placed in a free slot. Rows 2, 3, 4 on the same page are loaded into memory with it.
3. UPDATE item SET name = 'changed' WHERE id = 1: Row 1 is modified in page A, which is in the buffer pool. The disk is neither read nor written. A page whose content in memory now differs from the disk is called a dirty page.
4. COMMIT: Only the record of what was changed (the redo log) is written to disk, and the commit is complete. Page A stays in the buffer pool in the dirty state, and it has not been written to the data file. Writing to the data file is postponed until later.
5. SELECT … WHERE id = 2: row 2 is in page A. Page A is already in the buffer pool, so it is read directly from memory without going to disk. This is the first time row 2 is read, but it was loaded along with the page when another row on the same page was read.
6. SELECT … WHERE id = 1: row 1 is in page A. Page A is already in the buffer pool, so it is read directly from memory without going to disk. The modified value changed is read. The data file on disk still has the old value item1.
7. A little later: a background thread writes dirty page A to the data file. The page stays in the buffer pool, and only its dirty mark is cleared. InnoDB decides when to write, and the commit does not wait for it.

Result of this run

• InnoDB reads from disk in units of pages, not rows. Reading row 1 also loads rows 2, 3, and 4 of the same page into memory, so reading rows that are stored close together does not go back to disk.
• Even when a row is changed, only the page in memory changes, and the data file is written later. It is the redo log that keeps committed changes from being lost if the server goes down in the meantime (see the "Undo and redo logs" lab).

Code

-- item table: 16 rows. One row is about 3.5KB, so 4 rows fit in one 16KB page
--   Page A: rows 1-4    Page B: rows 5-8
--   Page C: rows 9-12   Page D: rows 13-16
-- At the start, the buffer pool holds no pages of this table

SELECT name FROM item WHERE id = 1;

START TRANSACTION;
UPDATE item SET name = 'changed' WHERE id = 1;
COMMIT;

SELECT name FROM item WHERE id = 2;
SELECT name FROM item WHERE id = 1;

Assumptions of this model

This is a conceptual model built from a buffer pool with only 2 to 4 slots and the simple rule "evict the page that has gone unused the longest". The real InnoDB uses a variation of LRU that divides the list into a new sublist and an old sublist and inserts a newly read page into the middle of the list, and dirty pages are written in advance by the background page cleaner threads, which scan the tail end of the list (MySQL 8.4 Reference Manual, 17.5.1 Buffer Pool and 17.8.3.5 Configuring Buffer Pool Flushing). A real query also reads the upper pages of the index in addition to the page that holds the row. The number of slots and the order shown on screen are not the same as in a real system. What was actually checked is as follows (MySQL 8.4.11 on the PC used to write this lab). The page size was 16384 bytes, and the buffer pool of 134217728 bytes held 8192 pages. I created a table of 16 rows with about 3.5KB per row and confirmed that each leaf page held 4 rows. Then I ran the six combinations in which nothing is evicted (two same-page orders and four cases with 4 slots) in the same order as on the screen, and compared, for each query, whether the data file of that table was read. File reads and Innodb_buffer_pool_reads increased only on a page read for the first time (3 for the first query, because it also reads the upper pages of the index, and then 1 for each new page), and were 0 when another row of an already loaded page, or the same row, was read. Right after UPDATE and COMMIT there were 0 file reads and writes and 1 dirty page, and the data file was written later (in some cases it was written automatically after about 5 seconds, and in another case it had still not been written after 20 seconds, so writing was forced for that table only). The behavior when the buffer pool is full was checked by inserting tens of MB into another table. The free pages of the buffer pool dropped to around 20, and the 24 pages of the six tables that had not been touched in the meantime had all disappeared from the buffer pool and were read from disk when read again, while the 4 pages of the table that kept being read stayed. The eight combinations in which pages are evicted because of too few slots could not be run as they are, since the number of slots of the real buffer pool cannot be changed, so only the reads before eviction were checked. The order in which the page that has gone unused the longest is evicted follows the description in section 17.5.1 of the documentation. That a dirty page can be evicted only after it is written to disk is known as InnoDB behavior, but this time it was not directly confirmed, either by execution or by a sentence in the documentation. Because the server is shared with other people, the global status values may include activity from other sessions.

Previous lab: Undo and redo logs · Next lab: Replication lag and CAP