Re: Any FSFS rep-sharing experts out there?

On Thu, Oct 8, 2009 at 2:48 PM, Bert Huijben <bert_at_qqmail.nl> wrote:
> On Thu, Oct 8, 2009 at 10:47 PM, David Glasser <glasser_at_davidglasser.net> wrote:
>
>> (This is making the assumption that a series of N non-transaction
>> SQLite INSERT statements (which is to say, a series of N implicit
>> transactions) is as efficient as one transaction with N INSERTs.
>> Maybe it isn't; I'm not sure.  Getting it out of the FSFS write lock
>> would still be good, though.)
>
> This is answered in http://www.sqlite.org/faq.html#q19. It isn't as fast:
>
> --------------
> (19) INSERT is really slow - I can only do few dozen INSERTs per second
>
>   Actually, SQLite will easily do 50,000 or more INSERT statements
> per second on an average desktop computer. But it will only do a few
> dozen transactions per second. Transaction speed is limited by the
> rotational speed of your disk drive. A transaction normally requires
> two complete rotations of the disk platter, which on a 7200RPM disk
> drive limits you to about 60 transactions per second.
>
>   Transaction speed is limited by disk drive speed because (by
> default) SQLite actually waits until the data really is safely stored
> on the disk surface before the transaction is complete. That way, if
> you suddenly lose power or if your OS crashes, your data is still
> safe. For details, read about atomic commit in SQLite..
>
>   By default, each INSERT statement is its own transaction. But if
> you surround multiple INSERT statements with BEGIN...COMMIT then all
> the inserts are grouped into a single transaction. The time needed to
> commit the transaction is amortized over all the enclosed insert
> statements and so the time per insert statement is greatly reduced.
>
>   Another option is to run PRAGMA synchronous=OFF. This command will
> cause SQLite to not wait on data to reach the disk surface, which will
> make write operations appear to be much faster. But if you lose power
> in the middle of a transaction, your database file might go corrupt.
>
> ----
> And it is certainly at its right place in the FAQ.. Referenced it
> three times in the last few days ;)

Thanks Bert. Then take away my recommendation to de-sqlitetxnify the
rep cache updating. But it's still worth considering moving it
outside of the FSFS write lock.

The tradeoffs, as far as I see it, are:

  - In Daniel's current patch, the sqlite DB is only accessed when the
FSFS write lock is held, which means that in theory there should never
be *any* concurrent access to it, so we never have to worry about
EBUSY, overlapping txns, etc. (Though at this point sqlite is kinda
overkill :) ) On the other hand, commits take longer and so if a repo
is getting lots and lots of commits, this could take longer.

 - If the rep cache is updated after the lock is dropped, then it's
possible to get contention on the DB, but multiple commits around the
same time can do more work in parallel.

I think it's worth trying...

-- 
glasser_at_davidglasser.net | langtonlabs.org | flickr.com/photos/glasser/
------------------------------------------------------
http://subversion.tigris.org/ds/viewMessage.do?dsForumId=462&dsMessageId=2405335