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

Автор оригинала: Haki Benita
  • Перевод


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


Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в 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. Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.


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


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


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


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


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

Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит 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. pgcrypto создает MD5 типа bytea (в двоичном виде):


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.


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



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


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


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



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


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

Southbridge
273,00
Обеспечиваем стабильную работу серверов
Поделиться публикацией

Похожие публикации

Комментарии 12

    +1

    Как раз на днях вышел пост от Robert Haas на связанную тему: How Much maintenance_work_mem Do I Need?

      +2

      Вижу, что перевод, но все же спрошу.


      1. Не будет ли такой подход вызывать сильную фрагментацию данных и тормозов с VACUUM?
      2. Почему бы не добавить индексированное поле, в котором хранится md5, с ограничением уникальности? При вставке данных можно просто отбрасывать уже существующие (делать UPSERT), тогда и чистить не придется.
      3. Нет ли опасности удалить что-то нужное при коллизии хешей?
      • НЛО прилетело и опубликовало эту надпись здесь
          +3
          Ну, тогда будет скучно и неинтересно. Нельзя будет написать целую статью о том, как удалять то, что в нормальной БД вообще не должно появляться.
          +2
          По-моему нужно было остановиться на md5 и дальше тюнить саму базу.

          Подобные костыли до добра не доводят.
            0
            Я вообще не понимаю зачем тут pgcrypto, если postgres может сам сконвертировать:
            select md5('my data')::uuid

            Сравнить по скорости к сожалению не могу
            0
            Картинка с графиком места на диске не грузится из-за роскомпозора, стоит перезалить на habrastorage.org
            +4

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

              +1
              У вас очень странный адрес оригинала статьи.
              Вот ссылка на правильный
                0
                Наша – через NoBlockMe. Не работает?

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

              Самое читаемое