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
