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 <
pavel.cernohor...@appeartv.com> 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
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
_______________________________________________
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
