Re: [sqlite] SQLITE 3.7.3 bug report (shell) - outp ut in column mode does not align UTF8-strings correctl y
Igor Tandetnik writes: > > Niklas Bäckman wrote: > > Columns with special characters like ("å" "ä" "å") get too short widths when > > output. > > > > I guess this is due to the shell not counting actual UTF8 *characters/code > > points* when calculating the widths, but instead only > > counting the plain bytes in the strings, so they will seem longer until they > > are actually printed to the console. > > Note that counting codepoints, while it happens to help with your particular data, won't help in general. > Consider combining diacritics: U+00E4 (small A with diaeresis) looks the same as U+0061 U+0308 (small > letter A + combining diaeresis) when printed on the console. You are right of course. The shell should not count code points, but graphemes. http://unicode.org/faq/char_combmark.html#7 I guess that this probably falls out of the "lite" scope of SQLITE though? Like how it does not support case-insensitive comparison of non-ASCII characters. Or would it be possible to write such a graphemelen(s) function in not too many lines of C code without needing any external Unicode libraries? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confused about Multiple Indexes
Hi All, I am looking at a database that someone created earlier and for one of the table, the schema shows: CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, ...); CREATE INDEX IDX1 on tx(name ASC); CREATE INDEX IDX2 on tx(type, search_name ASC); I am under the impression that IDX1 is not necessary in this case. Would there be any (significant) benefit in retaining that index? Thanks, Mohit. 26/11/2010 | 2:54 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Queries getting slow with concurrency
On 25 Nov 2010, at 10:15pm, Prakash Reddy Bande wrote: > I have observed that when multiple processes from "different" hosts are > concurrently doing the queries, the performance of sqlite degrades > considerably. > Queries are read only hence I believe that there should not be any locking > issues (no busy handler called). Is there a chance you have network congestion ? Your server can only send out so many bytes per second to its clients and it might have reached the number of bytes its network card can handle. Your operating system should have some tool that tells you how many bytes per second input and output your network card is handling. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Queries getting slow with concurrency
Hi, I have observed that when multiple processes from "different" hosts are concurrently doing the queries, the performance of sqlite degrades considerably. Queries are read only hence I believe that there should not be any locking issues (no busy handler called). I will dig a little more in my application (not much I can do except check if my busy handler is getting called). Only other problem I can think of is file system does not like being read concurrently, but we use this file system (NTFS network drive) for lot of other operations which happen concurrently. Regards, Prakash Bande ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
> So, if you have a large scale system, you have two choices: block during > the checkpoint, or suffer from huge WAL file (and a crash if the file is > too big). For a large scale system you have a third choice: use some other RDBMS which is implemented in one process and has a much better control over its data and much better communication between readers and writers. Pavel On Thu, Nov 25, 2010 at 4:07 AM, Yoni wrote: > Hi, > > > In which case the writer would have to block on the reader, > > which is something that the current system doesn't suffer from. > I agree when we are talking about small systems, which does not > read/write allot. But, on a larger scale system, who have a several > threads reading and writing to the DB non-stop, there always will be at > least one reader who block the checkpoint from being completed, thus > making the WAL file grow forever (unless of course, you block during the > checkpoint - but then you make your system unresponsive). > > So, if you have a large scale system, you have two choices: block during > the checkpoint, or suffer from huge WAL file (and a crash if the file is > too big). > > > You can never be sure when is the right time to wrap around. > > If you wrap around too early, you run the risk of hitting the wall > > put up by the earliest reader, who is still using the end of the > > WAL file. > If we choose a large enough size to be free at the beginning of the file > (can be proportional to the WAL file size of just a constant), the > chances that this will happen a very low. and even when this will > happen, the write wont have to block the reader, it just have to force > running checkpoint (while they are reading a page), and wait until they > finish read the specific page (not the whole read transaction), since > the next page they need to read is already in the main DB file. > > > The only solution around this would be to have more than 1 WAL file. > > To avoid hitting this same problem with multiple WAL files, you'd > > need to support an arbitrary N number of WAL files. > I think my solution will work too. on PostgreSQL they already attend the > problem of how many WAL files should exist. see here: > http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html. > > > On one system of mine, where blocking is an issue, I > > buffer up the write messages, and flush them on a background thread. > > Of course this may not be practical for you... > I think this kind of a solution can work of me too (I dont want to block > the main thread EVER, and I have a steady stream of writes to the DB). > Can you please elaborate how did you implemented your solution? > > Yoni. > > > On 25/11/2010 8:42 AM, Ben Harper wrote: >> A cyclical WAL file has this problem: >> >> You can never be sure when is the right time to wrap around. >> If you wrap around too early, you run the risk of hitting the wall >> put up by the earliest reader, who is still using the end of the >> WAL file. In which case the writer would have to block on the reader, >> which is something that the current system doesn't suffer from. >> My guess is that a very high proportion of use cases would never >> suffer this issue, provided they set an appropriate wrap around size >> but this does place that burden on the implementer - of picking >> the right heuristics. >> >> It is a pathological case, but imagine an implementer never tests >> for this wraparound issue, and builds a deadlock into his app >> that arises when a writer blocks on a reader. This is the kind of >> unexpected behaviour that could really bite you. >> >> The only solution around this would be to have more than 1 WAL file. >> To avoid hitting this same problem with multiple WAL files, you'd >> need to support an arbitrary N number of WAL files. And that, >> undoubtedly, is a more complex implementation than what currently >> exists. I imagine it's quite a task for the SQLite developers to >> get 100% branch test coverage. >> >> On one system of mine, where blocking is an issue, I >> buffer up the write messages, and flush them on a background thread. >> Of course this may not be practical for you... >> >> Ben >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni >> Sent: 24 November 2010 04:46 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] WAL file size >> >> Hi, >> >> I am currently developing a system, which have the following requirements: >> 1. should be very fast and responsive >> 2. run forever (or at least a very long time) without restart. >> 3. have steady stream of writes to the DB. >> >> Currently I am using sqlite with one sql connection, and I run sql >> checkpoint every once in a while (e.g. after 5 minutes, or after 1 >> inserts to the DB) - I don't use auto checkpoint. >> Since I run the checkpoint in the main thread, the system is >> unresponsive for a long time (can take from 400ms to 2ms!)
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
On 25 Nov 2010, at 5:25pm, Tito Ciuro wrote: > 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would > fail right away is it wasn't a valid database. > 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds. > > However, I'm wondering whether (1) is redundant. If the integrity check > detects right away that the file is not a SQLite3 database, then we're good. > But if the integrity check reads the whole file before failing, perhaps > running (1) would not be such a bad after all. > > Also, if there is a need to know whether a database is corrupted, an > optimization seems to be to issue "PRAGMA integrity_check (1)" statement, > which would bail out at the first sign of trouble. As stated in the docs: > http://www.sqlite.org/pragma.html I believe that if Nick's going to do (2) anyway, (1) is redundant. In my own case I couldn't do (2) because some of the databases I was dealing with would have made it take too long. And my hardware were extremely trustworthy anyway: I was really just checking that the files were the right files, and someone hadn't (for example) renamed a journal file as the database file WHICH IS SOMETHING SOMEONE ACTUALLY DID TO ME ONCE ARGH ! And I agree Nick only needs to see the first error from 'PRAGMA integrity_check' so he can use 1 as a parameter. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
Hi Nick, On Nov 25, 2010, at 2:09 PM, Nick Shaw wrote: > As Igor points out, that may not fail if the corruption is in a table > you don't query. > > In this case, the databases I'm working with are either small enough for > the speed of an integrity check not to be an issue, or the integrity of > the database is more important than a delayed DB open (which happens in > a seprate thread to my apps' main GUI, so the user won't notice an issue > anyway). > > Thanks for all the help peeps, > Nick. Agreed. However, the original email looks like it's a two-part question: opening non-SQLite files and detecting corrupted databases. Looks like the solution would be to integrate a method combining both Igor and Simon's solution: 1) SELECT on sqlite_master. All SQLite3 databases contain it, so it would fail right away is it wasn't a valid database. 2) Issue a "PRAGMA integrity_check" statement, if (1) succeeds. However, I'm wondering whether (1) is redundant. If the integrity check detects right away that the file is not a SQLite3 database, then we're good. But if the integrity check reads the whole file before failing, perhaps running (1) would not be such a bad after all. Also, if there is a need to know whether a database is corrupted, an optimization seems to be to issue "PRAGMA integrity_check (1)" statement, which would bail out at the first sign of trouble. As stated in the docs: http://www.sqlite.org/pragma.html > PRAGMA integrity_check; > PRAGMA integrity_check(integer) > > This pragma does an integrity check of the entire database. It looks for > out-of-order records, missing pages, malformed records, and corrupt indices. > If any problems are found, then strings are returned (as multiple rows with a > single column per row) which describe the problems. At most integer errors > will be reported before the analysis quits. The default value for integer is > 100. If no errors are found, a single row with the value "ok" is returned. Cheers, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs - Found word(s) list error in the Text body
Tito Ciuro wrote: > On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > > Run "PRAGMA integrity_check" right after opening. > > That could be a potentially slow operation if the database is valid and contains lots of records. > Wouldn't be better to issue a SELECT statement and let SQLite come back with a SQLITE_NOTADB error right away? As Igor points out, that may not fail if the corruption is in a table you don't query. In this case, the databases I'm working with are either small enough for the speed of an integrity check not to be an issue, or the integrity of the database is more important than a delayed DB open (which happens in a seprate thread to my apps' main GUI, so the user won't notice an issue anyway). Thanks for all the help peeps, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copying data from memory to file
Berryl Hesh wrote: > After generating an in memory db schema, I use the attach command to > replicate that schema to a file Consider using backup API: http://www.sqlite.org/c3ref/backup_finish.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
On 25 Nov 2010, at 4:06pm, Tito Ciuro wrote: > Wouldn't be better to issue a SELECT statement and let SQLite come back with > a SQLITE_NOTADB error right away? In one of my applications I used to do a SELECT on sqlite_master. This confirms that the file exists, that it's not blank, and that it was used as an SQLite3 database somewhere along the way and not, say, an SQLite2 database or a text file. It takes so little time you can include it in startup routines without people complaining. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
On Thu, Nov 25, 2010 at 11:06 AM, Tito Ciuro wrote: > On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > > > Run "PRAGMA integrity_check" right after opening. > > That could be a potentially slow operation if the database is valid and > contains lots of records. > > Wouldn't be better to issue a SELECT statement and let SQLite come back > with a SQLITE_NOTADB error right away? > > I ran into this problem too. What I did was to execute pragma synchronous=full (or whatever mode you are using) after opening the database. This will fail if the file is not really an SQLite db or you have the wrong key for an encrypted db. It doesn't handle the problem of a corrupted db, but as has been mentioned, there's not a fast way to detect that. Jim -- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
On 25 Nov 2010, at 12:51, Igor Tandetnik wrote: > Run "PRAGMA integrity_check" right after opening. That could be a potentially slow operation if the database is valid and contains lots of records. Wouldn't be better to issue a SELECT statement and let SQLite come back with a SQLITE_NOTADB error right away? -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
Nick Shaw wrote: >> Do you really want SQLite to scan a gigabytes-large file every time it's >> opened? > > Depends - obviously not if you need quick access to the data, however if > the gigabytes-large file is corrupt, do you really want to wait until > you execute the first "substantive" statement to discover the DB is > corrupt? You won't even necessarily know at this point. Only when one of your statements actually hits the corrupted part. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly
Niklas Bäckman wrote: > Columns with special characters like ("å" "ä" "å") get too short widths when > output. > > I guess this is due to the shell not counting actual UTF8 *characters/code > points* when calculating the widths, but instead only > counting the plain bytes in the strings, so they will seem longer until they > are actually printed to the console. Note that counting codepoints, while it happens to help with your particular data, won't help in general. Consider combining diacritics: U+00E4 (small A with diaeresis) looks the same as U+0061 U+0308 (small letter A + combining diaeresis) when printed on the console. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
Igor Tandetnik wrote: > > > Is there a reason that sqlite3_open() will happily "open" a non-sqlite > > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which > > would seem a more obvious return value? > > SQLite doesn't actually touch the file until the first "substantive" statement is executed on the connection. This allows one to set various PRAGMAs that can only be set before the database is > created. Ah, that makes sense, ok. > > It will also happily open a > > sqlite database that it will later say is corrupt when I try to query > > some data, implying it doesn't do data integrity checks on the > > database on opening? > > Do you really want SQLite to scan a gigabytes-large file every time it's opened? Depends - obviously not if you need quick access to the data, however if the gigabytes-large file is corrupt, do you really want to wait until you execute the first "substantive" statement to discover the DB is corrupt? It makes coding for corrupt database more complex if you only find out (possibly much) further down the code stream... > > > So... Assuming the above is how sqlite is meant to work, what would be > > the best way to ascertain if a successfully opened file is ok to use? > > Run "PRAGMA integrity_check" right after opening. Ah, that's what I was looking for! Thanks Igor, I'll try that. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
On Thu, Nov 25, 2010 at 6:08 PM, Nick Shaw wrote: > Hi all, > > > > So... Assuming the above is how sqlite is meant to work, what would be > the best way to ascertain if a successfully opened file is ok to use? > Currently once the file has been opened by sqlite3_open(), and provided > the file existed prior to that call, I then run the query "SELECT > COUNT(rowid) from sqlite_master" and if it fails, I assume the DB is > corrupt or not an sqlite file. This seems to work, however it seems a > bit long winded and wondered if there was a better way of checking this > kind of thing? > > When I implemented an encryption scheme with an VFS, I noticed that sqlite doesn't read anything until specially requested, so I ended up with reading encoding pragma for this task. At least if I provided wrong password, sqlite just by trying reading this pragma returned an error about something like "encrypted or bad" Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open on non-DB files / corrupt DBs
Nick Shaw wrote: > Is there a reason that sqlite3_open() will happily "open" a non-sqlite > file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which > would seem a more obvious return value? SQLite doesn't actually touch the file until the first "substantive" statement is executed on the connection. This allows one to set various PRAGMAs that can only be set before the database is created. > It will also happily open a > sqlite database that it will later say is corrupt when I try to query > some data, implying it doesn't do data integrity checks on the database > on opening? Do you really want SQLite to scan a gigabytes-large file every time it's opened? > So... Assuming the above is how sqlite is meant to work, what would be > the best way to ascertain if a successfully opened file is ok to use? Run "PRAGMA integrity_check" right after opening. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_open on non-DB files / corrupt DBs
Hi all, Is there a reason that sqlite3_open() will happily "open" a non-sqlite file, returning SQLITE_OK, instead of returning SQLITE_NOTADB, which would seem a more obvious return value? It will also happily open a sqlite database that it will later say is corrupt when I try to query some data, implying it doesn't do data integrity checks on the database on opening? So... Assuming the above is how sqlite is meant to work, what would be the best way to ascertain if a successfully opened file is ok to use? Currently once the file has been opened by sqlite3_open(), and provided the file existed prior to that call, I then run the query "SELECT COUNT(rowid) from sqlite_master" and if it fails, I assume the DB is corrupt or not an sqlite file. This seems to work, however it seems a bit long winded and wondered if there was a better way of checking this kind of thing? Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
> threads reading and writing to the DB non-stop, there always will be at > least one reader who block the checkpoint from being completed, thus It is in essence writing, not reading, that blocks the checkpointing. If you stopped writing for a while, the checkpointer would catch up, and it could rewind. > I think this kind of a solution can work of me too (I dont want to block > the main thread EVER, and I have a steady stream of writes to the DB). > Can you please elaborate how did you implemented your solution? My specific application maintains a history of tiles that are touched by a renderer. A tile record looks like this: struct Tile { char Signature[20]; int64 X, int64 Y; } I simply keep a queue, in essence: vector Queue; The UI thread adds tiles to the queue, and a background thread fetches them off the queue, does the writing, and the checkpointing. By having the writing and checkpointing on the same thread you can guarantee that the WAL will rewind. By the sounds of it, your application may be harder to abstract, the way I do here, with a simple queue of instructions. Aside from rolling WAL logs, I can't think of any other way that you could achieve the responsiveness that you require. I have no contact with the SQLite authors, but I wouldn't place my bets on rolling logs being available any time soon. Ben -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni Sent: 25 November 2010 11:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] WAL file size Hi, > In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. I agree when we are talking about small systems, which does not read/write allot. But, on a larger scale system, who have a several threads reading and writing to the DB non-stop, there always will be at least one reader who block the checkpoint from being completed, thus making the WAL file grow forever (unless of course, you block during the checkpoint - but then you make your system unresponsive). So, if you have a large scale system, you have two choices: block during the checkpoint, or suffer from huge WAL file (and a crash if the file is too big). > You can never be sure when is the right time to wrap around. > If you wrap around too early, you run the risk of hitting the wall > put up by the earliest reader, who is still using the end of the > WAL file. If we choose a large enough size to be free at the beginning of the file (can be proportional to the WAL file size of just a constant), the chances that this will happen a very low. and even when this will happen, the write wont have to block the reader, it just have to force running checkpoint (while they are reading a page), and wait until they finish read the specific page (not the whole read transaction), since the next page they need to read is already in the main DB file. > The only solution around this would be to have more than 1 WAL file. > To avoid hitting this same problem with multiple WAL files, you'd > need to support an arbitrary N number of WAL files. I think my solution will work too. on PostgreSQL they already attend the problem of how many WAL files should exist. see here: http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html. > On one system of mine, where blocking is an issue, I > buffer up the write messages, and flush them on a background thread. > Of course this may not be practical for you... I think this kind of a solution can work of me too (I dont want to block the main thread EVER, and I have a steady stream of writes to the DB). Can you please elaborate how did you implemented your solution? Yoni. On 25/11/2010 8:42 AM, Ben Harper wrote: > A cyclical WAL file has this problem: > > You can never be sure when is the right time to wrap around. > If you wrap around too early, you run the risk of hitting the wall > put up by the earliest reader, who is still using the end of the > WAL file. In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. > My guess is that a very high proportion of use cases would never > suffer this issue, provided they set an appropriate wrap around size > but this does place that burden on the implementer - of picking > the right heuristics. > > It is a pathological case, but imagine an implementer never tests > for this wraparound issue, and builds a deadlock into his app > that arises when a writer blocks on a reader. This is the kind of > unexpected behaviour that could really bite you. > > The only solution around this would be to have more than 1 WAL file. > To avoid hitting this same problem with multiple WAL files, you'd > need to support an arbitrary N number of WAL files. And that, > undoubtedly, is a more complex implementation than what currently > exists. I imagine it's quite a task for the SQ
Re: [sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)
On 11/25/2010 03:45 PM, Philip Graham Willoughby wrote: > Hi all, > > I'm noticing a new failure with SQLite 3.7.3 as compared to the previous > version I was using, 3.6.23.1. Are you able to share the database and the query that causes the assert() to fail? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 2:00pm, Dan Kennedy wrote: > In WAL mode with synchronous=NORMAL, when the user commits > a transaction, it is written into the WAL file. No sync > until a checkpoint happens. So if the power fails, you might > lose all the transactions that have been written into the WAL > file. Ahha. That explains it. Thanks for that. This makes WAL mode less attractive to me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 08:49 PM, Simon Slavin wrote: > > On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > >> On 11/25/2010 03:24 PM, Twylite wrote: >>> > > Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't > like. > >>> I am seeking technical information on the durability of transactions >>> with journal_mode=WAL and synchronous=NORMAL. >>> >>> Specifically, in the event of a power failure, can the following ever >>> happen: >>> (1) Loss of the last transaction completed. >>> (2) Loss of some indeterminate number of recent transactions. >>> (3) Loss of the entire WAL file. >>> (4) Corrupt of the database making it unopenable. >> >> Possible. Possible. Possible. Not supposed to be possible. > > Is (2) really possible ? I thought that, unless the power failure caused > corruption at the hard disk level, a maximum of one transaction could be lost. > (2) and (3) are really the same question. In WAL mode with synchronous=NORMAL, when the user commits a transaction, it is written into the WAL file. No sync until a checkpoint happens. So if the power fails, you might lose all the transactions that have been written into the WAL file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 25 Nov 2010, at 8:36am, Dan Kennedy wrote: > On 11/25/2010 03:24 PM, Twylite wrote: >> Argh. Trevor, I'm going to find Stephenie Meyer and do something she doesn't like. >> I am seeking technical information on the durability of transactions >> with journal_mode=WAL and synchronous=NORMAL. >> >> Specifically, in the event of a power failure, can the following ever >> happen: >> (1) Loss of the last transaction completed. >> (2) Loss of some indeterminate number of recent transactions. >> (3) Loss of the entire WAL file. >> (4) Corrupt of the database making it unopenable. > > Possible. Possible. Possible. Not supposed to be possible. Is (2) really possible ? I thought that, unless the power failure caused corruption at the hard disk level, a maximum of one transaction could be lost. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copying data from memory to file
On 24 November 2010 18:16, Berryl Hesh wrote: > Hello: > > After generating an in memory db schema, I use the attach command to > replicate that schema to a file - at least that is my intention. I get > a no such table error, so something is wrong somewhere. Can someone > get me on the right track? I see nothing in your code that creates the schema. You are trying to copy data to non-existent tables. "SELECT SQL FROM sqlite_master WHERE type='table';" will get you the SQL to execute in the attached db to recreate the schema tables, then you can copy the data. You may need to handle views, triggers and indices as well as tables. > > Cheers, > Berryl > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cppcheck error
On Wed, Nov 24, 2010 at 6:49 PM, Stephen Blessing < stephen.bless...@datasplice.com> wrote: > Hi SQLite folks, > > I ran cppcheck and got this error: > > [./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc > mistake: "p" nulled but not freed upon failure. > This should be a "warning" not an "error" (is that a bug in cppcheck?) and in this case the warning is wrong. The SQLite code is correct as it stands. sqlite3MemRealloc() has the same semantics as realloc() and so the p pointer should definitely not be freed. > > /* > ** Like realloc(). Resize an allocation previously obtained from > ** sqlite3MemMalloc(). > ** > ** For this low-level interface, we know that pPrior!=0. Cases where > ** pPrior==0 while have been intercepted by higher-level routine and > ** redirected to xMalloc. Similarly, we know that nByte>0 becauses > ** cases where nByte<=0 will have been intercepted by higher-level > ** routines and redirected to xFree. > */ > static void *sqlite3MemRealloc(void *pPrior, int nByte){ > sqlite3_int64 *p = (sqlite3_int64*)pPrior; > assert( pPrior!=0 && nByte>0 ); > nByte = ROUND8(nByte); > p--; > (Line 13028) p = realloc(p, nByte+8 ); > if( p ){ >p[0] = nByte; >p++; > }else{ >testcase( sqlite3GlobalConfig.xLog!=0 ); >sqlite3_log(SQLITE_NOMEM, > "failed memory resize %u to %u bytes", > sqlite3MemSize(pPrior), nByte); > } > return (void*)p; > } > > Thanks and best regards, > > Stephen Blessing > Quality Assurance > DataSplice, LLC > Phone: 970-232-1647 > stephen.bless...@datasplice.com > For support issues please keep our team > in the loop and CC: supp...@datasplice.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cppcheck error
Hi SQLite folks, I ran cppcheck and got this error: [./PlugIns/SQLiteDataStore/SQLite/sqlite3.c:13028]:(error) Common realloc mistake: "p" nulled but not freed upon failure. /* ** Like realloc(). Resize an allocation previously obtained from ** sqlite3MemMalloc(). ** ** For this low-level interface, we know that pPrior!=0. Cases where ** pPrior==0 while have been intercepted by higher-level routine and ** redirected to xMalloc. Similarly, we know that nByte>0 becauses ** cases where nByte<=0 will have been intercepted by higher-level ** routines and redirected to xFree. */ static void *sqlite3MemRealloc(void *pPrior, int nByte){ sqlite3_int64 *p = (sqlite3_int64*)pPrior; assert( pPrior!=0 && nByte>0 ); nByte = ROUND8(nByte); p--; (Line 13028) p = realloc(p, nByte+8 ); if( p ){ p[0] = nByte; p++; }else{ testcase( sqlite3GlobalConfig.xLog!=0 ); sqlite3_log(SQLITE_NOMEM, "failed memory resize %u to %u bytes", sqlite3MemSize(pPrior), nByte); } return (void*)p; } Thanks and best regards, Stephen Blessing Quality Assurance DataSplice, LLC Phone: 970-232-1647 stephen.bless...@datasplice.com For support issues please keep our team in the loop and CC: supp...@datasplice.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] copying data from memory to file
Hello: After generating an in memory db schema, I use the attach command to replicate that schema to a file - at least that is my intention. I get a no such table error, so something is wrong somewhere. Can someone get me on the right track? Cheers, Berryl = Code called after in memory db is created with test data: = public void ExportData(SQLiteConnection conn, string dataFile) { if (!File.Exists(dataFile)) { SQLiteConnection.CreateFile(dataFile); } _attachDatabase(conn, dataFile); foreach (var table in _getTableNames(conn)) { _copyTableData(conn, table, _getAttachedTableName(table)); } _detachDatabase(conn); } private static IEnumerable _getTableNames(SQLiteConnection conn) { var tables = SQLiteMetaDataCollectionNames.Tables; var dt = conn.GetSchema(tables); return dt.Rows.Cast().Select(R => (string) R["TABLE_NAME"]); } private static string _getAttachedTableName(string table) { return string.Format("{0}.{1}", ATTACHED_DB, table); } private static void _attachDatabase(SQLiteConnection conn, string dataFile) { var cmd = new SQLiteCommand(conn) { CommandText = string.Format("ATTACH '{0}' AS {1}", dataFile, ATTACHED_DB) }; _log.Debug(cmd.CommandText); cmd.ExecuteNonQuery(); } private static void _copyTableData(SQLiteConnection conn, string source, string destination) { var cmd = new SQLiteCommand(conn) { CommandText = string.Format("INSERT INTO {0} SELECT * FROM {1}", destination, source) }; _log.Debug(cmd.CommandText); cmd.ExecuteNonQuery(); } == error, stack trace and log output == TestFixture failed: System.Data.SQLite.SQLiteException : SQLite error no such table: asdfgaqwernb.ActorRole at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() at SQLiteTesting.Helpers.SQLiteLoader._copyTableData(SQLiteConnection conn, String source, String destination) in C:\Users\Lord & Master \Documents\Projects\Data\NHib projects\Cookbook\SQLiteTesting\Helpers \SQLiteLoader.cs:line 71 at SQLiteTesting.Helpers.SQLiteLoader.ExportData(SQLiteConnection conn, String dataFile) in C:\Users\Lord & Master\Documents\Projects \Data\NHib projects\Cookbook\SQLiteTesting\Helpers \SQLiteLoader.cs:line 33 at SQLiteTesting.Helpers.QueryTests.OnFixtureSetUp() in C:\Users \Lord & Master\Documents\Projects\Data\NHib projects\Cookbook \SQLiteTesting\Helpers\QueryTests.cs:line 32 at SQLiteTesting.Helpers.BaseFixture.FixtureSetUp() in C:\Users \Lord & Master\Documents\Projects\Data\NHib projects\Cookbook \SQLiteTesting\Helpers\BaseFixture.cs:line 23 2010-11-24 10:00:24,968 INFO processing cascade NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for: Eg.Core.Movie 2010-11-24 10:00:24,968 INFO cascade NHibernate.Engine.CascadingAction +SaveUpdateCascadingAction for collection: Eg.Core.Movie.Actors 2010-11-24 10:00:24,970 INFO done cascade NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for collection: Eg.Core.Movie.Actors 2010-11-24 10:00:24,970 INFO deleting orphans for collection: Eg.Core.Movie.Actors 2010-11-24 10:00:24,973 INFO done deleting orphans for collection: Eg.Core.Movie.Actors 2010-11-24 10:00:24,973 INFO done processing cascade NHibernate.Engine.CascadingAction+SaveUpdateCascadingAction for: Eg.Core.Movie 2010-11-24 10:01:25,195 DEBUG ATTACH '...\SPUD.db3' AS asdfgaqwernb 2010-11-24 10:03:22,362 DEBUG INSERT INTO asdfgaqwernb.ActorRole SELECT * FROM ActorRole ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing time to create indexes
Thats interesting Max, I'll look at that. There are two main string fields one has an average of about 15 characters per field with a max of about 100. The other has an average length of about 150 characters with a max of about 250 (although occasionally up to 500 or so) It may be possible to split the table into two with the main fields in one and all of the remainder (including the text fields) in the other. I'll have a play and see what I come up with On 25 November 2010 10:20, Max Vlasov wrote: > On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson < > sandersonforens...@gmail.com> wrote: > >> I have a table with over 1 million rows and 20+ columns all of which >> are indexed, I reasonably regularly recreate the table with new data >> and find that the indexing process takes about 30 minutes. Are there >> any settings/tweaks that I can use to reduce the time required to >> create the index? >> >> > Paul, thought about it recently. I think there's something you could do if > you're ready to change the scheme a little. > > The index creation involves reading and writing. The size of the index in > your case for most of the fields is smaller than the data of the main table > so we'll forget about writing, possible it works well using the sqlite and > system cache. > > But your reading for every index should read the whole table and the full > record (if it fits on a single page) even if the index needs only one field. > This process of full table reading can be comparatively fast (if your base > is not fragmented), slow (if it's heavily fragmented) and amazingly fast if > your entire table luckily fitted in the os system cache when your first > field was indexed. And the latter is possible if your text fields contribute > much to the record size and you're ready to move it to a different table > accessing later with a join. I don't know your exact numbers, but 1m records > with 20 modest integers (using much few bits than 64) should take about > 50MB-100MB and many modern software/hardware configurations will possibly > read it just once accessing this data from the cache when your second and > later indexes are created. > > Max Vlasov > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing time to create indexes
On Wed, Nov 24, 2010 at 1:13 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I have a table with over 1 million rows and 20+ columns all of which > are indexed, I reasonably regularly recreate the table with new data > and find that the indexing process takes about 30 minutes. Are there > any settings/tweaks that I can use to reduce the time required to > create the index? > > Paul, thought about it recently. I think there's something you could do if you're ready to change the scheme a little. The index creation involves reading and writing. The size of the index in your case for most of the fields is smaller than the data of the main table so we'll forget about writing, possible it works well using the sqlite and system cache. But your reading for every index should read the whole table and the full record (if it fits on a single page) even if the index needs only one field. This process of full table reading can be comparatively fast (if your base is not fragmented), slow (if it's heavily fragmented) and amazingly fast if your entire table luckily fitted in the os system cache when your first field was indexed. And the latter is possible if your text fields contribute much to the record size and you're ready to move it to a different table accessing later with a join. I don't know your exact numbers, but 1m records with 20 modest integers (using much few bits than 64) should take about 50MB-100MB and many modern software/hardware configurations will possibly read it just once accessing this data from the cache when your second and later indexes are created. Max Vlasov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] autoindex1 test fail on linux debian
That worked. Thanks for the answer. On 25/11/2010 11:00 AM, Dan Kennedy wrote: > On 11/25/2010 02:43 PM, Yoni wrote: >> Hi, >> >> I get the following fail when running sqlite3 tests, under linux debian, >> with TCL 8.4. >> Code is latest from fossil. >> Any help appreciated. > > Try with 8.5 or newer. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hi, > In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. I agree when we are talking about small systems, which does not read/write allot. But, on a larger scale system, who have a several threads reading and writing to the DB non-stop, there always will be at least one reader who block the checkpoint from being completed, thus making the WAL file grow forever (unless of course, you block during the checkpoint - but then you make your system unresponsive). So, if you have a large scale system, you have two choices: block during the checkpoint, or suffer from huge WAL file (and a crash if the file is too big). > You can never be sure when is the right time to wrap around. > If you wrap around too early, you run the risk of hitting the wall > put up by the earliest reader, who is still using the end of the > WAL file. If we choose a large enough size to be free at the beginning of the file (can be proportional to the WAL file size of just a constant), the chances that this will happen a very low. and even when this will happen, the write wont have to block the reader, it just have to force running checkpoint (while they are reading a page), and wait until they finish read the specific page (not the whole read transaction), since the next page they need to read is already in the main DB file. > The only solution around this would be to have more than 1 WAL file. > To avoid hitting this same problem with multiple WAL files, you'd > need to support an arbitrary N number of WAL files. I think my solution will work too. on PostgreSQL they already attend the problem of how many WAL files should exist. see here: http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html. > On one system of mine, where blocking is an issue, I > buffer up the write messages, and flush them on a background thread. > Of course this may not be practical for you... I think this kind of a solution can work of me too (I dont want to block the main thread EVER, and I have a steady stream of writes to the DB). Can you please elaborate how did you implemented your solution? Yoni. On 25/11/2010 8:42 AM, Ben Harper wrote: > A cyclical WAL file has this problem: > > You can never be sure when is the right time to wrap around. > If you wrap around too early, you run the risk of hitting the wall > put up by the earliest reader, who is still using the end of the > WAL file. In which case the writer would have to block on the reader, > which is something that the current system doesn't suffer from. > My guess is that a very high proportion of use cases would never > suffer this issue, provided they set an appropriate wrap around size > but this does place that burden on the implementer - of picking > the right heuristics. > > It is a pathological case, but imagine an implementer never tests > for this wraparound issue, and builds a deadlock into his app > that arises when a writer blocks on a reader. This is the kind of > unexpected behaviour that could really bite you. > > The only solution around this would be to have more than 1 WAL file. > To avoid hitting this same problem with multiple WAL files, you'd > need to support an arbitrary N number of WAL files. And that, > undoubtedly, is a more complex implementation than what currently > exists. I imagine it's quite a task for the SQLite developers to > get 100% branch test coverage. > > On one system of mine, where blocking is an issue, I > buffer up the write messages, and flush them on a background thread. > Of course this may not be practical for you... > > Ben > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Yoni > Sent: 24 November 2010 04:46 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] WAL file size > > Hi, > > I am currently developing a system, which have the following requirements: > 1. should be very fast and responsive > 2. run forever (or at least a very long time) without restart. > 3. have steady stream of writes to the DB. > > Currently I am using sqlite with one sql connection, and I run sql > checkpoint every once in a while (e.g. after 5 minutes, or after 1 > inserts to the DB) - I don't use auto checkpoint. > Since I run the checkpoint in the main thread, the system is > unresponsive for a long time (can take from 400ms to 2ms!) > > What I would like to do, is to run the checkpoint in another thread, so > the main thread can keep working (and using sql), and do the long > checkpoint work in the background. > > The problem is that when I try to do it, the WAL file grows without limit. > I wrote a little program that do exactly this (insert rows as fast as C > allows, and in the background run checkpoints). > The result (after 30 secs and 400K records) was: DB size 19MB and WAL > size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with > same sqlite
Re: [sqlite] autoindex1 test fail on linux debian
On 11/25/2010 02:43 PM, Yoni wrote: > Hi, > > I get the following fail when running sqlite3 tests, under linux debian, > with TCL 8.4. > Code is latest from fossil. > Any help appreciated. Try with 8.5 or newer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion failure in SQLite 3.7.3 (new vs. 3.6.23.1)
Hi all, I'm noticing a new failure with SQLite 3.7.3 as compared to the previous version I was using, 3.6.23.1. Error message: -- Assertion failed: (memIsValid(&u.bq.r.aMem[i])), function sqlite3VdbeExec, file .../sqlite3.c, line 64507. Information from the time of error: --- (gdb) print i 0 (gdb) print *u.bq.r.aMem { u = { i = 1, nZero = 1, pDef = 0x1, pRowSet = 0x1, pFrame = 0x1 }, r = 0, db = 0x83a218, z = 0x0, n = 0, flags = 4228, type = 0 '\0', enc = 0 '\0', pScopyFrom = 0x0, pFiller = 0x0, xDel = 0, zMalloc = 0x0 } (gdb) print pc 31 (gdb) print u.bq { pC = 0x2714e8, res = 2561376, r = { pKeyInfo = 0x1fbb58, nField = 2, flags = 12, rowid = 343597383684, aMem = 0xa052d8 } } Repeatability: -- Every time. u.bq.r.aMem->flags is always 4228 (==0x1084), u.bq.r.aMem->u.i is always 1, u.bq.r.aMem->db varies as expected and all other fields of u.bq.r.aMem are zeroes. Background: --- I am using SQLite in an iPhone application I'm developing. I am using the amalgamation from sqlite.org so that I know what I have; I am not using the version of SQLite supplied by Apple in their SDK. The previous version I had was 3.6.23.1; I updated to 3.7.3 because I wanted the very useful sqlite3_register_function_v2() API which is new in that release. The same assertion fires when I run my app in the iPhone simulator or execute the same query on the same database from the sqlite3 shell program. I am compiling sqlite3.c with GCC 4.2. Obviously when I execute the same query on a version of sqlite built without the -DSQLITE_DEBUG it cannot fail the assertion since the assertion is not built. I can confirm however that it executes without any detected memory access violation and the result set is what I would expect. It is important for me to understand if this is an assertion mis-fire in the debug case (and not a real problem) or a lucky escape in the release case (and therefore a real problem, unless I can guarantee my customers are all similarly lucky). The failing query is explained as follows: addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 OpenEphemeral 9 3 0 keyinfo(1,-BINARY) 00 2 Integer1 1 000 3 Variable 1 2 0 :row 00 4 MustBeInt 2 0 000 5 IfPos 2 7 000 6 Integer0 2 000 7 Add1 2 300 8 IfPos 1 10000 9 Integer-13 000 10Goto 0 118 000 11OpenRead 0 310 0 00 12OpenRead 1 210 3 00 13OpenRead 2 270 3 00 14OpenRead 10300 keyinfo(2,BINARY,BINARY) 00 15OpenRead 3 130 4 00 16OpenRead 4 8 0 2 00 17OpenRead 5 240 2 00 18OpenRead 6 2 0 3 00 19OpenRead 7 250 2 00 20OpenRead 8 2 0 3 00 21Rewind 0 95000 22Rowid 0 4 000 23MustBeInt 4 94000 24NotExists 1 94400 25SCopy 4 5 000 26IsNull 5 94000 27Column 1 2 600 28IsNull 6 94000 29Affinity 6 1 0 d 00 30SeekGe 10945 2 00 31IdxGE 10945 2 01 32IdxRowid 107 000 33Seek 2 7 000 34Column 2 2 800 35MustBeInt 8 93000 36NotExists 3 938
Re: [sqlite] WAL, durability, and synchronous=NORMAL
On 11/25/2010 03:24 PM, Twylite wrote: > Hi, > > I am seeking technical information on the durability of transactions > with journal_mode=WAL and synchronous=NORMAL. > > Specifically, in the event of a power failure, can the following ever > happen: > (1) Loss of the last transaction completed. > (2) Loss of some indeterminate number of recent transactions. > (3) Loss of the entire WAL file. > (4) Corrupt of the database making it unopenable. Possible. Possible. Possible. Not supposed to be possible. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL, durability, and synchronous=NORMAL
Hi, I am seeking technical information on the durability of transactions with journal_mode=WAL and synchronous=NORMAL. Specifically, in the event of a power failure, can the following ever happen: (1) Loss of the last transaction completed. (2) Loss of some indeterminate number of recent transactions. (3) Loss of the entire WAL file. (4) Corrupt of the database making it unopenable. My use case is an embedded x86 running off a CFIDE (NAND flash). There is no shut down (just hard power-off), and a corrupt database will be a tragedy (best case: we wipe and reinit the database losing all data; worst case: device cannot boot and is rendered inoperable), but I can tolerate losing some recent transactions. Performance tests with synchronous=FULL yield 41 TPS which is slightly too slow for our purposes. I have scoured the SQLite docs (include http://www.sqlite.org/wal.html), the Wiki and the mailing list archives [1], and I can't find a definitive answer. It seems clear that (1) and (2) can happen, and likely that (3) can happen, but no clarity on (4). The following quotes would seem to indicate that the database should never become corrupted in WAL mode? Can someone confirm this? * DRH: "If you set synchronous=NORMAL, then the checkpoint thread/process is the only thread/process that ever calls fsync() so you can do queries and updates in a GUI thread with much less risk of freezing the system due to a slow disk. You pay for this by giving up durability following a power-loss/hard-reset. Please note that SQLite databases with WAL will continue to feature fully automatic recovery from power-loss or hard-reset; applications do not have to do anything special to recover from a crash." * http://www.sqlite.org/wal.html: "Further, syncing the content to the disk is not required, as long as the application is willing to sacrifice durability following a power loss or hard reboot. (Writers sync the WAL on every transaction commit if PRAGMA synchronous is set to FULL but omit this sync if PRAGMA synchronous is set to NORMAL.)" [1] Related mailing list threads * sqlite - WAL in SQLite, May 2010, http://www.pubbs.net/201005/sqlite/55971-sqlite-wal-in-sqlite.html * [sqlite] WAL - Performance/fsync, Jul 2010 http://www.mail-archive.com/sqlite-users@sqlite.org/msg53555.html * [sqlite] performance, transactions and wal checkpoints, Aug 2010, http://www.mail-archive.com/sqlite-users@sqlite.org/msg54512.html * [sqlite] Sqlite on NAND flash devices..., Aug 2010, http://www.mail-archive.com/sqlite-users@sqlite.org/msg54896.html PART 2 (A) My understanding is that the WAL is a ring-buffer that is a delta on the main database file. In the context of NAND flash and wear leveling, are there any portions of the database file or WAL that get written more regularly and may impact on the longevity of the flash device? (e.g. with journal_mode=DELETE there are parts of the database file header that seem to be modified on each transaction). (B) Will regular checkpointing cause uneven wear of the flash device? Specifically, does a checkpoint affect the "tail" of the WAL (causing it e.g. to reset to the start of the WAL file)? Conceptually, if I checkpointed after every transaction, would the disk write behaviour be similar to journal_mode=PERSIST? (C) If I am correct in assuming that WAL + synchronous=NORMAL can lose the entire WAL file but not corrupt the database, AND I don't want to checkpoint too regularly to prevent uneven wear of the flash device, what would be the best way to force an occasional fsync() of the WAL so that I limit losses to the last few transactions? (Implied sub-question: will an fsync() make the transactions durable, or can the entire WAL still be lost/corrupt even if an fsync() occurs?). Could I turn on synchronous=FULL to cause an immediate fsync()? Turn on synchronous=FULL for one transaction? Thanks in advance, Twylite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users