In short, this post would like to answer the following question: how (if possible) can we configure the SQLite database to be absolutely sure that any INSERT command will return in less than 8 milliseconds?
By configuration, I mean: compilation options, database pragma parameters, and runtime parameters.
To give some background, we would like to apply the same INSERT statement at 120 frames per second. (1000 ms / 120 frames per second ≤ 8 ms)
The database is created with the following lines:
"CREATE TABLE IF NOT EXISTS MYTABLE ("
"int1 INTEGER PRIMARY KEY AUTOINCREMENT, "
"int2 INTEGER, "
"int3 INTEGER, "
"int4 INTEGER, "
"fileName TEXT);
and options:
"PRAGMA SYNCHRONOUS=NORMAL;"
"PRAGMA JOURNAL_MODE=WAL;"
The INSERT statement is as follows:
INSERT INTO MYTABLE VALUES (NULL, ?, ?, ?, ?)
Last? (for fileName) is the name of the file, so this is a small line. So every INSERT is small.
Of course, I use precompiled instructions to speed up the process.
, 8 , . , , THEN 8 , THEN .. 7200 , 1 .
, :
, , , . , 0, , 100 .
. 5 , , 7200 7161 5 ( 0, ).
1m2.286s 0m1.228s sys 0m0.320s.
, 1 4 . , 7200 8 , . , 7200 4 --- > 1800 , , 0,55 .
, , , 8 , , .
, ?
WAL ( 1MB), SQLite ( WAL ). PRAGMA SYNCHRONOUS = NORMAL, SQLite fsync .
, - fsync, .
WAL. WAL_AUTOCHECKPOINT ( 1000), WAL, .
PRAGMA SYNCHRONOUS = OFF. , .
dirty_background_ratio (/proc/sys/vm/dirty_background_ratio) 0, , .
- "" , , 8 ?