psql_diagpack. Дополнительные инструменты мониторинга
В исходном дистрибутиве установлено по умолчанию: нет.
Связанные компоненты: отсутствуют.
Схема размещения:
ext.
Описание
Расширение psql_diagpack создает представления, которые отображают в удобном для восприятия пользователем виде информацию из системных представлений. Список представлений:
| Представление | Отображаемая информация |
|---|---|
dba_query_cpu_and_waits_time | Список запросов, отсортированных по убыванию времени, проведенного на CPU или событиях ожидания, отличных от IO |
dba_query_io_time | Список запросов, отсортированных по убыванию времени, проведенного на IO |
dba_query_run_time | Список запросов, отсортированных по убыванию общего времени выполнения |
dba_locks | Дерево блокировок |
dba_standby_check | Отчет по статусу репликации |
dba_top_tables | Список самых больших таблиц (полный размер) в БД |
dba_top_objects | Список самых больших отношений в БД |
dba_activity | Расширенный отчет по активности процессов |
dba_activity_vacuum | Отчет о текущей активности процессов очистки (AUTOVACUUM+VACUUM+VACUUM FULL) |
dba_waits | Отчет о распределении процессов по событиям ожидания |
dba_bloat_wastedbytes | Список таблиц, отсортированных по объему пространства, не занятого для хранения актуальных версий записей |
dba_bloat_tbloat | Список таблиц, отсортированных по доле пространства, не занятого для хранения актуальных версий записей |
dba_unused_indexes | Список неиспользуемых индексов |
dba_duplicated_indexes | Список индексов, перекрываемых другими индексами |
Настройка
Проверка установленного расширения pg_stat_statements
Поскольку часть представлений из поставки данного расширения требуют наличия установленного расширения pg_stat_statements, необходимо проследить, чтобы данное расширение было установлено.
-
С помощью метакоманды
\dxв psql проверьте, что расширениеpg_stat_statementsприсутствует в списке:\dxВывод:
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
...
pg_stat_statements | 1.8 | ext | track planning and execution statistics of all SQL statements executed
... -
Проверьте, чтобы схема, в которой установлено расширение
pg_stat_statements, присутствовала вsearch_path:SHOW search_pathПример вывода:
search_path
-------------
ext, publicЕсли схема отсутствует в
search_path, ее можно добавить для заданного пользователя (например, одной из приведенных ниже команд), чтобы не выполнять командуSET SEARCH_PATH TO ...каждый раз вручную:ALTER ROLE TEST_ROLE SET search_path TO EXTENSION_SCHEMA,...';
ALTER DATABASE TESTDB SET search_path TO EXTENSION_SCHEMA,...'; -
Если производится установка на оригинальную версию PostgreSQL, необходимо установить данное расширение вручную. Для этого в
postgresql.confдобавьте значение'pg_stat_statements'в параметрshared_preload_libraries:shared_preload_libraries = 'pg_stat_statements'После этого, создайте расширение
pg_stat_statementsв заданной БД:CREATE EXTENSION pg_stat_statements;
Выдача необходимых привилегий
По умолчанию, пользователь может видеть только тексты запросов для процессов, которые были запущены под той же самой ролью. Если требуется видеть тексты запросов для всех процессов, необходимо предоставить пользователю привилегию pg_read_all_stats. Выдать привилегию пользователю можно командой:
GRANT pg_read_all_stats TO USER;
Установка расширения psql_diagpack
После настройки, можно перейти непосредственно к активации расширения psql_diagpack:
CREATE EXTENSION psql_diagpack;
Проверка корректности установки
Следует проверить, что расширение установлено корректно.
Расширения pg_stat_statements и psql_diagpack должны фигурировать в списке установленных расширений:
\dx
Вывод:
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
...
pg_stat_statements | 1.8 | ext | track planning and execution statistics of all SQL statements executed
psql_diagpack | 1.0 | ext | Administrative views for PostgreSQL monitoring
...
Следующие два запроса должны возвращать данные:
TABLE dba_query_cpu_time LIMIT 2;
time_percent | iotime_percent | cputime_percent | total_exec_time | avg_time | avg_cpu_time | avg_io_time | calls | calls_percent | rows | row_percent | query
--------------+----------------+-----------------+-----------------+----------+--------------+-------------+-------+---------------+------+-------------+------------------------------------------------
68.77 | 0.00 | 68.77 | 43.90 | 43904.56 | 43.90 | 0.00 | 1 | 6.67 | 0 | 0.00 | create extension psql_diagpack schema ext
23.15 | 0.00 | 23.15 | 14.78 | 7391.40 | 7.39 | 0.00 | 2 | 13.33 | 0 | 0.00 | create extension pg_stat_statements schema ext
(2 rows)
TABLE dba_bloat_wastedbytes LIMIT 2;
nspname | tabname | relname | reltype | tsize | tplcnt | dtplcnt | wspaceprc | wspaceb
------------+------------------+-----------------------------------+---------+---------+--------+---------+-----------+------------
pg_catalog | pg_proc | pg_proc | table | 1112 kB | 3295 | 5 | 13.1 | 145 kB
pg_catalog | pg_depend | pg_depend_reference_index | index | 392 kB | 8597 | 0 | 35.2 | 138 kB
(2 rows)
Управление
Расширение psql_diagpack не предоставляет никаких параметров для изменения поведения расширения или иных средств управления.
Устранение неполадок
Если при просмотре представлений dba_locks и dba_activity вместо текстов запросов отображается строка Insufficient privilege, то необходимо предоставить пользователю привилегию pg_read_all_stats, чтобы наблюдать тексты запросов для всех процессов.
Безопасность
Поскольку psql_diagpack предполагает необходимость только Read Only доступа к БД, риски его использования относительно невелики. Тем не менее возможность наблюдать тексты запросов в представлениях pg_stat_activity и pg_stat_statements дает возможность злоумышленнику-инсайдеру обнаружить, к примеру, запросы, которые запускаются без использования переменных привязки. Из текста подобных запросов можно получить элементы персональных данных, либо рассмотреть возможность использования подобных запросов для проведения атаки типа «SQL-инъекция».
Описание объектов
Примечание:
Все указанные в разделе примеры вывода не содержат действительные данные (например,
id).
Представления
dba_query_cpu_and_waits_time: Получение списка запросов, отсортированных по убыванию времени, проведенного на CPU или событиях ожидания, отличных от IO
| Название поля | Тип | Описание поля |
|---|---|---|
| time_pcnt | numeric(5,2) | Доля времени, проведенного на выполнении данного запроса, в общем времени, проведенном на выполнении запросов для данной БД |
| io_pcnt | numeric(5,2) | Доля времени, проведенного на IO при выполнении данного запроса, в общем времени, проведенном на IO при выполнении запросов для данной БД |
| cpu_and_waits_pcnt | numeric(5,2) | Доля времени, проведенного на CPU при выполнении данного запроса, в общем времени, проведенном на CPU или событиях ожидания, отличных от IO, при выполнении запросов для данной БД |
| calls_pcnt | numeric(5,2) | Доля от общего количества выполнений запросов в данной БД |
| rows_pcnt | numeric(5,2) | Доля от общего количества обработанных записей в данной БД |
| total_time_ms | bigint | Время, потраченное на все выполнения данного запроса (в миллисекундах) |
| total_io_ms | bigint | Время, проведенное на IO в рамках всех выполнений данного запроса (в миллисекундах) |
| shr_blks | numeric | Количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках всех выполнений данного запроса |
| calls | numeric | Число выполнений запроса |
| rows | numeric | Число обработанных записей |
| avg_time_us | bigint | Среднее время выполнения запроса (в микросекундах) |
| avg_io_us | bigint | Среднее время проведенное на IO в рамках выполнения запроса (в микросекундах) |
| avg_shr_blks | bigint | Среднее количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках выполнения данного запроса |
| query | text | Текст запроса |
DDL:
First_db=# \d+ dba_query_cpu_and_waits_time
View "public.dba_query_cpu_and_waits_time"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------------+--------------+-----------+----------+---------+----------+-------------
time_pcnt | numeric(5,2) | | | | main |
io_pcnt | numeric(5,2) | | | | main |
cpu_and_waits_pcnt | numeric(5,2) | | | | main |
calls_pcnt | numeric(5,2) | | | | main |
rows_pcnt | numeric(5,2) | | | | main |
total_time_ms | bigint | | | | plain |
total_io_ms | bigint | | | | plain |
shr_blks | numeric | | | | main |
calls | numeric | | | | main |
rows | numeric | | | | main |
avg_time_us | bigint | | | | plain |
avg_io_us | bigint | | | | plain |
avg_shr_blks | bigint | | | | plain |
query | text | | | | extended |
View definition:
WITH summ AS (
SELECT GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time)) AS sum_total_exec_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time)) AS sum_io_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time)) AS sum_cpu_and_waits_time,
sum(pg_stat_statements.calls) AS sum_calls,
sum(pg_stat_statements.rows) AS sum_rows
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
), _pg_stat_statements AS (
SELECT pg_stat_statements.query,
sum(pg_stat_statements.total_exec_time) AS total_exec_time,
sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time) AS io_time,
sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time) AS cpu_and_waits_time,
sum(pg_stat_statements.calls) AS calls,
sum(pg_stat_statements.rows) AS rows,
sum(pg_stat_statements.shared_blks_read + pg_stat_statements.shared_blks_hit) AS shr_blks
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
GROUP BY pg_stat_statements.query
)
SELECT (100::double precision * _pg_stat_statements.total_exec_time / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * _pg_stat_statements.io_time / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * _pg_stat_statements.cpu_and_waits_time / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * _pg_stat_statements.calls / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * _pg_stat_statements.rows / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
_pg_stat_statements.total_exec_time::bigint AS total_time_ms,
_pg_stat_statements.io_time::bigint AS total_io_ms,
_pg_stat_statements.shr_blks,
_pg_stat_statements.calls,
_pg_stat_statements.rows,
(_pg_stat_statements.total_exec_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_time_us,
(_pg_stat_statements.io_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_io_us,
(_pg_stat_statements.shr_blks / _pg_stat_statements.calls)::bigint AS avg_shr_blks,
_pg_stat_statements.query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.cpu_and_waits_time / (( SELECT summ.sum_cpu_and_waits_time
FROM summ))) >= 0.02::double precision
UNION ALL
SELECT (100::double precision * sum(_pg_stat_statements.total_exec_time) / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * sum(_pg_stat_statements.io_time) / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * sum(_pg_stat_statements.cpu_and_waits_time) / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * sum(_pg_stat_statements.calls) / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * sum(_pg_stat_statements.rows) / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
sum(_pg_stat_statements.total_exec_time)::bigint AS total_time_ms,
sum(_pg_stat_statements.io_time)::bigint AS total_io_ms,
sum(_pg_stat_statements.shr_blks) AS shr_blks,
sum(_pg_stat_statements.calls) AS calls,
sum(_pg_stat_statements.rows) AS rows,
(sum(_pg_stat_statements.total_exec_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_time_us,
(sum(_pg_stat_statements.io_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_io_us,
(sum(_pg_stat_statements.shr_blks) / sum(_pg_stat_statements.calls))::bigint AS avg_shr_blks,
'other'::text AS query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.cpu_and_waits_time / (( SELECT summ.sum_cpu_and_waits_time
FROM summ))) < 0.02::double precision
ORDER BY 3 DESC;
Пример эксплуатации
Подайте нагрузку на БД с помощью утилиты pg_bench:
pgbench -i -s 10 --foreign-keys First_db
pgbench -c 5 -j 5 -T 30 -P 10 First_db
Пример вывода списка тяжелых запросов:
psql -d First_db -c 'table dba_query_cpu_and_waits_time'
time_pcnt | io_pcnt | cpu_and_waits_pcnt | calls_pcnt | rows_pcnt | total_time_ms | total_io_ms | shr_blks | calls | rows | avg_time_us | avg_io_us | avg_shr_blks | query
-----------+---------+--------------------+------------+-----------+---------------+-------------+----------+--------+---------+-------------+-----------+--------------+------------------------------------------------------------------------------------------------------
56.89 | 0.00 | 57.51 | 14.26 | 2.90 | 17074 | 0 | 141083 | 33940 | 33940 | 503 | 0 | 4 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
9.58 | 0.00 | 9.68 | 14.26 | 2.90 | 2875 | 0 | 106137 | 33940 | 33940 | 85 | 0 | 3 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
9.06 | 0.75 | 9.15 | 0.00 | 0.00 | 2720 | 2 | 50693 | 1 | 0 | 2720126 | 2395 | 50693 | CREATE EXTENSION "pg_profile" WITH SCHEMA "pgse_profile"
9.01 | 0.01 | 9.10 | 14.26 | 2.90 | 2703 | 0 | 456267 | 33940 | 33940 | 80 | 0 | 13 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
4.45 | 11.42 | 4.38 | 14.26 | 2.90 | 1336 | 37 | 292065 | 33940 | 33940 | 39 | 1 | 9 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
4.12 | 39.08 | 3.74 | 42.95 | 2.92 | 1237 | 125 | 229422 | 102216 | 34102 | 12 | 1 | 2 | other
3.65 | 15.65 | 3.52 | 0.00 | 85.48 | 1096 | 50 | 6 | 1 | 1000000 | 1095872 | 50228 | 6 | copy pgbench_accounts from stdin
3.23 | 33.09 | 2.90 | 0.00 | 0.00 | 968 | 106 | 49305 | 1 | 0 | 968289 | 106225 | 49305 | vacuum analyze pgbench_accounts
(8 rows)
dba_query_io_time: Получение списка запросов, отсортированных по убыванию времени, проведенного на IO
| Название поля | Тип | Описание поля |
|---|---|---|
| time_pcnt | numeric(5,2) | Доля времени, проведенного на выполнении данного запроса, в общем времени, проведенном на выполнении запросов для данной БД |
| io_pcnt | numeric(5,2) | Доля времени, проведенного на IO при выполнении данного запроса, в общем времени, проведенном на IO при выполнении запросов для данной БД |
| cpu_and_waits_pcnt | numeric(5,2) | Доля времени, проведенного на CPU при выполнении данного запроса, в общем времени, проведенном на CPU или событиях ожидания, отличных от IO, при выполнении запросов для данной БД |
| calls_pcnt | numeric(5,2) | Доля от общего количества выполнений запросов в данной БД |
| rows_pcnt | numeric(5,2) | Доля от общего количества обработанных записей в данной БД |
| total_time_ms | bigint | Время, потраченное на все выполнения данного запроса (в миллисекундах) |
| total_io_ms | bigint | Время, проведенное на IO в рамках всех выполнений данного запроса (в миллисекундах) |
| shr_blks | numeric | Количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках всех выполнений данного запроса |
| calls | numeric | Число выполнений запроса |
| rows | numeric | Число обработанных записей |
| avg_time_us | bigint | Среднее время выполнения запроса (в микросекундах) |
| avg_io_us | bigint | Среднее время проведенное на IO в рамках выполнения запроса (в микросекундах) |
| avg_shr_blks | bigint | Среднее количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках выполнения данного запроса |
| query | text | Текст запроса |
DDL:
First_db=# \d+ dba_query_io_time
View "public.dba_query_io_time"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------------+--------------+-----------+----------+---------+----------+-------------
time_pcnt | numeric(5,2) | | | | main |
io_pcnt | numeric(5,2) | | | | main |
cpu_and_waits_pcnt | numeric(5,2) | | | | main |
calls_pcnt | numeric(5,2) | | | | main |
rows_pcnt | numeric(5,2) | | | | main |
total_time_ms | bigint | | | | plain |
total_io_ms | bigint | | | | plain |
shr_blks | numeric | | | | main |
calls | numeric | | | | main |
rows | numeric | | | | main |
avg_time_us | bigint | | | | plain |
avg_io_us | bigint | | | | plain |
avg_shr_blks | bigint | | | | plain |
query | text | | | | extended |
View definition:
WITH summ AS (
SELECT GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time)) AS sum_total_exec_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time)) AS sum_io_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time)) AS sum_cpu_and_waits_time,
sum(pg_stat_statements.calls) AS sum_calls,
sum(pg_stat_statements.rows) AS sum_rows
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
), _pg_stat_statements AS (
SELECT pg_stat_statements.query,
sum(pg_stat_statements.total_exec_time) AS total_exec_time,
sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time) AS io_time,
sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time) AS cpu_and_waits_time,
sum(pg_stat_statements.calls) AS calls,
sum(pg_stat_statements.rows) AS rows,
sum(pg_stat_statements.shared_blks_read + pg_stat_statements.shared_blks_hit) AS shr_blks
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
GROUP BY pg_stat_statements.query
)
SELECT (100::double precision * _pg_stat_statements.total_exec_time / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * _pg_stat_statements.io_time / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * _pg_stat_statements.cpu_and_waits_time / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * _pg_stat_statements.calls / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * _pg_stat_statements.rows / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
_pg_stat_statements.total_exec_time::bigint AS total_time_ms,
_pg_stat_statements.io_time::bigint AS total_io_ms,
_pg_stat_statements.shr_blks,
_pg_stat_statements.calls,
_pg_stat_statements.rows,
(_pg_stat_statements.total_exec_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_time_us,
(_pg_stat_statements.io_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_io_us,
(_pg_stat_statements.shr_blks / _pg_stat_statements.calls)::bigint AS avg_shr_blks,
_pg_stat_statements.query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.io_time / (( SELECT summ.sum_io_time
FROM summ))) >= 0.02::double precision
UNION ALL
SELECT (100::double precision * sum(_pg_stat_statements.total_exec_time) / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * sum(_pg_stat_statements.io_time) / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * sum(_pg_stat_statements.cpu_and_waits_time) / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * sum(_pg_stat_statements.calls) / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * sum(_pg_stat_statements.rows) / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
sum(_pg_stat_statements.total_exec_time)::bigint AS total_time_ms,
sum(_pg_stat_statements.io_time)::bigint AS total_io_ms,
sum(_pg_stat_statements.shr_blks) AS shr_blks,
sum(_pg_stat_statements.calls) AS calls,
sum(_pg_stat_statements.rows) AS rows,
(sum(_pg_stat_statements.total_exec_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_time_us,
(sum(_pg_stat_statements.io_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_io_us,
(sum(_pg_stat_statements.shr_blks) / sum(_pg_stat_statements.calls))::bigint AS avg_shr_blks,
'other'::text AS query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.io_time / (( SELECT summ.sum_io_time
FROM summ))) < 0.02::double precision
ORDER BY 2 DESC;
Пример эксплуатации
Подайте нагрузку на БД с помощью утилиты pg_bench:
pgbench -i -s 10 --foreign-keys First_db
pgbench -c 5 -j 5 -T 30 -P 10 First_db
Пример вывода списка тяжелых запросов:
psql -d First_db -c 'table dba_query_io_time'
time_pcnt | io_pcnt | cpu_and_waits_pcnt | calls_pcnt | rows_pcnt | total_time_ms | total_io_ms | shr_blks | calls | rows | avg_time_us | avg_io_us | avg_shr_blks | query
-----------+---------+--------------------+------------+-----------+---------------+-------------+----------+--------+-------+-------------+-----------+--------------+---------------------------------------------------------------------
4.98 | 76.21 | 4.48 | 14.28 | 19.90 | 747 | 80 | 106453 | 17532 | 17532 | 43 | 5 | 6 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
3.02 | 22.91 | 2.88 | 0.00 | 0.00 | 452 | 24 | 31655 | 1 | 1 | 452237 | 24130 | 31655 | SELECT pgse_profile.take_sample()
92.00 | 0.88 | 92.65 | 85.72 | 80.10 | 13789 | 1 | 559617 | 105211 | 70568 | 131 | 0 | 5 | other
(3 rows)
dba_query_run_time: Получение списка запросов, отсортированных по убыванию общего времени выполнения
| Название поля | Тип | Описание поля |
|---|---|---|
| time_pcnt | numeric(5,2) | Доля времени, проведенного на выполнении данного запроса, в общем времени, проведенном на выполнении запросов для данной БД |
| io_pcnt | numeric(5,2) | Доля времени, проведенного на IO при выполнении данного запроса, в общем времени, проведенном на IO при выполнении запросов для данной БД |
| cpu_and_waits_pcnt | numeric(5,2) | Доля времени, проведенного на CPU при выполнении данного запроса, в общем времени, проведенном на CPU или событиях ожидания, отличных от IO, при выполнении запросов для данной БД |
| calls_pcnt | numeric(5,2) | Доля от общего количества выполнений запросов в данной БД |
| rows_pcnt | numeric(5,2) | Доля от общего количества обработанных записей в данной БД |
| total_time_ms | bigint | Время, потраченное на все выполнения данного запроса (в миллисекундах) |
| total_io_ms | bigint | Время, проведенное на IO в рамках всех выполнений данного запроса (в миллисекундах) |
| shr_blks | numeric | Количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках всех выполнений данного запроса |
| calls | numeric | Число выполнений запроса |
| rows | numeric | Число обработанных записей |
| avg_time_us | bigint | Среднее время выполнения запроса (в микросекундах) |
| avg_io_us | bigint | Среднее время проведенное на IO в рамках выполнения запроса (в микросекундах) |
| avg_shr_blks | bigint | Среднее количество страниц (из буферного кеша и из файловой системы), прочитанных в рамках выполнения данного запроса |
| query | text | Текст запроса |
DDL:
First_db=# \d+ dba_query_run_time
View "public.dba_query_run_time"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------------+--------------+-----------+----------+---------+----------+-------------
time_pcnt | numeric(5,2) | | | | main |
io_pcnt | numeric(5,2) | | | | main |
cpu_and_waits_pcnt | numeric(5,2) | | | | main |
calls_pcnt | numeric(5,2) | | | | main |
rows_pcnt | numeric(5,2) | | | | main |
total_time_ms | bigint | | | | plain |
total_io_ms | bigint | | | | plain |
shr_blks | numeric | | | | main |
calls | numeric | | | | main |
rows | numeric | | | | main |
avg_time_us | bigint | | | | plain |
avg_io_us | bigint | | | | plain |
avg_shr_blks | bigint | | | | plain |
query | text | | | | extended |
View definition:
WITH summ AS (
SELECT GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time)) AS sum_total_exec_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time)) AS sum_io_time,
GREATEST(0.001::double precision, sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time)) AS sum_cpu_and_waits_time,
sum(pg_stat_statements.calls) AS sum_calls,
sum(pg_stat_statements.rows) AS sum_rows
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
), _pg_stat_statements AS (
SELECT pg_stat_statements.query,
sum(pg_stat_statements.total_exec_time) AS total_exec_time,
sum(pg_stat_statements.blk_read_time + pg_stat_statements.blk_write_time) AS io_time,
sum(pg_stat_statements.total_exec_time - pg_stat_statements.blk_read_time - pg_stat_statements.blk_write_time) AS cpu_and_waits_time,
sum(pg_stat_statements.calls) AS calls,
sum(pg_stat_statements.rows) AS rows,
sum(pg_stat_statements.shared_blks_read + pg_stat_statements.shared_blks_hit) AS shr_blks
FROM pg_stat_statements
WHERE pg_stat_statements.dbid = (( SELECT pg_database.oid
FROM pg_database
WHERE pg_database.datname = current_database()))
GROUP BY pg_stat_statements.query
)
SELECT (100::double precision * _pg_stat_statements.total_exec_time / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * _pg_stat_statements.io_time / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * _pg_stat_statements.cpu_and_waits_time / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * _pg_stat_statements.calls / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * _pg_stat_statements.rows / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
_pg_stat_statements.total_exec_time::bigint AS total_time_ms,
_pg_stat_statements.io_time::bigint AS total_io_ms,
_pg_stat_statements.shr_blks,
_pg_stat_statements.calls,
_pg_stat_statements.rows,
(_pg_stat_statements.total_exec_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_time_us,
(_pg_stat_statements.io_time * 1000::double precision / _pg_stat_statements.calls::double precision)::bigint AS avg_io_us,
(_pg_stat_statements.shr_blks / _pg_stat_statements.calls)::bigint AS avg_shr_blks,
_pg_stat_statements.query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.total_exec_time / (( SELECT summ.sum_total_exec_time
FROM summ))) >= 0.02::double precision
UNION ALL
SELECT (100::double precision * sum(_pg_stat_statements.total_exec_time) / (( SELECT summ.sum_total_exec_time
FROM summ)))::numeric(5,2) AS time_pcnt,
(100::double precision * sum(_pg_stat_statements.io_time) / (( SELECT summ.sum_io_time
FROM summ)))::numeric(5,2) AS io_pcnt,
(100::double precision * sum(_pg_stat_statements.cpu_and_waits_time) / (( SELECT summ.sum_cpu_and_waits_time
FROM summ)))::numeric(5,2) AS cpu_and_waits_pcnt,
(100::numeric * sum(_pg_stat_statements.calls) / (( SELECT summ.sum_calls
FROM summ)))::numeric(5,2) AS calls_pcnt,
(100::numeric * sum(_pg_stat_statements.rows) / (( SELECT summ.sum_rows
FROM summ)))::numeric(5,2) AS rows_pcnt,
sum(_pg_stat_statements.total_exec_time)::bigint AS total_time_ms,
sum(_pg_stat_statements.io_time)::bigint AS total_io_ms,
sum(_pg_stat_statements.shr_blks) AS shr_blks,
sum(_pg_stat_statements.calls) AS calls,
sum(_pg_stat_statements.rows) AS rows,
(sum(_pg_stat_statements.total_exec_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_time_us,
(sum(_pg_stat_statements.io_time) * 1000::double precision / sum(_pg_stat_statements.calls)::double precision)::bigint AS avg_io_us,
(sum(_pg_stat_statements.shr_blks) / sum(_pg_stat_statements.calls))::bigint AS avg_shr_blks,
'other'::text AS query
FROM _pg_stat_statements
WHERE (_pg_stat_statements.total_exec_time / (( SELECT summ.sum_total_exec_time
FROM summ))) < 0.02::double precision
ORDER BY 1 DESC;
Пример эксплуатации
Подайте нагрузку на БД с помощью утилиты pg_bench:
pgbench -i -s 10 --foreign-keys First_db
pgbench -c 5 -j 5 -T 30 -P 10 First_db
Пример вывода списка тяжелых запросов:
psql -d First_db -c 'table dba_query_run_time'
time_pcnt | io_pcnt | cpu_and_waits_pcnt | calls_pcnt | rows_pcnt | total_time_ms | total_io_ms | shr_blks | calls | rows | avg_time_us | avg_io_us | avg_shr_blks | query
-----------+---------+--------------------+------------+-----------+---------------+-------------+----------+-------+-------+-------------+-----------+--------------+------------------------------------------------------------------------------------------------------
67.95 | 0.00 | 67.95 | 14.28 | 19.94 | 16443 | 0 | 122563 | 29646 | 29646 | 555 | 0 | 4 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
11.67 | 0.00 | 11.67 | 14.28 | 19.94 | 2824 | 0 | 92859 | 29646 | 29646 | 95 | 0 | 3 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
10.85 | 3.93 | 10.85 | 14.28 | 19.94 | 2626 | 0 | 387742 | 29646 | 29646 | 89 | 0 | 13 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
5.80 | 0.00 | 5.80 | 14.28 | 19.94 | 1405 | 0 | 189541 | 29646 | 29646 | 47 | 0 | 6 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
3.73 | 1.45 | 3.73 | 42.86 | 20.25 | 903 | 0 | 160965 | 88958 | 30106 | 10 | 0 | 2 | other
(5 rows)
dba_locks: Построение дерева блокировок
| Название поля | Тип | Описание поля |
|---|---|---|
| pid_string | text | Цепочка блокировок, которая привела к ожиданию получения блокировки данным процессом |
| root_pid | integer | Корневой узе л цепочки блокировок, которая привела к ожиданию получения блокировки данным процессом |
| tid | text | Идентификатор транзакции |
| lock_time | interval | Время ожидания блокировки |
| state | text | Состояние / текущее событие ожидания |
| datname | name | Имя БД |
| lock_mode_type_info | text | Дополнительная информация о блокировке, которую ожидает данный процесс |
| query_text | text | Текст запроса |
| usename | name | Имя роли, под которой установлено соединение |
| client_addr | inet | IP-адрес, с которого установлено соединение |
| application_name | text | Имя приложения, из которого установлено соединение |
DDL:
First_db=# \d+ dba_locks
View "public.dba_locks"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+----------+-----------+----------+---------+----------+-------------
pid_string | text | | | | extended |
tid | text | | | | extended |
lock_time | interval | | | | plain |
state | text | | | | extended |
datname | name | | | | plain |
lock_mode_type_info | text | C | | | extended |
query_text | text | | | | extended |
usename | name | | | | plain |
client_addr | inet | | | | main |
application_name | text | | | | extended |
root_pid | integer | | | | plain |
View definition:
with recursive tree (lev,pid,root_pid,pid_string,waitstart) as (
select
1 as lev,
all_blocking_pids.pid,
all_blocking_pids.pid as root_pid,
all_blocking_pids.pid::text as pid_string,
null::timestamptz as waitstart
from
(
select distinct unnest(pg_blocking_pids(pid)) as pid
from pg_locks
where not granted
) all_blocking_pids
where not exists (
select 1
from pg_locks blocking_pids_locks
where blocking_pids_locks.pid = all_blocking_pids.pid
and not granted
)
or all_blocking_pids.pid = 0
union all
select
tree.lev + 1,
locks_nl.pid,
tree.root_pid,
tree.pid_string || '>' || locks_nl.pid::text,
locks_nl.waitstart
from
pg_locks locks_nl,
unnest(pg_blocking_pids(locks_nl.pid)) blocking_pids_nl (pid),
tree
where not locks_nl.granted
and tree.pid = blocking_pids_nl.pid
and tree.lev <= 1000
)
select
rn_tree.pid_string,
(
case when rn_tree.pid != 0
then (
select transactionid::text
from pg_locks tid_locks
where tid_locks.pid = rn_tree.pid
and tid_locks.locktype = 'transactionid'
and tid_locks.mode = 'ExclusiveLock'
and tid_locks.granted = true
)
else 'prep.trans.'
end
) as tid,
date_trunc('second',clock_timestamp() - rn_tree.waitstart) as lock_time,
case when rn_tree.lev > 1 then 'blocked' else pgsa.state || ' / ' || pgsa.wait_event end as state,
pgsa.datname,
(
select string_agg(
locks.mode
|| ' / ' || locks.locktype
|| ' ('
|| case
when locks.locktype = 'transactionid'
then 'tid = ' || locks.transactionid
|| coalesce(
(
select ', global XID = ' || gid
from pg_prepared_xacts prep_trans
where prep_trans.transaction=locks.transactionid
),
''
)
when locks.locktype = 'relation'
then locks.full_relation_name
when locks.locktype = 'tuple'
then locks.full_relation_name || ', ctid=(' || locks.page || ',' || locks.tuple || ')'
else ''
end
|| ')'
, ' / '
)
from (
select
*,
(
select
'['
|| (case rels.relkind
when 'r' then 'ORD.TABLE'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 't' then 'TOAST.TABLE'
when 'v' then 'VIEW'
when 'm' then 'MAT.VIEW'
when 'c' then 'COMP.TYPE'
when 'f' then 'FOREIGN.TABLE'
when 'p' then 'PART.TABLE'
when 'I' then 'PART.INDEX'
end
)
|| '] '
|| (select nspname from pg_namespace where oid = rels.relnamespace)
|| '.'
|| rels.relname
|| ', oid = ' || pgl.relation
from pg_class rels
where rels.oid = pgl.relation
) as full_relation_name
from pg_locks pgl
) locks
where locks.pid = rn_tree.pid
and locks.granted = false
) as lock_mode_type_info,
regexp_replace(pgsa.query, E'[\\n\\r]+', ' ', 'g') as query_text,
pgsa.usename,
pgsa.client_addr,
pgsa.application_name,
rn_tree.root_pid -- adding it here to make the aggregates like root_pid,count(*) possible.
from (
select
lt.*,
row_number() over(partition by pid order by lev,pid_string) as rn
from tree lt
) rn_tree
left outer join pg_stat_activity pgsa on (pgsa.pid = rn_tree.pid)
where rn_tree.rn = 1
order by pid_string;
Пример эксплуатации
«Создайте» дерево блокировок. В рамках нескольких подключений выполните следующие команды:
a => \set AUTOCOMMIT 'off'
a => create table ttt_test(id bigint, name text);
a => insert into ttt_test(id,name)
values (1,'test 1'),(2,'test 2'),(3,'test 3');
a => commit;
a => update ttt_test set name = 'test 1 - mod' where id = 1;
b => \set AUTOCOMMIT 'off'
b => update ttt_test set name = 'test 2 - mod' where id = 2;
b => update ttt_test set name = 'test 1 - mod' where id = 1;
c => update ttt_test set name = 'test 2 - mod' where id = 2;
d => update ttt_test set name = 'test 1 - mod' where id = 1;
e => update ttt_test set name = 'test 2 - mod' where id = 2;
Пример вывода получившегося дерева блокировок посредством представления dba_locks:
psql -d First_db -c 'table dba_locks'
pid_string | tid | lock_time | state | datname | lock_mode_type_info | query_text | usename | client_addr | application_name | root_pid
---------------------+-------+------------+----------------------------------+----------+---------------------------------------------------------------------------+---------------------------------------------------------+----------+--------------+-------------------------------------------+----------
7800 | 96222 | 00:02:10 | idle in transaction / ClientRead | First_db | | SHOW search_path | postgres | {IP-адрес} | DBeaver 22.0.1 - SQLEditor <Script.sql> | 7800
7800>7879 | 96223 | 00:01:35 | blocked | First_db | ShareLock / transactionid (tid = 96222) | update ttt_test set name = 'test 1 - mod' where id = 1 | postgres | {IP-адрес} | DBeaver 22.0.1 - SQLEditor <Script-3.sql> | 7800
7800>7879>7911 | 96224 | 00:01:19 | blocked | First_db | ShareLock / transactionid (tid = 96223) | update ttt_test set name = 'test 2 - mod' where id = 2 | postgres | {IP-адрес} | DBeaver 22.0.1 - SQLEditor <Script-4.sql> | 7800
7800>7879>7911>7953 | 96226 | 00:00:54 | blocked | First_db | ExclusiveLock / tuple ([ORD.TABLE] ext.ttt_test, oid = 17865, ctid=(0,2)) | update ttt_test set name = 'test 2 - mod' where id = 2 | postgres | {IP-адрес} | DBeaver 22.0.1 - SQLEditor <Script-6.sql> | 7800
7800>7879>7925 | 96225 | 00:01:04 | blocked | First_db | ExclusiveLock / tuple ([ORD.TABLE] ext.ttt_test, oid = 17865, ctid=(0,1)) | update ttt_test set name = 'test 1 - mod' where id = 1 | postgres | {IP-адрес} | DBeaver 22.0.1 - SQLEditor <Script-5.sql> | 7800
(5 rows)
dba_standby_check: Построение отчета по статусу репликации
| Название поля | Тип | Описание поля |
|---|---|---|
| pid | integer | Идентификатор процесса WAL sender, отвечающего за отсылку данных данному подписчику |
| client | inet | IP-адрес подписчика |
| user | name | Имя роли, используемой для репликации |
| state | text | Поле-флаг, отображающее информацию о синхронности репликации |
| application_name | text | Имя приложения, подписанного на репликацию |
| sending_lag | numeric | Величина отставания (в байтах) между позицией WAL, записанной в локальный журнал, и позицией WAL, отосланной данному подписчику |
| receiving_lag | numeric | Величина отставания (в байтах) между позицией WAL, отосланной данному подписчику, и позицией WAL, записанной в журнал подписчика |
| replaying_lag | numeric | Величина отставания (в байтах) между позицией WAL, записанной в журнал подписчика, и позицией WAL, примененной к файлам данных подписчика |
| total_lag | numeric | Величина отставания (в байтах) между позицией WAL, записанной в локальный журнал, и позицией WAL, примененной к файлам данных подписчика |
DDL:
First_db=# \d+ dba_standby_check
View "public.dba_standby_check"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------+---------+-----------+----------+---------+----------+-------------
pid | integer | | | | plain |
client | inet | | | | main |
user | name | | | | plain |
state | text | | | | extended |
application_name | text | | | | extended |
sending_lag | numeric | | | | main |
receiving_lag | numeric | | | | main |
replaying_lag | numeric | | | | main |
total_lag | numeric | | | | main |
View definition:
SELECT pg_stat_replication.pid,
pg_stat_replication.client_addr AS client,
pg_stat_replication.usename AS "user",
pg_stat_replication.sync_state AS state,
pg_stat_replication.application_name,
pg_wal_lsn_diff(pg_current_wal_lsn(), pg_stat_replication.sent_lsn) AS sending_lag,
pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.flush_lsn) AS receiving_lag,
pg_wal_lsn_diff(pg_stat_replication.flush_lsn, pg_stat_replication.replay_lsn) AS replaying_lag,
pg_wal_lsn_diff(pg_current_wal_lsn(), pg_stat_replication.replay_lsn) AS total_lag
FROM pg_stat_replication;
Пример эксплуатации
Создайте сервер для реплики.
Добавьте запись для репликации в pg_hba.conf лидер-сервера:
host replication postgres {IP-адрес}/32 trust
Создайте, физическую резервную копию, предварительно настроенную для потоковой репликации:
pg_basebackup --pgdata=/pgdata/6.5.0-replica/data --write-recovery-conf --tablespace-mapping=/pgdata/6.5.0/tablespaces/Tbl_t=/pgdata/6.5.0-replica/tablespaces/Tbl_t --verbose
Поменяйте в postgresql.conf резервной копии следующие параметры:
authentication_port = '15544'
port='15433'
archive_command = '... -B /pgarclogs/6.5.0-replica...'
log_directory = '/pgerrorlogs/6.5.0-replica'
hba_file = '/pgdata/6.5.0-replica/data/pg_hba.conf'
ident_file = '/pgdata/6.5.0-replica/data/pg_ident.conf'
Запустите сервер-реп лику:
pg_ctl -D /pgdata/6.5.0-replica/data start
Проверьте статус репликации на лидер-сервере:
psql -d First_db -c 'table dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 0 | 0 | 0 | 0
Подайте на лидер-сервер нагрузку в рамках одного подключения:
psql -d First_db <<EOF
drop table ttt_test;
create table ttt_test(id bigint, name text);
insert into ttt_test(id,name)
select a.t, lpad('',1000,'test')
from generate_series(1,1000000) a(t);
EOF
Проверьте статус репликации в рамках другого подключения:
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 0 | 0 | 0 | 0
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 64110592 | 10223616 | 16777552 | 91111760
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 0 | 0 | 400 | 400
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 36904960 | 10166272 | 16777616 | 63848848
...
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 77119488 | 23535616 | 16778200 | 117433304
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 68041512 | 22618112 | 16778072 | 107437696
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 0 | 0 | 6774384 | 6774384
(1 row)
psql -d First_db -c 'select * from dba_standby_check'
pid | client | user | state | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
------+------------+----------+-------+------------------+-------------+---------------+---------------+-----------
4739 | {IP-адрес} | postgres | async | walreceiver | 0 | 0 | 0 | 0
(1 row)
dba_top_tables: Построение списка самых больших таблиц (полный размер) в БД
| Название поля | Тип | Описание поля |
|---|---|---|
| schema_name | name | Имя схемы |
| table_name | name | Имя таблицы |
| persistence | text | Тип хранения таблицы (permanent / temporary / unlogged) |
| tab_size | text | Размер таблицы |
| toast_size | text | Размер TOAST сегмента |
| idx_size | text | Размер всех индексов по данной таблице |
| total_size | text | Полный размер таблицы |
DDL:
First_db=# \d+ dba_top_tables
View "public.dba_top_tables"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------+------+-----------+----------+---------+----------+-------------
schema_name | name | | | | plain |
table_name | name | | | | plain |
persistence | text | | | | extended |
tab_size | text | | | | extended |
toast_size | text | | | | extended |
idx_size | text | | | | extended |
total_size | text | | | | extended |
View definition:
SELECT n.nspname AS schema_name,
c.relname AS table_name,
case relpersistence
when 'p' then 'permanent'
when 't' then 'temporary'
when 'u' then 'unlogged'
end as persistence,
pg_size_pretty(pg_relation_size(c.oid::regclass)) AS tab_size,
pg_size_pretty(pg_table_size(c.oid::regclass) - pg_relation_size(c.oid::regclass)) AS toast_size,
pg_size_pretty(pg_indexes_size(c.oid::regclass)) AS idx_size,
pg_size_pretty(pg_total_relation_size(c.oid::regclass)) AS total_size
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND n.nspname !~ '^pg_toast'::text AND (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"]))
ORDER BY (pg_total_relation_size(c.oid::regclass)) DESC
LIMIT 30;
Пример эксплуатации
Пример вывода списка самых больших таблиц:
psql -d First_db -c 'table dba_top_tables'
schema_name | table_name | tab_size | toast_size | idx_size | total_size
--------------+------------------------------+------------+------------+----------+------------
public | pgbench_accounts | 130 MB | 64 kB | 21 MB | 152 MB
public | pgbench_history | 1648 kB | 32 kB | 0 bytes | 1680 kB
pgse_profile | last_stat_tables | 328 kB | 32 kB | 88 kB | 448 kB
pgse_profile | last_stat_indexes | 224 kB | 32 kB | 96 kB | 352 kB
public | pgbench_tellers | 216 kB | 32 kB | 16 kB | 264 kB
...
dba_top_objects: Построение списка самых больших отношений в БД
| Название поля | Тип | Описание поля |
|---|---|---|
| schema_name | name | Имя схемы |
| table_name | name | Имя таблицы, к которой относится данное отношение (в случае таблицы и мат. представления - имя самой таблицы; в случае индекса, TOAST сегмента или индекса по TOAST сегменту - имя таблицы, к которой они относятся) |
| object_name | name | Имя отношения |
| object_type | text | Тип отношения |
| persistence | text | Тип хранения таблицы (permanent / temporary / unlogged) |
| size | text | Размер отношения |
DDL:
First_db=# \d+ dba_top_objects
View "public.dba_top_objects"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------+------+-----------+----------+---------+----------+-------------
schema_name | name | | | | plain |
table_name | name | | | | plain |
object_name | name | | | | plain |
object_type | text | | | | extended |
persistence | text | | | | extended |
size | text | | | | extended |
View definition:
SELECT n.nspname AS schema_name,
CASE
WHEN c.relkind = 't'::"char" THEN ( SELECT c2.relname
FROM pg_class c2
WHERE c2.reltoastrelid = c.oid)
WHEN c.relkind = 'i'::"char" AND n.nspname ~ '^pg_toast'::text THEN ( SELECT c4.relname
FROM pg_index i1,
pg_class c3,
pg_class c4
WHERE i1.indexrelid = c.oid AND c3.oid = i1.indrelid AND c4.reltoastrelid = c3.oid)
WHEN c.relkind = 'r'::"char" THEN c.relname
WHEN c.relkind = 'i'::"char" THEN ( SELECT c5.relname
FROM pg_index i2,
pg_class c5
WHERE i2.indexrelid = c.oid AND c5.oid = i2.indrelid)
WHEN c.relkind = 'm'::"char" THEN c.relname
ELSE NULL::name
END AS table_name,
c.relname AS object_name,
CASE
WHEN c.relkind = 't'::"char" THEN 'toast'::text
WHEN c.relkind = 'i'::"char" AND n.nspname ~ '^pg_toast'::text THEN 'toast index'::text
WHEN c.relkind = 'r'::"char" THEN 'table'::text
WHEN c.relkind = 'i'::"char" THEN 'index'::text
WHEN c.relkind = 'm'::"char" THEN 'mat.view'::text
ELSE NULL::text
END AS object_type,
case c.relpersistence
when 'p' then 'permanent'
when 't' then 'temporary'
when 'u' then 'unlogged'
end as persistence,
pg_size_pretty(pg_relation_size(c.oid::regclass)) AS size
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])
ORDER BY (pg_relation_size(c.oid::regclass)) DESC
LIMIT 30;
Пример эксплуатации
Пример вывода списка самых больших отношений в БД:
psql -d First_db -c 'table dba_top_objects'
schema_name | table_name | object_name | object_type | size
--------------+--------------------------+---------------------------+-------------+---------
public | pgbench_accounts | pgbench_accounts | table | 130 MB
public | pgbench_accounts | pgbench_accounts_pkey | index | 21 MB
public | pgbench_history | pgbench_history | table | 1648 kB
pg_toast | pg_rewrite | pg_toast_2618 | toast | 672 kB
pg_toast | pg_proc | pg_toast_1255 | toast | 344 kB
...
dba_activity: Построение расширенного отчета по активности процессов
| Название поля | Тип | Описание поля |
|---|---|---|
| pid | integer | Идентификатор процесса |
| state | text | Статус процесса |
| xact_age | interval | Время, прошедшее с момента открытия транзакции |
| query_age | interval | Время, прошедшее с момента начала обработки запроса |
| change_age | interval | Время, прошедшее с момента последнего изменения статуса процесса |
| wait_event_type | text | Тип события ожидания |
| wait_event | text | Событие ожидания |
| datname | name | Имя БД |
| query | text | Текст обрабатываемого запроса |
| usename | name | Имя р оли, под которой установлено соединение |
| client_addr | inet | IP машины, с которой было установлено соединение |
| client_port | integer | TCP-порт, который был использован для установки соединения |
| backend_type | text | Тип процесса |
| backend_xmin_age | integer | Возраст снимка (snapshot age) для процесса |
DDL:
First_db=# \d+ dba_activity
View "public.dba_activity"
Column | Type | Collation | Nullable | Default | Storage | Description
------------------+----------+-----------+----------+---------+----------+-------------
pid | integer | | | | plain |
state | text | | | | extended |
xact_age | interval | | | | plain |
query_age | interval | | | | plain |
change_age | interval | | | | plain |
wait_event_type | text | | | | extended |
wait_event | text | | | | extended |
datname | name | | | | plain |
query | text | | | | extended |
usename | name | | | | plain |
client_addr | inet | | | | main |
client_port | integer | | | | plain |
backend_type | text | | | | extended |
backend_xmin_age | integer | | | | plain |
View definition:
SELECT pgsa.pid,
pgsa.state,
date_trunc('second'::text, clock_timestamp() - pgsa.xact_start) AS xact_age,
date_trunc('second'::text, clock_timestamp() - pgsa.query_start) AS query_age,
date_trunc('second'::text, clock_timestamp() - pgsa.state_change) AS change_age,
pgsa.wait_event_type,
pgsa.wait_event,
pgsa.datname,
pgsa.query,
pgsa.usename,
pgsa.client_addr,
pgsa.client_port,
pgsa.backend_type,
age(pgsa.backend_xmin) AS backend_xmin_age
FROM pg_stat_activity pgsa
WHERE ((clock_timestamp() - pgsa.xact_start) > '00:00:00.1'::interval OR (clock_timestamp() - pgsa.query_start) > '00:00:00.1'::interval AND pgsa.state = 'idle in transaction (aborted)'::text) AND pgsa.pid <> pg_backend_pid()
ORDER BY (COALESCE(pgsa.xact_start, pgsa.query_start));
Пример эксплуатации
В данном примере наблюдается картина, использованная для демонстрации представления dba_locks (мониторинг дерева блокировок):
psql -d First_db -c 'table dba_activity'
pid | state | xact_age | query_age | change_age | wait_event_type | wait_event | datname | query | usename | client_addr | client_port | backend_type | backend_xmin_age
------+---------------------+----------+-----------+------------+-----------------+---------------+----------+--------------------------------------------------------+----------+--------------+-------------+----------------+------------------
7800 | idle in transaction | 00:11:14 | 00:11:08 | 00:11:08 | Client | ClientRead | First_db | SHOW search_path | postgres | {IP-адрес} | {Порт} | client backend |
7879 | active | 00:10:33 | 00:10:33 | 00:10:33 | Lock | transactionid | First_db | \r +| postgres | {IP-адрес} | {Порт} | client backend | 5
| | | | | | | | update ttt_test set name = 'test 1 - mod' where id = 1 | | | | |
7911 | active | 00:10:18 | 00:10:18 | 00:10:18 | Lock | transactionid | First_db | update ttt_test set name = 'test 2 - mod' where id = 2 | postgres | {IP-адрес} | {Порт} | client backend | 5
7925 | active | 00:10:03 | 00:10:03 | 00:10:03 | Lock | tuple | First_db | update ttt_test set name = 'test 1 - mod' where id = 1 | postgres | {IP-адрес} | {Порт} | client backend | 5
7953 | active | 00:09:52 | 00:09:52 | 00:09:52 | Lock | tuple | First_db | update ttt_test set name = 'test 2 - mod' where id = 2 | postgres | {IP-адрес} | {Порт} | client backend | 5
(5 rows)
dba_activity_vacuum: Построение отчета о текущей активности процессов очистки (AUTOVACUUM+VACUUM+VACUUM FULL)
| Название поля | Тип | Описание поля |
|---|---|---|
| relname | name | Имя таблицы, обрабатываемой процессом очистки |
| age | integer | Возраст самой старой незамороженной транзакции для данной таблицы |
| pid | integer | Идентификатор (PID) обслуживающего процесса |
| datid | oid | OID базы данных, к которой подключен этот обслуживающий процесс |
| datname | name | Имя базы данных, к которой подключен этот обслуживающий процесс |
| relid | oid | OID очищаемой таблицы |
| command | text | 'VACUUM' для процессов автоматической и ручной очистки; 'VACUUM FULL' для операций ручной полной очистки; 'CLUSTER' для операции кластеризации таблиц |
| phase | text | Текущая фаза очистки |
| heap_blks_total | bigint | Общее число блоков кучи в таблице на момент начала процесса очистки |
| heap_blks_scanned | bigint | Число просканированных блоков кучи. Так как для оптимизации сканирования применяется карта видимости, некоторые блоки могут пропускаться без осмотра; пропущенные блоки входят в это общее число, так что по завершении очистки это число станет равно heap_blks_total. Этот счетчик увеличивается только в фазе scanning heap. |
| heap_blks_vacuumed | bigint | Число очищенных блоков кучи. Если в таблице нет индексов, этот счетчик увеличивается только в фазе vacuuming heap (очистка кучи). Блоки, не содержащие «мертвых» кортежей, при этом пропускаются, так что этот счетчик иногда может увеличиваться резкими рывками. В случае операций VACUUM FULL / CLUSTER - NULL. |
| index_vacuum_count | bigint | Количество завершенных циклов очистки индекса. В случае операций VACUUM FULL / CLUSTER - NULL. |
| max_dead_tuples | bigint | Число «мертвых» кортежей, которое возможно сохранить, прежде чем потребуется выполнить цикл очистки индекса, в зависимости от maintenance_work_mem. В случае операций VACUUM FULL/CLUSTER - NULL. |
| num_dead_tuples | bigint | Число «мертвых» кортежей, собранных со времени последнего цикла очистки индекса. В случае операций VACUUM FULL / CLUSTER - NULL. |
DDL:
First_db=# \d+ dba_activity_vacuum
View "public.dba_activity_vacuum"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------------+---------+-----------+----------+---------+----------+-------------
relname | name | | | | plain |
age | integer | | | | plain |
pid | integer | | | | plain |
datid | oid | | | | plain |
datname | name | | | | plain |
relid | oid | | | | plain |
command | text | | | | extended |
phase | text | | | | extended |
heap_blks_total | bigint | | | | plain |
heap_blks_scanned | bigint | | | | plain |
heap_blks_vacuumed | bigint | | | | plain |
index_vacuum_count | bigint | | | | plain |
max_dead_tuples | bigint | | | | plain |
num_dead_tuples | bigint | | | | plain |
View definition:
SELECT c1.relname,
age(c1.relfrozenxid) AS age,
v1.pid,
v1.datid,
v1.datname,
v1.relid,
'VACUUM'::text AS command,
v1.phase,
v1.heap_blks_total,
v1.heap_blks_scanned,
v1.heap_blks_vacuumed,
v1.index_vacuum_count,
v1.max_dead_tuples,
v1.num_dead_tuples
FROM pg_stat_progress_vacuum v1,
pg_class c1
WHERE v1.relid = c1.oid
UNION ALL
SELECT c2.relname,
age(c2.relfrozenxid) AS age,
v2.pid,
v2.datid,
v2.datname,
v2.relid,
v2.command,
v2.phase,
v2.heap_blks_total,
v2.heap_blks_scanned,
NULL::bigint AS heap_blks_vacuumed,
NULL::bigint AS index_vacuum_count,
NULL::bigint AS max_dead_tuples,
NULL::bigint AS num_dead_tuples
FROM pg_stat_progress_cluster v2,
pg_class c2
WHERE v2.relid = c2.oid;
Пример эксплуатации
Создайте таблицу, заполните ее данными, обновите достаточно большую долю данных, чтобы сработал AUTOVACUUM:
psql -d First_db <<EOF
drop table ttt_test;
create table ttt_test(id bigint, name text);
insert into ttt_test(id,name)
select a.t, lpad('',1000,'test')
from generate_series(1,3000000) a(t);
update ttt_test
set name = lpad('',1000,'TEST')
where mod(id,4) = 0;
EOF
Раз в несколько секунд проверяйте, не начал ли работать AUTOVACUUM:
psql -d First_db -c 'table dba_activity_vacuum'
relname | age | pid | datid | datname | relid | command | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
----------+-----+------+-------+----------+-------+---------+---------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
ttt_test | 2 | 9118 | 16800 | First_db | 17882 | VACUUM | scanning heap | 493434 | 48657 | 0 | 0 | 291 | 0
(1 row)
Запустите VACUUM FULL вручную в одном сеансе:
a => psql -d First_db -c 'VACUUM FULL ttt_test'
Проверьте в другом сеансе, что активность наблюдается посредством представления:
b => psql -d First_db -c 'table dba_activity_vacuum'
relname | age | pid | datid | datname | relid | command | phase | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
----------+-----+------+-------+----------+-------+-------------+-------------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
ttt_test | 3 | 7761 | 16800 | First_db | 17882 | VACUUM FULL | seq scanning heap | 535715 | 359999 | | | |
(1 row)
dba_waits: Построение отчета о распределении процессов по событиям ожидания
| Название поля | Тип | Описание поля |
|---|---|---|
| datname | name | Имя БД |
| cnt | bigint | Количество процессов, ожидающих на данном событии |
| wait_event | text | Событие ожидания |
| state | text | Статус процесса |
| wait_event_type | text | Тип события ожидания |
DDL:
First_db=# \d+ dba_waits
View "public.dba_waits"
Column | Type | Collation | Nullable | Default | Storage | Description
-----------------+--------+-----------+----------+---------+----------+-------------
datname | name | | | | plain |
state | text | | | | extended |
wait_event_type | text | | | | extended |
wait_event | text | | | | extended |
cnt | bigint | | | | plain |
View definition:
SELECT pg_stat_activity.datname,
pg_stat_activity.state,
pg_stat_activity.wait_event_type,
pg_stat_activity.wait_event,
count(*) AS cnt
FROM pg_stat_activity
GROUP BY pg_stat_activity.datname, pg_stat_activity.state, pg_stat_activity.wait_event_type, pg_stat_activity.wait_event
ORDER BY pg_stat_activity.state, (count(*)) DESC, pg_stat_activity.datname;
Пример эксплуатации
В данном примере наблюдается картина, использованная для демонстрации представления dba_locks (мониторин г дерева блокировок):
psql -d First_db -c 'table dba_waits'
datname | state | wait_event_type | wait_event | cnt
----------+---------------------+-----------------+-------------------------+-----
First_db | active | Lock | transactionid | 2
First_db | active | Lock | tuple | 2
First_db | active | | | 1
First_db | idle | Client | ClientRead | 1
postgres | idle | Extension | Extension | 1
First_db | idle in transaction | Client | ClientRead | 1
| | Activity | BgWriterHibernate | 1
| | Activity | PasswordPolicyCacheMain | 1
| | Activity | WalWriterMain | 1
| | Activity | LogicalLauncherMain | 1
| | Activity | CheckpointerMain | 1
| | Activity | AutoVacuumMain | 1
| | Activity | PerfInsightsMain | 1
(13 rows)
dba_bloat_wastedbytes: Построение списка таблиц, отсортированных по объему пространства, не занятого для хранения актуальных версий записей
| Название поля | Тип | Описание поля |
|---|---|---|
| nspname | name | Имя схемы |
| tabname | name | Имя таблицы, к которой относится отношение |
| relname | name | Имя отношения |
| reltype | text | Тип отношения |
| tsize | text | Размер отношения (в байтах) |
| tplcnt | bigint | Количество актуальных версий записей в отношени и |
| wspaceprc | double precision | Оценка доли пространства в отношении, не занятого для хранения актуальных версий записей |
| wspaceb | text | Оценка размера пространства (в байтах) в отношении, не занятого для хранения актуальных версий записей |
DDL:
First_db=# \d+ dba_bloat_wastedbytes
View "public.dba_bloat_wastedbytes"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------------+---------+-----------+----------+---------+----------+-------------
nspname | name | | | | plain |
tblname | name | | | | plain |
relname | name | | | | plain |
relkind | "char" | | | | plain |
relsize | text | | | | extended |
wstd_space | text | | | | extended |
bloat | numeric | | | | main |
min_poss_bloat | numeric | | | | main |
reltuples | bigint | | | | plain |
avg_tpl_size | integer | | | | plain |
avg_full_tpl_size | bigint | | | | plain |
max_rows_per_page | integer | | | | plain |
View definition:
SELECT foo3.nspname,
foo3.tblname,
foo3.relname,
foo3.relkind,
pg_size_pretty(foo3.relpages::numeric * foo3.block_size) AS relsize,
pg_size_pretty((foo3.relpages::double precision - foo3.expected_pages)::integer::numeric * foo3.block_size) AS wstd_space,
round(((foo3.relpages::double precision - foo3.expected_pages) / foo3.relpages::double precision)::numeric, 2) AS bloat,
round(((foo3.page_usable_space - foo3.avg_full_tpl_size::double precision * foo3.max_rows_per_page) / foo3.page_usable_space)::numeric, 2) AS min_poss_bloat,
foo3.reltuples::bigint AS reltuples,
foo3.datawidth AS avg_tpl_size,
foo3.avg_full_tpl_size,
foo3.max_rows_per_page::integer AS max_rows_per_page
FROM ( SELECT foo2.nspname,
foo2.tblname,
foo2.relname,
foo2.relkind,
foo2.relpages,
foo2.reltuples,
foo2.fillfactor,
foo2.hdr,
foo2.maxalign,
foo2.block_size,
foo2.datawidth,
foo2.hdr_and_nullbits,
foo2.datawidth_ma,
foo2.hdr_and_nullbits_ma,
foo2.avg_full_tpl_size,
foo2.page_usable_space,
foo2.avg_full_tpl_size::double precision * foo2.reltuples / foo2.page_usable_space AS expected_pages,
floor(foo2.page_usable_space / foo2.avg_full_tpl_size::double precision) AS max_rows_per_page
FROM ( SELECT foo1.nspname,
foo1.tblname,
foo1.relname,
foo1.relkind,
foo1.relpages,
foo1.reltuples,
foo1.fillfactor,
foo1.hdr,
foo1.maxalign,
foo1.block_size,
foo1.datawidth,
foo1.hdr_and_nullbits,
foo1.datawidth_ma,
foo1.hdr_and_nullbits_ma,
CASE
WHEN foo1.relkind = 'i'::"char" THEN (foo1.datawidth_ma + 8 + 4)::bigint
ELSE foo1.datawidth_ma + foo1.hdr_and_nullbits_ma + 4
END AS avg_full_tpl_size,
foo1.fillfactor::double precision / 100::double precision * (foo1.block_size - 24::numeric)::double precision AS page_usable_space
FROM ( SELECT foo.nspname,
foo.tblname,
foo.relname,
foo.relkind,
foo.relpages,
foo.reltuples,
foo.fillfactor,
foo.hdr,
foo.maxalign,
foo.block_size,
foo.datawidth,
foo.hdr_and_nullbits,
foo.datawidth + foo.maxalign -
CASE
WHEN (foo.datawidth % foo.maxalign) = 0 THEN foo.maxalign
ELSE foo.datawidth % foo.maxalign
END AS datawidth_ma,
foo.hdr_and_nullbits + foo.maxalign -
CASE
WHEN (foo.hdr_and_nullbits % foo.maxalign::bigint) = 0 THEN foo.maxalign::bigint
ELSE foo.hdr_and_nullbits % foo.maxalign::bigint
END AS hdr_and_nullbits_ma
FROM ( SELECT ns.nspname,
rels.tblname,
rels.relname,
rels.relkind,
rels.relpages,
rels.reltuples,
CASE
WHEN "position"(rels.reloptions::text, 'fillfactor='::text) > 0 THEN (regexp_match(rels.reloptions::text, 'fillfactor=(\d+)'::text))[1]
ELSE
CASE
WHEN rels.relkind = 'i'::"char" THEN '90'::text
ELSE '100'::text
END
END AS fillfactor,
constants.hdr,
constants.maxalign,
constants.block_size,
sum((1::double precision - COALESCE(stat.null_frac, 0::real)) * COALESCE(stat.avg_width, 2048)::double precision)::integer AS datawidth,
constants.hdr + 1 + sum(
CASE
WHEN stat.null_frac <> 0::double precision THEN 1
ELSE 0
END) / 8 AS hdr_and_nullbits
FROM ( SELECT tbl.relnamespace,
tbl.oid AS reloid,
tbl.relname,
tbl.relkind,
tbl.relpages,
tbl.reltuples,
tbl.reloptions,
tbl.relname AS tblname
FROM pg_class tbl
WHERE (tbl.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND tbl.relpages > 10
UNION ALL
SELECT ind.relnamespace,
ind.oid AS reloid,
ind.relname,
ind.relkind,
ind.relpages,
ind.reltuples,
ind.reloptions,
indtbl.relname AS tblname
FROM pg_class ind
JOIN pg_index pgi ON pgi.indexrelid = ind.oid
JOIN pg_class indtbl ON indtbl.oid = pgi.indrelid
WHERE ind.relkind = 'i'::"char" AND ind.relpages > 10) rels
JOIN pg_namespace ns ON ns.oid = rels.relnamespace
JOIN pg_attribute att ON att.attrelid = rels.reloid
LEFT JOIN pg_stats stat ON stat.schemaname = ns.nspname AND stat.tablename = rels.tblname AND stat.attname = att.attname AND stat.inherited = false,
( SELECT ( SELECT current_setting('block_size'::text)::numeric AS current_setting) AS block_size,
CASE
WHEN "substring"(split_part(foo_1.v, ' '::text, 2), '#"[0-9]+.[0-9]+#"%'::text, '#'::text) = ANY (ARRAY['8.0'::text, '8.1'::text, '8.2'::text]) THEN 27
ELSE 23
END AS hdr,
CASE
WHEN foo_1.v ~ 'mingw32'::text OR foo_1.v ~ '64-bit'::text THEN 8
ELSE 4
END AS maxalign
FROM ( SELECT version() AS v) foo_1) constants
WHERE att.attnum > 0
GROUP BY ns.nspname, rels.tblname, rels.relname, rels.relkind, rels.relpages, rels.reltuples, (
CASE
WHEN "position"(rels.reloptions::text, 'fillfactor='::text) > 0 THEN (regexp_match(rels.reloptions::text, 'fillfactor=(\d+)'::text))[1]
ELSE
CASE
WHEN rels.relkind = 'i'::"char" THEN '90'::text
ELSE '100'::text
END
END), constants.hdr, constants.maxalign, constants.block_size) foo) foo1) foo2) foo3
WHERE (foo3.relpages::double precision - foo3.expected_pages)::integer > 0
ORDER BY (foo3.relpages::double precision - foo3.expected_pages) DESC
LIMIT 200;
Пример эксплуатации
Создайте таблицу, заполните ее данными и измените их. Поскольку используется fillfactor=100 (по умолчанию), при обновлении часто придется переносить записи в другие страницы. Размер записи с заголовками составляет ~1044 байта, в одну страницу будет помещаться 7 записей. Поэтому даже после VACUUM FULL останется какое-то количество незаполненного пространства (хотя таблица и станет меньше):
psql -d First_db <<EOF
drop table ttt_test;
create table ttt_test(id bigint, name text);
create index on ttt_test(name,id);
insert into ttt_test(id,name)
select a.t, lpad('',1000,'test') || a.t
from generate_series(1,100000) a(t);
update ttt_test
set name = lpad('',1000,'TEST') || id
where mod(id,10) = 0;
EOF
Подождите, пока AUTOVACUUM не закончит собирать статистику по новой таблице:
psql -d First_db -c 'table dba_bloat_wastedbytes'
nspname | tblname | relname | relkind | relsize | wstd_space | bloat | min_poss_bloat | reltuples | avg_tpl_size | avg_full_tpl_size | max_rows_per_page
--------------+--------------------+---------------------------------+---------+---------+------------+-------+----------------+-----------+--------------+-------------------+-------------------
ext | ttt_test | ttt_test | r | 123 MB | 23 MB | 0.19 | 0.11 | 100000 | 1016 | 1044 | 7
public | pgbench_accounts | pgbench_accounts | r | 128 MB | 1864 kB | 0.01 | 0.01 | 1000000 | 97 | 132 | 61
pg_catalog | pg_proc | pg_proc | r | 1624 kB | 544 kB | 0.34 | 0.01 | 3393 | 286 | 324 | 25
pg_catalog | psql_omd | psql_omd | r | 464 kB | 256 kB | 0.55 | 0.00 | 4098 | 24 | 52 | 157
pgse_profile | last_stat_indexes | last_stat_indexes | r | 440 kB | 224 kB | 0.52 | 0.02 | 435 | 465 | 500 | 16
...
psql -d First_db -c 'VACUUM FULL ttt_test'
psql -d First_db -c 'table dba_bloat_wastedbytes'
nspname | tblname | relname | relkind | relsize | wstd_space | bloat | min_poss_bloat | reltuples | avg_tpl_size | avg_full_tpl_size | max_rows_per_page
--------------+--------------------+---------------------------------+---------+---------+------------+-------+----------------+-----------+--------------+-------------------+-------------------
ext | ttt_test | ttt_test | r | 112 MB | 12 MB | 0.11 | 0.11 | 100000 | 1016 | 1044 | 7
public | pgbench_accounts | pgbench_accounts | r | 128 MB | 1864 kB | 0.01 | 0.01 | 1000000 | 97 | 132 | 61
pg_catalog | pg_proc | pg_proc | r | 1624 kB | 544 kB | 0.34 | 0.01 | 3393 | 286 | 324 | 25
pg_catalog | psql_omd | psql_omd | r | 464 kB | 256 kB | 0.55 | 0.00 | 4098 | 24 | 52 | 157
pgse_profile | last_stat_indexes | last_stat_indexes | r | 440 kB | 224 kB | 0.52 | 0.02 | 435 | 465 | 500 | 16
...
Оценка доли пространства, не используемого для актуальных версий записей, снизилась до минимально возможной оценки (bloat = min_poss_bloat).
dba_bloat_tbloat: Построение списка таблиц, отсортированных по доле пространства, не з анятого для хранения актуальных версий записей
| Название поля | Тип | Описание поля |
|---|---|---|
| nspname | name | Имя схемы |
| tabname | name | Имя таблицы, к которой относится отношение |
| relname | name | Имя отношения |
| reltype | text | Тип отношения |
| tsize | text | Размер отношения (в байтах) |
| tplcnt | bigint | Количество актуальных версий записей в отношении |
| wspaceprc | double precision | Оценка доли пространства в отношении, не занятого для хранения актуальных версий записей |
| wspaceb | text | Оценка размера пространства (в байтах) в отношении, не занятого для хранения актуальных версий записей |
DDL:
First_db=# \d+ dba_bloat_tbloat
View "public.dba_bloat_tbloat"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------------+---------+-----------+----------+---------+----------+-------------
nspname | name | | | | plain |
tblname | name | | | | plain |
relname | name | | | | plain |
relkind | "char" | | | | plain |
relsize | text | | | | extended |
wstd_space | text | | | | extended |
bloat | numeric | | | | main |
min_poss_bloat | numeric | | | | main |
reltuples | bigint | | | | plain |
avg_tpl_size | integer | | | | plain |
avg_full_tpl_size | bigint | | | | plain |
max_rows_per_page | integer | | | | plain |
View definition:
SELECT foo3.nspname,
foo3.tblname,
foo3.relname,
foo3.relkind,
pg_size_pretty(foo3.relpages::numeric * foo3.block_size) AS relsize,
pg_size_pretty((foo3.relpages::double precision - foo3.expected_pages)::integer::numeric * foo3.block_size) AS wstd_space,
round(((foo3.relpages::double precision - foo3.expected_pages) / foo3.relpages::double precision)::numeric, 2) AS bloat,
round(((foo3.page_usable_space - foo3.avg_full_tpl_size::double precision * foo3.max_rows_per_page) / foo3.page_usable_space)::numeric, 2) AS min_poss_bloat,
foo3.reltuples::bigint AS reltuples,
foo3.datawidth AS avg_tpl_size,
foo3.avg_full_tpl_size,
foo3.max_rows_per_page::integer AS max_rows_per_page
FROM ( SELECT foo2.nspname,
foo2.tblname,
foo2.relname,
foo2.relkind,
foo2.relpages,
foo2.reltuples,
foo2.fillfactor,
foo2.hdr,
foo2.maxalign,
foo2.block_size,
foo2.datawidth,
foo2.hdr_and_nullbits,
foo2.datawidth_ma,
foo2.hdr_and_nullbits_ma,
foo2.avg_full_tpl_size,
foo2.page_usable_space,
foo2.avg_full_tpl_size::double precision * foo2.reltuples / foo2.page_usable_space AS expected_pages,
floor(foo2.page_usable_space / foo2.avg_full_tpl_size::double precision) AS max_rows_per_page
FROM ( SELECT foo1.nspname,
foo1.tblname,
foo1.relname,
foo1.relkind,
foo1.relpages,
foo1.reltuples,
foo1.fillfactor,
foo1.hdr,
foo1.maxalign,
foo1.block_size,
foo1.datawidth,
foo1.hdr_and_nullbits,
foo1.datawidth_ma,
foo1.hdr_and_nullbits_ma,
CASE
WHEN foo1.relkind = 'i'::"char" THEN (foo1.datawidth_ma + 8 + 4)::bigint
ELSE foo1.datawidth_ma + foo1.hdr_and_nullbits_ma + 4
END AS avg_full_tpl_size,
foo1.fillfactor::double precision / 100::double precision * (foo1.block_size - 24::numeric)::double precision AS page_usable_space
FROM ( SELECT foo.nspname,
foo.tblname,
foo.relname,
foo.relkind,
foo.relpages,
foo.reltuples,
foo.fillfactor,
foo.hdr,
foo.maxalign,
foo.block_size,
foo.datawidth,
foo.hdr_and_nullbits,
foo.datawidth + foo.maxalign -
CASE
WHEN (foo.datawidth % foo.maxalign) = 0 THEN foo.maxalign
ELSE foo.datawidth % foo.maxalign
END AS datawidth_ma,
foo.hdr_and_nullbits + foo.maxalign -
CASE
WHEN (foo.hdr_and_nullbits % foo.maxalign::bigint) = 0 THEN foo.maxalign::bigint
ELSE foo.hdr_and_nullbits % foo.maxalign::bigint
END AS hdr_and_nullbits_ma
FROM ( SELECT ns.nspname,
rels.tblname,
rels.relname,
rels.relkind,
rels.relpages,
rels.reltuples,
CASE
WHEN "position"(rels.reloptions::text, 'fillfactor='::text) > 0 THEN (regexp_match(rels.reloptions::text, 'fillfactor=(\d+)'::text))[1]
ELSE
CASE
WHEN rels.relkind = 'i'::"char" THEN '90'::text
ELSE '100'::text
END
END AS fillfactor,
constants.hdr,
constants.maxalign,
constants.block_size,
sum((1::double precision - COALESCE(stat.null_frac, 0::real)) * COALESCE(stat.avg_width, 2048)::double precision)::integer AS datawidth,
constants.hdr + 1 + sum(
CASE
WHEN stat.null_frac <> 0::double precision THEN 1
ELSE 0
END) / 8 AS hdr_and_nullbits
FROM ( SELECT tbl.relnamespace,
tbl.oid AS reloid,
tbl.relname,
tbl.relkind,
tbl.relpages,
tbl.reltuples,
tbl.reloptions,
tbl.relname AS tblname
FROM pg_class tbl
WHERE (tbl.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND tbl.relpages > 10
UNION ALL
SELECT ind.relnamespace,
ind.oid AS reloid,
ind.relname,
ind.relkind,
ind.relpages,
ind.reltuples,
ind.reloptions,
indtbl.relname AS tblname
FROM pg_class ind
JOIN pg_index pgi ON pgi.indexrelid = ind.oid
JOIN pg_class indtbl ON indtbl.oid = pgi.indrelid
WHERE ind.relkind = 'i'::"char" AND ind.relpages > 10) rels
JOIN pg_namespace ns ON ns.oid = rels.relnamespace
JOIN pg_attribute att ON att.attrelid = rels.reloid
LEFT JOIN pg_stats stat ON stat.schemaname = ns.nspname AND stat.tablename = rels.tblname AND stat.attname = att.attname AND stat.inherited = false,
( SELECT ( SELECT current_setting('block_size'::text)::numeric AS current_setting) AS block_size,
CASE
WHEN "substring"(split_part(foo_1.v, ' '::text, 2), '#"[0-9]+.[0-9]+#"%'::text, '#'::text) = ANY (ARRAY['8.0'::text, '8.1'::text, '8.2'::text]) THEN 27
ELSE 23
END AS hdr,
CASE
WHEN foo_1.v ~ 'mingw32'::text OR foo_1.v ~ '64-bit'::text THEN 8
ELSE 4
END AS maxalign
FROM ( SELECT version() AS v) foo_1) constants
WHERE att.attnum > 0
GROUP BY ns.nspname, rels.tblname, rels.relname, rels.relkind, rels.relpages, rels.reltuples, (
CASE
WHEN "position"(rels.reloptions::text, 'fillfactor='::text) > 0 THEN (regexp_match(rels.reloptions::text, 'fillfactor=(\d+)'::text))[1]
ELSE
CASE
WHEN rels.relkind = 'i'::"char" THEN '90'::text
ELSE '100'::text
END
END), constants.hdr, constants.maxalign, constants.block_size) foo) foo1) foo2) foo3
WHERE (foo3.relpages::double precision - foo3.expected_pages)::integer > 0
ORDER BY (round(((foo3.relpages::double precision - foo3.expected_pages) / foo3.relpages::double precision)::numeric, 2)) DESC
LIMIT 200;
Пример эксплуатации
В данном разделе приведен пример вывода списка таблиц с самой большой долей пространства, не используемого для актуальных версий записей.
Очень часто наверху списка оказываются словарные небольшие таблицы, поэтому использование представления dba_bloat_wastedbytes в общем случае более полезно:
psql -d First_db -c 'table dba_bloat_tbloat'
nspname | tblname | relname | relkind | relsize | wstd_space | bloat | min_poss_bloat | reltuples | avg_tpl_size | avg_full_tpl_size | max_rows_per_page
--------------+--------------------+---------------------------------+---------+---------+------------+-------+----------------+-----------+--------------+-------------------+-------------------
public | pgbench_tellers | pgbench_tellers | r | 216 kB | 208 kB | 0.98 | 0.00 | 100 | 12 | 44 | 185
pg_catalog | psql_omd | psql_omd_oid_index | i | 208 kB | 120 kB | 0.57 | 0.00 | 4098 | 8 | 20 | 367
pg_catalog | psql_omd | psql_omd | r | 464 kB | 256 kB | 0.55 | 0.00 | 4098 | 24 | 52 | 157
pgse_profile | last_stat_tables | last_stat_tables | r | 336 kB | 176 kB | 0.53 | 0.00 | 318 | 468 | 508 | 16
pgse_profile | last_stat_indexes | last_stat_indexes | r | 440 kB | 224 kB | 0.51 | 0.02 | 436 | 465 | 500 | 16
...
Выполните полную очистку таблицы с самой большой долей неиспользуемого пространства. В данном случае это pgbench_tellers:
psql -d First_db -c 'VACUUM FULL pgbench_tellers'
Перепроверьте информацию по таблице:
psql -d First_db -c 'table dba_bloat_tbloat'
Поскольку таблица пропала из выборки dba_bloat_tbloat (доля неиспользуемого пространства стала околонулевой), можно проверить ее текущий размер посредством команды:
psql -d First_db -c "select relpages*8192 as relation_size from pg_class where relname = 'pgbench_tellers'"
relation_size
---------------
8192
(1 row)
Размер таблицы действительно уменьшился до одной страницы.
dba_unused_indexes: Построение списка неиспользуемых индексов
| Название поля | Тип | Описание поля |
|---|---|---|
| schemaname | name | Имя схемы |
| relname | name | Имя таблицы, к которой относится индекс |
| index_name | name | Имя индекса |
| indexdef | text | «Определение» индекса: алгоритм индексирования, список колонок индекса, дополнительные свойства (набор INCLUDE колонок, ограничения WHERE, параметры хранения) |
| index_size | text | Размер индекса |
| last_db_stats_reset | timestamp with time zone | Время последнего сброса статистики по БД |
DDL:
First_db=# \d+ dba_unused_indexes
View "public.dba_unused_indexes"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+--------------------------+-----------+----------+---------+----------+-------------
schema_name | name | | | | plain |
tab_name | name | | | | plain |
idx_name | name | | | | plain |
idx_def | text | | | | extended |
idx_size | text | | | | extended |
last_db_stats_reset | timestamp with time zone | | | | plain |
View definition:
SELECT pgsai.schemaname AS schema_name,
pgsai.relname AS tab_name,
pgsai.indexrelname AS idx_name,
"substring"(pgi.indexdef, "position"(pgi.indexdef, ' USING '::text) + 7) AS idx_def,
pg_size_pretty(pg_relation_size(pgsai.indexrelid::regclass)) AS idx_size,
stats_reset.stats_reset AS last_db_stats_reset
FROM pg_stat_all_indexes pgsai,
pg_indexes pgi,
( SELECT pg_stat_database.stats_reset
FROM pg_stat_database
WHERE pg_stat_database.datname = current_database()) stats_reset
WHERE pgsai.idx_scan = 0 AND (pgsai.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pgsai.relname !~~ 'pg_toast%'::text AND pgi.schemaname = pgsai.schemaname AND pgi.tablename = pgsai.relname AND pgi.indexname = pgsai.indexrelname
ORDER BY (pg_relation_size(pgsai.indexrelid::regclass)) DESC
LIMIT 30;
Пример эксплуатации
Создайте таблицу с двумя индексами:
psql -d First_db <<EOF
drop table ttt_test;
create table ttt_test(
id bigint,
name text
);
insert into ttt_test
select
a.t,
'name ' || a.t
from generate_series(1,100000) a(t);
create index ttt_test_id_name_unused on ttt_test(id,name);
create index ttt_test_name_id_unused on ttt_test(name,id);
EOF
В верхней части списка неиспользуемых индексов наблюдаются оба индекса (поскольку они крупнее остальных неиспользуемых индексов):
psql -d First_db -c 'table dba_unused_indexes'
schema_name | tab_name | idx_name | idx_def | idx_size | last_db_stats_reset
--------------+------------------------------+---------------------------------+------------------------------------------------------+------------+-------------------------------
ext | ttt_test | ttt_test_name_id_unused | btree (name, id) | 3984 kB | 2023-07-13 12:31:05.637901+03
ext | ttt_test | ttt_test_id_name_unused | btree (id, name) | 3976 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_statements | ix_sample_stmts_qid | btree (queryid_md5) | 32 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_kcache | pk_sample_kcache_n | btree (server_id, sample_id, datid, userid, queryid) | 32 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_kcache_total | pk_sample_kcache_total | btree (server_id, sample_id, datid) | 16 kB | 2023-07-13 12:31:05.637901+03
...
Используйте один из индексов:
explain analyze
select * from ttt_test where name = 'name 1000';
Index Only Scan using ttt_test_name_id_unused on ttt_test (cost=0.42..2.44 rows=1 width=19) (actual time=0.072..0.072 rows=1 loops=1)
Index Cond: (name = 'name 1000'::text)
Heap Fetches: 0
Planning Time: 0.328 ms
Execution Time: 0.113 ms
Перепроверьте список неиспользуемых индексов:
psql -d First_db -c 'table dba_unused_indexes'
schema_name | tab_name | idx_name | idx_def | idx_size | last_db_stats_reset
--------------+------------------------------+---------------------------------+------------------------------------------------------+------------+-------------------------------
ext | ttt_test | ttt_test_id_name_unused | btree (id, name) | 3976 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_statements | ix_sample_stmts_qid | btree (queryid_md5) | 32 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_kcache | pk_sample_kcache_n | btree (server_id, sample_id, datid, userid, queryid) | 32 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_kcache_total | pk_sample_kcache_total | btree (server_id, sample_id, datid) | 16 kB | 2023-07-13 12:31:05.637901+03
pgse_profile | sample_statements_total | pk_sample_statements_total | btree (server_id, sample_id, datid) | 16 kB | 2023-07-13 12:31:05.637901+03
...
Индекс ttt_test_name_id_unused из списка пропал, поскольку был использован.
dba_duplicated_indexes: Построение списка индексов, перекрываемых другими индексами
| Название поля | Тип | Описание поля |
|---|---|---|
| tab_name | text | Имя схемы + имя таблицы |
| idx_name | regclass | Имя индекса |
| idx_def | text | «Определение» индекса: алгоритм индексирования, список колонок индекса, дополнительные свойства (набор INCLUDE колонок, ограничения WHERE, параметры хранения) |
| used_by_constraints | text | Список ограничений целостности, использующих данный индекс (имя ограничения целостности и тип) |
| idx_size | bigint | Размер индекса |
| cover_idx_name | regclass | Имя перекрывающего индекса |
| cover_idx_def | text | «Определение» перекрывающего индекса: алгоритм индексирования, список колонок индекса, дополнительные свойства (набор INCLUDE колонок, ограничения WHERE, параметры хранения) |
| cover_used_by_constraints | text | Список ограничений целостности, использующих перекрывающий индекс (имя ограничения целостности и тип) |
DDL:
First_db=# \d+ dba_duplicated_indexes
View "public.dba_duplicated_indexes"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------------+--------+-----------+----------+---------+----------+-------------
schema_name | name | | | | plain |
tab_name | name | | | | plain |
idx_name | name | | | | plain |
idx_def | text | | | | extended |
used_by_constraints | text | C | | | extended |
idx_size | bigint | | | | plain |
cover_idx_name | name | | | | plain |
cover_idx_def | text | | | | extended |
cover_used_by_constraints | text | C | | | extended |
View definition:
WITH indlist AS (
SELECT pgi.schemaname AS schema_name,
pgi.tablename AS table_name,
pgi.indexname AS index_name,
"substring"(pgi.indexdef, "position"(pgi.indexdef, ' USING '::text) + 7) AS indexdef,
( SELECT string_agg(attrs.attname::text, ', '::text) AS string_agg
FROM ( SELECT a.attname
FROM pg_attribute a
WHERE a.attrelid = ((pgi.schemaname::text || '.'::text) || pgi.indexname::text)::regclass::oid
ORDER BY a.attnum) attrs) AS column_list,
( SELECT string_agg(((c.conname::text || '('::text) || c.contype::text) || ')'::text, ','::text) AS string_agg
FROM pg_constraint c
WHERE c.conindid = ((pgi.schemaname::text || '.'::text) || pgi.indexname::text)::regclass::oid) AS used_by_constraints,
pg_relation_size(((pgi.schemaname::text || '.'::text) || pgi.indexname::text)::regclass) AS index_size
FROM pg_indexes pgi
WHERE pgi.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name, 'pg_toast'::name])
)
SELECT il1.schema_name,
il1.table_name AS tab_name,
il1.index_name AS idx_name,
il1.indexdef AS idx_def,
il1.used_by_constraints,
il1.index_size AS idx_size,
il2.index_name AS cover_idx_name,
il2.indexdef AS cover_idx_def,
il2.used_by_constraints AS cover_used_by_constraints
FROM indlist il1,
indlist il2
WHERE il2.schema_name = il1.schema_name AND il2.table_name = il1.table_name AND il2.index_name <> il1.index_name AND "position"(il2.column_list, il1.column_list) = 1
ORDER BY il1.index_size DESC
LIMIT 30;
Пример эксплуатации
Создайте таблицу с несколькими индексами, часть из которых «перекрывает» друг друга:
psql -d First_db <<EOF
drop table ttt_test;
create table ttt_test(
id bigint primary key,
name text
);
create index ttt_test_id_test on ttt_test using hash(id);
create index ttt_test_id_name_test on ttt_test(id,name);
create index ttt_test_name_id_test on ttt_test(name,id);
EOF
Вывод списка дублирующих индексов:
psql -d First_db -c 'table dba_duplicated_indexes'
schema_name | tab_name | idx_name | idx_def | used_by_constraints | idx_size | cover_idx_name | cover_idx_def | cover_used_by_constraints
-------------+----------+------------------+------------+---------------------+----------+-----------------------+------------------+---------------------------
ext | ttt_test | ttt_test_id_test | hash (id) | | 49152 | ttt_test_id_name_test | btree (id, name) |
ext | ttt_test | ttt_test_id_test | hash (id) | | 49152 | ttt_test_pkey | btree (id) | ttt_test_pkey(p)
ext | ttt_test | ttt_test_pkey | btree (id) | ttt_test_pkey(p) | 8192 | ttt_test_id_name_test | btree (id, name) |
ext | ttt_test | ttt_test_pkey | btree (id) | ttt_test_pkey(p) | 8192 | ttt_test_id_test | hash (id) |
(4 rows)
В полученном списке наблюдаются индексы ttt_test_id_test и ttt_test_pkey по два раза, потому что каждый из них может быть использован вместо другого, индекс ttt_test_id_name_test, у которого первая колонка также id, может быть использован вместо каждого из них.