performance - 改善SQLite每秒插入的性能?

  显示原文与译文双语对照的内容

优化SQLite很棘手。Bulk-insert应用程序的性能可以从 85 inserts-per-second变化到 96 inserts-per-second 000 !

的背景:我们使用SQLite作为桌面应用程序的一部分。 我们有大量的配置数据存储在XML文件中,这些数据被解析并加载到SQLite数据库以便在应用程序初始化时进行进一步处理。 SQLite对于这种情况非常理想,因为它速度快,不需要专门的配置,并且数据库存储在磁盘上作为一个单一文件。

的理由: 最初我对我看到的性能感到失望。 使用 api, 它有很大 turns-out,SQLite的性能差别( bulk-inserts和选择两者) 取决于数据库是如何配置的,以及如何你的所在, 它不是一个简单的对figure-out所有的选项和技术的都是什么,所以我有意义但是它必要创建这个社区维基条目以将结果分享给所以的读者为了节省别人的麻烦,同样的调查进度。

在通常的概念中( 实验寻找: 而不是单纯谈论性能方法 "使用事务" ),实际上我认为它最好地编写一些C 代码和测量可以供选择的影响项目。 我们将从一些简单的数据开始:

  • 一个 28 meg TAB-delimited文本文件( 大约 865000条记录)的完成了多伦多市的
  • 我的测试机器是一个运行 Windows 3.60 1GHz P4 XP 。
  • 代码是用 MSVC 2005作为"释放"编译的,带有"完全优化"(/ox ),并支持快速代码(/ot ) 。
  • 我使用了 SQLite"合并",直接编译到测试应用程序中。 我正好有一个SQLite版本有点旧( 3.6.7 ),但我怀疑这些结果会与用最新版本( 如果你想的话,请留言) 得。

我们写一些代码 !

line-by-line: 一个简单的C 程序如下的文本文件,将字符串拆分为值的代码然后会将数据插入到了一个SQLite数据库。 在这里"基准线"版本的代码中,创建了数据库,但我们实际上不会插入数据:


/*************************************************************
 Baseline code to experiment with SQLite performance.

 Input data is a 28 Mb TAB-delimited text file of the
 complete Toronto Transit System schedule/route info 
 from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include"sqlite3.h"

#define INPUTDATA"C:TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE"c:TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE"CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

 sqlite3 * db;
 sqlite3_stmt * stmt;
 char * sErrMsg = 0;
 char * tail = 0;
 int nRetCode;
 int n = 0;

 clock_t cStartClock;

 FILE * pFile;
 char sInputBuf [BUFFER_SIZE] ="";

 char * sRT = 0;/* Route */
 char * sBR = 0;/* Branch */
 char * sVR = 0;/* Version */
 char * sST = 0;/* Stop Number */
 char * sVI = 0;/* Vehicle */
 char * sDT = 0;/* Date */
 char * sTM = 0;/* Time */

 char sSQL [BUFFER_SIZE] ="";

/*********************************************/
/* Open the Database and create the Schema */
 sqlite3_open(DATABASE, &db);
 sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

/*********************************************/
/* Open input file and import into Database*/
 cStartClock = clock();

 pFile = fopen (INPUTDATA,"r");
 while (!feof(pFile)) {

 fgets (sInputBuf, BUFFER_SIZE, pFile);

 sRT = strtok (sInputBuf,"t");/* Get Route */
 sBR = strtok (NULL,"t");/* Get Branch */
 sVR = strtok (NULL,"t");/* Get Version */
 sST = strtok (NULL,"t");/* Get Stop Number */
 sVI = strtok (NULL,"t");/* Get Vehicle */
 sDT = strtok (NULL,"t");/* Get Date */
 sTM = strtok (NULL,"t");/* Get Time */

/* ACTUAL INSERT WILL GO HERE */

 n++;

 }
 fclose (pFile);

 printf("Imported %d records in %4.2f secondsn", n, (clock() - cStartClock)/(double)CLOCKS_PER_SEC);

 sqlite3_close(db);
 return 0;
}


"控制""

运行代码as-is并不实际执行任何数据库操作,但它将让我们了解原始C 文件IO和字符串处理操作的速度。

在 864913秒内导入了条记录

好极了我们可以做 920 000 inserts-per-second,如果我们不做任何插入操作:- ) !


"worst-case-scenario""

我们将使用从文件读取的值生成SQL字符串并使用sqlite3_exec调用SQL操作:


sprintf(sSQL,"INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

这将会很慢,因为SQL将被编译为每个插入的VDBE代码,每次插入都会发生在自己的事务中。 多慢?

在 864913秒内导入了条记录

啊1小时和 45分钟 ! 这只是 85 inserts-per-second 。

使用事务

默认情况下,SQLite将评估唯一事务中的每个插入/更新语句。 如果执行大量插入,建议将操作包装在事务中:


sqlite3_exec(db,"BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

. . .

}
fclose (pFile);

sqlite3_exec(db,"END TRANSACTION", NULL, NULL, &sErrMsg);

在 864913秒内导入了条记录

这样就好多了简单包装即可我们所有的插入在单个事务中改进我们的性能来 23 000 inserts-per-second 。

使用准备好的语句

使用事务是一个巨大的改进,但是如果我们使用相同的SQL over-and-over,那么重新编译每个插入的SQL语句都没有意义。 让我们使用 sqlite3_prepare_v2 编译我们的SQL语句一次,然后使用 sqlite3_bind_text 将我们的参数绑定到那个语句:


/* Open input file and import into Database*/
cStartClock = clock();

sprintf(sSQL,"INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db,"BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

 fgets (sInputBuf, BUFFER_SIZE, pFile);

 sRT = strtok (sInputBuf,"t");/* Get Route */
 sBR = strtok (NULL,"t");/* Get Branch */
 sVR = strtok (NULL,"t");/* Get Version */
 sST = strtok (NULL,"t");/* Get Stop Number */
 sVI = strtok (NULL,"t");/* Get Vehicle */
 sDT = strtok (NULL,"t");/* Get Date */
 sTM = strtok (NULL,"t");/* Get Time */

 sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
 sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

 sqlite3_step(stmt);

 sqlite3_clear_bindings(stmt);
 sqlite3_reset(stmt);

 n++;

}
fclose (pFile);

sqlite3_exec(db,"END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f secondsn", n, (clock() - cStartClock)/(double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

在 864913秒内导入了条记录

漂亮这些有一些更多的代码( 别忘了调用 sqlite3_clear_bindingssqlite3_reset ) 但我们设立了翻了一倍多绩效 53 000 inserts-per-second 。

杂注同步= 关闭

默认情况下,在发出OS-level写命令后,SQLite将暂停。 这保证了数据写入磁盘。 通过设置 synchronous = OFF,我们指示SQLite简单地将数据hand-off写入操作系统,然后继续。 如果计算机在将数据写入磁盘之前遇到灾难性崩溃( 或者电源故障),数据库文件可能会损坏:


/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db,"PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

在 864913秒内导入了条记录

这些改进已经是现在稍小,但是我们是高达 69 600 inserts-per-second 。

杂注 journal_mode = 内存

考虑通过评估将回滚日志存储在内存中 PRAGMA journal_mode = MEMORY 处于损坏的状态用一个 partially-completed transaction, 。你的事务将会更快,但如果你的能源或者你的程序崩溃在事务期间你的数据库可以 left:


/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db,"PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在 864913秒内导入了条记录

64 000比以前的optimization.慢一点。

杂注同步= 关闭和杂注 journal_mode = 内存

让我们结合前面两个优化。 这是一个更危险的( 万一发生撞车),但我们只是导入数据( 不运行银行):


/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db,"PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db,"PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

在 864913秒内导入了条记录

奇妙的我们是能够做到 72 000 inserts-per-second 。

使用In-Memory数据库

为了进行踢腿,让我们构建所有以前的优化并重新定义数据库文件名,这样我们就完全在内存中工作了:


#define DATABASE":memory:"

在 864913秒内导入了条记录

它不是super-practical来存储我们的数据库在内存中,但它是令人印象深刻,我们可以执行 79 000 inserts-per-second 。

重构C 代码

while loop,尽管没有明确的一个SQLite的改进,我不喜欢这种额外 char* 赋值 operations. 让我们快速重构代码,将 strtok()的输出直接传递到 sqlite3_bind_text() 中,让编译器尝试加快我们的工作:


pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

 fgets (sInputBuf, BUFFER_SIZE, pFile);

 sqlite3_bind_text(stmt, 1, strtok (sInputBuf,"t"), -1, SQLITE_TRANSIENT);/* Get Route */
 sqlite3_bind_text(stmt, 2, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Branch */
 sqlite3_bind_text(stmt, 3, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Version */
 sqlite3_bind_text(stmt, 4, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Stop Number */
 sqlite3_bind_text(stmt, 5, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Vehicle */
 sqlite3_bind_text(stmt, 6, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Date */
 sqlite3_bind_text(stmt, 7, strtok (NULL,"t"), -1, SQLITE_TRANSIENT);/* Get Time */

 sqlite3_step(stmt);/* Execute the SQL Statement */
 sqlite3_clear_bindings(stmt);/* Clear bindings */
 sqlite3_reset(stmt);/* Reset VDBE */

 n++;
}
fclose (pFile);

注意:我们回到使用一个真正的数据库文件。 In-memory数据库快速但不一定是实际的数据库

在 864913秒内导入了条记录

稍微重构到字符串处理代码中使用这个参数绑定器允许我们执行 96 700 inserts-per-second 。 我觉得考虑到说,这是足够快速 。 当我们开始调整其他变量时( 例如 。 页面大小,索引创建,等等 ) 这将是我们的基准。


摘要( 到目前为止)

我希望你还在我身边 ! 原因开始讲到该道路是,bulk-insert性能因如此狂野与SQLite过程并不总是明显的什么改变都需要修改speed-up我们的行动。 使用相同的编译器( 和编译器选项),相同版本的SQLite和相同的数据,我们优化代码和使用SQLite的最糟糕的场景,从 85 inserts-per-second到 96 000 inserts-per-second !


创建索引然后插入 vs 插入然后创建索引

在开始度量 SELECT 性能之前,我们知道我们将创建索引。 在下面的一个答案中,建议在进行大容量插入时,在数据插入( 与创建索引相反,然后插入数据) 之后创建索引会更快。 让我们试试:

创建索引然后插入数据


sqlite3_exec(db,"CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db,"BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

在 864913秒内导入了条记录

插入数据,然后创建索引


...
sqlite3_exec(db,"END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db,"CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

在 864913秒内导入了条记录

就像预期的那样,如果一个列被索引,bulk-inserts会变慢,但如果在插入数据之后创建了索引,则会造成差异。 我们的no-index基线是 96 000 insert-per-second 。 首先创建索引,然后插入数据给我们 47 700 inserts-per-second,而先插入数据,然后创建索引会给我们 63 300 inserts-per-second 。


我很乐意为其他场景提出建议。 并将为选择查询编译类似的数据。

时间:

几点提示:

  1. 在事务中插入插入/更新
  2. 对于旧版本的SQLite - 考虑一个不太偏执的日志模式( pragma journal_mode ) 。 有 NORMAL,然后有 OFF 可以显著提高插入速度,如果操作系统崩溃,你不太担心数据库可能会被破坏。 如果你的应用程序崩溃了数据应该是好的。 请注意,在新版本中,OFF/MEMORY 设置对于应用程序级别的崩溃是不安全的。
  3. 使用页面大小也会产生不同的( PRAGMA page_size ) 。 较大的页面大小可以使读取和写入更快,因为在内存中容纳较大的页面。 请注意,你的数据库将使用更多的内存。
  4. 如果有索引,请在完成所有插入后调用 CREATE INDEX 。 这比创建索引和执行插入操作要快得多。
  5. 如果你对SQLite有并发访问,则必须十分小心,因为在写操作完成时整个数据库都被锁定,尽管有多个读取器可能会被锁定。 在新的SQLite版本中增加了一个,这一点有所改进。
  6. 利用节省空间。。更小的数据库更快。 例如如果你有键值对,请尝试使密钥成为 INTEGER PRIMARY KEY,这将替换表中隐含的唯一行号列。
  7. 如果使用的是多个线程,可以尝试使用共享页缓存,这将允许在线程之间共享加载的页面,这可以避免昂贵的i/o 调用。

这里我也问了类似的问题这里的

选择性能对我最感兴趣的是硬币的另一面,是因为我知道爱 SQLite 。 在一个 50 MB table, join.我见过经过 100,000选择每秒在我的一个 C++ 应用程序,包括一个3 方式 这显然是经过足够的'预热'时间才能把表放到Linux页面缓存中,但仍然是令人惊讶的性能 !

从历史上看 SQLite 得到还是很难选择指标要用于联接,但这种情况有改善,到什么时候我不再注意。 仔细使用索引显然很重要。 有时只需 重排序 也在 选择语句中的参数可以使一个大的差异。

避免 sqlite3_clear_bindings(stmt);

测试中的代码每次通过绑定都应该足够。

来自SQLite文档的C API介绍说明

在第一次调用 sqlite3_step() 或者 sqlite3_reset(), 之后,应用程序可以调用一个 sqlite3_bind() 接口来将值附加到参数。 对 sqlite3_bind()的每次调用都覆盖同一参数上的先前绑定

( 参见:sqlite.org/cintro.html ) 。 文档中没有的函数,函数说你必须调用它,除了设置绑定。

更多细节:http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings()

在大容量插入上

受到这篇文章和堆栈溢出问题的启发,我在这里找到了 -- 在一个SQLite数据库中一次插入多行? --我发布了第一个 Git库:

https://github.com/rdpoor/CreateOrUpdate

将ActiveRecords数组大容量加载到 MySQL,SQLite或者数据库中。 它包括忽略现有记录,覆盖或者引发错误的选项。 我的初步的基准测试显示一个 10 x 速度的改进相比于顺序写入 -- YMMV 。

我在生产代码中使用它,我经常需要导入大型数据集,我对它非常满意。

批量导入似乎能达到最佳性能如果你可以在插入/更新 。语句。 一个值为 10,000的值在一个只有几行行的表中工作良好,ymmv 。。

尝试使用SQLITE_STATIC代替SQLITE_TRANSIENT进行插入。 SQLITE_TRANSIENT将导致SQLite在返回之前复制字符串数据。 SQLITE_STATIC告诉它,你提供的内存地址将在查询执行完毕之前生效。 这将为每个循环保存几个分配,复制和取消分配操作。 可能有很大的改进。

如果你只关心阅读,那么从多个线程的多个连接读取更快的( 但可能会读取陈旧数据) 版本会更快。

首先在表格中查找项目:


 SELECT COUNT(*) FROM table

然后读入页面( 限制/偏移)


 SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

在哪里计算和计算 per-thread,如下所示:


int limit = (count + n_threads - 1)/n_threads;

对于每个线程:


int offset = thread_index * limit

对于我们的小型( 200 mb ) 数据库,这使得 50 -75% speed-up ( 3.8.0.2 64位 在 Windows 7上) 。 我们的表是大量 non-normalized ( 1000 -1500列,大约 100,000行或者更多行) 。

太多或者太小的线程不会做它,你需要测试和配置你自己。

对于我们来说,SHAREDCACHE降低了性能,所以我手动地把PRIVATECACHE放入了( 因为它是全局启用的)

在将cache_size提升到更高的值 换句话说,PRAGMA cache_size=10000; 之前,无法从事务获得任何收益

...