IBAnalyst: Dicas e Truques

Este texto foi escrito originalmente em 2012, é válido para a versão 1.0 - 2.5, nas versões 3.0-5.0 houve muitas mudanças, que não puderam ser refletidas. Por favor, leia a documentação ou entre em contato conosco para suporte: [email protected].

Algumas perguntas que não foram respondidas nas Recomendações do IBAnalyst e/ou na Ajuda:

1. Como reconstruir índices em restrições PRIMARY, FOREIGN ou UNIQUE?

R: Para versões do Firebird 1.0-2.5. Sim, você não pode usar ALTER INDEX xxx INACTIVE/ACTIVE em índices de restrição. Se você vir um índice profundo ou fragmentado nesta restrição, você pode usar um truque especial (usado pelo gbak na restauração):

RDB$INDICES tem o sinalizador RDB$INDEX_INACTIVE que é nulo ou 0 se o índice estiver ativo (após CREATE INDEX ou ALTER INDEX ACTIVE). 1 significa que o índice está inativo (após ALTER INDEX INACTIVE). Mas também há o valor 3 que é usado para indicar índices inativos em restrições. Então, você pode definir RDB$INDEX_INACTIVE=3 para esse índice, fazer COMMIT, e depois retornar o valor para 0 e commitar novamente - o índice será reconstruído.

Para Firebird 3.0-5.0 - simplesmente faça ALTER INDEX nome_do_indice ACTIVE

2. Usei todas as recomendações do IBAnalyst, mas isso não ajudou a acelerar as consultas.

R: Este é um problema separado, onde o IBAnalyst não pode ajudar. Aqui podem haver 2 causas para o problema:

1. Os índices têm estatísticas desatualizadas. Você pode atualizar as estatísticas do índice com o comando SET STATISTICS INDEX xxx (veja mais detalhes http://www.ibase.ru/proc_selectivity/).

2. Simplesmente não há um índice adequado para alguma condição usada na consulta

3. As consultas são muito complexas, ou o otimizador não consegue otimizar a consulta, então é necessário refatorar a consulta.

4. Em alguns casos, você verá “tabelas fragmentadas” logo após a restauração.

Normalmente, o Firebird e o InterBase (sem o parâmetro -use_all_space) reservam cerca de 25% de espaço nas páginas de dados para futuras inserções, atualizações ou exclusões (para colocar versões de registro). Mas, com qualquer tamanho de página de banco de dados (1, 2, 4 ou 8 k), você verá ~50% de fragmentação para tabelas que têm tamanho de registro pequeno (cerca de ~12-20 bytes, por exemplo, uma tabela com 2 campos inteiros tem tamanho médio de registro = 12 bytes).

Isso é normal, considere isso como um número mágico do servidor (ou comportamento).

Então, se você tem tabelas com registros tão pequenos, você pode:

a) ignorar o aviso de “fragmentada” para essas tabelas

b) diminuir a “% fragmentada” para 45%, por exemplo, no diálogo de Opções do IBAnalyst.

4. Versões de registro para tabela que não deve ser atualizada

Se você vir versões de registro em uma tabela que não deve ser atualizada (por exemplo, uma tabela com algum log de eventos) - não se preocupe, essas versões são geradas por exclusão.

Assim, você saberá quantos registros atuais existem na tabela e quantos registros foram excluídos.

Isso só é verdade se MaxVer = 1. Se for > 1, então esta tabela está sendo atualizada por algum aplicativo. Se você tem certeza de que esta tabela nunca deve ser atualizada, é melhor definir um trigger “before update” com exceção para descobrir qual aplicativo faz as atualizações.

5. Blobs podem causar fragmentação de tabela.

O mecanismo armazena blobs de 3 maneiras diferentes:

1. Se o conteúdo do blob couber na página de dados (espaço livre suficiente), ele será armazenado nessa página de dados próximo ao seu registro (ou versão).

2. Se o conteúdo do blob não couber na página de dados, ele será armazenado em uma página separada

3. Se no caso 2 o blob não couber em uma página de dados, uma página de ponteiro é criada para apontar para as páginas de blob apropriadas.

O caso 1 acontece dependendo do tamanho do blob armazenado e do tamanho da página do banco de dados. Por exemplo, se você tivesse tamanho de página 4K e blobs com tamanho médio ~5K, eles não são armazenados nas páginas de dados, mas em páginas de blob adicionais.

Mas se você fizer backup do seu banco de dados e restaurá-lo com tamanho de página 8K, os blobs caberão na página de dados e serão armazenados com os registros, causando alta fragmentação de registro.

O IBAnalyst marca essas tabelas como Pale (coluna Registros) e a dica mostra os registros estimados para essa tabela (com base na contagem de páginas de dados) e o valor real de preenchimento médio (%).

Se sua consulta lê qualquer campo, exceto blobs, dessa tabela, a varredura natural, junção ou agregação será muito lenta.

A única solução para evitar isso: criar uma tabela adicional (vinculada 1-1 à tabela original) e mover todas as colunas blob que têm tamanho médio menor que o tamanho da página para ela.

Nesse caso, não tente fazer backup/restauração com tamanho de página maior! Isso fará com que os blobs que não cabiam nas páginas de dados com o tamanho de página atual sejam colocados nas páginas de dados durante a restauração com tamanho de página maior. Então, suas tabelas com blobs ficarão mais fragmentadas do que antes.

Também não é recomendado restaurar com tamanho de página menor, pois isso pode diminuir o desempenho dos índices e das tabelas sem blob.

Você também não deve tentar mudar campos blob para campos varchar - campos varchar são sempre armazenados como parte de um registro, então o registro pode ter 2 ou mais fragmentos (ser colocado em 2 ou mais páginas de dados) se não couber na página de dados.

p.s. O IBAnalyst pode relatar essas tabelas “por engano”, por exemplo, a tabela tinha campos blob com dados, mas eles foram removidos da estrutura da tabela. Infelizmente não há opção configurável para esse aviso, porque calculamos exatamente a partir dos dados relatados pelo servidor (estatísticas).

6. Relação VerLen e RecLength

a) VerLen >= 90% do RecLength: as versões que você vê na coluna Versão são principalmente exclusões de registros. Quanto mais registros forem excluídos, menor será o RecLength (até 0 bytes). Além disso, VerLen pode ser maior que RecLen se você atualizar sua tabela com dados de string maiores do que os armazenados nos registros originais.

b) VerLen <= 80% do RecLength: as versões são principalmente atualizações de registros.

Não podemos diferenciar esses casos com mais precisão porque as estatísticas mostram o tamanho médio de registro e versão para toda a tabela, enquanto a contagem de versões visíveis para transações concorrentes pode variar.

7. Por que o IBAnalyst nomeia alguns índices como “ruins”?

Índices com valor de seletividade inferior a 0,01 são marcados como “ruins” no IBAnalyst (veja a ajuda da visão Índice). Há várias causas para nomear um índice específico como ruim:

1. A seletividade desse índice é inferior a 0,01. Teoricamente, o otimizador não deve usar esse índice, mas o usa se não houver outros índices (para cláusula where, order by ou join, pelo menos)

2. Esse índice causa coleta de lixo muito lenta. Este problema não existe no InterBase 7.1/7.5 e será corrigido no Firebird 2.0

3. Esse índice torna o processo de restauração muito lento, e ele é criado muito lentamente (create/alter index active). Isso ocorre porque a cadeia de números de registro é grande para uma chave de índice.

4. Se esse índice for usado na cláusula where, o uso de memória dependerá do valor pesquisado (tamanho do bitmap). Como a cadeia de registros pode ser grande (muitas duplicatas de chave), o consumo de memória também será grande.

5. Se esse índice for usado em “order by”, e houver muitas duplicatas principalmente em valores de chave mais baixos (dependendo da ordem de classificação do índice), haverá muitas leituras de páginas de índice, o que diminuirá a consulta.

É por isso que o IBAnalyst não pode ignorar a existência de tais índices.

O pior caso para um índice é quando ele tem a coluna Uniques = 1, ou seja, todos os valores para a coluna indexada são os mesmos. Esses índices estão listados em “Índices inúteis” na página Resumo.

Claro, para sua aplicação, tal índice pode ser “bom”. Por exemplo, se os registros têm um sinalizador “arquivo” em alguma coluna, e sua aplicação pesquisa pelo índice nessa coluna apenas para dados atuais, não arquivados. Portanto, cabe a você decidir se estamos certos em nomear esse índice como “ruim” ou não.

8. E se o índice “ruim” for criado por uma restrição de Chave Estrangeira?

Bem, o parágrafo anterior mostra que é melhor eliminar índices “ruins” (se você não os usa para pesquisar chaves com menos duplicatas do que outras chaves). Mas, se tal índice for criado por uma chave estrangeira, você só pode eliminá-lo eliminando a chave estrangeira. Eliminar a chave estrangeira desabilitará a verificação de restrição de relação, o que pode ser inaceitável.

Você pode substituir a FK por triggers, mas com algumas restrições. A FK controla as relações de registro usando índice, e o índice “vê” todas as chaves para todos os registros independentemente do estado das transações. Mas os triggers funcionam apenas no contexto da transação do cliente. Então, ao substituir a FK por triggers, você deve garantir que:

• Os registros não serão excluídos da tabela mestre, ou serão excluídos no modo “snapshot table reserving”
• A coluna, usada pela PK na tabela mestre, nunca será modificada. Você pode restringir isso com um trigger before update.

Se você mantiver essas condições, pode eliminar a Chave Estrangeira específica. Claro, não crie índice manualmente nessa coluna.

9. Por que na linha de porcentagem da versão de dados há apenas 12 megabytes de dados, mas tenho um banco de dados de 140 megabytes?

1. O IBAnalyst aqui mostra o volume de dados “puro”, sem contar outras estruturas do banco de dados (índices, metadados…) e fragmentação de páginas.

2. Após a restauração, o InterBase e o Firebird deixam algum espaço livre (15-25%) nas páginas de dados para tornar futuras atualizações/exclusões mais rápidas.

3. Há um comportamento específico do servidor quando ele deixa as páginas de dados fragmentadas em ~50%, se o tamanho do registro dessa tabela for baixo, cerca de 11-22 bytes.

10. Como melhorar o desempenho do otimizador em caso de atualizações frequentes

As estatísticas de índice são armazenadas na coluna RDB$INDICES.RDB$STATISTICS e são atualizadas de 3 maneiras:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE, ou CREATE INDEX …

3. Processo de restauração (todos os índices são reconstruídos, bem como “ALTER INDEX ACTIVE”)

O otimizador usa essas informações de estatísticas para preparar consultas. Usando os valores de estatísticas, o otimizador pode decidir se o índice é “bom o suficiente” ou “não útil” para recuperar registros.

Se as estatísticas não forem atualizadas por um longo tempo, o otimizador pode produzir um plano ruim porque os valores de estatísticas existentes não correspondem ao estado real das coisas, pois os dados da tabela podem ser significativamente alterados (por exemplo, a quantidade de registros aumentou 5-10 vezes, ou vice-versa, todos os registros foram excluídos).

Você pode substituir o plano automático de consulta ruim por um PLAN explícito para uma consulta específica, mas essa não é uma boa abordagem, pois os dados podem ser significativamente alterados após o desenvolvimento do plano.

A maneira alternativa (e correta) é atualizar as estatísticas periodicamente aplicando a instrução SET STATISTICS para todos os índices. Você pode agendar a execução de um script SQL para atualizar as estatísticas usando ISQL ou a ferramenta pronta para uso gidx (somente Windows).

Se você tem algumas tabelas com registros diferentes recarregados periodicamente, essa abordagem não ajudará. Vamos considerar o exemplo:

• A tabela A é carregada com dados 4-5 vezes por dia.
• Após o processamento dos dados carregados, todos os registros na tabela A são excluídos

Neste caso, podemos ver 2 valores de estatísticas corretos para índices na tabela A - quando está carregada com dados e quando está vazia. Então, as estatísticas recalculadas na tabela carregada serão inúteis quando a tabela estiver vazia, e vice-versa.

Para evitar isso, você precisa recalcular as estatísticas dos índices na tabela A apenas quando a tabela estiver preenchida com dados. O melhor é antes que as consultas nessa tabela sejam executadas.

Desde a versão 1.91, o IBAnalyst mostra a diferença de estatísticas de índice e permite que você a recalcule a qualquer momento. Primeiro, você precisa olhar as informações de registro da tabela - é a contagem média usual de registros ou não. Se sim, você pode recalcular a seletividade do índice com segurança. Se não - talvez seja melhor não tocar nas estatísticas do índice, pois isso pode fazer o otimizador produzir planos de consulta ainda piores.