Hello David, thanks for describing things in details, it helped to clear
up some of my misunderstandings.
I was also thinking about VFS, but your description pointed out some
very important things I have missed (such as persisting my in-memory WAL
to disk before checkpointing).
If nobody picks up your challenge to "rip apart your suggestion", as you
are saying, I will probably go for that.
Thanks a lot,
Pavel
On 04/04/2018 09:00 PM, David Raymond wrote:
I don't think you can have all of the above. The "should never get corrupted"
part of SQLite comes from having the data in 2 non-volatile storage files during a
commit/checkpoint. Problems while writing data to the main file are covered by having the
rollback journal or WAL on disk to recover from in the event of death.
The memory journal mode says "I care about speed to the exclusion of recovery"
and means a problem at the wrong time will leave you with a corrupted database. Without
that second non-volatile store of the data then there will always be a chance of
corruption while writing to the one and only permanent file that you're using.
WAL gets you around the writer blocking readers problem, but still uses the
stored-in-2-places technique to prevent disaster.
(Sketchy on implementation details from here out)
With all your access being from 1 connection on the local computer, I would
think that the best-case for you would be to write your own VFS that keeps a
WAL in-memory so that you can have the concurrent access and speed, but then
for corruption avoidance come checkpoint time writes the pages to be updated to
a non-volatile rollback journal before updating the main db file. That way you
could still have multiple in-memory non-recoverable transactions and commits
between recoverable checkpoints. You'd still have to write to both the journal
and to the main file, but would only have to do so once per page updated
between checkpoints rather than potentially multiple times.
At least I think that's an option anyway. Those more familiar with what's
actually possible and actually useful will now rip apart my suggestion.
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Pavel Cernohorsky
Sent: Wednesday, April 04, 2018 10:01 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In memory only WAL file
Hello, does anybody know if there is some possibility to not have WAL
file as a normal file on the disk, but only in memory? I understand that
all the modifications to the database would get lost in case of the
application / OS crash, but for my application, I only need the level of
durability based on checkpointing. I just need to guarantee that all the
data are properly written to the main database and synchronized to disk
when manual (or even automatic) WAL checkpoint is called, but I do not
care if I loose data in between the checkpoints. Of course database
should never get corrupted.
My goal is to limit the number of IOps being performed to the disk.
Currently I use "PRAGMA synchronous = 1" and there is only one process
manipulating the database (multiple reader threads, only one writer
thread at one moment in time). Or if it is not possible to have WAL in
memory only, is there something like “PRAGMA wal_synchronous =
none_and_delete_wal_if_corrupted”?
Thanks for suggestions, kind regards,
Pavel
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
