Re: [sqlite] Avoiding use of temporary files
> Would it also be acceptable (assuming you want the protection offered > by the temporary tables) to do ... "PRAGMA temp_store = MEMORY" on > the database? > The temp_store pragma only effects the placement of temporary tables. But the rollback journal is not a table. The temp_store pragma has no effect on the placement of rollback journals. Rollback journals always go to disk. I am not sure of the side-effects it would lead to, but I would love to see a "PRAGMA journal = NO". Cheers -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
[EMAIL PROTECTED] wrote: Siebe Warners <[EMAIL PROTECTED]> wrote: 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. You must have a UNIQUE or CHECK constraint on your "t" table. The extra file being opened is a rollback journal for each particular UPDATE statement. This extra rollback journal is needed in case you hit a UNIQUE or CHECK constraint half way through the update and have to go back and undo those parts of the UPDATE that are already done. You can avoid this extra journal file by using UPDATE OR FAIL instead of UPDATE. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - Thanks for the info, using UPDATE OR FAIL took some 50ms off from what originally was around 80ms. The extra journals are no longer written, so this is a good way to avoid them. Now i only need to make sure the OR FAIL clause has no unsuspected effects, but considering the (lack of) complexity of the sql used in our application the behaviour should be very similar to the default OR ABORT. Thanks again, A happy SQLite user -- This message has been scanned for viruses and is believed to be clean - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
"Rich Rattanni" <[EMAIL PROTECTED]> wrote: > On 3/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Siebe Warners <[EMAIL PROTECTED]> wrote: > > > > > > When i perform a sequence of commands like: > > > BEGIN; > > > UPDATE t SET col="val1" WHERE key="key1"; > > > . > > > UPDATE t SET col="valx" WHERE key="keyx"; > > > COMMIT; > > > > > > Using strace i observe: > > > - a temp file is created at the start of the transaction > > > > The extra file being opened is a rollback journal for each > > particular UPDATE statement > > > > You can avoid this extra journal file by using UPDATE OR FAIL > > instead of UPDATE. > > Would it also be acceptable (assuming you want the protection offered > by the temporary tables) to do ... "PRAGMA temp_store = MEMORY" on > the database? > The temp_store pragma only effects the placement of temporary tables. But the rollback journal is not a table. The temp_store pragma has no effect on the placement of rollback journals. Rollback journals always go to disk. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
On 3/19/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Siebe Warners <[EMAIL PROTECTED]> wrote: > 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. > You must have a UNIQUE or CHECK constraint on your "t" table. The extra file being opened is a rollback journal for each particular UPDATE statement. This extra rollback journal is needed in case you hit a UNIQUE or CHECK constraint half way through the update and have to go back and undo those parts of the UPDATE that are already done. You can avoid this extra journal file by using UPDATE OR FAIL instead of UPDATE. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - Would it also be acceptable (assuming you want the protection offered by the temporary tables) to do ... "PRAGMA temp_store = MEMORY" on the database? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
Siebe Warners <[EMAIL PROTECTED]> wrote: > 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. > You must have a UNIQUE or CHECK constraint on your "t" table. The extra file being opened is a rollback journal for each particular UPDATE statement. This extra rollback journal is needed in case you hit a UNIQUE or CHECK constraint half way through the update and have to go back and undo those parts of the UPDATE that are already done. You can avoid this extra journal file by using UPDATE OR FAIL instead of UPDATE. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
I'll give 3.3.13 a try but it will be for debugging only for now. The project using the database is in the stage of avoiding all risk, so i'm not sure i will be able to convince the people to swap databases at this point. So were there any updates wrt transactions and temporary files? Thanks, Siebe [EMAIL PROTECTED] wrote: Siebe Warners <[EMAIL PROTECTED]> wrote: I'm using sqlite 3.2.7. Have you tried 3.3.13? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - -- This message has been scanned for viruses and is believed to be clean - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Avoiding use of temporary files
Siebe Warners <[EMAIL PROTECTED]> wrote: > I'm using sqlite 3.2.7. Have you tried 3.3.13? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Avoiding use of temporary files
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
