On 03/11/2014 07:01 AM, Jono Poff wrote:
Hi,
I have an application that uses sqlite3.
Investigating a problem with the app stalling occasionally I found
that (every hour or two) an fdsync() system call from sqlite3_step()
was taking over 3 seconds to return.
On closer investigation, the file descriptor in these calls point to
the directory in which the database resides, rather than the database
of journal file itself, and are opened with O_LARGEFILE flag and no
O_RDWR and transfers zero bytes.
Presumably this is updating metadata, but I understood fdsync()
doesn't touch metadata.
Can anybody suggest a way to prevent sqlite making these fdsync()
calls? The database updates aren't high volume (maybe 20/sec max) but
the app needs to be responsive. I traced mmap() calls in my process
and found none relating to the sqlite db. I thought maybe I could
force sqlite to not cache writes this way. It seems that using pragma
to disable caching in sqlite simply exposes the database to
corruption rather than forcing synchronous writing to disk. Is this
correct?
I'm using Solaris 10 with zfs and I may be able to disable write
caching on the entire disk, but would prefer not to! Any ideas
appreciated.
It's probably syncing the directory to make sure that the entry
corresponding to a journal file has made it to disk before it
begins updating the database file. Otherwise, if a power failure
were to occur, following recovery SQLite might find that although
the contents of the journal file have been synced to disk, they
cannot be located as the directory entry has been lost. Leading
to corruption.
Using "PRAGMA journal_mode=WAL" might prevent SQLite from syncing
the directory as often.
Or "PRAGMA locking_mode=EXCLUSIVE; PRAGMA journal_mode=PERSIST;",
if there will only ever be a single connection to the database.
Or you could build with SQLITE_DISABLE_DIRSYNC, which omits all
syncs on directories. Of course, that will slightly increase the
chances of a power failure causing database corruption.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
