[Mulgara-dev] Transaction commit performance

[Alex Hall]

Paul Gearon wrote:
> I already mentioned this to Alex, but it should be published to the  
> group as well...
> On Jun 11, 2008, at 11:25 AM, Alex Hall wrote:
> 
>> I'm having performance issues with Mulgara on my Windows laptop.
>> Specifically, the transaction commit time seems to be slow.  I turn  
>> off
>> autocommit on a connection, make a series of modifications, and then
>> turn autocommit back on.  Committing the transaction takes, on  
>> average,
>> a little more than 2 seconds.  Given that the commit operation is just
>> pointing the current phase to the new tree root for each index (plus  
>> the
>> stringpool), this seems a bit excessive.  Even accounting for the time
>> it takes to flush the files, it doesn't seem like it should take that
>> long.  Does that sound right?  I'm hoping that maybe this is a Windows
>> issue, and that once we start running our production server on Linux
>> with better hardware the commit times will speed up.  Any thoughts?
> 
> Yes. A commit operation guarantees that everything on disk is complete  
> and consistent. That means that everything that you wrote (and will be  
> sitting in the write-back buffer cache) needs to be flushed.  
> Unfortunately, that includes data in a lot of files, sometimes  
> scattered around those files.

One thing that occurred to me is that it might benefit us to keep track 
of which byte buffers have been modified in the current transaction, and 
only force those to disk (the current approach is to force ALL mapped 
byte buffers).  On further thought, though, forcing a non-modified 
mapped byte buffer should be a no-op (assuming that the OS is smart 
about managing its cache).  Do you agree?

> If you waited for a while (say, 30 seconds maybe), then you'd find all  
> that data would be written back already. At that point a commit would  
> be the simple root node updates that you said. However, since you're  
> doing all that writing and immediately committing, the OS has no  
> choice but to pause and make all those updates happen immediately. I  
> think it will even bypass some of the OS optimizations for clustering  
> writes in similar localities together.

When I ran my same tests on a Linux system with similar hardware, I 
found that my average time per commit on a clean database decreased from 
over 2 seconds on Windows to around 25 ms on Linux.  While this is still 
expensive relative to the insertions, clearly the problem is exacerbated 
by Windows' poor handling of memory-mapped files.

Regards,
Alex