Перейти к содержимому

12 Занятие 12: Производительность / Анализ производительности

Исходная страница в нативном Markdown: Производительность / Анализ производительности
Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: pgpro_12_perfanalysis.html Archive: PGPRO-handouts-16.zip Страниц PDF: 26

Page 1: Производительность / Анализ производительности

Авторские права

© Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

  • Расширение pgpro_stats
  • Агрегированная статистика
  • Дополнительные метрики
  • Статистика очистки
  • Статистика аннулирования кеша
  • Трассировка сеансов

Page 3: Расширение pgpro_stats

Статистика запросов

  • затраченные ресурсы, планы и события ожиданий
  • Основные настройки
    • pgpro_stats.max = 5000
    • предел количества запросов
    • pgpro_stats.track = top
    • верхний уровень или все (all)
    • pgpro_stats.query_sample_rate = 1.0
    • размер выборки (доля запросов)
    • pgpro_stats.enable_profile = true
    • профиль ожиданий
    • pgpro_stats.profile_period = 10 ms
    • период семплирования
  • Представление pgpro_stats_statements

Комментарии преподавателя

Расширение pgpro_stats собирает статистику по выполняемым операторам. По сравнению с pg_stat_statements добавлен сбор данных о планах запросов и событиях ожидания для каждого типа запроса. Расширения pgpro_stats и pg_stat_statements несовместимы.

Статистика доступна через представление pgpro_stats_statements, максимальное число строк в нем определяет параметр pgpro_stats.max. Значение по умолчанию — 5000.

Параметр pgpro_stats.track определяет, будут отслеживаться лишь только непосредственно выполненные команды SQL (значение top — по умолчанию), или также и вложенные операторы (значение all).

Для снижения нагрузки можно уменьшить размер выборки, заданный параметром pgpro_stats.query_sample_rate. Тогда для вычисления статистики будет случайно выбираться заданная доля запросов.

Семплинг событий ожидания выполняется с периодичностью раз в pgpro_stats.profile_period единиц времени (10 мс по умолчанию), если установлен параметр pgpro_stats.enable_profile.

Статистика ожиданий доступна в поле pgpro_stats_statements.wait_stats типа jsonb. Все показатели представлены в миллисекундах и кратны параметру конфигурации pgpro_stats.profile_period.

Также собирается информация о затраченных ресурсах с точки зрения операционной системы с помощью вызова rusage:

https://postgrespro.ru/docs/enterprise/16/pgpro-stats#PGPRO-STATS-RUSA GE

Page 4: Агрегированная статистика

Статистика запросов, агрегированная по объектам

  • весь кластер, база данных, пользователь, адрес, приложение, процесс
  • Основные настройки
    • pgpro_stats.track_totals = on
    • pgpro_stats.track_cluster, pgpro_stats.track_databases и т. п.
    • pgpro_stats.max_totals = 1000
    • предел количества объектов
  • Представление pgpro_stats_totals

Комментарии преподавателя

Модуль pgpro_stats позволяет агрегировать статистику запросов по различным объектам:

● cluster — весь кластер баз данных;

● database — отдельная база данных;

● user — пользователь;

● client_addr — адрес клиента;

● application — приложение;

● backend — обслуживающий процесс;

● session — сеанс.

Необходимость агрегации по конкретному типу объекта настраивается параметрами pgpro_stats.track_cluster, pgpro_stats.track_databases и т. п.

Агрегированная статистика доступна в представлении pgpro_stats_totals. Каждая строка в этом представлении содержит значения по конкретному объекту. Количество объектов, по которым отслеживается статистика, ограничено параметром pgpro_stats.max_totals (по умолчанию — 1000). Когда количество объектов превышает заданное, информация о наименее активно используемых объектах удаляется.

Page 5: Сбор статистики по операторам и планам

student$ psql

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

=> ALTER SYSTEM SET shared_preload_libraries = 'pgpro_stats';
ALTER SYSTEM
student$ sudo systemctl restart postgrespro-ent-16.service

Создадим тестовую базу данных на основе demo:

student$ psql
=> CREATE DATABASE pgpro_perfanalysis TEMPLATE demo;
CREATE DATABASE

Для удобства настроим путь поиска:

=> ALTER DATABASE pgpro_perfanalysis SET search_path TO bookings, public;
ALTER DATABASE

В схеме bookings копия стандартной демобазы минимального размера.

=> \c pgpro_perfanalysis
You are now connected to database "pgpro_perfanalysis" as user "student".
=> \dt
              List of relations
  Schema  |      Name       | Type  |  Owner
----------+-----------------+-------+---------
 bookings | aircrafts_data  | table | student
 bookings | airports_data   | table | student
 bookings | boarding_passes | table | student
 bookings | bookings        | table | student
 bookings | flights         | table | student
 bookings | seats           | table | student
 bookings | ticket_flights  | table | student
 bookings | tickets         | table | student
(8 rows)

Подключим расширение:

=> CREATE EXTENSION pgpro_stats;
CREATE EXTENSION

Выполним несколько запросов к таблице билетов tickets, предварительно добавив индекс по фамилиям пассажиров.

=> CREATE INDEX ticket_pname ON tickets(passenger_name);
CREATE INDEX

Пара запросов с параллельным последовательным сканированием таблицы.

=> SELECT count(book_ref) FROM tickets WHERE passenger_name < 'Z';
 count
--------
 363794
(1 row)
=> SELECT count(book_ref) FROM tickets WHERE passenger_name < 'Y';
 count
--------
 351813
(1 row)

Посмотрим, что накопилось в статистике операторов:

Page 6: Page 6

=> SELECT query, plan, calls
FROM pgpro_stats_statements
WHERE query LIKE 'SELECT count(book_ref) FROM tickets WHERE passenger_name%' \gx
-[ RECORD 1
]--------------------------------------------------------------------------------------
query | SELECT count(book_ref) FROM tickets WHERE passenger_name < $1
plan  | Finalize Aggregate
         +
      |   Output: count(book_ref)
         +
      |   ->  Gather
         +
      |         Output: (PARTIAL count(book_ref))
         +
      |         Workers Planned: 2
         +
      |         ->  Partial Aggregate
         +
      |               Output: PARTIAL count(book_ref)
         +
      |               ->  Parallel Seq Scan on bookings.tickets
         +
      |                     Output: ticket_no, book_ref, passenger_id, passenger_name,
contact_data+
      |                     Filter: (tickets.passenger_name < $1)
         +
      | Query Identifier: -7272530214084821635
         +
      |
calls | 2

Два оператора, отличающиеся константами, объединены в одну запись. Поле calls показывает количество выполнений. В следующих запросах используется индексный доступ:

=> SELECT count(book_ref) FROM tickets WHERE passenger_name > 'Z';
 count
-------
  2939
(1 row)
=> SELECT count(book_ref) FROM tickets WHERE passenger_name > 'Y';
 count
-------
 14920
(1 row)

Теперь у нас два разных плана.

=> SELECT query, plan, calls
FROM pgpro_stats_statements
WHERE query LIKE 'SELECT count(book_ref) FROM tickets WHERE passenger_name%' \gx

Page 7: Page 7

-[ RECORD 1
]--------------------------------------------------------------------------------------
query | SELECT count(book_ref) FROM tickets WHERE passenger_name < $1
plan  | Finalize Aggregate
         +
      |   Output: count(book_ref)
         +
      |   ->  Gather
         +
      |         Output: (PARTIAL count(book_ref))
         +
      |         Workers Planned: 2
         +
      |         ->  Partial Aggregate
         +
      |               Output: PARTIAL count(book_ref)
         +
      |               ->  Parallel Seq Scan on bookings.tickets
         +
      |                     Output: ticket_no, book_ref, passenger_id, passenger_name,
contact_data+
      |                     Filter: (tickets.passenger_name < $1)
         +
      | Query Identifier: -7272530214084821635
         +
      |
calls | 2
-[ RECORD 2
]--------------------------------------------------------------------------------------
query | SELECT count(book_ref) FROM tickets WHERE passenger_name > $1
plan  | Aggregate
         +
      |   Output: count(book_ref)
         +
      |   ->  Bitmap Heap Scan on bookings.tickets
         +
      |         Output: ticket_no, book_ref, passenger_id, passenger_name, contact_data
         +
      |         Recheck Cond: (tickets.passenger_name > $1)
         +
      |         ->  Bitmap Index Scan on ticket_pname
         +
      |               Index Cond: (tickets.passenger_name > $1)
         +
      | Query Identifier: -9112409754780737962
         +
      |
calls | 2

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

=> SELECT object_type, object_name, queries_executed, total_exec_time,
  (total_exec_rusage).reads, (total_exec_rusage).writes,
  (total_exec_rusage).user_time, (total_exec_rusage).system_time,
  jsonb_pretty(wait_stats) AS wait_stats
FROM pgpro_stats_totals
WHERE object_type = 'user'
  AND object_id = 'student'::regrole \gx

Page 8: Page 8

-[ RECORD 1 ]----+------------------------------------
object_type      | user
object_name      | student
queries_executed | 13
total_exec_time  | 10205.154014999996
reads            | 0
writes           | 661045248
user_time        | 1.666614
system_time      | 1.1153609999999998
wait_stats       | {                                  +
                 |     "IO": {                        +
                 |         "WALSync": 280,            +
                 |         "WALWrite": 60,            +
                 |         "BufFileWrite": 10,        +
                 |         "DataFileRead": 120,       +
                 |         "DataFileWrite": 190,      +
                 |         "DataFileExtend": 50,      +
                 |         "VersionFileSync": 10,     +
                 |         "RelationMapReplace": 30,  +
                 |         "DataFileImmediateSync": 30+
                 |     },                             +
                 |     "Total": {                     +
                 |         "IO": 780,                 +
                 |         "Total": 1020,             +
                 |         "LWLock": 240              +
                 |     },                             +
                 |     "LWLock": {                    +
                 |         "WALWrite": 240            +
                 |     }                              +
                 | }

В выводе этого запроса получены количество выполненных запросов, общее время выполнения запросов, а также несколько полей записи в столбце total_exec_rusage: reads — количество байтов, прочитанное из файловой системы; writes — количество байтов, записанное в файловую систему; user_time — время, потраченное на работу запроса в непривилегированном режиме (user space); system_time — время, потраченное на работу запроса на стороне ядра ОС (kernel space). Столбец total_exec_rusage составного типа pgpro_stats_rusage содержит статистику использования ресурсов на этапах планирования и выполнения запросов. Собранная статистика сбрасывается функцией pgpro_stats_statements_reset для заданного пользователя (userid), базы данных (dbid), запроса (queryid) или плана (planid). Если не указать ни один аргумент, будет очищена вся статистика:

=> SELECT pgpro_stats_statements_reset();
 pgpro_stats_statements_reset
-------------------------------
 2026-07-27 23:41:24.958237+03
(1 row)

Статистика по запросам удалена:

=> SELECT query, plan, calls
FROM pgpro_stats_statements
WHERE query LIKE 'SELECT count(*) FROM tickets WHERE passenger_name%' \gx
(0 rows)

Агрегированная статистика для заданного типа и идентификатора объекта очищается функцией pgpro_stats_totals_reset. Например, сбросим агрегатную статистику по роли student:

=> SELECT pgpro_stats_totals_reset('user', 'student'::regrole::bigint);
   pgpro_stats_totals_reset
-------------------------------
 2026-07-27 23:41:25.126849+03
(1 row)

Page 9: Page 9

=> SELECT object_type, object_name, queries_executed, total_exec_time,
  (total_exec_rusage).reads, (total_exec_rusage).writes,
  (total_exec_rusage).user_time, (total_exec_rusage).system_time,
  jsonb_pretty(wait_stats) AS wait_stats
FROM pgpro_stats_totals
WHERE object_type = 'user'
  AND object_id = 'student'::regrole \gx
-[ RECORD 1 ]----+-----------------------
object_type      | user
object_name      | student
queries_executed | 1
total_exec_time  | 0.010332999999999998
reads            | 0
writes           | 0
user_time        | 1.7000000000044757e-05
system_time      | 0
wait_stats       |

После сброса статистики был только что выполнен единственный запрос. Поэтому queries_executed равно единице. Статистика по базе данных сохранилась, так как она не была стерта:

=> SELECT object_type, object_name, queries_executed, total_exec_time,
  (total_exec_rusage).reads, (total_exec_rusage).writes,
  (total_exec_rusage).user_time, (total_exec_rusage).system_time,
  jsonb_pretty(wait_stats) AS wait_stats
FROM pgpro_stats_totals
WHERE object_type = 'database'
  AND object_id = (SELECT oid FROM pg_database WHERE datname = 'pgpro_perfanalysis') \gx
-[ RECORD 1 ]----+------------------------------------
object_type      | database
object_name      | pgpro_perfanalysis
queries_executed | 15
total_exec_time  | 1042.906253
reads            | 0
writes           | 31039488
user_time        | 0.774153
system_time      | 0.13438300000000003
wait_stats       | {                                  +
                 |     "IO": {                        +
                 |         "BufFileWrite": 10,        +
                 |         "DataFileRead": 60,        +
                 |         "DataFileWrite": 20,       +
                 |         "DataFileExtend": 10,      +
                 |         "DataFileImmediateSync": 30+
                 |     },                             +
                 |     "Total": {                     +
                 |         "IO": 130,                 +
                 |         "Total": 130               +
                 |     }                              +
                 | }

Page 10: Дополнительные метрики

Произвольные метрики на основе SQL-запросов

  • собираются фоновыми рабочими процессами
  • кольцевой буфер
  • Основные настройки
    • pgpro_stats.metrics_buffer_size = 16kB
    • pgpro_stats.metrics_workers = 2
    • pgpro_stats.metric_N_name
    • имя метрики
    • pgpro_stats.metric_N_query
    • SQL-запрос
    • pgpro_stats.metric_N_period = 1 min
    • интервал сбора
    • pgpro_stats.metric_N_db
    • список баз данных
    • pgpro_stats.metric_N_user
    • пользователь
  • Представление pg_stats_metrics

Комментарии преподавателя

Используя pgpro_stats, можно определить дополнительные метрики. Собираемые статистические данные будут накапливаться в кольцевом буфере (представление pgpro_stats_metrics) для последующей передачи их в систему мониторинга.

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

Для определения дополнительной метрики в конфигурацию СУБД необходимо поместить соответствующие параметры. Номер N в имени параметра служит уникальным идентификатором метрики, к которой будет применяться задаваемое значение; он должен быть положительным и уникальным среди всех метрик. Параметры:

● pgpro_stats.metric_N_name — имя метрики;

● pgpro_stats.metric_N_query — запрос для получения метрики;

● pgpro_stats.metric_N_period — периодичность измерений;

● pgpro_stats.metric_N_db — список баз данных;

● pgpro_stats.metric_N_user — пользователь, от имени которого будет собираться метрика. Пользователь должен иметь доступ к базе данных, в которой будет выполняться запрос для сбора метрики.

Обсуждение того, какие именно метрики имеет смысл собирать, выходит за рамки курса, но порекомендуем книгу Алексея Лесовского «Мониторинг PostgreSQL»: https://postgrespro.ru/education/books/monitoring

Page 11: Сбор дополнительных метрик

Предположим, что требуется раз в 30 секунд собирать количество выполнений контрольной точки по требованию. Запишем параметры в файл conf.d/metrics.conf в каталоге данных кластера и перезапустим СУБД.

/var/lib/pgpro/ent-16/conf.d/metrics.conf
pgpro_stats.metric_1_name = 'bgwriter'
pgpro_stats.metric_1_query = 'SELECT * FROM pg_stat_bgwriter'
pgpro_stats.metric_1_db = 'pgpro_perfanalysis'
pgpro_stats.metric_1_user = 'student'
pgpro_stats.metric_1_period = '30s'

Добавленная метрика — первая, поэтому N=1.

student$ sudo systemctl restart postgrespro-ent-16.service
student$ psql -p 5432 -d pgpro_perfanalysis

Заглянем в представление для дополнительных метрик:

=> SELECT metric_number, db_name, ts, value #> '{0,checkpoints_req}' AS checkpoints_req
FROM pgpro_stats_metrics;
 metric_number | db_name | ts | checkpoints_req
---------------+---------+----+-----------------
(0 rows)

Столбец value содержит результат запроса, вычисляющего метрику, в виде массива объектов jsonb. Здесь извлечен первый по порядку (нулевой) элемент, в котором по ключу checkpoints_req получено искомое значение. Поменяем сразу много строк, например, фамилии пассажиров во всех купленных авиабилетах:

=> UPDATE tickets SET passenger_name = initcap(passenger_name);
UPDATE 366733

Выполним контрольную точку и немного подождем.

=> CHECKPOINT;
CHECKPOINT

В представлении pgpro_stats_metrics появится одна или несколько записей. Обратите внимание на значение checkpoints_req:

=> SELECT metric_number, db_name, ts, value #> '{0,checkpoints_req}' AS checkpoints_req
FROM pgpro_stats_metrics;
 metric_number |      db_name       |              ts               | checkpoints_req
---------------+--------------------+-------------------------------+-----------------
             1 | pgpro_perfanalysis | 2026-07-27 23:41:32.319727+03 | 3
             1 | pgpro_perfanalysis | 2026-07-27 23:42:02.091546+03 | 4
(2 rows)

Удалим настройки дополнительных метрик:

postgres$ rm /var/lib/pgpro/ent-16/conf.d/metrics.conf

Снова перезагрузим сервер.

student$ sudo systemctl restart postgrespro-ent-16.service

Page 12: Статистика очистки

Собирается ядром Postgres Pro Enterprise

  • Представления
    • pg_stats_vacuum_database — очистка баз данных
    • pg_stats_vacuum_tables — очистка таблиц текущей базы
    • pg_stats_vacuum_indexes — очистка индексов текущей базы

Комментарии преподавателя

Представления pg_stats_vacuum_database, pg_stats_vacuum_tables и pg_stats_vacuum_indexes содержат статистику очистки, соответственно, баз данных, таблиц в текущей базе данных и индексов в ней. Все эти представления основаны на одноименных функциях и доступны без установки расширения pgpro_stats.

Данные собираются ядром Postgres Pro Enterprise: стандартный сборщик статистики расширен для получения этой дополнительной информации.

Отслеживается подробная информация на уровне страниц (в количественном и временнóм представлении) и журнальных записей.

Page 13: Статистика очистки

student$ psql -p 5432 -d pgpro_perfanalysis

Выключим автоочистку для таблицы tickets:

=> ALTER TABLE tickets SET (autovacuum_enabled = off);
ALTER TABLE

Поскольку статистика по очистке собирается ядром СУБД, для ее сброса используется стандартная функция:

=> SELECT pg_stat_reset();
 pg_stat_reset
---------------
(1 row)

Статистика очистки для таблицы tickets отсутствует:

=> SELECT * FROM pg_stats_vacuum_tables(
  (SELECT oid FROM pg_database WHERE datname = 'pgpro_perfanalysis'),
  'tickets'::regclass
) \gx
-[ RECORD 1 ]---------+-
relid                 |
total_blks_read       |
total_blks_hit        |
total_blks_dirtied    |
total_blks_written    |
rel_blks_read         |
rel_blks_hit          |
pages_scanned         |
pages_removed         |
pages_frozen          |
pages_all_visible     |
tuples_deleted        |
tuples_frozen         |
dead_tuples           |
index_vacuum_count    |
rev_all_frozen_pages  |
rev_all_visible_pages |
wal_records           |
wal_fpi               |
wal_bytes             |
blk_read_time         |
blk_write_time        |
delay_time            |
system_time           |
user_time             |
total_time            |
interrupts            |

Вернем имена пассажиров к верхнему регистру и выполним очистку.

=> UPDATE tickets SET passenger_name = upper(passenger_name);
UPDATE 366733
=> VACUUM tickets;
VACUUM

Немного ожидания… Снова проверим статистику очистки для таблицы tickets. Теперь статистика очистки собрана:

=> SELECT * FROM pg_stats_vacuum_tables(
  (SELECT oid FROM pg_database WHERE datname = 'pgpro_perfanalysis'),
  'tickets'::regclass
) \gx

Page 14: Page 14

-[ RECORD 1 ]---------+-------------------
relid                 | 16443
total_blks_read       | 261
total_blks_hit        | 31665
total_blks_dirtied    | 262
total_blks_written    | 228
rel_blks_read         | 261
rel_blks_hit          | 31648
pages_scanned         | 61595
pages_removed         | 0
pages_frozen          | 16731
pages_all_visible     | 47071
tuples_deleted        | 1833665
tuples_frozen         | 0
dead_tuples           | 0
index_vacuum_count    | 3
rev_all_frozen_pages  | 12310
rev_all_visible_pages | 12310
wal_records           | 26846
wal_fpi               | 3
wal_bytes             | 3141770
blk_read_time         | 0
blk_write_time        | 0
delay_time            | 0
system_time           | 9.074000000000002
user_time             | 135.62900000000002
total_time            | 222.19
interrupts            | 0

Вернем автоочистку для таблицы tickets:

=> ALTER TABLE tickets SET (autovacuum_enabled = on);
ALTER TABLE
=> \q

Page 15: Аннулирование кешей

Сообщения об аннулировании метаданных измененных объектов в локальных кешах процессов

  • Представления
    • pgpro_stats_inval_status — состояние очереди сообщений
    • pgpro_stats_statements
    • pgpro_stats_totals
    • счетчики сообщений для операторов

Комментарии преподавателя

Обслуживающие процессы кешируют метаданные объектов в своих локальных кешах. Когда процесс меняет метаданные объекта, нужно обновить информацию в кешах других обслуживающих процессов. Для этого процесс отправляет другим процессам сообщение аннулирования (Cache Invalidation), которое помещается в специальную очередь.

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

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

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

Представление pgpro_stats_inval_status содержит одну строку с текущим состоянием очереди аннулирования кеша. Представления pgpro_stats_statements и pgpro_stats_totals для каждого объекта показывают запись со счетчиками сообщений аннулирования кеша.

https://postgrespro.ru/docs/enterprise/16/pgpro-stats#PGPRO-STATS-CAC HE-INVALIDATION-METRICS

Page 16: Аннулирование кешей

Откроем новый сеанс и получим PID обслуживающего процесса:

student$ psql -d pgpro_perfanalysis
=> SELECT pg_backend_pid();
 pg_backend_pid
----------------
         824559
(1 row)

Пока ни одного сообщения об аннулировании кеша нет:

=> SELECT object_type, (inval_msgs).total
FROM pgpro_stats_totals
WHERE object_type = 'backend'
  AND object_id = 824559;
 object_type | total
-------------+-------
 backend     |     0
(1 row)

Изменим, например, параметр хранения таблицы.

=> ALTER TABLE seats SET (autovacuum_enabled = on);
ALTER TABLE

Сообщения об аннулировании кеша, сгенерированные текущим обслуживающим процессом:

=> SELECT object_type,
  (inval_msgs).total,
  (inval_msgs).catcache,
  (inval_msgs).relcache
FROM pgpro_stats_totals
    WHERE object_type = 'backend'
      AND object_id = 824559;
 object_type | total | catcache | relcache
-------------+-------+----------+----------
 backend     |     3 |        2 |        1
(1 row)

Здесь: total — общее число сообщений аннулирования; catcache — число сообщений избирательного аннулирования кеша каталога; relcache — число сообщений избирательного аннулирования кеша отношений.

=> \q

Page 17: Трассировка сеансов

Трассировка сеансов в журнал сообщений или файл

  • Трассировка выполняется только для команд, удовлетворяющих критериям фильтрации
    • pgpro_stats_trace_insert
    • создает фильтр
    • pgpro_stats_trace_update — изменяет фильтр
    • pgpro_stats_trace_delete — удаляет фильтр
    • pgpro_stats_trace_reset
    • удаляет все фильтры
    • pgpro_stats_trace_show
    • показывает фильтры

Комментарии преподавателя

Расширение pgpro_stats позволяет выполнять трассировку сеансов, записывая данные о выполняемых командах в журнал сообщений или в файлы. Регистрируются команды, которые удовлетворяют фильтрам. Фильтры размещаются в таблице в общей памяти. Строки таблицы описывают фильтры, а столбцы содержат условия фильтрации.

Для управления фильтрами расширение pgpro_stats предоставляет функции, показанные на слайде.

Изменения фильтров сразу влияют на трассировку во всех сеансах экземпляра СУБД.

Условия фильтров включают:

● идентифицирующие поля — проверяются на равенство;

● ресурсные поля — проверяются на превышение порога;

● поля, определяющие параметры EXPLAIN.

Например, database_name — идентифицирующее поле: сеанс, в котором выполнена команда, должен быть в указанной базе данных. Если же в фильтре указано ресурсное поле duration, то команда должна выполняться более указанной длительности.

Подробности об условиях фильтров в документации: https://postgrespro.ru/docs/enterprise/16/pgpro-stats#PGPRO-STATS-TRAC E-SHOW-OUTPUT

Page 18: Трассировка сеансов

student$ psql -d pgpro_perfanalysis

Откроем второй сеанс и получим PID обслуживающего процесса.

student$ psql -d pgpro_perfanalysis
=> SELECT pg_backend_pid();
 pg_backend_pid
----------------
         824863
(1 row)

Добавим фильтр pa1, который будет записывать в файл трассировки pa1.trace вывод EXPLAIN ANALYZE для каждой команды, выполняющейся во втором сеансе:

=> SELECT pgpro_stats_trace_insert(
  'alias', 'pa1',
  'database_name', current_database(),
  'pid', 824863,
  'explain_analyze', true,
  'tracefile', 'pa1'
);
 pgpro_stats_trace_insert
--------------------------
                        1
(1 row)

Проверим получившийся фильтр трассировки:

=> SELECT * from pgpro_stats_trace_show() \gx

Page 19: Page 19

-[ RECORD 1 ]-------+-------------------
filter_id           | 1
active              | t
alias               | pa1
tracefile           | pa1
pid                 | 824863
database_name       | pgpro_perfanalysis
client_addr         |
application_name    |
username            |
queryid             |
planid              |
duration            |
plan_time           |
exec_time           |
user_time           |
system_time         |
rows                |
shared_blks_hit     |
shared_blks_read    |
shared_blks_fetched |
shared_blks_dirtied |
shared_blks_written |
local_blks_hit      |
local_blks_read     |
local_blks_fetched  |
local_blks_dirtied  |
local_blks_written  |
temp_blks_read      |
temp_blks_written   |
wal_bytes           |
total_wait_time     |
total_inval_msgs    |
explain_analyze     | t
explain_verbose     |
explain_costs       |
explain_settings    |
explain_buffers     |
explain_wal         |
explain_timing      |
explain_format      |
time_info           | f

Во втором сеансе выполним запрос.

=> SELECT count(*) FROM tickets WHERE passenger_name > 'Z';
 count
-------
  2939
(1 row)

Закроем второй сеанс и проверим файл трассировки.

=> \q
postgres$ cat /var/lib/pgpro/ent-16/pg_stat/pa1.trace
Query Text: SELECT count(*) FROM tickets WHERE passenger_name > 'Z';
Aggregate  (cost=12.74..12.75 rows=1 width=8) (actual time=1.325..1.326 rows=1 loops=1)
  ->  Index Only Scan using ticket_pname on tickets  (cost=0.42..11.70 rows=416 width=0)
(actual time=0.040..1.064 rows=2939 loops=1)
        Index Cond: (passenger_name > 'Z'::text)
        Heap Fetches: 0

Удалим фильтр трассировки.

=> SELECT pgpro_stats_trace_delete(1);
 pgpro_stats_trace_delete
--------------------------
 t
(1 row)

Page 20: Итоги

Расширение pgpro\_stats позволяет выявлять и анализировать ресурсоемкие операции

  • Собирается статистика по затраченным ресурсам, планам и событиям ожиданий
  • Отдельно собирается агрегированная статистика
  • Можно использовать метрики, определенные пользователем
  • Имеется статистика по очистке и аннулированию кешей
  • Можно трассировать сеансы

Page 21: Практика

  1. Подготовьте СУБД для работы с расширением pgpro_stats. 2. Выполните запрос с двухпроходным хеш-соединением в условиях недостаточной памяти work_mem. 3. Получите отчет pgpro_stats. 4. Выполните этот же запрос в условиях достаточной памяти work_mem и снова получите отчет.

Комментарии преподавателя

  1. Подключите разделяемую библиотеку pgpro_stats, создайте базу данных — копию базы данных demo и подключите к ней расширение.

  2. Запустите транзакцию с установленными локальными параметрами work_mem = ‘12MB’ и hash_mem_multiplier = 1. В транзакции получите реальный план запроса SELECT * FROM bookings b JOIN tickets t ON b.book_ref = t.book_ref

  3. Получите отчет pgpro_stats с помощью представления pgpro_stats_statements, из которого необходимо вывести поля: query, plan, calls, temp_blks_read, temp_blks_written.

  4. Очистите статистику и второй раз запустите транзакцию с настройками work_mem = ‘48MB’ и hash_mem_multiplier = 3 и снова получите такой же отчет. Обратите внимание во втором отчете на отсутствие операций по работе с временными файлами.

Page 22: 1. Подготовка СУБД для работы с расширением pgpro_stats

student$ psql

Подключим разделяемую библиотеку:

=> ALTER SYSTEM SET shared_preload_libraries = pgpro_stats;
ALTER SYSTEM

Перезагрузим сервер.

student$ sudo systemctl restart postgrespro-ent-16.service

2. Запрос при недостатке памяти

Создадим тестовую базу данных на основе demo:

student$ psql
=> CREATE DATABASE pgpro_perfanalysis TEMPLATE demo;
CREATE DATABASE

Для удобства настроим путь поиска:

=> ALTER DATABASE pgpro_perfanalysis SET search_path TO bookings, public;
ALTER DATABASE

Подключимся к базе данных.

=> \c pgpro_perfanalysis
You are now connected to database "pgpro_perfanalysis" as user "student".

Подключим расширение:

=> CREATE EXTENSION pgpro_stats;
CREATE EXTENSION

Зададим размер памяти:

=> SET work_mem = '12MB';
SET
=> SET hash_mem_multiplier = 1;
SET

Выполняем запрос:

=> EXPLAIN (analyze, buffers, costs off, timing off, summary off)
SELECT * FROM bookings b
  JOIN tickets t ON b.book_ref = t.book_ref;
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Join (actual rows=366733 loops=1)
   Hash Cond: (t.book_ref = b.book_ref)
   Buffers: shared hit=3760 read=4128, temp read=3491 written=3491
   ->  Seq Scan on tickets t (actual rows=366733 loops=1)
         Buffers: shared hit=2048 read=4128
   ->  Hash (actual rows=262788 loops=1)
         Buckets: 262144  Batches: 2  Memory Usage: 9071kB
         Buffers: shared hit=1712, temp written=690
         ->  Seq Scan on bookings b (actual rows=262788 loops=1)
               Buffers: shared hit=1712
 Planning:
   Buffers: shared hit=191 read=3
(12 rows)

3. Отчет по нагрузке

Соединение хешированием при недостатке оперативной памяти выполняется в двухпроходном режиме и использует

Page 23: Page 23

временные файлы. Проверим, что накопилось в статистике.

=> SELECT query, plan, calls, temp_blks_read, temp_blks_written
FROM pgpro_stats_statements
WHERE query LIKE 'EXPLAIN (analyze, buffers,%' \gx
-[ RECORD 1 ]-----+---------------------------------------------------------------
query             | EXPLAIN (analyze, buffers, costs off, timing off, summary off)+
                  | SELECT * FROM bookings b                                      +
                  |   JOIN tickets t ON b.book_ref = t.book_ref
plan              |
calls             | 1
temp_blks_read    | 3491
temp_blks_written | 3491

По столбцам temp_blks_read и temp_blks_written можно судить об активности использования временных файлов.

4. Отчет по нагрузке при достаточном объеме памяти

Сбросим статистику.

=> SELECT pgpro_stats_statements_reset();
 pgpro_stats_statements_reset
------------------------------
 2026-07-28 00:13:57.07154+03
(1 row)

Увеличим значения параметров:

=> SET work_mem = '48MB';
SET
=> SET hash_mem_multiplier = 3;
SET

Соединение хешированием станет однопроходным и не будет использовать временные файлы.

=> EXPLAIN (analyze, buffers, costs off, timing off, summary off)
SELECT * FROM bookings b
  JOIN tickets t ON b.book_ref = t.book_ref;
                           QUERY PLAN
-----------------------------------------------------------------
 Hash Join (actual rows=366733 loops=1)
   Hash Cond: (t.book_ref = b.book_ref)
   Buffers: shared hit=3792 read=4096
   ->  Seq Scan on tickets t (actual rows=366733 loops=1)
         Buffers: shared hit=2080 read=4096
   ->  Hash (actual rows=262788 loops=1)
         Buckets: 524288  Batches: 1  Memory Usage: 18190kB
         Buffers: shared hit=1712
         ->  Seq Scan on bookings b (actual rows=262788 loops=1)
               Buffers: shared hit=1712
 Planning:
   Buffers: shared hit=8
(12 rows)

Проверим, что накопилось в статистике.

=> SELECT query, plan, calls, temp_blks_read, temp_blks_written
FROM pgpro_stats_statements
WHERE query LIKE 'EXPLAIN (analyze, buffers,%' \gx
-[ RECORD 1 ]-----+---------------------------------------------------------------
query             | EXPLAIN (analyze, buffers, costs off, timing off, summary off)+
                  | SELECT * FROM bookings b                                      +
                  |   JOIN tickets t ON b.book_ref = t.book_ref
plan              |
calls             | 1
temp_blks_read    | 0
temp_blks_written | 0

Временные файлы не использовались.

Page 24: Уборка

Удалим созданную базу данных и очистим список загружаемых библиотек.

=> \c student
You are now connected to database "student" as user "student".
=> DROP DATABASE pgpro_perfanalysis WITH (FORCE);
DROP DATABASE
=> ALTER SYSTEM RESET shared_preload_libraries;
ALTER SYSTEM
student$ sudo systemctl restart postgrespro-ent-16.service

Page 25: Практика PPEM

  1. В основном экземпляре создайте из шаблона demo базу данных stats_ppem и подготовьте ее для работы с pgpro_stats. 2. С помощью psql-консоли выполните запрос из оператора EXPLAIN п. 2 задания основной практики. 3. Модифицируйте запрос так, чтобы он ограничивал результаты определенным количеством строк. Выполните запрос. 4. В статистике, собранной расширением pgpro_stats, найдите оба запроса и сравните их представления и планы.

Комментарии преподавателя

  1. Для удобства дальнейшей работы подключитесь к базе как пользователь student с помощью метакоманды \c — student и включите в путь поиска схему bookings (или используйте запросы с квалифицированными именами таблиц): SELECT * FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book_ref = t.book_ref;

  2. Дополненный запрос: SELECT * FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book_ref = t.book_ref LIMIT 555;

  3. В разделе Мониторинг/SQL-статистика удобно поставить фильтры на базу данных и пользователя и отсортировать данные по количеству возвращенных строк.

Обратите внимание на представление константы в запросе из п. 3.

Page 26: 1. Создание базы из шаблона и установка расширения pgpro_stats

Выберите вкладку «Базы данных» и нажмите «Создать базу данных». При заполнении формы укажите шаблон — базу demo. На вкладке «Параметры» добавьте в shared_preload_libraries библиотеку pgpro_stats. Сохраните измененный параметр и перезапустите сервер. Далее на вкладке «Расширения» нажмите «Установить расширение», выберите базу, в списке расширений найдите pgpro_stats и установите его.

2,3. Выполнение запросов

В списке «Базы данных» найдите stats_ppem и в столбце «Действия» нажмите на пиктограмму «{/}» (открыть PSQL-консоль). Выполните запросы: SELECT * FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book_ref = t.book_ref; SELECT * FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book_ref = t.book_ref LIMIT 555;

4. Сохраненные планы запросов

На вкладке «SQL-статистика» отфильтруйте запросы для базы stats_ppem и пользователя student. Для просмотра плана запроса нажмите на значок «Раскрыть» в самом левом столбце таблицы. Запросы, различающиеся константами, объединяются расширением в одну запись, а сами эти константы заменяются обозначением параметра; в плане запроса п.3 константа 555 заменена на $1.