IBAnalyst: Entendendo Seu Banco de Dados

Dmitri Kuzmenko, [email protected], última atualização 31 de março de 2014

Trabalho com InterBase desde 1994. Naquela época, a maioria dos bancos de dados era pequena e não exigia nenhum ajuste. Claro, havia ocasiões em que eu precisava alterar o ibconfig em um servidor e reconfigurar o hardware ou o sistema operacional, mas isso era quase tudo que eu podia fazer para ajustar o desempenho.

Há quatro anos, nossa empresa começou a fornecer suporte técnico e treinamento para usuários de InterBase. Trabalhar com muitos bancos de dados de produção também me ensinou muitas coisas diferentes. No entanto, a maior parte do que aprendi dizia respeito a aplicações - uso de parâmetros de transação, otimização de consultas e conjuntos de resultados.

Claro, eu já sabia há bastante tempo sobre o gstat - a ferramenta que fornece informações de estatísticas do banco de dados. Se você já olhou a saída do gstat ou leu o opguide.pdf sobre ele, saberia que a saída estatística parece apenas um monte de números e nada mais. Ok, você pode descobrir informações de fragmentação para uma tabela ou índice específico, mas que outras informações úteis podem ser obtidas?

Felizmente, antes de trabalhar com InterBase, eu me interessava por diferentes estruturas de dados, como elas são armazenadas e quais algoritmos usam. Isso me ajudou a interpretar a saída do gstat. Naquela época, decidi escrever uma ferramenta que pudesse analisar a saída do gstat para ajudar no ajuste do banco de dados ou, pelo menos, identificar a causa de problemas de desempenho.

História longa, mas o resultado foi que o IBAnalyst foi criado. Apesar da minha experiência, ele ainda me permite encontrar coisas muito interessantes ou problemas de desempenho em diferentes bancos de dados.

Sistemas reais têm desempenho em tempo de execução que flutua como uma onda. A amplitude dessas ‘ondas’ pode ser baixa ou alta, então você pode ver como o desempenho difere de dia para dia (ou de hora em hora). O desempenho real depende de muitos fatores, incluindo o design da aplicação, a configuração do servidor, a concorrência de transações, o lixo de versões no banco de dados e assim por diante. Para descobrir o que está acontecendo em um banco de dados (tanto aspectos positivos quanto negativos do desempenho), você deve, no mínimo, dar uma olhada nas estatísticas do banco de dados de tempos em tempos.

Vamos dar uma olhada nos recursos do IBAnalyst. O IBAnalyst pode coletar estatísticas do gstat ou da Services API e compilá-las em um relatório que fornece informações completas sobre o banco de dados, suas tabelas e índices. Ele tem avisos no local que estão disponíveis durante a navegação das estatísticas; também inclui comentários de dicas e relatórios de recomendações.

Informações do Banco de Dados

Figura 1 Resumo das estatísticas do banco de dados

O resumo mostrado na Figura 1 fornece informações gerais sobre o seu banco de dados. Os avisos ou comentários mostrados são baseados em conhecimento cuidadosamente reunido a partir de um grande número de bancos de dados de produção do mundo real.

Nota: Todas as figuras neste artigo contêm estatísticas do gstat que foram obtidas de um banco de dados de produção do mundo real (com a permissão de seus proprietários).

Como eu disse antes, estatísticas brutas do banco de dados parecem enigmáticas e são difíceis de interpretar. O IBAnalyst destaca quaisquer problemas potenciais claramente em amarelo ou vermelho e o detalhe do problema pode ser lido simplesmente colocando o cursor sobre a entrada relevante e lendo a dica exibida.

Em seguida, podemos ver que o parâmetro Forced Write está definido como OFF e marcado em vermelho. InterBase 4.x e 5.x por padrão tinham esse parâmetro como ON. Forced Writes em si é um método de cache de escrita: quando ON, ele grava dados alterados imediatamente no disco, mas OFF significa que as gravações serão armazenadas por tempo indeterminado pelo sistema operacional em seu cache de arquivos. O InterBase 6 cria bancos de dados com Forced Writes OFF.

Por que isso está marcado em vermelho no relatório do IBAnalyst? A resposta é simples - usar gravações assíncronas pode causar corrupção do banco de dados em casos de falha de energia, do sistema operacional ou do servidor.

Dica: É interessante que interfaces modernas de HDD (ATA, SATA, SCSI) não mostram nenhuma diferença significativa de desempenho com Forced Write definido como On ou Off(1).

Em seguida no relatório está o misterioso “intervalo de sweep”. Se positivo, ele define o tamanho da lacuna entre a transação mais antiga (2) e a transação snapshot mais antiga, na qual o mecanismo é alertado sobre a necessidade de iniciar uma coleta de lixo automática. Em alguns sistemas, atingir esse limite causará um efeito de “perda súbita de desempenho” e, como resultado, às vezes é recomendado que o intervalo de sweep seja definido como 0 (desabilitando o sweep automático completamente). Aqui, o intervalo de sweep está marcado em amarelo, porque o valor da lacuna de sweep é negativo, o que pode ocorrer em estatísticas do InterBase 6.0, Firebird e Yaffil, mas não no InterBase 7.x. Quando o valor da lacuna de sweep é maior que o intervalo de sweep (se o intervalo de sweep não for 0), a entrada do relatório para o intervalo de sweep será marcada em vermelho com uma dica apropriada.

Vamos examinar as próximas 8 linhas como um grupo, pois todas exibem aspectos do estado de transação do banco de dados:

A transação mais antiga é a transação não confirmada mais antiga. Quaisquer números de transação menores são para transações confirmadas, e nenhuma versão de registro está disponível para tais transações. Números de transação maiores que a transação mais antiga são para transações que podem estar em qualquer estado. Isso também é chamado de “transação interessante mais antiga”, porque ela congela quando uma transação é encerrada com rollback, e o servidor não pode desfazer suas alterações naquele momento.
O snapshot mais antigo - a transação ativa (ou seja, ainda não confirmada) mais antiga que existia no início da transação que é atualmente a transação “interessante” mais antiga. Indica o número de transação de snapshot mais baixo que está interessado em versões de registro.
A ativa mais antiga - a transação ativa mais antiga atualmente (3).
A próxima transação - o número de transação que será atribuído a uma nova transação.
Transações ativas - o IBAnalyst dará um aviso se o número da transação ativa mais antiga for 30% menor que a contagem diária de transações. As estatísticas não informam se há outras transações ativas entre a ativa mais antiga e a próxima transação, mas pode haver tais transações. Normalmente, se a ativa mais antiga ficar presa, há duas causas possíveis: a) alguma transação está ativa por um longo tempo ou b) o design da aplicação permite que transações sejam executadas por um longo tempo. Ambas as causas impedem a coleta de lixo e consomem recursos do servidor.
Transações por dia - isso é calculado a partir da próxima transação, dividido pelo número de dias decorridos desde a criação do banco de dados até o ponto em que as estatísticas são recuperadas. Isso pode estar correto apenas para bancos de dados de produção, ou para bancos de dados que são periodicamente restaurados a partir de backup, fazendo com que a numeração de transações seja reiniciada.

Como você já aprendeu, se houver quaisquer avisos, eles são mostrados como linhas coloridas, com dicas claras e descritivas sobre como corrigir ou prevenir o problema.

Deve-se notar que as estatísticas do banco de dados nem sempre são úteis. Estatísticas coletadas durante operações de trabalho e manutenção podem ser sem sentido.

Não colete estatísticas se você:

Acabou de restaurar seu banco de dados
Executou um backup (gbak -b db.gdb) sem a opção -g
Recentemente executou um sweep manual (gfix -sweep)

Estatísticas obtidas em tais ocasiões serão praticamente inúteis. Também é correto que, durante o trabalho normal, pode haver momentos em que o banco de dados está em perfeito estado, por exemplo, quando as aplicações fazem menos carga no banco de dados do que o usual (os usuários estão no almoço ou é um momento tranquilo no dia de negócios).

Como você pode saber quando há algo errado com o banco de dados?

Suas aplicações podem ser tão bem projetadas que sempre trabalharão com transações e dados corretamente, não criando lacunas de sweep, não acumulando muitas transações ativas, não mantendo snapshots de longa duração e assim por diante. Normalmente isso não acontece (desculpe, colegas).

O motivo mais comum é que os desenvolvedores testam suas aplicações executando apenas dois ou três usuários simultâneos. Quando a aplicação é então usada em um ambiente de produção com quinze ou mais usuários simultâneos, o banco de dados pode se comportar de maneira imprevisível. Claro, o modo multiusuário pode funcionar bem porque a maioria dos conflitos multiusuário pode ser testada com duas ou três aplicações executando simultaneamente. No entanto, com números maiores de usuários, problemas de coleta de lixo podem surgir. Tais problemas potenciais podem ser detectados se você coletar estatísticas do banco de dados nos momentos corretos.

Informações da Tabela

Vamos dar uma olhada em outra saída de exemplo do IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Figura 2 Estatísticas da tabela

A visão de estatísticas de Tabela do IBAnalyst também é muito útil. Ela pode mostrar quais tabelas têm muitas versões de registro, onde um grande número de atualizações/exclusões foi feito, tabelas fragmentadas, com fragmentação causada por atualização/exclusão ou por blobs, e assim por diante. Você pode ver quais tabelas estão sendo atualizadas com frequência e qual é o tamanho da tabela em megabytes. A maioria desses avisos é personalizável.

Neste exemplo de banco de dados, há vários problemas. Primeiro, a cor amarela na coluna VerLen avisa que o espaço ocupado pelas versões de registro é maior que o ocupado pelos próprios registros. Isso pode resultar da atualização de muitos campos em um registro ou de exclusões em massa. Veja as linhas em que a coluna MaxVers está marcada em azul. Isso mostra que apenas uma versão por registro é armazenada e, consequentemente, que o problema se deve a exclusões em massa. O valor na coluna Versions mostra quantos registros foram excluídos.

Transações ativas de longa duração que impedem a coleta de lixo são a principal razão para a degradação do desempenho. Para algumas tabelas, pode haver muitas versões que ainda estão “em uso”. O servidor não pode decidir se elas realmente estão em uso, porque transações ativas potencialmente precisam de qualquer uma ou todas essas versões. Consequentemente, o servidor não considera essas versões como lixo, e leva cada vez mais tempo para construir um registro correto a partir de muitas versões sempre que uma transação o lê. Na Figura 2, você pode ver duas tabelas que têm a contagem de versões três vezes maior que a contagem de registros. Usando essas informações, você também pode verificar se o fato de suas aplicações estarem atualizando essas tabelas com tanta frequência é por design ou devido a um erro.

A visão de Índice

Os índices são usados pelo mecanismo do banco de dados para impor restrições de chave primária, chave estrangeira e unicidade. Eles também aceleram a recuperação de dados. Índices únicos são os melhores para recuperar dados, mas o nível de benefício de índices não únicos depende da diversidade dos dados indexados.

Por exemplo, observe o ADDR_ADDRESS_IDX6. Primeiro, o próprio nome do índice sugere que ele foi criado manualmente. Se as estatísticas foram coletadas pela Services API com informações de metadados, você pode ver quais colunas são indexadas (no IBAnalyst 1.83 e superior). Para o índice em exame, você pode ver que ele tem 34999 chaves, TotalDup é 34995 e MaxDup é 25056. Ambas as colunas de duplicatas estão marcadas em vermelho. Isso ocorre porque há apenas 4 valores de chave únicos entre todas as chaves neste índice, como pode ser visto na coluna Uniques. Além disso, a maior cadeia de duplicatas (chave apontando para registros com o mesmo valor de coluna) é 25056 - ou seja, quase todas as chaves armazenam um dos quatro valores únicos. Como resultado, este índice poderia:

Reduza a velocidade do processo de restauração. Ok, trinta e cinco mil chaves não é um grande problema para bancos de dados e hardware modernos, mas o impacto deve ser observado mesmo assim.
Desacelere a coleta de lixo. Índices com um baixo número de valores únicos podem impedir a coleta de lixo em até dez vezes em comparação com um índice completamente único. Esse problema foi resolvido no InterBase 7.1/7.5 e no Firebird 2.0.
Produza leituras de página desnecessárias quando o otimizador lê o índice. Isso depende do valor pesquisado em uma consulta específica - pesquisar por um índice que tenha um valor maior para MaxDup será mais lento. Pesquisar por valor em uma coluna que tenha menos valores duplicados será mais rápido, mas somente você sabe que a coluna é indexada.

É por isso que o IBAnalyst chama sua atenção para esses índices, marcando-os em vermelho e amarelo e incluindo-os no relatório de Recomendações. Infelizmente, a maioria dos índices “ruins” é criada automaticamente para impor restrições de chave estrangeira. Em alguns casos, esse problema pode ser resolvido impedindo, por meio de gatilhos, exclusões ou atualizações da chave primária em tabelas de referência. Mas se não for possível implementar tais alterações, o IBAnalyst mostrará os índices “ruins” em Chaves Estrangeiras toda vez que você visualizar as estatísticas.

Relatórios

Não há necessidade de examinar o relatório inteiro toda vez, identificando a cor das células e lendo dicas para novos avisos. Informações mais diretas e detalhadas podem ser obtidas usando o recurso de Recomendações do IBAnalyst. Basta carregar as estatísticas e ir ao menu Relatórios/Ver Recomendações. Este relatório fornece uma análise passo a passo, incluindo avisos descritivos mais detalhados sobre gravações forçadas, intervalo de varredura, atividade do banco de dados, estado das transações, tamanho da página do banco de dados, varredura, páginas de inventário de transações, tabelas fragmentadas, tabelas com muitas versões de registros, exclusões/atualizações em massa, índices profundos, índices não amigáveis ao otimizador, índices inúteis e até tabelas vazias. Todas essas informações e as sugestões acompanhantes são criadas dinamicamente com base nas estatísticas carregadas.

Como exemplo da saída do relatório, vamos dar uma olhada em um relatório gerado para as estatísticas do banco de dados que você viu anteriormente neste artigo:

“O tamanho geral das páginas de inventário de transações (TIP) é grande - 94 kilobytes ou 23 páginas. A transação Read_committed usa o TIP global, mas transações de snapshot fazem cópias próprias do TIP na memória. Um TIP grande pode diminuir o desempenho. Tente executar a varredura manualmente (gfix -sweep) para reduzir o tamanho do TIP.”

Aqui está outra citação da parte de tabelas/índices do relatório:

“Contagem de tabelas com versões: 8. Uma grande quantidade de versões de registros geralmente diminui o desempenho. Se houver muitas versões de registros na tabela, a coleta de lixo não funciona ou os registros não estão sendo lidos por nenhuma instrução select. Você pode tentar executar select count(*) nessas tabelas para forçar a coleta de lixo, mas isso pode levar muito tempo (se houver muitas versões e índices não únicos) e pode não ter sucesso se houver pelo menos uma transação interessada nessas versões.

Aqui está a lista de tabelas com uma proporção versão/registro maior que 3:


Tabela	Registros	Versões	Tamanho Reg/Vers
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Resumo

O IBAnalyst é uma ferramenta inestimável que ajuda o usuário a realizar uma análise detalhada das estatísticas do banco de dados Firebird ou InterBase e a identificar possíveis problemas com o banco de dados em termos de desempenho, manutenção e como um aplicativo interage com o banco de dados. Ele pega estatísticas enigmáticas do banco de dados e as exibe de maneira gráfica e fácil de entender, além de fazer sugestões sensatas automaticamente sobre como melhorar o desempenho do banco de dados e facilitar a manutenção do banco de dados.

1 O InterBase 7.5 e o Firebird 1.5 têm recursos especiais que podem liberar periodicamente páginas não salvas se as Gravações Forçadas estiverem Desativadas.

2 A transação mais antiga é a mesma transação interessante mais antiga, mencionada em todos os lugares. A saída do gstat não mostra essa transação como “interessante”.

3 Ann Harrison diz que a transação ativa mais antiga é a transação mais antiga que estava ativa quando a transação ativa mais antiga atual começou. Para aplicativos, isso não faz uma grande diferença aqui.