The buffer pool: LRU, flushing and warm-up

pool_reads_session.py

"""Every page a query needs comes through the buffer pool: the pool's size on this lab host, then the same 40 orders
read twice after a restart, once from an empty pool and once from a warm one.

A throwaway MySQL 8.4.11 server (mysql_session.py) starts with --innodb-buffer-pool-load-at-startup=OFF, so that its
restart really empties the pool. orders.sql makes 1,000 orders of about 7 KB each; the restart clears the pool; and
pool_reads.sql, run twice, reads the pool's two read counters, reads orders 101 to 140 and reads the counters again.
Last, the sys schema's innodb_buffer_stats_by_table view shows how much of the pool the orders table holds now.
"""

from mysql_session import Server

with Server(options=["--innodb-buffer-pool-load-at-startup=OFF"]) as my:
    my.up()
    my.sql("SELECT @@innodb_buffer_pool_size AS pool_bytes, @@innodb_page_size AS page_bytes, "
           "@@innodb_buffer_pool_instances AS instances, POOL_SIZE AS frames "
           "FROM information_schema.INNODB_BUFFER_POOL_STATS")
    my.mysql("orders.sql", flags=("-t",))
    my.restart(show=True)
    my.mysql("pool_reads.sql", note="first read after the restart: the pages are only on disk")
    my.mysql("pool_reads.sql", flags=("-t",), note="the same script again, results only")
    my.sql("SELECT object_name, allocated, data, pages FROM sys.innodb_buffer_stats_by_table "
           "WHERE object_schema = 'shop'", note="what the pool holds of the orders table now")

$ mysql -t -e "SELECT @@innodb_buffer_pool_size AS pool_bytes, @@innodb_page_size AS page_bytes, @@innodb_buffer_pool_instances AS instances, POOL_SIZE AS frames FROM information_schema.INNODB_BUFFER_POOL_STATS"
+------------+------------+-----------+--------+
| pool_bytes | page_bytes | instances | frames |
+------------+------------+-----------+--------+
|  134217728 |      16384 |         1 |   8191 |
+------------+------------+-----------+--------+

$ mysql -t < orders.sql
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| shop.orders | analyze | status   | OK       |
+-------------+---------+----------+----------+

$ mysqld --no-defaults --datadir=./data --bind-address=127.0.0.1 --port=$PORT --socket=mysql.sock --mysqlx=OFF --innodb-buffer-pool-load-at-startup=OFF &

-- first read after the restart: the pages are only on disk
$ mysql -t -vvv --skip-comments < pool_reads.sql
--------------
SELECT note = '' AS empty_note FROM shop.orders WHERE id = 1000
--------------

+------------+
| empty_note |
+------------+
|          0 |
+------------+
1 row in set

--------------
SELECT VARIABLE_VALUE INTO @requests FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
--------------

Query OK, 1 row affected

--------------
SELECT VARIABLE_VALUE INTO @from_disk FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
--------------

Query OK, 1 row affected

--------------
SELECT COUNT(*) AS orders_read FROM shop.orders WHERE id BETWEEN 101 AND 140 AND note <> ''
--------------

+-------------+
| orders_read |
+-------------+
|          40 |
+-------------+
1 row in set

--------------
SELECT r.VARIABLE_VALUE - @requests AS page_requests, d.VARIABLE_VALUE - @from_disk AS read_from_disk,
       ROUND(100 * (1 - (d.VARIABLE_VALUE - @from_disk) / (r.VARIABLE_VALUE - @requests)), 1) AS hit_pct
  FROM performance_schema.global_status AS r, performance_schema.global_status AS d
  WHERE r.VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' AND d.VARIABLE_NAME = 'Innodb_buffer_pool_reads'
--------------

+---------------+----------------+---------+
| page_requests | read_from_disk | hit_pct |
+---------------+----------------+---------+
|            65 |             21 |    67.7 |
+---------------+----------------+---------+
1 row in set

Bye

-- the same script again, results only
$ mysql -t < pool_reads.sql
+------------+
| empty_note |
+------------+
|          0 |
+------------+
+-------------+
| orders_read |
+-------------+
|          40 |
+-------------+
+---------------+----------------+---------+
| page_requests | read_from_disk | hit_pct |
+---------------+----------------+---------+
|            44 |              0 |     100 |
+---------------+----------------+---------+

-- what the pool holds of the orders table now
$ mysql -t -e "SELECT object_name, allocated, data, pages FROM sys.innodb_buffer_stats_by_table WHERE object_schema = 'shop'"
+-------------+------------+------------+-------+
| object_name | allocated  | data       | pages |
+-------------+------------+------------+-------+
| orders      | 384.00 KiB | 315.03 KiB |    24 |
+-------------+------------+------------+-------+

orders.sql

-- The lesson's small table: 1,000 orders whose 7,000-character notes fill a 16 KB page about two rows at a time.
-- ANALYZE TABLE writes the table's statistics now, so no background update of them reads pages later.
CREATE DATABASE shop;
CREATE TABLE shop.orders (id INT PRIMARY KEY, note VARCHAR(7000) NOT NULL) ENGINE=InnoDB;
INSERT INTO shop.orders (id, note)
  WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
  SELECT n, REPEAT('o', 7000) FROM seq;
ANALYZE TABLE shop.orders;

pool_reads.sql

-- Orders 101 to 140 (40 rows on about 20 leaf pages), with the pool's two read counters taken before and after.
-- The first statement opens the table, so the dictionary pages it needs are not counted in the measured read.
SELECT note = '' AS empty_note FROM shop.orders WHERE id = 1000;
SELECT VARIABLE_VALUE INTO @requests FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests';
SELECT VARIABLE_VALUE INTO @from_disk FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads';
SELECT COUNT(*) AS orders_read FROM shop.orders WHERE id BETWEEN 101 AND 140 AND note <> '';
SELECT r.VARIABLE_VALUE - @requests AS page_requests, d.VARIABLE_VALUE - @from_disk AS read_from_disk,
       ROUND(100 * (1 - (d.VARIABLE_VALUE - @from_disk) / (r.VARIABLE_VALUE - @requests)), 1) AS hit_pct
  FROM performance_schema.global_status AS r, performance_schema.global_status AS d
  WHERE r.VARIABLE_NAME = 'Innodb_buffer_pool_read_requests' AND d.VARIABLE_NAME = 'Innodb_buffer_pool_reads';

mysql_session.py

"""Throwaway MySQL 8.4.11 servers for the examples of this lesson.

Every session creates a server of its own: `mysqld --initialize-insecure` makes a new data directory inside a new
folder in the examples folder, the server starts on it, the session runs `mysql` client scripts and MySQL's programs
against it and prints what they printed, and then the server is stopped and the folder deleted, also when something
fails. The server is the pinned MySQL 8.4.11 from the mise installations next to the Python that runs this file;
Server(basedir=...) runs the programs of another unpacked MySQL tarball instead (dump_defaults.py uses it).

Safety: the server listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system
reports as unused; the X Protocol listener is off (--mysqlx=OFF); its Unix socket, temporary files, error log and
import/export folder all stay inside the run folder. Right after the first start the session gives root a password made
up for the run (never printed) and connects only with a client option file that holds it, so no password ever appears
on a command line. Every program starts with --no-defaults or --defaults-file, so no option file of the computer
(/etc/my.cnf, ~/.my.cnf) can change a result, and HOME points at the run folder, so ~/.mylogin.cnf is not read either.
A printed mysqld command shows the options that matter to the lesson; the session also passes, on the command line,
the folders it keeps inside the run folder (--basedir, --tmpdir, --log-error, --pid-file, --secure-file-priv) and
the port, which is why performance_schema.variables_info reports those variables as COMMAND_LINE.
learn:verify's run sandbox cannot read the home folder, where mise keeps MySQL, so these sessions are recorded with
LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with these substitutions so that a record never shows the computer it
ran on and is the same on every run: the run folder is shown as ".", the folder of MySQL's programs is left out of
their paths and MySQL's own folder is shown as <basedir>, the computer's name is shown as "lab-host" and the operating-system user's name as "learner", the time a
statement took (the client's "(0.01 sec)") is left out, the time stamp SET PERSIST writes into mysqld-auto.cnf is
shown as <when it was set>, error-log lines lose their timestamp and thread number, and a session can name more values
that change between runs with my.mask(pattern, replacement), which then prints in their place. A client table whose cells
change is drawn again so that its borders still line up.
The sessions never print the password, and the run's port prints as <port>. A session stops with status 1 when a command that should have worked
fails, or when a script reports a different number of errors than the session expects, so a run that went wrong is
never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

MYSQL_VERSION = "8.4.11"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
PINNED = INSTALLS / "mysql" / MYSQL_VERSION
OS_USER = pwd.getpwuid(os.geteuid()).pw_name
HOST = socket.gethostname().split(".")[0]
# 2026-10-17T16:50:01.123456Z 0 [ERROR] ... -> [ERROR] ...
LOG_PREFIX = re.compile(r"^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d\.\d+Z \d+ ", re.M)


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway server: initialise, start; stopped and deleted when the with block ends.

    options: extra mysqld options for every start (for example "--sql-mode=" or "--character-set-server=latin1").
    basedir: the folder of an unpacked MySQL tarball to run instead of the pinned 8.4.11.
    masks: (pattern, replacement) pairs, as for mask().
    """

    def __init__(self, options=(), redo=None, basedir=None, masks=()):
        self.options = [str(o) for o in options]
        self.redo = redo
        self.basedir = Path(basedir).resolve() if basedir else PINNED
        self.bin = self.basedir / "bin"
        self.masks = []
        for pattern, replacement in masks:
            self.mask(pattern, replacement)
        self.proc = None

    def mask(self, pattern, replacement):
        """From now on every printed line shows text matching the regular expression as the replacement: for a value
        that changes from run to run (an uptime, a time stamp), so that the recorded output stays the same."""
        self.masks.append((re.compile(pattern), replacement))

    # ---- the folder ------------------------------------------------------------------------------------------------
    def __enter__(self):
        if not (self.bin / "mysqld").exists():
            sys.exit(f"no MySQL server program in {self.basedir}" if self.basedir != PINNED
                     else f"MySQL {MYSQL_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="my", dir=".")).resolve()
        for sub in ("tmp", "files"):
            (self.base / sub).mkdir(mode=0o700)
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        self.client_cnf = self.base / "client.cnf"
        self._write_client_cnf(password=None)
        self.env = {key: value for key, value in os.environ.items()
                    if not key.startswith("MYSQL") and key not in ("TZ", "HOME", "LANG", "LC_ALL")}
        self.env.update({"HOME": str(self.base), "TZ": "UTC", "LANG": "en_US.UTF-8",
                         "MYSQL_TEST_LOGIN_FILE": str(self.base / "no-login-paths.cnf")})
        for pattern in ("*.sql", "*.cnf", "*.csv", "*.txt"):
            for f in Path(".").glob(pattern):
                shutil.copy(f, self.base / f.name)
        return self

    def __exit__(self, *exc):
        if self.proc and self.proc.poll() is None:
            self.stop(quiet=True)
        shutil.rmtree(self.base, ignore_errors=True)

    def _write_client_cnf(self, password):
        lines = ["[client]", "user=root", "host=127.0.0.1", f"port={self.port}", "protocol=TCP"]
        if password:
            lines.append(f"password={password}")
        self.client_cnf.write_text("\n".join(lines) + "\n")
        self.client_cnf.chmod(0o600)

    def server_options(self):
        b = self.base
        return ["--no-defaults", f"--basedir={self.basedir}", f"--datadir={b / 'data'}", f"--tmpdir={b / 'tmp'}",
                f"--log-error={b / 'mysqld.err'}", f"--pid-file={b / 'mysqld.pid'}",
                f"--secure-file-priv={b / 'files'}",
                *([f"--innodb-redo-log-capacity={self.redo}"] if self.redo else [])]

    # ---- the server ------------------------------------------------------------------------------------------------
    def initialize(self, show=False):
        """mysqld --initialize-insecure: a new data directory whose root account has no password yet."""
        args = ["mysqld", *self.server_options(), "--initialize-insecure"]
        code, out = self._call(args)
        if show:
            _show(["mysqld", "--no-defaults", "--initialize-insecure", "--datadir=./data"], out)
        if code != 0:
            print(self.log())
            sys.exit(f"mysqld --initialize-insecure ended with status {code}")

    def start(self, extra=(), defaults_file=None, expect_fail=False, show=False):
        """Starts mysqld on 127.0.0.1 and waits until it answers. With expect_fail, prints the error-log lines of a
        start that must fail, and returns them."""
        log = self.base / "mysqld.err"
        before = log.read_text() if log.exists() else ""
        opts = self.server_options()
        if defaults_file:
            opts[0] = f"--defaults-file={self.base / defaults_file}"
        args = [str(self.bin / "mysqld"), *opts, "--bind-address=127.0.0.1", f"--port={self.port}",
                "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *[str(e) for e in extra]]
        if show:
            shown = ["mysqld", opts[0].replace(str(self.base) + "/", ""), "--datadir=./data", "--bind-address=127.0.0.1",
                     "--port=$PORT", "--socket=mysql.sock", "--mysqlx=OFF", *self.options, *extra]
            print("$ " + " ".join(_arg(a) if a != "--port=$PORT" else a for a in shown) + " &")
            print()
        with open(self.base / "mysqld.out", "a") as out:
            self.proc = subprocess.Popen(args, stdout=out, stderr=subprocess.STDOUT, cwd=self.base, env=self.env)
        for _ in range(600):
            if self.proc.poll() is not None:
                break
            if self._ping():
                break
            time.sleep(0.1)
        if self.proc.poll() is not None:
            new = (log.read_text() if log.exists() else "")[len(before):]
            lines = [l for l in self.clean(LOG_PREFIX.sub("", new)).splitlines() if "[ERROR]" in l]
            if expect_fail:
                print("\n".join(lines))
                print(f"(mysqld ended with status {self.proc.returncode})")
                print()
                self.proc = None
                return lines
            print("\n".join(lines))
            sys.exit(f"mysqld ended with status {self.proc.returncode} while starting")
        if expect_fail:
            sys.exit("mysqld started, but the session expected it to fail")
        if not self._ping():
            sys.exit("mysqld did not answer within 60 seconds")
        return []

    def _ping(self):
        code, _ = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "--connect-timeout=2", "ping"])
        return code == 0

    def up(self, extra=(), defaults_file=None, show=False):
        """Initialise, start (with start()'s options), and give root the run's password, quietly."""
        self.initialize()
        self.start(extra=extra, defaults_file=defaults_file, show=show)
        return self.login()

    def login(self):
        """Gives root the run's password (never printed) on a server started from a new data directory, and makes
        every later client of the session use it."""
        self.silent(f"ALTER USER 'root'@'localhost' IDENTIFIED BY '{self.password}'")
        self._write_client_cnf(self.password)
        return self

    def stop(self, quiet=False):
        """A clean shutdown (mysqladmin shutdown), then waits until the process has ended."""
        code, out = self._call(["mysqladmin", f"--defaults-file={self.client_cnf}", "shutdown"])
        if code != 0 and not quiet:
            sys.exit(f"mysqladmin shutdown failed: {out}")
        try:
            self.proc.wait(timeout=120)
        except subprocess.TimeoutExpired:
            self.proc.terminate()
            self.proc.wait(timeout=60)
        self.proc = None

    def restart(self, extra=(), defaults_file=None, show=False):
        self.stop()
        self.start(extra=extra, defaults_file=defaults_file, show=show)

    # ---- running things --------------------------------------------------------------------------------------------
    def _call(self, args, stdin=None, merge=True):
        """Runs one of MySQL's programs in the run folder: (status, cleaned output). merge=False keeps only what it
        wrote to standard output (its warnings on standard error are dropped)."""
        args = [str(a) for a in args]
        done = subprocess.run([str(self.bin / args[0]), *args[1:]], stdout=subprocess.PIPE,
                              stderr=subprocess.STDOUT if merge else subprocess.DEVNULL,
                              text=True, cwd=self.base, env=self.env, input=stdin)
        return done.returncode, self.clean(done.stdout)

    def output(self, *args, connect=False, status=0):
        """Runs one of MySQL's programs without printing anything and returns its standard output, for example the
        SQL that mysql_tzinfo_to_sql writes, which the session saves with write_file(..., show=False) and runs next."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run, merge=False)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The substitutions of the docstring. A table of the client whose cells change is drawn again with the
        new column widths, so its borders still line up."""
        lines, out, i = text.split("\n"), [], 0
        while i < len(lines):
            if lines[i].startswith("+-"):
                j = i
                while j < len(lines) and lines[j][:1] in ("+", "|"):
                    j += 1
                block = lines[i:j]
                done = [self._clean(l) for l in block]
                out.extend(done if done == block else _redraw(block, self._clean))
                i = j
            else:
                out.append(self._clean(lines[i]))
                i += 1
        return "\n".join(out)

    def _clean(self, text):
        text = text.replace(str(self.bin) + "/", "").replace(str(self.basedir), "<basedir>")
        text = text.replace(str(self.base) + "/", "./").replace(str(self.base), ".")
        text = text.replace("\x07", "")   # the mysql client rings the terminal bell on an error; drop it
        text = re.sub(r" \(\d+\.\d\d sec\)", "", text)
        text = re.sub(r'"Timestamp": \d+', '"Timestamp": <when it was set>', text)
        text = re.sub(rf"\b{re.escape(HOST)}\b", "lab-host", text)
        text = re.sub(rf"\b{re.escape(OS_USER)}\b", "learner", text)
        text = re.sub(rf"(?<![\d.]){self.port}(?![\d.])", "<port>", text)
        for pattern, replacement in self.masks:
            text = pattern.sub(replacement, text)
        return text

    def log(self, pattern=None):
        """The error log without timestamps and thread numbers, optionally only the lines matching a pattern."""
        text = self.clean(LOG_PREFIX.sub("", (self.base / "mysqld.err").read_text()))
        if pattern:
            text = "\n".join(l for l in text.splitlines() if re.search(pattern, l))
        return text

    def show_log(self, pattern, note=None):
        """Prints the error-log lines that match a pattern, as grep would."""
        print(f"$ grep -E {_arg(pattern)} mysqld.err" + (f"   # {note}" if note else ""))
        print(self.log(pattern))
        print()

    def query(self, sql, db=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        args = ["mysql", f"--defaults-file={self.client_cnf}", "-N", "-B", *([db] if db else [])]
        code, out = self._call(args, stdin=sql)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only for the run's password)."""
        self.query(sql)

    def mysql(self, script, errors=0, status=None, db=None, flags=("-t", "-vvv", "--skip-comments"), note=None, force=None):
        """Runs a client script from standard input and prints the command and its output. `errors` is how many
        ERROR lines the script must print; with errors > 0 the client runs on past an error (--force) and then ends
        with status 0, without --force it stops at the first error with status 1. The default flags print each
        statement (-vvv) as a table (-t) without the script's comments (--skip-comments)."""
        force = errors > 0 if force is None else force
        shown = ["mysql", *flags, *(["--force"] if force else []), *([db] if db else [])]
        args = ["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]]
        code, out = self._call(args, stdin=(self.base / script).read_text())
        if note:
            print(f"-- {note}")
        print("$ " + " ".join(_arg(a) for a in shown) + f" < {script}")
        if out.strip():
            print(out.rstrip("\n"))
        print()
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        expected_status = (1 if errors and not force else 0) if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")
        return out

    def shell(self, *args, status=0, shown=None, connect=False):
        """Runs one of MySQL's programs and prints the command and its output. connect=True gives a client program
        (mysqladmin, mysqlshow, mysql) the run's client option file, which the printed command leaves out."""
        args = [str(a) for a in args]
        run = [args[0], f"--defaults-file={self.client_cnf}", *args[1:]] if connect else args
        code, out = self._call(run)
        _show(shown or args, out)
        if code != status:
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def sql(self, statement, flags=("-t",), errors=0, db=None, note=None):
        """Runs one statement with mysql -e and prints the command and its output."""
        shown = ["mysql", *flags, *([db] if db else []), "-e", statement]
        code, out = self._call(["mysql", f"--defaults-file={self.client_cnf}", *shown[1:]])
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if line.startswith("ERROR "))
        if found != errors or (code != 0) != (errors > 0):
            sys.exit(f"{statement}: {found} errors and status {code}; the session expects {errors}")
        return out

    def write_file(self, path, text, show=True):
        """Writes a text file into the run folder (an option file, a script) and prints it, as cat would."""
        (self.base / path).write_text(textwrap.dedent(text).lstrip("\n"))
        if show:
            self.show_file(path)

    def show_file(self, path):
        print(f"$ cat {path}")
        print(self.clean((self.base / path).read_text()).rstrip("\n"))
        print()

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the run (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"$ ls -p {folder}" + (f" | grep '^{prefix}'" if prefix else ""))
        print(textwrap.fill("  ".join(names), width=78, break_on_hyphens=False))
        print()
        return names


def _redraw(block, clean):
    """Draws a client table again after its cells were cleaned. The columns come from the first border line; the row
    after it is the header (left-aligned); a column whose data cells were padded on the left (numbers) stays
    right-aligned (an empty cell says nothing about its column)."""
    cuts = [k for k, ch in enumerate(block[0]) if ch == "+"]
    rows = [None if line.startswith("+") else [line[x + 1:y] for x, y in zip(cuts, cuts[1:])] for line in block]
    header = next(k for k, r in enumerate(rows) if r is not None)
    ncol = len(cuts) - 1
    right = [any(r is not None and k != header and r[c].strip() and r[c].startswith("  ") for k, r in enumerate(rows))
             for c in range(ncol)]
    cells = [None if r is None else [clean(x.strip()) for x in r] for r in rows]
    widths = [max(len(r[c]) for r in cells if r is not None) for c in range(ncol)]
    drawn = []
    for k, r in enumerate(cells):
        if r is None:
            drawn.append("+" + "+".join("-" * (w + 2) for w in widths) + "+")
        else:
            drawn.append("|" + "|".join(" " + (t.rjust(w) if right[c] and k != header else t.ljust(w)) + " "
                                        for c, (t, w) in enumerate(zip(r, widths))) + "|")
    return drawn


def _arg(a):
    """A command-line argument as a reader would type it: quotes only where the shell needs them."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


# ====================================================================================================================
# Concurrency for the MVCC and locking lessons (undo-logs-mvcc-and-purge, row-gap-next-key-locks).
# Two or more persistent `mysql` clients keep their own transactions open at the same time, so a session can show what
# one transaction sees of another's uncommitted or committed changes, and the record, gap and next-key locks a
# statement takes. One statement can be started without waiting (it blocks on a lock); an observer connection polls
# performance_schema until the wait is visible; the blocked statement then finishes (with its result or its
# ERROR 1205 / ERROR 1213). Output is masked so two runs are byte-identical.
# ====================================================================================================================
import threading
import pty
import termios
import struct
import fcntl

_PROMPT = "READYmk> "   # a distinctive mysql prompt we use as the boundary between statements


class Client:
    """A persistent interactive mysql client on a pseudo-terminal: one connection whose transaction lives across
    calls. Built by Server.client(name). run() sends one statement, waits for the next prompt and prints the result;
    start() sends a statement expected to block and returns at once; finish() waits for that blocked statement.
    A pseudo-terminal is used because the mysql client block-buffers its output to a plain pipe, which would hide the
    result until the process ends; on a terminal it flushes a prompt after every statement. Terminal echo is off (the
    statement is printed by the session, not by the terminal) and the window is made wide so tables never wrap."""

    def __init__(self, server, name):
        self.server = server
        self.name = name
        self.pty_fd, tty_fd = pty.openpty()
        attrs = termios.tcgetattr(tty_fd)
        attrs[3] = attrs[3] & ~termios.ECHO          # lflags: no echo of what we type
        termios.tcsetattr(tty_fd, termios.TCSANOW, attrs)
        fcntl.ioctl(tty_fd, termios.TIOCSWINSZ, struct.pack("HHHH", 100, 1000, 0, 0))  # 1000 columns, no wrap
        args = [str(server.bin / "mysql"), f"--defaults-file={server.client_cnf}",
                f"--prompt={_PROMPT}", "--skip-auto-rehash", "-t"]
        self.proc = subprocess.Popen(args, stdin=tty_fd, stdout=tty_fd, stderr=tty_fd,
                                     cwd=server.base, env=server.env, close_fds=True)
        os.close(tty_fd)
        self._buf = ""
        self._consumed = 0
        self._lock = threading.Lock()
        self._reader = threading.Thread(target=self._read, daemon=True)
        self._reader.start()
        self._read_until_prompt()          # discard the welcome banner up to the first prompt
        self.conn_id = self._value("SELECT CONNECTION_ID()")

    def _read(self):
        while True:
            try:
                data = os.read(self.pty_fd, 4096)
            except OSError:
                break
            if not data:
                break
            with self._lock:
                self._buf += data.decode("utf-8", "replace").replace("\r\n", "\n").replace("\r", "")

    def _send(self, sql):
        os.write(self.pty_fd, (sql.rstrip().rstrip(";") + ";\n").encode())

    def _read_until_prompt(self, timeout=60):
        deadline = time.time() + timeout
        while time.time() < deadline:
            with self._lock:
                idx = self._buf.find(_PROMPT, self._consumed)
                if idx != -1:
                    block = self._buf[self._consumed:idx]
                    self._consumed = idx + len(_PROMPT)
                    return block
            time.sleep(0.02)
        return None

    def _collect(self, sql, timeout=60):
        self._send(sql)
        block = self._read_until_prompt(timeout=timeout)
        if block is None:
            sys.exit(f"{self.name}: statement did not finish within {timeout}s: {sql}")
        return block

    def _value(self, sql):
        text = self.server.clean(self._collect(sql))
        cells = [l for l in text.splitlines() if l.startswith("|")]
        return cells[-1].split("|")[1].strip() if len(cells) >= 2 else ""

    def run(self, sql, note=None, errors=0):
        """Run a statement to completion and print it as the mysql client showed it."""
        out = self._collect(sql)
        self._print(sql, out, note)
        self._check(sql, out, errors)
        return out

    def start(self, sql, note=None):
        """Send a statement expected to block on a lock; return at once. finish() collects its output."""
        self._pending = (sql, note)
        self._send(sql)

    def finish(self, errors=0, timeout=60):
        """Wait for the statement start() sent and print it (its result, or its ERROR 1205/1213)."""
        sql, note = self._pending
        block = self._read_until_prompt(timeout=timeout)
        if block is None:
            sys.exit(f"{self.name}: blocked statement did not finish within {timeout}s: {sql}")
        self._print(sql, block, note)
        self._check(sql, block, errors)
        return block

    def _check(self, sql, out, errors):
        found = sum(1 for l in out.splitlines() if l.startswith("ERROR "))
        if found != errors:
            sys.exit(f"{self.name}: {found} errors on {sql!r}, expected {errors}")

    def _print(self, sql, out, note):
        print(f"-- {self.name}" + (f": {note}" if note else ""))
        print(f"mysql> {sql.rstrip().rstrip(';')};")
        text = self.server.clean(out).strip("\n")
        if text:
            print(text)
        print()

    def close(self):
        try:
            os.write(self.pty_fd, b"\\q\n")
        except OSError:
            pass
        try:
            self.proc.wait(timeout=10)
        except subprocess.TimeoutExpired:
            self.proc.terminate()
        try:
            os.close(self.pty_fd)
        except OSError:
            pass


# ---- Server helpers for the concurrency lessons --------------------------------------------------------------------
def _server_client(self, name):
    """A new persistent connection whose transaction lives across statements."""
    if not hasattr(self, "_clients"):
        self._clients = []
    c = Client(self, name)
    self._clients.append(c)
    return c


def _server_wait_for(self, sql, want, timeout=20):
    """Poll a single-value query (through a short-lived connection) until it returns `want` (as text)."""
    want = str(want)
    deadline = time.time() + timeout
    while time.time() < deadline:
        if self.query(sql) == want:
            return True
        time.sleep(0.05)
    sys.exit(f"wait_for timed out: {sql} never became {want}")


def _server_locks(self, note=None, where="1=1"):
    """Print performance_schema.data_locks, only the columns that do not change between runs, ordered stably."""
    sql = ("SELECT INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA "
           "FROM performance_schema.data_locks "
           f"WHERE OBJECT_SCHEMA='test' AND ({where}) "
           "ORDER BY INDEX_NAME, FIELD(LOCK_MODE,'X','X,GAP','X,REC_NOT_GAP','X,GAP,INSERT_INTENTION','S','S,GAP'), "
           "LOCK_DATA")
    self.sql(sql, note=note)


def _server_status_deadlock(self, note=None):
    """The LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS, trimmed to the parts that do not change
    between runs: the two transactions, the locks each holds and waits for, and which one was rolled back. The
    per-record hex dumps and the run-varying ids, times and thread handles are left out or masked."""
    out = self.output("mysql", "-E", "-e", "SHOW ENGINE INNODB STATUS", connect=True)
    lines = out.splitlines()
    try:
        a = next(i for i, l in enumerate(lines) if "LATEST DETECTED DEADLOCK" in l)
        b = next(i for i in range(a + 2, len(lines)) if lines[i].startswith("-----------"))
    except StopIteration:
        a, b = 1, len(lines)
    kept, i = [], a - 1
    while i < b:
        l = lines[i]
        if l.startswith("Record lock, heap no"):      # skip the record's hex field dump up to the blank line
            while i < b and lines[i].strip():
                i += 1
            continue
        kept.append(l)
        i += 1
    text = "\n".join(kept)
    text = re.sub(r"^\d{4}-\d\d-\d\d \d\d:\d\d:\d\d .*$", "<date and time> <thread>", text, flags=re.M)
    text = re.sub(r"TRANSACTION \d+,", "TRANSACTION <id>,", text)
    text = re.sub(r"ACTIVE \d+ sec", "ACTIVE <n> sec", text)
    text = re.sub(r"heap size \d+", "heap size <n>", text)
    text = re.sub(r"MySQL thread id \d+, OS thread handle \w+, query id \d+",
                  "MySQL thread id <tid>, OS thread handle <h>, query id <q>", text)
    text = re.sub(r"trx id \d+", "trx id <id>", text)
    text = re.sub(r"\n{3,}", "\n\n", text)
    if note:
        print(f"-- {note}")
    print("mysql> SHOW ENGINE INNODB STATUS\\G   (LATEST DETECTED DEADLOCK section)")
    print(self.clean(text).strip("\n"))
    print()


Server.client = _server_client
Server.wait_for = _server_wait_for
Server.locks = _server_locks
Server.status_deadlock = _server_status_deadlock

scan_resistance_session.py

"""A scan larger than the buffer pool, first with the old sublist's one-second rule and then without it.

A throwaway MySQL 8.4.11 server (mysql_session.py) keeps the default 128 MiB pool and starts with
--innodb-buffer-pool-load-at-startup=OFF, so that its restart begins with an empty pool. scan_tables.sql makes three
tables of 7,000-character rows; the restart empties the pool; scan_warm.sql fills it with one read of customers and
reads orders twice, 1.2 seconds apart; scan.sql scans history, which is larger than the pool, and reads where the
orders pages are afterwards. The session runs scan.sql once with innodb_old_blocks_time at its default of 1000 ms and
once more with it set to 0. scan.sql prints the two young-making counters and the unused frames as ranges, because
their exact values change from one run to the next: the frames left unused after a scan depend on whether the page
cleaner has freed frames at the tail of the list since it ended.
"""

from mysql_session import Server

with Server(options=["--innodb-buffer-pool-load-at-startup=OFF"]) as my:
    my.up()
    my.mysql("scan_tables.sql", flags=("-t",))
    my.restart()
    my.sql("SELECT @@innodb_old_blocks_pct AS old_pct, @@innodb_old_blocks_time AS old_ms, "
           "POOL_SIZE AS frames FROM information_schema.INNODB_BUFFER_POOL_STATS")
    my.mysql("scan_warm.sql", flags=("-t",))
    my.mysql("scan.sql", flags=("-t",), note="the scan with the default window of 1000 ms")
    my.sql("SET GLOBAL innodb_old_blocks_time = 0")
    my.mysql("scan.sql", flags=("-t",), note="the same scan with innodb_old_blocks_time = 0")

$ mysql -t < scan_tables.sql
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| shop.customers | analyze | status   | OK       |
| shop.history   | analyze | status   | OK       |
| shop.orders    | analyze | status   | OK       |
+----------------+---------+----------+----------+
+------------+------------+
| table_name | leaf_pages |
+------------+------------+
| customers  |       7601 |
| history    |       8501 |
| orders     |        501 |
+------------+------------+

$ mysql -t -e "SELECT @@innodb_old_blocks_pct AS old_pct, @@innodb_old_blocks_time AS old_ms, POOL_SIZE AS frames FROM information_schema.INNODB_BUFFER_POOL_STATS"
+---------+--------+--------+
| old_pct | old_ms | frames |
+---------+--------+--------+
|      37 |   1000 |   8191 |
+---------+--------+--------+

$ mysql -t < scan_warm.sql
+----------------+
| customers_rows |
+----------------+
|          15200 |
+----------------+
+-------------+
| orders_rows |
+-------------+
|        1000 |
+-------------+
+-------------+
| orders_rows |
+-------------+
|        1000 |
+-------------+
+--------------+----------------+
| orders_pages | in_old_sublist |
+--------------+----------------+
|          502 |              0 |
+--------------+----------------+

-- the scan with the default window of 1000 ms
$ mysql -t < scan.sql
+--------------+
| history_rows |
+--------------+
|        17000 |
+--------------+
+------------------+----------------------+-------------+
| pages_made_young | pages_not_made_young | free_frames |
+------------------+----------------------+-------------+
| under 100        | over 10,000          | 0 to 1,024  |
+------------------+----------------------+-------------+
+--------------+----------------+
| orders_pages | in_old_sublist |
+--------------+----------------+
|          502 |              0 |
+--------------+----------------+

$ mysql -t -e "SET GLOBAL innodb_old_blocks_time = 0"

-- the same scan with innodb_old_blocks_time = 0
$ mysql -t < scan.sql
+--------------+
| history_rows |
+--------------+
|        17000 |
+--------------+
+------------------+----------------------+-------------+
| pages_made_young | pages_not_made_young | free_frames |
+------------------+----------------------+-------------+
| 5,000 to 10,000  | none                 | 0 to 1,024  |
+------------------+----------------------+-------------+
+--------------+----------------+
| orders_pages | in_old_sublist |
+--------------+----------------+
|            0 |              0 |
+--------------+----------------+

scan_tables.sql

-- Three tables of 7,000-character rows, about two rows to a 16 KB page. customers (about 7,600 pages) fills the
-- 128 MiB pool when it is read; orders (about 500 pages) is read often; history (about 8,500 pages) is larger than
-- the whole pool and is read by one scan.
SET SESSION cte_max_recursion_depth = 17000;
CREATE DATABASE shop;
CREATE TABLE shop.customers (id INT PRIMARY KEY, note VARCHAR(7000) NOT NULL) ENGINE=InnoDB;
INSERT INTO shop.customers (id, note)
  WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 15200)
  SELECT n, REPEAT('c', 7000) FROM seq;
CREATE TABLE shop.history LIKE shop.customers;
INSERT INTO shop.history (id, note)
  WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 17000)
  SELECT n, REPEAT('h', 7000) FROM seq;
CREATE TABLE shop.orders LIKE shop.customers;
INSERT INTO shop.orders (id, note)
  WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)
  SELECT n, REPEAT('o', 7000) FROM seq;
ANALYZE TABLE shop.customers, shop.history, shop.orders;
SELECT table_name, stat_value AS leaf_pages FROM mysql.innodb_index_stats
  WHERE database_name = 'shop' AND index_name = 'PRIMARY' AND stat_name = 'n_leaf_pages' ORDER BY table_name;

scan_warm.sql

-- A full pool, as on a busy server: customers read once; then orders read twice, 1.2 seconds apart.
SELECT COUNT(*) AS customers_rows FROM shop.customers WHERE note <> '';
SELECT COUNT(*) AS orders_rows FROM shop.orders WHERE note <> '';
DO SLEEP(1.2);
SELECT COUNT(*) AS orders_rows FROM shop.orders WHERE note <> '';
SELECT COUNT(*) AS orders_pages, SUM(IS_OLD = 'YES') AS in_old_sublist
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`';

scan.sql

-- One scan of history, with the pool's young-making counters taken before and after it, and its unused frames
-- after it. All three are shown in bands, because their exact values change from one run to the next.
SELECT PAGES_MADE_YOUNG, PAGES_NOT_MADE_YOUNG INTO @young, @not_young
  FROM information_schema.INNODB_BUFFER_POOL_STATS;
SELECT COUNT(*) AS history_rows FROM shop.history WHERE note <> '';
SELECT CASE WHEN made = 0 THEN 'none' WHEN made < 100 THEN 'under 100' WHEN made < 5000 THEN '100 to 5,000'
            WHEN made < 10000 THEN '5,000 to 10,000' ELSE 'over 10,000' END AS pages_made_young,
       CASE WHEN refused = 0 THEN 'none' WHEN refused < 100 THEN 'under 100' WHEN refused < 5000 THEN '100 to 5,000'
            WHEN refused < 10000 THEN '5,000 to 10,000' ELSE 'over 10,000' END AS pages_not_made_young,
       CASE WHEN free_now <= @@innodb_lru_scan_depth THEN CONCAT('0 to ', FORMAT(@@innodb_lru_scan_depth, 0))
            ELSE CONCAT('over ', FORMAT(@@innodb_lru_scan_depth, 0)) END AS free_frames
  FROM (SELECT PAGES_MADE_YOUNG - @young AS made, PAGES_NOT_MADE_YOUNG - @not_young AS refused,
               FREE_BUFFERS AS free_now FROM information_schema.INNODB_BUFFER_POOL_STATS) AS counters;
SELECT COUNT(*) AS orders_pages, COALESCE(SUM(IS_OLD = 'YES'), 0) AS in_old_sublist
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`';

flushing_session.py

"""Dirty pages and the page cleaner: the flushing settings of MySQL 8.4.11 on this lab host, then the orders table's
dirty pages counted right after it is loaded, after five quiet seconds, and right after one hundred orders change.

A throwaway MySQL 8.4.11 server (mysql_session.py) runs flush_defaults.sql, which lists the settings that decide when
InnoDB writes changed pages back; orders.sql makes 1,000 orders of about 7 KB each; dirty_pages.sql counts the table's
cached and dirty pages, waits five seconds without running anything, counts again, changes 100 orders and counts once
more.
"""

from mysql_session import Server

with Server() as my:
    my.up()
    my.mysql("flush_defaults.sql", flags=("-t",))
    my.mysql("orders.sql", flags=("-t",))
    my.mysql("dirty_pages.sql")

$ mysql -t < flush_defaults.sql
+--------------------------------+----------------+
| VARIABLE_NAME                  | VARIABLE_VALUE |
+--------------------------------+----------------+
| innodb_adaptive_flushing       | ON             |
| innodb_adaptive_flushing_lwm   | 10             |
| innodb_adaptive_hash_index     | OFF            |
| innodb_change_buffering        | none           |
| innodb_flush_neighbors         | 0              |
| innodb_flushing_avg_loops      | 30             |
| innodb_idle_flush_pct          | 100            |
| innodb_io_capacity             | 10000          |
| innodb_io_capacity_max         | 20000          |
| innodb_lru_scan_depth          | 1024           |
| innodb_max_dirty_pages_pct     | 90.000000      |
| innodb_max_dirty_pages_pct_lwm | 10.000000      |
| innodb_page_cleaners           | 1              |
+--------------------------------+----------------+

$ mysql -t < orders.sql
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| shop.orders | analyze | status   | OK       |
+-------------+---------+----------+----------+

$ mysql -t -vvv --skip-comments < dirty_pages.sql
--------------
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`'
--------------

+--------------+-------+
| orders_pages | dirty |
+--------------+-------+
|          502 |   502 |
+--------------+-------+
1 row in set

--------------
DO SLEEP(5)
--------------

Query OK, 0 rows affected

--------------
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`'
--------------

+--------------+-------+
| orders_pages | dirty |
+--------------+-------+
|          502 |     0 |
+--------------+-------+
1 row in set

--------------
UPDATE shop.orders SET note = REPEAT('u', 7000) WHERE id <= 100
--------------

Query OK, 100 rows affected
Rows matched: 100  Changed: 100  Warnings: 0

--------------
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`'
--------------

+--------------+-------+
| orders_pages | dirty |
+--------------+-------+
|          502 |    51 |
+--------------+-------+
1 row in set

Bye

flush_defaults.sql

-- The settings that decide when InnoDB writes changed pages back to the data files, as this server reports them.
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables
  WHERE VARIABLE_NAME IN ('innodb_page_cleaners', 'innodb_io_capacity', 'innodb_io_capacity_max',
    'innodb_lru_scan_depth', 'innodb_max_dirty_pages_pct_lwm', 'innodb_max_dirty_pages_pct',
    'innodb_adaptive_flushing', 'innodb_adaptive_flushing_lwm', 'innodb_flushing_avg_loops',
    'innodb_idle_flush_pct', 'innodb_flush_neighbors', 'innodb_change_buffering', 'innodb_adaptive_hash_index')
  ORDER BY VARIABLE_NAME;

dirty_pages.sql

-- The orders table's pages in the pool and how many are dirty: right after the load, after five quiet seconds,
-- and right after one hundred orders change.
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`';
DO SLEEP(5);
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`';
UPDATE shop.orders SET note = REPEAT('u', 7000) WHERE id <= 100;
SELECT COUNT(*) AS orders_pages, SUM(OLDEST_MODIFICATION > 0) AS dirty
  FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME = '`shop`.`orders`';

resize_session.py

"""Resizing the buffer pool while the server runs: from the 128 MiB default to 256 MiB, then a request that is not a
multiple of the chunk size.

A throwaway MySQL 8.4.11 server (mysql_session.py) starts with --log-error-verbosity=3, so that InnoDB's notes about
the resize reach the error log. resize.sql prints the pool's size, chunk size and instances and asks for 256 MiB; the
session waits until the pool reports its new size, then reads the resize status, the error-log notes, and the pool
in pages; last it asks for 300 MiB and reads what the server made of that request.
"""

from mysql_session import Server

POOL_PAGES = "SELECT POOL_SIZE FROM information_schema.INNODB_BUFFER_POOL_STATS"
RESIZE_CODE = ("SELECT VARIABLE_VALUE FROM performance_schema.global_status "
               "WHERE VARIABLE_NAME = 'Innodb_buffer_pool_resize_status_code'")

with Server(options=["--log-error-verbosity=3"]) as my:
    my.up()
    my.mask(r"Completed resizing buffer pool at \d+ +\d+:\d\d:\d\d\.", "Completed resizing buffer pool at <time>.")
    before = int(my.query(POOL_PAGES))
    my.mysql("resize.sql")
    my.wait_for(f"SELECT POOL_SIZE > {before} FROM information_schema.INNODB_BUFFER_POOL_STATS", 1, timeout=60)
    my.wait_for(RESIZE_CODE, 0, timeout=60)
    my.sql("SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status "
           "WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool_resize%' ORDER BY VARIABLE_NAME")
    my.show_log("[Rr]esiz|chunks", note="the resize, as InnoDB logged it")
    my.sql("SELECT @@innodb_buffer_pool_size AS pool_bytes, POOL_SIZE AS pool_pages "
           "FROM information_schema.INNODB_BUFFER_POOL_STATS")
    middle = int(my.query(POOL_PAGES))
    my.sql("SET GLOBAL innodb_buffer_pool_size = 314572800; SHOW WARNINGS", note="300 MiB is not a multiple of 128 MiB")
    my.wait_for(f"SELECT POOL_SIZE > {middle} FROM information_schema.INNODB_BUFFER_POOL_STATS", 1, timeout=60)
    my.wait_for(RESIZE_CODE, 0, timeout=60)
    my.sql("SELECT @@innodb_buffer_pool_size AS pool_bytes, POOL_SIZE AS pool_pages "
           "FROM information_schema.INNODB_BUFFER_POOL_STATS")

$ mysql -t -vvv --skip-comments < resize.sql
--------------
SELECT @@innodb_buffer_pool_size AS pool_bytes, @@innodb_buffer_pool_chunk_size AS chunk_bytes,
       @@innodb_buffer_pool_instances AS instances
--------------

+------------+-------------+-----------+
| pool_bytes | chunk_bytes | instances |
+------------+-------------+-----------+
|  134217728 |   134217728 |         1 |
+------------+-------------+-----------+
1 row in set

--------------
SET GLOBAL innodb_buffer_pool_size = 268435456
--------------

Query OK, 0 rows affected

Bye

$ mysql -t -e "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool_resize%' ORDER BY VARIABLE_NAME"
+-------------------------------------------+-------------------------------------------+
| VARIABLE_NAME                             | VARIABLE_VALUE                            |
+-------------------------------------------+-------------------------------------------+
| Innodb_buffer_pool_resize_status          | Completed resizing buffer pool at <time>. |
| Innodb_buffer_pool_resize_status_code     | 0                                         |
| Innodb_buffer_pool_resize_status_progress | 100                                       |
+-------------------------------------------+-------------------------------------------+

$ grep -E "[Rr]esiz|chunks" mysqld.err   # the resize, as InnoDB logged it
[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 268435456 bytes)
[Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).
[Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize
[Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 2.
[Note] [MY-011891] [InnoDB] buffer pool 0 : 1 chunks (8191 blocks) were added.
[Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 268435456.
[Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at <time>.

$ mysql -t -e "SELECT @@innodb_buffer_pool_size AS pool_bytes, POOL_SIZE AS pool_pages FROM information_schema.INNODB_BUFFER_POOL_STATS"
+------------+------------+
| pool_bytes | pool_pages |
+------------+------------+
|  268435456 |      16382 |
+------------+------------+

-- 300 MiB is not a multiple of 128 MiB
$ mysql -t -e "SET GLOBAL innodb_buffer_pool_size = 314572800; SHOW WARNINGS"
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect innodb_buffer_pool_size value: '314572800' |
+---------+------+----------------------------------------------------------------+

$ mysql -t -e "SELECT @@innodb_buffer_pool_size AS pool_bytes, POOL_SIZE AS pool_pages FROM information_schema.INNODB_BUFFER_POOL_STATS"
+------------+------------+
| pool_bytes | pool_pages |
+------------+------------+
|  402653184 |      24573 |
+------------+------------+

resize.sql

-- The pool's size, its chunk size and its instances: the size must be a multiple of chunk size times instances.
SELECT @@innodb_buffer_pool_size AS pool_bytes, @@innodb_buffer_pool_chunk_size AS chunk_bytes,
       @@innodb_buffer_pool_instances AS instances;
SET GLOBAL innodb_buffer_pool_size = 268435456;

warm_up_session.py

"""Keeping the pool warm across a restart: the dump file ib_buffer_pool and the load at startup.

A throwaway MySQL 8.4.11 server (mysql_session.py) keeps its defaults for saving and loading the pool. orders.sql makes
1,000 orders of about 7 KB (about 500 pages); warm_up.sql shows the dump and load settings, raises the share of the
pool that is saved to 100 % and saves it now. The session counts the lines for the orders tablespace in the file with
grep, restarts the server, waits until the background load reports that it has finished, counts the orders
tablespace's pages in the pool by page type before any query has opened the table, and then reads 40 orders with pool_reads.sql.
"""

import subprocess

from mysql_session import Server

LOAD_DONE = ("SELECT VARIABLE_VALUE LIKE '%load completed%' FROM performance_schema.global_status "
             "WHERE VARIABLE_NAME = 'Innodb_buffer_pool_load_status'")


def run(my, command):
    """Runs a shell command in the run folder and prints it with its output, as a terminal would."""
    done = subprocess.run(["/bin/sh", "-c", command], cwd=my.base, capture_output=True, text=True)
    print(f"$ {command}")
    print(my.clean(done.stdout).rstrip("\n"))
    print()


with Server() as my:
    my.up()
    my.mask(r"completed at \d+ +\d+:\d\d:\d\d", "completed at <time>")
    my.mysql("orders.sql", flags=("-t",))
    my.mysql("warm_up.sql")
    space = my.query("SELECT SPACE FROM information_schema.INNODB_TABLESPACES WHERE NAME = 'shop/orders'")
    print(f"-- shop/orders is tablespace {space}; each line of the file is one page: tablespace,page")
    run(my, f"grep -c '^{space},' data/ib_buffer_pool")
    run(my, f"grep '^{space},' data/ib_buffer_pool | sort -t, -k2 -n | head -3")
    my.restart(show=True)
    my.wait_for(LOAD_DONE, 1, timeout=60)
    my.sql("SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status'")
    my.sql(f"SELECT PAGE_TYPE, COUNT(*) AS pages FROM information_schema.INNODB_BUFFER_PAGE WHERE SPACE = {space} "
           "GROUP BY PAGE_TYPE ORDER BY pages DESC, PAGE_TYPE", note="no query has opened shop.orders since the restart")
    my.mysql("pool_reads.sql", flags=("-t",), note="the first read of orders after the restart")

$ mysql -t < orders.sql
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| shop.orders | analyze | status   | OK       |
+-------------+---------+----------+----------+

$ mysql -t -vvv --skip-comments < warm_up.sql
--------------
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables
  WHERE VARIABLE_NAME IN ('innodb_buffer_pool_dump_at_shutdown', 'innodb_buffer_pool_load_at_startup',
    'innodb_buffer_pool_dump_pct', 'innodb_buffer_pool_filename')
  ORDER BY VARIABLE_NAME
--------------

+-------------------------------------+----------------+
| VARIABLE_NAME                       | VARIABLE_VALUE |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_load_at_startup  | ON             |
+-------------------------------------+----------------+
4 rows in set

--------------
SET GLOBAL innodb_buffer_pool_dump_pct = 100
--------------

Query OK, 0 rows affected

--------------
SET GLOBAL innodb_buffer_pool_dump_now = ON
--------------

Query OK, 0 rows affected

--------------
DO SLEEP(1)
--------------

Query OK, 0 rows affected

--------------
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_dump_status'
--------------

+--------------------------------+-----------------------------------------+
| Variable_name                  | Value                                   |
+--------------------------------+-----------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at <time> |
+--------------------------------+-----------------------------------------+
1 row in set

Bye

-- shop/orders is tablespace 2; each line of the file is one page: tablespace,page
$ grep -c '^2,' data/ib_buffer_pool
506

$ grep '^2,' data/ib_buffer_pool | sort -t, -k2 -n | head -3
2,0
2,1
2,2

$ mysqld --no-defaults --datadir=./data --bind-address=127.0.0.1 --port=$PORT --socket=mysql.sock --mysqlx=OFF &

$ mysql -t -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status'"
+--------------------------------+-----------------------------------------+
| Variable_name                  | Value                                   |
+--------------------------------+-----------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at <time> |
+--------------------------------+-----------------------------------------+

-- no query has opened shop.orders since the restart
$ mysql -t -e "SELECT PAGE_TYPE, COUNT(*) AS pages FROM information_schema.INNODB_BUFFER_PAGE WHERE SPACE = 2 GROUP BY PAGE_TYPE ORDER BY pages DESC, PAGE_TYPE"
+-------------------+-------+
| PAGE_TYPE         | pages |
+-------------------+-------+
| INDEX             |   502 |
| FILE_SPACE_HEADER |     1 |
| IBUF_BITMAP       |     1 |
| INODE             |     1 |
| SDI_INDEX         |     1 |
+-------------------+-------+

-- the first read of orders after the restart
$ mysql -t < pool_reads.sql
+------------+
| empty_note |
+------------+
|          0 |
+------------+
+-------------+
| orders_read |
+-------------+
|          40 |
+-------------+
+---------------+----------------+---------+
| page_requests | read_from_disk | hit_pct |
+---------------+----------------+---------+
|            44 |              0 |     100 |
+---------------+----------------+---------+

warm_up.sql

-- The settings that save the pool at shutdown and load it at startup, then a save of the whole pool now.
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_variables
  WHERE VARIABLE_NAME IN ('innodb_buffer_pool_dump_at_shutdown', 'innodb_buffer_pool_load_at_startup',
    'innodb_buffer_pool_dump_pct', 'innodb_buffer_pool_filename')
  ORDER BY VARIABLE_NAME;
SET GLOBAL innodb_buffer_pool_dump_pct = 100;
SET GLOBAL innodb_buffer_pool_dump_now = ON;
DO SLEEP(1);
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_dump_status';