Hello Dan, thank you very much for clearing this up, because that was my
important misunderstanding.
Risking corruption when the OS crashes is not really an option for me. I
will probably go for what David Raymond suggested in one of other posts,
or I may also solve my whole problem on the application level and have 2
databases. One in memory only, which holds "all the changes since the
last checkpoint", the second one on disk with all the data up to the
last checkpoint. The character of the data I need to store allows this -
I can first query the in-memory database for the most recent results, if
I do not find them, I can query the on-disk database. My manual
checkpoint will then be simply "writing everything from my in-memory
database to my on-disk database in a single transaction". But my first
choice will be the VFS David suggested.
Thanks,
Pavel
On 04/05/2018 09:28 AM, Dan Kennedy wrote:
On 04/05/2018 02:08 PM, Pavel Cernohorsky wrote:
Hello Dan, so you are saying that if WAL is somehow in memory only
(not properly persisted) and app or OS crashes in the middle of the
checkpoint operation, my main database file will get corrupted? And
by corrupted you mean as in "you will loose changes which were in the
WAL file", or "you will end up with unusable main database file, or
file where rows which were affected by the checkpoint will have wrong
contents (halfway through written, ...)". In other words, I may end
up with the main database file in some other state than just "like
before checkpointing" or "like after checkpointing"? I understood
checkpointing as a kind of atomic operation which "merges data in the
main database file and in the WAL". Is that understanding wrong?
That's correct. If you crash mid-checkpoint and lose the wal file,
some future queries may return inconsistent results or SQLITE_CORRUPT
errors.
The suggestion made in another post to put the wal file on a tmpfs or
similar file-system is a good one. Then you will only risk corruption
if the OS crashes. There is no easy way to do that at the moment
though, you will have to do some hacking to get it to work.
Dan.
Thanks, Pavel
On 04/04/2018 06:33 PM, Dan Kennedy wrote:
On 04/04/2018 09:01 PM, Pavel Cernohorsky wrote:
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.
If your app or the OS crashes halfway through a checkpoint and this
means that the WAL file is lost, the database is likely to be
corrupted. Is that a problem?
Dan.
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
_______________________________________________
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
