Перейти к основному содержанию
`Ctrl`+`K`

# Инструкция по настройке и работе с расширением 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;