看到 Mark Callaghan 寫的「MariaDB innovation: binlog_storage_engine」、「MariaDB innovation: binlog_storage_engine, small server, Insert Benchmark」這篇,裡面提到了 MariaDB 12.3 (還沒 GA) 的 InnoDB 改了很底層的架構,從本來的 binlog + InnoDB 的架構整合到只有 InnoDB,連帶也降低了 fsync 呼叫的次數:
MariaDB 12.3 has a new feature enabled by the option binlog_storage_engine. When enabled it uses InnoDB instead of raw files to store the binlog. A big benefit from this is reducing the number of fsync calls per commit from 2 to 1 because it reduces the number of resource managers from 2 (binlog, InnoDB) to 1 (InnoDB).
這個是 MySQL InnoDB 架構上很久的問題,binlog 與 InnoDB 本身兩次的寫入會需要呼叫兩次 fsync,而 fsync 本身就有很高的 latency (需要等到 i/o 完成)。
Mark Callaghan 用手邊的機器測試發現,改進甚至比想像中的大,無論是 cpu bounded 或是 i/o bounded 的情況:
tl;dr for a CPU-bound workload
* Enabling sync on commit for InnoDB and the binlog has a large impact on throughput for the write-heavy steps -- l.i0, l.i1 and l.i2.
* When sync on commit is enabled, then also enabling the binlog_storage_engine is great for performance as throughput on the write-heavy steps is 1.75X larger for l.i0 (load) and 4X or more larger on the random write steps (l.i1, l.i2)tl;dr for an IO-bound workload
* Enabling sync on commit for InnoDB and the binlog has a large impact on throughput for the write-heavy steps -- l.i0, l.i1 and l.i2. It also has a large impact on qp1000, which is the most write-heavy of the query+write steps.
* When sync on commit is enabled, then also enabling the binlog_storage_engine is great for performance as throughput on the write-heavy steps is 4.74X larger for l.i0 (load), 1.50X larger for l.i1 (random writes) and 2.99X larger for l.i2 (random writes)
如果在大台 server 上面也有類似的效果,這個有機會是 InnoDB 這十年來在寫入效能上最大的改進?上次應該是壓縮了...