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 unlink("/tmp/sqli