Привет!  Меня зовут Никита Галушко, я R&D-разработчик в Lamoda. Специально для Хабра я сделал вольный перевод интересной статьи “Postgres regex search over 10,000 GitHub repositories (using only a Macbook)”. 

Ее автор провел эксперимент: собрал датасет из 10 тысяч GitHub-репозиториев и проверил, насколько Postgres подходит для поиска по документам на одной машине — MacBook Pro, а также измерил скорость поиска и подобрал подходящую конфигурацию.

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

В этой статье я поделюсь результатами эксперимента по использованию Postgres для индексирования и последующего поиска среди 10 000 GitHub-репозиториев с использованием pg_trgm только на MacBook.

Это продолжение статьи “Postgres Trigram search learnings”, в которой я рассказывал о фишках и подводных камнях при использовании триграммных индексов в Postgres как альтернативе гугловому Zoekt. Я поделился итогами и точными шагами, чтобы вы могли воспроизвести результаты самостоятельно, если захотите.

Цели эксперимента

Я хочу получить эмпирические измерения, насколько Postgres подходит для поиска по документам с помощью regexp в качестве альтернативы гугловому Zoekt. А именно:

• сколько репозиториев можно проиндексировать на одном MacBook Pro 2019 года;

• насколько быстрым будет поиск по корпусу данных с помощью различных регулярных выражений;

• какая конфигурация Postgres 13 дает наилучший результат;

• какие посторонние эффекты нужно учитывать, чтобы рассматривать Postgres как бэкенд поисковой системы на регулярных выражениях;

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

Железо

Все тесты запускаются на MacBook Pro 2019 в конфигурации:

• 2.3 GHz 8-Core Intel Core i9,

• 16 GB 2667 MHz DDR4.

Во время выполнения тестов я почти не использовал другие приложения, так что  можно пренебречь их эффектом на потребление CPU и считать, что все CPU/RAM были отданы для Postgres.

Корпус

Я собрал списки 1000 лучших репозиториев с GitHub, отсортированных по количеству звездочек для C++, C#, CSS, Go, HTML, Java, JavaScript, MatLab, ObjC, Perl, PHP, Python, Ruby, Rust, Shell, Solidity, Swift, TypeScript, VB .NET и Zig. Всего получилось примерно 20 500 репозиториев. Их клонирование заняло примерно 14 часов с соединением до серверов GitHub около 100Мб/с.

Уменьшение размера датасета

Я обнаружил, что объем дискового пространства, которое требуется для git clone --depth 1 только лишь для 12 148 репозиториев, составляет примерно 412 Гб. Я решил использовать пару приемов для уменьшения размера набора данных примерно на 66%:

• Удаление директории .git дало снижение на 30% (412 Гб > 290 Гб для 12 148 репозиториев).

• Удаление файлов > 1 Мб дало снижение еще на 51% (290 Гб > 142 Гб для 12148 репозиториев. Кстати, GitHub не индексирует файлы размером больше 384 Кб).

Вставка данных

Вставка производилась конкурентно в Postgres со следующей схемой:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE IF NOT EXISTS files (

    id bigserial PRIMARY KEY,

    contents text NOT NULL,

    filepath text NOT NULL

);

Это длилось примерно 8 часов, а на диске Postgres занял 101 Гб.

Создание индекса

CREATE INDEX IF NOT EXISTS files_contents_trgm_idx ON files USING GIN (contents gin_trgm_ops);

• В первый раз я поймал OOM после 11 с половиной часов индексирования. Это связано с резким всплеском потребления памяти в самом конце индексирования. Это не было неожиданностью, поскольку я использовал достаточно агрессивную конфигурацию Postgres с максимальным размером WAL.

shared_buffers = 4GB > 2560MB

effective_cache_size = 12GB > 7680MB

maintenance_work_mem = 16GB > 1280MB

default_statistics_target = 100 > 500

work_mem = 5242kB > 16MB

min_wal_size = 50GB > 4GB

max_wal_size = 4GB > 16GB

max_parallel_workers_per_gather = 8 > 4

max_parallel_maintenance_workers = 8 > 4

• В третий и последний раз датасет был обрезан наполовину, а индексирование заняло примерно 22 часа. Я удалил половину файлов (с 19 441 820 файлов / 178 Гб сократилось до 9 720 910 файлов / 82 Гб). Конфигурация Postgres была аналогична той, что использовалась во второй попытке.

Потребление памяти

При первой попытке контейнер с Postgres использовал целых 12 Гб, если верить docker stats:

Во второй и третьей попытке использование памяти сильно упало — примерно до 1.6 Гб:

Потребление CPU

Построение GIN-индекса в Postgres похоже на однопоточное — такой результат получен при индексировании одной таблицы, в дальнейшем протестируем несколько таблиц.

В первой попытке использование CPU не превышало 156%:

Во второй попытке потребление CPU в среднем было 150–200%:

В третьей попытке потребление CPU было примерно таким же и составляло 150–200% с небольшим всплеском до 350% к концу:

Input/Output (Ввод/Вывод) 

Операции ввода-вывода для диска в процессе индексирования варьировались около 250 Мб/с для чтения (голубой цвет) и записи (красный). Бенчмарки диска, установленного в тестовый MacBook, показывают, что он способен достичь скорости примерно 860 Мб/с на чтение/запись с < 5% утилизацией CPU.

Примечание: Postgres вне контейнера показывает лучшую производительность при индексировании.

Дисковое пространство

postgres=# select count(filepath) from files;

  count

---------

 9720910

(1 row)

postgres=# select SUM(octet_length(contents)) from files;

     sum

-------------

 88123563320

(1 row)

До индексирования Postgres занимал 54 Гб:

$ du -sh .postgres/

 54G    .postgres/

После CREATE INDEX:

$ du -sh .postgres/

 73G    .postgres/

Таким образом, размер индекса для 82 Гб текста составляет 19 Гб или 23% от объема данных.

Время запуска базы данных

С эксплуатационной точки зрения стоит отметить, что при штатном завершении работы Postgres, время его запуска практически моментально: Postgres сразу начинает принимать соединения и загружает индекс по мере его использования. В противном случае потребуется около 10 минут на запуск, поскольку при старте происходит автоматическое восстановление.

Запросы

В общей сложности я выполнил 19 936 поисковых запросов к индексу. Я выбрал запросы, которые, по моим ожиданиям, дают разное покрытие триграммного индекса. То есть те запросы, триграммы которых с большей или меньшей вероятностью встречаются во многих файлах:

Производительность запросов

В общей сложности я выполнил 19 936 поисковых запросов к базе данных (линейно, не параллельно), которые завершились за следующее время:

Реальная производительность vs. планировщик

Приведенный ниже график показывает, что 79% запросов выполнялись менее чем за 3 секунды (ось Y в миллисекундах), в то время как планировщик запросов Postgres планировал их выполнение за 100–250 миллисекунд (ось X):

Если расширить график, чтобы в него помещались все запросы, то будет видно, насколько оставшийся 21% запросов выбивается от остальных. Обратите внимание, что небольшой блок точек в левом нижнем углу представляет собой диаграмму, показанную выше:

Потребление CPU/RAM

Следующие графики показывают:

• верхний — время запроса в миллисекундах,

• средний — процент использования ЦП (например, 801% означает, что используется 8 из 16 виртуальных ядер),

• нижний — потребление памяти в мегабайтах.

Из этого можно сделать следующие выводы:

• Значительное увеличение использования ресурсов к концу — это когда я начал выполнять запросы без LIMIT.

• Использование CPU не превышает 138% до всплеска в конце.

• Потребление памяти не превышает 42 Мб до всплеска в конце.

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

Исследование медленных запросов

График зависимости количества повторных проверок индекса (ось X) от времени выполнения (ось Y) показывает, что один из значимых аспектов замедления выполнения запроса — большее количество повторных проверок индекса:

И если взглянуть на EXPLAIN ANALYZE одного из таких запросов, можно подтвердить, что Parallel Bitmap Heap Scan работает медленно из-за Rows Removed by Index Recheck.

Партиционирование

Разделение на несколько небольших таблиц кажется очевидным подходом к тому, чтобы заставить pg_trgm использовать несколько ядер процессора. Я попробовал это сделать: взял тот же набор данных, разделил его на 200 таблиц и обнаружил многочисленные преимущества.

№1: Инкрементальное индексирование

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

№2: Параллельное индексирование

В отличие от первого подхода, показавшего, что при построении индекса использовались всего 1,5-2 виртуальных ядра CPU, с несколькими таблицами я смог утилизировать 8-9 ядер CPU:

№3: Индексирование на 84% быстрее

В отличие от первого подхода, где индексирование занимало 22 часа, параллельное индексирование завершилось всего за 3 часа 27 минут.

№4: Индексирование потребляет на 69% меньше RAM

В подходе с одной таблицей пиковое значение потребление памяти составляло 12 Гб. С такой же конфигурацией Postgres можно рассчитывать на пиковое значение потребления памяти всего в 3,7 Гб:

№5: Параллельная обработка запросов

Ранее загрузка процессора составляла всего 138% (1,3 виртуальных ядра CPU), а с разделением таблиц — 1600% во время запросов (16 виртуальных ядер CPU). Это показывает, что я выполняю работу полностью параллельно:

Аналогично дело обстоит и с потреблением памяти. Среднее значение потребления памяти увеличилось до 380 Мб в отличие от 42 Мб в подходе с одной таблицей:

№6: Производительность запросов

Я повторно выполнил тот же набор поисковых запросов, но меньше: 350 запросов вместо 19,9 тысяч, что, по-моему, достаточно представительная выборка. 

Разделение таблиц в целом ускорило выполнение запросов на 200–300% для более тяжелых запросов. Раньше они занимали 20–30 секунд, а теперь всего 7–5 секунд благодаря параллельному выполнению запросов (верхний график — до, нижний — после, оба в миллисекундах):

Также я сгруппировал запросы на основе LIMIT, указанного в запросе, и распределил их по временным интервалам — сколько запросов завершилось менее чем за 50 мс. Сравнение этих двух показателей показывает, что менее сложные запросы или запросы с меньшим количеством результатов пострадали незначительно, в то время как более крупные запросы получили существенный прирост в производительности:

Docker vs. нативный Postgres

Сначала я не думал о влиянии производительности при запуске Postgres в Docker. Thorsten Ball задавался вопросом о потенциальном источнике разницы в производительности IO-операций.

Все тесты, приведенные выше, были произведены на Postgres, запущенном в Docker с использованием драйвера osxfs, а не на экспериментальном драйвере FUSE gRPC. Я дополнительно прогнал те же тесты на собственном сервере Postgres и обнаружил следующие ключевые изменения.

Потребление CPU/RAM

Потребление CPU и памяти было похожим на то, что я наблюдал с Postgres в Docker.

Индексирование стало на 88% быстрее

Разбиение одной большой таблицы на несколько маленьких происходило следующим образом:

CREATE TABLE files_000 AS SELECT * FROM files WHERE id > 0 AND id < 50000;

CREATE TABLE files_001 AS SELECT * FROM files WHERE id > 50000 AND id < 100000;

...

Процесс разбиения был куда быстрее в нативно запущенном Postgres. На построение каждой таблицы уходило от 2 до 8 секунд, тогда как при запуске в Docker на это уходило 20–40 секунд.

Параллельное создание триграммных индексов CREATE INDEX IF NOT EXISTS files_000_contents_trgm_idx ON files USING GIN (contents gin_trgm_ops); также было быстрым — всего 23 минуты вместо примерно 3 часов в Docker.

Скорость обработки запросов увеличилась на 12–99%

Я запустил те же 350 запросов с прошлого теста и обнаружил несколько существенных улучшений:

• Запросы, которые ранее выполнялись очень медленно, улучшились на ~12%. Вероятно, это связано с операциями ввода-вывода, необходимыми при взаимодействии с 200 отдельными таблицами.

• Для средних по времени запросов прирост составил примерно 5%.

• Запросы, которые ранее были очень быстрыми (вероятнее всего, поиск осуществлялся по одной-двум таблицам), улучшились на 16–99%.

Исчерпывающие детали сравнения: негативные изменения —  это хорошо.

Выводы

• Директория .git , даже с клонированием --depth=1, составляет 30% от размера репозитория на диске (по крайней мере, в 10 000 лучших репозиториях GitHub).

• Файлы более 1 Мб (часто бинарные файлы) составляют 51% от объема данных на диске.

• Используя только MacBook, можно построить GIN-индексы Postgres по 10 000 репозиториев GitHub и выполнять большинство разумных запросов менее чем за 5 секунд. Дело пойдет гораздо быстрее при использовании более мощного железа.

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

• По умолчанию Postgres сжимает колонки с типом text, что в результате приводит к уменьшению размера на 23%.

• pg_trgm индексы занимают около 26% размера данных на диске. Таким образом, если индексируется 1 Гб необработанного текста, Postgres может потребовать примерно 827 Мб для хранения данных, а для индекса — около 279 Мб.

• Однозначно стоит разделять данные на несколько таблиц при использовании pg_trgm. Такой подход позволяет уменьшить время на построение индекса (в нашем случае — с 22 до 4 часов).

• Bind mount — довольно медленная технология за пределами хост-окружения Linux.