c - русском - использование sqlite




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

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

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

https://github.com/rdpoor/CreateOrUpdate

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

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

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

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

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

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

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

Контроль"

Выполнение кода 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 = 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 секунд

Фантастика! Мы можем делать 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.


Если вы заботитесь только о чтении, несколько быстрее (но можете читать устаревшие данные), то версия должна считываться из нескольких подключений из нескольких потоков (соединение по потокам).

Сначала найдите элементы в таблице:

 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

Для нашего небольшого (200 мБ) дБ это сделало 50-75% ускорение (3.8.0.2 64-бит в Windows 7). Наши таблицы сильно ненормированы (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, поскольку вся база данных заблокирована при выполнении записи, и, хотя возможны несколько считывателей, записи будут заблокированы. Это несколько улучшилось с добавлением WAL в новых версиях SQLite.
  6. Воспользуйтесь преимуществами экономии места ... более мелкие базы данных идут быстрее. Например, если у вас есть пары ключевых значений, попробуйте сделать ключ INTEGER PRIMARY KEY если это возможно, что заменит подразумеваемый уникальный столбец строк в таблице.
  7. Если вы используете несколько потоков, вы можете попробовать использовать кеш разделяемой страницы , который позволит обмениваться загружаемыми страницами между потоками, что позволяет избежать дорогостоящих вызовов ввода-вывода.
  8. Не используйте !feof(file) !

Я также задал подобные вопросы here и here .


Ответ на ваш вопрос заключается в том, что новый sqlite3 имеет улучшенную производительность, используйте это.

Этот ответ Почему SQLAlchemy вставляет sqlite в 25 раз медленнее, чем использование sqlite3 напрямую? автор SqlAlchemy Orm Автор имеет 100k вставки за 0,5 секунды, и я видел аналогичные результаты с python-sqlite и SqlAlchemy. Это заставляет меня думать, что производительность улучшилась благодаря sqlite3


Прочитав этот учебник, я попытался реализовать его в своей программе.

У меня есть 4-5 файлов, содержащих адреса. Каждый файл содержит около 30 миллионов записей. Я использую ту же конфигурацию, которую вы предлагаете, но мой номер INSERT в секунду является низким (~ 10.000 записей в секунду).

Здесь ваше предложение терпит неудачу. Вы используете одну транзакцию для всех записей и одну вставку без ошибок / сбоев. Предположим, что вы разбиваете каждую запись на несколько вставок в разных таблицах. Что произойдет, если запись будет нарушена?

Команда ON CONFLICT не применяется, потому что если у вас есть 10 элементов в записи, и вам нужно, чтобы каждый элемент был вставлен в другую таблицу, если элемент 5 получает ошибку CONSTRAINT, тогда все предыдущие 4 вставки тоже должны идти.

Таким образом, здесь происходит откат. Единственная проблема с откатом заключается в том, что вы теряете все свои вставки и начинаете с вершины. Как вы можете это решить?

Мое решение заключалось в использовании нескольких транзакций. Я начинаю и заканчиваю транзакцию каждые 10.000 записей (не спрашивайте, почему это число, это был самый быстрый, который я тестировал). Я создал массив размером 10.000 и вставлял там успешные записи. Когда возникает ошибка, я делаю откат, начинаю транзакцию, вставляю записи из своего массива, фиксирую и начинаю новую транзакцию после разбитой записи.

Это решение помогло мне обойти проблемы, которые у меня возникают при работе с файлами, содержащими плохие / дублированные записи (у меня было почти 4% плохих записей).

Созданный мной алгоритм помог мне сократить мой процесс на 2 часа. Окончательный процесс загрузки файла 1hr 30m, который все еще медленный, но не сравнимый с 4hrs, который он первоначально взял. Мне удалось ускорить вставку с 10.000 / с до ~ 14.000 / с

Если у кого-то есть другие идеи о том, как ускорить это, я открыт для предложений.

ОБНОВЛЕНИЕ :

В дополнение к моему ответу выше, вы должны иметь в виду, что вставки в секунду зависят от жесткого диска, который вы используете. Я тестировал его на трех разных ПК с разными жесткими дисками и получал огромные различия во времени. PC1 (1 час 30 м), PC2 (6 часов) PC3 (14 часов), поэтому я начал задаваться вопросом, почему бы это было так.

После двух недель исследований и проверки нескольких ресурсов: Hard Drive, Ram, Cache, я узнал, что некоторые настройки на вашем жестком диске могут повлиять на скорость ввода-вывода. Нажимая свойства на желаемом выходном диске, вы можете увидеть два варианта на общей вкладке. Opt1: Сжатие этого диска, Opt2: Разрешить файлу этого диска индексировать содержимое.

Отключив эти два варианта, все 3 компьютера теперь занимают примерно одно и то же время для завершения (1 час и от 20 до 40 минут). Если вы сталкиваетесь с медленными вставками, проверьте, настроен ли ваш жесткий диск с этими параметрами. Это сэкономит вам много времени и головных болей, пытаясь найти решение


Я не получаю никакой выгоды от транзакций, пока не повышу cache_size до более высокого значения, т.е. PRAGMA cache_size=10000;





optimization