Olá novamente! Na próxima terça-feira, um novo fluxo será iniciado no curso
"DBMS relacional" , para que continuemos a publicar material útil sobre o assunto. Vamos lá
Na semana passada, escrevi sobre o
acesso competitivo no Postgres , quais equipes estão bloqueando uma à outra e como você pode diagnosticar equipes bloqueadas. Obviamente, após o diagnóstico, você pode precisar de tratamento. Com o Postgres, você pode dar um tiro no pé, mas o Postgres também oferece maneiras de não acertar a ponta. Aqui estão algumas dicas importantes sobre como e como não fazer isso que consideramos úteis ao trabalhar com usuários ao passar do banco de dados Postgres único para o
Citus ou ao criar novos aplicativos de
análise em tempo real .
1. Nunca adicione uma coluna com um valor padrão
Regra Golden PostgreSQL: Ao adicionar uma coluna a uma tabela em um ambiente de produção,
nunca especifique um valor padrão .
Adicionar uma coluna requer um bloqueio de tabela muito agressivo, que bloqueia a leitura e a gravação. Se você adicionar uma coluna com um valor padrão, o PostgreSQL substituirá a tabela inteira para preencher o valor padrão de cada linha, o que pode levar várias horas em tabelas grandes. Ao mesmo tempo, todas as solicitações serão bloqueadas, portanto, seu banco de dados não estará disponível.
Não faça isso:
Faça melhor assim:
Ou, melhor ainda, evite atualizar e
delete bloqueios por um longo tempo, atualizando em pequenos lotes, por exemplo:
do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0);
Dessa forma, você pode adicionar e preencher uma nova coluna com interferência mínima para seus usuários.
2. Cuidado com as filas de bloqueio, use tempos limite
Cada bloqueio no PostgreSQL tem uma prioridade. Se a transação B tentar se apossar de um bloqueio que já está retido pela transação A com um nível de bloqueio conflitante, a transação B aguardará na fila de bloqueio. Agora, algo interessante acontece: se outra transação C chegar, ela terá que verificar não apenas o conflito com A, mas também com a transação B e qualquer outra transação na fila de bloqueio.
Isso significa que, mesmo que seu comando DDL seja capaz de executar muito rapidamente, ele poderá ficar na fila por um longo tempo, aguardando a conclusão
das solicitações ,
e as solicitações que forem executadas depois dele serão bloqueadas por trás .
Se você encontrar longas consultas
SELECT em uma tabela, não faça o seguinte:
ALTER TABLE items ADD COLUMN last_update timestamptz;
Melhor fazer isso:
SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz;
Se
lock_timeout definido
lock_timeout comando DDL não será executado se estiver aguardando um bloqueio e, assim, bloqueia solicitações por mais de 2 segundos. A desvantagem é que sua
ALTER TABLE pode não ser executada, mas você pode tentar novamente mais tarde. Você pode consultar
pg_stat_activity para ver se possui consultas longas antes de executar o comando DDL.
3. Use criação de índice sem bloqueio
Outra regra de ouro do PostgreSQL: sempre use a criação de índices sem bloqueio.
A criação de um índice para um grande conjunto de dados pode levar horas ou até dias, e o comando
CREATE INDEX regular bloqueia todos os registros pela duração do comando. Apesar de não bloquear os SELECTs, ainda é muito ruim e existe uma maneira melhor:
CREATE INDEX CONCURRENTLY .
Não faça isso:
Em vez disso, faça o seguinte:
A criação de índice sem bloqueio tem uma desvantagem. Se algo der errado, ele não reverte e deixa um índice incompleto ("inválido"). Se isso acontecer, não se preocupe, basta correr
DROP INDEX CONCURRENTLY items_value_idx
e tente criá-lo novamente.
4. Use bloqueios agressivos o mais tarde possível
Quando você precisar executar um comando que receba bloqueios de tabela agressivos, tente fazer isso o mais tarde possível na transação, para que as consultas possam continuar o maior tempo possível.
Por exemplo, se você deseja substituir completamente o conteúdo da tabela. Não faça isso:
BEGIN;
Em vez disso, carregue os dados em uma nova tabela e substitua a antiga:
BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL);
Há um problema: não bloqueamos registros desde o início, e a tabela antiga de elementos poderia ter sido alterada quando a redefinimos. Para evitar isso, podemos bloquear explicitamente a tabela para escrever, mas não para ler:
BEGIN; LOCK items IN EXCLUSIVE MODE; ...
Às vezes é melhor aceitar o bloqueio com suas próprias mãos.
5. Adicionando uma chave primária com bloqueio mínimo
Adicionar uma chave primária às suas tabelas geralmente é uma boa ideia. Por exemplo, se você deseja usar a replicação lógica ou migrar um banco de dados usando o
Citus Warp .
O Postgres facilita a criação de uma chave primária usando
ALTER TABLE , mas, ao criar um índice para a chave primária, que pode levar muito tempo se a tabela for grande, todas as solicitações serão bloqueadas.
ALTER TABLE items ADD PRIMARY KEY (id);
Felizmente, você pode fazer todo o trabalho duro primeiro usando
CREATE UNIQUE INDEX CONCURRENTLY e, em seguida, use o índice exclusivo como chave primária, o que é uma operação rápida.
CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id);
A divisão da criação da chave primária em dois estágios praticamente não afeta o usuário.
6. Nunca use VÁCUO CHEIO
Às vezes, o postgres da experiência do usuário pode ser um pouco impressionante. Embora o
VACUUM FULL pareça o que você gostaria de fazer para limpar a "poeira" do seu banco de dados, um comando mais adequado seria:
PLEASE FREEZE MY DATABASE FOR HOURS;
VACUUM FULL sobrescreve a tabela inteira no disco, o que pode levar horas ou dias e, ao mesmo tempo, bloqueia todas as solicitações. Embora existam vários
VACUUM FULL uso válidos para
VACUUM FULL , como uma tabela que costumava ser grande, mas agora é pequena e ainda ocupa muito espaço, mas essa provavelmente não é sua opção.
Embora você deva se esforçar para configurar as opções de limpeza automática e usar índices para acelerar as consultas, às vezes você pode executar
VACUUM mas NÃO
VACUUM FULL .
7. Evite impasses organizando comandos
Se você usa o PostgreSQL há algum tempo, provavelmente já viu erros como:
ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.
Isso acontece quando transações simultâneas usam os mesmos bloqueios em uma ordem diferente. Por exemplo, uma transação executa os seguintes comandos.
BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello';
Ao mesmo tempo, outra transação pode emitir os mesmos comandos, mas em uma ordem diferente.
BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world';
Se esses blocos de transações forem executados ao mesmo tempo, é provável que eles fiquem presos esperando um pelo outro e nunca terminem. O Postgres reconhece essa situação em cerca de um segundo e cancelará uma das transações para concluir a outra. Quando isso acontece, você deve dar uma olhada no seu aplicativo para descobrir se você pode garantir que suas transações sejam sempre executadas na mesma ordem. Se ambas as transações mudarem
hello primeiro, depois
world , a primeira transação bloqueará o segundo
hello antes de poder capturar outros bloqueios.
Compartilhe suas dicas!
Esperamos que você ache essas recomendações úteis. Se você tiver outras dicas, sinta-se à vontade para twittar
@citusdata ou nossa comunidade ativa de usuários do Citus no
Slack .
Lembramos que em algumas horas haverá um
dia aberto no qual falaremos detalhadamente sobre o programa para o próximo curso.