On 20/03/2014 5:14 p.m., Nico Williams wrote:
On Wed, Mar 19, 2014 at 8:03 PM, Jono Poff <jonathan.p...@taitradio.com> wrote:
On 12/03/2014 12:01 p.m., Nico Williams wrote:
ZFS recordsize
Hi Nico,
I have one (or two) last question(s)...
All I need to do is prevent a single process ('process A') from being time
bound by making fsync() calls that clash with fsync() calls from other
processes using other files in the same dataset. I don't care if the other
processes occasionally block but process A has to be responsive.
fsync() from other processes on other files in the same pool/dataset
shouldn't cause the process you care about any additional latency
unless they are sync'ing enough data to swamp your pool's or ZIL's (if
you have one) write bandwidth.
If other processes are swamping that bandwidth then you'll need a
separate pool, with separate devices. (This isn't a problem in ZFS.
It's about your devices and the load you're putting on them.)
You should use DTrace to find out whether sync I/O is the issue. You
should search for DTrace scripts. There are ZFS users mailing lists
too.
I can accomplish this goal by preventing process A from making any fsync
calls and I can do this by setting pragma synchronous = off; I realise this
Yes.
is inviting data corruption in most circumstances, however 'process A' is
Yes.
the exclusive user of it's sqlite database and it's on ZFS, so my questions
are:
- with pragma synchronous = off; and assuming exclusive use of the
database, can I rely on ZFS atomic write to ensure data integrity?
ZFS will not corrupt itself. That's as much as you can guarantee this
way. SQLite3 is not log-structured ("COW") like ZFS, so SQLite3 can't
guarantee non-corruption in power outage/panic situations if you don't
have synchronous writes.
So: "no".
If you could make a ZFS snapshot after each SQLite3 transaction then
yes. But though snapshot creation is fast, it's almost certainly not
fast enough.
- does pragma locking_mode = exclusive; ensure this exclusive use assumption
is always true? (I'm ~assuming~ it does :)
No. It's orthogonal.
________________
This would be the Sword of Damocles solution to my problem so I'm hoping it
can be made risk free. Preliminary testing shows me it solves the original
problem.
Option B for me is to experiment with the impact of ZFS recordsize, SQLite
pagesize and SQLITE_IOCAP_ATOMIC on SQLite's use of fsync... I've looked at
the source, but it's not easy to infer much from it without being sure of
the context. Messing with these record sizes isn't an ideal option for me
and I can't easily add ZIL/SLOG.
You can set the page size when you first create a SQLite3 DB, before
the very first write.
First use PRAGMA page_size; to see what SQLite3 is using, and PRAGMA
default_page_size; to see what it would use by default if you didn't
set a page size. If it's using 512 then that's almost certainly
hurting you. You should definitely try a page_size/recordsize of 8KB.
Depending on your data bigger might help. You should not make it
bigger than 32KB.
If you have any ideas on this, could you please let me know. Also, if you
have any example code demonstrating use of sqlite3_io_methods and
xDeviceCharacteristics() it would be much appreciated. I'd like to verify
the actual device characteristics that SQLite is lifting from my dataset.
I don't. If you want to see when SQLite3 is fsync()ing (of
fdatasync()ing), just truss it.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Thanks for all that Nico. Not really what I wanted to hear. What does
pragma locking_mode = exclusive do if not lock other processes out of
the database (ie, what do you mean by orthogonal)?
I'm using dtrace and yes, the problem occurs when sqlite calls fsync
when another process is swamping disk io. Thats the root of my
problem. I may be able to fix it by using separate datasets for the
processes, but thats tricky in the circumstances and I can't easily
change the page size of the db either. Currently its 1k and the zdb
record size is 128k.
I'm thinking I'll create a dataset with recordsize = 1k and see if
sqlite will detect the disk is atomic write and dispense with the
fsyncs. Is this reasonable? From there I'll decide what to do next.
My understanding is the max page size in sqlite is 64k.
Also, is there a way to call sqlite3_io_methods.xDeviceCharacteristics()
from the sqlite api? I want to see if sqlite is getting
SQLITE_IOCAP_ATOMIC.
Thanks for all your help and good idea to check the ZFS list. I'll do that.
Cheers,
Jono.
--
------------------------------
This email, including any attachments, is only for the intended recipient.
It is subject to copyright, is confidential and may be the subject of legal
or other privilege, none of which is waived or lost by reason of this
transmission.
If you are not an intended recipient, you may not use, disseminate,
distribute or reproduce such email, any attachments, or any part thereof.
If you have received a message in error, please notify the sender
immediately and erase all copies of the message and any attachments.
Unfortunately, we cannot warrant that the email has not been altered or
corrupted during transmission nor can we guarantee that any email or any
attachments are free from computer viruses or other conditions which may
damage or interfere with recipient data, hardware or software. The
recipient relies upon its own procedures and assumes all risk of use and of
opening any attachments.
------------------------------
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
