提高SQLite的INSERT-per-second效能?

Improve INSERT-per-second performance of SQLite?

來源: link 標籤:cperformancesqliteoptimization 更新時間:

  問題

最佳化SQLite很棘手. C應用程式的Bulkinsert效能可以從每秒85個插入到每秒96,000多個插入!

背景:我們使用SQLite作為桌面應用程式的一部分.我們有大量的配置資料儲存在XML檔案中,當應用程式初始化時解析並載入到SQLite資料庫進一步處理. SQLite對於這種情況是理想的,因為它很快,它不需要專門的配置,資料庫儲存在磁碟上作為單個檔案.

理由:最初我對我看到的效能感到失望.它轉出了SQLite的效能可以根據資料庫的配置方式和使用API的方式大不相同(包括bulking和選擇).弄清楚所有選項和技術是什麼並不是一個微不足道的事情,所以我認為建立這個社群wiki條目來與Stack Overflow讀者共享結果以儲存其他人的調查麻煩是謹慎的.

實驗:我認為最好編寫一些C程式碼並實際測量各種選項的影響.我們將從一些簡單的資料開始:

  • 一個 28 MB TAB 分隔的文字檔案(約 865,000 條記錄) 完整的多倫多 的過境時間表
  • 我的測試機是執行Windows XP的3.60 GHz P4.
  • 程式碼是用 Visual C++ 2005作為“Release”編譯的,其中包括“完全最佳化”(/ Ox)和法惠或快速程式碼(/ Ot)。
  • 我正在使用SQLite“Amalgamation”,直接編譯到我的測試應用程式中.我碰巧擁有的SQLite版本有點老(3.6.7),但我懷疑這些結果將與最新版本相當(如果你認為其他的話請留下評論).

讓我們寫一些程式碼!

程式碼:一個讀取文字檔案line-byline的簡單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, "	");     /* Get Route */
        sBR = strtok (NULL, "	");            /* Get Branch */
        sVR = strtok (NULL, "	");            /* Get Version */
        sST = strtok (NULL, "	");            /* Get Stop Number */
        sVI = strtok (NULL, "	");            /* Get Vehicle */
        sDT = strtok (NULL, "	");            /* Get Date */
        sTM = strtok (NULL, "	");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

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

    sqlite3_close(db);
    return 0;
}

"控制"

執行程式碼as-is實際上沒有執行任何資料庫操作,但它會讓我們知道原始的C檔案I/O和字串處理操作有多快.

進口記錄 864913 0.94 秒

很棒!我們可以每秒做920,000個插入,只要我們實際上沒有做任何插入: –)

"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 9933.61 秒

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

進口記錄 864913 38.03 秒

這更好.簡單地將我們的所有插入包裝在一個事務中將我們的效能提高到每秒23,000個插入.

使用編寫的說明

使用事務是一個巨大的改進,但是如果我們使用相同的SQL over-and-over,重新編譯每個插入的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, "	");   /* Get Route */
    sBR = strtok (NULL, "	");        /* Get Branch */
    sVR = strtok (NULL, "	");        /* Get Version */
    sST = strtok (NULL, "	");        /* Get Stop Number */
    sVI = strtok (NULL, "	");        /* Get Vehicle */
    sDT = strtok (NULL, "	");        /* Get Date */
    sTM = strtok (NULL, "	");        /* 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, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

16.27中的進口記錄864913 秒

很好!有更多的程式碼(不要忘記呼叫sqlite3_clear_bindings和sqlite3_reset),但我們的效能增加了一倍以上,每秒53,000個插入.

pragma同步=OFF

預設情況下,SQLite在發出OSlevel write命令後暫停.這保證資料被寫入磁碟.透過設定synchronous = OFF,我們指示SQLite簡單地將資料傳輸到作業系統以便寫入,然後繼續.如果計算機在資料寫入平臺之前遇到災難性崩潰(或功率故障),資料庫檔案可能會受到損壞: 

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

進口記錄 864913 13.50 秒

比之前的最佳化慢一點,每秒64,000插入。

pragma同步= 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);

進口記錄 864913 12.00 秒

太棒了!我們能每秒做72000個插入。

使用記憶體資料庫

只是為了踢球,讓我們基於以前的所有最佳化並重新定義資料庫檔名,所以我們完全在RAM中工作: 

 #define DATABASE ":memory:"

在10.94中匯入864913條記錄 秒

將資料庫儲存在 RAM 中是不太實用的,但是我們可以每秒執行 79,000 個插入令人印象深刻。

重構C程式碼

雖然沒有具體的SQLite改進,但我不喜歡char*分配操作中的額外while迴圈.讓我們快速重構該程式碼以將strtok()的輸出直接傳遞給sqlite3_bind_text(),並讓編譯器嘗試加速我們的事情: 

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

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "	"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "	"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "	"), -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-記憶體資料庫很快,但不一定實用

8.94中的進口記錄864913 秒

對我們引數繫結中使用的字串處理程式碼進行輕微重構,使我們能夠每秒執行96,700個插入.我認為可以安全地說這是很快的.當我們開始調整其他變數(即頁面大小,索引建立等)時,這將是我們的基準.

摘要(迄今)

我希望你仍然跟我在一起!我們開始這條路的原因是bulkinsert效能與SQLite非常不同,並且並不總是明顯需要進行哪些更改來加快我們的操作.使用相同的編譯器(和編譯器選項),相同的SQLite版本和相同的資料,我們已經優化了我們的程式碼,並且我們使用SQLite從每秒85插入的worstcase場景轉到每秒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);
...

進口記錄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 秒

正如預期的那樣,如果一列被索引,bulk-instruct會更慢,但如果在插入資料後建立索引,則確實會有所不同.我們的noindex基線是每秒96,000插入.建立索引然後首先插入資料給我們每秒47,700插入,而先插入資料然後建立索引給我們每秒63,300插入.

我很樂意為其他場景提出建議來嘗試...並且很快將為SELECT查詢編譯類似的資料.

  最佳答案

幾個提示:

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

我也問過類似的問題here 和here .

  相同標籤的其他問題

cperformancesqliteoptimization
上一個問題:
下一個問題: