Server — MariaDB — InnoDB

Every table in February’s MariaDB databases is stored using InnoDB. You have been configuring it since the config article — the buffer pool size, the flush behaviour, the redo log — without a full picture of what those settings actually do. This article fills that gap.

InnoDB is a storage engine: the component of MariaDB responsible for reading data from disk, writing it back, managing transactions, and recovering cleanly from crashes. It is not the only storage engine available, but it is the default and the right choice for every application running on February. Understanding how it works at a conceptual level makes the configuration settings legible and the log messages interpretable, which is the goal here.

What InnoDB is for

A storage engine has to solve a fundamental tension: memory is fast but volatile; disk is persistent but slow. Every database read and write involves negotiating between these two facts.

InnoDB’s approach to this tension has three main components that work together: the buffer pool, the redo log, and the doublewrite buffer. Each one addresses a specific part of the problem. Together they give you a database that is fast under normal operation and recovers correctly after a crash.

The buffer pool

The buffer pool is InnoDB’s in-memory cache. When a query needs a row from a table, InnoDB first checks whether the relevant page is already in the buffer pool. If it is, the data is returned directly from memory without a disk read. If it is not, InnoDB reads the page from disk into the buffer pool and returns the data from there.

Writes work similarly. When a transaction modifies data, the changes are applied to the page in the buffer pool immediately, making the modified page what InnoDB calls a dirty page. The modified page is not written back to disk immediately. Instead, a background process called the page cleaner periodically flushes dirty pages to disk, batching writes for efficiency.

This is why the buffer pool size matters so much. A buffer pool large enough to hold the entire active dataset means most reads are served from memory with no disk IO. A buffer pool too small means frequent page evictions: pages are read from disk, used, evicted to make room for other pages, and then read from disk again when needed. This is called thrashing, and it is the most common cause of MariaDB performance problems on memory-constrained servers.

For February, the buffer pool was set to 256 MB in the config article. To check how well this is working in practice, query the buffer pool hit ratio:

sudo mariadb -e "
SELECT
  ROUND(
    (1 - (
      SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    ) / (
      SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100, 2
  ) AS buffer_pool_hit_ratio_pct;"

A healthy server should show 99% or above. Below 95% means the buffer pool is too small for the active dataset and February is doing substantial disk IO for reads that could be served from memory. If that is the case, increase innodb_buffer_pool_size in 50-server.cnf and restart.

For a more complete picture of buffer pool state:

sudo mariadb -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "BUFFER POOL AND MEMORY"

This shows the current pool size, the number of pages cached, the number of dirty pages waiting to be flushed, and the read and write activity rates.

The redo log

The redo log is InnoDB’s write-ahead log. Before a modified page is written to disk, a record of the change is written to the redo log. This ordering is the key to crash recovery: if the server loses power or crashes after a transaction commits but before the modified pages have been flushed to disk, the redo log contains enough information to replay those changes from scratch.

On startup after a crash, InnoDB reads the redo log, identifies any changes that were committed but not yet written to the data files, and replays them. This is crash recovery, and it is what the error log means when it shows InnoDB recovery messages after an unclean shutdown.

The redo log is a circular fixed-size file called ib_logfile0 in the MariaDB data directory. When InnoDB writes to the redo log, it advances a position called the Log Sequence Number (LSN). When dirty pages are flushed to disk, InnoDB records a checkpoint: the LSN up to which all changes have been persisted. Log space before the checkpoint can be reused.

The size of the redo log affects two things: write performance and crash recovery time. A larger redo log allows more dirty pages to accumulate before a checkpoint is forced, which reduces the frequency of checkpoint flushes and improves write throughput. A smaller redo log forces more frequent checkpoints, which reduces crash recovery time because there is less to replay.

For February’s workload, the default redo log size in MariaDB 10.11 is appropriate. The write volume is low, so checkpoint frequency is not a bottleneck, and recovery time after a crash is not a significant concern.

The relevant setting in 50-server.cnf:

innodb_flush_log_at_trx_commit = 1

This was set in the config article. A value of 1 means the redo log is flushed to disk on every transaction commit, guaranteeing that a committed transaction is durable even if the server crashes a millisecond later. A value of 2 flushes to the OS cache but not to disk on every commit, meaning up to one second of committed transactions could be lost on a crash. A value of 0 is the least safe option and not appropriate for any data worth keeping. Keep it at 1.

The doublewrite buffer

The doublewrite buffer solves a specific problem: a torn write. When InnoDB flushes a dirty page to disk, the write is 16 KB. If the server loses power mid-write, only part of the page reaches disk, leaving a partially-written page that is neither the old version nor the new version. This is a torn write, and it can corrupt the data file permanently.

The doublewrite buffer prevents this by writing each page to a dedicated area of the data file first, then to its actual location. If a crash occurs during the second write, the doublewrite buffer still contains the complete page and can be used to complete the write on recovery.

InnoDB enables the doublewrite buffer by default and there is no reason to disable it on February. The performance cost is modest, and the protection it provides against data corruption from power loss is exactly the kind of thing the UPS and NUT setup is also defending against.

You can confirm it is enabled:

sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_doublewrite';"

The output should show ON.

Crash recovery in practice

When MariaDB starts after an unclean shutdown, InnoDB automatically runs crash recovery before accepting connections. The error log shows what it is doing:

[Note] InnoDB: Starting crash recovery...
[Note] InnoDB: Starting to apply a batch of log records...
[Note] InnoDB: Apply batch completed
[Note] InnoDB: Recovered

This is normal and expected after a power loss or hard reset. Recovery time is proportional to the amount of redo log that needs to be replayed, which depends on how many dirty pages were in the buffer pool at the time of the crash. On a lightly loaded server like February, recovery typically takes a few seconds.

If crash recovery fails, InnoDB enters a mode where it refuses to start. The error log will show the reason. The most common causes are a corrupted redo log file, a corrupted data file, or an inconsistency between the two.

In that scenario, MariaDB provides a graduated series of recovery modes via the innodb_force_recovery setting, which can be set temporarily in 50-server.cnf to allow the server to start in progressively more permissive modes at the cost of data integrity guarantees:

innodb_force_recovery = 1

Values range from 1 to 6. Start at 1 and increase only if the server still cannot start. At level 1, InnoDB skips corrupt pages it cannot recover. At level 6, it ignores all integrity checks and lets you read tables even from a severely damaged database. At any level above 1, immediately dump all data you can reach with mysqldump and rebuild from scratch.

Never run in a forced recovery mode on a production database. Use it only to extract data from a damaged instance, then restore from backup.

Monitoring InnoDB status

The SHOW ENGINE INNODB STATUS command provides a comprehensive snapshot of InnoDB’s internal state. On a healthy server it is mostly noise. On a server experiencing problems it is the most useful diagnostic tool available.

sudo mariadb -e "SHOW ENGINE INNODB STATUS\G"

The sections to focus on:

BUFFER POOL AND MEMORY — pool size, pages cached, dirty pages, hit rate. The hit rate figure here is the same one computed in the query above.

LOG — current LSN, flushed LSN, checkpoint LSN, and the gap between them. A large and growing gap between the checkpoint LSN and the current LSN means InnoDB is not flushing dirty pages to disk fast enough. This is unusual on February.

TRANSACTIONS — active transactions and any lock waits. On a lightly loaded server this section should show nothing unusual.

ROW OPERATIONS — reads, inserts, updates, deletes per second. Useful for confirming that activity levels are what you expect.

What does not need tuning on February

Several InnoDB settings appear in every MariaDB tuning guide that have no practical relevance for February:

innodb_buffer_pool_instances — splitting the buffer pool into multiple instances reduces contention on high-concurrency servers. February has a handful of applications and low concurrency. One instance is correct.

innodb_io_capacity — controls how aggressively InnoDB flushes dirty pages. The default of 200 is calibrated for spinning disks. If February runs on an SSD, increasing this to 1000 or 2000 allows more aggressive flushing. For a server with February’s write volume, the difference is negligible.

innodb_log_file_size — the default in 10.11 is appropriate for February’s workload. Increasing it benefits high-write servers by reducing checkpoint frequency. For a server writing a handful of DNS records and sensor readings per hour, the default is fine.

file_per_table — InnoDB creates a separate .ibd file for each table by default. This is correct and should not be changed. Deprecated since 11.0 anyway.

The principle from the config article applies here too: tune for what February actually is, not for what a dedicated database server needs to be.