Инструкция по настройке и работе с расширением pg_stat_kcache

Инструкция по настройке и работе с расширением pg_stat_kcache#

Установка pg_stat_kcache#

  1. Установить pg_stat_kcache

    sudo apt install postgresql-16-pg-stat-kcache postgresql-contrib

  2. Зарегистрировать расширение в postgresql

    su postgres
psql

    Проверьте какие библиотеки у вас уже занесены в shared_preload_libraries

    show shared_preload_libraries;

    Пример вывода:

    postgres=# show shared_preload_libraries;
    shared_preload_libraries
-----------------------------------
<ваши расширения>
(1 строка)

    Дополнить shared_preload_libraries новыми расширениями, сохранив старые

    alter system set shared_preload_libraries = '<ваши расширения>,pg_stat_statements,pg_stat_kcache';

    Что-бы применить новые расширения, необходимо перезапустить postgresql

    sudo systemctl restart postgresql

  3. Создать расширение в нужной БД

    Подключитесь к нужной вам БД

    \c <ваша бд>

    Создайте расширения

    CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_kcache;

    Примечание

    Чтобы распространялось на новые БД — выполнить команды в БД template1.

  4. Проверка

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

    \dx

    Пример вывода:

    pg16=# \dx
                                        Список установленных расширений
        Имя         | Версия |   Схема    |                                Описание
--------------------+--------+------------+------------------------------------------------------------------------
...
pg_stat_kcache     | 2.3.0  | public     | Kernel statistics gathering
pg_stat_statements | 1.10   | public     | track planning and execution statistics of all SQL statements executed
...
(N строк)

Пример запросов#

Топ-20 по IO (exec_reads + exec_writes) с текстом запроса

WITH k AS (
  SELECT *
  FROM pg_stat_kcache_detail
  WHERE top IS TRUE
),
s AS (
  SELECT dbid, userid, toplevel, query,
         calls
  FROM pg_stat_statements
)
SELECT
  s.calls,
  (k.exec_reads + k.exec_writes)                  AS io_ops,
  k.exec_reads, k.exec_writes,
  round(k.exec_user_time::numeric, 3)   AS user_sec,
  round(k.exec_system_time::numeric, 3) AS sys_sec,
  left(k.query, 200)                    AS query_sample
FROM k
JOIN pg_database d ON d.datname = k.datname
JOIN pg_roles    u ON u.rolname = k.rolname
JOIN s ON s.dbid = d.oid
       AND s.userid = u.oid
       AND s.toplevel = k.top
       -- нормализуем пробелы в тексте запроса на обоих сторонах
       AND md5(regexp_replace(s.query, '\s+', ' ', 'g'))
           = md5(regexp_replace(k.query, '\s+', ' ', 'g'))
ORDER BY (k.exec_reads + k.exec_writes) DESC
LIMIT 20;

Топ-20 по CPU (exec_user_time + exec_system_time)

WITH k AS (
  SELECT *
  FROM pg_stat_kcache_detail
  WHERE top IS TRUE
),
s AS (
  SELECT dbid, userid, toplevel, query,
         calls
  FROM pg_stat_statements
)
SELECT
  s.calls,
  round((k.exec_user_time + k.exec_system_time)::numeric, 3) AS cpu_sec,
  round(k.exec_user_time::numeric, 3)   AS user_sec,
  round(k.exec_system_time::numeric, 3) AS sys_sec,
  left(k.query, 200)                    AS query_sample
FROM k
JOIN pg_database d ON d.datname = k.datname
JOIN pg_roles    u ON u.rolname = k.rolname
JOIN s ON s.dbid = d.oid
       AND s.userid = u.oid
       AND s.toplevel = k.top
       AND md5(regexp_replace(s.query, '\s+', ' ', 'g'))
           = md5(regexp_replace(k.query, '\s+', ' ', 'g'))
ORDER BY (k.exec_user_time + k.exec_system_time) DESC
LIMIT 20;

Быстрая сводка по БД (без джойнов)#

SELECT
  datname,
  (exec_reads + exec_writes) AS io_ops,
  exec_reads, exec_writes,
  round(exec_user_time::numeric, 3)   AS user_sec,
  round(exec_system_time::numeric, 3) AS sys_sec,
  stats_since
FROM pg_stat_kcache
ORDER BY io_ops DESC;
Содержание