从Python模块设置sqlite I/O优先级（加速sqlite提交）
我在一个嵌入式系统中有一个Python程序，需要往一个sqlite数据库里写数据。这个数据库非常重要，所以必须保证每次写入都是完全同步的。问题是，往数据库里插入一条数据的时间非常长（需要3到30秒）。虽然我把插入操作放在一个事务里，但实际上没办法把这些插入分成多个事务来处理。
我一直在寻找缩短数据库提交时间的方法，但有点无从下手。
我尝试过设置 pragma journal_mode=persistance，这似乎有一点帮助，但效果不大。现在我在想，可能是sqlite在输入输出（I/O）上得不到足够的时间。
有没有办法单独提高sqlite3的处理优先级？我不想提高Python程序本身的优先级，因为我们还在做日志记录、配置更新和其他文件的输入输出，但我希望能让sqlite尽可能多地占用I/O时间。
我也欢迎其他建议来加快提交时间。
这是我在插入操作中做的：
cur = None
try:
logging.info('Inserting into : ' + table + ':' + str(m))
sql = "INSERT INTO " + table + "("
bind = " VALUES("
list = [];
for k, v in m.items():
if(v != None):
sql += k + ","
bind += "?,"
list.append(v)
sql = str(sql).rstrip(',') + ")"
bind = str(bind).rstrip(',') + ")"
cur = conn.cursor()
cur.execute("PRAGMA journal_mode=PERSIST")
logging.info(sql + bind)
cur.execute(sql + bind, list) # It's definitely this that takes the most time. Yes I've profiled.
conn.commit()
id = cur.lastrowid
return id
except Exception as e:
raise e
finally:
if cur != None:
cur.close()
4 个回答
根据你的数据库是如何建立索引的，如果你使用更大的缓存，可能会获得更好的性能。你可以通过输入以下命令来改变这个设置：
cursor.execute("PRAGMA cache_size=200000")
我认为在大多数情况下，这样做会让你的缓存大小变成200 MB（不过这还要看你的页面大小），所以如果你的内存更多或更少，你可能需要进行调整。
你没有说明你的嵌入式平台是什么。如果是Linux的话，那这就是发生这种情况的原因。
为了完成一个提交，SQLite必须等到相关的数据确实已经写入到磁盘上。通常，它需要为一个事务做三次这样的操作——分别是数据库本身、日志文件和包含这两个文件的目录。即使是WAL模式，也需要进行一次同步。
系统调用fsync就是用来完成这个操作的，它会一直等待，直到相关文件或目录的数据被写入磁盘。不过，常见的Linux ext3/4文件系统会把这个操作变成一个同步调用。同步会一直阻塞，直到整个文件系统中所有未写入的数据都被写入磁盘。（其他嵌入式操作系统可能也有类似的实现。）
你可以使用strace或类似的工具来跟踪系统调用及其时间，这样可以帮助你找出或排除这个问题的原因。
如果真的是这样（很有可能），那么你有两个解决方案。一个是不断在后台调用同步，或者配置内核的同步行为（比如bdflush/kflushd等），设置较短的时间间隔，这样未提交的写入数据就会很少。Linux的默认设置大约是30秒，除非你在笔记本模式下，那样可能会延长到几分钟。
另一个方法是把数据库放在自己的文件系统上，这样其他文件系统上的未提交写入就不会影响到你的数据库文件系统。
在Linux下，可以使用ionice这个系统调用或工具来改变输入输出的优先级。（需要管理员权限才能提升你的优先级。）不过，如果上面提到的同步行为是问题的根源，那这样做也不会有太大帮助，因为未提交的数据量还是一样，改变写入的顺序也无济于事。
如果你的底层文件系统使用的是某种劣质的闪存，那么你还可以调整SQLite的页面大小（默认是1KB），使其与文件系统的页面大小匹配。这可能会有一点帮助。
你试过3.7版本的WAL吗？
以前，SQLite在处理安全写入时非常慢（比如不把设置改成synchronous=off）。它会把整个事务写入一个日志文件，然后再把这个日志文件的内容写回到原来的文件中，这个过程需要多次同步，导致整个操作变得很慢。
SQLite 3.7版本的写前日志（WAL）大大改善了这个问题；它避免了重复写入，这在处理大事务时非常耗费时间，并且显著减少了需要的文件系统同步次数。
了解更多信息请查看：http://www.sqlite.org/wal.html