One presumes that you mayhaps read the 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 and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions. With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss. Transactions are consistent with or without the extra syncs provided by synchronous=FULL. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode."
So, the question is whether or not you want D in your ACID. If all you need is ACI, then synchronous=NORMAL guarantees that. synchronous=FULL adds the D to your ACI giving you ACID. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Ian Freeman >Sent: Friday, 29 December, 2017 22:38 >To: SQLite mailing list >Subject: Re: [sqlite] fsync on -wal still happening > >On Fri, 2017-12-29 at 22:18 +0000, Simon Slavin wrote: >> >> On 29 Dec 2017, at 7:07pm, Ian Freeman <i...@ifreeman.net> wrote: >> >> > Thanks, Simon. Indeed I did see that option. But I'm concerned >> > about >> > maintaining integrity in the middle of an power-interrupted >commit, >> >> This is not a problem for either WAL mode or the older journal >> modes. If you avoid these commands >> >> PRAGMA journal_mode = OFF >> PRAGMA journal_mode = MEMORY >> PRAGMA synchronous = OFF >> PRAGMA synchronous = NORMAL >> >> then SQLite will ensure that the database file always reflects your >> data as it was before the more recent COMMIT or after the most >recent >> COMMIT. >> > >Agreed, the defaults would be very safe. But I realize I never >mentioned what my actual goal was: minimizing disk writes for a >write- >heavy application. That's why I want to run -wal out of memory, or >set >synchronous=NORMAL. Of course, I cannot do this at the expense of >integrity. > >There are other ways to solve my problem, for example I could operate >solely in memory and perform periodic online backups. But then I'm >dumping entire databases to disk when I really only need to capture a >single row's update, etc. Those could be minimized with breaking up >the >database into sub-sections, etc. But if I can just keep the -wal >updates off of the disk until checkpoint, it would be the best >solution. > >Thanks, all. I'm still open to your appreciated input as I mull this >over. > > >On Fri, 2017-12-29 at 15:09 -0700, Barry Smith wrote: >> I believe the inotifywait does not actually wait for the fsync >> operation before notifying. > >Barry Smith is making me question my inotifywait results. I need to >look into that further. >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
