Se existem consultas no seu ambiente que apresentam os problemas com volume de dados conforme acima apresentados, e cujos resultados são demandados por um ou mais usuá- rios, considere a possibilidade de não disponibilizar essas consultas de forma online, mas sim como relatórios cuja execução pode ser programada.
Um erro muito comum é criar relatórios para usuários, às vezes fechamentos mensais ou até anuais, e disponibilizar um link no sistema para o usuário gerá-lo a qualquer instante. Relatórios devem ser pré-processados, agendados para executar à noite e de madrugada, e apresentar o resultado para o usuário pela manhã.
Integrações entre sistemas por vezes também são processos pesados que não devem ser executados em horário de pico. Cargas grandes de escrita ou leitura para integração de dados entre sistemas feitas com ferramentas de ETL, Web Services ou outras soluções simi- lares devem ter o horário controlado ou serem pulverizadas entre diversas chamadas com pouco volume de dados a cada vez.
Se ainda assim existem consultas pesadas que precisem ser executadas a qualquer momento, ou relatórios que não podem ter seus horários de execução restringidos, con- sidere usar Replicação (será tratada na sessão 10) para criar servidores slaves, onde essas consultas poderão ser executadas sem prejudicar as operações normais do sistema.
Bloqueios
O PostgreSQL controla a concorrência e garante o Isolamento (o “I” das propriedades ACID) com um mecanismo chamado Multi-Version Concurrency Control (MVCC). Devido ao MVCC, problemas de bloqueios – ou locks – no PostgreSQL são pequenos. Esse mecanismo basica- mente cria versões dos registros, que podem estar sendo manipulados simultaneamente por transações diferentes, cada uma tendo uma visão dos dados, chamada snapshot. Essa é uma excelente forma de evitar contenção por locks. A forma mais simples de explicar a vantagem desse mecanismo é que no PostgreSQL uma leitura nunca bloqueia uma escrita e uma escrita nunca bloqueia uma leitura.
Existem diversas ferramentas, como o Pentaho, Jasper Server e outras comerciais, que possuem facilidades para geração de relatórios, executando as consultas agendadas e fazendo cache ou snapshots dos resultados. Desse modo, toda vez que um usuário pede determinado relatório, a consulta não é mais disparada contra o banco, mas extraída desse snapshot dos dados.
Ad m in is tr aç ão d e B an co d e D ad os
Explicado isso, fica claro que situações de conflitos envolvendo locks são restritas a operações de escritas concorrentes. Na prática, a maioria das situações estão ligadas a operações de UPDATE. Os INSERTs geram informação nova, não havendo concorrência. Já os DELETEs podem também apresentar problemas com locks, mas são bem mais raros. Até porque uma prática comum em muitos sistemas é não remover seus registros, apenas marcá-los como inativos ou não usados.
Mesmo as situações de conflitos geradas por locks em UPDATEs não chegam a ser um pro- blema, já que são situações rotineiras na medida em que o lock é um mecanismo de controle de compartilhamento comum do banco. Problemas surgem de fato quando uma transação que faz um lock demora para terminar ou quando ocorre um deadlock. A seguir analisamos cada uma dessas situações.
Lembre-se, locks não são um problema. Problema é a transação não liberar o lock! Quando um lock é obtido sobre um registro para ser feito um UPDATE, por exemplo, ele somente será liberado ao final da transação que o obteve. Se a transação tiver muitas ope- rações após ter adquirido o lock, ou não envie o comando de final de transação por algum bug ou outro motivo qualquer, ela pode dar origem a vários processos bloqueados, podendo criar um problema em cascata.
BEGIN TRANSACTION; ...
...
UPTADE contas SET... ... ... ... COMMIT; Lock Adquirido Lock Liberado
Essa situação é mais frequente com o uso de camadas de persistência e frameworks, pois o desenvolvedor não escreve mais o código SQL. Ele não é mais responsável por abrir ou fechar transações explicitamente, muito provavelmente apenas “marcando” o seu método como transacional, deixando para as camadas de acesso a dados a execução das operações de BEGIN e COMMIT (ou ROLLBACK).
A solução é sempre fazer a transação o mais curta possível. Deve ser encontrado o motivo pelo qual a transação está demorando, providenciando a reescrita desta se necessário. Vimos na sessão de aprendizagem sobre monitoramento que podemos localizar os locks através do pg_activity, do pgAdmin e da tabela do catálogo pg_locks. É possível também ras- trear queries envolvidas em longas esperas por locks ligando o parâmetro log_lock_waits. No postgresql.conf, defina:
log_lock_waits = on
Serão registradas mensagens no log como esta (o prefixo da linha foi suprimido para clareza): user=aluno,db=curso LOG: process 15186 still waiting for ExclusiveLock on tuple ( 0,7) of relation 24584 of database 16384 after 1002.912 ms
user=aluno,db=curso STATEMENT: UPDATE grupos SET nome = ‘X’ WHERE id=7;
Com os IDs dos processos é possível localizar na log as operações correspondentes para entender o que as transações fazem e avaliar se podem ser melhoradas.
Figura 7.5
Transações longas e bloqueios.
Ca pí tu lo 7 - D es em pe nh o – T óp ic os s ob re a pl ic aç ão
Se o bloqueio estiver causando problemas graves, a solução pode ser simplesmente matar o processo.
Se o processo bloqueante for eliminado, deverá ser visto no log algo como o seguinte: user=postgres,db=postgres LOG: statement: SELECT pg_terminate_backend(‘8905’) user=aluno,db=curso LOG: process 15186 acquired ExclusiveLock on tuple (0,7) of r elation 24584 of database 16384 after 1601203.086 ms
user=aluno,db=curso STATEMENT: UPDATE grupos SET nome = ‘X’ WHERE id=7; No exemplo da figura 7.5, o processo 8905 está há mais de 637 minutos executando. Um olhar mais cuidadoso nos dados de CPU, READ/s e WRITE/s permitirá concluir que o pro- cesso não está consumindo recursos, simplesmente está parado. O comando exibido, nesse caso um UPDATE, pode não estar mais em execução, embora tenha sido o último comando executado pela transação. Isso pode ser verificado na tabela pg_stat_activity, onde estado do processo é IDLE IN TRANSACTION. Processos nesse estado por longo tempo são o pro- blema a ser resolvido, mas é um comportamento que varia de aplicação para aplicação. Além dos problemas com locks relacionados a escritas de dados como UPDATE e DELETE, há as situações menos comuns e mais fáceis de identificar envolvendo DDL. Comandos como ALTER TABLE e CREATE INDEX também bloquearão escritas de dados. Essas alterações de modelo devem ocorrer em horário de baixa atividade do sistema.
Uma outra situação que pode ocorrer são bloqueios gerados por causa do autovacuum. Se uma tabela passar por uma grande alteração de dados, ela é grande candidata a sofrer autovacuum, potencialmente gerando problemas de performance. Se uma situação como essa ocorrer, uma alternativa é eliminar o processo e configurar a tabela para não ter autovacuum. Mas o bloqueio mais “famoso” é o deadlock. Essa é uma situação especial de lock, necessaria- mente envolvendo mais de um recurso, no nosso caso provavelmente registros, onde cada processo obteve um registro e está esperando o do outro ser liberado, o que nunca aconte- cerá. É uma situação clássica na ciência da computação sobre concorrência de recursos. Deadlocks somente ocorrerão se existirem programas que acessam registros em ordens inversas. Se houver uma lógica geral de ordem de acesso aos registros, garantindo que os programas sempre acessam os recursos na mesma ordem, um deadlock nunca acontecerá.
Figura 7.6
Transações antigas boqueando processos.
Ad m in is tr aç ão d e B an co d e D ad os PostgreSQL Lock no registro A Processo 1 Processo 2 Esperando registro B Lock no registro B Esperando registro A
O PostgreSQL detecta deadlocks, verificando a ocorrência deles em um intervalo de tempo definido pelo parâmetro deadlock_timeout, por padrão a cada 1 segundo. Se um deadlock for identificado, o PostgreSQL escolherá um dos processos como “vítima” e a respectiva operação será abortada. Nesses casos poderá ser vista a seguinte mensagem no log:
LOG: process 16192 detected deadlock while waiting for ShareLock on transaction 837 after 1005.635 ms
STATEMENT: UPDATE grupos SET nome=’Z’ WHERE id = 1; ERROR: deadlock detected
DETAIL: Process 16192 waits for ShareLock on transaction 837; blocked by process 15186.
Process 15186 waits for ShareLock on transaction 838; blocked by process 16192. O valor do parâmetro deadlock_timeout geralmente é razoável para a maioria dos usos. Caso estejam ocorrendo muitos locks e deadlocks, seu valor pode ser baixado para ajudar na depuração do problema, mas isso tem um preço, já que o algoritmo de busca por deadlocks é relativamente custoso.
Se deadlocks estão ocorrendo com frequência, então programas, scripts e procedures devem ser revistos e verificada a ordem que estão acessando registros.
Nas versões anteriores do PostgreSQL, havia algumas situações envolvendo Foreign Keys que podiam gerar deadlocks. Isso foi corrigido nas versões mais novas.