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