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

Subject: Re: [boost] ACID transactions/flushing/MySQL InnoDB
From: Stefan Strasser (strasser_at_[hidden])
Date: 2009-12-10 18:02:03

Am Thursday 10 December 2009 21:47:53 schrieb Dean Michael Berris:
> On Fri, Dec 11, 2009 at 6:02 AM, Stefan Strasser <strasser_at_[hidden]> 
wrote:
> > thank you very much for both your answers.
> > but they don't solve the mystery, please see below.
>
> Let me try one more time. ;)

I was about to write you've got to try one more time, but...see below.

> 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.

> What InnoDB does is puts everything that fits in memory up there, and
> keeps it there. Small transactions will write to the log file, but not
> write all the data directly to disk right away.

that's almost equal to my approach. I do write to the data files, but only 
sync them when a large transaction is committed or the log is rolled to a new 
one.
I should probably think about sector-aligning those data writes, too, given 
the new insights.

> Because you're using fsync, you're asking the kernel to do it for you
> -- and if your file is already in the vfs cache, the chances of fsync
> returning quicker is higher due to write caching at the OS level.

I don't think the OS uses write caching in the case of fsync. it isn't 
supposed to, is it?