Короткая история о «тяжелом» запросе и изящном решении проблемы

Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в ETL-задачах.

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

Для поиска повторяющихся дампов мы использовали этот запрос:

   id,
   MIN(id) OVER (PARTITION BY blob ORDER BY id)
FROM
   dumps

Запрос объединяет одинаковые дампы по BLOB-полю. С помощью функции окна мы получаем идентификатор первого появления каждого дампа. Потом этим запросом удаляем все повторяющиеся дампы.

Запрос выполнялся какое-то время, и, как видно из логов, кушал много памяти. На графике показано, как он каждую ночь забивал свободное пространство на диске:

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

  Buffers: shared hit=3916, temp read=3807 written=3816
  -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160)
    Sort Key: blob, id
    Sort Method: external merge  Disk: 30456kB
    Buffers: shared hit=3916, temp read=3807 written=3816
    -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160)
      Buffers: shared hit=3916

Execution time: 159.960 ms

Сортировка занимает много памяти. В плане выполнения из тестового набора данных сортировке требуется примерно 30 МБ памяти.

Почему так?

PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem ^[1]. Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.

Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.

Экономная сортировка

"Сколько сортировка съест – зависит от размера набора данных и ключа сортировки. Набор данных не уменьшишь, а вот размер ключа — можно.

За точку отсчета возьмем средний размер ключа сортировки:

   avg
----------
   780

Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 ^[2] (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит BLOB, хэшированный с помощью md5:

    avg
-----------
    36

Размер ключа, хэшированного через md5, — 36 байт. Хэшированный ключ весит всего 4% от исходного варианта.

Дальше мы запустили исходный запрос с хэшированным ключом:

      id,
      MIN(id) OVER (
            PARTITION BY md5(array_to_string(blob, '')
      ) ORDER BY id)
FROM
      dumps;

И план выполнения:

  Buffers: shared hit=3916
  -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160)
    Sort Key: (md5(array_to_string(blob, ''::text))), id
    Sort Method: quicksort  Memory: 4005kB
    Buffers: shared hit=3916
    -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160)
      Buffers: shared hit=3916

Execution time: 374.125 ms

С хэшированным ключом запрос потребляет всего 4 лишних мегабайта, то есть чуть больше 10% от прежних 30 МБ. Значит размер ключа сортировки сильно влияет на то, сколько памяти отъедает сортировка.

Дальше — больше

В этом примере мы хэшировали BLOB с помощью md5. Хэши, созданные с MD5, должны весить 16 байт. А у нас получилось больше:

md5_size
-------------
32

Наш хэш был ровно в два раза больше, ведь md5 выдает хэш в виде шестнадцатеричного текста.

В PostgreSQL можно использовать MD5 для хэширования с расширением pgcrypto ^[3]. pgcrypto создает MD5 типа bytea (в двоичном виде) ^[4]:

select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size

crypto_md5_size
---------------
20

Хэш все равно на 4 байта больше положенного. Просто тип bytea использует эти 4 байта, чтобы хранить длину значения, но мы этого так не оставим.

Оказывается, тип uuid в PostgreSQL весит ровно 16 байт и поддерживает любое произвольное значение, так что избавляемся от оставшихся четырех байтов:

uuid_size
---------------
16

Вот и все. 32 байта с md5 превращаются в 16 с uuid.

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

^[5]

Как видно из таблицы, исходный проблемный запрос весил 300 МБ (и будил нас среди ночи). С ключом uuid сортировке потребовалось всего 7 МБ.

Соображения вдогонку

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

Автор: nAbdullin

Источник ^[6]