Hi all,
I'm using sqlite on an embedded system with flash EEPROM as the medium.
Performance of the flash is not too good, so i'm trying to avoid file
system access where possible without running risk of database corruption.
So the database file and the journal files need to be written to the
flash, but i also noticed temp files being written to disk. I'm using
sqlite 3.2.7.
When i perform a sequence of commands like:
BEGIN;
UPDATE t SET col="val1" WHERE key="key1";
UPDATE t SET col="val2" WHERE key="key2";
UPDATE t SET col="val3" WHERE key="key3";
....
UPDATE t SET col="valx" WHERE key="keyx";
COMMIT;
Using strace i observe:
- the journal file is created at the start of the transaction
- a temp file is created at the start of the transaction
- the journal file is written at the start of the sequence and some more
data is appended somewhere halfway
- the temp file is written at every UPDATE
- at the commit the journal file is written, the database file is
updated, and journal file and temp file are removed.
For completeness the strace output of such a sequence is appended to
this message.
I was surprised by this outcome as my expectation was that "disk" access
could be limited by using a transaction. What i am trying to achieve is
to eliminate the write actions to the temporary file in order to gain
some performance.
Reading the documentation i was convinced that i should be able to use
the temp_store pragma to influence writing of the temp file, but
unfortunately that does not seem to work. Does anyone have an idea how i
can get sqlite not to write the temporary file?
Thanks in advance,
Siebe
strace output:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Process 206 attached - interrupt to quit
--- SIGRTMIN (Unknown signal 32) @ 0 (0) ---
rt_sigsuspend([]) = 2116025440
sched_yield() = 2116025440
write(1, "\n", 1) = 2116025216
fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0x7e1ff450) = 16
fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0x7e1ff450) = 16
fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0x7e1ff450) = 275817880
access("/var/db/addf.db-journal", F_OK) = 16
fstat64(16, {st_mode=S_IFREG|0644, st_size=113664, ...}) = 16
_llseek(16, 0, [0], SEEK_SET) = 16
read(16, "SQLite format 3\0", 1024) = 16
_llseek(16, 69632, [69632], SEEK_SET) = 16
read(16, "\5\0\0\0\2\3\366\0\0\0\0#\3\373\3\366", 1024) = 16
_llseek(16, 70656, [70656], SEEK_SET) = 16
read(16, "\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310", 1024) = 16
_llseek(16, 30720, [30720], SEEK_SET) = 16
read(16, "\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2\373\2\324"...,
1024) = 2116024456
fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0x7e1ff420) = 2116025256
fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0x7e1ff3f0) = 16
fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0x7e1ff3f0) = 16
fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0x7e1ff3f0) = 275817880
access("/var/db/addf.db-journal", F_OK) = 16
fstat64(16, {st_mode=S_IFREG|0644, st_size=113664, ...}) = 16
_llseek(16, 0, [0], SEEK_SET) = 16
read(16, "SQLite format 3\0", 1024) = 16
_llseek(16, 70656, [70656], SEEK_SET) = 16
read(16, "\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272\3\254\3"...,
1024) = 2116024720
fcntl64(16, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=1073741825,
len=1}, 0x7e1ff4f0) = 275817880
access("/var/db/addf.db-journal", F_OK) = 275817880
open("/var/db/addf.db-journal", O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE, 0644)
= 29
fstat64(29, {st_mode=S_IFDIR|02041000250, st_size=16, ...}) = 275817864
open("/var/db", O_RDONLY|O_LARGEFILE) = 29
_llseek(29, 0, [0], SEEK_SET) = 29
write(29, "\331\325\5\371 \241c\327", 8) = 29
write(29, "\0\0\0\0", 4) = 29
write(29, "[EMAIL PROTECTED]", 4) = 29
write(29, "\0\0\0o", 4) = 29
write(29, "\0\0\2\0", 4) = 29
_llseek(29, 511, [511], SEEK_SET) = 29
write(29, "\0", 1) = 805842344
stat64("/var/tmp", {st_mode=0, st_size=9088250304414123248, ...}) =
805842356
stat64("/usr/tmp", {st_mode=0, st_size=9088250304414123248, ...}) =
805842368
stat64("/tmp", {st_mode=S_IFDIR|0777, st_size=0, ...}) = 805842368
access("/tmp", R_OK|W_OK|X_OK) = 2116023440
access("/tmp/sqlite_jT5odBG4x7ALEgD", F_OK) = 2116023440
access("/tmp/sqlite_jT5odBG4x7ALEgD", F_OK) = 2116023440
open("/tmp/sqlite_jT5odBG4x7ALEgD", O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE,
0644) = 31
fstat64(31, {st_mode=02040570200, st_size=9088250098785467100, ...}) =
2116023440
unlink("/tmp/sqlite_jT5odBG4x7ALEgD") = 16
_llseek(16, 69632, [69632], SEEK_SET) = 16
read(16, "\5\0\0\0\2\3\366\0\0\0\0#\3\373\3\366", 1024) = 16
_llseek(16, 30720, [30720], SEEK_SET) = 16
read(16, "\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2\373\2\324"...,
1024) = 29
write(29, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1032) = 29
write(29, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1032) = 31
_llseek(31, 0, [1], SEEK_SET) = 2116024728
write(1, "db::set exec: 249 ms\n", 21) = 2116025256
kill(204, SIGRTMIN) = 31
write(31, "\0\0\0F\n\0\0\0?\0\216\0\0\216\3\362\3\344\3\326\3\310"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\0=\3\333\3\266\3\221\3l\3G\3"...,
1028) = 31
_llseek(31, 0, [1400348317057025], SEEK_SET) = 2116024728
write(1, "db::set exec: 90 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\0\216\3\344\3\326\3\310"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\0=\3\266\3\221\3l\3G\3"...,
1028) = 31
_llseek(31, 0, [1947209322987521], SEEK_SET) = 2116024728
write(1, "db::set exec: 105 ms\n", 21) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\0\216\3\326\3\310"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\0=\3\221\3l\3G\3"...,
1028) = 31
_llseek(31, 0, [2562798395588609], SEEK_SET) = 2116024728
write(1, "db::set exec: 88 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\0\216\3\310"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\0=\3l\3G\3"...,
1028) = 31
_llseek(31, 0, [3107778205843458], SEEK_SET) = 2116024728
write(1, "db::set exec: 85 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024456
kill(204, SIGRTMIN) = 2116024720
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\0\216"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\0=\3G\3"...,
1028) = 31
_llseek(31, 0, [3637704155725826], SEEK_SET) = 2116024728
write(1, "db::set exec: 87 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\0=\3"...,
1028) = 31
_llseek(31, 0, [4277555498582018], SEEK_SET) = 2116024728
write(1, "db::set exec: 90 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\0"...,
1028) = 31
_llseek(31, 0, [4822462294392834], SEEK_SET) = 2116024728
write(1, "db::set exec: 85 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024456
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5347199923781634], SEEK_SET) = 2116024728
write(1, "db::set exec: 86 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5875987707330563], SEEK_SET) = 2116024728
write(1, "db::set exec: 89 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 31
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [6433225354248195], SEEK_SET) = 2116024728
write(1, "db::set exec: 86 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024456
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [6976650386341891], SEEK_SET) = 2116024728
write(1, "db::set exec: 91 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [7523494212403203], SEEK_SET) = 2116024728
write(1, "db::set exec: 91 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [8089725520838659], SEEK_SET) = 2116024728
write(1, "db::set exec: 81 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [8617619951190019], SEEK_SET) = 2116024728
write(1, "db::set exec: 82 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [4865575176110083], SEEK_SET) = 2116024728
write(1, "db::set exec: 92 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5407496969650179], SEEK_SET) = 2116024728
write(1, "db::set exec: 83 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024720
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5920908770279427], SEEK_SET) = 2116024728
write(1, "db::set exec: 91 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [6488733511581699], SEEK_SET) = 2116024728
write(1, "db::set exec: 81 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [3516435754123267], SEEK_SET) = 2116024728
write(1, "db::set exec: 87 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [4050549297119235], SEEK_SET) = 2116024728
write(1, "db::set exec: 89 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024720
kill(204, SIGRTMIN) = 204
kill(204, SIGRTMIN) = 31
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [4596310791421955], SEEK_SET) = 2116024728
write(1, "db::set exec: 95 ms\n", 20) = 2116025256
kill(200, SIGRTMIN) = 2116024720
kill(204, SIGRTMIN) = 31
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5196051434700803], SEEK_SET) = 2116024728
write(1, "db::set exec: 93 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [5788009597239299], SEEK_SET) = 2116024728
write(1, "db::set exec: 86 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 31
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\37\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2"...,
1028) = 31
_llseek(31, 0, [2045589843869699], SEEK_SET) = 2116024728
write(1, "db::set exec: 99 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 16
_llseek(16, 4096, [4096], SEEK_SET) = 16
read(16, "\r\0\0\0\30\0X\0\3\331\3\262\3\213\3d\3=\3\26\0X\2\357"...,
1024) = 2116024456
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 29
write(29, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\3\262\3\213\3d\3=\3\26\0"...,
1032) = 31
_llseek(31, 0, [2636753437458435], SEEK_SET) = 2116024728
write(1, "db::set exec: 90 ms\n", 20) = 2116025256
kill(204, SIGRTMIN) = 2116024720
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\0X\3\331\3\262\3\213\3d\3=\3\26"...,
1028) = 31
_llseek(31, 0, [3183777652146179], SEEK_SET) = 2116024728
write(1, "db::set exec: 82 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\0X\3\262\3\213\3d\3=\3\26"...,
1028) = 31
_llseek(31, 0, [3758637549879299], SEEK_SET) = 2116024728
write(1, "db::set exec: 94 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\3\262\0X\3\213\3d\3=\3\26"...,
1028) = 31
_llseek(31, 0, [4322132964147203], SEEK_SET) = 2116024728
write(1, "db::set exec: 91 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\3\262\3\213\0X\3d\3=\3\26"...,
1028) = 31
_llseek(31, 0, [606565346312195], SEEK_SET) = 2116024728
write(1, "db::set exec: 81 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\3\262\3\213\3d\0X\3=\3\26"...,
1028) = 31
_llseek(31, 0, [1141275889762307], SEEK_SET) = 2116024728
write(1, "db::set exec: 83 ms\n", 20) = 2116025256
write(31, "\0\0\0F\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272"...,
1028) = 31
write(31, "\0\0\0\5\r\0\0\0\30\0X\0\3\331\3\262\3\213\3d\3=\0X\3\26"...,
1028) = 31
_llseek(31, 0, [1695687448199171], SEEK_SET) = 2116024728
write(1, "db::set exec: 89 ms\n", 20) = 2116025256
write(29, "\0\0\0\1SQLite format 3\0\4\0\1\1\0@ \0\0\22\34"..., 1032) = 29
fsync(29) = 30
fsync(30) = 30
close(30) = 29
_llseek(29, 8, [3], SEEK_SET) = 29
write(29, "\0\0\0\4", 4) = 29
_llseek(29, 4640, [4640], SEEK_SET) = 29
fdatasync(29) = 16
fcntl64(16, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0x7e1ff440) = 16
fcntl64(16, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0x7e1ff440) = 16
_llseek(16, 0, [0], SEEK_SET) = 16
write(16, "SQLite format 3\0\4\0\1\1\0@ \0\0\22\35\0\0\0\0"..., 1024) = 16
_llseek(16, 70656, [70656], SEEK_SET) = 16
write(16, "\n\0\0\0?\0\216\0\3\362\3\344\3\326\3\310\3\272\3\254\3"...,
1024) = 16
_llseek(16, 30720, [30720], SEEK_SET) = 16
write(16, "\r\0\0\0\31\0=\0\3\333\3\266\3\221\3l\3G\3\"\2\373\2\324"...,
1024) = 16
_llseek(16, 4096, [4096], SEEK_SET) = 16
write(16, "\r\0\0\0\30\0X\0\3\331\3\262\3\213\3d\3=\3\26\0X\2\357"...,
1024) = 16
fsync(16) = 31
close(31) = 29
close(29) = 275817880
unlink("/var/db/addf.db-journal") = 16
fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741826,
len=510}, 0x7e1ff4a0) = 16
fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=1073741824,
len=2}, 0x7e1ff4a0) = 16
fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, start=0, len=0},
0x7e1ff460) = 2116025256
--
This message has been scanned for viruses and is believed to be clean
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
