MySQL Tuning, Indexing & Concurrency
8-10 Years (Tech Lead)
Hard
Production Scenario
How does `innodb_flush_log_at_trx_commit` trade ACID durability for 10x write throughput?
Real-World Scenario / Incident Description
A high-speed IoT ingestion pipeline writing 20,000 metrics per second was bottlenecked by disk IOPS.
117 views
•
Updated 1 month ago
innodb_flush_log_at_trx_commit Settings
1(Default / Strict ACID): Redo log is flushed to disk on every single transaction commit. Maximum safety, high disk IOPS.2: Redo log is written to OS cache on commit and flushed to disk once per second. If OS crashes, at most 1 second of data is lost, but write throughput increases up to 10x.0: Flushed once per second in memory.
Related Concepts:
#ACID
#Redo Log
#IOPS
#Tuning
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →