Открыть страницу поиска
Привет, Я DocuDroid!
Открыть страницу ИИ Помощника
Оценка ИИ поиска
Спасибо за оценку нашего ИИ поиска!
Мы будем признательны, если вы поделитесь своими впечатлениями, чтобы мы могли улучшить наш ИИ поиск для вас и других читателей.
GitHub

Управление spill-файлами

Павел Семёнов

Spill-файлы (или work-файлы) — это временные файлы, которые Greengage DB создает на диске, когда запросам требуется больше памяти, чем им выделено для выполнения. Они хранят промежуточные результаты соединений, сортировок, группировок и других операций, выходящих за пределы доступной памяти.

Spill-файлы помогают избежать ошибок out-of-memory (OOM) при выполнении SQL-запросов. Вместо того чтобы прервать выполнение запроса при нехватке памяти, Greengage DB переносит избыточные временные данные на диск и продолжает выполнение. Это повышает стабильность системы, особенно при одновременном исполнении большого количества запросов или при обработке большого объема данных одним запросом.

Однако использование spill-файлов может негативно повлиять на производительность кластера, так как операции чтения и записи на диск медленнее, чем работа с памятью. Кроме того, spill-файлы временно занимают дисковое пространство, особенно при выполнении запросов с соединениями больших таблиц, сортировкой или агрегацией больших объемов данных.

Spill-файлы являются временными. Greengage DB автоматически удаляет их после завершения запроса или транзакции. Обычно администраторам не требуется управлять этими файлами вручную. При этом важно понимать, когда и почему создаются spill-файлы — это полезно для настройки запросов, оптимизации конфигурации и контроля за использованием ресурсов.

Когда появляются spill-файлы

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

Типичные причины появления spill-файлов:

  • Недостаточное выделение памяти в конфигурации кластера. Объем памяти, доступный запросу, задается параметрами конфигурации, такими как statement_mem, max_statement_mem и work_mem. Убедитесь, что их значения соответствуют предполагаемой нагрузке и ресурсам кластера.

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

  • Неоптимальные запросы, например:

    • Соединения больших таблиц, особенно хеш-соединения, которые используют хеш-таблицы в памяти. Если объем данных превышает лимит памяти, части хеш-таблицы или входных данных записываются на диск.

    • Агрегации без фильтров (выражения WHERE), в которых обрабатывается весь набор данных без предварительной выборки.

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

    • Выражение ORDER BY на больших выборках. Когда используется ORDER BY, Greengage DB нужно место для хранения результата сортировки. Сначала они размещаются в памяти, но при недостатке памяти записываются на диск.

  • Устаревшая статистика. Если статистика таблиц отсутствует или неактуальна, оптимизатор может неверно оценить объем данных, что приведет к созданию spill-файлов.

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

Настройка использования spill-файлов

Greengage DB позволяет настраивать следующие параметры работы со spill-файлами:

  • расположение

  • лимиты

  • сжатие

Расположение

Расположение spill-файлов Greengage DB определяется назначенными табличными пространствами. Spill-файлы создаются в каталоге pgsql_tmp/ в указанном табличном пространстве. Например, при использовании табличного пространства по умолчанию путь к spill-файлам имеет вид $MASTER_DATA_DIRECTORY/base/pgsql_tmp/ .

Чтобы поменять расположение spill-файлов, используйте следующие параметры конфигурации:

  • temp_tablespaces — задает общие табличные пространства для всех временных объектов в базе данных, включая spill-файлы.

  • temp_spill_files_tablespaces — указывает отдельные табличные пространства специально для spill-файлов. Если этот параметр задан, он имеет приоритет над temp_tablespaces для размещения spill-файлов.

Чтобы настроить отдельное табличное пространство для spill-файлов, создайте его и выполните следующие команды:

$ gpconfig -c temp_spill_files_tablespaces -v '<spill_tablespace>' --masteronly
$ gpstop -u

где <spill_tablespace> — имя отдельного табличного пространства.

Вы также можете указать несколько табличных пространств через запятую:

$ gpconfig -c temp_spill_files_tablespaces -v '<spill_tablespace1>,...,<spill_tablespaceN>' --masteronly
$ gpstop -u

Подробности смотрите в разделе Настройка табличных пространств по умолчанию.

Именование spill-файлов

Названия spill-файлов формируются по следующему шаблону:

pgsql_tmp_<operation>_<PID>.<N>

где:

  • <operation> — тип операции, породившей файл.

  • <PID> — идентификатор процесса, породившего файл.

  • <N> — числовой идентификатор spill-файла в рамках одного процесса.

Например, два spill-файла, порожденные одной операцией, могут называться pgsql_tmp_HashAggregate_2_18001.100 и pgsql_tmp_HashAggregate_2_18001.101.

Лимиты

Максимальное количество одновременно существующих spill-файлов задается параметром gp_workfile_limit_files_per_query. Он определяет, сколько spill-файлов может создать один запрос на каждом сегменте. Значение по умолчанию — 100000 — достаточно для большинства случаев. Если этого лимита недостаточно для выполнения запросов, рекомендуется оптимизировать их, чтобы уменьшить использование spill-файлов. Значение 0 отключает лимит.

Для управления объемом дискового пространства, используемого под spill-файлы, доступны следующие параметры:

  • gp_workfile_limit_per_query — максимальный объем (в килобайтах), который один запрос может использовать на каждом сегменте.

  • gp_workfile_limit_per_segment — общий объем, доступный для spill-файлов всех выполняемых запросов на каждом сегменте.

По умолчанию оба параметра равны 0, что означает отсутствие ограничений. Чтобы задать лимиты на использование диска spill-файлами, выполните команды следующего вида:

$ gpconfig -c gp_workfile_limit_per_query -v '2097152'
$ gpconfig -c gp_workfile_limit_per_segment -v '16777216'
$ gpstop -r
ПРИМЕЧАНИЕ

Обратите внимание, что gp_workfile_limit_per_segment задается на сегментах и требует перезапуска кластера. Параметр gp_workfile_limit_per_query можно изменить с помощью перезагрузки конфигурации.

Порог создания spill-файлов

При использовании ресурсных групп для управления ресурсами кластера можно настроить порог использования памяти (в процентах). Когда транзакция потребляет этот процент доступной памяти группы, создаются spill-файлы.

Порог использования памяти можно задать на двух уровнях:

  • Ресурсная группа — атрибут ресурсной группы MEMORY_SPILL_RATIO задает порог по умолчанию для всех транзакций в этой группе.

  • Сессия — параметр конфигурации memory_spill_ratio позволяет переопределить значение по умолчанию для конкретной сессии или транзакции.

Подробнее о пороге создания spill-файлов см. в разделе Создание spill-файлов: MEMORY_SPILL_RATIO.

Сжатие

Чтобы уменьшить размер spill-файлов, можно включить их сжатие с помощью параметра gp_workfile_compression типа boolean:

$ gpconfig -c gp_workfile_compression -v ON --masteronly
$ gpstop -u

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

ПРИМЕЧАНИЕ

Сжатие применяется к spill-файлам, которые порождаются операциями хеш-агрегирования (hash aggregate) и хеш-соединения (hash join). Остальные spill-файлы не сжимаются.

Сжатие использует дополнительную память, которая не учитывается в лимите statement_mem. Объем этой памяти ограничен параметром gp_workfile_compression_overhead_limit, который по умолчанию равен 2GB. Если одновременно выполняется много запросов со сжатием spill-файлов и достигается этот лимит, Greengage DB перестает сжимать новые spill-файлы. Чтобы изменить лимит дополнительной памяти для сжатия, задайте новое значение gp_workfile_compression_overhead_limit или установите его в 0, чтобы отключить ограничение.

Проверка spill-файлов

Spill-файлы являются временными — они существуют только во время выполнения запроса. Чтобы определить, создает ли запрос spill-файлы, необходимо просматривать соответствующие представления системного каталога во время выполнения запроса. После завершения запроса файлы и связанные с ними статистики автоматически удаляются.

Существует два способа проверить использование spill-файлов:

  1. Отслеживать представления системного каталога gp_workfile_* во время выполнения запроса. Эти представления отображают информацию об активных spill-файлах в момент просмотра.

  2. Использовать SQL-команду EXPLAIN ANALYZE. Ее вывод содержит сведения о spill-файлах, созданных при выполнении запроса. Это позволяет оценить объем данных, сброшенных на диск, и выявить возможные проблемы в плане выполнения запроса.

Чтобы воспроизвести примеры из этого раздела, выполните следующие шаги:

  1. Подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql.

  2. Создайте новую базу данных и подключитесь к ней:

    CREATE DATABASE marketplace;
\c marketplace

  3. Создайте тестовую таблицу:

    CREATE TABLE items
(
    item_id SERIAL PRIMARY KEY,
    category TEXT,
    price NUMERIC,
    count NUMERIC,
    value NUMERIC,
    description TEXT
)
    DISTRIBUTED BY (item_id);

  4. Заполните таблицу большим объемом данных, например, одним миллионом строк:

    INSERT INTO items (category, price, count, value, description)
SELECT
    CASE WHEN id % 5 = 0 THEN 'Category A'
         WHEN id % 5 = 1 THEN 'Category B'
         WHEN id % 5 = 2 THEN 'Category C'
         WHEN id % 5 = 3 THEN 'Category D'
         ELSE 'Category E' END,
    RANDOM() * 1000,
    RANDOM() * 1000,
    RANDOM() * 1000,
    md5(RANDOM()::text)
FROM generate_series(1, 1000000) id;

  5. Установите низкий лимит памяти для выполнения запроса, который будет исчерпан при выполнении ресурсоемкого запроса, например, 5MB. При достижении этого лимита будут создаваться spill-файлы:

    SET statement_mem = '5MB';

В такой конфигурации следующий SQL-запрос использует всю доступную память и начнет создавать spill-файлы:

SELECT DISTINCT (item_id, price, count), value
FROM items
GROUP BY item_id
ORDER BY value;

Чтобы проверить spill-файлы, создаваемые этим запросом, откройте другое подключение psql к той же базе данных и выполните запросы к системным представлениям, перечисленным ниже во время выполнения запроса. После завершения запроса spill-файлы удаляются, и представления не содержат данных.

ВНИМАНИЕ

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

Представления gp_toolkit.gp_workfile_*

Следующие представления административной схемы gp_toolkit содержат информацию об активных spill-файлах. Они полезны для мониторинга и оптимизации использования ресурсов:

  • gp_toolkit.gp_workfile_usage_per_query — показывает использование spill-файлов по запросам. Содержит агрегированную информацию о количестве и размере spill-файлов, созданных каждым запросом. Представление содержит такие метаданные, как размер файлов, их количество, соответствующий SQL-запрос и другую информацию. Это позволяет определить запросы с высоким потреблением ресурсов.

    Пример:

    SELECT datname, usename, query, segid, size, numfiles
FROM gp_toolkit.gp_workfile_usage_per_query;

    Пример вывода:

       datname   | usename |                                           query                                            | segid |   size   | numfiles
-------------+---------+--------------------------------------------------------------------------------------------+-------+----------+----------
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     0 | 18317312 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     3 | 18317312 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     1 | 18219008 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     2 | 18350080 |        1
(4 rows)

  • gp_toolkit.gp_workfile_entries — предоставляет подробную информацию об активных spill-файлах в кластере. В отличие от представления gp_workfile_usage_per_query, которое агрегирует данные на уровне запроса, в gp_toolkit.gp_workfile_entries каждая строка соответствует одному оператору (столбец optype), создавшему spill-файл. Такой уровень детализации помогает при глубоком анализе запросов и их настройке.

    Пример:

    SELECT datname, usename, query, segid, optype, size, numfiles
FROM gp_toolkit.gp_workfile_entries;

    Пример вывода:

       datname   | usename |                                           query                                            | segid | optype |   size   | numfiles
-------------+---------+--------------------------------------------------------------------------------------------+-------+--------+----------+----------
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     2 | Sort   | 18382848 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     3 | Sort   | 18284544 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     0 | Sort   | 18350080 |        1
 marketplace | gpadmin | SELECT DISTINCT (item_id, price, count), value FROM items GROUP BY item_id ORDER BY value; |     1 | Sort   | 18284544 |        1
(4 rows)

  • gp_toolkit.gp_workfile_usage_per_segment — отображает использование spill-файлов на уровне сегментов. Позволяет оценить, как spill-файлы распределяются по кластеру. Это представление помогает выявить дисбаланс нагрузки и возможные узкие места в производительности.

    Пример:

    SELECT * FROM gp_toolkit.gp_workfile_usage_per_segment;

    Пример вывода:

 segid |   size   | numfiles
-------+----------+----------
     1 | 18251776 |        1
     0 | 18350080 |        1
     2 | 18350080 |        1
     3 | 18284544 |        1
    -1 |        0 |
(5 rows)
ПРИМЕЧАНИЕ

Представления gp_toolkit.gp_workfile_entries, gp_toolkit.gp_workfile_usage_per_query и gp_toolkit.gp_workfile_usage_per_segment доступны всем пользователям. Однако для обычных пользователей (не суперпользователей) отображаются только данные по базам, к которым у них есть доступ.

Команда EXPLAIN ANALYZE

В тестовых средах вы можете проверить, создает ли запрос spill-файлы, с помощью команды EXPLAIN ANALYZE. Эта команда строит план запроса и выполняет его, а затем выводит подробную статистику выполнения, включая информацию об использовании памяти и наличии spill-файлов.

Например, следующая команда выводит план запроса и сведения о spill-файлах:

EXPLAIN ANALYZE
    SELECT DISTINCT (item_id, price, count), value
    FROM items
    GROUP BY item_id
    ORDER BY value;

Результат:

                                                                              QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
 Unique  (cost=2665.04..2875.04 rows=16800 width=68) (actual time=1630.148..4123.738 rows=1000000 loops=1)
   Group Key: value, (ROW(item_id, price, count))
   ->  Gather Motion 4:1  (slice2; segments: 4)  (cost=2665.04..2791.04 rows=16800 width=68) (actual time=1630.147..3624.408 rows=1000000 loops=1)
         Merge Key: value, (ROW(item_id, price, count))
         ->  Unique  (cost=2665.04..2791.04 rows=4200 width=68) (actual time=1613.354..1982.876 rows=250461 loops=1)
               Group Key: value, (ROW(item_id, price, count))
               ->  Sort  (cost=2665.04..2707.04 rows=4200 width=68) (actual time=1613.352..1884.099 rows=250461 loops=1)
                     Sort Key (Distinct): value, (ROW(item_id, price, count))
                     Sort Method:  external merge  Disk: 71488kB
                     ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=478.00..1486.00 rows=4200 width=68) (actual time=287.676..560.936 rows=250461 lo
ops=1)
                           Hash Key: value
                           ->  HashAggregate  (cost=478.00..814.00 rows=4200 width=68) (actual time=286.894..509.883 rows=250659 loops=1)
                                 Group Key: item_id
                                 Extra Text: (seg1)   249548 groups total in 32 batches; 1 overflows; 249548 spill groups.
 (seg1)   Hash chain length 3.9 avg, 14 max, using 72165 of 73728 buckets; total 1 expansions.

                                 ->  Seq Scan on items  (cost=0.00..436.00 rows=4200 width=100) (actual time=0.012..130.936 rows=250659 loops=1)
 Planning time: 0.139 ms
   (slice0)    Executor memory: 884863K bytes.
 * (slice1)    Executor memory: 3671K bytes avg x 4 workers, 3671K bytes max (seg0).  Work_mem: 2511K bytes max, 19201K bytes wanted.
 * (slice2)    Executor memory: 4753K bytes avg x 4 workers, 4753K bytes max (seg0).  Work_mem: 4698K bytes max, 73909K bytes wanted.
 Memory used:  5120kB
 Memory wanted:  148316kB
 Optimizer: Postgres query optimizer
 Execution time: 4246.067 ms
(25 rows)

Если во время выполнения запроса создавались spill-файлы, это отображается в строках со звездочкой ( * (slice1) и * (slice2)) после плана запроса. Параметры Memory used и Memory wanted показывают, сколько памяти фактически использовал запрос и сколько ему требовалось. Обычно при создании spill-файлов значение Memory wanted превышает Memory used, которое, в свою очередь, зависит от statement_mem (лимита памяти на запрос) и memory_spill_ratio (порога использования памяти).

Минимизация использования spill-файлов

Хотя spill-файлы позволяют выполнять запросы при нехватке памяти, они могут существенно снижать производительность и занимать дисковое пространство. Поэтому по возможности рекомендуется сводить к минимуму их использование.

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

  • Поддерживайте статистику в актуальном состоянии. Регулярно обновляйте статистику таблиц и индексов. Актуальная статистика помогает планировщику запросов выбирать более эффективные планы выполнения. Узнайте больше в статье Сбор статистики с помощью ANALYZE.

  • Оптимизируйте SQL-запросы. Проверьте и при необходимости упростите логику запросов для повышения производительности:

    • Используйте фильтры. Применяйте условия фильтрации как можно раньше, чтобы сократить объем обрабатываемых в дальнейшем данных.

    • Используйте правильный порядок таблиц в соединениях. При выполнении LEFT JOIN Greengage DB строит хеш-таблицу на основе таблицы справа в выражении. Если справа указана большая таблица, это может привести к нехватке памяти. По возможности размещайте большие таблицы в левой части выражения LEFT JOIN. Также ограничивайте число возвращаемых полей, чтобы сократить объем обрабатываемых данных.

    • Избегайте ORDER BY. В общем случае использование ORDER BY в СУБД Greengage не рекомендуется. Если сортировка необходима, старайтесь уменьшить объем данных или выполняйте сортировку на стороне клиента после возвращения результата запроса.

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

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

  • Выделяйте достаточно ресурсов. Если предполагаемая рабочая нагрузка включает запросы, создающие spill-файлы и не поддающиеся оптимизации, выделите больше ресурсов для их эффективного выполнения:

    • Увеличьте лимиты памяти, заданные параметрами конфигурации statement_mem и max_statement_mem.

    • Настройте доступные ресурсы с помощью ресурсных групп или ресурсных очередей для более эффективного управления памятью и CPU.

Чтобы избежать сбоев из-за чрезмерного количества или объема spill-файлов, настройте параметры, ограничивающие их использование: gp_workfile_limit_files_per_query, gp_workfile_limit_per_query и gp_workfile_limit_per_segment.

Предыдущая
Удаление устаревших строк с помощью VACUUM
Следующая
Базы данных