cache - sqlite performance comparison




提高SQLite的每秒INSERT性能? (6)

優化SQLite很棘手。 C應用程序的批量插入性能可以從每秒85次插入到每秒超過96,000次插入!

背景:我們使用SQLite作為桌面應用程序的一部分。 我們有大量的配置數據存儲在XML文件中,這些數據被解析並加載到SQLite數據庫中,以便在初始化應用程序時進一步處理。 SQLite非常適合這種情況,因為它速度快,不需要專門配置,數據庫作為單個文件存儲在磁盤上。

理由: 最初我對我所看到的表現感到失望。 事實證明,SQLite的性能可能會有很大差異(對於批量插入和選擇),具體取決於數據庫的配置方式以及您使用API​​的方式。 弄清楚所有選項和技術是什麼並不是一件小事,所以我認為創建這個社區wiki條目與Stack Overflow讀者分享結果是謹慎的,以便為其他人節省相同調查的麻煩。

實驗:我不是簡單地談論一般意義上的性能提示(即“使用事務!” ),而是認為最好編寫一些C代碼並實際測量各種選項的影響。 我們將從一些簡單的數據開始:

  • 多倫多市完整交通時間表的28 MB TAB分隔文本文件(約865,000條記錄)
  • 我的測試機器是運行Windows XP的3.60 GHz P4。
  • 該代碼使用Visual C ++ 2005編譯為“Release”,帶有“Full Optimization”(/ Ox)和Favor Fast Code(/ Ot)。
  • 我正在使用SQLite“Amalgamation”,直接編譯到我的測試應用程序中。 我碰巧遇到的SQLite版本有點舊(3.6.7),但我懷疑這些結果與最新版本相當(如果你不這麼想請發表評論)。

我們來寫一些代碼吧!

代碼:一個簡單的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] = "\0";

    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] = "\0";

    /*********************************************/
    /* 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 seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

“控制”

按原樣運行代碼實際上並不執行任何數據庫操作,但它會讓我們了解原始C文件I / O和字符串處理操作的速度。

在0.94秒內導入864913條記錄

大! 我們可以每秒執行920,000次插入,前提是我們實際上沒有插入任何插入:-)

“最壞情況場景”

我們將使用從文件中讀取的值生成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代碼,並且每個插入都將在其自己的事務中發生。 多慢?

在9933.61秒內導入864913條記錄

哎呀! 2小時45分鐘! 這只是每秒85次插入。

使用交易

默認情況下,SQLite將評估唯一事務中的每個INSERT / UPDATE語句。 如果執行大量插入操作,建議將操作包裝在事務中:

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);

在38.03秒內導入864913條記錄

那更好。 在一次交易中簡單地包裝我們的所有插件,我們的性能提高到每秒23,000次插入。

使用準備好的聲明

使用事務是一個巨大的改進,但是如果我們使用相同的SQL反複使用,則重新編譯每個插入的SQL語句是沒有意義的。 讓我們使用sqlite3_prepare_v2編譯我們的SQL語句一次,然後使用sqlite3_bind_text將我們的參數綁定到該語句:

/* Open input file and import into the 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 seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

在16.27秒內導入864913條記錄

太好了! 還有一些代碼(不要忘記調用sqlite3_clear_bindingssqlite3_reset ),但是我們的性能提高了一倍多,達到每秒53,000次插入。

PRAGMA同步= OFF

默認情況下,SQLite將在發出操作系統級寫入命令後暫停。 這可以保證將數據寫入磁盤。 通過設置synchronous = OFF ,我們指示SQLite簡單地將數據切換到OS進行寫入然後繼續。 如果計算機在將數據寫入盤片之前遭受災難性崩潰(或電源故障),則數據庫文件可能會損壞:

/* 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);

在12.41秒內導入864913條記錄

這些改進現在變小了,但我們每秒最多可達69,600次插入。

PRAGMA journal_mode = MEMORY

考慮通過評估PRAGMA journal_mode = MEMORY將回滾日誌存儲在內存中。 您的交易會更快,但如果您在交易期間失去電力或程序崩潰,您的數據庫可能會因部分完成的交易而處於損壞狀態:

/* 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);

在13.50秒內導入864913條記錄

比之前的優化速度慢一點, 每秒64,000次插入。

PRAGMA synchronous = OFF PRAGMA journal_mode = MEMORY

讓我們結合前兩個優化。 風險更大(如果發生崩潰),但我們只是導入數據(不運行銀行):

/* 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);

在12.00秒內導入864913條記錄

太棒了! 我們每秒可以進行72,000次插入。

使用內存數據庫

只是為了解決問題,讓我們在之前的所有優化基礎上重新定義數據庫文件名,以便我們完全在RAM中工作:

#define DATABASE ":memory:"

在10.94秒內導入864913條記錄

將我們的數據庫存儲在RAM中並不是非常實用,但令人印象深刻的是我們每秒可以執行79,000次插入。

重構C代碼

雖然不是特別的SQLite改進,但我不喜歡while循環中的額外char*賦值操作。 讓我們快速重構該代碼,將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);

注意:我們回到使用真正的數據庫文件。 內存數據庫很快,但不一定實用

在8.94秒內導入864913條記錄

對參數綁定中使用的字符串處理代碼進行輕微重構,使我們每秒執行96,700次插入。 我認為可以說這很快 。 當我們開始調整其他變量(即頁面大小,索引創建等)時,這將成為我們的基準。

摘要(到目前為止)

我希望你還和我在一起! 我們開始走這條道路的原因是,SQLite的批量插入性能變化如此之大,並且並不總是很明顯需要做出哪些改變來加速我們的操作。 使用相同的編譯器(和編譯器選項),相同版本的SQLite和相同的數據,我們優化了我們的代碼和SQLite的使用, 從最壞情況下每秒85次插入到每秒超過96,000次插入!

創建INDEX然後INSERT與INSERT然後CREATE INDEX

在我們開始測量SELECT性能之前,我們知道我們將創建索引。 在下面的一個答案中建議,在進行批量插入時,在插入數據後創建索引會更快(而不是先創建索引然後插入數據)。 我們試試吧:

創建索引然後插入數據

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

在18.13秒內導入864913條記錄

插入數據然後創建索引

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

在13.66秒內導入864913條記錄

正如預期的那樣,如果對一列進行索引,批量插入會更慢,但如果在插入數據後創建索引,則確實會產生差異。 我們的無指數基線是每秒96,000次插入。 首先創建索引然後插入數據每秒給我們47,700個插入,而先插入數據然後創建索引每秒給我們63,300個插入。

我很樂意為其他場景嘗試建議......並且很快就會為SELECT查詢編譯類似的數據。


批量插入

靈感來自這篇文章以及引導我的問題 - 是否可以在SQLite數據庫中一次插入多行? - 我發布了我的第一個Git存儲庫:

https://github.com/rdpoor/CreateOrUpdate

它將一系列ActiveRecords批量加載到MySQL ,SQLite或PostgreSQL數據庫中。 它包括一個忽略現有記錄,覆蓋它們或引發錯誤的選項。 我的基本測試表明,與順序寫入相比,速度提高了10倍 - YMMV。

我在生產代碼中使用它,我經常需要導入大型數據集,我對此非常滿意。


避免使用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()http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings()


在將cache_size提升到更高的值(即PRAGMA cache_size=10000;之前,我不能從事務中獲得任何收益PRAGMA cache_size=10000;


如果你只關心閱讀,那麼更快(但可能讀取過時的數據)版本是從多個線程的多個連接中讀取(每個線程的連接)。

首先找到表中的項目:

 SELECT COUNT(*) FROM table

然後讀入頁面(LIMIT / OFFSET)

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

在哪里和每個線程計算,像這樣:

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

對於每個線程:

int offset = thread_index * limit

對於我們的小型(200mb)數據庫,這使得速度提高了50-75%(Windows 7上為3.8.0.2 64位)。 我們的表非常非標準化(1000-1500列,大約100,000行或更多行)。

線程太多或太少都無法做到,您需要自己進行基準測試和分析。

同樣對我們來說,SHAREDCACHE使性能變慢,所以我手動放置PRIVATECACHE(因為它是全局啟用的)


幾個提示:

  1. 將插入/更新放入事務中。
  2. 對於舊版本的SQLite - 考慮一個不那麼偏執的日誌模式( pragma journal_mode )。 有NORMAL ,然後有OFF ,如果您不太擔心如果操作系統崩潰可能會損壞數據庫,則可以顯著提高插入速度。 如果您的應用程序崩潰,數據應該沒​​問題。 請注意,在較新版本中, OFF/MEMORY設置對於應用程序級別崩潰是不安全的。
  3. 使用頁面大小也會產生影響( PRAGMA page_size )。 具有較大的頁面大小可以使讀取和寫入更快,因為較大的頁面保存在內存中。 請注意,您的數據庫將使用更多內存。
  4. 如果您有索引,請考慮在完成所有插入後調用CREATE INDEX 。 這比創建索引然後執行插入要快得多。
  5. 如果您同時訪問SQLite,則必須非常小心,因為在完成寫入時整個數據庫都被鎖定,儘管可能有多個讀取器,但寫入將被鎖定。 通過在較新的SQLite版本中添加WAL,這有所改善。
  6. 利用節省空間......較小的數據庫更快。 例如,如果您有鍵值對,請盡可能使鍵成為INTEGER PRIMARY KEY ,這將替換錶中隱含的唯一行號列。
  7. 如果您使用多個線程,則可以嘗試使用共享頁面緩存 ,這將允許在線程之間共享加載的頁面,這可以避免昂貴的I / O調用。
  8. 不要使用!feof(file)

我也在herehere問過類似的問題。






optimization