Exemplo de como um índice ruim pode afetar a performance de um banco de dados Firebird

Sumário

Este pequeno artigo mostra através de exemplos como um índice ruim pode afetar a performance de um banco de dados Firebird

Banco de dados de testes

Imagine que você tem um banco de dados bem simples do Firebird

CREATE DATABASE “E:\TESTFB25INDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;

CREATE TABLE TABLEIND1 (

    I1            INTEGER NOT NULL PRIMARY KEY,

    NAME          VARCHAR(250),

    MALEORFEMALE  SMALLINT

);

CREATE GENERATOR G1;

SET TERM ^ ;

create or alter procedure INS1MLN

returns (

    INSERTED_CNT integer)

as

BEGIN

inserted_cnt = 0;

WHILE (inserted_cnt <1000000) DO

BEGIN

 Insert into tableind1(i1, name, maleorfemale) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));

 inserted_cnt=inserted_cnt+1;

END

suspend;

END^

SET TERM ; ^

GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;

GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;

COMMIT;

Por alguma razão você precisa:

·         inserir 1 milhão de registros

·         atualizar todos estes registros

·         apagar todos os registros

·         executar um select count(*) neste banco de dados

Para executar esta tarefa, nós podemos usar o script abaixo (que será executado imediatamente após o script de criação do banco de dados) :

set stat on;  /*show statistics*/

select * from ins1mln;

update tableind1 SET MALEORFEMALE = 3;

delete from tableind1;

select count(*) from tableind1;

guardaremos os resultados para análise posterior.

Então criaremos outro banco de dados com a mesma estrutura, mas adicionaremos um índice na coluna MALEORFEMAIL

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MALEORFEMALE);

Como pode notar, vamos inserir apenas os valores 0 ou 1 nesta coluna.

Repetiremos a execução do script no banco de dados com este índice e compararemos os resultados.

Resultados estão na tabela a seguir:

Sem o índice para MALEORFEMAIL

Com o índice para MALEORFEMALE

SQL> set stat on; /*show statistics*/ SQL> select * from ins1mln; INSERTED_CNT ============      1000000 Current memory = 10487216 Delta memory = 80560 Max memory = 12569996 Elapsed time= 13.33 sec Buffers = 2048 Reads = 0 Writes 18756 Fetches = 7833503 SQL> update tableind1 SET MALEORFEMALE = 3; Current memory = 76551788 Delta memory = 66064572 Max memory = 111442520 Elapsed time= 15.04 sec Buffers = 2048 Reads = 16166 Writes 15852 Fetches = 6032307 SQL> delete from tableind1; Current memory = 76550240 Delta memory = -1548 Max memory = 111442520 Elapsed time= 3.27 sec Buffers = 2048 Reads = 16147 Writes 16006 Fetches = 5032277 SQL> select count(*) from tableind1;        COUNT ============            0 Current memory = 76552064 Delta memory = 1824 Max memory = 111442520 Elapsed time= 1.35 sec Buffers = 2048 Reads = 16021 Writes 1791 Fetches = 2032278

SQL> set stat on; /*show statistics*/ SQL> select * from ins1mln; INSERTED_CNT ============      1000000 Current memory = 10484140 Delta memory = 75524 Max memory = 12569996 Elapsed time= 23.94 sec Buffers = 2048 Reads = 1 Writes 23942 Fetches = 11459599 SQL> update tableind1 SET MALEORFEMALE = 3; Current memory = 76548712 Delta memory = 66064572 Max memory = 111439444 Elapsed time= 29.30 sec Buffers = 2048 Reads = 16167 Writes 19492 Fetches = 10035948 SQL> delete from tableind1; Current memory = 76547164 Delta memory = -1548 Max memory = 111439444 Elapsed time= 3.41 sec Buffers = 2048 Reads = 16147 Writes 15967 Fetches = 5032277 SQL> select count(*) from tableind1;        COUNT ============            0 Current memory = 76548988 Delta memory = 1824 Max memory = 111439444 Elapsed time= 0.69 sec Buffers = 2048 Reads = 16021 Writes 1901 Fetches = 2032278

Portanto, um índice ruim reduz a performance aproximadamente 2 vezes durante inserções e atualizações. Também podemos notar que um índice pouco seletivo aumenta o número de escritas e fetches de registros.

Vamos analisar as estatísticas deste banco de dados de exemplo (com o índice ruim para MALEORFEMALE) e olhar os detalhes.

Para obter as estatísticas, rodaremos o seguinte comando:

gstat -r e:\testfb25index.fdb > e:\teststat.txt

A seção para a tabela TABLEIND1 e seus índices tem informações intrigantes, mas que tipo de informação útil nos fornece ?  

TABLEIND1 (128)

    Primary pointer page: 166, Index root page: 167

    Average record length: 0.00, total records: 1000000

    Average version length: 27.00, total versions: 1000000, max versions: 1

    Data pages: 16130, data page slots: 16130, average fill: 93%

    Fill distribution:

       0 - 19% = 1

      20 - 39% = 0

      40 - 59% = 0

      60 - 79% = 0

      80 - 99% = 16129

    Index RDB$PRIMARY1 (0)

      Depth: 3, leaf buckets: 1463, nodes: 1000000

      Average data length: 1.00, total dup: 0, max dup: 0

      Fill distribution:

           0 - 19% = 0

          20 - 39% = 0

          40 - 59% = 0

          60 - 79% = 1

          80 - 99% = 1462

    Index TABLEIND1_IDX1 (1)

      Depth: 3, leaf buckets: 2873, nodes: 2000000

      Average data length: 0.00, total dup: 1999997, max dup: 999999

      Fill distribution:

           0 - 19% = 0

          20 - 39% = 1

          40 - 59% = 1056

          60 - 79% = 0

          80 - 99% = 1816

Para entender o significado dos números e porcentagens, podemos usar a ferramenta IBAnalyst, que nos oferece uma interpretação visual das estatísticas do banco de dados:

Clicando em "Reports/View recommendations" acharemos a explicação apropriada para este índice:

Bad indices count: 1.

By `bad` we name indices with many duplicate keys (90% of all keys)

and big groups of equal keys (30% of all keys). Big groups of equal

keys slowdown garbage collection - MaxEquals here is % of max groups

of keys having equal values. Index search for such an index is not

efficient. You can drop such indices (if they are not on FK

constraints).
Contagem de índices ruins:1.
Chamamos de "Ruins" os índices com muitos valores duplicados
(90% de todas as chaves) e com grupos grandes de chaves iguais
(30% de todas as chaves). Grupos grandes de chaves iguais fazem
com que o processo de garbage collection seja mais lento - MaxEquals
é o % de grupos que contém chaves com o mesmo valor. Pesquisas indexadas
usando tais índices não são eficientes. Você pode remover estes índices
(se eles não forem criados por constraints de FK)

Index          ( Relation)  Duplicates  MaxEquals

TABLEIND1_IDX1 (TABLEIND1) :   100%,      50%

Em bancos de dados de produção é comum ver muitos índices ruins, o que pode afetar de forma dramática a performance do banco de dados. Neste exemplo vemos uma tabela com 13 milhões de registros que possui 7 índices ruins, que são (provavelmente) inúteis e degradam significativamente a performance do Firebird.

Geralmente a primeira ação do desenvolvedor é remover estes índices ruins. Por outro lado, é possível que o índice ruim seja usado em algumas queries especiais, que precisam de uma combinação específica de índices (incluindo o índice ruim) para que possa ser executada de forma suficientemente rápida.

O Otimizador SQL do Firebird Firebird possui regras complexas, e é comum que removendo o índice ruim faça com que algumas queries tenham a performance significativamente degradada.

Claro que estas queries devem ser reescritas de forma a usar um plano melhor sem o índice ruim, mas ainda temos um problema: como encontrar estas queries que usam o índice ruim ? 


A resposta está no próximo episódio da série de nossos artigos sobre Otimização.