Otimizando a performance do seu banco de dados no PostgreSQL

Quem trabalha com informática fatalmente vai ter que mexer com tudo. No início você trabalha somente com redes ou com desenvolvimento de software, mas atualmente as duas áreas estão cada vez mais integradas.

O desenvolvedor de software tem que conhecer cada vez mais de redes, segurança e protocolos de comunicação para desenvolver um software eficiente, que consuma poucos recursos, seja seguro e tenha qualidade. Já o administrador de redes, precisa conhecer o desenvolvimento de software para conseguir monitorar as aplicações que funcionam no seu servidor, verificar questões de segurança e em alguns momentos desenvolver scripts ou pequenos programas que auxiliem na administração da rede e dos servidores.

O novo desafio que peguei pela frente foi fazer a otimização de um servidor de banco de dados PostgreSQL.

A necessidade surgiu após fazer a análise do funcionamento do servidor durante um período de produção. A CPU do servidor estava trabalhando sempre acima de 75% e a memória sempre abaixo de 15%.

Algumas partes do sistema estavam extremamente lentas, principalmente a tela de consulta de log de transações realizadas e o detalhamento de cada transação.

Antes de iniciarmos a conversa sobre otimização, vamos a alguns números do Banco de Dados e do servidor em questão:

• Servidor: Dell PowerEdge Quad Xeon 2.33 Ghz, 4GB RAM, 1TB HD
• Quantidade de tabelas: 50
• Tamanho atual do banco: 5,3 GB

O banco de dados possui 4 principais tabelas que recebem inúmeros INSERTs por dia. A estimativa é que sejam inseridos no banco de dados todos os dias cerca de 1.000.000 de registros (Putz! Haja registro! Mas vamos em frente... vai dar certo! hehehe).

Lendo um pouco sobre o PostgreSQL e sobre Banco de Dados, cheguei à conclusão que para melhorar o desempenho era necessário:

1. Melhorar a estrutura de tabelas do banco de dados
2. Realizar algumas configurações no PostgreSQL
3. Programar manutenções diárias no banco de dados

Antes de executar as modificações, realizei o acesso à página de logs do sistema (nessa página a consulta é realizada por data e em um dia de consulta o retorno é de aproximadamente 110.000 registros) e ao detalhamento de um dos logs. Resultado:

• Tempo para carregar página de logs: 4,53 segundos

• Tempo para carregar detalhamento: 35,42 segundos

O primeiro passo é melhorar a estrutura do banco de dados através da criação de índices nos campos que são geralmente usados na clásula where do SQL.

Analisei as consultas e encontrei os principais campos em cada tabela. É importante observar que não adianta criar índices em todos os campos de todas as tabelas, se não a CPU do seu servidor vai para as alturas.

A análise da consulta pode ser feita usando o comando EXPLAIN ANALYZE. Basta executar EXPLAIN ANALYZE seguido do seu select que está lento e você vai ter um resumo do tempo consumido em cada parte da sua consulta e a partir daí você saberá quais os campos são necessários criar um índice.

Mas antes de melhorarmos os índices do nosso banco de dados, vamos fazer uma limpeza geral de registros que foram deletados mas que o banco de dados ainda não os removeu por completo, apenas marcou os registros como apagados. Fazemos isso através do comando VACUUM FULL.

Este comando não pode ser executado frequentemente, já que ele dá um lock nas tabelas na hora que está fazendo a operação. É importante que você faça a manutenção do seu Banco de Dados para evitar o uso deste comando. Falarei sobre estas operações no meu próximo post.

O comando VACUUM FULL demora um pouco para ser executado, dependendo do tamanho do seu banco de dados. No meu caso, demorou 103 segundos, ou seja, 1 min e 43 segundos.

Agora vamos criar os índices das tabelas. A criação dos índices é feita usando o comando CREATE INDEX seguido do comando ANALYZE tabela, exemplo:

• CREATE INDEX log_chamada_id_idx1 on log_operacao_cti(log_chamada_id)

Onde log_chamada_id_idx1 é o nome do índice, log_operacao_cti é o nome da tabela e log_chamada_id é o campo da tabela que eu desejo criar um índice.

• ANALYZE log_operacao_cti

Resultado da criação dos índices no meu banco de dados:

Agora que temos índices criados nas nossas tabelas, vamos realizar um novo teste no sistema para ver como está o tempo de carregamento.

Resultado após as alterações:

• Tempo para carregar a página de logs: 1,46 segundos

• Tempo para carregar o detalhamento: 492 ms

Percebeu a diferença? :)

O próximo passo é melhorar a configuração do servidor PostgreSQL para que ele consuma os recursos disponíveis no hardware de forma mais eficiente e programar manutenções no banco de dados para mantermos o banco sempre limpo e funcionando perfeitamente, mas isto é assunto para um próximo post.

Java Heap Dump: Analisando a utilização de memória da sua aplicação Java

No post passado eu falei um pouco sobre o VisualVM mas não falei sobre como fazer o dump do heap do Java em uma máquina monitorada remotamente.

Não dá para fazer isso usando o VisualVM. Você precisa acessar a máquina remota e executar o seguinte comando:

jmap -F -dump:live,format=b,file=dump-heap-java.hprof PID

PID é o número do processo do Java. Para saber o número do processo do Java no Linux, execute o seguinte comando:

ps ax|grep java

Este comando demora um pouco para ser executado, tenha paciência, pois vai depender do seu Heap Size atual. Ele vai gerar um arquivo do tamanho do heap atual. No meu caso, gerou um arquivo de 778 Mb.

Agora que já temos o arquivo de dump, é necessário copiá-lo para a máquina aonde tenha o VisualVM rodando.

Carregue o arquivo no VisualVM usando o Menu File > Load...

Procure o arquivo de dump do heap e abra ele.

Agora que estamos com o dump carregado no VisualVM, vamos tentar encontrar o que está gerando problema de uso de memória na aplicação Java.

Clique na aba Classes e será exibida uma lista com as classes, o percentual de instâncias, a quantidade de instâncias e, o que nos interessa, o tamanho alocado por essa classe.

Vamos ordenar a lista por tamanho, clicando na coluna tamanho.

Veja que o primeiro da lista é o byte[]. Clicando duas vezes nele, será apresentada a aba Instances contendo todas as instâncias que estão usando o byte[] na memória.

Perceba que temos várias instâncias com o mesmo tamanho (16681), o que significa que é a mesma classe que está usando o byte[] e não está desalocando o recurso.

Clique em uma das instâncias e vai abrir na lateral direita a área References na parte inferior. Clique com o botão direito na classe que vai aparecer e em seguida escolha a opção Show Instance.

O resultado irá mostrar o que está ocupando a memória:

No meu caso era um comando SQL de Insert a um banco de dados de teste MySQL. Perceba na imagem que temos o comando SQL completo, separado em vários chars.

Associado a este dump, utilizei o dump dos threads onde vi qual a classe que estava gerando os Threads Blocked. Neste meu caso, o problema estava no servidor de Banco de Dados que estava mau configurado e não estava tendo recursos suficientes para atender a todos os requests.

Utilizando dump de Heap e dump de threads como mostrei no meu post anterior, você conseguirá detectar todos os problemas de gerência de memória da sua aplicação.

Uma outra funcionalidade legal que você pode utilizar no VisualVM é utilizar o JConsole somado ao plugin JTop para monitorar o consumo de CPU por threads para detectar uso excessivo de CPU gerado por algum bug no software, mas isto é assunto para um outro post.

Links de referência:

• Heap Dump Analysis
• jmap - Memory Map

VisualVM: Analisando o funcionamento de sua aplicação Java

O desenvolvimento de aplicações em Java e a otimização é algo que requer uma atenção muito especial. Muitos por aí falam que odeiam o Java falando que é muito difícil de desenvolver, que tem problema de uso de memória e etc.

Os programadores Java que me desculpem, mas estou começando a achar que o problema não está no Java e sim na forma como se desenvolve e como se implanta o sistema. Acrescento ainda, o problema vem desde a Faculdade, onde não se fala absolutamente nada sobre otimização e análise de desempenho de aplicações.

Antes de liberar o sistema para a produção é extremamente importante fazer uma análise geral do funcionamento da aplicação, do uso de CPU, de memória e o número de threads geradas, para saber se a aplicação está alocando e desalocando os recursos corretamente, abrindo e fechando as threads, etc.

Bom, mas vamos ao que interessa. Como fazer para monitorar a sua JVM?

Para iniciar é necessário prepararmos a JVM para ser monitorada. Neste exemplo vou utilizar o servidor de aplicação Jetty rodando em um servidor Linux na pasta /opt/jetty.

Abra o arquivo jetty.sh que está dentro da pasta /opt/jetty/bin/ e adicione a seguinte variável no começo do arquivo (após os comentários):

JAVA_OPTIONS="-Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=8086 -Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false"

Explicando cada parâmetro:

-Dcom.sun.management.jmxremote: Habilita o gerenciamento remoto

-Dcom.sun.management.jmxremote.port=8086: Porta que será usada para conexão

-Dcom.sun.management.jmxremote.ssl=false: Habilitar ou não o SSL

-Dcom.sun.management.jmxremote.authenticate=false: Se haverá necessidade de autenticação para se conectar na JVM

Feito isto, basta iniciar o seu Jetty usando o script jetty.sh:

/opt/jetty/bin/jetty.sh start

O seu Jetty vai iniciar e permitirá que você se conecte remotamente na JVM para fazer o monitoramento.

Outra opção para iniciar o jetty é executar diretamente na linha de comando:

java -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=8086 -Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false -jar /opt/jetty/start.jar

Para o monitoramento utilizaremos a excelente ferramenta da Sun chamada VisualVM. Não sei se é a melhor, mas atendeu às minhas necessidades e resolveu os meus problemas. :)

Execute o comando jvisualvm em qualquer máquina que tenha uma JVM instalada. No meu caso, uso Mac OS X Snow Leopard para o monitoramento.

Tela inicial do VisualVM

Na coluna na lateral esquerda execute os seguintes passos para adicionar o seu servidor remoto:

1. Clica com o botão direito em Remote
2. Clica em Add Remote Host...
3. Coloca o IP ou nome da máquina a ser monitorada no campo e clica em OK
4. Clica com o botão direito no Host adicionado
5. Clica em Add JMX Connection...
6. Preencha o campo Connection com IP:PORTA, onde a porta é a que foi adicionada no JAVA_OPTIONS do Jetty, no meu caso 8086, e depois clica em OK

Pronto, a sua JVM já vai ser monitorada. Você poderá acompanhar na aba Monitor em tempo real a utilização de CPU, Heap Size, número de classes e número de threads.

Na aba Threads, é possível monitorar os threads em tempo real.

Um recurso muito útil para descobrir problemas de má utilização de memória é fazer um dump das threads e analisar qual a thread que está ficando presa e qual classe que foi executada para causar o travamento da thread.

Para fazer um thread dump, basta clicar com o botão direito no IP do servidor monitorado e clicar em Thread Dump.

Ele vai gerar um dump de todas as threads rodando na sua JVM. Este comando pode demorar dependendo da quantidade de threads que você tem atualmente sendo executadas.

Ao clicar duas vezes no Dump, abrirá uma nova aba com todas as informações das suas threads.

Agora é só procurar por BLOCKED e aí você vai encontrar as threads que estão travadas.

Importante: nem sempre uma thread blocked significa que ela ficará travada para sempre. Pode acontecer de uma thread que esteja fazendo alguma operação com um banco de dados esteja esperando recurso disponível para executar o processo e aí ela fica blocked até o recurso ficar disponível. Neste caso, o problema pode estar no seu banco de dados que não está suportando a quantidade de transações, no seu pool de conexões ou no seu SQL que está mau feito.

Ah... lembrei de uma coisa legal. Se você está rodando o servidor de aplicação no mesmo host do VisualVM, você pode fazer dump do Heap do Java e fazer a análise no VisualVM. Se a aplicação está rodando remotamente, então para fazer o dump do heap e analisá-lo, é necessário fazer alguns passos extras. Mas não se preoculpe, no meu próximo post eu falarei sobre isso... :)

Agora é só você brincar com a ferramenta e descobrir o que é possível fazer com ela. :)

Links de relacionados:

• VisualVM: Free and Open Source Java Troubleshooter
• Monitoring and Managing Java SE 6 Platform Applications
• VisualVM
• Java VisualVM