Вопрос: Улучшить производительность SQLite в секунду в секунду?


Оптимизация SQLite сложна. Производительность вложений в C-приложение может варьироваться от 85 вставок в секунду до более чем 96 000 вставок в секунду!

Задний план: Мы используем SQLite как часть настольного приложения. У нас есть большое количество данных конфигурации, хранящихся в XML-файлах, которые анализируются и загружаются в базу данных SQLite для дальнейшей обработки, когда приложение инициализируется. SQLite идеально подходит для этой ситуации, потому что он быстрый, он не требует специализированной конфигурации, а база данных хранится на диске как один файл.

Обоснование: Первоначально я был разочарован работой, которую я видел. Оказывается, производительность SQLite может значительно различаться (как для объемных вставок, так и для их выбора) в зависимости от того, как настроена база данных и как вы используете API. Не было тривиального вопроса выяснить, какие все варианты и методы были, поэтому я счел разумным создать эту запись в вики сообщества, чтобы поделиться результатами с читателями Stack Overflow, чтобы спасти других от проблем тех же исследований.

Эксперимент: Вместо того, чтобы просто говорить о подсказках производительности в общем смысле (т. «Используйте транзакцию!» ), Я подумал, что лучше написать код C и фактически измерять влияние различных вариантов. Мы начнем с простых данных:

  • Текстовый файл с разделителем TAB объемом 28 МБ (приблизительно 865 000 записей) полный транзитный график для города Торонто
  • Моя тестовая машина - это 3,60 ГГц P4, работающая под управлением Windows XP.
  • Код компилируется с помощью Visual C ++ 2005 как «Release» с «Полная оптимизация» (/ Ox) и Fast 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;
}

Контроль"

Выполнение кода as-is фактически не выполняет каких-либо операций с базой данных, но это даст нам представление о том, насколько быстрыми являются операции ввода-вывода и операций обработки строк в C-файле.

Импортировано 864913 записей в 0.94   секунд

Большой! Мы можем делать 920 000 вставок в секунду, если мы фактически не делаем никаких вставок :-)


«Сценарий наихудшего случая»

Мы собираемся сгенерировать строку SQL с использованием значений, считанных из файла, и вызвать эту операцию SQL с помощью sqlite3_exec:

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   секунд

Хлоп! 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 для каждой вставки не имеет смысла, если мы используем один и тот же SQL over-and-over. Давайте использовать 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;

Импортировано 864913 записей в 16.27   секунд

Ницца! Есть еще немного кода (не забудьте позвонить sqlite3_clear_bindingsа также sqlite3_reset), но мы более чем удвоили нашу производительность до 53 000 вставок в секунду.

PRAGMA synchronous = OFF

По умолчанию SQLite приостанавливается после выдачи команды записи на уровне ОС. Это гарантирует, что данные записываются на диск. Установив 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);

Импортировано 864913 записей в 12.41   секунд

Усовершенствования теперь меньше, но мы готовы 69 600 вставок в секунду.

PRAGMA journal_mode = ПАМЯТЬ

Рассмотрите возможность хранения журнала отката в памяти путем оценки 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 synchronous = OFF а также PRAGMA 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);

Imported 864913 записей в 12.00   секунд

Фантастика! Мы в состоянии сделать 72 000 вставок в секунду.

Использование базы данных в памяти

Просто для ударов, давайте опишем все предыдущие оптимизации и переопределим имя файла базы данных, чтобы мы полностью работали в ОЗУ:

#define DATABASE ":memory:"

Импортировано 864913 записей в 10.94   секунд

Это не супер-практично хранить нашу базу данных в ОЗУ, но впечатляет, что мы можем выполнять 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, "\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);

Примечание. Мы вернулись к использованию реального файла базы данных. Базы данных в памяти бывают быстрыми, но не обязательно практичными

Импортировано 864913 записей в 8.94   секунд

Небольшой рефакторинг кода обработки строк, используемый в привязке параметров, позволил нам выполнить 96 700 вставок в секунду. Я думаю, можно с уверенностью сказать, что это много быстро , Когда мы начнем изменять другие переменные (т. Е. Размер страницы, создание индекса и т. Д.), Это будет нашим эталоном.


Резюме (до сих пор)

Надеюсь, ты все еще со мной! Причина, по которой мы пошли по этой дороге, заключается в том, что производительность вместительной вставки сильно отличается от SQLite, и не всегда очевидно, какие изменения необходимо внести, чтобы ускорить нашу работу. Используя тот же компилятор (и параметры компилятора), ту же версию SQLite и те же данные, мы оптимизировали наш код и наше использование SQLite для перехода от наихудшего сценария из 85 вставок в секунду до более чем 96 000 вставок в секунду!


CREATE 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 записей в 18.13   секунд

Вставить данные, затем Создать индекс

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

Импортировано 864913 записей в 13.66   секунд

Как и ожидалось, объемные вставки медленнее, если индексируется один столбец, но имеет значение, если индекс создается после того, как данные вставлены. Наш базовый уровень без индекса составляет 96 000 вставок в секунду. Сначала создание индекса, а затем вставка данных дает нам 47 700 вставок в секунду, тогда как ввод данных сначала, а затем создание индекса дает нам 63 300 вставок в секунду.


Я бы с радостью принял предложения по другим сценариям, чтобы попробовать ... И скоро будет компилировать похожие данные для запросов SELECT.


2540


источник


Ответы:


Несколько советов:

  1. Вставьте вставки / обновления в транзакцию.
  2. Для более старых версий SQLite - рассмотрите режим менее параноидального журнала ( pragma journal_mode). Там есть NORMAL, а затем OFF, что может значительно увеличить скорость вставки, если вы не слишком беспокоитесь о том, что база данных может быть повреждена, если ОС выйдет из строя. Если ваше приложение выходит из строя, данные должны быть точными. Обратите внимание, что в более новых версиях OFF/MEMORYнастройки небезопасны для сбоев на уровне приложений.
  3. Игра с размерами страниц также имеет значение ( PRAGMA page_size). Имея большие размеры страниц, вы можете сделать чтение и запись немного быстрее, поскольку в памяти хранятся более крупные страницы. Обратите внимание, что для вашей базы данных будет использоваться больше памяти.
  4. Если у вас есть индексы, подумайте о том, чтобы позвонить CREATE INDEXпосле выполнения всех ваших вставок. Это значительно быстрее, чем создание индекса, а затем выполнение ваших вставок.
  5. Вы должны быть достаточно осторожны, если у вас есть одновременный доступ к SQLite, поскольку вся база данных заблокирована при выполнении записи, и, хотя возможны несколько считывателей, записи будут заблокированы. Это несколько улучшилось с добавлением WAL в новых версиях SQLite.
  6. Воспользуйтесь преимуществами экономии места ... более мелкие базы данных идут быстрее. Например, если у вас есть пары ключевых значений, попробуйте сделать ключ INTEGER PRIMARY KEYесли это возможно, что заменит подразумеваемый уникальный столбец строк в таблице.
  7. Если вы используете несколько потоков, вы можете попробовать использовать кеш общих страниц , что позволит загружать загружаемые страницы между потоками, что позволяет избежать дорогостоящих вызовов ввода-вывода.
  8. Не использовать !feof(file)!

Я также задал аналогичные вопросы Вот а также Вот ,


656



Попробуйте использовать SQLITE_STATICвместо SQLITE_TRANSIENTдля этих вставок.

SQLITE_TRANSIENTзаставит SQLite скопировать данные строки перед возвратом.

SQLITE_STATICсообщает, что адрес памяти, который вы ему дали, будет действителен до тех пор, пока запрос не будет выполнен (что в этом цикле всегда имеет место). Это позволит вам несколько распределить, скопировать и освободить операции для каждого цикла. Возможно, значительное улучшение.


98



Избегайте sqlite3_clear_bindings (stmt);

Код в тесте устанавливает привязки каждый раз, через которые должно быть достаточно.

Вступление C API из документации SQLite

Перед вызовом sqlite3_step () в первый раз или сразу   после sqlite3_reset () приложение может вызвать один из   sqlite3_bind () для присоединения значений к параметрам. каждый   вызов sqlite3_bind () переопределяет предыдущие привязки по одному и тому же параметру

(видеть: sqlite.org/cintro.html ). В документах нет ничего эта функция говоря, что вы должны называть его, а не просто устанавливать привязки.

Более детально: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


76



На объемных вставках

Вдохновленный этим сообщением и вопросом о переполнении стека, который привел меня сюда, Можно ли вставлять сразу несколько строк в базу данных SQLite? - Я отправил свой первый Гит репозиторий:

https://github.com/rdpoor/CreateOrUpdate

который загружает массив ActiveRecords в MySQL , SQLite или PostgreSQL базы данных. Он включает в себя возможность игнорировать существующие записи, перезаписывать их или вызывать ошибку. Мои рудиментарные тесты показывают 10-кратное улучшение скорости по сравнению с последовательной записью - YMMV.

Я использую его в производственном коде, где мне часто приходится импортировать большие наборы данных, и я очень доволен им.


45



Bulk imports seems to perform best if you can chunk your INSERT/UPDATE statements. A value of 10,000 or so has worked well for me on a table with only a few rows, YMMV...


38



If you care only about reading, somewhat faster (but might read stale data) version is to read from multiple connections from multiple threads (connection per-thread).

First find the items, in the table:

 SELECT COUNT(*) FROM table

then read in pages (LIMIT/OFFSET)

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

where and are calculated per-thread, like this:

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

for each thread:

int offset = thread_index * limit

For our small (200mb) db this made 50-75% speed-up (3.8.0.2 64-bit on Windows 7). Our tables are heavily non-normalized (1000-1500 columns, roughly 100,000 or more rows).

Too many or too little threads won't do it, you need to benchmark and profile yourself.

Also for us, SHAREDCACHE made the performance slower, so I manually put PRIVATECACHE (cause it was enabled globally for us)


29



I coudn't get any gain from transactions until I raised cache_size to a higher value i.e. PRAGMA cache_size=10000;


18



After reading this tutorial, I tried to implement it to my program.

I have 4-5 files that contain addresses. Each file has approx 30 million records. I am using the same configuration that you are suggesting but my number of INSERTs per second is way low (~10.000 records per sec).

Here is where your suggestion fails. You use a single transaction for all the records and a single insert with no errors/fails. Let's say that you are splitting each record into multiple inserts on different tables. What happens if the record is broken?

The ON CONFLICT command does not apply, cause if you have 10 elements in a record and you need each element inserted to a different table, if element 5 gets a CONSTRAINT error, then all previous 4 inserts need to go too.

So here is where the rollback comes. The only issue with the rollback is that you lose all your inserts and start from the top. How can you solve this?

My solution was to use multiple transactions. I begin and end a transaction every 10.000 records (Don't ask why that number, it was the fastest one I tested). I created an array sized 10.000 and insert the successful records there. When the error occurs, I do a rollback, begin a transaction, insert the records from my array, commit and then begin a new transaction after the broken record.

This solution helped me bypass the issues I have when dealing with files containing bad/duplicate records (I had almost 4% bad records).

The algorithm I created helped me reduce my process by 2 hours. Final loading process of file 1hr 30m which is still slow but not compared to the 4hrs that it initially took. I managed to speed the inserts from 10.000/s to ~14.000/s

If anyone has any other ideas on how to speed it up, I am open to suggestions.

UPDATE:

In Addition to my answer above, you should keep in mind that inserts per second depending on the hard drive you are using too. I tested it on 3 different PCs with different hard drives and got massive differences in times. PC1 (1hr 30m), PC2 (6hrs) PC3 (14hrs), so I started wondering why would that be.

After two weeks of research and checking multiple resources: Hard Drive, Ram, Cache, I found out that some settings on your hard drive can affect the I/O rate. By clicking properties on your desired output drive you can see two options in the general tab. Opt1: Compress this drive, Opt2: Allow files of this drive to have contents indexed.

By disabling these two options all 3 PCs now take approximately the same time to finish (1hr and 20 to 40min). If you encounter slow inserts check whether your hard drive is configured with these options. It will save you lots of time and headaches trying to find the solution


10



The answer to your question is that newer sqlite3 has improved performance, use that.

This answer Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly? by SqlAlchemy Orm Author has 100k inserts in 0.5 sec, and I have seen similar results with python-sqlite and SqlAlchemy. Which leads me to believe that performance has improved with sqlite3


4



There is great lecture form Paul Betts on how he made C# akavache so fast: https://www.youtube.com/watch?v=j7WnQhwBwqA

Maybe you can find some clues for you. It is too long to make short summary here


2