c cache 提高SQLite的每秒INSERT性能?




sqlite performance comparison (8)

如果您可以對INSERT / UPDATE語句進行分塊,則批量導入似乎表現最佳。 在一張只有幾排YYV的桌子上,價值10,000左右的價格對我來說效果很好......

優化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()


嘗試對這些插入使用SQLITE_STATIC而不是SQLITE_TRANSIENT

SQLITE_TRANSIENT將導致SQLite在返回之前複製字符串數據。

SQLITE_STATIC告訴它你給它的內存地址在查詢執行之前是有效的(在這個循環中總是如此)。 這將為每個循環節省幾次分配,複製和取消分配操作。 可能是一個很大的改進。



在將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(因為它是全局啟用的)


閱讀本教程後,我嘗試將其實現到我的程序中。

我有4-5個包含地址的文件。 每個文件有大約3000萬條記錄。 我使用的是你建議的相同配置,但我每秒的INSERT數量很低(每秒約10.000條記錄)。

這是你的建議失敗的地方。 您對所有記錄使用單個事務,並且沒有錯誤/失敗的單個插入。 假設您將每條記錄拆分為不同表上的多個插入。 如果記錄壞了會怎麼樣?

ON CONFLICT命令不適用,因為如果記錄中有10個元素,並且需要將每個元素插入到不同的表中,如果元素5出現CONSTRAINT錯誤,那麼之前的所有4個插入也需要。

所以這裡是回滾的來源。 回滾的唯一問題是您丟失了所有插入並從頂部開始。 你怎麼解決這個問題?

我的解決方案是使用多個交易。 我每隔10.000條記錄開始和結束一次交易(不要問為什麼這個數字,這是我測試過的最快的數字)。 我創建了一個10.000的數組,並在那裡插入成功的記錄。 發生錯誤時,我執行回滾,開始事務,從我的數組中插入記錄,提交然後在損壞的記錄之後開始新的事務。

這個解決方案幫助我繞過了處理包含錯誤/重複記錄的文件時遇到的問題(我的記錄差不多有4%)。

我創建的算法幫助我減少了2個小時的過程。 文件1小時30米的最終加載過程仍然很慢,但與最初的4小時相比沒有。 我設法將插入速度從10.000 / s加速到~14.000 / s

如果有人對如何加快它有任何其他想法,我願意接受建議。

更新

除了上面的答案,你應該記住,每秒插入次數取決於你使用的硬盤驅動器。 我在具有不同硬盤驅動器的3台不同PC上進行了測試,並且在時間上有很大差異。 PC1(1小時30分鐘),PC2(6小時)PC3(14小時),所以我開始想知道為什麼會這樣。

經過兩週的研究和檢查多個資源:Hard Drive,Ram,Cache,我發現硬盤上的某些設置會影響I / O速率。 通過單擊所需輸出驅動器上的屬性,您可以在常規選項卡中看到兩個選項。 Opt1:壓縮此驅動器,Opt2:允許此驅動器的文件將內容編入索引。

通過禁用這兩個選項,現在所有3台PC都需要大約相同的時間才能完成(1小時和20到40分鐘)。 如果遇到慢速插入,請檢查您的硬盤驅動器是否配置了這些選項。 它將為您節省大量時間和麻煩,試圖找到解決方案







optimization