Sqlite3 Disk I/o Error Encountered After A While, But Worked After Using Copy Of Db

I'm using an sqlite3 database to record data every second. The interface to it is provided by Flask-SQLAlchemy. This can work fine for a couple of months, but eventually (as the .

Solution 1:

UPDATE:

A colleague pointed out that this might be related to another question: https://stackoverflow.com/a/49506243/3274353

I strongly suspect now that this is a filesystem issue - in my system, the database file is being updated constantly alongside some other files which are also being written to.

So to reduce the amount of fragmentation, I'm getting the database to pre-allocate some disk space now using the answer provided in aforementioned question: https://stackoverflow.com/a/49506243/3274353

Something like this:

CREATETABLE t(x);
INSERTINTO t VALUES(zeroblob(500*1024*1024));  -- 500 MBDROPTABLE t;

To know whether this needs to be done, I use a call to the freelist_countpragma:

PRAGMA schema.freelist_count; Return the number of unused pages in the database file.