Hello SQLiters,
We're doing some benchmarks on our app using SQLite and it appears disk writes are bottlenecking selects against cached data. The benchmark is doing a series of selects only. The vast majority of these selects return a single row. The database is small, 1000ish rows. It is cached as iostat shows almost no disk reads. Meanwhile, iostat shows our pathetic little IDE drive is maxed with disk writes, 1500ish. This occurs when "PRAGMA default_synchronous = NORMAL" or "PRAGMA default_synchronous = FULL". But it doesn't happen when "PRAGMA default_synchronous = OFF". We observe a 5x speedup with "PRAGMA default_synchronous = OFF". Too bad its not appropriate for our app.
We ran our benchmark with strace . Here's illustrative output:
[pid 18933] fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=1}, 0xbe3ff0c4) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826, len=510}, 0xbe3ff0c4) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824, len=1}, 0xbe3ff0c4) = 0
[pid 18933] access("/u2/peter/sandbox/herc/var/spool/jsm.db-journal", F_OK) = -1 ENOENT (No such file or directory)
[pid 18933] fstat64(16, {st_mode=S_IFREG|0644, st_size=4664320, ...}) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0}, 0xbe3ff0b4) = 0
[pid 18933] write(2, "20050105T23:52:05: SQLite.cpp:19"..., 15220050105T23:52:05: SQLite.cpp:193 - (elapsed 0.000625) select user_id, namespace, value, length(value) from generic where user_id = ? AND namespace = ?
) = 152
We believe the disk writes are caused by fcntl(). Its updates to the bits on the file can be has resource consuming as an fsync() or write().
Also, we can't understand why each select statement has access() and fstat64() calls for each select.
Has anyone else seen this? Is this making sense? The result is odd enough that I fear someone will give the "pull your head out of your backside" response.
Much thanks, Bob Gilson
