Introdução

Atualmente, tenho recebido muitos chamados referentes a problemas relacionados ao desempenho e tive a ideia de dar uma aula sobre isso. Já que sou preguiçoso demais para criar o meu próprio benchmark e seria mais útil "dissecar" um benchmark amplamente utilizado, considerei que o "benchmark Fastest DBA", usado por Lester Knutsen e pela Advanced DataTools seria um bom estudo. Para os que não estão familiarizados, trata-se de uma competição periódica organizada pela Advanced DataTools, na qual eles apresentam um aplicativo lento e desafiam os participantes a ajustá-lo. Incluí links para os detalhes sobre essa competição na seção Recursos .

Também percebi que as aulas não são tão úteis quanto a palavra escrita — portanto, o que você está lendo agora é uma análise de como ajustar um banco de dados com base em uma "dissecação" do benchmark Fastest DBA. Executaremos o benchmark, observaremos os problemas de desempenho, trataremos de cada problema individualmente e tentaremos executar o benchmark em uma passagem final com todos os problemas resolvidos. Observe que esse é o segundo benchmark; haverá um artigo de acompanhamento que dissecará o terceiro (e atual) benchmark.

Este artigo não é sobre a instalação do benchmark — o arquivo README é bastante abrangente. Tive dois problemas para colocá-lo no meu MacBook — o primeiro foi o SHMBASE do mecanismo. Tive que editar o arquivo $ONCONFIG de origem e configurá-lo adequadamente (à medida que ele é copiado novamente a cada execução do setup.sh). O segundo problema que eu tive foi que o meu usuário do Informix adota como padrão o grupo "admin" — portanto, eu tive que alterar manualmente a propriedade no dbspaces para Informix:Informix.

A execução do benchmark sem alterações não concluirá em tempo hábil. O benchmark propriamente dito é bastante simples: ele carrega uma tabela de clientes e, em seguida, gera faturas para esses clientes. A última dificuldade é uma atualização na tabela de faturas, que toma bastante tempo. O trabalho não é muito complicado, mas dá a oportunidade de ver como se pode melhorar o desempenho por meio do ajuste.

Por onde começar?

Normalmente, em qualquer projeto de ajuste de desempenho, a primeira questão é a determinação do problema. Como você sabe que resolveu o problema com o ajuste? Como você mede o desempenho e determina que ele melhorou e quanto melhorou? Para os fins deste artigo, afirmarei que a execução do benchmark demora demais e que nós precisamos que ele execute em menos de uma hora. Além disso, capturaremos métricas de banco de dados em cada estágio para determinar se o fizemos melhorar.

A segunda questão do ajuste de banco de dados é determinar o que deve ser ajustado. Geralmente veremos os melhores resultados ao corrigir o seguinte:

1. O modelo de dados
2. O aplicativo
3. O disco
4. E, finalmente, o mecanismo.

Já que estou executando em um único disco em um MacBook Pro, não posso fazer muitas coisas em termos de ajuste de disco. Fique de olho na CPU, no disco e no uso da memória.

Voltar para parte superior

Executando o benchmark

Depois de instalar o benchmark, execute um onstat -z para limpar as métricas do mecanismo, para que você possa ter certeza de que só está vendo o que o benchmark faz com o mecanismo.

Você verá que os criadores do benchmark tiveram a gentileza de ativar o explain plan — portanto, ele está integrado ao diretório atual. Há um timer para todo o script run.sh, mas talvez seja útil ter tempos intermediários para cada etapa — portanto, editaremos benchmark_run.sql e colocaremos indicações de data e hora depois de cada bloco de código com a seguinte instrução:

select current hour to second from systables where tabid=1;

Em seguida, inicie o benchmark com run.sh. Em uma janela separada, inicie um onstat -pr. Essa ação imprimirá as métricas do mecanismo a cada cinco segundos. Em seguida, é possível sentar-se ao lado do benchmark e monitorá-lo ou passar a noite ou o final de semana em casa.

Nossa primeira inserção na tabela de faturas (no meu laptop) demorou 1m16s (a segunda demorou 1m17s) e o perfil do mecanismo é semelhante a:

IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- 
Up 1 days 02:35:27 -- 118812 Kbytes

Profile
dskreads pagreads  bufreads  %cached dskwrits  pagwrits  bufwrits  %cached
175586   178320    4720830   96.28   113398    237194    1002488   88.69  

isamtot   open      start     read      write     rewrite   delete   commit  rollbk
4130803   157       495827    1321784   330055    24        65       55      0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs   
0          0          0          0          0          0          0         

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          112.78   13.67    2          1         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
449        0          2480376    0          0          1          7          28        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
28         0          165256     165232     1520    

Essa saída é semelhante à vara que mede a quantidade de óleo em um carro. Em uma breve página, captura o estado do mecanismo.

A linha superior (sob Profile) trata do desempenho do disco e cache. Aqui, estamos interessados nas taxas de acessos do cache de leitura e gravação. A taxa de acesso ao cache de leitura deve ser superior a 99%, e queremos que a taxa de acessos ao cache de gravação seja superior a 95%. Sem dúvida, pode-se ver que os valores não são esses. Já que ainda não fizemos muita coisa e os valores de cache podem não ter se consolidado, isso pode estar correto. Apesar disso, olhando uma linha acima, no tamanho do mecanismo na memória (118.812 KB), não estamos trabalhando com muita memória. Vamos manter o cache na tabela.

A segunda linha (isamtot) indica a quantidade de trabalho real que foi realizada (isamtot é o total de operações de isam, por exemplo).

A terceira linha (gp_*) descreve como o mecanismo está gerenciando com páginas genéricas ou páginas não padrão. Já que não estamos usando nenhuma neste benchmark, todas as leituras são 0. Removerei essa linha nas leituras futuras da "vareta de medição de óleo".

A quarta linha começa com o número de vezes que excedemos bloqueios, encadeamentos de usuários ou buffers (tudo 0, e geralmente deve ser assim). As duas seguintes são a quantidade de CPU do usuário e CPU do sistema, o número de pontos de verificação e o número de limpezas de memória (flushes).

A quinta linha é de grande interesse:

O interessante aqui é um número marginal de esperas de buffer e varreduras sequenciais, mas um número muito grande (neste ponto) de solicitações de bloqueio.

A sexta linha nos informa como a leitura antecipada está funcionando e o número de esperas por travas. Aparentemente, todas as leituras antecipadas de índices e dados são consumidas pelo mecanismo. Novamente, vemos um número muito grande (neste ponto) de solicitações de trava.

Vamos deixar o benchmark executar por mais tempo e verificar novamente.

Faz dois dias. Meu laptop entrou no modo de espera algumas vezes — portanto, não executamos de fato durante 48 horas (embora o mecanismo "pense" que esteve ativo esse tempo todo). Vamos repassar a "vareta de medição de óleo" e ver o que ela tem a dizer.

IBM Informix Dynamic Server Version 11.50.FC6DE -- On-Line -- 
Up 3 days 05:42:03 -- 127004 Kbytes

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
19966695480 19989379994 60891586765 67.21   660217     892639     3121469    78.85  

isamtot   open     start     read      write    rewrite   delete   commit   rollbk
15495512  9115     1409626   6189708   759769   955295    1112     6097     0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          221553.88 127876.85 1136       604       

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
364286149  0          59940823034 0          0          51         1707       102128    

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
224274     0          19966050547 19966274029 4146547 

As coisas que queríamos observar aumentaram muito e estão fora de controle. (Observação: normalmente, o número de espera do ponto de verificação pode dar pausa, mas, nesse caso, o benchmark está executando um único processo SQL; portanto, esse processo está esperando a si mesmo e, de qualquer forma, os pontos de verificação são um meio mais eficiente de gravar no disco. Então, não vamos ficar empolgados com isso).

A taxa de cache de leitura está em 67%. A taxa de cache de gravação está em 78%. Enquanto a quantidade de instruções ISAM lógicas que realizamos está em seis milhões de leituras, o número de solicitações de bloqueio (um indicador físico do que foi lido) está em 59 bilhões. Os números de varreduras sequenciais, esperas de buffer e esperas de trava estão fora de controle. Estamos fazendo uma grande quantidade de leituras antecipadas, que está sendo consumida. Isso é ótimo, mas por que estamos fazendo tantas leituras antecipadas?

Parece que estamos fazendo uma varredura sequencial (de uma tabela inteira) para cumprir o comando SQL, seja ele qual for. Vamos analisar isso mais detalhadamente.

No banco de dados sysmaster, há duas tabelas que nos interessam no momento: sysptprof (Partition Profile) e syssesprof (Session Profile). Vamos ver o que elas nos oferecem:

select * from sysptprof order by abs(seqscans) desc;

Observação: se o número de varreduras sequenciais excede MAXINT (mais de dois bilhões), ele passará a ser um número negativo — sendo assim, pedimos o valor absoluto. Estes são os resultados dessa seleção:

dbsname     benchmark
tabname     bills
partnum     3145799
lockreqs    136990885
lockwts     0
deadlks     0
lktouts     0
isreads     2419074
iswrites    605280
isrewrites  853404
isdeletes   0
bufreads    1085720339
bufwrites   2071693
seqscans    99566
pagreads    -1398716020
pagwrites   534809

dbsname     benchmark
tabname     customer
partnum     3145795
lockreqs    727188
lockwts     0
deadlks     0
lktouts     0
isreads     99566
iswrites    0
isrewrites  99562
isdeletes   0
bufreads    1898629
bufwrites   99562
seqscans    4
pagreads    727202
pagwrites   99562

Observe que pagreads (leituras de página) excedeu o limite e se tornou negativo. Não há como saber quantas vezes isso aconteceu durante a execução. Da mesma forma, as lockreqs (solicitações de bloqueio) podem ter excedido o limite várias vezes.

Em 102.000 varreduras sequenciais (consulte a saída de onstat acima), 99.000 foram realizadas nessa tabela. Essa tabela tem aproximadamente 600.000 linhas. Emitimos pelo menos 136 milhões de solicitações relacionadas à mesma. (Na verdade, 20 minutos depois, esse número está em 934 milhões. Isso é um problema). Se tomarmos 99.000*600.000, obteremos 59,4 bilhões — que, não por coincidência, constitui uma grande parte das solicitações de bloqueio. Em outras palavras, varremos sequencialmente essa tabela, bloqueando todas as linhas, várias vezes. Já que há 100.000 clientes, devo supor que estamos perto do fim da etapa atual. Na verdade, acabou de terminar:

Real  3113m27.925s
User  0m0.006s
Sys   23m45.717s

Infelizmente, não tivemos a oportunidade de examinar a tabela Session Profile. Essa tabela teria mostrado informações semelhantes, mas relacionadas à sessão; dessa forma, poderíamos ter visto que a nossa sessão havia realizado uma quantidade exagerada de varreduras sequenciais e solicitações de bloqueio.

Voltar para parte superior

Um problema comum

Tratarei do primeiro problema separadamente, porque ocorreu três vezes na semana passada com alguns colegas. Nosso primeiro problema grave é que estamos varrendo toda a tabela de faturas sempre que queremos uma linha da mesma e bloqueando todas as linhas que lemos.

Essa bobagem de varredura sequencial/bloqueio distorceu totalmente a métrica real de desempenho. Não podemos dar atenção à taxa de acessos ao cache de leitura porque varremos sequencialmente uma tabela enorme várias vezes. Consequentemente, vamos resolver isso antes de analisar métricas de desempenho que seriam mais realistas.

A instrução real que demorou dois dias para executar foi:

update customer
    set balance_due = balance_due + ( select sum ( total_bill )
          from bills where bills.customer_number = customer.customer_number )
    where customer_number in ( select customer_number from bills );

Se analisarmos o explain plan (localizado em sqexplain.out), começaremos a ver o problema (minhas observações em negrito):

update customer
        set  balance_due = balance_due + ( select sum ( total_bill )
                from bills where bills.customer_number = customer.customer_number )
where   customer_number in ( select customer_number from bills )

Estimated Cost: 3   -- The optimizer thought this would be cheap
Estimated # of Rows Returned: 10  With only 10 rows affected

  1) informix.customer: INDEX PATH Excellent, an index was used to read 
                                              this table

    (1) Index Name: informix. 101_2
        Index Keys: customer_number   (Serial, fragments: ALL)
        Lower Index Filter: informix.customer.customer_number = ANY subquery

    Subquery:
   
    Estimated Cost: 2
    Estimated # of Rows Returned: 1

      1) informix.bills: SEQUENTIAL SCAN Here is our problem

            Filters: informix.bills.customer_number = informix.customer.customer_number


    Subquery:
    
    Estimated Cost: 2  The optimizer thinks this will be cheap.  Why?
    Estimated # of Rows Returned: 1

      1) informix.bills: SEQUENTIAL SCAN

[deletia]

O que há de errado com a tabela de faturas, que faz o otimizador considerar que deve varrer sequencialmente a tabela inteira? Vamos dar uma olhada na tabela.

Select * from benchmark:systables where tabname = 'bills';

tabname          bills
rowsize          1266
nrows            0.00
created          08/23/2010
ustlowts

Eu excluí várias informações excessivas aqui para mostrar duas informações importantes. De acordo com os catálogos do sistema, a tabela de faturas está vazia. "Ustlowts" é nulo, indicando que as estatísticas nunca foram executadas nessa tabela. De fato, se examinarmos o código do benchmark, veremos que a tabela foi criada, preenchida e usada, mas as estatísticas nunca foram atualizadas. Essa é uma questão de Database 101. O otimizador usa catálogos de sistema indicando quantas linhas existem e qual é a qualidade da formação dos índices para detectar o caminho de consulta que deve ser usado. Já que a tabela está "vazia", obviamente é mais fácil ler a tabela inteira do que tentar usar um índice.

A segunda informação importante é que estamos tentando ler as faturas usando customer_number. Não há índice em customer_number na tabela de faturas. Mesmo se atualizarmos as estatísticas da tabela, o otimizador não terá outra opção senão varrê-la sequencialmente.

Na nossa primeira melhoria no benchmark, vamos acrescentar um índice à tabela de faturas e atualizar as estatísticas da tabela. Já que o índice "encarece" a gravação em uma tabela, crie o índice imediatamente após o preenchimento da tabela de faturas. Vamos atualizar as estatísticas depois de criar o índice. (Observação: na V11.x as estatísticas são geradas no momento da criação do índice, como parte do processo de criação do mesmo. Vamos testar isso).

Acrescentamos o seguinte:

create index bills_idx1 on bills(customer_number);

Imediatamente, após o preenchimento da tabela de faturas, podemos limpar os contadores de status com onstat -z e reiniciar o benchmark.

Nosso índice foi criado. Vamos verificar as informações da tabela:

tabname          bills
rowsize          1266
nrows            605280.0000000
ustlowts         2010-08-25 20:52:22.00000

Muito melhor. As estatísticas foram atualizadas e, não só nrows está preenchida, mas também vemos quando as estatísticas foram atualizadas pela última vez.
Essa execução teve o seguinte desempenho:

Real    36m5.033s
User    0m0.005s
Sys     0m1.479s

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
1397183    1600342    17971316   92.23   656896     885752     3087149    78.72  

isamtot   open    start     read      write     rewrite   delete   commit   rollbk
14888305  284     1354389   6438035   747981    956259    30       170      0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          2980.00  119.77   22         20        

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
244886     0          10281980   0          0          10         12         71        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
479759     64         901978     1381601    12531   

Agora estamos dentro do razoável em vários indicadores e eliminamos o ruído, mas ainda é cedo demais para ajustar o mecanismo.

Voltar para parte superior

Ajustando o modelo de dados

A coisa mais eficiente que podemos fazer para melhorar o desempenho é corrigir quaisquer problemas de modelo de dados. Este é o modelo de dados atual.

Vemos alguns problemas aqui. Para começar, a tabela de faturas tem todo tipo de "lixo" desnormalizado. Todas as informações de cliente, estado e produto foram despejadas lá.

Todos os itens em negrito são lixo extra para carregar que poderiam ser recuperados facilmente a partir das tabelas de origem. Sales_Tx é horrível, porque, no único lugar onde ela poderia ser usada, a leitura é realizada a partir da tabela de estados. Product_Price também não deveria estar aqui, mas, se analisarmos como ela é usada, é simplesmente o nome que está equivocado. Trata-se, na verdade, de quanto foi faturado do cliente pelo produto que está na fatura. O nome mais adequado seria Billed_Price.

Se analisarmos com mais profundidade, veremos dados estáticos misturados com dados dinâmicos. Por exemplo, é improvável que o nome e endereço do cliente mudem. Entretanto, certamente esperamos que o balance_due mude. Além disso, normalmente as notas de faturas não descrevem um cliente. Devem descrever a relação entre o cliente e sua fatura. Além do mais, a fatura em si está quebrada. Neste benchmark, geraremos seis notas para cada cliente, em vez de uma única nota com seis linhas. Vamos repassar esse modelo de dados com essas ideias em mente.

Este é um modelo revisado. O modelo ainda tem problemas: Zipcode é suficiente para identificar a cidade e o estado — portanto, esses atributos devem migrar para uma tabela City (em vez de uma tabela de estado) mas, como esses dados são estáticos, nós os deixaremos como estão.

Se fôssemos reorganizar o esquema, a reorganização daria um pouco de trabalho e seria conveniente incluir isso no tempo do benchmark. Observe que a execução do mesmo aplicativo no modelo de dados acima demorou 16 segundos (depois da reorganização dos dados).

Antes de fazermos isso, devemos dimensionar as tabelas. Embora o Informix não tenha mais um problema de desempenho relacionado a um grande número de extensões, ainda há o problema da demora de cada alocação de extensão. É muito melhor dimensionar a tabela adequadamente e não precisar perder tempo estendendo-a sempre que inserirmos 20 linhas.

O dimensionamento dessas tabelas deve ser:

O produto e o estado não mudarão; portanto, não nos daremos ao trabalho de redimensioná-los. Entretanto, atualizaremos as estatísticas relacionadas aos mesmos. (Observação: isso se tornou desnecessário e ineficiente devido às alterações realizadas mais adiante neste artigo).

Voltar para parte superior

Ajustando o aplicativo

A segunda coisa mais eficiente que podemos fazer para melhorar o desempenho é ajustar o aplicativo. Isso se enquadra na categoria "Conhece os teus dados, conhece as tuas consultas". Devemos ficar atentos ao modelo de dados ao fazer isso, já que alguns aspectos do modelo de dados podem ajudar o aplicativo.

A primeira coisa que o aplicativo faz é gerar três conjuntos de dados para os clientes que têm um product_code que começa com A. Um número diferente de faturas pode ser gerado com base na data inicial do cliente (>= 1/1/2000, >=1/1/2005, >=1/1/1985). E de formas diferentes para os produtos 1 e 2, 4 e 7 e para 9 e 10. Observando os dados, todos os códigos de produto começam com A e a data inicial mais baixa na tabela de clientes é 1/1/2007. Em outras palavras, podemos combinar essas três instruções insert em uma única instrução insert e só precisamos ler as tabelas de origem uma vez, e não três.

Em seguida, o aplicativo atualiza a tabela Bills, configurando um desconto para o produto como 10 onde a start_date é <= 1/1/2009 e o balance_due é > 50000. A cláusula WHERE é uma repetição dessa condição e acrescenta a pista falsa do "número do produto em (1 a 10)". Já que isso engloba todos os produtos, esse filtro não faz sentido. Há aproximadamente 40.000 clientes cuja start_date é < 1/1/2009 e cujo balance_due é > 50000. Isso significa que uma varredura sequencial da tabela será mais rápida do que uma leitura indexada, e não podemos brincar com os critérios de filtro.

Em seguida, atualizamos as faturas novamente, configurando o total da fatura como "preço - desconto + imposto sobre as vendas", que é lido a partir da tabela de estados. Novamente, há a pista falsa dos números de produto de 1 a 10 (em outras palavras, todos eles). Se tivéssemos deixado o sales_tax na tabela Bills, não teríamos que procurá-lo e obtê-lo, principalmente porque agora temos que navegar na tabela de clientes para chegar lá. Vamos colocar isso de volta.

Finalmente, atualizamos o cliente, incrementando o seu balance_due com o valor de total_bill a partir da tabela de faturas. Há outras pistas falsas, já que somente atualizamos clientes que têm faturas. Já que todos têm faturas, isso é irrelevante. Colocamos Balance_due em sua própria tabela no momento. Se estivesse na tabela com as Bills nela, poderíamos evitar uma atualização extra. Pode-se argumentar que, como agora só há uma nota por cliente, a nota poderia residir lá.

Em seguida, lemos os totais para se certificar de que o aplicativo executou corretamente: Record count, sum(product_price), sum(product_discount), sum(total_bill). Essas informações agora estão na tabela Bill_Items. Poderíamos acelerar essa leitura se fizéssemos colunas de resumo para essas informações na tabela de faturas. Obviamente, isso teria como resultado uma contagem de registros incorreta, que sabemos ser #Customers*6, mas que não reflete a verdade).

Além disso, lemos Count(*) e sum(balance_due) a partir dos clientes. Já que agora temos Balance_Due na tabela de faturas, agra devemos ler diretamente de lá.

Agora o nosso modelo de dados está assim:

As operações que iremos realizar são:

• Varredura da tabela de clientes (6 segundos)
• Varredura da tabela State (0 segundo)
• União das tabelas Customer e State (6 segundos)
• Inserção em Bills (7 segundos)
• Criação de índices (1 segundo)
• Carregamento da tabela bill_items (8 segundos)
• Criação do índice em bill_items (3 segundos)
• Atualização de bill_items (58 segundos); varredura sequencial de bill_items com uma leitura dos clientes em loop aninhado
• Atualização das faturas (5 segundos); varredura sequencial de faturas com uma leitura de bill_items em loop aninhado
• Verificação (4 segundos)

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
721155     734054     10036861   92.82   19724      60567      1450097    98.64  

isamtot   open    start     read      write    rewrite   delete    commit   rollbk
10295768  447     807589    4558564   706236   706197    65        45       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          75.50    42.50    4          3         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
459393     0          7468727    0          0          2          13         22        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
605515     0          105695     711210     212     

real    1m38.397s
user    0m0.007s
sys     0m1.510s

Se analisamos a atualização de bill_items, só estamos atualizando um desconto com base em informações da tabela de clientes, que tínhamos quando desenvolvemos a tabela bill_items pela primeira vez. Vamos aproveitar isso e configurar o desconto enquanto estamos criando a tabela logo de início. O seguinte uso da instrução CASE cuidará disso para nós:

insert into bill_items
        (bill_number,
        customer_number,
        product_number,
        billed_price,
        product_discount)
 select bill_number,
        bills.customer_number,
        product_number,
        product_price,
        case when (customer.start_date<="01/01/2009" 
               and customer.balance_due>50000) then 10
        else 0
        end case
   from bills, product, customer
  where product.product_number in (1,2,4,7,9,10)
    and bills.customer_number=customer.customer_number;

E se executamos novamente?

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
209038     221593     8178955    97.44   25104      49481      844982     97.03  

isamtot   open    start    read      write     rewrite   delete   commit   rollbk
7271368   376     908477   4242538   706230    100913    65       32       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          45.13    3.65     4          3         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
1617       0          6056020    0          0          2          13         10        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
1784       0          202678     204462     81      

real	0m34.160s
user	0m0.006s
sys	0m0.050s

loaded bills in 3 sec
loaded bill_items in 19 sec
updated bills in 6 sec
verifications 4 sec

Veja todos esses bloqueios. Já que não estamos manipulando os bloqueios, o mecanismo está bloqueando cada linha (ou página) à medida que precisa dela. Vamos bloquear as tabelas em modo exclusivo. Observe que isso requer o uso de transações para cercar os nossos bloqueios. Precisamos ficar atentos aos logs lógicos e garantir que tenhamos o suficiente, para que não fiquem cheios e nos coloquem em uma transação longa.

Ao bloquear as tabelas de forma exclusiva antes do início de cada inserção, o resultado da nossa operação de atualização é:

78036 Kbytes
Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
9943       217306     7344046    99.88   5757       38980      836059     99.31  

isamtot   open    start    read      write    rewrite   delete   commit   rollbk
7270986   496     908610   4241799   706213   100905    54       28       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          35.80    3.90     1          0         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
3          0          3252       0          0          0          11         11        

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
6          0          0          6          152     

real	0m31.829s
user	0m0.006s
sys     0m0.048s

Outra coisa que eu esqueci na camada do aplicativo: o Informix tem um desempenho muito bom ao ler e gravar grandes volumes de dados. Entretanto, em um banco de dados que cria logs, iremos registrar cada operação realizada. Podemos melhorar grandes inserções de lote configurando o tipo da tabela como raw ao carregá-la e em seguida redefini-la como tabela padrão para poder desenvolver índices nela. Alterar as tabelas Bills e Bill_Items para raw e, em seguida, carregá-las dá o seguinte resultado:

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
849        208192     6732674    100.00  10031      13202      836054     98.80  

isamtot   open    start    read      write    rewrite   delete   commit   rollbk
7474373   370     807441   4042766   706210   100903    49       20       0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
0          0            0          31.98    2.32     6          6         

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
0          0          2664       0          0          3          11         7         

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits  
0          0          0          0          13      

real	0m21.686s
user	0m0.005s
sys	0m0.019s

Voltar para parte superior

Resumo

Esgotamos o que podemos demonstrar com este exemplo especifico e teremos que nos aprofundar no mecanismo em um artigo posterior, com um exemplo mais voltado para a exploração do mecanismo. O que interessa neste exemplo é a execução do benchmark sem trabalhar com o mecanismo. "Corrigimos" o modelo de dados e o aplicativo e atualizamos as estatísticas. As ferramentas que usamos para monitorar o que o mecanismo estava fazendo se aplicam a qualquer exercício de ajuste com o Informix.

Apêndice

Veja abaixo o script SQL do benchmark original e as modificações que fizemos neste artigo. Os acréscimos estão em negrito e as exclusões estão em itálico.

-- File: benchmark_run.sql
-- Date: 4/20/2009
-- Author: Lester Knutsen
-- Description:
-- There are 4 tables in this process to generate bills
-- customer table with 100100 records,
-- states table with sales tax info
-- product table with 10 products and the
-- bills table where the results get written to.
-- Log: Created 4/20/2009

set explain on;
update statistics high for table product(product_number);
update statistics high for table state(state);
update statistics high for table customer(customer_number);
drop table bill_items;
set isolation dirty read;

-- drop and re-create the bills table

drop table "informix".bills ;

-- Note: Type raw and not carrying useless columns around
-- Also got rid of Primary key since we are now a raw table.

create raw table "informix".bills 
	(
	bill_number serial not null ,
	customer_number integer,
	last_name char(30),
	first_name char(30),
	address char(50),
	city char(20),
	state char(2),
	state_name char(20),
	zip integer,
	start_date date,
	bill_date date,
	bill_notes char(1000),
	product_code char(4),
	product_number integer,
	product_name char(50),
	product_price decimal(16,2),
	product_discount decimal(16,2),
	sales_tx decimal(16,2),
	total_bill decimal(16,2),
	balance_due decimal(16,2),
	primary key (bill_number )
	);

-- Note: Bill_items was not part of original data model

create raw table bill_items
        (
        bill_number integer not null ,
        customer_number integer,
        product_number integer,
        billed_price decimal(16,2),
        product_discount decimal(16,2)
        ) ;

-- Note: Loading Bills in one single statement.

begin work;
lock table bills in exclusive mode;
insert into bills 
        (bill_number,
        customer_number,
        bill_date,
        sales_tax,
        total_bill,
        balance_due)
 select 0,
        customer_number,
        today, -- bill_date
        state.sales_tax,
        0, -- total bill
        balance_due
   from customer, state
  where customer.state=state.state;

alter table bills type(standard);

-- Note: And putting the index back on, effectively updates statistics

create index bill_idx1 on bills(customer_number);
commit;

-- Note: Replaced the next 4 blocks of code with the single block above

-- Create bills for product numbers 1 and 2

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date ,
	bill_date ,
	product_code ,
	product_number,
	product_name ,
	product_price ,
	product_discount ,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date ,
	today, -- bill_date ,
	customer.product_code ,
	product.product_number,
	product.product_name ,
	product.product_price ,
	0, -- product_discount ,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2000"
and product.product_number in ( 1, 2 );

Create bills for product number 4 and 7

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date,
	bill_date,
	product_code,
	product_number,
	product_name,
	product_price,
	product_discount,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date,
	today, -- bill_date,
	customer.product_code,
	product.product_number,
	product.product_name,
	product.product_price,
	0, -- product_discount,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/2005"
and product.product_number in ( 4, 7 );

-- Create bills for product number 9 and 10

insert into  bills 
	(
	customer_number,
	last_name,
	first_name,
	address,
	city,
	state,
	state_name,
	zip,
	start_date ,
	bill_date ,
	product_code ,
	product_number,
	product_name ,
	product_price ,
	product_discount ,
	sales_tx,
	total_bill
	)
select 
	customer.customer_number,
	customer.last_name,
	customer.first_name,
	customer.address,
	customer.city,
	customer.state,
	state.state_name,
	customer.zip,
	customer.start_date ,
	today, -- bill_date ,
	customer.product_code ,
	product.product_number,
	product.product_name ,
	product.product_price ,
	0, -- product_discount ,
	state.sales_tax,
	0 -- total_bill
from customer, state, product
where customer.state = state.state
and customer.product_code[1] = product.product_code[1]
and customer.start_date >= "01/01/1985"
and product.product_number in ( 9, 10 );

Note: Loading Bill_Items with info that used to be in Bills

begin work;
lock table bill_items in exclusive mode;
insert into bill_items
        (bill_number,
        customer_number,
        product_number,
        billed_price,
        product_discount)
 select bill_number,
        bills.customer_number,
        product_number,
        product_price,
        case when (customer.start_date<="01/01/2009" and \
customer.balance_due>50000) then 10
        else 0
        end case
   from bills, product, customer
  where product.product_number in (1,2,4,7,9,10)
    and bills.customer_number=customer.customer_number;

alter table bill_items type(standard);

create index bill_items_idx2 on bill_items(bill_number);

commit;

Note: We took care of this by loading the bill_items table.

-- Update bills - give the best customers a $10 discount

update bills
   set product_discount =  ( select 10 from customer 
 where customer.customer_number = bills.customer_number
   and customer.start_date <= "01/01/2009" 
   and customer.balance_due > 50000 )
 where bills.customer_number in  ( select customer_number 
  from customer where customer.start_date <= "01/01/2009" 
   and customer.balance_due > 50000 )
   and product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ); 

-- Update bills - calculate the total with sales tax and minus the discount

-- Note: Similar, but taking advantage of our new data model.

begin work;
lock table bills in exclusive mode;
update bills
       set total_bill = (( (select sum(bill_items.billed_price-product_discount)
        from bill_items where bills.bill_number=bill_items.bill_number)) 
        * ( 1 +  sales_tax  )) ,

        balance_due=balance_due + ((
        (select sum(bill_items.billed_price-product_discount) 
        from bill_items where bills.bill_number=bill_items.bill_number)) 
        * ( 1 +  sales_tax  )) ;


commit;

update bills
set total_bill = (( product_price - product_discount )  \
* ( 1 + ( select sales_tax  from state where bills.state = state.state ))) \
where product_number in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 );

Note: Not needed since we have balance in the bills table.

-- Update the customer balance for customers who just got a bill

update customer
set  balance_due = balance_due + ( select sum ( total_bill ) \
from bills where bills.customer_number = customer.customer_number )\
where  customer_number in ( select customer_number from bills );


-- Calculate the totals  - This must match the expected results

-- Note: Modified to read values out of new structures.
select  count(*) record_count, 
        sum ( billed_price ) sum_product_price,  
        sum ( product_discount) sum_product_discount
from bill_items;

select  count(*) recourd_count, 
        sum ( product_price ) sum_product_price,  
        sum ( product_discount) sum_product_discount, 
        sum ( total_bill )  sum_total_bill
from bills;

Note: We had to move the total bill down to this section since that data is now at the bill level.

Note also: Because there are 4 states in the original customer table which do not exist in the State table, the number of bills will not match the number of customers (off by 120) and the balance_due as computed from bills will not include the 6044509.99 balance due from these customers. This could of course be corrected, but that is beyond the scope of this effort.

select count(*) customer_count,
       sum(total_bill) sum_total_bill,
       sum ( bills.balance_due )
from bills;

-- Perform a checkpoint to make sure all data is written to disk -
-- This is required ;)

execute function sysadmin:task ( 'onmode', 'c' );

-- End of SQL script
 

Recursos

Aprender

• Saiba mais sobre a competição Fastest Informix DBA, patrocinada pela Advanced DataTools.
  
  
• Saiba sobre Information Management na zona de Information Management no developerWorks. Encontre documentação técnica, artigos com instruções, educação, downloads, informações sobre produtos e mais.
  
  
• Fique por dentro doseventos técnicos e webcasts do developerWorks.
  
  
• Siga o developerWorks no Twitter.
  
  

Obter produtos e tecnologias

• Crie seu próximo projeto de desenvolvimento com a Versão de teste do software IBM, disponível para download diretamente no developerWorks.
  
  

Discutir

• Participar do fórum de discussão.
  
  
• Confira os blogs do developerWorks e participe da comunidade do developerWorks.
  
  

Sobre o autor

Jack Parker é engenheiro da Cisco Systems. Desenvolve e gerencia sistemas baseados em dados Informix desde meados da década de 80. É escritor ocasional, palestrante e contribui com o comp.databases.informix. Pode-se entrar em contato com ele pelo e-mailjack.parker4@verizon.net.

Classificar este artigo

Comentários

Voltar para parte superior

Índice

• Introdução
• Por onde começar?
• Executando o benchmark
• Um problema comum
• Ajustando o modelo de dados
• Ajustando o aplicativo
• Resumo
• Apêndice
• Recursos
• Sobre o autor
• Comentários

Conheça a IBM da sua cidade

A IBM está mais perto do que você imagina!

---

Saiba mais