一段时间后遇到Sqlite3磁盘I / O错误，但在使用db副本后有效 [英] Sqlite3 Disk I/O error encountered after a while, but worked after using copy of db
问题描述
我正在使用sqlite3数据库每秒记录一次数据。它的接口由Flask-SQLAlchemy提供。
I'm using an sqlite3 database to record data every second. The interface to it is provided by Flask-SQLAlchemy.
这可以正常工作几个月，但是最终（由于.db文件接近8 GB），错误导致无法将更多数据写入数据库：
提交失败：（sqlite3.OperationalError）磁盘I / O错误
This can work fine for a couple of months, but eventually (as the .db file approaches 8 GB), an error prevents any more data from being written to the database:
Failed to commit: (sqlite3.OperationalError) disk I/O error
日志文件似乎不是这里的问题-如果我重新启动应用程序并使用编译指示 journal_mode = TRUNCATE ，则会创建日志文件，但磁盘I / O错误仍然存在。
The journal file does not seem to be the issue here - if I restart the application and use the pragma journal_mode=TRUNCATE, the journal file is created but the disk I/O error persists.
.dbinfo（从sqlite3.exe获取）：
Here's the .dbinfo (obtained from sqlite3.exe):
database page size: 1024
write format: 1
read format: 1
reserved bytes: 0
file change counter: 5200490
database page count: 7927331
freelist page count: 0
schema cookie: 12
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3008011
number of tables: 6
number of indexes: 7
number of triggers: 0
number of views: 0
schema size: 5630
data version 2
但是这可行：
- 我制作了.db文件的副本（调用app.db和copy.db）。
- I将app.db重命名为orig.db
- 我将copy.db重命名为app.db（如此有效，我将其交换以使副本成为应用程序）。
当我再次启动应用程序时，它能够再次写入app.db文件！这样我就可以写入数据库的副本了。
When I started my application again, it was able to write to the app.db file once more! So I could write to a copy I made of the database.
驱动器是SSD（三星850 EVO mSATA）>我想知道这是否与它有关？有谁对如何防止它再次发生有任何想法？
The drive is an SSD (Samsung 850 EVO mSATA)> I wonder if that's got something to do with it? Does anyone have any ideas on how I can prevent it from happening again?
编辑：我已经使用sqlite3.exe CLI手动执行了INSERT INTO命令，并且这实际上已成功完成（并已写入磁盘）。但是，当我重新运行Flask-SQLAlchemy接口进行写入时，它仍然出现磁盘I / O错误。
I've used the sqlite3.exe CLI to execute an INSERT INTO command manually, and this actually completed successfully (and wrote to the disk). However, when I re-ran my Flask-SQLAlchemy interface to write to it, it still came up with the disk I/O error.
推荐答案
更新：
一位同事指出，这可能与另一个问题有关：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.
因此，为了减少碎片数量，我正在将数据库-使用上述问题中提供的答案分配一些磁盘空间： https://stackoverflow.com/a/49506243/3274353
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
类似这样的东西：
CREATE TABLE t(x);
INSERT INTO t VALUES(zeroblob(500*1024*1024)); -- 500 MB
DROP TABLE t;
要知道是否需要执行此操作，请使用 freelist_count pragma ：
To know whether this needs to be done, I use a call to the freelist_count pragma:
PRAGMA schema.freelist_count;
返回数据库文件中未使用的页数。
PRAGMA schema.freelist_count;
Return the number of unused pages in the database file.
这篇关于一段时间后遇到Sqlite3磁盘I / O错误，但在使用db副本后有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
