On Tue, Jul 6, 2010 at 1:05 PM, Martin Knafve <mar...@hmailserver.com>wrote:
> Hi, > > I'm considering using SQLite as backend for my software. What's stopping > me is the low insert-performance. If I disable fsync/FlushFileBuffers, > performance is good but I can't do with the risk of database corruption. > > I don't know the details of the WAL-implementation in 3.7, but if > fsync/FlushFileBuffers are only required when data is moved from the > write-ahead log to the database file, I would expect performance to be a > lot faster. Reading the documentation about WAL, I get the impression that > I can accomplish this using the following pragmas: > > sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, 0, &db_err); > sqlite3_exec(db, "PRAGMA synchronous=NORMAL", NULL, 0, &db_err); > If you do the above, no fsyncs happen except when doing a checkpoint, and a checkpoint should only happen after about each megabyte of inserting. We don't have any git support here, so I don't know where you got your snapshot, but it wasn't an official SQLite. Prerelease snapshots for SQLite 3.7.0 are available here: http://www.sqlite.org/draft/download.html Additional information on WAL: http://www.sqlite.org/draft/wal.html > > I picked a snapshot from git and tried it. I'm unable to see any real > performance improvement by doing this though. Haven't looked very > carefully and only run the tests a few times but I don't notice any > difference larger than 10% or so. On my PC, INSERT's with fsync disabled > is ~50 times faster than when fsync is used, so I would expect enabling > WAL to give me at least a 500% speedup. Of course, my assumptions are very > rough and I understand that I'm likely far off. > > Am I missing something here? What kind of performance improvments should I > expect on INSERT statements not taking part in a transaction when using > the WAL? > > Kind regards, > > Martin Knafve > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- --------------------- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users