Скорость вставки SQLite замедляется по мере увеличения количества записей из-за индекса

Оригинальный вопрос

Задний план

Хорошо известно, что SQLite нуждается в тонкой настройке для достижения скорости вставки порядка 50 тыс. Вставок / с. Здесь много вопросов относительно медленной скорости вставки и большого количества советов и тестов.

Существуют также утверждения, что SQLite может обрабатывать большие объемы данных , а отчеты с более 50 ГБ не создают проблем с правильными настройками.

Я следил за советами здесь и в других местах, чтобы достичь этих скоростей, и я доволен вставками 35k-45k. Проблема заключается в том, что все эталонные тесты демонстрируют только быстрые скорости вставки с записью <1 м. Я вижу, что скорость вставки, по-видимому, обратно пропорциональна размеру таблицы .

вопрос

В моем случае использования требуется хранить от 500 до 1 [x_id, y_id, z_id] кортежей ( [x_id, y_id, z_id] ) в течение нескольких лет (1 м строк / день) в таблице ссылок. Значения представляют собой целые идентификаторы от 1 до 2 000 000. На z_id есть один индекс.

Производительность отлично подходит для первых 10-миллиметровых строк, ~ 35 тыс. Вставок / с, но к тому времени, когда таблица имеет ~ 20 м строк, производительность начинает страдать. Теперь я вижу около 100 вставок / с.

Размер таблицы не особо большой. С 20-метровыми рядами размер на диске составляет около 500 МБ.

Проект написан на Perl.

Вопрос

Является ли это реальностью больших таблиц в SQLite или есть какие-то секреты для поддержания высоких ставок вставки для таблиц с> 10 м строк?

Известные обходные пути, которые я хотел бы избежать, если это возможно

  • Отбросьте индекс, добавьте записи и переиндексируйте : это нормально в качестве обходного пути, но не работает, когда БД все еще необходимо использовать во время обновлений. Это не сработает, чтобы сделать базу данных полностью недоступной для x минут / дня
  • Разбейте таблицу на более мелкие субтитры / файлы : это будет работать в краткосрочной перспективе, и я уже экспериментировал с ним. Проблема в том, что мне нужно иметь возможность извлекать данные из всей истории при запросе, что означает, что в конечном итоге я удалю ограничение 62 привязки таблицы. Прикрепление, сбор результатов в таблице temp и отключение сотен раз за запрос, похоже, много работы и накладных расходов, но я попробую, если нет других альтернатив.
  • Установите SQLITE_FCNTL_CHUNK_SIZE : я не знаю C (?!), Поэтому я бы предпочел не узнать его, чтобы это сделать. Однако я не вижу способа установить этот параметр с помощью Perl.

ОБНОВИТЬ

Следуя предложению Тима о том, что индекс вызывал все более медленное время вставки, несмотря на утверждения SQLite о том, что он способен обрабатывать большие наборы данных, я провел сравнительное сравнение со следующими настройками:

  • вставленные строки: 14 миллионов
  • фиксировать размер партии: 50 000 записей
  • cache_size pragma: 10 000
  • page_size pragma: 4 096
  • temp_store прагма: память
  • journal_mode прагма: удалить
  • synchronous прагма: выкл.

В моем проекте, как и в приведенных ниже результатах теста, создается временная таблица на основе файлов и используется встроенная поддержка SQLite для импорта CSV-данных. Временная таблица затем прикрепляется к принимающей базе данных, а insert-select из 50 000 строк вставляются с помощью инструкции insert-select . Поэтому время вставки не отражает время загрузки файла в базу данных , а скорее скорость вставки таблицы в таблицу . Учитывая время импорта CSV, скорость будет снижена на 25-50% (очень приблизительная оценка, для импорта CSV-данных не требуется много времени).

Очевидно, что индекс приводит к замедлению скорости вставки при увеличении размера таблицы.

График скорости вставки SQLite и размера таблицы

Из приведенных выше данных совершенно ясно, что правильный ответ может быть отнесен к ответу Тима, а не утверждениям, которые SQLite просто не может с этим справиться. Очевидно, что он может обрабатывать большие наборы данных, если индексирование этого набора данных не является частью вашего прецедента. Я использую SQLite только для этого, как бэкэнд для системы ведения журнала, какое-то время, которое не нужно индексировать, поэтому я был очень удивлен замедлением, которое я испытал.

Вывод

Если кто-то захочет хранить большой объем данных с помощью SQLite и проиндексировать его, использование ответвлений может быть ответом. В конце концов я решил использовать первые три символа hashа MD5 в столбце z чтобы определить назначение одной из 4096 баз данных. Поскольку мой вариант использования носит преимущественно архивный характер, схема не изменится, и запросы никогда не потребуют шаринговой ходьбы. Ограничение по размеру базы данных ограничено, так как чрезвычайно старые данные будут уменьшены и в конечном итоге будут отброшены, поэтому эта комбинация настроек sharding, pragma и даже некоторая нормализация дает мне хороший баланс, который, основываясь на сравнительном анализе выше, поддерживает скорость вставки не менее 10 тыс. вставок в секунду.

5 Solutions collect form web for “Скорость вставки SQLite замедляется по мере увеличения количества записей из-за индекса”

Если вам нужно найти определенный z_id и x_ids и y_ids, связанные с ним (в отличие от быстрого выбора диапазона z_ids), вы можете посмотреть в неиндексированную хеш-таблицу вложенных реляционных db, которая позволит вам мгновенно найти ваш путь к определенному z_id, чтобы получить его y_ids и x_ids – без служебных данных индексирования и сопутствующей ухудшенной производительности во время вставок по мере роста индекса. Чтобы избежать столкновения со случайными столкновениями, выберите алгоритм хеширования ключей, который наносит наибольший вес на цифры z_id с наибольшим изменением (вправо-взвешенным).

PS База данных, использующая b-дерево, может сначала отображаться быстрее, чем db, которая использует линейное хеширование, скажем, но производительность вставки будет оставаться на уровне линейного хеша, так как производительность на b-дереве начинает ухудшаться.

PPS Чтобы ответить на вопрос kawing-chiu: основная особенность, которая здесь важна, заключается в том, что такая firebase database основана на так называемых «разреженных» таблицах, в которых физическое местоположение записи определяется алгоритмом hashирования, который принимает ключ записи в качестве входного. Такой подход позволяет напрямую искать местоположение записи в таблице без использования индекса . Поскольку нет необходимости проходить индексы или индексы перебалансировки, времена вставки остаются постоянными, так как таблица становится более густонаseleniumной. С помощью b-дерева, напротив, время вставки ухудшается по мере роста дерева индексов. Приложения OLTP с большим количеством параллельных вставок могут извлечь выгоду из такого подхода с разреженной таблицей. Записи разбросаны по всей таблице. Недостаток записей, разбросанных по «тундре» разреженной таблицы, заключается в том, что сбор больших наборов записей, имеющих общую ценность, например почтовый код, может быть медленнее. Хэшированный подход с разреженной таблицей оптимизирован для вставки и извлечения отдельных записей, а также для извлечения сетей связанных записей, а не для больших наборов записей, имеющих общее значение поля.

Вложенная реляционная firebase database – это та, которая допускает кортежи внутри столбца строки.

Большой вопрос и очень интересное продолжение!

Я хотел бы сделать краткое замечание: вы упомянули, что разбиение таблицы на более мелкие субтитры / файлы и их последующее присоединение не является вариантом, потому что вы быстро достигнете жесткого предела из 62 прикрепленных баз данных. Хотя это совершенно верно, я не думаю, что вы рассмотрели вариант «средний путь»: очертание данных в несколько таблиц, но продолжайте использовать одну и ту же единую базу данных (файл).


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

Схема:

 CREATE TABLE IF NOT EXISTS "test_$i" ( "i" integer NOT NULL, "md5" text(32) NOT NULL ); 

Данные – 2 миллиона строк:

  • i = 1,2000000
  • md5 = md5-шестнадцатеричный дайджест i

Каждая транзакция = 50 000 INSERT .


Базы данных: 1; Таблицы: 1; Индексы: 0

 0..50000 records inserted in 1.87 seconds 50000..100000 records inserted in 1.92 seconds 100000..150000 records inserted in 1.97 seconds 150000..200000 records inserted in 1.99 seconds 200000..250000 records inserted in 2.19 seconds 250000..300000 records inserted in 1.94 seconds 300000..350000 records inserted in 1.94 seconds 350000..400000 records inserted in 1.94 seconds 400000..450000 records inserted in 1.94 seconds 450000..500000 records inserted in 2.50 seconds 500000..550000 records inserted in 1.94 seconds 550000..600000 records inserted in 1.94 seconds 600000..650000 records inserted in 1.93 seconds 650000..700000 records inserted in 1.94 seconds 700000..750000 records inserted in 1.94 seconds 750000..800000 records inserted in 1.94 seconds 800000..850000 records inserted in 1.93 seconds 850000..900000 records inserted in 1.95 seconds 900000..950000 records inserted in 1.94 seconds 950000..1000000 records inserted in 1.94 seconds 1000000..1050000 records inserted in 1.95 seconds 1050000..1100000 records inserted in 1.95 seconds 1100000..1150000 records inserted in 1.95 seconds 1150000..1200000 records inserted in 1.95 seconds 1200000..1250000 records inserted in 1.96 seconds 1250000..1300000 records inserted in 1.98 seconds 1300000..1350000 records inserted in 1.95 seconds 1350000..1400000 records inserted in 1.95 seconds 1400000..1450000 records inserted in 1.95 seconds 1450000..1500000 records inserted in 1.95 seconds 1500000..1550000 records inserted in 1.95 seconds 1550000..1600000 records inserted in 1.95 seconds 1600000..1650000 records inserted in 1.95 seconds 1650000..1700000 records inserted in 1.96 seconds 1700000..1750000 records inserted in 1.95 seconds 1750000..1800000 records inserted in 1.95 seconds 1800000..1850000 records inserted in 1.94 seconds 1850000..1900000 records inserted in 1.95 seconds 1900000..1950000 records inserted in 1.95 seconds 1950000..2000000 records inserted in 1.95 seconds 

Размер файла базы данных: 89.2 MiB.


Базы данных: 1; Таблицы: 1; Индексы: 1 ( md5 )

 0..50000 records inserted in 2.90 seconds 50000..100000 records inserted in 11.64 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.62 seconds 200000..250000 records inserted in 11.28 seconds 250000..300000 records inserted in 12.09 seconds 300000..350000 records inserted in 10.60 seconds 350000..400000 records inserted in 12.25 seconds 400000..450000 records inserted in 13.83 seconds 450000..500000 records inserted in 14.48 seconds 500000..550000 records inserted in 11.08 seconds 550000..600000 records inserted in 10.72 seconds 600000..650000 records inserted in 14.99 seconds 650000..700000 records inserted in 10.85 seconds 700000..750000 records inserted in 11.25 seconds 750000..800000 records inserted in 17.68 seconds 800000..850000 records inserted in 14.44 seconds 850000..900000 records inserted in 19.46 seconds 900000..950000 records inserted in 16.41 seconds 950000..1000000 records inserted in 22.41 seconds 1000000..1050000 records inserted in 24.68 seconds 1050000..1100000 records inserted in 28.12 seconds 1100000..1150000 records inserted in 26.85 seconds 1150000..1200000 records inserted in 28.57 seconds 1200000..1250000 records inserted in 29.17 seconds 1250000..1300000 records inserted in 36.99 seconds 1300000..1350000 records inserted in 30.66 seconds 1350000..1400000 records inserted in 32.06 seconds 1400000..1450000 records inserted in 33.14 seconds 1450000..1500000 records inserted in 47.74 seconds 1500000..1550000 records inserted in 34.51 seconds 1550000..1600000 records inserted in 39.16 seconds 1600000..1650000 records inserted in 37.69 seconds 1650000..1700000 records inserted in 37.82 seconds 1700000..1750000 records inserted in 41.43 seconds 1750000..1800000 records inserted in 49.58 seconds 1800000..1850000 records inserted in 44.08 seconds 1850000..1900000 records inserted in 57.17 seconds 1900000..1950000 records inserted in 50.04 seconds 1950000..2000000 records inserted in 42.15 seconds 

Размер файла базы данных: 181.1 MiB.


Базы данных: 1; Таблицы: 20 (по одному на 100 000 записей); Индексы: 1 ( md5 )

 0..50000 records inserted in 2.91 seconds 50000..100000 records inserted in 10.30 seconds 100000..150000 records inserted in 10.85 seconds 150000..200000 records inserted in 10.45 seconds 200000..250000 records inserted in 10.11 seconds 250000..300000 records inserted in 11.04 seconds 300000..350000 records inserted in 10.25 seconds 350000..400000 records inserted in 10.36 seconds 400000..450000 records inserted in 11.48 seconds 450000..500000 records inserted in 10.97 seconds 500000..550000 records inserted in 10.86 seconds 550000..600000 records inserted in 10.35 seconds 600000..650000 records inserted in 10.77 seconds 650000..700000 records inserted in 10.62 seconds 700000..750000 records inserted in 10.57 seconds 750000..800000 records inserted in 11.13 seconds 800000..850000 records inserted in 10.44 seconds 850000..900000 records inserted in 10.40 seconds 900000..950000 records inserted in 10.70 seconds 950000..1000000 records inserted in 10.53 seconds 1000000..1050000 records inserted in 10.98 seconds 1050000..1100000 records inserted in 11.56 seconds 1100000..1150000 records inserted in 10.66 seconds 1150000..1200000 records inserted in 10.38 seconds 1200000..1250000 records inserted in 10.24 seconds 1250000..1300000 records inserted in 10.80 seconds 1300000..1350000 records inserted in 10.85 seconds 1350000..1400000 records inserted in 10.46 seconds 1400000..1450000 records inserted in 10.25 seconds 1450000..1500000 records inserted in 10.98 seconds 1500000..1550000 records inserted in 10.15 seconds 1550000..1600000 records inserted in 11.81 seconds 1600000..1650000 records inserted in 10.80 seconds 1650000..1700000 records inserted in 11.06 seconds 1700000..1750000 records inserted in 10.24 seconds 1750000..1800000 records inserted in 10.57 seconds 1800000..1850000 records inserted in 11.54 seconds 1850000..1900000 records inserted in 10.80 seconds 1900000..1950000 records inserted in 11.07 seconds 1950000..2000000 records inserted in 13.27 seconds 

Размер файла базы данных: 180.1 MiB.


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

К сожалению, я бы сказал, что это ограничение больших таблиц в SQLite. Он не предназначен для работы с крупномасштабными или крупномасштабными наборами данных. Хотя я понимаю, что это может значительно увеличить сложность проекта, вам, вероятно, лучше исследовать более сложные решения для баз данных, соответствующие вашим потребностям.

Из всего, что вы связываете, похоже, что размер таблицы для доступа к скорости – это прямой компромисс. Не может быть и того, и другого.

В моем проекте я не мог очертить базу данных, поскольку она была проиндексирована в разных столбцах. Чтобы ускорить вставку, я поместил базу данных во время создания на / dev / shm (= linux ramdisk), а затем скопировал ее на локальный диск. Очевидно, что это работает только для базы данных write-once, read-many.

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

Когда у нас много строк в одной таблице, и тогда столкновение хеш-индекса с индексированным столбцом будет происходить чаще. Это означает, что движок Sqlite должен вычислять значение hashа два или три раза, а может быть, даже четыре раза, чтобы получить другое значение hash-функции.

Поэтому я предполагаю, что это основная причина замедления SQLite, когда таблица имеет много строк.

Этот момент может объяснить, почему использование осколков может избежать этой проблемы. Кто настоящий эксперт в домене SQLite, чтобы подтвердить или опровергнуть мою точку зрения?

  • Самый быстрый способ удаления дубликатов документов в mongodb
  • Использование индекса, используя временный, с помощью filesort - как это исправить?
  • Встроенные функции в C #?
  • Является ли RVO (Оптимизация возвращаемого значения) применимым ко всем объектам?
  • Как я могу ускорить свою программу Perl?
  • __builtin_prefetch, сколько он читает?
  • Константы и оптимизация компилятора в C ++
  • Замена 32-битного счетчика циклов на 64-битные значения приводит к сумасшедшим отклонениям производительности
  • Значение аббревиатуры SSO в контексте std :: string
  • Что отсутствует / не оптимально в этой реализации memcpy?
  • Утечка памяти в .NET
  • Давайте будем гением компьютера.