In memory only WAL file

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

In memory only WAL file

Pavel Cernohorsky
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Warren Young
On Apr 4, 2018, at 8:01 AM, Pavel Cernohorsky <[hidden email]> 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?

Why turn on WAL mode at all, then?

Are you maybe using a SQLite setup where WAL is enabled by default?  If so, it’s easy to disable it:

    https://sqlite.org/pragma.html#pragma_journal_mode

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Dan Kennedy-4
In reply to this post by Pavel Cernohorsky
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Jens Alfke-2
In reply to this post by Warren Young


> On Apr 4, 2018, at 9:01 AM, Warren Young <[hidden email]> wrote:
>
> Why turn on WAL mode at all, then?

Performance, probably. An in-memory WAL would scream.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Peter da Silva
In reply to this post by Pavel Cernohorsky
You could put the WAL in a tmpfs/ramfs so the DB would only get corrupted if the OS crashed, it'd still be there for recovering from application crashes.
 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Simon Slavin-3
In reply to this post by Pavel Cernohorsky
On 4 Apr 2018, at 3:01pm, Pavel Cernohorsky <[hidden email]> 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.

You may be mistaking what checkpoints are for.  They should not be used as an ACID barrier.

In your situation, I recommend you use

    PRAGMA journal_mode = MEMORY

and where you write 'checkpoint' above, that's where you do a COMMIT.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

David Raymond
In reply to this post by Pavel Cernohorsky
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:[hidden email]] On Behalf Of Pavel Cernohorsky
Sent: Wednesday, April 04, 2018 10:01 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by Jens Alfke-2
Hello Jens and Warren,

Performance is really one of the reasons, second is concurrent writer
and readers (which can be in fact viewed as part of the performance).

Pavel


On 04/04/2018 06:57 PM, Jens Alfke wrote:

>
>> On Apr 4, 2018, at 9:01 AM, Warren Young <[hidden email]> wrote:
>>
>> Why turn on WAL mode at all, then?
> Performance, probably. An in-memory WAL would scream.
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by Dan Kennedy-4
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?

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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by Peter da Silva
And how do you change location of the WAL file? Plus, will the main
database file really get corrupted (as in Consistency corrupted, not
only Durability of the data which were in the WAL file) if the OS crashes?

Pavel

On 04/04/2018 07:07 PM, Peter Da Silva wrote:
> You could put the WAL in a tmpfs/ramfs so the DB would only get corrupted if the OS crashed, it'd still be there for recovering from application crashes.
>  
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by Simon Slavin-3
Well, so you are saying that I should use single transaction for the
"checkpoints"? Unfortunately, that wouldn't play really well with those
multiple database readers I mentioned - those readers would not see
changes done by the writer until the transaction finishes (which can be
several seconds in between the "checkpoints") and that is not an option.
Also, documentation states that:

 > "If the application using SQLite crashes in the middle of a
transaction when the MEMORY journaling mode is set, then the database
file will very likely go corrupt"

Which is, as I mentioned not acceptable. Or, it depends, what is meant
by corruption here. As I mentioned, I can loose D from ACID in between
the checkpoints, I need the other ones.

Pavel


On 04/04/2018 07:15 PM, Simon Slavin wrote:

> On 4 Apr 2018, at 3:01pm, Pavel Cernohorsky <[hidden email]> 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.
> You may be mistaking what checkpoints are for.  They should not be used as an ACID barrier.
>
> In your situation, I recommend you use
>
>      PRAGMA journal_mode = MEMORY
>
> and where you write 'checkpoint' above, that's where you do a COMMIT.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Dan Kennedy-4
In reply to this post by Pavel Cernohorsky
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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by David Raymond
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:[hidden email]] On Behalf Of Pavel Cernohorsky
> Sent: Wednesday, April 04, 2018 10:01 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
In reply to this post by Dan Kennedy-4
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
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

wmertens
Serious question: what prompts you to consider these things? Is sqlite
being too slow for you?

On Thu, Apr 5, 2018 at 10:00 AM Pavel Cernohorsky <
[hidden email]> wrote:

> 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
> >>>> [hidden email]
> >>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>>
> >>> _______________________________________________
> >>> sqlite-users mailing list
> >>> [hidden email]
> >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
Hi, what prompts me? Well, I mentioned that in the original post - it is
the number of IOPS going to the disk (write IOPS I am concerned about).

Not that SQLite would be slow, the performance is OK (for now), but
having IO in the range of thousands per second constant load just
doesn't feel right. Not to mention that without the SSD, the system
wouldn't run now. And I am really not sure about effects of that high
IOPS on SSDs in the longer run (and wasn't able to find any reliable
thorough test / explanation from somebody who actually knows what (s)he
is saying - like someone working for Samsung Storage :). I mean, if you
have database having 7000 IOPS, if single IO to disk is a write IO to
one block (4 kB on the OS level), you have write amplification, SSDs
write block size is 2MB, even if 100 of your requests get combined by
SSD controller (which I have no clue how good / bad it actually is), you
still have real write throughput to the SSDs cells of (7000/100)⋅2 = 140
MB / s (although your "real" throughput from your application on the OS
level is only (7000⋅4)/1024 = approx 27.4 MB / s). This way, if you look
at consumer grade SSD, you have 150 TBW, which means it passes it's
warranty in (((150⋅1024⋅1024)/140)/3600)/24 = approx 13 days. Not
good... if you use bigger pro-level SSD, you get on a bit less that 1
year.. still not good enough. Of course, big unknown in this is the
constant representing the ability of the SSD controller to merge
requests and minimize block re-writes.

If I had an ability in my use case to say "now apply durability" and I
applied it once a 10 seconds, the whole thing would not need SSD at all...

Pavel


On 04/06/2018 06:59 PM, Wout Mertens wrote:

> Serious question: what prompts you to consider these things? Is sqlite
> being too slow for you?
>
> On Thu, Apr 5, 2018 at 10:00 AM Pavel Cernohorsky <
> [hidden email]> wrote:
>
>> 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
>>>>>> [hidden email]
>>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>>
>>>>> _______________________________________________
>>>>> sqlite-users mailing list
>>>>> [hidden email]
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Simon Slavin-3
On 9 Apr 2018, at 8:41am, Pavel Cernohorsky <[hidden email]> wrote:

> if you use bigger pro-level SSD, you get on a bit less that 1 year.. still not good enough. Of course, big unknown in this is the constant representing the ability of the SSD controller to merge requests and minimize block re-writes.

If you compiled SQLite with standard options, and have the correct settings for

PRAGMA fullfsync  (Macintosh only)
PRAGMA journal_mode
PRAGMA synchronous

then SQLite tells the operating system all the correct things.

Whether the operating system, the storage driver, and the storage subsystem itself do the right things is out of control of software.  Not only can you not tell what's being done, but it can change with what looks like a minor change in driver or hardware.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

Pavel Cernohorsky
I understand that the SQLite tells the OS all the correct things, but as
I described earlier, that may just produce way too much IOPS, even if
everything in the chain does the correct thing down to the last silicon
transistor. That is why I was asking about a way how to go around it by
holding part of the newest data in memory only and manually checkpoint
(and I hoped WAL will be a way). And I got some very good suggestions
earlier.

In my last mail, I have outlined a calculation suggesting the SSD may
wear out pretty soon with the described load. Yesterday I have found a
way how not to speculate and get some real data - Samsung's Enterprise
SSDs which we use provide a diagnostics which measures a real world load
and using their drive's SMART capabilities calculates the estimated
SSD's lifetime, in other words saying how bad the load really is. It is
actually a very useful thing, so if somebody else needs it as well, here
is the link:
https://www.slideshare.net/SamsungBusinessUSA/using-smart-attributes-to-estimate-enterprise-ssd-lifetime

Pavel



On 04/09/2018 02:47 PM, Simon Slavin wrote:

> On 9 Apr 2018, at 8:41am, Pavel Cernohorsky <[hidden email]> wrote:
>
>> if you use bigger pro-level SSD, you get on a bit less that 1 year.. still not good enough. Of course, big unknown in this is the constant representing the ability of the SSD controller to merge requests and minimize block re-writes.
> If you compiled SQLite with standard options, and have the correct settings for
>
> PRAGMA fullfsync  (Macintosh only)
> PRAGMA journal_mode
> PRAGMA synchronous
>
> then SQLite tells the operating system all the correct things.
>
> Whether the operating system, the storage driver, and the storage subsystem itself do the right things is out of control of software.  Not only can you not tell what's being done, but it can change with what looks like a minor change in driver or hardware.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In memory only WAL file

wmertens
ah ok, now I understand. So you would like some setting like "limit writes
to x/s", keeping the intermediate writes in memory.

Here is a great writeup on how to tune sqlite for writes:
https://stackoverflow.com/q/1711631/124416

But depending on your app you might just group a ton of writes in a single
transaction and retain all the regular safeties. E.g. suppose you have an
incoming stream of messages to write, you could group them and confirm
receipt of each message after the transaction completes.

On Tue, Apr 10, 2018, 10:43 AM Pavel Cernohorsky, <
[hidden email]> wrote:

> I understand that the SQLite tells the OS all the correct things, but as
> I described earlier, that may just produce way too much IOPS, even if
> everything in the chain does the correct thing down to the last silicon
> transistor. That is why I was asking about a way how to go around it by
> holding part of the newest data in memory only and manually checkpoint
> (and I hoped WAL will be a way). And I got some very good suggestions
> earlier.
>
> In my last mail, I have outlined a calculation suggesting the SSD may
> wear out pretty soon with the described load. Yesterday I have found a
> way how not to speculate and get some real data - Samsung's Enterprise
> SSDs which we use provide a diagnostics which measures a real world load
> and using their drive's SMART capabilities calculates the estimated
> SSD's lifetime, in other words saying how bad the load really is. It is
> actually a very useful thing, so if somebody else needs it as well, here
> is the link:
>
> https://www.slideshare.net/SamsungBusinessUSA/using-smart-attributes-to-estimate-enterprise-ssd-lifetime
>
> Pavel
>
>
>
> On 04/09/2018 02:47 PM, Simon Slavin wrote:
> > On 9 Apr 2018, at 8:41am, Pavel Cernohorsky <
> [hidden email]> wrote:
> >
> >> if you use bigger pro-level SSD, you get on a bit less that 1 year..
> still not good enough. Of course, big unknown in this is the constant
> representing the ability of the SSD controller to merge requests and
> minimize block re-writes.
> > If you compiled SQLite with standard options, and have the correct
> settings for
> >
> > PRAGMA fullfsync  (Macintosh only)
> > PRAGMA journal_mode
> > PRAGMA synchronous
> >
> > then SQLite tells the operating system all the correct things.
> >
> > Whether the operating system, the storage driver, and the storage
> subsystem itself do the right things is out of control of software.  Not
> only can you not tell what's being done, but it can change with what looks
> like a minor change in driver or hardware.
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users