Spill-файлы

Дарья Барышева

Обзор

Spill-файлы (также известные как work-файлы) создаются на диске, если оперативной памяти становится недостаточно для хранения временных данных, формируемых при выполнении запросов к БД. Временные данные могут включать в себя хеши JOIN, результаты сортировки, перераспределенные таблицы и так далее. Директорию для хранения spill-файлов можно определять с помощью серверного конфигурационного параметра temp_tablespaces.

Поскольку spill-файлы хранятся на жестком диске, а скорость чтения/записи данных для дисков значительно ниже по сравнению с RAM — общее время выполнения запросов возрастает. Это может также привести к увеличению очередей на получение ресурсов со стороны других пользователей. В идеальном случае запросов, формирующих spill-файлы, быть не должно. Однако небольшое количество spill-файлов допускается. Фактически они обеспечивают выполнение запросов, несмотря на ограниченный размер RAM. Но стоит избегать запросов, формируюших избыточное число spill-файлов.

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

  • В системе присутствует skew данных. Как избежать этого, можно узнать в разделе Рекомендации по равномерному распределению данных.

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

  • Запрос не оптимизирован:

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

    • Используется сортировка с помощью ORDER BY. Промежуточные результаты сортировки изначально сохраняются в RAM. Чем больше объем данных, которые предстоит отсортировать, тем быстрее начнется генерация spill-файлов. В Greenplum не рекомендуется использовать сортировку в запросах, особенно для больших таблиц. Вместо этого лучше сортировать результаты непосредственно в приемнике данных (на стороне клиента).

    • Большой объем данных обрабатывается без использования фильтров в секции WHERE. При выполнении JOIN-запросов данные одной из таблиц помещаются в хеш-таблицу в оперативной памяти. Соответственно, если искомые строки этой таблицы не будут отфильтрованы — память очень быстро переполнится. Рекомендуется по возможности всегда использовать фильтры, особенно на даты и время. Даже если есть необходимость выборки за весь период — желательно сформировать несколько запросов за короткие временные промежутки, после чего можно объединить результаты с помощью UNION ALL. Такой подход эффективнее попытки обработать все данные целиком.

    • Хеш-таблица, создаваемая при выполнении JOIN-запросов, строится на основе большей из таблиц. При выполнении INNER JOIN хеш-таблица, которая упоминалась выше, строится по таблице с наименьшим объемом данных (чтобы минимизировать использование оперативной памяти). Однако, например, при использовании LEFT JOIN за основу для хеша всегда берется таблица, указанная справа, независимо от ее объема, что может привести к проблемам, если эта таблица в паре наибольшая. Поэтому внимательно следите за порядком таблиц в запросах. Также старайтесь минимизировать количество извлекаемых столбцов.

РЕКОМЕНДАЦИЯ

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

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

Анализ использования spill-файлов

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

  • gp_workfile_entries — показывает операторы (query operators), вызвавшие создание spill-файлов на диске на сегментах в текущий момент времени. Одна результирующая строка относится к одному оператору.

    Структура
    Столбец Описание

    datname

    Название базы данных

    pid

    Идентификатор серверного процесса (process ID)

    sess_id

    Идентификатор сессии (session ID)

    command_cnt

    Идентификатор запроса (command ID)

    usename

    Название роли

    query

    Запрос к БД, выполняемый в текущий момент времени

    segid

    Идентификатор сегмента. Ссылается на gp_segment_configuration.content. Дополнительную информацию можно получить в разделе Таблицы системного каталога → gp_segment_configuration статьи Таблицы

    slice

    Слайс (slice) плана выполнения запроса. Указывает на часть плана, выполняемую в текущий момент времени

    optype

    Тип оператора (query operator), вызвавшего создание spill-файла

    size

    Общий объем сгенерированных spill-файлов на сегменте (в байтах)

    numfiles

    Количество созданых spill-файлов

    prefix

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

    Пример:

    SELECT * FROM gp_toolkit.gp_workfile_entries;

    Результат:

     datname | pid  | sess_id | command_cnt | usename |                    query                    | segid | slice | optype |  size   | numfiles | prefix
---------+------+---------+-------------+---------+---------------------------------------------+-------+-------+--------+---------+----------+--------
 adb     | 5332 |      65 |          42 | gpadmin | SELECT DISTINCT(id1,id2,gen1,gen2) from t1; |    -1 |     0 | Sort   | 4292608 |        1 | Sort_1
(1 row)

  • gp_workfile_usage_per_query — показывает запросы (queries), вызвавшие создание spill-файлов на диске на сегментах в текущий момент времени. Одна результирующая строка относится к одному запросу.

    Structure
    Столбец Описание

    datname

    Название базы данных

    pid

    Идентификатор серверного процесса (process ID)

    sess_id

    Идентификатор сессии (session ID)

    command_cnt

    Идентификатор запроса (command ID)

    usename

    Название роли

    query

    Запрос к БД, выполняемый в текущий момент времени

    segid

    Идентификатор сегмента. Ссылается на gp_segment_configuration.content. Дополнительную информацию можно получить в разделе Таблицы системного каталога → gp_segment_configuration статьи Таблицы

    size

    Общий объем сгенерированных spill-файлов на сегменте (в байтах)

    numfiles

    Количество созданых spill-файлов

    Пример:

    SELECT * FROM gp_toolkit.gp_workfile_usage_per_query;

    Результат:

     datname | pid  | sess_id | command_cnt | usename |                    query                    | segid |   size   | numfiles
---------+------+---------+-------------+---------+---------------------------------------------+-------+----------+----------
 adb     | 5332 |      65 |          42 | gpadmin | SELECT DISTINCT(id1,id2,gen1,gen2) from t1; |    -1 | 20807680 |        1
(1 row)

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

    Структура
    Столбец Описание

    segid

    Идентификатор сегмента. Ссылается на gp_segment_configuration.content. Дополнительную информацию можно получить в разделе Таблицы системного каталога → gp_segment_configuration статьи Таблицы

    size

    Общий объем сгенерированных spill-файлов на сегменте (в байтах)

    numfiles

    Количество созданых spill-файлов

    Пример:

    SELECT * FROM gp_toolkit.gp_workfile_usage_per_segment;

    Результат:

     segid |   size   | numfiles
-------+----------+----------
     5 |        0 |
    13 |        0 |
    10 |        0 |
    11 |        0 |
     1 |        0 |
     6 |        0 |
    12 |        0 |
     0 |        0 |
     2 |        0 |
     7 |        0 |
    15 |        0 |
     4 |        0 |
     8 |        0 |
     3 |        0 |
    -1 | 31752192 |        1
    14 |        0 |
     9 |        0 |
(17 rows)
    ПРИМЕЧАНИЕ
    Приведенные выше представления могут просматривать все пользователи. Однако те из них, кто не является суперпользователем, могут получить информацию только по тем БД, которые доступны для их ролей.

Управление генерацией spill-файлов

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

Название Описание Значение по умолчанию Тип

gp_workfile_limit_files_per_query

Максимальное количество spill-файлов, допустимое для создания одним запросом на одном сегменте. При превышении лимита возникает следующая ошибка: ERROR: number of workfiles per query limit exceeded. Значение 0 соответствует неограниченному количеству spill-файлов

100000

master
session
reload

gp_workfile_limit_per_query

Максимальный размер дискового пространства, выделяемого под spill-файлы, которые генерируются одним запросом на одном сегменте (в КБ). При превышении лимита возникает следующая ошибка: ERROR: workfile per query size limit exceeded. Значение 0 означает отсутствие ограничений

25000000

master
session
reload

gp_workfile_limit_per_segment

Максимальный размер дискового пространства, выделяемого под spill-файлы, которые генерируются всеми запущенными запросами на одном сегменте (в КБ). При превышении лимита возникает следующая ошибка: ERROR: workfile per segment size limit exceeded. Значение 0 означает отсутствие ограничений

50000000

local
system
restart

gp_workfile_compression

Определяет, нужно ли использовать сжатие данных для spill-файлов, генерируемых операциями hash aggregation или hash join при выполнении запросов. Значение по умолчанию off означает отсутствие сжатия данных. Значение on используется для включения компрессии

off

master
session
reload
ВАЖНО

  • Значение параметра gp_workfile_limit_files_per_query, используемое по умолчанию (100000), достаточно для большинства запросов к БД. Прежде чем увеличивать значение этой величины, постарайтесь оптимизировать запросы, а также настройки распределения данных и потребления памяти.

  • Ограничение количества и размера формируемых spill-файлов предотвращает сбои в системе при выполнении ресурсоемких запросов. Но имейте в виду, что это приводит к прерыванию запросов при достижении указанных в параметрах лимитов.

  • Настройка сжатия spill-файлов с помощью параметра gp_workfile_compression может существенно повысить производительность запросов, генерирующих spill-файлы. Однако обратите внимание, что уменьшение числа операций ввода/вывода всегда происходит за счет повышенного потребления CPU.
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней