Atenção: esse artigo tem um vídeo complementar. Clique e assista!
Este artigo apresenta a importância e o uso dos comandos vacuum, analyze, explain e count. Ao final do artigo você entenderá a importância de cada um deles.
Para que serve:
Ao fazer a leitura deste artigo, você entenderá como estes comandos impactam ou facilitam a análise de questões associadas a desempenho no PostgreSQL.
Em que situação o tema é útil:
No dia a dia da administração de bancos de dados PostgreSQL.
Resumo DevMan
Um componente chave de qualquer banco de dados é atender as propriedades ACID (Atomicidade, Consistência, Isolamento e Durabilidade). Em resumo, as regras ACID são o que protege os dados de um banco e dados, mantendo o controle de transações. Se um banco de dados não possui um mecanismo para atender as propriedades ACID, não existe nada para garantir que seus dados estejam seguros contra mudanças aleatórias que possam ocorrer. Neste sentido, este artigo apresenta descrições, explicações e exemplos para alguns dos principais comandos usados no PostgreSQL para gerenciamento de bancos de dados (VACUUM, ANALYZE,EXPLAIN e COUNT).
Um componente chave de qualquer banco de dados é atender as propriedades ACID (Atomicidade, Consistência, Isolamento e Durabilidade). Em resumo, as regras ACID são o que protege os dados de um banco e dados, mantendo o controle de transações. Se um banco de dados não possui um mecanismo para atender as propriedades ACID, não existe nada para garantir que seus dados estejam seguros contra mudanças aleatórias que possam ocorrer. Esse é o motive pelo qual todos os SGBDs atualmente atendem às propriedades ACID, ou seja, fazem controle de transações.
Focando um pouco mais na propriedade I (Isolamento), ela garante que múltiplos usuários acessando o mesmo dado obterão o mesmo resultado como se existisse apenas um usuário acessando o dado por vez.
Uma forma simples de garantir isso é não permitindo que qualquer usuário modifique uma parte dos dados se outros usuários estão no momento lendo este dado. Isso garante que o dado não mudará até que todas as leituras em andamento sejam finalizadas. Isso é feito usando um “bloqueio de leitura”, e é como a maioria dos bancos de dados trabalha. No entanto, bloqueio de leitura possui algumas sérias desvantagens.
Imagine um banco de dados que está sendo usado em um Web site. A maioria das páginas no site fará ao menos uma consulta ao banco de dados, e muitas páginas irão fazer várias consultas. É claro, existem outras páginas que também modificarão dados.
Agora lembre que para cada linha que é lida do banco de dados, um bloqueio de leitura deve ser adquirido. Então toda página irá adquirir muitos bloqueios, algumas vezes centenas deles. A todo momento que um bloqueio é adquirido ou liberado, o banco de dados não está processando seus dados; ele está “reclamando” da existência dos bloqueios.
E o que dizer das páginas que atualizam dados? Não podemos atualizar nada que está sendo lido, da mesma forma que qualquer coisa que está sendo atualizada não pode ser lida. Quando alguém quer atualizar dados, eles precisam aguardar até que todos aqueles que estão lendo o dado em questão finalizem suas leituras. Enquanto isso, para garantir que a pessoa que deseja atualizar esteja apta a eventualmente fazer essa operação, novas consultas que querem ler este dado são bloqueadas até que a atualização aconteça. Portanto, tendo apenas uma query que quer fazer uma atualização, temos uma grande quantidade de pessoas que estão aguardando a atualização concluir, e a atualização está aguardando a todas as leituras em andamento serem finalizadas.
Considere a mesma situação com um mecanismo de controle de concorrência com múltiplas versões (MVCC – Multi Version Concurrency Control). Nenhuma dessas consultas que estão lendo dados precisa adquirir qualquer bloqueio. Assim, o banco de dados não precisa mais se preocupar, então ele pode gastar mais tempo cuidando dos dados (que é o que queremos de fato que um banco de dados faça por nós). Mais importante, a query de atualização não precisa aguardar por nenhuma query de leitura, ela pode executar imediatamente, e as queries de leitura não precisam aguardar pelas queries de atualização. Em vez de termos várias queries aguardando pelo fim de outras queries, seu web site estaria sempre disponível para executar uma operação.
É claro que o MVCC não vem sem uma desvantagem. O “MV” no MVCC é para Multi-versão. Isso significa que múltiplas versões do mesmo dado serão mantidas sempre que os dados mudarem. O Oracle faz isso colocando dados antigos em um “undo log” (log de operações desfeitas). PostgreSQL não usa um undo log, em vez disso ele mantém múltiplas versões do dado em tabelas bases. Isso significa que existe muito menos sobrecarga ao fazer atualizações, e devemos ocasionalmente remover as versões antigas dos dados. Isso é uma das tarefas que o comando VACUUM realiza.
O PostgreSQL gerencia essas múltiplas versões armazenando algumas informações extras em todas as linhas. Esta informação é usada para determinar qual transação deve estar apta para ver a linha. Se a linha é uma versão antiga, existe uma informação que diz ao PostgreSQL como encontrar essa nova versão da linha. Esta informação é precisa e está disponível para bloquear linhas durante uma atualização.
Considere este cenário: uma linha é inserida em uma tabela que possui vários índices, e esta transação é comitada. Várias atualizações acontecem nesta linha. Cada atualização irá criar uma nova linha em todos os índices, mesmo se a chave do índice não mudou. E cada atualização irá também deixar uma versão antiga da linha na tabela base, uma que foi atualizada para apontar para a localização da nova versão da linha que a substituiu. Todos os dados antigos serão mantidos até que o vacuum seja executado na tabela. Em um sistema complexo, não leva muito tempo para todos os dados antigos serem traduzidos em muitos espaços desperdiçados. E é muito difícil recuperar este espaço se ele cresce até um nível inaceitável.
Isso significa que para aqueles que desejam manter seus bancos de dados no PostgreSQL com bom desempenho, esta operação de vacuum é crítica. Isso é especialmente verdade naquelas tabelas que possuem alta carga de atualização/inserção/exclusão. Estas tabelas geralmente devem receber operações de vacuum frequentemente se elas são pequenas. Para cargas mais moderadas, o autovacuum irá normalmente fazer um bom trabalho de manter espaços “mortos” em um nível mínimo. Você pode e deve ajustar o autovacuum ...
