Hello, I am using sqlite database in my arm9 embedded linux platform. I want to reduce writes to disk database because my disk is a flash memory and it needs minimum write cycles. So I tried to increment SQLITE_DEFAULT_CACHE_SIZE as 5000. My objective was to write data to cache and when the cache is filled, automatically flush to disk. But by incrementing SQLITE_DEFAULT_CACHE_SIZE, I can't confirm whether this is working or not. I am not seeing any changes in the operations! Is my way correct? Can anybody give me some suggestions? Thanks Aneesh
A:
The latest SQLite has a feature for backing up hot databases it's still experimental, but my recommendation would be to use an on Memory Database and merge it with the Disk database when you think is appropriate.
Robert Gould
2009-04-21 07:27:04
Yes Robert you are Right,But the Problem is i have to flush the Database periodically,that seems to be too risky.My intention is whether Sqlite is supporting any kind of default flushing based on Cache full?i have done some experiments based on the above scenario by setting PRAGMA journal_mode=MEMORY ,PRAGMA SQLITE_DEFAULT_CACHE_SIZE=5000 and PRAGMA synchronous=FULL,But the flushing rate seems to be similar on cache size of 2000(default) and 5000.this makes me wonder.please help ?
2009-04-21 07:42:03
Unfortunately those options won't affect the rate to which it'll hit the drive
Robert Gould
2009-04-21 07:58:49
First of all thanks for your valuble suggestions,you are telling that,cache full can not be used as a criteria for flushing the data from Ram to disk file? if it have, then my problem may solve with out any risk factor.do you have any other option to suggest?Advanced thanks,Aneesh
2009-04-21 08:08:39
Although it's not specific to SQLite, I'll assume you have enough RAM, because you're willing to cache, so you can have your own cache, store transactions and once you reach say 100 transactions, or 1 minute goes by, you flush all transactions. But you'll need to store the commands in an array (using RAM)
Robert Gould
2009-04-21 08:13:55
A:
You have the source code to SQLite - why not simply instrument it to record the information you are interested in.
anon
2009-04-21 07:31:03
At some point in the SQLite code, the decision is made to flush the cache - add a counter and a of some kind there to record the write. You can then examine the counter to extract stats of how frequently flushing is happening.
anon
2009-04-21 07:57:33
Hi,But the Problem is i have to flush the Database periodically,that seems to be too risky.My intention is whether Sqlite is supporting any kind of default flushing based on Cache full? i have done some experiments based on the above scenario by setting PRAGMA journal_mode=MEMORY ,PRAGMA SQLITE_DEFAULT_CACHE_SIZE=5000 and PRAGMA synchronous=FULL, But the flushing rate seems to be similar on cache size of 2000(default) and 5000.this makes me wonder.please help ?
2009-04-21 08:10:17
@Neil: The question isn't about recording/metrics, its about reducing the frequency with which SQLite hits the drive. But ok, with the explanation your answer is at least understandable. Before it seemed like you came out of the blue.
Robert Gould
2009-04-21 08:11:01
@aneesh - are you trying to confirm that changing the cache size alters the rate of flushing, which is how I read your question. If not, please edit your question to make it clearer.
anon
2009-04-21 08:27:54
Exactly.I am doing the same.But i change the cache size also the same behavior is happening
2009-04-21 08:33:53
@ahneesh I just took a cursory look at the code and it seems to me that SQLite is using a form of write-through cacheing, in which case the cache size will make little difference to write frequency. I could easily be wrong on this though.
anon
2009-04-21 08:46:52
A:
Ok Neil .If the "SQLite is using a form of write-through caching" then on the cache overflow, it will try to flush the Data to some temporary file or disk file .This is the Same point i am trying to experiment by by enalrging the cache size and thus acquire control over flushing rate .but it is not happening.please reply.
Firstly, you should probably modify your original question to ask this. Secondly, write-through cacheing means that all writes are immediately commited to disk, but the data that was written is also held in the cache. Thus cache size has little or no effect on write-to-disk frequency. Like I said, I can't be 100% sure that this is happening - take a look at the code yourself.
anon
2009-04-21 09:40:14
+1
A:
SQLite to be ACID db flushes with every commit OR with every insert/delete/update not wrapped with transaction. Use transactions for grouping operations OR turn OFF ACIDity and set PRAGMA synchronous=OFF.
"PRAGMA synchronous = OFF" and SQLite won't flush data at all (effectively leaving that to OS Cache)
SQLITE_DEFAULT_CACHE_SIZE is ONLY for size of cache. And cache is used ONLY for reading data.
There is another option - you can implement own VFS layer and prevent page saving at all before your own buffer will be full. http://www.sqlite.org/c3ref/vfs.html
But I'm sure that sync=off (or much better to use transactions) will do the job good enough (while having a good chance to corrupt your db in case power failures or hard reset for sync=off).
Another hint is to place JOURNAL in memory or turn it off completely. Again - it's turning off acidity, but that also removes some disk touches.
Mash
2009-04-26 12:03:22
I agree; transaction structure is probably where the greatest benefit could be found.
Kim Reece
2009-04-27 06:26:41