Skip to main content

Мониторинг и анализ

jinx-the-cat.gif

Неплохой мониторинг из коробки может предоставить заббикс: https://www.zabbix.com/integrations/postgresql

https://sematext.com/blog/postgresql-performance-tuning/

Если база нагруженная, для диагностики будет проще поднять еще одну СУБД на каком-нибудь отдельном хосте, хоть на виртуалке, и крутить там параметры как угодно.

Как вообще работает постгря?

Важно партиционирование и индексы. Надо нарезать большие таблицы на более мелкие (партиционировать их) и создавать индексы для столбцов, которые чаще всего используются. Но не стоит переусердствовать с индексами.

Куда смотреть на мониторинге? Как мониторить? Что мониторить?

Понятное дело, надо мониторить саму железку. Сеть, проц, озу, нагрузку на диски. Советую настроить мониторинг тем же заббиксом, там из коробки мониторится много полезных параметров.

Мониторим запросы, таблицы, индексы

Можно накостылять такой скриптец и поместить его в крон:


#!/bin/bash

result=$(sudo -u postgres psql  -c "select query_start, state, query from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null and (now() - pg_stat_activity.query_start) > interval '1 minutes';")

echo $result | grep '0 rows' && exit

echo ok

echo $result >> /root/log

Лучше будет включить логгирование медленных запросов (src)


log_min_duration_statement = 1000 # миллисекунды
logging_collector = on
log_directory = 'pg_log'

Обязателен перезапуск базы, pg_reload_conf() не поможет. На лету можно только длительность запроса менять. Если нельзя перезапускать, то придется жить с костылем.

Посмотрим еще размер таблиц:


SELECT 
  table_name, 
  pg_size_pretty( pg_total_relation_size(quote_ident(table_name))), 
  pg_total_relation_size(quote_ident(table_name))
FROM 
  information_schema.tables
WHERE 
  table_schema = 'public' and table_catalog = 'URDATABASE'
ORDER BY 
  pg_total_relation_size(quote_ident(table_name)) DESC;

Если есть что-то сильно жирное - надо партиционировать.

Смотрим индексы


\d table
\di+


Статистика в запросах

https://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/


  • Cache Hit Rate

Будет прекрасно, если hit rate будет около 99%. Смотреть вот так (предварительно зайти в нужную базу):

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;


  • Index Usage

Статистика использования индексов. Заходим в базу и выполняем:

SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 and n_live_tup > 10000
ORDER BY 
  n_live_tup DESC;

While there is no perfect answer, if you’re not somewhere around 99% on any table over 10,000 rows you may want to consider adding an index.

  • Index cache hit rate

Должно быть близко к 99%. Заходим в базу и выполняем:

SELECT 
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM 
  pg_statio_user_indexes;


Детальный анализ

https://its.1c.ru/db/metod8dev/content/6011/hdoc

Чтобы получить статистику по запросам необходимо подключить соотв-ие модули

SELECT current_setting('shared_preload_libraries');
-- Смотрим какие либы подключены, дописываем их в следующем запросе вместо ...,
ALTER SYSTEM SET shared_preload_libraries = ..., 'auto_explain', 'pg_stat_statements';

После этого необходимо перезапустить базу. Релоад не работает.

Настраиваем

-- auto_explain
ALTER SYSTEM SET auto_explain.log_min_duration = '10s'; -- записывать в лог план для запросов >= 10 сек.
ALTER SYSTEM SET auto_explain.log_analyze = true;
SELECT pg_reload_conf(); -- применение настроек

-- pg_statements
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
SELECT pg_reload_conf(); -- применение настроек
\с ИМЯ_БАЗЫ
CREATE EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset(); -- для сброса накопленной статистики..

-- Просто slow log
ALTER SYSTEM SET lc_messages = 'C'; -- все сообщения на английском языке
ALTER SYSTEM SET logging_collector = on; -- потребуется перезапуск сервера
ALTER SYSTEM SET log_directory = 'log';
ALTER SYSTEM SET log_min_duration_statement = 30s; -- 0-все запросы. ВНИМАНИЕ. Сбор всех запросов может ухудшить производительность при высокой нагрузке. Поэтому тут укажем 30s
ALTER SYSTEM SET log_duration = on;


Настраиваем

Базовые настройки можно взять отсюда: https://pgtune.leopard.in.ua/

Коннектов указывать лучше в районе 100, больше для 1С не надо (учитывай кол-во баз, если их несколько).

Back to top