Мониторинг Postgresql
Мониторинг
Postgresql состоит из большого колличества компонентов. Эти компоненты взаимодействуют друг с другом.
В PostgreSQL есть, так называемая, подсистема Stats Collector, которая позволяет собирать статистику о работе этих подсистем
Эта статистика представлена в виде некоторого набора функций и вьюх (view)
Статистика может быть полезна для:
1. Мониторинга доступности, так как база это сервис, который предоставляет доступ к базе
2. Мониторинга клиентов и их деятельности
3. Мониторинга данных ( таблицы, индексы )
4. Оптимизации запросов
5. Отслеживания фоновых процессов
Для фиксации колличества ошибок можно использовать представление pg_stat_database, в котором можно ориентироваться xact_rollback
Поле xact_rollback показывает кол-во rollback и кол-во ошибок
С помощью статистики можно измерять кол-во транзакций(pg_stat_database) и кол-во запросов в секунду, данные показатели можно использовать для анализа производительности
Если сложить колличество commit и кол-во rollback и получить кол-во транзакций в секунду
В одну транзакцию может уложиться несколько запросов TPS (Transactions Per Second) — это показатель пропускной способности базы данных PostgreSQL, который показывает количество транзакций, обработанных базой данных за одну секунду. Для измерения этого показателя используется встроенный бенчмарк pgbench. Он выполняет одну и ту же последовательность SQL-команд, а затем вычисляет среднюю скорость транзакций
По умолчанию pgbench тестирует сценарий, основанный на TPC-B, который включает пять команд SELECT, UPDATE и INSERT для каждой транзакции.
В результате теста выводится отчёт, в котором указано, с какими параметрами запускался тест, количество запланированных и выполненных транзакций, а также значение TPS (количество транзакций в секунду). Чем это значение выше, тем производительней система.
QPS в PostgreSQL — это количество запросов в секунду
Для проверки этого показателя можно использовать следующий SQL-запрос:
with t1 as (select sum(calls) n from pg_stat_statements), t2 as (select sum(calls) n from pg_stat_statements, pg_sleep(1))
select t2.n-t1.n the_num_of_queries_per_second from t1,t2;
Также для приблизительной оценки QPS можно использовать формулу: QPS ≈ (1 / Среднее время выполнения запроса в секундах) × Количество ядер. Она связывает среднее время запроса, количество запросов в секунду и число доступных ядер процессора. При этом предполагается, что каждое ядро может обрабатывать по одному запросу за раз, а другие факторы, такие как память или доступ к диску, не являются узкими местами
Количество запросов в секунду можно получить по pg_stat_statements и просто просчитать сумму всех выполненных запросов
Следует мониторить количество воркеров вакуума. Autovacuum который чистит устаревшие версии строк, которые не нужны ни одной из транзакции и освобождает место в таблицах, индексах для новых строк. За сборщиком мусора необходимо следить, так как он отжирает большое количество ресурсов и клиентские запросы от этого начинают страдать.
Мониторить процесс avtovacuum следует его через вьюху pg_stat_activity
Есть смысл отслеживать слишком долгие транзакции, которые долго висят и ничего не делают. Это так называемые, stat idle-in-transaction. Такая транзакция удерживает блокировки, она мешает работать вакууму, как следствие – таблицы пухнут, они увеличиваются в размере, запросы, которые работают с этими таблицами, они начинают работать медленнее, потому что нужно лопатить все старые версии строк из памяти на диск и обратно.
Если какие-то процессы, которые работают уже очень долго, уже больше 10-20-30 минут для OLTP-нагрузки, то на них нужно уже обращать внимание и завершать
Бывает что клиент подключился, он удерживает коннект, но при этом ничего не делает, такой клиент находится в состоянии idle
Ждущие транзакции могут накапливаться и переполнять лимит подключений к базе данных.
Для отслеживания таких процессов можно использовать представление pg_stat_activity
Состояние клиента мы можем оценивать по полю stat.
Поля xact_start и query_start. Они, условно говоря, показывают время старта транзакции и время старта запроса. Мы берем функцию now(), которая показывает текущую отметку времени и вычитаем timestamp транзакции и запроса. И получаем длительность транзакции, длительность запроса.
Не всегда можно наладить производительность сервера изменяя настройки в файле postgresql.conf. Однако во многих случаях проблемы возникают из за неадекватных запросов в самом приложении
Чтобы обнаружить запросы, которые создают наибольшую нагрузку, можно использовать представление pg_stat_statements. На основе этого представления можно получить информацию по самым частым запросам, можно мониторить самые долгие запросы, которые можно отслеживать по полям: total_time, mean_time, blk_write_time и blk_read_time
Можно оценивать и мониторить самые тяжелые запросы в плане использования ресурсов, те, которые читают с диска, которые работают с памятью или, наоборот, создают какую-то пишущую нагрузку.
Можно также мониторить запросы, которые используют временные файлы или временные таблицы.
Фоновые процессы - чекпоинты или контрольные точки, autovacuum и репликация
Грязные» страницы в PostgreSQL — это страницы, которые были изменены и ещё не записаны на диск
Во время контрольной точки (checkpoint). Во время её выполнения все «грязные» страницы данных, находящиеся в памяти, сбрасываются на диск, а в файле журнала создаётся специальная запись контрольной точки
Во время работы bgwriter (background writer). Этот фоновый процесс записывает на диск «грязные» редко используемые страницы
Во время обработки запроса. Если нужной страницы нет в shared buffers, а страница-кандидат на вытеснение «грязная», бэкенд должен записать на диск эту страницу и только потом продолжить работу
Контрольные точки сбрасывают грязные страницы из области shared памяти на диск, затем создают контрольную точку. И эта контрольная точка уже дальше может использоваться как некое место при восстановлении, если вдруг PostgreSQL был завершен в аварийном порядке
Чтобы сбросить все «грязные» страницы на диск, нужно проделать некий объем записи. На системах с большим объемом памяти – это очень много. Если чекпоинты делаются очень часто в какой-то короткий интервал, то дисковая производительность будет очень сильно проседать. И клиентские запросы будут страдать от нехватки ресурсов
Через pg_stat_bgwriter по указанным можно мониторить количество случающихся чекпоинтов. Если за какой-то промежуток времени (за 10-15-20 минут, за полчаса) очень много чекпоинтов, например, 3-4-5, то это уже может быть проблемой. И уже нужно посмотреть в базе данных, посмотреть в конфигурации, что вызывает такое обилие чекпоинтов. Может быть, какая-то большая запись идет
Количество воркеров autovacuum в базе данных ограничено
Если все воркеры работают постоянно, то это значит, что autovacuum недонастроен, нужно поднимать лимиты, пересматривать настройки autovacuum и проверять конфигурацию конфигурацию.
Можно ли выставить индивидуальные параметры для очень больших таблиц?
Конфигурация postgresql
Параметры конфигурации postgresql, файлы конфигурации postgresql, управление параметрами postgresql на уровнях экземпляра и сеанса...
Подробнее здесь
Переполнение диска
Если переполнится диск, содержащий файлы WAL, это может привести к аварийному сбою сервера с последующим его отключением.
Если вы не можете освободить дополнительное пространство на диске, удалив какие-либо другие файлы, то можно перенести часть файлов базы данных на другие файловые системы, с помощью создания табличных пространств
О табличных пространствах здесь
Накопительная статистика
Для просмотра текущего состояния системы предназначены несколько предопределённых представлений, которые перечислены в Таблице 28.1
В дополнение к ним есть несколько других представлений, перечисленных в Таблице 28.2 позволяющих просмотреть собранную статистику
При интерактивном анализе статистики, или когда выполняются ресурсоёмкие запросы, получаемая статистика может оказаться смещённой из-за задержки между обращениями к отдельным показателям. Установив для параметра stats_fetch_consistency значение snapshot, можно минимизировать смещение ценой увеличения объёма памяти для кеширования лишних показателей статистики. И наоборот, когда известно, что обращения к статистике будут единичными, кешировать статистику, к которой уже обращались, не нужно, и этого можно избежать, установив для stats_fetch_consistency значение none. Сбросить снимок статистики или кешируемые значения (если таковые имеются) в текущей транзакции можно, вызвав функцию pg_stat_clear_snapshot. При следующем обращении к данным статистики будет создан новый снимок (в режиме snapshot), или запрошенные данные сохранятся в кеше (в режиме cache).
Часть информации в представлениях с динамическими статистическими данными, перечисленных в Таблице 28.1 скрыта по соображениям безопасности. Суперпользователи и роли с правами встроенной роли pg_read_all_statsмогут получить всю информацию о любом сеансе.
Источник: https://postgrespro.ru/docs/postgresql/16/monitoring-stats#MONITORING-STATS-SETUP
Накопительная статистика отслеживает обращения к таблицам и индексам как на уровне блоков на диске, так и на уровне отдельных строк
Можно также учитывать количество вызовов пользовательских функций и время, затраченное на их выполнение
https://postgrespro.ru/docs/postgresql/16/monitoring-stats
Обслуживающие процессы собирают статистику в рамках транзакций, затем эта статистика записывается в разделяемую память
Накопительная статистика собирается в PGDATA/pg_stat при штатной остановке сервера и считывается при его запуске, при аварийной остановке все счетчики сбрасываются
Статистика по отдельным индексам особенно полезна для определения того, какие индексы используются и насколько они эффективны
Набор представлений pg_statio_ и pg_stat_io полезны для определения эффективности кеша буфера.
В статистике ввода-вывода PostgreSQL фиксируется большинство случаев вызова ядра для выполнения ввода-вывода, но не разграничиваются случаи, когда обращение к данным, которые необходимо получить с диска или данным, которые уже находятся в кеше страниц ядра. Пользователям рекомендуется использовать статистические представления PostgreSQL в сочетании с утилитами операционной системы для получения более полной картины производительности ввода-вывода в базе данных.
Пример
Смотреть на активности сервера имеет смысл, когда какие-то активности на самом деле есть. Чтобы сымитировать нагрузку, можно использовать pgbench
postgres=#
postgres=# CREATE DATABASE admin_monitoring;
CREATE DATABASE
postgres=# \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
admin_monitoring=#
admin_monitoring=# --Включить сбор статистики
admin_monitoring=# ALTER SYSTEM SET track_io_timing=on;
ALTER SYSTEM
admin_monitoring=#
admin_monitoring=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
admin_monitoring=#
--утилита pgbench создает набор таблиц и заполняет их данными.
sudo -u postgres pgbench -i admin_monitoring
--Сбросим накопленную ранее статистику по базе данных
admin_monitoring=# SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)
admin_monitoring=#
--Сбросим также статистику экземпляра по вводу-выводу:
SELECT pg_stat_reset_shared('io');
--Запускаем тест TPC-B на несколько секунд:
sudo -u postgres pgbench -T 10 admin_monitoring
pgbench (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 2532
latency average = 3.948 ms
initial connection time = 3.005 ms
tps = 253.265114 (without initial connection time)
--Теперь можно посмотреть статистику обращений к таблицам в терминах строк
admin_monitoring=# SELECT *
FROM pg_stat_all_tables
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]-------+-----------------
relid | 16477
schemaname | public
relname | pgbench_accounts
seq_scan | 0
seq_tup_read | 0
idx_scan | 5064
idx_tup_fetch | 5064
n_tup_ins | 0
n_tup_upd | 2532
n_tup_del | 0
n_tup_hot_upd | 1217
n_live_tup | 0
n_dead_tup | 2062
n_mod_since_analyze | 2532
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
admin_monitoring=#
--И в терминах страниц
admin_monitoring=# SELECT *
FROM pg_statio_all_tables
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]---+-----------------
relid | 16477
schemaname | public
relname | pgbench_accounts
heap_blks_read | 21
heap_blks_hit | 15462
idx_blks_read | 274
idx_blks_hit | 12506
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit |
admin_monitoring=#
--Существуют аналогичные представления для индексов:
admin_monitoring=# SELECT *
FROM pg_stat_all_indexes
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]-+----------------------
relid | 16477
indexrelid | 16491
schemaname | public
relname | pgbench_accounts
indexrelname | pgbench_accounts_pkey
idx_scan | 5064
idx_tup_read | 6400
idx_tup_fetch | 5064
admin_monitoring=#
admin_monitoring=# SELECT *
FROM pg_statio_all_indexes
WHERE relid = 'pgbench_accounts'::regclass \gx
-[ RECORD 1 ]-+----------------------
relid | 16477
indexrelid | 16491
schemaname | public
relname | pgbench_accounts
indexrelname | pgbench_accounts_pkey
idx_blks_read | 274
idx_blks_hit | 12506
admin_monitoring=#
--Можно посмотреть общую статистику по базе данных:
admin_monitoring=# SELECT *
FROM pg_stat_database
WHERE datname = 'admin_monitoring' \gx
-[ RECORD 1 ]------------+------------------------------
datid | 16470
datname | admin_monitoring
numbackends | 1
xact_commit | 2580
xact_rollback | 6
blks_read | 382
blks_hit | 49109
tup_returned | 53066
tup_fetched | 6616
tup_inserted | 2538
tup_updated | 7604
tup_deleted | 0
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 7.997
blk_write_time | 0
session_time | 933821.191
active_time | 9252.903
idle_in_transaction_time | 646.222
sessions | 2
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | 2024-10-28 15:37:15.051918+03
admin_monitoring=#
Эти представления, в частности, могут помочь определить неиспользуемые индексы. Такие индексы не только бессмысленно занимают место на диске, но и тратят ресурсы на обновление при каждом изменении данных в таблице
Есть также представления для пользовательских и системных объектов (all, user, sys), для статистики текущей транзакции (pg_stat_xact*) и другие
Здесь есть много полезной информации о количестве произошедших взаимоблокировок, зафиксированных и отмененных транзакций, использовании временных файлов, ошибках подсчета контрольных сумм. Здесь же хранится статистика общего количества сеансов и количества прерванных по разным причинам сеансов
Столбец numbackends показывает текущее количество обслуживающих процессов, подключенных к базе данных.
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html
Статистика ввода-вывода на уровне сервера доступна в представлении pg_stat_io. Например, выполним контрольную точку и посмотрим количество операций чтения и записи страниц по типам процессов:
admin_monitoring=# CHECKPOINT;
CHECKPOINT
admin_monitoring=# SELECT backend_type, sum(hits) hits, sum(reads) reads, sum(writes) writes
FROM pg_stat_io
GROUP BY backend_type;
Представления для отслеживания выполнения
ANALYZE pg_stat_progress_analyze
CREATE INDEX, REINDEX pg_stat_progress_create_index
VACUUM pg_stat_progress_vacuum ( включая процессы автоочистки )
CLUSTER, VACUUM FULL pg_stat_progress_cluster
Создание базовой резервной копии pg_stat_progress_basebackup
COPY pg_stat_progress_copy
Следить за ходом выполнения некоторых потенциально долгих команд можно, выполняя запросы к соответствующим представлениям
Структуры представлений описаны в документации:
https://postgrespro.ru/docs/postgresql/16/progress-reporting
Конфигурация системы сбора статистики
track_activities - включает мониторинг текущих команд
track_counts - сбор статистики по обращениям к таблицами индексам
track_functions - отслеживание использования пользовательских функций (выключен по умолчанию)
track_io_timing мониторинг времени чтения и записи блоко (ввыключен по умолчанию)
track_wal_io_timing мониторинг времени записи WAL (выключен по умолчанию)
Эти параметры устанавливаются в postgresql.conf, поэтому они применяются ко всем серверным процессам, однако, используя команду SET, их можно включать и выключать в отдельных сеансах. Эти параметры с помощью команды SET могут только суперпользователи
Накопительная статистика собирается в общей памяти. Каждый процесс PostgreSQL собирает статистику локально, а затем обновляет общие данные с некоторым интервалом.
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html
Текущие активности
pg_stat_activity
pg_stat_activity - представление, которое отображает текущие активности всех обсуживающих и фоновых процессов
В представлении pg_stat_activity для каждого серверного процесса будет присутствовать по одной строке с информацией, относящейся к текущей деятельности этого процесса.
Все столбцы представления перечислены в таблице 28.3 pg_stat_activity
Источник: https://postgrespro.ru/docs/postgresql/16/monitoring-stats#MONITORING-PG-STAT-ACTIVITY-VIEW
pg_stat_activity показывает только текущие активности. Для получения информации об истории запросов можно использовать другие инструменты, например, pg_stat_statements или log_statement=all
Работа этого представления зависит от параметра track_activities, который включен по умолчанию
postgres=# \c megabase
You are now connected to database "megabase" as user "postgres".
megabase=#
megabase=# --Включить сбор статистики
ALTER SYSTEM SET track_io_timing=on;
ALTER SYSTEM
megabase=#
megabase=# --Перезапустить конфигурацию
SELECT pg_reload_conf();
-[ RECORD 1 ]--+--
pg_reload_conf | t
megabase=#
megabase=# --Выполнить какую либо активность
megabase=#
megabase=# --Сбросим накопленную ранее статистику по базе данных
megabase=# SELECT pg_stat_reset();
-[ RECORD 1 ]-+-
pg_stat_reset |
megabase=#
megabase=# --Выполнить какую либо активность
megabase=#
megabase=# -- Теперь можно посмотреть информацию о процессе
megabase=#
megabase=# select * from pg_stat_activity where datname = 'megabase';
-[ RECORD 1 ]----+------------------------------
datid | 16389
datname | megabase
pid | 776
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2024-12-15 01:26:18.141117+03
xact_start |
query_start |
state_change |
wait_event_type |
wait_event |
state | disabled
backend_xid |
backend_xmin | 843486
query_id |
query |
backend_type | client backend
megabase=#
datid - oid базы данных к которой подключен серверный процесс
datname - имя базы данных к которой подключен серверный процесс
pid - идентификатор процесса этого серверного процесса (ID обслуживающего процесса сессии)
ID обслуживающего процесса сессии, можно получить используя функцию pg_backend_pid()
megabase=#
megabase=# SELECT pg_backend_pid();
-[ RECORD 1 ]--+----
pg_backend_pid | 776
usesysid - OID пользователя, подключённого к этому серверному процессу
usename - имя пользователя
application_name - приложение которое подключено к серверному процессу
client_addr - ip-адрес клиента или null если клиент подключен через сокет unix
client_port - tcp порт используемый клиентом или -1 если используется сокет unix
backend_start - Время запуска процесса (время подключения клиента к серверу)
query_start - Время начала выполнения активного в данный момент запроса
state_change - Время последнего изменения состояния
wait_event_type - Тип события, которого ждёт обслуживающий процесс, если это ожидание имеет место; в противном случае — NULL
wait_event - Имя ожидаемого события, если обслуживающий процесс находится в состоянии ожидания, а в противном случае — NULL
state - Общее текущее состояние этого серверного процесса. Возможные значения:
active: серверный процесс выполняет запрос
idle: серверный процесс ожидает новой команды от клиента
idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос
disabled: Это состояние отображается для серверных процессов, у которых параметр track_activities отключён
backend_xid - Идентификатор транзакции верхнего уровня данного серверного процесса, если есть
backend_xmin - Текущая граница xmin для серверного процесса
query_id - Идентификатор последнего запроса этого серверного процесса. Если state имеет значение active, то в этом поле отображается идентификатор запроса, выполняемого в данный момент. Если процесс находится в любом другом состоянии, то в этом поле отображается идентификатор последнего выполненного запроса. По умолчанию идентификаторы запросов не вычисляются, поэтому данное поле будет иметь значение NULL, если не включён параметр compute_query_id или если не загружен сторонний модуль, вычисляющий идентификаторы запросов
query - Текст последнего запроса этого серверного процесса. Если state имеет значение active, то в этом поле отображается запрос, который выполняется в настоящий момент. Если процесс находится в любом другом состоянии, то в этом поле отображается последний выполненный запрос
backend_type - Тип текущего серверного процесса. Возможные варианты: autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, archiver, standalone backend, startup, walreceiver, walsender и walwriter
Типы событий ожидания
Типы событий ожидания (wait_event_type) можно посмотреть в таблице 28.4
Создадим таблицу, добавим данные и запустим два сеанса:
postgres=# \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
admin_monitoring=#
admin_monitoring=# CREATE TABLE t(n integer);
CREATE TABLE
admin_monitoring=#
admin_monitoring=# INSERT INTO t VALUES(42);
INSERT 0 1
admin_monitoring=#
admin_monitoring=# --Первый сеанс изменяет таблицу и не закрывает транзакцию
admin_monitoring=# BEGIN;
BEGIN
admin_monitoring=*# UPDATE t SET n = n + 1;
UPDATE 1
admin_monitoring=*#
postgres=# --Второй сеанс пытается изменить ту же строку
postgres=# \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
admin_monitoring=#
admin_monitoring=# UPDATE t SET n = n + 2;
-- ....Ожидание блокировки
--Посмотрим информацию об обслуживающих процессах
SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend' \gx
-[ RECORD 1 ]----+-----------------------------------------------------------------------------
pid | 12525
query | UPDATE t SET n = n + 1;
state | idle in transaction
wait_event | ClientRead
wait_event_type | Client
pg_blocking_pids | {}
-[ RECORD 2 ]----+-----------------------------------------------------------------------------
pid | 13839
query | UPDATE t SET n = n + 2;
state | active
wait_event | transactionid
wait_event_type | Lock
pg_blocking_pids | {12525}
-[ RECORD 3 ]----+-----------------------------------------------------------------------------
pid | 14302
query | SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid)+
| FROM pg_stat_activity +
| WHERE backend_type = 'client backend'
state | active
wait_event |
wait_event_type |
pg_blocking_pids | {}
idle in transaction - состояние, которое говорит о том, что сеанс начал транзакцию, но ничего не делает, поэтому транзакция осталась незавершенная
Данное состояние может быть ошибкой приложения
В арсенале администратора имеется параметр idle_in_transaction_session_timeout, позволяющий принудительно завершать сеансы, в которых транзакция простаивает больше указанного времени
Также имеется параметр idle_session_timeout — принудительно завершает сеансы, простаивающие больше указанного времени вне транзакции
Можно также завершить заблокированный сеанс вручную, для этого нужно узнать номер заблокированного процесса при помощи функции pg_blocking_pids
admin_monitoring=# SELECT pid AS blocked_pid
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND cardinality(pg_blocking_pids(pid)) > 0;
blocked_pid
-------------
13839
(1 row)
Блокирующий процесс можно вычислить и без функции pg_blocking_pids, используя запросы к таблице блокировок. Запрос покажет две строки: одна транзакция получила блокировку (granted), а другая ее ожидает.
admin_monitoring=# SELECT locktype, transactionid, pid, mode, granted
FROM pg_locks
WHERE transactionid IN (
SELECT transactionid FROM pg_locks WHERE pid = 13839 AND NOT granted
);
locktype | transactionid | pid | mode | granted
---------------+---------------+-------+---------------+---------
transactionid | 3309 | 13839 | ShareLock | f
transactionid | 3309 | 12525 | ExclusiveLock | t
(2 rows)
admin_monitoring=#
Выполнение запроса можно прервать функцией pg_cancel_backend. В нашем случае транзакция простаивает, так что просто прерываем сеанс, вызвав pg_terminate_backend
admin_monitoring=#
admin_monitoring=# SELECT pg_terminate_backend(b.pid)
FROM unnest(pg_blocking_pids(13839)) AS b(pid);
pg_terminate_backend
----------------------
t
(1 row)
admin_monitoring=#
Функция unnest нужна, поскольку pg_blocking_pids возвращает массив идентификаторов процессов, блокирующих искомый обслуживающий процесс. В нашем примере блокирующий процесс один, но в общем случае их может быть несколько
Проверим состояние обслуживающих процессов
admin_monitoring=#
admin_monitoring=# SELECT pid, query, state, wait_event, wait_event_type
FROM pg_stat_activity
WHERE backend_type = 'client backend' \gx
-[ RECORD 1 ]---+------------------------------------------------------
pid | 13839
query | UPDATE t SET n = n + 2;
state | idle
wait_event | ClientRead
wait_event_type | Client
-[ RECORD 2 ]---+------------------------------------------------------
pid | 14302
query | SELECT pid, query, state, wait_event, wait_event_type+
| FROM pg_stat_activity +
| WHERE backend_type = 'client backend'
state | active
wait_event |
wait_event_type |
admin_monitoring=#
Осталось только два, причем заблокированный успешно завершил транзакцию.
Представление pg_stat_activity показывает информацию не только про обслуживающие процессы, но и про служебные фоновые процессы экземпляра:
admin_monitoring=# SELECT pid, backend_type, backend_start, state
FROM pg_stat_activity;
pid | backend_type | backend_start | state
-------+------------------------------+-------------------------------+--------
1020 | logical replication launcher | 2024-11-04 15:26:56.418787+03 |
1018 | autovacuum launcher | 2024-11-04 15:26:56.419402+03 |
13839 | client backend | 2024-11-04 16:40:33.891827+03 | idle
14302 | client backend | 2024-11-04 16:43:46.62887+03 | active
1016 | background writer | 2024-11-04 15:26:56.417504+03 |
1014 | checkpointer | 2024-11-04 15:26:56.416861+03 |
1017 | walwriter | 2024-11-04 15:26:56.418022+03 |
(7 rows)
admin_monitoring=#
Сравним с тем, что показывает операционная система:
~$ sudo head -n 1 /var/lib/postgresql/14/main/postmaster.pid
1001
~$ ps -o pid,command --ppid 1001
PID COMMAND
1014 postgres: 14/main: checkpointer
1016 postgres: 14/main: background writer
1017 postgres: 14/main: walwriter
1018 postgres: 14/main: autovacuum launcher
1019 postgres: 14/main: stats collector
1020 postgres: 14/main: logical replication launcher
13839 postgres: 14/main: postgres admin_monitoring [local] idle
Найти зависшие процессы
Например какой то процесс делает огромный запрос к базе, данный запрос падает по таймауту, но сам процесс продолжает свое существование до тех пор пока не получит результат от базы, через какое то время снова делает огромный запрос к базе и снова запрос падает по таймауту, следом за первым процессом может появиться второй и третий процесс, которые будут делать аналогичный запрос к базе вместе или по очереди. В итоге данные процессы будут сильно грузить базу данных и тормозить выполнение других не менее важных запросов, тут уже зависит от пропускной способности ввода/вывода и мощности сервера.
Возможны другие причины, например ошибка сети или клиент который внезапно потерял соединение до получения ответа
Запросы, которые падают по таймауту можно увидеть в логах postgres
Процессы, которые долго выполняются можно увидеть с помощью следующего запроса:
-- Выборка только по имени пользователя:
SELECT usename, pid, now() - backend_start AS duration, state, query FROM pg_stat_activity WHERE usename='www' ORDER BY duration;
Для завершения работы зависшего процесса:
#Мягкий вариант SIGINT:
SELECT pg_cancel_backend(23000);
#Жесткий вариант SIGTERM:
SELECT pg_terminate_backend(23000);
Возможно убить процесс с помощью kill, однако не рекомендуется, так как база данных может перейти в режим восстановления или перезагрузки СУБД
Найти самые длительные запросы
postgres=# SET track_activities = on;
postgres=# SELECT pg_reload_conf();
-[ RECORD 1 ]--+--
pg_reload_conf | t
postgres=# SELECT pid, datname, usename, application_name, query,
now() - query_start AS duration_query,
wait_event_type, wait_event, state, backend_type
FROM pg_stat_activity
WHERE datname = 'postgres'
ORDER BY duration_query;
Найти самые длительные транзакции
postgres=# SELECT pid, datname, usename, application_name, query,
now() - xact_start AS duration_transaction,
wait_event_type, wait_event, state, backend_type
FROM pg_stat_activity
WHERE datname = 'postgres'
ORDER BY duration_transaction;
Вывод информации о процессе по id процесса
postgres=# SELECT pid, datname, usename, application_name, query,
now() - xact_start AS duration_transaction, now() - query_start AS duration_query,
wait_event_type, wait_event, state, backend_type
FROM pg_stat_activity
WHERE pid = 22673
ORDER BY duration_query;
Распределение серверных процессов по состояниям
active - выполнение запроса;
idle - ожидание новой команды от клиента;
idle in transaction - серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос;
idle in transaction (aborted) - серверный процесс находится внутри транзакции, но один из операторов в транзакции вызывал ошибку;
fastpath function call - выполнение fast-path функции;
disabled – у серверного процесса отключён параметр track_activities
Количество соединений по состояниям
Получить общее количество соединений по состояниям позволяет следующая группировка:
postgres=# SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
-[ RECORD 1 ]-
state |
count | 5
-[ RECORD 2 ]-
state | active
count | 1
-[ RECORD 3 ]-
state | idle
count | 3
--Получить общее количество соединений по состояниям
newbase=# SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
state | count
--------+-------
| 5
active | 1
(2 rows)
Длительность текущих активных транзакций и запросов
SELECT
datname,
usename,
now() - xact_start AS TransactionDuration,
now() - query_start as QueryDuration
FROM pg_stat_activity
WHERE state = 'active';
Представление pg_stat_database
postgres=# SELECT * FROM pg_stat_database WHERE datname = 'megabase';
-[ RECORD 1 ]------------+------------------------------
datid | 16389
datname | megabase
numbackends | 0
xact_commit | 8496
xact_rollback | 0
blks_read | 421
blks_hit | 774721
tup_returned | 2793999
tup_fetched | 364833
tup_inserted | 243
tup_updated | 20
tup_deleted | 242
conflicts | 0
temp_files | 0
temp_bytes | 0
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 33.951
blk_write_time | 0
session_time | 1429226.907
active_time | 71597.383
idle_in_transaction_time | 0
sessions | 1318
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset | 2024-12-22 11:07:45.915067+03
numbackends - Количество обслуживающих процессов, в настоящее время подключённых к этой базе данных. Это единственный столбец в представлении, значение в котором отражает текущее состояние; все другие столбцы возвращают суммарные значения со времени последнего сброса статистики.
xact_commit - Количество зафиксированных транзакций в этой базе данных
xact_rollback - Количество транзакций в этой базе данных, для которых был выполнен откат транзакции
blks_read - Количество прочитанных дисковых блоков в этой базе данных
blks_hit - Сколько раз дисковые блоки обнаруживались в буферном кеше, так что чтение с диска не потребовалось (в значение входят только случаи обнаружения в буферном кеше PostgreSQL, а не в кеше файловой системы ОС)
tup_returned bigint - Количество строк, возвращённое запросами в этой базе данных
tup_fetched bigint - Количество строк, извлечённое запросами в этой базе данных
tup_inserted bigint - Количество строк, вставленное запросами в этой базе данных
tup_updated bigint - Количество строк, изменённое запросами в этой базе данных
tup_deleted bigint - Количество строк, удалённое запросами в этой базе данных
deadlocks bigint - Количество взаимных блокировок, зафиксированное в этой базе данных
blk_read_time double precision - Время, которое затратили обслуживающие процессы в этой базе на чтение блоков из файлов данных, в миллисекундах (если включён параметр track_io_timing; в противном случае 0).
blk_write_time double precision - аналогично для записи
session_time double - Длительность сеансов в этой базе в миллисекундах (обратите внимание, что статистика обновляется только при изменении состояния сеанса, поэтому, если сеансы простаивают в течение длительного времени, оно не будет учитываться)
active_time - Время, затраченное на выполнение операторов SQL в этой базе, в миллисекундах (соответствует состояниям active и fastpath function call в pg_stat_activity)
idle_in_transaction_time - Время простоя в транзакциях в этой базе в миллисекундах (соответствует состояниям idle in transaction и idle in transaction (aborted) в pg_stat_activity)
sessions - Общее количество сеансов, относящихся к этой базе
sessions_abandoned - Количество сеансов в этой базе данных, прерванных из-за потери соединения с клиентом
sessions_fatal - Количество сеансов в этой базе данных, прерванных из-за критических ошибок
sessions_killed - Количество сеансов в этой базе данных, прерванных из-за вмешательства оператора
stats_reset - Последнее время сброса этих статистических данных
Источник: https://postgrespro.ru/docs/postgresql/14/monitoring-stats#MONITORING-PG-STAT-DATABASE-VIEW
Cуммарное количество транзакций
Для понимания нагрузки на базу данных хорошо знать общий объём транзакций за определенный период времени
Сумма xact_commit и количество xact_rollback – суммарное количество транзакций за период с момента сброса статистических данных stats_reset
postgres=# SELECT
datname,
xact_commit + xact_rollback ,
stats_reset
FROM pg_stat_database
where datname = 'megabase';
-[ RECORD 1 ]---------
datname | megabase
?column? | 407066
stats_reset |
postgres=#
Сумма xact_commit и количество xact_rollback – суммарное количество транзакций за период с момента сброса статистических данных stats_reset
Дополнительные статистические функции
Дополнительные функции, связанные со сбором статистики, перечислены в Таблице 28.32
pg_stat_reset_single_table_counters ( oid ) - Обнуляет статистику по отдельной таблице или индексу в текущей базе данных
Функция pg_stat_reset() также сбрасывает счётчики, на основании которых процесс автоочистки принимает решение о запуске очистки или анализа. Поэтому после сброса счётчиков рекомендуется выполнить ANALYZE на уровне базы
--Обнулить все статистические счётчики в текущей базе данных
select pg_stat_reset ();
-- Получить идентификатор серверного процесса обслуживающий текущий сеанс
postgres=# select pg_backend_pid () ;
-[ RECORD 1 ]--+------
pg_backend_pid | 14768
--Получить запись с информацией о серверном процессе с заданным ID
postgres=# select pg_stat_get_activity(17654);
-[ RECORD 1 ]--------+--------------------------------------------------------------------------------------------------------------------------------
pg_stat_get_activity | (17745,17654,10,psql,disabled,"",Client,ClientRead,,,"2024-12-17 10:35:12.739281+00",,,,-1,,,"client backend",f,,,,,,,f,,f,f,,)
Статистические функции на уровне серверных процессов перечислены в Таблице 28.33.
Представление pg_stat_all_tables
megabase=# SELECT * FROM pg_stat_all_tables WHERE relname = 'content_table';
-[ RECORD 1 ]-------+--------------
relid | 16396
schemaname | public
relname | content_table
seq_scan | 4543
seq_tup_read | 1163008
idx_scan | 368
idx_tup_fetch | 343
n_tup_ins | 0
n_tup_upd | 21
n_tup_del | 0
n_tup_hot_upd | 21
n_live_tup | 0
n_dead_tup | 1
n_mod_since_analyze | 21
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
schemaname - Имя схемы, в которой расположена эта таблица
relname - relname
seq_scan - Количество последовательных чтений, произведённых в этой таблице
seq_tup_read - Количество «живых» строк, прочитанных при последовательных чтениях
idx_scan - Количество сканирований по индексу, произведённых в этой таблице
idx_tup_fetch - Количество «живых» строк, отобранных при сканированиях по индексу
n_tup_ins - Количество вставленных строк
n_tup_upd - Количество изменённых строк
n_tup_del - Количество удалённых строк
n_tup_hot_upd - Количество строк, обновлённых в режиме HOT (т. е. без отдельного изменения индекса)
n_live_tup - Оценочное количество «живых» строк
n_dead_tup - Оценочное количество «мёртвых» строк
n_mod_since_analyze - Оценочное число строк, изменённых в этой таблице с момента последнего сбора статистики
n_ins_since_vacuum - Примерное число строк, вставленных в эту таблицу с момента последней очистки
last_vacuum - Время последней очистки этой таблицы вручную (VACUUM FULL не учитывается)
last_autovacuum - Время последней очистки таблицы фоновым процессом автоочистки
last_analyze - Время последнего выполнения сбора статистики для этой таблицы вручную
last_autoanalyze - Время последнего выполнения сбора статистики для этой таблицы фоновым процессом автоочистки
vacuum_count - Сколько раз очистка этой таблицы была выполнена вручную (VACUUM FULL не учитывается)
autovacuum_count - Сколько раз очистка этой таблицы была выполнена фоновым процессом автоочистки
analyze_count - Сколько раз сбор статистики для этой таблицы был выполнен вручную
autoanalyze_count - Сколько раз сбор статистики для этой таблицы был выполнен фоновым процессом автоочистки
Определение наиболее нагруженных таблиц
pg_stat_all_tables - представление для статистики обращений к таблицам. Позволяет оценить, общий объём операций к таблице, например insert, update, delate и др.
postgres=# SELECT
relname,
n_tup_upd+n_tup_ins+n_tup_del AS operationsAmount
FROM pg_stat_all_tables WHERE relname = 'mega.table1'
ORDER BY operationsAmount DESC
--Определить наиболее часто используемые таблицы в БД
newbase=# SELECT
relname,
n_tup_upd+n_tup_ins+n_tup_del AS operationsAmount
FROM pg_stat_all_tables
ORDER BY operationsAmount DESC;
Отношение сканирований по индексам к последовательным сканированиям
Для анализа эффективности чтения данных в конкретной таблице можно получить соотношение запросов, выполненных с использованием индексов к количеству запросов, читающих данные путём последовательного сканирования таблиц
Источник: https://club.directum.ru/post/363218
newbase=# SELECT
relname,
seq_scan,
idx_scan,
idx_scan/seq_scan as IndexStat
FROM pg_stat_all_tables
WHERE seq_scan <> 0
ORDER BY IndexStat DESC;
relname | seq_scan | idx_scan | indexstat
--------------------+----------+----------+-----------
pg_db_role_setting | 5 | 63016 | 12603
pg_index | 1 | 145 | 145
pg_database | 15745 | 74778 | 4
pg_namespace | 1 | 4 | 4
pg_tablespace | 7 | 26 | 3
pg_authid | 85 | 127 | 1
pg_am | 132 | 0 | 0
pg_subscription | 1 | 0 | 0
pg_auth_members | 56 | 30 | 0
pg_class | 525 | 284 | 0
(10 rows)
newbase=#
Проверить колличество мертвых кортежей
Мертвые кортежи могут занимать значительный объем дискового пространства, что ухудшает производительность
Столбец n_dead_tup отображает кол-во мертвых кортежей
SELECT n_dead_tup FROM pg_stat_all_tables WHERE relname = 'content_table';
Источник: https://stupin.su/wiki/postgresql16_pg_stat_all_tables_indexes/ Определение количества операций последовательного сканирования
Определение количества операций последовательного сканирования пользовательских таблиц
Покажет топ-10 пользовательских таблиц, по которым было больше всего операций последовательного сканирования
SELECT schemaname, relname, seq_scan, idx_scan, seq_tup_read, seq_tup_read / seq_scan as avg_seq_read FROM pg_stat_all_tables WHERE seq_scan > 0 AND schemaname not in ('pg_catalog','information_schema','pg_toast') ORDER BY avg_seq_read DESC LIMIT 10;
Поиск неиспользуемых или редко используемых таблиц
Поиск неиспользуемых или редко используемых таблиц
Покажет таблицы, у которых операции последовательного и индексного сканирования не превышали 10
SELECT schemaname, relname, seq_scan, idx_scan, (COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) as total_scans_performed FROM pg_stat_all_tables WHERE (COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) < 10 AND schemaname not in ('pg_catalog', 'information_schema', 'pg_toast') ORDER BY 5 DESC;
Анализ активности операций записи по пользовательским таблицам
Анализ активности операций записи по пользовательским таблицам
окажет количество операций последовательного и индексного сканирования по таблицам, а также количество операций вставки, удаления и обновления строк таблиц
SELECT st.schemaname,st.relname, pg_size_pretty(pg_total_relation_size(st.relid)) as Total_Size, st.seq_scan, st.idx_scan, st.n_tup_ins, st.n_tup_upd, st.n_tup_del FROM pg_stat_all_tables st WHERE st.schemaname not in ('pg_catalog','information_schema','pg_toast') ORDER BY Total_Size DESC;
Проверка количества «живых» и «мёртвых» строк пользовательских таблиц и статуса VACUUM
Проверка количества «живых» и «мёртвых» строк пользовательских таблиц и статуса VACUUM
Покажет количество «мёртвых» строк на таблицах, а также время последнего прихода AUTOVACUUM в таблицу
SELECT schemaname, relname, n_live_tup, n_dead_tup, n_dead_tup * 100 / (case when n_live_tup > 0 then n_live_tup else 1 end) as dead_rows_percent, last_autovacuum, last_autoanalyze, n_dead_tup, relname FROM pg_stat_all_tables WHERE schemaname not in ('pg_catalog','information_schema','pg_toast') ORDER BY n_dead_tup DESC;
Представление pg_stat_all_indexes
pg_stat_all_indexes - представляет информацию по созданным в базе данных индексам
Устаревшие индексы
newbase=# SELECT
indexrelname,
relname,
idx_tup_read/idx_tup_fetch as stats
FROM pg_stat_all_indexes
WHERE idx_tup_fetch <> 0
ORDER BY stats DESC;
indexrelname | relname | stats
-----------------------------------------------+---------------+-------
pg_toast_2618_index | pg_toast_2618 | 1
pg_proc_oid_index | pg_proc | 1
pg_proc_proname_args_nsp_index | pg_proc | 1
pg_type_oid_index | pg_type | 1
pg_attribute_relid_attnum_index | pg_attribute | 1
pg_class_oid_index | pg_class | 1
....
..
.
idx_tup_read/idx_tup_fetch – это отношение записей из индекса, возвращённых в запросах по этому индексу, к общему числу записей, для которых пришлось обращаться к родительским таблицам. Если этот коэффициент меньше единицы, значит много данных читается в обход индекса, поэтому его необходимо обновить.
Источник: https://club.directum.ru/post/363218
Дополнительная статистика
Расширения в поставке:
pg_stat_statements статистика по запросам
pgstattuple статистика по версиям строк
pg_buffercache состояние буферного кеша
Другие расширения:
pg_wait_sampling статистика ожиданий
pg_stat_kcache статистика по процессору и вводу-выводу
pg_qualstats статистика по предикатам
Существуют расширения, позволяющие собирать дополнительную статистику, как входящие в поставку, так и внешние
Например, расширение pg_stat_statements сохраняет информациюо запросах, выполняемых СУБД; pg_buffercache позволяет заглянутьв содержимое буферного кеша и т. п.
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html
Использование pg_stat_statements
Модуль pg_stat_statements предоставляет возможность отслеживать статистику планирования и выполнения сервером всех операторов SQL.
Подробнее здесь
Внешний мониторинг
Универсальные системы мониторинга:
Zabbix, Munin, Cacti...
в облаке: Okmeter, NewRelic, Datadog...
Системы мониторинга PostgreSQL:
pg_profile, pgpro_pwr
PGObserver
PostgreSQL Workload Analyzer (PoWA)
Open PostgreSQL Monitoring (OPM)
Расширение pg_profile позволяет строить снимки статических данных и сравнивать их, выявляя ресурсоемкие операции и их динамику
https://postgrespro.ru/docs/enterprise/16/pgpro-pwr
Неполный, но представительный список систем мониторинга можно посмотреть на странице https://wiki.postgresql.org/wiki/Monitoring
Для более глубокого погружения в эту тему можно прочитать книгу Алексея Лесовского «Мониторинг PostgreSQL»: https://edu.postgrespro.ru/monitoring.pdf
Источник: https://edu.postgrespro.ru/16/dba1-16/dba1_12_admin_monitoring.html
Мониторинг в GUI-клиентах
Для мониторинга в графических клиентах, можно использовать бесплатный pgAdmin
OOM Killer
Eсли в какой-то момент, например, ночью выполнялась какая-то задача, пришел OOM-killer и завершил принудительно дочерний процесс PostgreSQL, то в этом случае PostgreSQL завершает соединение всех клиентов, сбрасывает область shared памяти и начинает восстановление с последней контрольной точки. И пока длится это восстановление с контрольной точки, база не принимает подключения, т. е. эту ситуацию можно оценивать, как downtime
OOM Killer в PostgreSQL — это системный процесс, который вызывается, когда в операционной системе наблюдается нехватка оперативной памяти. Он убивает самый прожорливый процесс, в том числе PostgreSQL
Чтобы избежать вызова OOM Killer и предотвратить уничтожение процессов, можно:
Увеличить объём пространства подкачки, если физической памяти недостаточно
Изменить конфигурацию сервера, если памяти не хватает по вине самого PostgreSQL. В некоторых случаях может помочь уменьшение конфигурационных параметров, связанных с памятью, а именно shared_buffers, work_mem и hash_mem_multiplier
Включить режим строгого выделения памяти, чтобы уменьшить вероятность вызова OOM Killer
TOAST
Таблицы TOAST в PostgreSQL положительно влияют на производительность. Они позволяют эффективно хранить большие объекты данных, разбивая их на небольшие фрагменты и сохраняя отдельно от основной таблицы.
Это улучшает производительность запросов и индексирования и уменьшает объём дискового пространства, необходимого для хранения данных. В результате происходит более быстрое получение данных и более эффективное выполнение запросов
Подробнее о TOAST здесь
Журналы Postgresql
Журналы PostgreSQL — текстовые файлы, в которых отображается информация о том, что в данный момент происходит в системе баз данных. Это включает в себя сведения о том, кто имеет доступ и к какому компоненту, какие ошибки произошли, что изменилось в настройках, какие запросы находятся в процессе выполнения и какие транзакции выполняются.
Подробнее о журнале Postgresql здесь
Вывод 20 самых больших таблиц в кластере
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
10 самых больших по объему ячеек атрибута
SELECT t.user_id, pg_column_size(t.description) as filesize FROM test.test t ORDER BY filesize DESC LIMIT 10;
Размер таблицы
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_total_relation_size('"test"."table"'))" -x demo
20 самых длинных строк в таблице
sudo -u postgres psql -c "select user_id, length(description) as length from users order by length desc limit 20" -x demo
Сортировка по возрастанию и вывод первого минимального значения
demo=# SELECT * FROM bookings.aircrafts ORDER BY range LIMIT 1;
aircraft_code | model | range
---------------+--------------------+-------
CN1 | Cessna 208 Caravan | 1200
(1 row)
-- равнозначно
demo=# SELECT MIN( range ) FROM bookings.aircrafts ;
min
------
1200
(1 row)
Сортировка по убыванию и вывод последнего максимального значения
demo=# SELECT * FROM bookings.aircrafts ORDER BY range DESC LIMIT 1;
aircraft_code | model | range
---------------+----------------+-------
773 | Boeing 777-300 | 11100
(1 row)
-- равнозначно
demo=# SELECT MAX( range ) FROM bookings.aircrafts ;
max
-------
11100
(1 row)
Представление pg_locks
Просмотр блокировок
Подробнее о блокировках здесь
Оптимизация производительности
Оптимизация производительности, подробнее здесь
Оптимизация запросов, подробнее здесь
Очистка базы данных
Исторические данные образуются из за многоверсионности.
В табличных страницах копятся старые версии строк.
В страницах индексов накапливаются ссылки на старые версии строк.
Какое то время исторические данные нужны, чтобы транзакции могли работать со своими снимками данных. Однако строки становятся не актуальными для снимков даных, становятся "мертвыми", накапливаются и поиск запросов среди этих строк замедляется.
Очистка подразумевает под собой удаление старых версий строк и индексов.
Подробнее здесь
Использование терминального клиента psql
Подробнее о возможностях psql здесь
Использование EXPLAIN
Подробнее об использовании EXPLAIN здесь
Дополнительно: Управление планировщиком в Postgresql
Средства операционной системы
Анализ дисковой активности
В конспекте рассматривается, с помощью каких инструментов можно отследить дисковую активность и производительность диска
Подробнее здесь
Использование lsof
lsof - утилита для вывода информации о том, какие файлы используются процессами
Например с помощью lsof можно получить дополнительную информацию связанную с процессом ( по id процесса или по имени системного пользователя )
Найти все открытые файлы в указанном каталоге или узнать все процессы, которые используют файл
С помощью lsof можно отследить операции, которые выполняются процессом в режиме реального времени
Подробнее здесь
#Информация в режиме реального времени по имени процесса
lsof -s -c postgres +r2
#Информация в режиме реального времени по ID процесса
lsof -s -p 22672 +r2
Информация о процессах
Отображение информации о процессах Postgres зависит от настроек операционной системы
Если включен параметр update_process_title, то в имени процесса отражается его текущее состояние ( idle, waiting и др. ). После изменения данного параметра в postgresql.conf, необходимо перечитать конфигурацию
Параметр cluster_name задает имя экземпляра, по которому экземпляр можно отличать в списке процессов
Для мониторинга использования системных ресурсов ( процессор, память, диски ), в unix можно использовать различные инструменты: iostat, vmstat, sar, top и др.
#Пример использования команды ps для просмотра процессов postgres
ps -auxww | grep ^postgres
postgres 1007 0.0 0.3 221464 30856 ? Ss окт26 0:05 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
postgres 1024 0.0 0.1 221592 13364 ? Ss окт26 0:00 postgres: 14/main: checkpointer
postgres 1026 0.0 0.1 221464 9560 ? Ss окт26 0:00 postgres: 14/main: background writer
postgres 1027 0.0 0.1 221464 11768 ? Ss окт26 0:00 postgres: 14/main: walwriter
postgres 1028 0.0 0.1 222164 9676 ? Ss окт26 0:02 postgres: 14/main: autovacuum launcher
postgres 1029 0.0 0.0 76328 7452 ? Ss окт26 0:02 postgres: 14/main: stats collector
postgres 1030 0.0 0.1 221892 7872 ? Ss окт26 0:00 postgres: 14/main: logical replication launcher
postgres 300581 0.0 0.1 24940 9180 pts/1 S+ 08:09 0:00 /usr/lib/postgresql/14/bin/psql
postgres 300582 0.0 0.1 222416 12636 ? Ss 08:09 0:00 postgres: 14/main: postgres postgres [local] idle
Примечание:
Для корректной работы команды ps в Linux Alpine, необходимо установить пакет:
apk add procps
idle - ожидание команды клиента
idle in transaction - ожидание команды клиента внутри блока BEGIN
К виду деятельности добавляется waiting, если в настоящий момент серверный процесс ожидает высвобождения блокировки, которую держит другой сеанс
Например SELECT waiting будет означать что процесс ожидает высвобождения блокировки, которую держит другой сеанс
В более сложных случаях может потребоваться обращение к системному представлению pg_locks, для того чтобы определить, кто кого блокирует
Представление pg_locks даёт доступ к информации о блокировках, удерживаемых активными процессами на сервере баз данных
https://postgrespro.ru/docs/postgresql/16/view-pg-locks
Если установлено значение cluster_name, имя кластера также будет показываться в выводе команды ps
Дополнительный материал: https://postgrespro.ru/docs/postgresql/16/monitoring-stats
Подробнее об инструментах для вывода информации о процессах здесь
Использование strace
Подробнее о возможностях strace здесь
audit
Использование audit
Мониторинг событий в linux с помощью auditd
Инструменты linux которые могут быть полезны
Подробнее Источник 1
Подробнее Источник 2
Подробнее Источник 3