SQLite WAL性能改进 [英] SQLite WAL performance improvement

问题描述

我有一个在嵌入式Linux上运行的应用程序.我有一个带有一些表的预建数据库，每个表都有很多行(千)和52列.我提前构建了数据库，因为我担心如果在运行时执行"INSERT"操作，则会使磁盘碎片化，因此我首先在运行时构建了一个带有大量垃圾"INSERT"的数据库我使用'UPDATE's.

I have an application running on embedded linux. I have a pre-built DB with some tables where each has a lot of rows (thousands) and 52 columns. I built the DB ahead, because i am concerned that if I'll do 'INSERT' at the run-time I will make disk fragmentation, so instead i build a DB first with a lot of garbage 'INSERT's and in the run-time i use 'UPDATE's .

我每3秒将大量数据写入数据库，并且为了加快写入过程，我在SQLite中使用WAL模式.虽然，我有一个性能问题.似乎每当出现检查点时，它就会花费很长时间，并且处理器在不到3秒的时间内无法完成检查.为了改善这一点，我创建了一个线程，该线程在经过10次写调用之后，从主线程接收消息队列，然后接收检查点.

I am writing a lot of data to the DB every 3 seconds, And for the write procedure to be fast, I use the WAL mode in SQLite. Though, I have a problem of performance. It seems that whenever a checkpoint occurs, It takes too long and the processor can't do it in less than 3 seconds. In order to improve this, I created a thread that after like 10 writing calls, it receives a message-queue from the main thread and than checkpointing.

所以现在看来​​情况好转了，但是WAL文件越来越大了...我该如何解决?

So now, it seems like the situation is better but the WAL file is getting bigger and bigger and bigger... How can I work around here?

推荐答案

为避免碎片并避免需要预先插入数据，您应将 sqlite3_file_control()与当前在Firefox/Thunderbird中与

To avoid fragmentation and remove a need to pre-insert data, you should use sqlite3_file_control() with SQLITE_FCNTL_CHUNK_SIZE to set chunk size. Allocating database file space in large chunks (say 1MB at a time), should reduce file-system fragmentation and improve performance. Mozilla project is currently using this setting in Firefox/Thunderbird with great success.

关于WAL.如果您经常写入大量数据，则应考虑将写入内容包装到更大的事务中.通常，每个INSERT都是自动提交的，SQLite必须等待直到数据真正刷新到磁盘或闪存上-这显然非常慢.如果将多个写入包装到一个事务中，则SQLite不必担心每一行，并且可以一次刷新许多行，最有可能是一次刷新写入-这要快得多.因此，如果可以的话，请尝试将至少几百个写操作包装到一个事务中.

Regarding WAL. If you are writing a lot of data so often, you should consider wrapping your writes into bigger transactions. Normally, every INSERT is auto-committed and SQLite has to wait until data is really flushed to disk or flash - which is obviously very slow. If you wrap multiple writes to one transaction, SQLite need not to worry about every single row, and can flush many rows at once, most likely into single flash write - which is much faster. So, if you can, try to wrap at least few hundred writes into one transaction.

根据我的经验，Flash上​​的WAL并不是真的工作得很好，我发现坚持旧的日记模式会更有益.例如，Android 4对其SQLite数据库不使用WAL模式，这可能是有原因的.正如您所注意到的，WAL在某些情况下有无限增长的趋势(但是，如果很少提交交易，也会发生WAL-因此请确保偶尔执行一次.)

In my experience, WAL on flash is not really working very well, and I find it more beneficial to stick to old journalling mode. For example, Android 4 does not use WAL mode for its SQLite databases, and probably for a reason. As you have noticed, WAL has a tendency to grow without bound in some situations (however, it will also happen if transactions are rarely committed - so be sure to do that once in a while).

这篇关于SQLite WAL性能改进的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！