how we can ensure caching to reduce file-system write cycle for SQLite databases

Question

Hello,

I would like to know how we can implement cashing in sqlite database.My primary objective is write data to ram and after when cache is filled i want to flush all the data to disk database.Whether it is possible? if yes Can i get any sample codes?

Thanks
Aneesh

Answer 1

SQLite already does its own cacheing, which is likely to be more efficient than anything you can implement - you can read about the interface to it here. You may be interested in other optimisations - there is a FAQ here.

Answer 2

You might want to checkout the SQLite fine-tuning commands (pragmas)

Answer 3

Since sqlite is transactional, it relies on fsync to ensure a particular set of statements have completed when a transaction is committed. The speed and implementation of fsync varies from platform to platform.

So, by batching several statements within a transaction, you can get a significant increase in speed since several blocks of data will be written before fsync is called.

An older sqlite article here illustrates this difference between doing several INSERTs inside and outside transactions.

However, if you are writing an application needing concurrent access to data, note that when sqlite starts a write transaction, all reads (select statements) will be blocked. You may want to explore using your in memory cache to retrieve data while a write transaction is taking place.

With that said, it's also possible that sqlite's caching scheme will handle that for you.

Answer 4

Why do you want to do this? Are you running into performance issues? Or do you want to prevent other connections from seeing data until you commit it to disk?

Regarding syncing to disk, there is a tradeoff between database integrity and speed. Which you want depends on your situation.

1. Use transactions. Advantages: High reliability and simple. Disadvantages: once you start a transaction, no one else can write to the database until you COMMIT or ROLLBACK. This is usually the best solution. If you have a lot of work to do at once, begin a transaction, write everything you need, then COMMIT. All your changes will be cached in RAM until you COMMIT, at which time the database will explicitly sync to disk.

2. Use PRAGMA journal_mode=MEMORY and/or PRAGMA synchronous=OFF. Advantages: High speed and simple. Disadvantages: The database is no longer safe against power loss and program crashes. You can lose your entire database with these options. However, they avoid explicitly syncing to disk as often.

3. Write your changes to an in-memory database and manually sync when you want. Advantages: High speed and reliable. Disadvantages: Complicated, and another program can write to the database without you knowing about it. By writing to an in-memory database, you never need to sync to disk until you want to. Other programs can write to the database file, and if you're not careful you can overwrite those changes. This option is probably too complicated to be worth it.

Answer 5

Hello, I would like to use this database in my arm9 Embedded linux platform.Here i am using a flash memory for data storing.Flash memory have an issue of write cycles.So i want to wrte to RAM and after filling ram I want to flush it to disc database.how i can implement this?Any sample codes or any document i can get?