Skip to main content

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

jinx-the-cat.gif

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

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

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

Инструкция больше про 1С

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

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

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

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

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

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


#!/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 = 1s; -- 0-все запросы. ВНИМАНИЕ. Сбор всех запросов может ухудшить производительность при высокой нагрузке. Поэтому тут укажем 1s
ALTER SYSTEM SET log_duration = on;

Логи будут в /var/lib/postgresql/15/main/log/
Теперь надо создать нагрузку. Выполнить типовые операции, прогнать тест Гилева и т.д..

Впринципе можно обойтись одним лишь slow_log'ом. Если таблица, на которой возник затуп, большая, то впринципе и так понятно, что проблема в ее размере.

 

Настраиваем

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

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

Подключим либы:

shared_preload_libraries = 'online_analyze, plantuner'
online_analyze.threshold = 50
online_analyze.scale_factor = 0.1
online_analyze.enable = on
online_analyze.verbose = off
online_analyze.min_interval = 10000
online_analyze.table_type = 'temporary'
plantuner.fix_empty_table = on

 

Модуль plantuner добавляет поддержку указаний для планировщика, позволяющих отключать или подключать определённые индексы при выполнении запроса.
F.46.1. Объяснение

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

Эта версия plantuner даёт возможность скрыть определённые индексы от планировщика Postgres Pro, чтобы он их не использовал. C некоторой нагрузкой Postgres Pro бывает слишком пессимистичным в отношении только что созданных таблиц и считает, что в них содержится гораздо больше строк, чем есть на самом деле. Если же переменная GUC plantuner.fix_empty_table имеет значение true, plantuner будет обнулять число страниц/кортежей в таблице, которая не содержит никаких блоков в файле.

Модуль online_analyze предоставляет набор функций, которые немедленно обновляют статистику после операций INSERT, UPDATE, DELETE или SELECT INTO в целевых таблицах.

online_analyze на самом деле включать не стоит. Подумай. https://it-expertise.ru/blog/records/parametr-online-analyze-postgresql-vs-1c-predpriyatie-8/ :

 Дело в том, что, начиная с версии 8.3.13, платформа 1С самостоятельно включает использование analyze явным образом после вставки во временную таблицу. 

 Так что при использовании актуальной версии платформы 1С на своих проектах с использованием PostgreSQL не нужно включать online_analyze.

Свои рекомендации, к слову, команда Постгрес актуализировала – сейчас в документации уже отсутствует устаревшая рекомендация.

 

Back to top