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
