最近开发了一个相册模块，用户上传照片后，通过fanotify监视到新文件，然后将照片信息保存到sqlite数据库。
数据库使用WAL模式，操作流程是：
sqlite3_open()
-> BEGIN TRANSACTION
-> insert
-> END TRANSACTION
sqlite3_close()
每次操作数据库都会打开，开始事务，插入数据、提交事务，然后关闭。
测试发现，在磁盘比较繁忙的时候，这套流程的耗时可能需要1s多。而仅仅只是插入几条数据，数据量可能也就几kB。
于是写一个demo，demo打开数据库，设置为WAL模式，然后循环执行事务，一个事务插入一定量的数据，将每个操作的耗时打印出来。
demo第一个参数表示一个事务插入多少数据，第二个参数表示执行多少次事务。
第一次执行：
# ./test_sqlite 10 2 open : 4296174 us --- begin transaction : 7 us insert : 611 us end transaction : 111811 us --- begin transaction : 25 us insert : 386 us end transaction : 131 us --- close : 65592 us
第一次会创建数据库。可以发现，在open的时候，消耗了4s。
第二次执行：
# ./test_sqlite 10 2 open : 1591 us --- begin transaction : 7 us insert : 438 us end transaction : 48153 us --- begin transaction : 25 us insert : 383 us end transaction : 121 us --- close : 43986 us
使用dd构造磁盘繁忙的情况，执行：
# dd if=/dev/zero of=tmp bs=4096 # ./test_sqlite 10 2 open : 1987 us --- begin transaction : 7 us insert : 373 us end transaction : 7627371 us --- begin transaction : 28 us insert : 392 us end transaction : 129 us --- close : 24180207 us
可以发现，第一个commit transaction耗时了7s，然后close耗时了24s。
使用strace进行分析，发现
# strace -vfttT ./test_sqlite 10 2 17:38:38.134131 fdatasync(4) = 0 <8.687928>
时间都消耗在了fdatasync。同时发现了一个规律，第二个transaction都非常快。strace里面显示，只有第一个transaction触发了fdatasync。
Write-Ahead Logging，https://www.sqlite.org/wal.html
它和传统的日志不一样，它将操作先写入wal文件，然后再日志达到1000个page的时候，再写回到主database。
这就引入了一个checkpoint机制，可以手动写回，也可以在commit transaction的自动写回，close数据库的时候，也会自动写回。
只有在写回的时候，才会触发fdatasync。
为了避免频繁触发fdatasync，我们不能频繁打开关闭，打开后的第一个transaction必然触发写回。所以优化方案是，数据库保持打开的状态，即可。
demo源码：
#include <sqlite3.h>
#include <stdio.h>
#include <stdint.h>
#include <stdlib.h>
#include <time.h>
#define SQL_JOURNAL_WAL "PRAGMA journal_mode=WAL;"
#define SQL_SYNC_NORMAL "PRAGMA synchronous=normal;"
#define SQL_BEGIN_TRANSACTION "BEGIN TRANSACTION;"
#define SQL_END_TRANSACTION "END TRANSACTION;"
#define SQL_CREAT_PHOTO \
"CREATE TABLE IF NOT EXISTS photo (" \
"id INTEGER PRIMARY KEY AUTOINCREMENT," \
"parent INTEGER," \
"name TEXT," \
"mode INTEGER," \
"size INTEGER," \
"msec INTEGER," \
"mnsec INTEGER," \
"shot_time INTEGER," \
"flags INTEGER," \
"latitude INTEGER," \
"longitude INTEGER," \
"duration INTEGER," \
"city TEXT," \
"poi TEXT" \
");"
struct sqlite_photo
{
uint64_t photo_id;
uint64_t parent;
const char *name;
unsigned mode;
uint64_t size;
uint64_t mtime_sec;
uint32_t mtime_nsec;
uint64_t shot_time;
uint32_t flags;
uint32_t latitude;
uint32_t longitude;
uint32_t duration;
const char *city;
const char *poi;
};
static unsigned tsus()
{
struct timespec ts;
unsigned v;
clock_gettime(CLOCK_MONOTONIC, &ts);
v = ts.tv_sec * 1000000 + ts.tv_nsec / 1000;
return v;
}
static char *sql_err(struct sqlite3 *db, int ret)
{
static char errstr[512];
snprintf(errstr, sizeof(errstr), "%d(%s): %s",
ret, sqlite3_errstr(ret), sqlite3_errmsg(db));
return errstr;
}
struct sqlite3 *sqlite_open(const char *db_path)
{
struct sqlite3 *db = NULL;
int ret;
ret = sqlite3_open(db_path, &db);
if (ret != SQLITE_OK) {
fprintf(stderr, "sqlite3_open() open %s failed: %s\n",
db_path, sqlite3_errstr(ret));
return NULL;
}
ret = sqlite3_exec(db, SQL_JOURNAL_WAL, 0, 0, 0);
if (ret != SQLITE_OK) {
fprintf(stderr, "enable WAL failed: %s\n", sql_err(db, ret));
return NULL;
}
ret = sqlite3_exec(db, SQL_SYNC_NORMAL, 0, 0, 0);
if (ret != SQLITE_OK) {
fprintf(stderr, "set sync failed: %s\n", sql_err(db, ret));
return NULL;
}
ret = sqlite3_exec(db, SQL_CREAT_PHOTO, 0, 0, 0);
if (ret != SQLITE_OK) {
fprintf(stderr, "create photo table failed: %s\n", sql_err(db, ret));
return NULL;
}
return db;
}
void sqlite_close(struct sqlite3 *db)
{
int ret ;
ret = sqlite3_close(db);
if (ret != SQLITE_OK)
fprintf(stderr, "sqlite3 close fialed: %s\n", sql_err(db, ret));
}
void sqlite_free_stmt(struct sqlite3_stmt *stmt)
{
sqlite3_finalize(stmt);
}
int sqlite_begin_transaction(struct sqlite3 *db)
{
int ret;
ret = sqlite3_exec(db, SQL_BEGIN_TRANSACTION, 0, 0, NULL);
if (ret != SQLITE_OK) {
fprintf(stderr, "begin transaction failed: %d: %s: %s\n",
ret, sqlite3_errstr(ret), sqlite3_errmsg(db));
return -1;
}
return 0;
}
int sqlite_end_transaction(struct sqlite3 *db)
{
int ret;
ret = sqlite3_exec(db, SQL_END_TRANSACTION, 0, 0, NULL);
if (ret != SQLITE_OK) {
fprintf(stderr, "end transaction failed: %d: %s: %s\n",
ret, sqlite3_errstr(ret), sqlite3_errmsg(db));
return -1;
}
return 0;
}
int sqlite_insert_photo(struct sqlite3 *db, struct sqlite_photo *photo)
{
sqlite3_stmt *stmt = NULL;
int ret;
int err = -1;
const char *sql = "INSERT INTO photo"
"(parent, name, mode, size, msec, mnsec, shot_time, "
"flags, latitude, longitude, city, poi)"
"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
ret = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (ret != SQLITE_OK) {
fprintf(stderr, "prepare insert photo failed: %s\n", sql_err(db, ret));
return -1;
}
ret = 0;
ret += sqlite3_bind_int64(stmt, 1, photo->parent);
ret += sqlite3_bind_text(stmt, 2, photo->name, -1, SQLITE_STATIC);
ret += sqlite3_bind_int(stmt, 3, photo->mode);
ret += sqlite3_bind_int64(stmt, 4, photo->size);
ret += sqlite3_bind_int64(stmt, 5, photo->mtime_sec);
ret += sqlite3_bind_int(stmt, 6, photo->mtime_nsec);
ret += sqlite3_bind_int64(stmt, 7, photo->shot_time);
ret += sqlite3_bind_int(stmt, 8, photo->flags);
ret += sqlite3_bind_int(stmt, 9, photo->latitude);
ret += sqlite3_bind_int(stmt, 10, photo->longitude);
ret += sqlite3_bind_text(stmt, 11, photo->city, -1, SQLITE_STATIC);
ret += sqlite3_bind_text(stmt, 12, photo->poi, -1, SQLITE_STATIC);
if (ret != SQLITE_OK) {
fprintf(stderr, "bind insert photo failed\n");
goto out;
}
ret = sqlite3_step(stmt);
if (ret != SQLITE_DONE) {
fprintf(stderr, "insert photo failed: %s\n", sql_err(db, ret));
goto out;
}
photo->photo_id = sqlite3_last_insert_rowid(db);
err = 0;
out:
sqlite3_finalize(stmt);
return err;
}
static int batch_insert(struct sqlite3 *db, int seq, int count)
{
int ret;
int i;
unsigned ts;
dprintf(1, "%-20s: ", "begin transaction");
ts = tsus();
ret = sqlite_begin_transaction(db);
dprintf(1, "%u us\n", tsus() - ts);
if (ret < 0)
return -1;
dprintf(1, "%-20s: ", "insert");
ts = tsus();
for (i = 0; i < count; i++) {
struct sqlite_photo photo = {
.parent = 0,
.name = "test",
.city = "shenzhen",
.poi = "park",
};
ret = sqlite_insert_photo(db, &photo);
if (ret < 0)
return -1;
}
dprintf(1, "%u us\n", tsus() - ts);
dprintf(1, "%-20s: ", "end transaction");
ts = tsus();
ret = sqlite_end_transaction(db);
if (ret < 0)
return -1;
dprintf(1, "%u us\n", tsus() - ts);
return 0;
}
int main(int argc, char **argv)
{
struct sqlite3 *db;
int count = 1;
int repeat = 1;
int i;
unsigned ts;
if (argc > 1)
count = atoi(argv[1]);
if (argc > 2)
repeat = atoi(argv[2]);
dprintf(1, "%-20s: ", "open");
ts = tsus();
db = sqlite_open("test.db");
if (!db)
return 1;
dprintf(1, "%u us\n", tsus() - ts);
for (i = 0; i < repeat; i++) {
int ret;
dprintf(1, "---\n");
ret = batch_insert(db, i, count);
if (ret < 0)
break;
}
dprintf(1, "---\n");
dprintf(1, "%-20s: ", "close");
ts = tsus();
sqlite_close(db);
printf("%u us\n", tsus()-ts);
return 0;
}