Re: [boost] ACID transactions/flushing/MySQL InnoDB

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

Am Thursday 10 December 2009 22:38:54 schrieb Dean Michael Berris:
> On Fri, Dec 11, 2009 at 7:02 AM, Stefan Strasser <strasser_at_[hidden]> 
wrote:
> > Am Thursday 10 December 2009 21:47:53 schrieb Dean Michael Berris:
> >> When you say logged on commit, are you writing to a file that's
> >> already created with enough space, or are you writing to a file which
> >> is "grown" every time you add data to it?
> >
> > I had tried that. and I've tried writing a sector instead of 1 byte. and
> > I've tried removing O_CREAT.
> > but you have to actually do ALL THREE, and one more: the sector writes
> > need to be sector-aligned.
> > so, writing 512 bytes, aligned to 512 bytes, without O_CREAT, when the
> > file already exists, brings the desired results
> > 2 seconds with much less disk usage.
> > that's some set of conditions.
> >
> > thanks for helping with this.
>

in case you're interested, I now get about 5000 synced small transactions/s.
with a theoretical maximum of about 7000, but the 5000 are including the data 
writes and the writing-ahead to zero out the data and make sure the log file 
doesn't grow on each transaction sync.
thanks again.

there are some more weird conditions to get sequential writing performance, 
e.g. when you write with the following sync intervals, starting from offset 0 
and "|" representing syncs:

512 | 2048 | 512 | 512 | 512 | ...

performance is much much worse than:

512 | [1536] | 2048 | 512 | 512 | ...

with 1536 bytes of garbage inserted so the 2048-bytes block can be written 
aligned to its own size. and yes, the sync after writing the garbage is 
necessary.

> Sounds like a good approach. If you're thinking of multi-threading and
> having an active object do the flush management, that should be
> something worth looking into to move the latency from persistence away
> from "worker" threads to a single serializing writer thread.

I've tried something like that, still synced by the worker threads but the 
sync is lock-free, so in theory the threads could meet at the sync call and 
if there were no writes between the sync calls, the OS could decide to 
perform only one of them. but it doesn't have large effects, about 5200 txs/s 
with 10 threads (on single CPU).
I guess you could improve multithreaded performance a lot if you yield the 
thread right before the sync and only sync in one thread if threads meet at 
that point. but I haven't looked into this and I don't plan to right now.