[sqlite] Journalling Settings
Hello everyone. I can see that the journal settings for the SQLite DB are buried deep within the code, so I figured I would ask before I go meddling. Is there a simple / easy / proper way to disable journalling for a given database? Can this be done on the fly ( on / off ), or is it possible at all? I would like to run journalling most of the time, but for some very intensive operations, I would like to disable it. Any information would be appreciated. Ehren K
Re: [sqlite] Journalling
That's not exactly correct. On modern systems, unless you exhaust memory you'll never hit the journal (in which case you'd need a journal- but read on). What you really want is to run the database without syncing. You _can_ comment this code out in os.c- but I wouldn't recommend it. The time is so minimal that you're not likely to gain much. You can find out exactly how much you'll gain by testing against a ramdisk- AFAIK, modern systems don't do anything special when fsync() occurs on a file in a ramdisk, although I suppose that strictly isn't very portable... On Mon, 2003-10-27 at 02:16, v t wrote: > I am trying to use sqlite in a context where I will be using it to > store some configuration about a system. I want to try to minimize the > disk access. Since journalling uses a file on the disk, I wanted to > turn it off. I am not worried about rolling back the database to a > known state in case of some failure. > > vt > > "Mrs. Brisby" <[EMAIL PROTECTED]> wrote: > On Thu, 2003-10-23 at 19:46, v t wrote: > > Hi, > > > > How do I turn journalling OFF? > > Why do you want to? What exactly are you trying to accomplish? > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > __ > Do you Yahoo!? > Exclusive Video Premiere - Britney Spears - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Journalling
Hello, I am aware that: 1) Reading from the database doesnt involve opening a journal file. 2) Writing (INSERT, UPDATE etc) to the database opens a journal file on the disk. 3) PRAGMA default_synchronous = OFF; (0) is to turn on sync ON or OFF. I am writing to a database file every 200ms on a compact flash. My understanding of the PRAGMA default_synchronous = OFF; (0) was that it is used for flushing the database contents on each write or doing it at the end of a database transaction. (simlilar to doing a buffered I/O eg fwrite, fflush or doing a unbuffered I/O like write). And my understanding of journalling is that it is used to rollback any changes to the database in case of failures during a transaction. I am no expert on journalling so I could be wrong. So I was just tailoring SQLite to my needs and not trying to throw away the use of a very useful feature. ( I may decide to use it when I need it again). vt [EMAIL PROTECTED] wrote: Hello, v t 27/10/2003 05:16 PM To: "Mrs. Brisby" cc: sqlite Subject: Re: [sqlite] Journalling > I am trying to use sqlite in a context where I will be using it to store some configuration about a system. I want to try to minimize the disk access. Since journalling uses a file on the disk, I wanted to turn it off. I am not worried about rolling back the database to a known state in case of some failure. You're not worried about your database becoming corrupted and all your data being destroyed? It doesn't sound like you like your data very much... ;) This is a question that pops up on the list every so often, and there have been some good reasons for it. Well. One comes to mind, and that's the use of flash memory in embedded devices. When you don't want to write to your media too many times you might find that it's better to turn off journalling and risk the consequences... perhaps make regular backups... rather than write to the media too often. The problem is that most people don't know what they're talking about when they ask how to turn journalling off. They don't understand when the journal gets written in the first place and they don't understand which operations they're performing that aren't affected by journalling. They haven't read the list archives, and they patently haven't read the manual, because it's listed under the pragma section of http://www.sqlite.org/lang.html. This is why when you ask the question on this list you get the response "Well I know you've asked how to turn off journalling, but what do you actually want to achieve by this and what alternatives have you considered?" You haven't yet given an explination that makes sense to me, so in the spirit of RTFM I'll leave you to find the exact manual reference yourself. I think it's worth you understanding, though, that journalling doesn't occur when you're only querying the database. It only happens when you modify the database. Using transactions while modifying the database is not only a good idea for data integrity, it also makes the overhead associated with synching the file to disk almost disappear so there's usually no need at all to turn off journalling. Given all of this, if you still can't find the exact spot in the manuals to turn this off yourself perhaps you could offer a more complete discussion about the nature of your database and your access to it. You'd be well advised to discuss the alternatives you have considered so that the gentle list members will feel more compelled to answer your question directly. Benjamin --Premature optimisation is the root of all evil - Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: [sqlite] Journalling
Hello, v t <[EMAIL PROTECTED]> 27/10/2003 05:16 PM To: "Mrs. Brisby" <[EMAIL PROTECTED]> cc: sqlite <[EMAIL PROTECTED]> Subject:Re: [sqlite] Journalling > I am trying to use sqlite in a context where I will be using it to store some configuration about a system. I want to try to minimize the disk access. Since journalling uses a file on the disk, I wanted to turn it off. I am not worried about rolling back the database to a known state in case of some failure. You're not worried about your database becoming corrupted and all your data being destroyed? It doesn't sound like you like your data very much... ;) This is a question that pops up on the list every so often, and there have been some good reasons for it. Well. One comes to mind, and that's the use of flash memory in embedded devices. When you don't want to write to your media too many times you might find that it's better to turn off journalling and risk the consequences... perhaps make regular backups... rather than write to the media too often. The problem is that most people don't know what they're talking about when they ask how to turn journalling off. They don't understand when the journal gets written in the first place and they don't understand which operations they're performing that aren't affected by journalling. They haven't read the list archives, and they patently haven't read the manual, because it's listed under the pragma section of http://www.sqlite.org/lang.html. This is why when you ask the question on this list you get the response "Well I know you've asked how to turn off journalling, but what do you actually want to achieve by this and what alternatives have you considered?" You haven't yet given an explination that makes sense to me, so in the spirit of RTFM I'll leave you to find the exact manual reference yourself. I think it's worth you understanding, though, that journalling doesn't occur when you're only querying the database. It only happens when you modify the database. Using transactions while modifying the database is not only a good idea for data integrity, it also makes the overhead associated with synching the file to disk almost disappear so there's usually no need at all to turn off journalling. Given all of this, if you still can't find the exact spot in the manuals to turn this off yourself perhaps you could offer a more complete discussion about the nature of your database and your access to it. You'd be well advised to discuss the alternatives you have considered so that the gentle list members will feel more compelled to answer your question directly. Benjamin --Premature optimisation is the root of all evil
Re: [sqlite] Journalling
I am trying to use sqlite in a context where I will be using it to store some configuration about a system. I want to try to minimize the disk access. Since journalling uses a file on the disk, I wanted to turn it off. I am not worried about rolling back the database to a known state in case of some failure. vt "Mrs. Brisby" <[EMAIL PROTECTED]> wrote: On Thu, 2003-10-23 at 19:46, v t wrote: > Hi, > > How do I turn journalling OFF? Why do you want to? What exactly are you trying to accomplish? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: [sqlite] Journalling
On Thu, 2003-10-23 at 19:46, v t wrote: > Hi, > > How do I turn journalling OFF? Why do you want to? What exactly are you trying to accomplish? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Journalling
Hi, How do I turn journalling OFF? Inside sqlite_open function, I passed TRUE to third argument of sqliteBtreeFactory which stands for omitJournal. That did not work. I got an assertion in sqlitepager_commit. Following is the surrounding code: TRACE1("COMMIT\n"); if( pPager->dirtyFile==0 ){ /* Exit early (without doing the time-consuming sqliteOsSync() calls) ** if there have been no changes to the database file. */ assert( pPager->needSync==0 ); rc = pager_unwritelock(pPager); pPager->dbSize = -1; return rc; } assert( pPager->journalOpen ); Thanks vt - Do you Yahoo!? The New Yahoo! Shopping - with improved product search