Главная » Хабрахабр » [Перевод] Как мы решили проблему с памятью в PostgreSQL, не добавив ни байта

[Перевод] Как мы решили проблему с памятью в PostgreSQL, не добавив ни байта

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

Мы быстро разобрались, что проблема в 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 МБ памяти.

Почему так?

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

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

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

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

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

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

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

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

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

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

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 МБ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Но мы пытались решить проблему с пространством на диске, к тому же задача выполняется ночью, так что время — не проблема. Хэширование задействует больше ЦП, поэтому запрос с хэшем работает медленнее. Мы пошли на компромисс, чтобы сэкономить память.

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


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

День Радио: патенты Маркони и Попова

Не имея большого желания присоединяться к более чем столетнему холивару Попов vs. Маркони, все же воспользуюсь грядущим очередным Днем Радио (который традиционно отмечается в нашей стране 7 мая), чтобы на примерах А.С. Попова и Г. Маркони напомнить почтеннейшей публике о ...

Обзор цифровой ручки MT6081 — ваши заметки сразу на компьютере

Вот чего, конечно, у «Даджета» не отнять, так это умения называть свои гаджеты странными символами: куда ни глянь, то MT1104, MT4017, MT… и так далее. Мы добрались до модели MT6081 — это довольно любопытная смарт-ручка, и мы вам расскажем, чем ...