Приложение 5. Комментарии к настройке серверов БД

Производитель VALO Cloud рекомендует ознакомиться:

с документацией на PostgreSQL и утилитой pgtune;
сайтом https://postgresqlco.nf/doc/ru/param/

Стартовая конфигурация мастер-реплик:

тип СУБД принимается как OLTP;
тип накопителя - SSD;
размер RAM – 32 Gb;
количество CPU – 8;
максимальное количество соединений c БД – 200;

Комментарии по основным параметрам конфигурации postgresql.conf, напрямую зависящим от аппаратной конфигурации

№ п/п	Наименование параметра	Значение	Описание
1	max_connections	200	Максимальное количество соединений к БД. Зависит от условий эксплуатации и количества конкурентных пользователей Системы. Если это значение будет сильно расти, то значения параметров, связанных с выделением памяти следует пересмотреть
2	shared_buffers	RAM/4	Размер разделяемых буфферов памяти
3	effective_cache_size	RAM – shared_buffers	Оценка размера кеша файловой системы
4	maintenance_work_mem	RAM/16	Объем памяти обслуживающих задач (вакуум, индексы ..)
5	checkpoint_completion_target	0.9	Коэффициент скорости записи во время checkpoint (0.7 – 0.9). По сути определяет длительность исполнения checkpoint
6	wal_buffers	wal_buffers = 16MB	Объём разделяемой памяти, который будет использоваться для буферизации данных, перед сбросом на диск журнала. Более высокое значение при большом количестве подключений может повысить производительность
7	default_statistics_target	100	Объем собираемой статистики таблиц для выполнения analyze. Чем выше значение, тем выше издержки за счет процессов autoanalyze/autovacuum, но тем точнее план запроса
8	random_page_cost	1.1	Приблизительная стоимость чтения страницы с диска. Значение характерно для указанного типа накопителя. Регулируется на основе процента попаданий в кэш
9	effective_io_concurrency	200	Оценочное значение одновременных запросов к дисковой системе. Значение характерно для указанного типа накопителя
10	work_mem	10485kB	Лимит памяти для обработки одного запроса (например с использованием сортировок)
11	min_wal_size, max_wal_size	2GB (от 2 до 4) * max_wal_size = от 4GB до 8GB	Минимальное и максимальный объем WAL файлов
12	max_parallel_workers_per_gather	4	Максимальное число рабочих процессов, которые могут запускаться одним узлом Gather при параллельном выполнении запроса
13	max_parallel_workers	8	Максимальное число рабочих процессов, которое система сможет поддерживать для параллельных операций
14	max_parallel_maintenance_workers	4	Максимальное число рабочих процессов, которые могут запускаться одной служебной командой (CREATE INDEX)
15	max_worker_processes	8	Максимальное число фоновых процессов максимальное число фоновых процессов. Для серверов, находящихся в резерве, этот параметр должен быть не менее того значения, которое указано на сервере БД, обслуживающем мастер-реплику
16	huge_pages	try	БД попробует использовать Huge Pages, если не получится переключится на обычные

Настраиваем huge_pages в ОС:

grep HUGETLB /boot/config-XXX - $(uname -r)
head -1 /var/lib/pgsql/11/data/postmaster.pid
19408

grep ^VmPeak /proc/19408/status
VmPeak : 2609244 kB

echo $((2609244/work_mem + 1))
5097

echo ’vm.nr_hugepages = 5097 ’ >> /etc/sysctl.conf

echo always > /sys/kernel/mm/transparent_hugepage/defrag

echo always > /sys/kernel/mm/transparent_hugepage/enabled

shutdown -r now

Комментарии по параметрам конфигурации postgresql.conf для сбора статистики и функционирования приложений VALO Cloud

№ п/п	Наименование параметра	Значение	Описание
1	join_collapse_limit	1	Предложения JOIN переставляться планировщиком не будут и явно заданный в запросе порядок отношений определит фактический порядок. Запросы в Системе оптимизированы под явно заданный порядок отношений
2	autovacuum	on	Автоочистка старых версий записей в таблицах. Работает совместно с процессом autoanalyze. Прежде чем трогать параметры этих процессов рекомендую тщательно ознакомиться с документацией. Дополнительно рекомендуется планировщиком запускать ‘vacuum full analyze’
3	track_counts	on	Включает сбор статистики активности в базе данных
4	checkpoint_timeout	1min	Параметр используется для установки времени между контрольными точками WAL. Установка слишком низкого значения уменьшает время восстановления после сбоя, поскольку на диск записывается больше данных, но это также снижает производительность, поскольку каждая контрольная точка потребляет системные ресурсы.
5	shared_preload_libraries	‘pg_stat_statements’	Включаем отслеживание статистики (после включения параметра выполнить в консоли CREATE EXTENSION pg_stat_statements;). Включаем анализ буфферного кэша ‘CREATE EXTENSION pg_buffercache;’
6	track_activity_query_size	32kB	Размер строки запроса, помещающийся в pg_stat_statements
7	max_pred_locks_per_transaction	256	Устанавливает максимальное количество предикатных блокировок на транзакцию
8	max_locks_per_transaction	256	Усредненное количество блокировок на одну транзакцию
9	max_prepared_transactions	200	Максимальное количество подготовленных транзакций
10	idle_in_transaction_session_timeout	30000	Отключаем по таймауту (мс.) простаивающие соединения. Их количество может увеличиваться из-за утечки JDBC-соединений
11	synchronous_commit	off	Отключена синхронная запись в WAL. Крайне рекомендуется ознакомиться с поведением

Примеры запросов для оценки состояния экземпляров БД

Использование буфферов кэша таблицами

SELECT c.relname, count(*) AS buffers 
    FROM pg_buffercache b INNER JOIN pg_class c 
    ON b.relfilenode = pg_relation_filenode(c.oid) AND 
    b.reldatabase IN (0, (SELECT oid FROM pg_database 
    WHERE datname = current_database())) 
    GROUP BY c.relname 
    ORDER BY 2 DESC 
    LIMIT 20;

Расположение объектов в кэше

SELECT c.relname, pg_size_pretty(count(*) * 8192) as buffered ,
round (100.0 * count(*) /
(SELECT setting FROM pg_settings WHERE name= 'shared_buffers') :: integer ,1)
AS buffers_percent, round(100.0 * count(*) * 8192/pg_table_size(c.oid) ,1)
AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database() )
GROUP BY c.oid , c.relname
ORDER BY 3 DESC
LIMIT 20;

Суммарное использование буфферного кэша

SELECT
'total', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
UNION SELECT
'dirty', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE isdirty
UNION SELECT
'clear', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE NOT isdirty
UNION SELECT
'used', pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE reldatabase IS NOT NULL
UNION SELECT
'free',pg_size_pretty(count(*) * (SELECT current_setting('block_size')::int))
FROM pg_buffercache
WHERE reldatabase IS NULL;

Использование буфферного кэша экземплярами БД

SELECT
d.datname,
pg_size_pretty(pg_database_size(d.datname)) AS database_size,
pg_size_pretty(count(b.bufferid) * (SELECT current_setting('block_size')::int)) AS size_in_shared_buffers,
round((100 * count(b.bufferid) / (SELECT setting FROM pg_settings WHERE name
= 'shared_buffers')::decimal),2) AS pct_of_shared_buffers
FROM pg_buffercache b
JOIN pg_database d ON b.reldatabase = d.oid
WHERE b.reldatabase IS NOT NULL
GROUP BY 1 ORDER BY 4 DESC LIMIT 10;

TOP 20 тяжелых запросов

SELECT substring(query, 1, 100) AS short_query, 
round(total_time::numeric, 2) AS total_time, 
calls, rows, rows/calls as rows_per_calls, 
round(mean_time::numeric, 2) AS mean_time, stddev_time,  
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu,
shared_blks_hit, local_blks_hit
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 20;

Запросы, вызвавшие блокировку

select                                         
a1.query as blocking_query,
a2.query as waiting_query,
t.schemaname ||'.'||t.relname as locked_table
from pg_stat_activity a1                                                                                
join pg_locks p1 on a1.pid = p1.pid and p1.granted
join pg_locks p2 on p1.relation = p2.relation and not p2.granted
join pg_stat_activity a2 on a2.pid = p2.pid
join pg_stat_all_tables t on p1.relation = t.relid;

Заблокированные таблицы

SELECT 
  pg_namespace.nspname as schemaname,
  pg_class.relname as tablename,
  pg_locks.mode as lock_type,
  age(now(),pg_stat_activity.query_start) AS time_running 
FROM pg_class
JOIN pg_locks on pg_locks.relation = pg_class.oid
JOIN pg_database on pg_database.oid = pg_locks.database
JOIN pg_namespace on pg_namespace.oid = pg_class.relnamespace
JOIN pg_stat_activity on pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relkind = 'r'
  AND pg_database.datname = current_database();

“Удаление” процессов с блокировками

select pid, state, wait_event, wait_event_type, query_start, query from pg_stat_activity order by query_start desc;
select pg_terminate_backend(значение pid); Поиск отсутствующих индексов

set schema to ‘tenant’;

--здесь tenant – интересующая схема компании/тенанта

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'tenant2'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

Статистика использования индексов

set search_path to “public”, “tenant”;

-- здесь tenant – интересующая схема компании/тенанта

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc 
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
( 
    SELECT 
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname 
    FROM pg_index AS pi
    JOIN pg_class AS pc 
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2 
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai 
        ON pi.indexrelid = psai.indexrelid 
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='tenant2'
ORDER BY 1;

Настраиваем huge_pages в ОС:​

Настраиваем huge_pages в ОС: