中文
devexamples
← Recipes

阅读中文版本

Benchmark MySQL InnoDB Flush Settings

Measure write throughput at different innodb_flush_log_at_trx_commit levels and pick the safest setting that meets the durability target.

~45 minmysqldatabaseperformance

Task

Benchmark durable versus relaxed InnoDB flush settings and choose based on measured throughput and acceptable data-loss risk.

When to use this

Write-heavy workloads are limited by fsync, and the team wants evidence before relaxing durability.

Prerequisites

  • MySQL 8 in a disposable staging instance
  • A benchmark tool such as sysbench or mysqlslap
  • A defined durability requirement

Steps

1. Baseline throughput with fully durable flushing.

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
-- run the write benchmark
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';

Expected output: Throughput and fsync counts at the strictest setting form the durability baseline.

2. Compare with the relaxed setting and record the trade-off.

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- rerun the same benchmark
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';

Expected output: Throughput rises and fsync count falls, quantifying the cost of durability.

Troubleshooting

Problem

Numbers vary wildly between runs

Solution

Warm the buffer pool and run each configuration multiple times, discarding the first run.

Problem

Setting resets on restart

Solution

Persist the chosen value in the config file after benchmarking, not only at runtime.

Limitations

  • Relaxed flushing can lose recent transactions on crash.
  • Benchmark hardware differs from production.
  • Does not account for replication durability.

What this recipe does

Benchmark durable versus relaxed InnoDB flush settings and choose based on measured throughput and acceptable data-loss risk.

Write-heavy workloads are limited by fsync, and the team wants evidence before relaxing durability.

Prerequisites

  • MySQL 8 in a disposable staging instance
  • A benchmark tool such as sysbench or mysqlslap
  • A defined durability requirement

Procedure

  1. Baseline throughput with fully durable flushing.
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
-- run the write benchmark
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';

Expected result: Throughput and fsync counts at the strictest setting form the durability baseline.

  1. Compare with the relaxed setting and record the trade-off.
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- rerun the same benchmark
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';

Expected result: Throughput rises and fsync count falls, quantifying the cost of durability.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the chosen setting meets the throughput target while its durability window matches the documented recovery requirement.

Troubleshooting

Numbers vary wildly between runs — Warm the buffer pool and run each configuration multiple times, discarding the first run.

Setting resets on restart — Persist the chosen value in the config file after benchmarking, not only at runtime.

Limitations

  • Relaxed flushing can lose recent transactions on crash.
  • Benchmark hardware differs from production.
  • Does not account for replication durability.