Вопрос по sqlite3, database, optimization, insert, sqlite – Скорость вставки SQLite замедляется по мере увеличения числа записей из-за индекса

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

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

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

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

вопрос

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

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

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

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

Вопрос

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

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

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

вставленные строки:14 миллионовпринять размер пакета:50000 записейcache_size прагма:10000page_size прагма:+4096temp_store прагма:Памятьjournal_mode прагма:удалятьsynchronous прагма:от

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

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

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

Заключение

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

Как вы используете транзакции? Насколько велик вы настроили кеш страниц? CL.
@veryhungrymike: В этом случае вы можете заглянуть в неиндексированную вложенную реляционную таблицу хеш-таблиц, которая позволит вам мгновенно найти путь к строкам z_id, а y_ids и x_ids связаны с конкретным z_id без затрат на индексацию и сопутствующее снижение производительности во время вставок по мере роста индекса. Тот, который придает больший вес цифрам z_id с наибольшим разбросом (справа) даст вам хорошее распределение. Tim
Выходя за рамки вашего конкретного вопроса: могу ли я спросить, используете ли вы индекс z_id для поиска отдельных записей или выбора диапазонов? Tim
@Tim это таблица ссылок, поэтому элементы выбираются изtable_z затем связано сtable_y а такжеt,able_x черезz_id через эту таблицу. Итак, чтобы ответить на ваш вопрос, он помогает найти отдельные записи. user918938
@CL.cache_size установлен в10000, Все вставки находятся в одной транзакции с фиксацией каждые 50 000 вставок. Эти значения были выбраны после всестороннего тестирования, чтобы найти самую быструю скорость вставки (35k-45k вставок / с), но эта скорость падает по мере того, как число записей увеличивается примерно за 10 м. user918938

Ваш Ответ

5   ответов
12

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

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

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

Вложенная реляционная база данных - это та, которая разрешает кортежив столбец строки.

Прочитав больше о линейном хешировании, я думаю, что это все еще не решение для действительно больших данных вообще. Потому что либо у вас есть меньшая хеш-карта с большим количеством коллизий, либо ваша хеш-карта становится почти такой же большой, как ваши данные. Автор lmdbпредпочитает б дерево, слишком. kawing-chiu
Никакая "карта хеша" не требуется; Файловое пространство делится на адресуемые страницы. И я когда-нибудь говорил, что LH - это решение для «действительно больших данных в целом»? Как я пояснил, LH оптимизирован для приложений OLTP, где отдельные записи вставляются и извлекаются. LH отлично подходит для поиска досье, медицинской карты или профиля пользователя в социальной сети. Для такого поиска индексы не требуются, и, тем не менее, поиск происходит мгновенно. LH не оптимизирован для аналитики «больших данных», где извлекаются подмножества данных, имеющие некоторые общие поля. Tim
Спасибо за дальнейшее объяснение. Но большинство известных мне БД используют либо b-tree, либо LSM-tree. Не могли бы вы назвать несколько БД, которые используют линейное хеширование? kawing-chiu
1

так как она проиндексирована в разных столбцах. Чтобы ускорить вставку, я поместил базу данных во время создания в / dev / shm (= linux ramdisk) и затем скопировал ее на локальный диск. Это, очевидно, хорошо работает только для базы данных с однократной записью и чтением.

2

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

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

Ссылка, которую вы предоставили, упоминает размеры базы данных порядка терабайтов, что здесь не так. Размер базы данных в настоящее время составляет около 500 МБ. В долгосрочной перспективе я планирую сбросить исторические данные, когда я достигну 20-50 ГБ. user918938
Пожалуйста, посмотрите на приведенный выше тест, который показывает, что вы на самом делеМожно иметь большой стола также быстрые вставки. Что тыне может иметь, однако, индекс на этой таблице. user918938
7

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

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

Схема:
CREATE TABLE IF NOT EXISTS "test_$i"
(
    "i" integer NOT NULL,
    "md5" text(32) NOT NULL
);
Данные - 2 миллиона строк:i = 1..2.000.000md5 = MD5 шестнадцатеричный дайджестi

Каждая транзакция = 50000INSERTs.

Базы данных: 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 МБ.

Базы данных: 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 МБ.

Базы данных: 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 МиБ.

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

В конечном итоге я разделил данные (на 4096 таблиц) по одной таблице на файл. Я читал в нескольких местах, что одна таблица на базу данных - лучший подход к SQLite, и в моем начальном сравнительном анализе, прежде чем я обнаружил вышеизложенное, казалось, что наличие таблиц в одной базе данных было частью проблемы. В конце я делаю около 100 тыс. Записей в памяти, а затем присоединяю / отсоединяю базы данных для записи по мере необходимости. Я не осознавал, что накладные расходы на присоединение / отсоединение были довольно незначительными, если вы можете сделать несколько тысяч записей для каждого. Я попробую ваше предложение на следующей итерации, хотя! user918938
@veryhungrymike: есть пара преимуществ, когда у вас есть 1 таблица на файл, но это также более непрактично. Узким местом являются индексы, и, поскольку они привязаны к таблицам, я вполне уверен, что вы получите почти одинаковое улучшение с одним или несколькими файлами, при условии что каждая отдельная таблица будет короткой. Alix Axel
@veryhungrymike: О, что касается количества записей, я знаю, что чем больше они, тем медленнее они становятся (у меня просто не было много времени, чтобы ждать несколько часов до окончания теста). В любом случае, у каждого из тестов есть 2 миллиона строк, и вы можете заметить, что второй тест начинает работать значительно медленнее при 800 тыс. Строк, а третий остается последовательным до конца. Это должно что-то сказать. Alix Axel
Может быть, мне нужно изменить описание, потому что весь смысл в том, как поддерживать скорость со многими (то есть миллионов) записей. Это тип использования в журнале, когда данные постоянно добавляются, но когда-то они никогда не меняются, поэтому первостепенной задачей является поддержание скорости для 10 или 100 миллионов строк. user918938
Кроме того, 2M строк недостаточно, чтобы увидеть падение скорости. Вам нужно будет сравниться с минимум 10M, как показано на графике выше. В конечном итоге я буду иметь дело с миллиардом строк. user918938
0

что столкновение хеш-значения индекса замедляет скорость вставки.

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

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

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

Похожие вопросы