Подтвердить что ты не робот

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

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

Фон

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

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

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

Вопрос

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

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

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

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

Вопрос

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

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

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

UPDATE

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

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

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

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

Plot of SQLite insert speed and table size

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

Заключение

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

4b9b3361

ОТВЕТЫ

Ответ 1

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

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

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

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

Ответ 2

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

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

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

Схема:

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

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

  • i= 1..2,000,000
  • md5= md5 hex digest i

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

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

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

Ответ 3

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

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

Ответ 4

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

Ответ 5

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

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

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

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