[boost] ACID transactions/flushing/MySQL InnoDB

Subject: [boost] ACID transactions/flushing/MySQL InnoDB
From: Stefan Strasser (strasser_at_[hidden])
Date: 2009-12-10 12:07:09

hey,

this is not a boost question, but it is for the development of a boost 
library, so maybe a database expert on the list could help me out?

I've been benchmarking some test cases of my library against MySQL InnoDB, 
which isn't even apples to oranges anymore, but nevertheless gave some 
insights.

what I find surprising is the performance of MySQL for small transactions.
while my library spends 90% of its time waiting for the disk to flush a 
transaction, InnoDB uses 100% CPU and barely uses any disk.

I've reduced the calls to fsync() for small transactions to 1 call per 
transaction after some sequential writing, so I'm wondering how MySQL 
accomplishes this?
by definition there must be a flush per transaction, unless you loosen the 
requirements of the D in ACID.

Is that what InnoDB is doing?
the only way I see to reduce flushes even more is combining some transactions. 
but after a power failure some of those transactions can be lost even though 
they haven reported as successfully committed.

I have not yet examined their source code yet. the MySQL documentation doesn't 
say anything about loosening Durability. (although you have to look pretty 
hard to see that the default isolation level isn't Serializable either.)

thanks in advance,

Stefan