# Занятие 12: Производительность / Анализ производительности > Исходная страница в нативном Markdown: Производительность / Анализ производительности --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [pgpro_12_perfanalysis.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_12_perfanalysis.html) > **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip) > **Страниц PDF:** 26 ## Page 1: Производительность / Анализ производительности {#source-page-001} ### Авторские права © Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Расширение pgpro\_stats - Агрегированная статистика - Дополнительные метрики - Статистика очистки - Статистика аннулирования кеша - Трассировка сеансов ## Page 3: Расширение pgpro_stats {#source-page-003} **Статистика запросов** - затраченные ресурсы, планы и события ожиданий - Основные настройки - 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: GE ## Page 4: Агрегированная статистика {#source-page-004} **Статистика запросов, агрегированная по объектам** - весь кластер, база данных, пользователь, адрес, приложение, процесс - Основные настройки - 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: Сбор статистики по операторам и планам {#source-page-005} ```sh student$ psql ``` Подключим разделяемую библиотеку pgpro\_stats. При наличии других загружаемых библиотек pgpro\_stats должна указываться в списке параметра shared\_preload\_libraries последней. ```sql => ALTER SYSTEM SET shared_preload_libraries = 'pgpro_stats'; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` Создадим тестовую базу данных на основе demo: ```sh student$ psql ``` ```sql => CREATE DATABASE pgpro_perfanalysis TEMPLATE demo; ``` ```sql CREATE DATABASE ``` Для удобства настроим путь поиска: ```sql => ALTER DATABASE pgpro_perfanalysis SET search_path TO bookings, public; ``` ```sql ALTER DATABASE ``` В схеме bookings копия стандартной демобазы минимального размера. ```sql => \c pgpro_perfanalysis ``` ```text You are now connected to database "pgpro_perfanalysis" as user "student". ``` ```sql => \dt ``` ```text 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) ``` Подключим расширение: ```sql => CREATE EXTENSION pgpro_stats; ``` ```sql CREATE EXTENSION ``` Выполним несколько запросов к таблице билетов tickets, предварительно добавив индекс по фамилиям пассажиров. ```sql => CREATE INDEX ticket_pname ON tickets(passenger_name); ``` ```sql CREATE INDEX ``` Пара запросов с параллельным последовательным сканированием таблицы. ```sql => SELECT count(book_ref) FROM tickets WHERE passenger_name < 'Z'; ``` ```text count -------- 363794 (1 row) ``` ```sql => SELECT count(book_ref) FROM tickets WHERE passenger_name < 'Y'; ``` ```text count -------- 351813 (1 row) ``` Посмотрим, что накопилось в статистике операторов: ## Page 6: Page 6 {#source-page-006} ```sql => SELECT query, plan, calls FROM pgpro_stats_statements WHERE query LIKE 'SELECT count(book_ref) FROM tickets WHERE passenger_name%' \gx ``` ```sql -[ 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 показывает количество выполнений. В следующих запросах используется индексный доступ: ```sql => SELECT count(book_ref) FROM tickets WHERE passenger_name > 'Z'; ``` ```text count ------- 2939 (1 row) ``` ```sql => SELECT count(book_ref) FROM tickets WHERE passenger_name > 'Y'; ``` ```text count ------- 14920 (1 row) ``` Теперь у нас два разных плана. ```sql => 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 {#source-page-007} ```sql -[ 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 получим агрегированную статистику по использованию некоторых системных ресурсов, а также по количеству выполненных запросов и событий ожидания. ```sql => 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 {#source-page-008} ```text -[ 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). Если не указать ни один аргумент, будет очищена вся статистика: ```sql => SELECT pgpro_stats_statements_reset(); ``` ```text pgpro_stats_statements_reset ------------------------------- 2026-07-27 23:41:24.958237+03 (1 row) ``` Статистика по запросам удалена: ```sql => SELECT query, plan, calls FROM pgpro_stats_statements WHERE query LIKE 'SELECT count(*) FROM tickets WHERE passenger_name%' \gx ``` ```text (0 rows) ``` Агрегированная статистика для заданного типа и идентификатора объекта очищается функцией pgpro\_stats\_totals\_reset. Например, сбросим агрегатную статистику по роли student: ```sql => SELECT pgpro_stats_totals_reset('user', 'student'::regrole::bigint); ``` ```text pgpro_stats_totals_reset ------------------------------- 2026-07-27 23:41:25.126849+03 (1 row) ``` ## Page 9: Page 9 {#source-page-009} ```sql => 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 ``` ```text -[ 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 равно единице. Статистика по базе данных сохранилась, так как она не была стерта: ```sql => 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 ``` ```text -[ 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: Дополнительные метрики {#source-page-010} **Произвольные метрики на основе 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»: ## Page 11: Сбор дополнительных метрик {#source-page-011} Предположим, что требуется раз в 30 секунд собирать количество выполнений контрольной точки по требованию. Запишем параметры в файл conf.d/metrics.conf в каталоге данных кластера и перезапустим СУБД. ```text /var/lib/pgpro/ent-16/conf.d/metrics.conf ``` ```sql 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. ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ```sh student$ psql -p 5432 -d pgpro_perfanalysis ``` Заглянем в представление для дополнительных метрик: ```sql => SELECT metric_number, db_name, ts, value #> '{0,checkpoints_req}' AS checkpoints_req FROM pgpro_stats_metrics; ``` ```text metric_number | db_name | ts | checkpoints_req ---------------+---------+----+----------------- (0 rows) ``` Столбец value содержит результат запроса, вычисляющего метрику, в виде массива объектов jsonb. Здесь извлечен первый по порядку (нулевой) элемент, в котором по ключу checkpoints\_req получено искомое значение. Поменяем сразу много строк, например, фамилии пассажиров во всех купленных авиабилетах: ```sql => UPDATE tickets SET passenger_name = initcap(passenger_name); ``` ```sql UPDATE 366733 ``` Выполним контрольную точку и немного подождем. ```text => CHECKPOINT; ``` ```text CHECKPOINT ``` В представлении pgpro\_stats\_metrics появится одна или несколько записей. Обратите внимание на значение checkpoints\_req: ```sql => SELECT metric_number, db_name, ts, value #> '{0,checkpoints_req}' AS checkpoints_req FROM pgpro_stats_metrics; ``` ```text 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) ``` Удалим настройки дополнительных метрик: ```sh postgres$ rm /var/lib/pgpro/ent-16/conf.d/metrics.conf ``` Снова перезагрузим сервер. ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ## Page 12: Статистика очистки {#source-page-012} **Собирается ядром 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: Статистика очистки {#source-page-013} ```sh student$ psql -p 5432 -d pgpro_perfanalysis ``` Выключим автоочистку для таблицы tickets: ```sql => ALTER TABLE tickets SET (autovacuum_enabled = off); ``` ```sql ALTER TABLE ``` Поскольку статистика по очистке собирается ядром СУБД, для ее сброса используется стандартная функция: ```sql => SELECT pg_stat_reset(); ``` ```text pg_stat_reset --------------- (1 row) ``` Статистика очистки для таблицы tickets отсутствует: ```sql => SELECT * FROM pg_stats_vacuum_tables( (SELECT oid FROM pg_database WHERE datname = 'pgpro_perfanalysis'), 'tickets'::regclass ) \gx ``` ```text -[ 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 | ``` Вернем имена пассажиров к верхнему регистру и выполним очистку. ```sql => UPDATE tickets SET passenger_name = upper(passenger_name); ``` ```sql UPDATE 366733 ``` ```sql => VACUUM tickets; ``` ```sql VACUUM ``` Немного ожидания... Снова проверим статистику очистки для таблицы tickets. Теперь статистика очистки собрана: ```sql => SELECT * FROM pg_stats_vacuum_tables( (SELECT oid FROM pg_database WHERE datname = 'pgpro_perfanalysis'), 'tickets'::regclass ) \gx ``` ## Page 14: Page 14 {#source-page-014} ```text -[ 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: ```sql => ALTER TABLE tickets SET (autovacuum_enabled = on); ``` ```sql ALTER TABLE ``` ```sql => \q ``` ## Page 15: Аннулирование кешей {#source-page-015} **Сообщения об аннулировании метаданных измененных объектов в локальных кешах процессов** - Представления - pgpro\_stats\_inval\_status — состояние очереди сообщений - pgpro\_stats\_statements - pgpro\_stats\_totals - счетчики сообщений для операторов ### Комментарии преподавателя Обслуживающие процессы кешируют метаданные объектов в своих локальных кешах. Когда процесс меняет метаданные объекта, нужно обновить информацию в кешах других обслуживающих процессов. Для этого процесс отправляет другим процессам сообщение аннулирования (Cache Invalidation), которое помещается в специальную очередь. Обслуживающие процессы получают сообщения аннулирования из этой очереди. Если объект, для которого получено сообщение аннулирования, находится в кеше, его метаданные в кеше обновляются. Когда обслуживающий процесс, добавляющий сообщения в очередь, сталкивается с пределом размера очереди, он удаляет сообщения, уже обработанные всеми обслуживающими процессами. Если какие-то обслуживающие процессы не успевают обрабатывать сообщения и задерживают очистку, им передается сигнал для сброса кеша. В pgpro\_stats значения большинства счетчиков сообщений аннулирования увеличиваются при генерации сообщений, а сообщения попадают в очередь после фиксации транзакции. При откате счетчики не уменьшаются, хотя сообщения при этом не попадают в очередь. Представление pgpro\_stats\_inval\_status содержит одну строку с текущим состоянием очереди аннулирования кеша. Представления pgpro\_stats\_statements и pgpro\_stats\_totals для каждого объекта показывают запись со счетчиками сообщений аннулирования кеша. HE-INVALIDATION-METRICS ## Page 16: Аннулирование кешей {#source-page-016} Откроем новый сеанс и получим PID обслуживающего процесса: ```sh student$ psql -d pgpro_perfanalysis ``` ```sql => SELECT pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 824559 (1 row) ``` Пока ни одного сообщения об аннулировании кеша нет: ```sql => SELECT object_type, (inval_msgs).total FROM pgpro_stats_totals WHERE object_type = 'backend' AND object_id = 824559; ``` ```text object_type | total -------------+------- backend | 0 (1 row) ``` Изменим, например, параметр хранения таблицы. ```sql => ALTER TABLE seats SET (autovacuum_enabled = on); ``` ```sql ALTER TABLE ``` Сообщения об аннулировании кеша, сгенерированные текущим обслуживающим процессом: ```sql => SELECT object_type, (inval_msgs).total, (inval_msgs).catcache, (inval_msgs).relcache FROM pgpro_stats_totals WHERE object_type = 'backend' AND object_id = 824559; ``` ```text object_type | total | catcache | relcache -------------+-------+----------+---------- backend | 3 | 2 | 1 (1 row) ``` Здесь: total — общее число сообщений аннулирования; catcache — число сообщений избирательного аннулирования кеша каталога; relcache — число сообщений избирательного аннулирования кеша отношений. ```sql => \q ``` ## Page 17: Трассировка сеансов {#source-page-017} **Трассировка сеансов в журнал сообщений или файл** - Трассировка выполняется только для команд, удовлетворяющих критериям фильтрации - 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, то команда должна выполняться более указанной длительности. Подробности об условиях фильтров в документации: E-SHOW-OUTPUT ## Page 18: Трассировка сеансов {#source-page-018} ```sh student$ psql -d pgpro_perfanalysis ``` Откроем второй сеанс и получим PID обслуживающего процесса. ```sh student$ psql -d pgpro_perfanalysis ``` ```sql => SELECT pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 824863 (1 row) ``` Добавим фильтр pa1, который будет записывать в файл трассировки pa1.trace вывод EXPLAIN ANALYZE для каждой команды, выполняющейся во втором сеансе: ```sql => SELECT pgpro_stats_trace_insert( 'alias', 'pa1', 'database_name', current_database(), 'pid', 824863, 'explain_analyze', true, 'tracefile', 'pa1' ); ``` ```text pgpro_stats_trace_insert -------------------------- 1 (1 row) ``` Проверим получившийся фильтр трассировки: ```sql => SELECT * from pgpro_stats_trace_show() \gx ``` ## Page 19: Page 19 {#source-page-019} ```text -[ 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 ``` Во втором сеансе выполним запрос. ```sql => SELECT count(*) FROM tickets WHERE passenger_name > 'Z'; ``` ```text count ------- 2939 (1 row) ``` Закроем второй сеанс и проверим файл трассировки. ```sql => \q ``` ```sh postgres$ cat /var/lib/pgpro/ent-16/pg_stat/pa1.trace ``` ```sql 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 ``` Удалим фильтр трассировки. ```sql => SELECT pgpro_stats_trace_delete(1); ``` ```text pgpro_stats_trace_delete -------------------------- t (1 row) ``` ## Page 20: Итоги {#source-page-020} **Расширение pgpro\\\_stats позволяет выявлять и анализировать ресурсоемкие операции** - Собирается статистика по затраченным ресурсам, планам и событиям ожиданий - Отдельно собирается агрегированная статистика - Можно использовать метрики, определенные пользователем - Имеется статистика по очистке и аннулированию кешей - Можно трассировать сеансы ## Page 21: Практика {#source-page-021} 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 {#source-page-022} ```sh student$ psql ``` Подключим разделяемую библиотеку: ```sql => ALTER SYSTEM SET shared_preload_libraries = pgpro_stats; ``` ```sql ALTER SYSTEM ``` Перезагрузим сервер. ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ### 2. Запрос при недостатке памяти Создадим тестовую базу данных на основе demo: ```sh student$ psql ``` ```sql => CREATE DATABASE pgpro_perfanalysis TEMPLATE demo; ``` ```sql CREATE DATABASE ``` Для удобства настроим путь поиска: ```sql => ALTER DATABASE pgpro_perfanalysis SET search_path TO bookings, public; ``` ```sql ALTER DATABASE ``` Подключимся к базе данных. ```sql => \c pgpro_perfanalysis ``` ```text You are now connected to database "pgpro_perfanalysis" as user "student". ``` Подключим расширение: ```sql => CREATE EXTENSION pgpro_stats; ``` ```sql CREATE EXTENSION ``` Зададим размер памяти: ```text => SET work_mem = '12MB'; ``` ```text SET ``` ```text => SET hash_mem_multiplier = 1; ``` ```text SET ``` Выполняем запрос: ```sql => EXPLAIN (analyze, buffers, costs off, timing off, summary off) SELECT * FROM bookings b JOIN tickets t ON b.book_ref = t.book_ref; ``` ```sql 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 {#source-page-023} временные файлы. Проверим, что накопилось в статистике. ```sql => SELECT query, plan, calls, temp_blks_read, temp_blks_written FROM pgpro_stats_statements WHERE query LIKE 'EXPLAIN (analyze, buffers,%' \gx ``` ```sql -[ 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. Отчет по нагрузке при достаточном объеме памяти Сбросим статистику. ```sql => SELECT pgpro_stats_statements_reset(); ``` ```text pgpro_stats_statements_reset ------------------------------ 2026-07-28 00:13:57.07154+03 (1 row) ``` Увеличим значения параметров: ```text => SET work_mem = '48MB'; ``` ```text SET ``` ```text => SET hash_mem_multiplier = 3; ``` ```text SET ``` Соединение хешированием станет однопроходным и не будет использовать временные файлы. ```sql => EXPLAIN (analyze, buffers, costs off, timing off, summary off) SELECT * FROM bookings b JOIN tickets t ON b.book_ref = t.book_ref; ``` ```sql 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) ``` Проверим, что накопилось в статистике. ```sql => SELECT query, plan, calls, temp_blks_read, temp_blks_written FROM pgpro_stats_statements WHERE query LIKE 'EXPLAIN (analyze, buffers,%' \gx ``` ```sql -[ 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: Уборка {#source-page-024} Удалим созданную базу данных и очистим список загружаемых библиотек. ```sql => \c student ``` ```text You are now connected to database "student" as user "student". ``` ```sql => DROP DATABASE pgpro_perfanalysis WITH (FORCE); ``` ```sql DROP DATABASE ``` ```sql => ALTER SYSTEM RESET shared_preload_libraries; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ## Page 25: Практика PPEM {#source-page-025} 1. В основном экземпляре создайте из шаблона demo базу данных stats\_ppem и подготовьте ее для работы с pgpro\_stats. 2. С помощью psql-консоли выполните запрос из оператора EXPLAIN п. 2 задания основной практики. 3. Модифицируйте запрос так, чтобы он ограничивал результаты определенным количеством строк. Выполните запрос. 4. В статистике, собранной расширением pgpro\_stats, найдите оба запроса и сравните их представления и планы. ### Комментарии преподавателя 2. Для удобства дальнейшей работы подключитесь к базе как пользователь student с помощью метакоманды \\c — student и включите в путь поиска схему bookings (или используйте запросы с квалифицированными именами таблиц): SELECT \* FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book\_ref = t.book\_ref; 3. Дополненный запрос: SELECT \* FROM bookings.bookings AS b JOIN bookings.tickets AS t ON b.book\_ref = t.book\_ref LIMIT 555; 4. В разделе Мониторинг/SQL-статистика удобно поставить фильтры на базу данных и пользователя и отсортировать данные по количеству возвращенных строк. Обратите внимание на представление константы в запросе из п. 3. ## Page 26: 1. Создание базы из шаблона и установка расширения pgpro_stats {#source-page-026} Выберите вкладку «Базы данных» и нажмите «Создать базу данных». При заполнении формы укажите шаблон — базу 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.