Dicas de Performance em aplicações com PostgreSQL

Tradução do texto original de Josh Berkus publicado em 28/11/2006 e 29/11/2006.

O que se segue é a versão editada de um conjunto de conselhos que eu tenho dado ao time da Sun no redesenho de uma aplicação em C++ que foi construída para MySQL, portado para o PostgreSQL, e nunca otimizado para performance. Ocorreu que estes conselhos podem ser geralmente úteis para a comunidade, então aí vão eles.

Projeto de aplicações para performance no PostgreSQL

Escrevendo regras de consultas

Para todos os sistemas gerenciadores de bancos de dados (SGDBs), o tempo por rodada significativo. Este é o tempo que leva para uma consulta passar pelo analisador de sintaxe da linguagem, o driver, a interface da rede, o analisador de sintaxe do banco de dados, o planejador, o executor, o analisador de sintaxe novamente, voltar pela interface de rede, passar pelo manipulador de dados do driver e para o cliente da aplicação. SGDBs variam na quantidade de tempo e CPU que elas levam para processar este ciclo, e por uma variedade de razões, o PostgreSQL possui um alto consumo de tempo e recursos do sistema por rodada.

Contudo, o PostgreSQL tem um overhead significativo por transação, incluindo o log de saída e as regras de acesso que precisam ser ajustadas em cada transação. Enquanto você pode pensar que não está utilizando transações para um simples comando de leitura SELECT, de fato, cada simples comando no PostgreSQL é uma transação. Na ausência de uma transação explícita, o comando é por si mesmo implicitamente uma transação.

Passando por isto, o PostgreSQL é claramente o segundo depois do Oracle em processamento de consultas complexas e longas com transações com vários comandos com fácil resolução de conflitos de concorrência. Ele também suporta cursores, tanto rolável quanto não rolável.

Dica 1: Nunca use várias consultas pequenas quando uma grande consulta pode fazer o trabalho.

É comum em aplicações MySQL lidar com joins no código da aplicação, ou seja, consultando o ID de um registro relacionado e então iterando através dos registros filhos com aquele ID manualmente. Isto pode resultar em rodar centenas de consultas por tela de interface com o usuário. Cada uma destas consultas levam 2 a 6 milissegundos por rodada, o que significa que se você executar cerca de 1000 consultas, neste ponto você estárá perdendo de 3 a 5 segundos. Comparativamente, solicitando estes registros numa única consulta levará apenas algumas centenas de milissegundos, economizando cerca de 80% do tempo.

Dica 2: Agrupe vários pequenos UPDATEs, INSERTs ou DELETEs em um único comando ou, se não for possível, em uma longa transação.

Antes, a falta de subselects nas versões anteriores do MySQL fizeram com que os desenvolvedores de aplicação projetassem seus comandos de modificação de dados (DML) da mesma forma que as junções em middleware. Esta é uma má idéia para o PostgreSQL. Ao invés, você irá tirar vantagem de subselects e joins no seu comando UPDATE, INSERT, e DELETE para tentar realizar modificações em lote com um único comando. Isto reduz o tempo da rodada e o overhead da transação.

Em alguns casos, contudo, não há uma única consulta que consiga alterar todas as linhas que você deseja e você irá usar um grupo de comandos em série. Neste caso, você irá querer se assegurar de envolver a sua séria de comandos DML em uma transação explícita (ex. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). Isto reduz o overhead de transação e corta o tempo de execução em até 50%.

Dica 3: Considere realizar cargas em lotes ao invés de INSERTs seriais.

O PostgreSQL prove um mecanismo de carga em lote chamado COPY, que pode pegar uma entrada de um arquivo ou pipe delimitado por tabulações ou CSV. Quando o COPY pode ser usado no lugar de centenas de INSERTs, ele pode cortar o tempo de execução em até 75%.

Dica 4: O DELETE é caro

É comum para um desenvolvedor de aplicação pensar que o comando DELETE é praticamente não tem custo. Você está apenas desligando alguns nós, correto? Errado. SGDBs não são sistemas de arquivo; quando você apaga uma linha, índices precisam ser atualizados, o espaço liberado precisa ser limpo, fazendo a exclusão de fato mais cara que a inserção. Assim, aplicações que habitualmente apagam todas as linhas de detalhe e repõe elas com novas toda vez que é realizada qualquer alteração estão economizando esforço no lado da aplicação e empurrando este dentro do banco de dados. Quando possível, isto deve ser substituído pela substituição mais discriminada das linhas, como atualizar apenas as linhas modificadas.

Além disso, quando for limpar toda uma tabela, sempre use o comando TRUNCATE TABLE ao invés de DELETE FROM TABLE. A primeira forma é até 100 vezes mais rápida que a posterior devido ao processamento da tabela como um todo ao invés de uma linha por vez.

Dica 5: Utilize o PREPARE/EXECUTE para iterações em consultas

Algumas vezes, mesmo tentando consolidar iterações de consultas semelhantes em um comando mais longo, nem sempre isto é possível de estruturar na sua aplicação. É para isto que o PREPARE ... EXECUTE serve; ele permite que o motor do banco de dados pule o analizador de sintaxe e o planejador para cada iteração da consulta. Por exemplo:

Preparar:

query_handle = query('SELECT * FROM TABLE WHERE id = ?')(parameter_type = INTEGER)

Então inicie as suas iterações:

for 1..100
query_handle.execute(i);
end

Classes para a preparação de comandos no C++ são explicadas na documentação do libpqxx.

Isto irá reduzir o tempo de execução na direta proporção do tamanho do número de iterações.

Dica 6: Use pool de conexões efetivamente

Para uma aplicação web, você irá perceber que até 50% do seu potencial de performance pode ser controlado através do uso, e configuração apropriada de um pool de conexões. Isto é porque criar e destruir conexões no banco de dados leva um tempo significativo no sistema, e um excesso de conexões inativas continuarão a requerer RAM e recursos do sistema.

Há um número de ferramentas que você pode utilizar para fazer um pool de conexões no PostgreSQL. Uma ferramenta de terceiros de código aberto é o pgPool. Contudo, para uma aplicação em C++ com requisitos de alta disponibilidade, é provavelmente melhor utilizar a técnica de pseudo-pooling nativa do libpqxx chamada de "conexões preguiçosas". Eu sugiro contatar a lista de de e-mail para mais informações sobre como utilizar isto.

Com o PostgreSQL, você irá querer ter quantas conexões persistentes (ou objetos de conexão) forem definidas no seu pico normal de uso de conexões concorrentes. Então, se o uso máximo normal (no início da manhã, digamos) é de 200 conexões concorrentes de agentes, usuários e componentes, então você irá querer que ter esta quantidade definida para que sua aplicação não tenha que esperar por novas conexões durante o pico onde será lenta na criação.

Notas de Copyright

Texto originalmente traduzido por Fábio Telles Rodriguez <fabio.telles@gmail.com> em http://savepoint.blog.br/dicas-de-performance-em-aplicacoes-com-postgresql/

Tradução livre do texto: "PostgreSQL Application Performance Tips, Part 1" publicado por Josh Berkus em 28/11/2006 em http://blogs.ittoolbox.com/database/soup/archives/postgresql-application-performance-tips-part-1-13172 e "PostgreSQL Application Performance Tips, Part 2" publicado por Josh Berkus em 29/11/2006 em http://blogs.ittoolbox.com/database/soup/archives/postgresql-application-performance-tips-part-2-13194