Como posso verificar se meu banco de dados precisa de mais RAM?

Question

Como você verificaria se sua instância de banco de dados postgresql precisa de mais memória RAM para manipular seus dados de trabalho atuais?

    

Answer 1

Se você está no Linux, sua RAM física total deve ser maior que o tamanho do banco de dados no disco para minimizar a E / S. Eventualmente, o banco de dados inteiro estará no cache de leitura do SO e a E / S ficará limitada a confirmar alterações no disco. Eu prefiro encontrar o tamanho do banco de dados executando "du -shc $ PGDATA / base" - esse método agrega todos os bancos de dados em um único número. Contanto que você seja maior que isso, tudo bem.

Além disso, você pode observar a taxa de acertos do cache de buscas de blocos de heap e índice. Estes medem a taxa de acertos nos buffers compartilhados do PostgreSQL. Os números podem ser um pouco enganadores - mesmo que possa ter sido um erro no cache de buffers compartilhados, ele ainda pode ser um sucesso no cache de leitura do sistema operacional. Ainda assim, as ocorrências em buffers compartilhados ainda são menos dispendiosas do que as ocorrências no cache de leitura do SO (que, por sua vez, são menos dispendiosas em algumas ordens de magnitude do que ter que voltar ao disco).

Para ver a taxa de acertos dos buffers compartilhados, eu uso essa consulta:

SELECT relname, heap_blks_read, heap_blks_hit,
    round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read),3)
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY 4
LIMIT 25;

Isso dá a você os 25 piores criminosos em que o cache de buffer é perdido para todas as tabelas onde pelo menos um bloco teve que ser buscado em "disco" (novamente, que pode ser o cache de leitura do SO ou E / S de disco real) ). Você pode aumentar o valor na cláusula WHERE ou adicionar outra condição para o heap_blks_hit para filtrar tabelas raramente usadas.

A mesma consulta básica pode ser usada para verificar a taxa de acerto do índice total por tabela substituindo globalmente a string "heap" por "idx". Dê uma olhada no pg_statio_user_indexes para obter uma análise por índice.

Uma nota rápida sobre buffers compartilhados: uma boa regra prática para isso no Linux é definir o parâmetro de configuração shared_buffers para 1/4 de RAM, mas não mais que 8GB. Esta não é uma regra rígida e rápida, mas sim um bom ponto de partida para ajustar um servidor. Se o seu banco de dados é de apenas 4 GB e você tem um servidor de 32 GB, 8 GB de buffers compartilhados é realmente um exagero e você deve ser capaz de definir isso para 5 ou 6 GB e ainda ter espaço para crescimento futuro.

    

Answer 2

Eu fiz este SQL para mostrar a proporção de acertos de tabela vs disco:

-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with 
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name, 
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as 
(
SELECT  *
FROM    (
    SELECT  relname as table_name, 
        ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        ( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT  table_name as "table name",
    from_disk as "disk hits",
    round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
    round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
    (from_disk + from_cache) as "total hits"
FROM    (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER   BY (case when table_name = 'all' then 0 else 1 end), from_disk desc

    

Answer 3

Também funciona, como dito no documento do Heroku:

SELECT
    'cache hit rate' AS name,
     sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;