----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 04/11/2003 02:26 PM -----
Ben Carlyle
04/11/2003 02:01 PM
To: Doug Currie <[EMAIL PROTECTED]>
cc:
Subject: Re: Re[2]: [sqlite] Performance problem
Doug Currie <[EMAIL PROTECTED]>
03/11/2003 05:39 AM
Please respond to Doug Currie
To: [EMAIL PROTECTED]
cc:
Subject: Re[2]: [sqlite] Performance problem
> > Can any readers suggest ways that I have not thought of for making
> > large numbers of non-localized INSERTs go faster?
> Instead of journaling the old ("undo") pages of idx2, simply record
> the fact that idx2 is modified (i.e., make a single record in the
> journal). Upon recovery, noticing that idx2 is modified will cause it
> to be recreated from the rolled-back data.
I've been thinking about his for a while and I think the solution, as with
many other potential sqlite performance issues is to solve it in the
operating system, not sqlite it's self. If you don't have enough bandwidth
to your file, raid it. If you want to be able to write a large number of
transactions per second using journalling that lazily writes to the
database enable data-journalling on your filesystem.
This is a couter-intuitve approach. When you apply data journalling on a
filesystem such as linux's ext3 the operating system writes all data to a
journal before writing it to the appropriate locations on disk. You may
think that because its being written twice (four times in the case of
sqlite, because the journal will be written twice and the file will be
written twice) it will take longer to work. When you use fsync() alot the
opposite is actually true. You get the behaviour that other list members
have been discussing with the combined undo/redo log. The data updates are
written sequentially by the operating system to disk so seek-time doesn't
come into it, and the fsync() simply ensures that your file has been
written to the sequential log. The operating system it's self deals with
the lazy writing to the actual disk, and it's transparent to sqlite
because the operating system cache makes it appear that its already been
written.
This only works when you use all-data journalling, not just meta-data like
directory entries, and regular writes to the same filesystem might be a
little slower... but try it. You should get better performance with
data-journalling and sqlite.
As this is a problem that can be solved very nicely indeed in the
operating system I think that sqlite should make no special provisions for
it. I do suggest that the relevant FAQs should contain the instruction to
use a data-journalled fs if a large number of transactions must be pushed
through sqlite.
Benjamin.
