5 стадий принятия необходимости изучения «плана запроса» или почему может долго выполняться запрос

Всем привет! Меня зовут Виктор, я работаю в Компании БФТ-Холдинг руководителем группы разработки. В этой статье разберем подходы и рекомендации по выявлению и устранению проблем с производительностью в системе базы данных Greenplum. Материал будет особенно полезен начинающим разработчикам Greenplum, которые пока не имеют достаточного опыта «чтения» плана запроса.

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

• 1.Проверка состояния системы (сегментов)

• 2.Проверка активных сеансов (рабочая нагрузка)

• 3.Проверка блокировок (конфликты)

• 4.Проверка очереди ресурсной группы

• 5. Проверка статистики

1. Проверка состояния системы (сегментов)

Таблица системного каталога gp_segment_configuration может позволить выявить перечень неисправных сегментов. В системе базы данных Greenplum произойдет снижение производительности при «падении» одного и более экземпляров сегмента, поскольку другие хосты должны взять на себя обязанности по обработке отключенных сегментов.

dbid	smallint	Уникальный идентификатор экземпляра сегмента (или координатора).
content	smallint	Идентификатор контента для экземпляра сегмента. Экземпляр основного сегмента и соответствующее ему зеркало всегда будут иметь один и тот же идентификатор контента. Для сегмента значение от 0 до N -1, где N — количество основных сегментов в системе. Для координатора значение всегда равно -1.
role	char	Роль, под которой в данный момент работает сегмент. Значения: p(первичные) или m(зеркальные).
preferred_role	char	Роль, изначально назначенная сегменту во время инициализации. Значения: p(первичные) или m(зеркальные).
mode	char	Статус синхронизации экземпляра сегмента с его зеркальной копией. Значения: s (Синхронизировано) или n (Не синхронизировано).
status	char	Статус ошибки экземпляра сегмента. Значения u(запущен) или d (остановлен).
port	integer	TCP-порт, который использует процесс прослушивателя сервера базы данных.
hostname	text	Имя хоста узла сегмента.
address	text	Имя хоста, используемое для доступа к определенному экземпляру сегмента на хосте сегмента. Это значение может быть таким же, как и hostname в системах, в которых не настроены имена хостов для каждого интерфейса.
datadir	text	Каталог данных экземпляра сегмента.

Колонка "status", показывает статус сегментов. Значения "u" сегмент «поднят» в рабочем состоянии, "d" «упавший» (нерабочий) сегмент.

Например:

SELECT * 
FROM gp_segment_configuration 
WHERE status = 'd';

dbid	content	role	preferred_role	mode	status	port	hostname	address	replication_port	san_mounts
16	6	m	m	s	d	1213	dm1	dm1-2	1213

Таблица gp_segment_configuration может так же показать, какие сегменты перешли из режима «Зеркало» в «Основной» режим, и соответственно у какой-то сегмент у нас не зеркалируется

Например:

SElECT * 
FROM gp_segment_configuration 
WHERE preferred_role = 'm' 
  AND role = 'p';

dbid	content	role	preferred_role	mode	status	port	hostname	address	replication_port	san_mounts
11	1	p	m	s	u	1213	dm3	dm3-1	1213

2. Проверка активных сеансов (рабочая нагрузка)

В представлении системного каталога pg_stat_activity отображается одна строка для каждого серверного процесса. Он показывает:

datid	oid	OID базы данных.
datname	name	Имя базы данных.
pid	integer	Идентификатор процесса этого серверного процесса.
sess_id	integer	Идентификатор сессии.
usesysid	oid	OID пользователя, вошедшего в этот бэкэнд.
usename	name	Имя пользователя, вошедшего в этот бэкэнд.
application_name	text	Имя приложения, подключенного к этому бэкэнду.
client_addr	inet	IP-адрес клиента, подключенного к этому серверу. Если это поле имеет значение NULL, это указывает либо на то, что клиент подключен через сокет Unix на сервере, либо на то, что это внутренний процесс, такой как автоочистка.
client_hostname	text	Имя хоста подключенного клиента, полученное при обратном DNS-поиске client_addr. Это поле будет ненулевым только для IP-соединений и только тогда, когда включен параметр log_hostname.
client_port	integer	Номер TCP-порта, который клиент использует для связи с этим сервером, или -1, если используется сокет Unix.
backend_start	timestamptz	Серверный процесс времени запущен.
xact_start	timestamptz	Время начала транзакции.
query_start	timestamptz	Время начала выполнения запроса.
state_change	timestampz	Время последнего state изменения.
waiting	boolean	Истина, если ожидание блокировки, ложь, если не ожидание.
state	text	Текущее общее состояние этого серверного процесса. Возможные значения: - active: Серверная часть выполняет запрос. - idle: серверная часть ожидает новой команды клиента. - idle in transaction: серверная часть находится в транзакции, но в данный момент не выполняет запрос. - idle in transaction (aborted): Это состояние похоже на состояние ожидания в транзакции, за исключением того, что один из операторов транзакции вызвал ошибку. - fastpath function call: серверная часть выполняет функцию быстрого пути. - disabled: об этом состоянии сообщается, если track_activitiesоно деактивировано в этом бэкэнде.
query	text	Текст последнего запроса этого серверного компонента. Если значение поле state = ‘active’, в этом поле отображается текущий выполняющийся запрос. Во всех остальных состояниях отображается последний выполненный запрос.
waiting_reason	text	Причина ожидания серверного процесса. Значение может быть: блокировка, репликация или перегруппировка.
rsgid	oid	OID группы ресурсов или 0.
rsgname	text	Имя группы ресурсов или unknown.
rsgqueueduration	interval	Для запроса в очереди — общее время нахождения запроса в очереди.

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

Например:

SELECT *
FROM pg_stat_activity
WHERE state = 'active';

datid

datname

pid

sess_id

usesysid

usename

application_name

client_addr

client_hostname

client_port

backend_start

xact_start

query_start

state_change

waiting

state

backend_xid

backend_xmin

query

waiting_reason

rsgid

rsgname

rsgqueueduration

18674

db

9425

548439

16901

zbx_monitor

PostgreSQL JDBC Driver

191.3.2.21

NULL

74935

2024-01-09 14:11:38.548379

2024-01-09 14:17:12.110398

2024-01-09 14:17:12.112985

2024-01-09 14:17:12.112986

false

active

NULL

19740556

INSERT INTO ...

NULL

16809

monitor_group

NULL

18674

db

139355

330858

16901

zbx_monitor

PostgreSQL JDBC Driver

193.3.8.53

NULL

23008

2024-01-08 23:11:46.415536

2024-01-08 23:12:05.627627

2024-01-09 14:17:12.092429

2024-01-09 14:17:12.092439

false

active

NULL

19740556

SELECT NOW() ...

NULL

16809

monitor_group

NULL

18674

db

47938

225594

16901

zbx_monitor

PostgreSQL JDBC Driver

192.2.3.13

NULL

53116

2024-01-08 17:25:33.355383

2024-01-08 17:25:33.554610

2024-01-09 14:17:12.112527

2024-01-09 14:17:12.112528

false

active

19740556

19740556

SELECT * FROM ...

NULL

16809

monitor_group

NULL

3. Проверка блокировок (конфликты)

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

Представление pg_locks может показать конфликты между сеансами клиентов базы данных. pg_locks обеспечивает глобальное представление всех блокировок в системе базы данных, а не только тех, которые относятся к текущей базе данных. Можно соединить столбец отношения с ним, pg_class.oid чтобы идентифицировать заблокированные отношения (например, таблицы), но это работает правильно только для отношений в текущей базе данных. Можно присоединиться к pid столбцу, чтобы pg_stat_activity.pid просмотреть дополнительную информацию об удержании сеанса или ожидании блокировки.

Например:

SELECT 
 blocked_locks.pid AS blocked_pid,
 blocked_activity.usename AS blocked_user,
 blocking_locks.pid AS blocking_pid,
 blocking_activity.usename AS blocking_user,
 blocking_activity.state AS blocking_state,
 blocked_activity.query AS blocked_statement,
 blocking_activity.query AS current_statement_in_blocking_process,
 blocked_locks.locktype AS blocked_locktype, 
 blocking_locks.locktype AS blocking_locktype
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid != blocked_locks.pid 
JOIN pg_catalog.pg_stat_activity blocking_activity 
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

blocked_pid	int4	Идентификатор серверного процесса (PID, Process ID), удерживающего или ожидающего эту блокировку.
blocked_user	name	Имя пользователя, удерживающего или ожидающего эту блокировку.
blocking_pid	int4	Идентификатор серверного процесса (PID, Process ID) установивший блокировку.
blocking_user	name	Имя пользователя установивший блокировку.
blocking_state	text	Текущее общее состояние этого серверного процесса. Возможные значения: - active: Серверная часть выполняет запрос. - idle: серверная часть ожидает новой команды клиента. - idle in transaction: серверная часть находится в транзакции, но в данный момент не выполняет запрос. - idle in transaction (aborted): Это состояние похоже на состояние ожидания в транзакции, за исключением того, что один из операторов транзакции вызвал ошибку. - fastpath function call: серверная часть выполняет функцию быстрого пути. - disabled: об этом состоянии сообщается, если track_activitiesоно деактивировано в этом бэкэнде.
blocked_statement	text	Текст заблокированного запроса этого серверного компонента.
current_statement_in_blocking_process	text	Текст блокирующего запроса этого серверного компонента.
blocking_locktype	text	Тип блокируемого объекта: relation (отношение), extend (расширение отношения), frozenid (замороженный идентификатор), page (страница), tuple (кортеж), transactionid (идентификатор транзакции), virtualxid (виртуальный идентификатор), object (объект), userlock  (пользовательская блокировка) или advisory (рекомендательная)

Более подробно про блокировки можно почитать в документации https://postgrespro.ru/docs/postgresql/12/explicit-locking, https://habr.com/ru/companies/postgrespro/articles/462877/

4. Проверка очереди ресурсной группы

Чтобы просмотреть назначения групп ресурсов и ролей, можно выполнить следующий запрос к таблицам системного каталога pg_roles и pg_resgroup.

Например:

SELECT rolname, rsgname
FROM pg_roles
JOIN pg_resgroup ON pg_roles.rolresgroup = pg_resgroup.oid
WHERE rolname = 'zbx_monitor';

rolname	rsgname
zbx_monitor	monitor_group

Если вы используете группы ресурсов, ожидающие запросы также будут отображаться в pg_locks . Чтобы узнать, сколько запросов ожидает выполнения в группе ресурсов, используйте представление системного каталога gp_resgroup_status . 

rsgname	name	Имя группы ресурсов.
groupid	oid	Идентификатор группы ресурсов.
num_running	integer	Количество транзакций, выполняемых в данный момент в группе ресурсов.
num_queueing	integer	Количество транзакций, находящихся в очереди в настоящее время для группы ресурсов.
num_queued	integer	Общее количество транзакций в очереди для группы ресурсов с момента последнего запуска кластера базы данных Greenplum, исключая файлы num_queueing.
num_executed	integer	Общее количество транзакций, выполненных в группе ресурсов с момента последнего запуска кластера базы данных Greenplum, исключая файлы num_running.
total_queue_duration	interval	Общее время, в течение которого любая транзакция находилась в очереди с момента последнего запуска кластера базы данных Greenplum.
cpu_usage	json	Набор пар ключ-значение. Для каждого экземпляра сегмента (ключа) значение представляет собой использование ядра ЦП для каждого экземпляра сегмента в реальном времени группой ресурсов. Значение представляет собой сумму процентов (в виде десятичного значения) ядер ЦП, которые используются группой ресурсов для экземпляра сегмента.
memory_usage	json	Использование памяти в реальном времени группой ресурсов на узле каждого сегмента базы данных Greenplum.

Например:

SELECT * 
  FROM gp_toolkit.gp_resgroup_status 
 WHERE rsgname = 'monitor_group';

rsgname	groupid	num_running	num_queueing	num_queued	num_executed	total_queue_duration	cpu_usage	memory_usage
monitor_group	16833	0	0	6	934487	0 years 0 mons 0 days 0 hours 4 mins 6.638049 secs	{"-1":0,01, "0":0,31, "1":0,31}	0:{"used":0, "available":76, "quota_used":-1, "quota_available":60, "shared_used":0, "shared_available":16}

Поле cpu_usage представляет собой строку «ключ : значение» в формате JSON, которая определяет для каждой группы ресурсов использование ядра ЦП экземпляра каждого сегмента. Ключом является идентификатор сегмента. Значение представляет собой сумму процентов (в виде десятичного значения) ядер ЦП, используемых группой ресурсов экземпляра сегмента на узле сегмента; максимальное значение — 1,00. Общее использование ЦП всеми экземплярами сегментов, работающими на хосте, не должно превышать gp_resource_group_cpu_limit.

Например:

{"-1":0,01, "0":0,31, "1":0,31}

В этом примере сегмент 0 и сегмент 1 работают на одном хосте; их использование процессора одинаково.

Поле memory_usage также представляет собой строку «ключ : значение» в формате JSON. Содержимое строки различается в зависимости от типа группы ресурсов. Для каждой группы ресурсов, которую вы назначаете роли (аудитор памяти по умолчанию vmtracker), эта строка определяет используемые и доступные выделения квот фиксированной и общей памяти в каждом сегменте. Ключом является идентификатор сегмента. Значения представляют собой значения памяти, отображаемые в МБ. В следующем примере показаны выходные данные столбца Memory_usage для одного сегмента группы ресурсов, которую вы назначаете роли:

Например:

"0":{"used":0, "available":76, "quota_used":-1, "quota_available":60, "shared_used":0, "shared_available":16}

Аналогично, если используется очереди ресурсов, запросы, ожидающие в очереди, также отображаются в pg_locks . Чтобы узнать, сколько запросов ожидает выполнения из очереди ресурсов, используйте представление системного каталога gp_resqueue_status .

queueid	oid	Идентификатор очереди ресурсов.
rsqname	name	Имя очереди ресурсов.
rsqcountlimit	real	Порог активного запроса очереди ресурсов. Значение -1 означает отсутствие ограничений.
rsqcountvalue	real	Количество активных слотов запросов, используемых в настоящее время в очереди ресурсов
rsqcostlimit	real	Порог стоимости запроса очереди ресурсов. Значение -1 означает отсутствие ограничений.
rsqcostvalue	real	Общая стоимость всех операторов, находящихся в настоящее время в очереди ресурсов.
rsqmemorylimit	real	Ограничение памяти для очереди ресурсов.
rsqmemoryvalue	real	Общий объем памяти, используемый всеми операторами, находящимися в настоящее время в очереди ресурсов.
rsqwaiters	integer	Количество операторов, ожидающих в данный момент в очереди ресурсов.
rsqholders	integer	Количество операторов, выполняющихся в настоящее время в системе из этой очереди ресурсов.

Например:

SELECT * 
 FROM gp_toolkit.gp_resqueue_status;

5. Проверка статистики

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

Статистика, используемая оптимизатором, рассчитывается и сохраняется в системном каталоге командой ANALYZE. Существует три способа инициировать операцию анализа:

• Можно запустить команду ANALYZE напрямую

ANALYZE table1;

• Можно запустить утилиту управления analysisdb вне базы данных, в командной строке.

• Операция автоматического анализа может быть запущена, когда операции DML выполняются над таблицами, не имеющими статистики, или когда операция DML изменяет количество строк, превышающее указанное пороговое значение.

Команда VACUUM ANALYZE — это еще один способ инициировать операцию анализа, но ее использование не рекомендуется, поскольку очистка и анализ — это разные операции с разными целями.

Вычисление статистики требует времени и ресурсов, поэтому база данных Greenplum создает оценки путем расчета статистики на выборках из больших таблиц. В большинстве случаев настройки по умолчанию предоставляют информацию, необходимую для создания правильных планов выполнения запросов. Если полученная статистика не дает оптимальных планов выполнения запросов, можно настроить параметры конфигурации для получения более точной статистики, увеличив размер выборки или степень детализации статистики, сохраняемой в системном каталоге. Создание более точной статистики требует затрат на ЦП и хранилище и может не привести к улучшению планов, поэтому важно просматривать планы объяснения и тестировать производительность запросов, чтобы убедиться, что дополнительные затраты, связанные со статистикой, приводят к повышению производительности запросов.

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

schemaname	name	Имя схемы, содержащей таблицу.
tablename	name	Имя таблицы.
attname	name	Имя столбца, описываемого этой строкой.
inherited	bool	Если это правда, эта строка включает дочерние столбцы наследования, а не только значения в указанной таблице.
null_frac	real	Доля записей столбца, имеющих значение NULL.
avg_width	integer	Средняя ширина записей столбца в байтах.
n_distinct	real	Если больше нуля, примерное количество различных значений в столбце. Если меньше нуля, это отрицательное число различных значений, деленное на количество строк. (Отрицаемая форма используется, когда ANALYZE полагает, что количество различных значений, вероятно, будет увеличиваться по мере роста таблицы; положительная форма используется, когда кажется, что столбец имеет фиксированное количество возможных значений.) Например, -1указывает на уникальный столбец, в котором количество различных значений равно количеству строк.
most_common_vals	любой массив	Список наиболее распространенных значений в столбце. (Нельзя, если ни одно из значений не является более распространенным, чем другие.)
most_common_freqs	real[]	Список частот наиболее распространенных значений, т. е. количество вхождений каждого из них, разделенное на общее количество строк.
histogram_bounds	anyarray	Список значений, который делит значения столбца на группы примерно равной численности. Значения в most_common_vals, если они присутствуют, исключаются из расчета гистограммы. (Этот столбец имеет значение NULL, если тип данных столбца не имеет <оператора или если most_common_valsсписок учитывает всю совокупность.)
correlation	real	Статистическая корреляция между физическим порядком строк и логическим порядком значений столбцов. Это значение варьируется от -1 до +1. Когда значение близко к -1 или +1, сканирование индекса столбца будет оценено как более дешевое, чем когда оно близко к нулю, из-за уменьшения произвольного доступа к диску. (Этот столбец имеет значение NULL, если тип данных столбца не имеет <оператора.)
most_common_elems	anyarray	Список значений элементов, отличных от NULL, которые чаще всего встречаются в значениях столбца.
most_common_elem_freqs	real[]	Список частот наиболее распространенных значений элементов, т. е. доля строк, содержащих хотя бы один экземпляр данного значения. Два или три дополнительных значения следуют за частотами каждого элемента; это минимальная и максимальная из предыдущих частот для каждого элемента и, необязательно, частота нулевых элементов.
element_count_histogram	real[]	Гистограмма количества различных значений ненулевых элементов в значениях столбца, за которыми следует среднее количество различных ненулевых элементов.

 

Максимальное количество записей в полях массива можно контролировать по каждому столбцу с помощью команды ALTER TABLE SET STATISTICS или глобально, задав параметр конфигурации времени выполнения default_statistics_target.

Если всё выше сказанное не дало результатов, то требуется понимание плана запроса

EXPLAIN ANALYSE 
SELECT *
FROM ...

 Если статья была вам полезна, пожалуйста, оставьте комментарий и поделитесь с коллегами?

Спасибо за внимание!