4

I'm developing an embedded system, which needs to store and retrieve data very frequently. I am expecting perhaps 100s of writes per second and 10s of reads. The data will arrive in bursts rather than continuous.

I would like to use SQLite to store all this data, but since it is a flash file-system, the writes (INSERTS & UPDATES) are much too slow. I tried setting synchronous=off and this does certainly improve the performance, but I am nervous about corrupting the database.

In my application, power failures would be a rare, but real possibility. I can live with losing some recent data, but absolutely cannot risk losing all the data due to a database corruption.

I also thought about using an in-memory SQLite database as the primary database and periodically sync it to the file-system as discussed in Synchronizing sqlite database from memory to file

Are there any other options I should consider?

Community
  • 1
  • 1
mccleanp
  • 111
  • 1
  • 4
  • >> `I ... absolutely cannot risk losing all the data due to a database corruption...I can live with losing some recent data... ` << This would suggest a solution where you periodically sync with an external database, or export, as often as necessary depending on which safety features in SQLite you have disabled to get greater insert speed, thereby increasing your risk of data loss without recovery. – Tim Sep 29 '11 at 13:25

2 Answers2

5

When a burst of data arrives, be sure to do the series of inserts and updates within a single transaction.

sqlite3_exec(handle, "BEGIN TRANSACTION", NULL, NULL, NULL);

for ( ... ) {
    // Do your inserts/updates
}

sqlite3_exec(handle, "END TRANSACTION", NULL, NULL, NULL);

By default, SQLite puts every insert/update in its own transaction. This can make SQLite quite slow if the underlying filesystem is slow. By declaring your own transactions, you can significantly reduce the amount of actual writing that is done to the filesystem, thereby substantially increasing SQLite's performance.

Dan Moulding
  • 211,373
  • 23
  • 97
  • 98
  • I was aware the performance penalty of single commands outside transactions and I have used this in my tests. In the application however it is not entirely clear where the transaction boundary should occur as the data will be received as a stream. Probably end up delimiting by number of inserts and/or timeouts on the steam. – mccleanp Sep 30 '11 at 11:51
1

I don't know how large are your data sets, but if they are not too big, then the WAL mode might help. You may experiment with "PRAGMA synchronous=1": This setup does not sync after each transaction, but once in a while. (Default: When 2 MB of new data is written.) SQLite docs say that you might loose a few recent transactions, but the DB won't be corrupted.

chue x
  • 18,573
  • 7
  • 56
  • 70
Jan Slodicka
  • 1,505
  • 1
  • 11
  • 14