Radiofisik

my knowledge base

Postgres

Нагрузка на базу

-- current activity
SELECT pg_blocking_pids(pid), *  
FROM pg_stat_activity  
where query_start < now() - interval '1 minute';

-- Disk I/O for tables  
SELECT schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit  
FROM pg_statio_user_tables  
ORDER BY heap_blks_read DESC;  
  
-- Disk I/O for indexes  
SELECT schemaname, relname, idx_blks_read, idx_blks_hit  
FROM pg_statio_user_indexes  
ORDER BY idx_blks_read DESC;

--writes
SELECT  
    schemaname,  
    relname,  
    n_tup_ins AS rows_inserted,  
    n_tup_upd AS rows_updated,  
    n_tup_del AS rows_deleted  
FROM  
    pg_stat_user_tables  
ORDER BY  
    (n_tup_ins + n_tup_upd + n_tup_del) DESC;

-- biggest tables
SELECT  
    schemaname AS schema_name,  
    tablename AS table_name,  
    pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"')) AS total_size,  
    pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || tablename || '"')) AS table_size,  
    pg_size_pretty(pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') - pg_relation_size('"' || schemaname || '"."' || tablename || '"')) AS index_size  
FROM  
    pg_tables  
WHERE  
    schemaname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY  
    pg_total_relation_size('"' || schemaname || '"."' || tablename || '"') DESC;

--reset
select pg_stat_reset();  
select pg_stat_statements_reset();  

-- top20
SELECT query,    
              round(total_exec_time::numeric, 2) AS total_time,    
              calls,    
              round(mean_exec_time::numeric, 2) AS mean,    
              round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu    
FROM  pg_stat_statements  
order by percentage_cpu desc  
LIMIT 20;

Не пора ли сделать вакуум

тут можно смотреть сколько процентов используется полезными tuple а сколько уже за горизонтом БД и занимают место

create extension pgstattuple;  
  
select *  
from pgstattuple_approx('tableName');

массовая проверка

-- Create a temporary table to store results  
CREATE TEMP TABLE temp_pgstattuple_results (  
    table_name TEXT,  
    tuple_percent FLOAT8  
);  
  
DO $$  
DECLARE  
    table_record RECORD;  
    table_name TEXT;  
    stat_result RECORD;  
    sql_query TEXT;  
BEGIN  
    FOR table_record IN  
        SELECT schemaname, tablename  
        FROM pg_tables  
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
    LOOP  
        table_name := quote_literal(quote_ident(table_record.schemaname) || '.' || quote_ident(table_record.tablename));  
        sql_query := format('SELECT %L AS table_name, * FROM pgstattuple_approx(%s);', table_name, table_name);  
        RAISE NOTICE 'Executing query: %', sql_query;  
         EXECUTE sql_query INTO stat_result;  
  
        INSERT INTO temp_pgstattuple_results  
        VALUES (  
            stat_result.table_name,  
            stat_result.approx_tuple_percent  
        );  
  
    END LOOP;  
END $$;  
  
-- Select from the temporary table to see the results  
SELECT * FROM temp_pgstattuple_results;

если процент tuple мал пора сделать

VACUUM FULL

также вакуум может быть необходимо сделать заблоговременного чтобы избежать wrap around. номер транзакции имеет конечное значение и если оно превзойдет то что прописано в SHOW autovacuum_freeze_max_age; Текущий возраст можно посмотреть запросами

SELECT  
    relname,  
    age(relfrozenxid) AS xid_age,  
    relfrozenxid  
FROM  
    pg_class  
WHERE  
    relkind = 'r'  
ORDER BY  
    age(relfrozenxid) DESC  
LIMIT 100;


SELECT  
    datname,  
    age(datfrozenxid) AS xid_age,  
    datfrozenxid  
FROM  
    pg_database  
ORDER BY  
    age(datfrozenxid) DESC;

Анализ эффективности кеширования

можно посмотреть распределение количества буферов и их использования


CREATE EXTENSION  pg_buffercache;  
  
SELECT usagecount, count(*)  
from pg_buffercache  
group by usagecount  
order by usagecount;

Распределение и использование кеша по отношениям

SELECT c.relname,  
       count(*)                       blocks,  
       round(100.0 * 8192 * count(*) /  
             pg_table_size(c.oid)) AS "% of rel",  
       round(100.0 * 8192 * count(*) FILTER (WHERE b.usagecount > 1) /  
             pg_table_size(c.oid)) AS "% hot"  
FROM pg_buffercache b  
         JOIN pg_class c ON pg_relation_filenode(c.oid) = b.relfilenode  
WHERE b.reldatabase IN (  
                        0, -- общие объекты кластера  
                        (SELECT oid FROM pg_database WHERE datname = current_database())  
    )  
  AND b.usagecount IS NOT NULL  
GROUP BY c.relname, c.oid  
ORDER BY 2 DESC  
LIMIT 50;

Режимы журнала

Есть два режима работы журнала синхронный и асинхронный. При синхронном при комите транзакции продолжение работы не возможно пока все изменения не окажутся на диске. При асинхронном транзакция комитится сразу, а данные сбрасываются на диск в фоне. В синхронном режиме есть параметр commit_delay который добавляет задержку к комиту если в системе не менее commit_siblings активных транзакций в надежде что они завершаться и на диск удастся скинуть все за раз. Асинхронный режим можно включить

ALTER SYSTEM SET synchronous_commit = off;  
SELECT pg_reload_conf();

а выключить

ALTER SYSTEM RESET synchronous_commit;
SELECT pg_reload_conf();

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

Утилита pg_test_fsync помогает определить наиболее подходящий способ синхронизации журнала для конкретной операционной системы и конкретной файловой системы. Выбранный способ указывается в параметре wal_sync_method. В остальных случаях способ выбирается автоматически1 .

Блокировки

Запросы длящиеся больше минуты и блокирующие их запросы можно посмотреть

SELECT pg_blocking_pids(pid), *  
FROM pg_stat_activity  
where query_start < now() - interval '1 minute';

Использование индексов

SELECT
    t.relname AS имя_таблицы,
    i.relname AS имя_индекса,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch
FROM
    pg_stat_user_indexes s
JOIN
    pg_index x ON s.indexrelid = x.indexrelid
JOIN
    pg_class t ON x.indrelid = t.oid
JOIN
    pg_class i ON s.indexrelid = i.oid
WHERE
  -- s.idx_scan < 50 and
t.relname = 'TableName';

Links: