Administrar um banco de dados PostgreSQL não e uma tarefa fácil pois existem muitas tarefas diárias que precisam de muita atenção como verificar backups, aplicar alterações de DDL, garantir que os erros que apareçam nos logs sejam tratados e atender as mais diversas chamadas de problemas de desenvolvedores cujos relatórios estão rodando com certa lentidão em alguns casos duas vezes mais do que o normal.
Mesmo com uma boa integridade dos bancos de dados gerenciados, sempre haverá novos casos e novos problemas relacionados ao desempenho e à sensação do banco de dados esta lento.
Esse guia ira explanar as etapas que precisam ser executadas, mas em nenhuma ordem específica. Mas é um conjunto de etapas iniciais que podem ser tomadas para ajudar a encontrar rapidamente os problemas comuns, além de obter novas idéias sobre qual pode ser o problema. Um desenvolvedor sabe como o aplicativo se comporta e responde, mas o Administrador do Banco de Dados sabe como o banco de dados age e responde ao aplicativo e, juntos, o problema pode ser encontrado.
NOTA: As consultas a serem executadas devem ser feitas como um superusuário, como ‘postgres’ ou qualquer usuário do banco de dados com permissões de superusuário. Usuários limitados serão negados ou terão dados omitidos.
Passo 1 – Coleta de informações
Obtenha o máximo de informações possível sobre o banco estar lento; consultas específicas, aplicativos conectados, prazos da lentidão de desempenho e mais. Quanto mais informações eles fornecerem, mais fácil será encontrar o problema.
Passo 2 – Verifique pg_stat_activity
O problema pode vir de várias formas, mas se “lentidão” for o problema geral, verificar pg_stat_activity é o primeiro passo para entender exatamente o que está acontecendo. A visualização pg_stat_activity contém uma linha para cada processo / conexão do servidor com o banco de dados de um cliente. Há algumas informações úteis nessa visão que podem ajudar.
NOTA: Sabe-se que pg_stat_activity altera a estrutura ao longo do tempo, refinando os dados que apresenta. O entendimento das próprias colunas ajudará a criar consultas dinamicamente conforme necessário no futuro.
As colunas importantes no pg_stat_activity são:
- query: uma coluna de texto mostrando a consulta que está sendo executada no momento, aguardando para ser executada ou que foi executada pela última vez (dependendo do estado). Isso pode ajudar a identificar quais consultas / consultas um desenvolvedor pode estar relatando em execução lenta.
- client_addr: o endereço IP do qual esta conexão e consulta se originaram. Se empty ou null originou-se do host local.
- backend_start, xact_start, query_start: esses três fornecem um registro de data e hora de quando cada um foi iniciado, respectivamente. Backend_start representa quando a conexão com o banco de dados foi estabelecida, xact_start é quando a transação atual foi iniciada e query_start é quando a consulta atual (ou última) foi iniciada.
- state: O estado da conexão com o banco de dados. Ativo significa que está executando uma consulta no momento, ‘inativo’ significa que está aguardando mais informações do cliente, ‘inativo na transação’ significa que está aguardando mais informações do cliente enquanto mantém uma transação aberta. (Existem outros, por mais rara que seja sua probabilidade, consulte a documentação para obter mais informações).
- datname: o nome do banco de dados ao qual a conexão está atualmente conectada. Em vários clusters de banco de dados, isso pode ajudar a isolar conexões problemáticas.
- wait_event_type e wait_event: essas colunas serão nulas quando uma consulta não estiver aguardando, mas se estiver aguardando, elas conterão informações sobre o motivo pela qual a consulta está aguardando, e explorar pg_locks pode identificar o que está aguardando. (O PostgreSQL 9.5 e anterior possui apenas uma coluna booleana chamada ‘waiting’, true se aguardando, false se não.
1.1 A consulta está aguardando / bloqueada?
Se houver uma consulta específica ou “lenta” ou “travada”, verifique se eles estão aguardando a conclusão de outra consulta. Devido ao bloqueio de relação, outras consultas podem bloquear uma tabela e não permitir que outras consultas acessem ou alterem dados até que essa consulta ou transação seja concluída.
PostgreSQL 9.5 e versões anteriores:
| 1 |
SELECT * FROM pg_stat_activity WHERE waiting = TRUE;
|
PostgreSQL 9.6:
| 1 |
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL;
|
PostgreSQL 10 e posterior:
| 1 |
SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL AND backend_type = 'client backend';
|
Os resultados desta consulta mostrarão todas as conexões atualmente aguardando outra conexão para liberar bloqueios em uma relação necessária.
Se a consulta for bloqueada por outra conexão, existem algumas maneiras de descobrir exatamente o que são. No PostgreSQL 9.6 e posterior, a função pg_blocking_pids () permite a entrada de um ID do processo que está sendo bloqueado e retornará uma matriz de IDs do processo responsáveis por bloqueá-lo.
PostgreSQL 9.6 e posterior:
| 1 2 |
SELECT * FROM pg_stat_activity
WHERE pid IN (SELECT pg_blocking_pids(<pid of blocked query>));
|
PostgreSQL 9.5 e versões anteriores:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename
AS blocked_user,
blocking_locks.pid
AS blocking_pid,
blocking_activity.usename
AS blocking_user,
blocked_activity.query
AS blocked_statement,
blocking_activity.query
AS current_statement_in_blocking_process
FROM
pg_catalog.pg_locks
blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid
= blocked_locks.pid
JOIN pg_catalog.pg_locks
blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
|
Essas consultas apontam para o que estiver
bloqueando um PID específico fornecido. Com isso, pode-se tomar uma
decisão para eliminar a consulta ou conexão de bloqueio ou deixá-la em
execução.
Passo 2 – Se as consultas estão em execução, por que estão demorando tanto?
2.1 O planejador está executando consultas com eficiência?
Se uma consulta (ou conjunto de consultas) em questão tiver o status ‘ativo’, ela estará em execução. Se a consulta inteira não estiver disponível em pg_stat_activity, busque-a nos desenvolvedores ou no log do postgresql e comece a explorar o planejador de consultas.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXPLAIN SELECT * FROM postgres_stats.table_stats t JOIN hosts h ON (t.host_id = h.host_id) WHERE logged_date >= '2018-02-01' AND logged_date < '2018-02-04' AND t.india_romeo = 569;
Nested Loop (cost=0.280..1328182.030
rows=2127135 width=335)
-> Index Scan using six on victor_oscar echo (cost=0.280..8.290 rows=1
width=71)
Index Cond: (india_romeo = 569)
-> Append
(cost=0.000..1306902.390 rows=2127135 width=264)
->
Seq Scan on india_echo romeo (cost=0.000..0.000 rows=1
width=264)
Filter:
((logged_date >= '2018-02-01'::timestamp with time zone) AND (logged_date < '2018-02-04'::timestamp with time zone) AND (india_romeo = 569))
->
Seq Scan on juliet victor_echo (cost=0.000..437153.700
rows=711789 width=264)
Filter:
((logged_date >= '2018-02-01'::timestamp with time zone) AND (logged_date < '2018-02-04'::timestamp with time zone) AND (india_romeo = 569))
->
Seq Scan on india_papa quebec_bravo
(cost=0.000..434936.960 rows=700197 width=264)
Filter:
((logged_date >= '2018-02-01'::timestamp with time zone) AND (logged_date < '2018-02-04'::timestamp with time zone) AND (india_romeo = 569))
->
Seq Scan on two oscar (cost=0.000..434811.720 rows=715148
width=264)
Filter:
((logged_date >= '2018-02-01'::timestamp with time zone) AND (logged_date < '2018-02-04'::timestamp with time zone) AND (india_romeo = 569))
|
Este exemplo mostra um plano de consulta para uma junção de duas tabelas que também atinge uma tabela particionada. Estamos procurando por qualquer coisa que possa fazer com que a consulta seja lenta e, nesse caso, o planejador está fazendo várias varreduras seqüenciais nas partições, sugerindo que faltam índices. Adicionar índices a essas tabelas para a coluna ‘india_romeo’ melhorará instantaneamente esta consulta.
O que você deve procurar são varreduras seqüenciais, loops aninhados, classificação cara etc. O entendimento do planejador de consultas é crucial para garantir que as consultas tenham o melhor desempenho possível. A documentação oficial pode ser lida para obter mais informações .
2.2 As tabelas envolvidas estão inchadas?
Se as consultas ainda estiverem parecendo lentas sem que o planejador de consultas aponte algo óbvio, é hora de verificar a integridade das tabelas envolvidas.
| 1 2 3 4 5 |
SELECT n_live_tup, n_dead_tup from pg_stat_user_tables where relname = ‘mytable’;
n_live_tup | n_dead_tup
------------+------------
15677
| 8275431
(1 row)
|
Aqui vemos que há muitas vezes mais linhas mortas do que linhas ativas, o que significa que, para encontrar as linhas corretas, o mecanismo deve filtrar dados que nem são relevantes para encontrar dados reais. Um vacuum ou vacuum full nesta tabela aumentará significativamente o desempenho.
Passo 3 – Verifique os logs
Se o problema ainda não for encontrado, verifique os logs em busca de pistas.
Mensagens FATAL / ERRO:
Procure mensagens que possam estar causando problemas, como deadlocks ou longos tempos de espera para obter um bloqueio.
Pontos de verificação
Esperamos que log_checkpoints esteja ativado, o que gravará as informações do ponto de verificação nos logs. Existem dois tipos de pontos de verificação, cronometrados e solicitados (forçados). Se os pontos de verificação estiverem sendo forçados, buffers sujos na memória deverão ser gravados no disco antes de processar mais consultas, o que pode dar ao sistema de banco de dados uma sensação geral de “lentidão”. Aumentar os checkpoint_segments ou max_wal_size (dependendo da versão do banco de dados) dará ao checkpointer mais espaço para trabalhar, além de ajudar o gravador em segundo plano a suportar parte da carga de gravação.
Passo 4 – Qual é a saúde do host?
Se não houver pistas no próprio banco de dados, talvez o próprio host esteja sobrecarregado ou com problemas. Qualquer coisa, desde um canal de E / S sobrecarregado ao disco, memória transbordando para trocar ou até mesmo uma unidade com falha, nenhum desses problemas seria aparente em tudo o que examinamos antes. Supondo que o banco de dados esteja sendo executado em um sistema operacional baseado em * nix, aqui estão algumas coisas que podem ajudar.
4.1 Carga do sistema
Usando ‘top’, observe a média de carga do host. Se o número estiver se aproximando ou excedendo o número de núcleos no sistema, pode haver simplesmente muitas conexões simultâneas atingindo o banco de dados, trazendo-o para um rastreamento para recuperar o atraso.
| 1 |
load average: 3.43, 5.25, 4.85
|
4.2 Memória do sistema e SWAP
Usando ‘free’, verifique se o SWAP já foi usado. A memória que transborda para SWAP em um ambiente de banco de dados PostgreSQL é extremamente ruim para o desempenho, e muitos DBAs até eliminam o SWAP dos hosts do banco de dados, pois um erro de ‘falta de memória’ é mais preferível do que um sistema lento para muitos.
Se o SWAP estiver sendo usado, uma reinicialização do sistema irá limpá-lo, e o aumento da memória total do sistema ou a reconfiguração do uso da memória para o PostgreSQL (como a redução de shared_buffers ou work_mem) podem estar em ordem.
| 1 2 3 4 |
[postgres@livedb1 ~]$ free -m
total
used free
shared
buff/cache available
Mem:
7986
225
1297
12
6462 7473
Swap:
7987
2048 5939
|
4.3 Acesso ao disco
O PostgreSQL tenta fazer muito de seu trabalho na memória e espalha a gravação em disco para minimizar gargalos, mas em um sistema sobrecarregado com gravações pesadas, é fácil ver leituras e gravações pesadas, fazendo com que todo o sistema fique lento à medida que o atualiza. nas demandas. Discos mais rápidos, mais discos e canais de E / S são algumas maneiras de aumentar a quantidade de trabalho que pode ser feito.
Ferramentas como ‘iostat’ ou ‘iotop’ podem ajudar a identificar se há um gargalo no disco e de onde ele pode estar vindo.
4.4 Verifique os Logs
Se tudo mais falhar, ou mesmo se não, os logs sempre devem ser verificados para ver se o sistema está relatando algo que não está certo. Já discutimos a verificação do postgresql.logs, mas os logs do sistema podem fornecer informações sobre problemas como discos com falha, memória com falha, problemas de rede, etc. Qualquer um desses problemas pode fazer com que o banco de dados aja lento e imprevisível, portanto, um bom entendimento saúde perfeita pode ajudar a encontrar esses problemas.
Passo 5 – Ainda não resolveu?
Até os administradores mais experientes encontrarão algo novo que não faz sentido. É aí que a comunidade global do PostgreSQL pode entrar para ajudar. Quanto mais clara a informação fornecida à comunidade, mais fácil ela pode ajudar.
5.1 Listas de discussão do PostgreSQL
Como o PostgreSQL é desenvolvido e gerenciado pela comunidade de código aberto, existem milhares de pessoas que conversam nas listas de discussão para discutir inúmeros tópicos, incluindo recursos, erros e problemas de desempenho. As listas de discussão podem ser encontradas com facilidade na Internet.
