> > What I'm thinking about is actually following: > > PRAGMA journal_mode = wal; > > PRAGMA synchronous = NORMAL; > PRAGMA wal_autocheckpoint = 0; > > Now once in a while (for example once in a second) > I can execute > > PRAGMA wal_checkpoint; > > Seems would make the DB ACI(D) where D is promised only > after I call > > PRAGMA wal_checkpoint; > > > This is quote from Sqlite PRAGMA synchronous documentation > >> In WAL mode when synchronous is NORMAL (1), >> the WAL file is synchronized before each >> checkpoint and the database file is synchronized >> after each completed checkpoint, but no other >> sync operations occur. With synchronous=FULL >> in WAL mode, an additional sync operation of >> the WAL file happens after each transaction >> commit. If durability is not a concern, then >> synchronous=NORMAL is normally all one needs >> in WAL mode. > > So it seems that what I've mentioned seems to > be correct according to this. > > Can somebody confirm this behavior? > > Artyom
Seems that there is nothing better then reading documentation: http://www.sqlite.org/wal.html#fast Using WAL available from Sqlite 3.7 increases performance in both FULL synchonous mode because reduces the number of fsyncs (only log should be fsynced) On the other had as I described above setting PRAGMA synchronous=NORMAL while using PRAGMA journal_mode=WAL - **significantly** increases the performance because fsync is called once in checkpoint and not every transaction. Of course it has a price that Durability is preserved on checkpoint boundaries only. BTW I compared performance of multiple inserts (each in its own transaction) using these options and similar options of MySQL and PostgreSQL and Sqlite was somewhere between MySQL and PostgreSQL and with big value wal_autocheckpoint it was faster then both of them (of course with price of memory use for -shm index) Best, It seems that WAL is one of the greatest features introduced in 3.7 Artyom _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
