Reindexação Automática em SGBDs Relacionais

Reindexação Automática em SGBDs Relacionais 

Departamento de Informática – PUC­Rio 

{emorelli,monteiro,abrito,sergio}@inf.puc­rio.br 

Abstract. We propose a solution to the automatic and online reindex problem.  The  proposed  mechanism  runs  continuously  and  with  almost  no  human  intervention. The adopted strategy is based on ad­hoc heuristics that monitor  the index fragmentation level  and, when  necessary,  reindex it  automatically.  We have actually implemented our ideas within the PostgreSQL DBMS code.  Practical performance tests show the effectiveness of our approach.

Resumo.  Neste  trabalho,  prop�e­se  uma  solução  para  o  problema  da  reconstrução automática e online de índices. O mecanismo proposto executa  de  forma  contínua  e  praticamente  sem  intervenção  humana.  A  estratégia  adotada  baseia­se  em  heurísticas  ad­hoc  que  monitoram  o  nível  de  fragmentação  das  estruturas  de  índice  e,  sempre  que  necessário,  as  reconstroem automaticamente. Nossas idéias  estão  implementadas no  SGBD  PostgreSQL.  Testes  de  desempenho  são  apresentados  para  mostrar  a  aplicabilidade da nossa abordagem na prática. 

1. Introdução 

As aplicações de bancos de dados têm se tornado cada vez mais complexas e variadas.  Estas  podem  ser  caracterizadas  por  manipularem  grandes  volumes  de  dados  e  pela  demanda  por  baixo  tempo  de  resposta  das  consultas  e  por  alta  produtividade  (throughput) das transações. Neste contexto, a sintonia (tuning) [Shasha e Bonnet 2003]  das estruturas de índice tem se revelado ainda mais importante, influindo diretamente no  desempenho dos sistemas de bancos de dados. 

Realizar o ajuste das estruturas de índice nas aplicações  atuais tem se tornado  uma  tarefa  extremamente  complicada  para  os  administradores  de  bancos  de  dados  (DBAs).  Esta  tarefa  requer  um  profundo  conhecimento  acerca  dos  detalhes  de  implementação  dos  sistemas  de  gerenciamento  de  bancos  de  dados  (SGBDs),  das  características dos dados armazenados, das aplicações e da carga de trabalho (conjunto  de consultas e atualizações) submetida ao SGBD [Bruno e Chaudhuri 2007a], [Luhring  et al 2007]. 

Os principais fabricantes de SGBDs oferecem ferramentas para prover suporte à  sintonia  automática  (ou  auto­sintonia)  de  índices  (Ex.:  [Zílio  et  al  2004],  [Bruno  e  Chaudhuri 2005]). A idéia é auxiliar os DBAs através da análise automática da carga de  trabalho e, com base nesta análise, recomendar a criação ou remoção de índices. 

Entretanto, tais ferramentas adotam uma abordagem estática (offline) na solução  do  problema  e  transferem  para  o  DBA  tarefas  de  sintonia  importantes  e  decisões  relativas à quando modificar o esquema e quais de fato são as modificações necessárias.  Além disso, são soluções ad­hoc e que exigem participação de DBAs especialistas. 

Algumas  iniciativas  recentes  apresentam  descrições  de  protótipos  que  implementam algumas funcionalidades na direção da sintonia automática e contínua das  estruturas de índice [Schnaitter et al 2006], [Luhring et al 2007], [Costa 2005], [Salles e  Lifschitz 2005], [Lifschitz e Morelli 2006]. Alguns desses trabalhos procuram estender  o SGBD PostgreSQL adicionando funcionalidades de auto­sintonia (Ex.:. [Salles 2004],  [Costa  2005],  [Salles  e  Lifschitz  2005],  [Lifschitz  e  Morelli  2006],  [Luhring  et  al  2007]).  Os  fabricantes  de  SGBDs  comerciais,  tais  como:  DB2,  Oracle  e  SQL  Server,  também têm procurado investigar novas funcionalidades que possibilitam a manutenção  automática das estruturas de índice [Bruno e Chaudhuri 2007a]. 

Contudo,  ainda  há  muito  desconhecimento  sobre  o  momento  ideal  de  criar  ou  remover  um  determinado  índice  e,  principalmente,  de  quando  recriá­lo.  O  benefício  trazido  pela  existência  de  um  determinado  índice,  para  uma  determinada  consulta,  é  influenciado por dois fatores: a sua seletividade da consulta e o grau de fragmentação do  índice.  Logo,  manter  índices  fragmentados  pode  degradar  o  desempenho  da  carga  de  trabalho submetida ao SGBD. Por outro lado, a remoção de um índice fragmentado para  sua posterior criação pode gerar um overhead desnecessário [Shasha e Bonnet 2003].     Este trabalho apresenta um mecanismo que possibilita automatizar a recriação de  índices  fragmentados  e  analisa  em  detalhes  uma  heurística  para  tal  automação.    A  abordagem  proposta  executa  independentemente  de  interações  com  os  DBAs.  Especificamente,  desenvolvemos  um  conjunto  de  heurísticas  que  executam  continuamente,  detectam  a  existência  de  índices  fragmentados,  e,  sempre  que  necessário,  reconstroem  estes  índices,  evitando  assim  os  malefícios  causados  pela  existência  de  índices  fragmentados.  A  arquitetura  concebida  neste  trabalho  foi  implementada em um SGBD de código aberto,  o PostgreSQL [Postgresql 2009]. Esta  implementação teve a sua eficácia comprovada através dos testes que foram realizados.    O restante deste artigo está organizado da seguinte maneira: A seção 2 apresenta  os trabalhos relacionados. A seção 3 discute o estudo realizado sobre a fragmentação de  índices  e  os  resultados  dos  testes  executados  que  comprovam  a  necessidade  da  preocupação com índices fragmentados. Na seção 4 descreve uma proposta de solução  para o problema da reconstrução automática de índices. Por fim, a seção 5 conclui este  trabalho e aponta direções para futuras pesquisas. 

2. Trabalhos Relacionados 

No  contexto  da  sintonia  de  índices,  foram  identificados  alguns  trabalhos  relacionados  que se propõe a realizar a sintonia automática de índices em SGBDs, buscando melhorar  o desempenho das execuções de consulta através de sugestões de criação ou remoção de  índices. 

  Em [Sattler et al 2003], [Sattler et al 2004] propõe­se um middleware, situado  entre  as  aplicações  e  o  SGBD  DB2,  que  sugere,  de  forma  automática,  a  criação  de  índices.  Esta  solução  baseia­se  em  comandos  proprietários  do  DB2  (Ex.:  SET  CURRENT  EXPLAIN  MODE  RECOMMEND  INDEXES),  os  quais  não  existem  em  outros SGBDs. Além disso, exige­se que todas as cláusulas SQL sejam enviadas para o  middleware e não mais para o SGBD, o que implica na necessidade de re­escrever as  aplicações  previamente  existentes  e  na  impossibilidade  de  se  gerenciar  uma  carga  de  trabalho que seja submetida diretamente ao SGBD. 

  Os trabalhos propostos em [Salles 2004], [Salles e Lifschitz 2005], [Costa 2005]  apresentam  um  componente  de  auto­sintonia  implementado  junto  ao  código  do  PostgreSQL,  que  possibilita  a  criação  automática  de  índices.    Utilizou­se  para  tanto  configurações de índices hipotéticos, índices existentes apenas na metabase, que podem  ser considerados para construção de planos de consulta alternativos.   

  Em [Schnaitter et al 2006] os autores apresentam um protótipo de um framework de  auto­sintonia  denominado  COLT  (Continuos  On­Line  Tuning),  o  qual  monitora  as  consultas submetidas ao SGBD e ajusta de forma automática a configuração de índices,  levando  em  consideração  a  restrição  do  espaço  disponível  para  estas  estruturas.  O  COLT foi implementado de forma integrada ao PostgreSQL. 

  Em [Luhring et al 2007] os autores apresentam uma abordagem para a sintonia  automática de índices que se baseia na utilização de índices hipotéticos e independe de  qualquer interação do DBA. Esta abordagem também foi implementada junto ao código  do PostgreSQL. 

  Em  [Bruno  e  Chaudhuri  2007a],  [Bruno  e  Chaudhuri  2007b]  os  autores  apresentam uma ferramenta de sintonia automática de índices implementada como uma  extensão do Microsoft SQL Server 2005. Este novo componente executa continuamente  e, reagindo a variações na carga de trabalho ou nas características dos dados, modifica  de  forma  automática  o  projeto  físico  do  banco  de  dados.  Os  algoritmos  propostos  apresentam  baixa  sobrecarga  e  levam  em  consideração  restrições  no  espaço  de  armazenamento,  o  custo  da  atualização  das  estruturas  de  índices  causadas  pelas  operações de update e o custo de criar estruturas físicas temporárias.

  Contudo, todos estes trabalhos não consideram a possível recriação automática  de índices fragmentados. Assim, a abordagem proposta neste artigo estende os trabalhos  anteriores realizando não somente a criação e a remoção, mas também a reconstrução  (reindex) automática de índices. 

Como  os  índices  considerados  neste  trabalho  obedecem  a  organizações  em  árvore B + (os ponteiros de dados encontram­se armazenados somente nos nós folhas da  árvore)  um  grande  número  de  inserções,  atualizações  ou  eliminações  de  chaves,  fatalmente  provocará  o  particionamento  de  páginas  de  disco  (page  splits  [Shasha  e  Bonnet  2003]).  Este  fato  pode  causar  fragmentação  na  estrutura,  dependendo  da  localização  de  onde  venham  a  ser  alocadas  novas  páginas.  A  Figura  1  apresenta  um  índice  que  não  sofreu  nenhum  particionamento  de  páginas  de  disco;  enquanto  que  a  Figura 2 exibe o mesmo índice após muitas atualizações.  

Figura 2. Índice fragmentado 

As setas apresentadas nas Figuras 1 e 2 revelam a ordem na qual o índice será  percorrido.  Deve­se  perceber  a  ausência  de  múltiplos  deslocamentos  a  posições  anteriores  em  um  índice  apresentando  grau  de  fragmentação  zero.  Já  um  índice  fragmentado  prejudica  as  operações  de  varredura,  já  que  o  artefato  físico  responsável  pela  leitura  pode  ser  obrigado  a  realizar  muitos  deslocamentos  físicos.  É  importante  lembrar que a preocupação com a fragmentação está relacionada à varredura do índice e  não aos acessos individuais as páginas em disco. 

Uma solução imediata para esse problema é a completa reconstrução periódica  de  todos  os  índices,  de  preferência  em  instantes  de  pouca  atividade.  Entretanto,  tal  abordagem  traria  dois  graves  inconvenientes:  1)  Os  grandes  bancos  de  dados  podem  possuir centenas ou mesmo milhares de índices; Reconstruir todos,  por mais “quieto�  que  esteja  o  Sistema,  causaria  um  esforço  computacional  considerável;  2)  Há  índices  cujas  tabelas  não  sofrem  atualizações  ou  são  raramente  atualizadas.  Nestes  casos,  a  reconstrução não traz benefício algum. 

Este  trabalho  apresenta  argumentos  que  atestam  a  necessidade  da  recriação  periódica  de  índices  fragmentados,  para  que  o  desempenho  no  processamento  de  consultas  sobre  as  tabelas  nas  quais  foram  criados  não  seja  prejudicado.  Além  disso,  propõe­se  uma  abordagem  para  a  identificação  e  reconstrução  automáticas  de  índices  fragmentados. 

3. Estudo da Fragmentação de Índices 

A existência de um índice fragmentado prejudica a execução de consultas que realizam  varreduras  (ordenação,  agrupamento,  filtros  como  intervalos)  e  apresentam  alta  seletividade (conjunto de linhas retornadas). Durante a realização dos testes práticos que  levaram à confecção deste trabalho, observou­se que o otimizador somente utiliza um  índice se a seletividade da consulta estiver abaixo de um determinado valor limítrofe.  Um índice fragmentado implica em maior consumo de espaço físico e maior duração na  execução  de  consultas  com  varreduras,  pois  quanto  maior  a  duração  de  execução  das  consultas com baixa velocidade no acesso (E/S) aos  dados, maior será o consumo de  recursos, aumentando assim o custo de processamento da consulta. 

Para comprovar estes fatos, realizou­se o seguinte experimento: a partir de uma  tabela  escolhida  aleatoriamente  e  um  determinado  índice  a  ela  associado,  elaborou­se  uma  rotina  de  carga  (com  inserções,  remoções  e  atualizações)  que  resultava  na  fragmentação  deste  índice.  Em  seguida,  estando  a  tabela  preenchida,  comparou­se  o  tempo de execução de uma consulta antes e depois da reconstrução do índice. Os dados  coletados  mostram  que  a  reconstrução  permite  melhoras  pontuais  em  dois  aspectos:  duração/custo  da  consulta  e  o  espaço  ocupado  pelo  índice.  A  seguir,  discutimos  em  detalhes o experimento realizado. 

  Considere  a  tabela Venda,  cujo  esquema  é  descrito  na  Figura  3,  inicialmente carregada com 400.000 tuplas. Elaborou­se uma rotina de carga de uma segunda tabela  denominada Venda2, a qual possui a mesma estrutura da tabela Venda, com o seguinte  roteiro:  Coluna   |     Tipo        ­­­­­­­­­+­­­­­­­­­­­­­­   num     | integer         prodnum | integer         data    | date        qtd     | integer         valor   | numeric(10,2) Figura 3. Estrutura da tabela Venda.  1. Estando a tabela Venda2 vazia;  2. Copiam­se 20 vezes as tuplas de Venda para a Venda2, porém alterando­ se  o  conteúdo  do  campo  num  (identificador  único  da  tupla). Cabe  observar que, inicialmente, Venda2 encontra­se vazia. A quantidade final  de tuplas em Venda2 será de 20 x 400.000 = 8.000.000. 

3. Cria­se  um  índice,  denominado Ind2,  sobre  o  campo  num  da  tabela Venda2.

4. Aumenta­se o volume da tabela Venda2 em 7 vezes, realizando inserções  entre  tuplas que já  existam. Ao final da última  etapa haverá cinqüenta  e  seis milhões de tuplas em Venda2. Como o índice é criado com a tabela já  contendo 8.000.000 de tuplas, a inserção de mais 48.000.000 de registros  causará  grande  fragmentação,  pois  haverá  grande  incidência  de  particionamento  de  páginas  de  disco.  Logo,  espera­se  que  consultas  que  realizem  operações  de  varredura  tenham,  conseqüentemente,  seu  desempenho prejudicado. 

3.1. Resultados dos testes experimentais 

Utilizando  a  tabela  Venda2,  realizamos  uma  bateria  de  testes  sobre  3  SGBDs  largamente utilizados no mercado: o PostgreSQL, Oracle 9i e MS SQL Server 2005.     Uma  das  consultas  testadas,  cujo  desempenho  é  prejudicado  devido  ao  estado  fragmentado do índice Ind2 é apresentada na Figura 4. Os valores foram efetivamente  utilizados como referência nos testes com os SGBDs considerados. 

  Para comprovarmos que a primeira consulta (executada antes da reconstrução do  índice Ind2)  foi  executada  sobre  uma  estrutura  de  índices  realmente  fragmentada,  utilizamos os comandos proprietários de cada SGBD utilizado para consultar o grau de  fragmentação do índice Ind2. Por exemplo, no SQL Server utilizou­se o comando DBCC  SHOWCONTIG. Neste  caso,  para  que  um  índice  apresente  uma  fragmentação  de  grau  zero,  a  medida Scan  Density  deve  apresentar  um  valor  próximo  a  100%.  Pode­se  observar através da Figura 5 que no teste realizado este valor equivale a 12,5%. 

  Através  deste  experimento  podemos  verificar  que  nos  três  SGBDs  utilizados  (PostgreSQL,  SQL  Server  e  Oracle),  executaram­se  comandos  que  mostraram 

resultados  numéricos  envolvendo  os  dois  fatores  afetados  pela  fragmentação:  espaço  ocupado  pelo  índice  e  duração/custo  da  consulta.  Além  disso,  podemos  observar  os  malefícios  da  fragmentação  de  índices.  Logicamente,  em  casos  de  volumes  de  dados  ainda maiores, estes problemas teriam um impacto ainda maior sobre o sistema.    Apesar de evidenciada pela diferença de tamanhos de um índice antes e depois  da reconstrução, os otimizadores ainda não possuem recursos para identificar situações  onde o índice apresente altos graus de fragmentação. O ideal é que um índice não seja  considerado pelo otimizador, caso exista uma distribuição não compacta de suas páginas  do nível folha.  select sum(valor) from venda2   where num between 5 and 64008   or num between 10000000 and 10064000  or num between 20000000 and 20064000  or num between 30000000 and 30064000  or num between 40000000 and 40064000  or num between 50000000 and 50064000  or num between 60000000 and 60064000 Figura 4. Exemplo de varredura sobre uma tabela com índice fragmentado  DBCC SHOWCONTIG scanning 'venda2' table...  Table: 'venda2' (277576027); index ID: 2, database ID: 8  LEAF level scan performed.  ­ Pages Scanned...: 173937  ­ Extents Scanned...: 21870  ­ Extent Switches...: 173936  ­ Avg. Pages per Extent...: 8.0  - Scan Density [Best Count:Actual Count]...: 12.50%  [21743:173937]  ­ Logical Scan Fragmentation ...: 99.22%  ­ Extent Scan Fragmentation ...: 0.41%  ­ Avg. Bytes Free per Page...: 1013.0  ­ Avg. Page Density (full)...: 87.48%  DBCC execution completed. If DBCC printed error messages, contact  your system administrator.  Figura 5. Evidência do estado de fragmentação de um índice no SQL Server.

  A Tabela 1 compara o espaço em disco ocupado pelo  índice Ind2, o custo da  consulta  apresentada  na  Figura  4  e  a  duração  da  mesma  consulta  em  dois  momentos  distintos: antes e depois da reconstrução do índiceInd2.

Tabela 1. Resultados PostgreSQL, SQL Server e Oracle. 

Resultados PostgreSQL 

Fator  Antes  Depois  Diferença 

Espaço  1.231,74 MB  957 MB  ­22,30% 

Duração  30s  28s  ­6,67% 

Custo  662.585 653.521 ­0,11% 

Resultados SQL Server 

Fator  Antes  Depois  Diferença 

Espaço  1.366,52 MB  1.193,01 MB  ­12,70% 

Duração  1.343 ms  1.126 ms  ­16,16% 

Custo  144.957 138,116 ­4,72% 

Resultados Oracle 

Fator  Antes  Depois  Diferença 

Espaço  1.152 MB  1.089 MB  ­5,47% 

Duração  4s  3s  ­25% 

Custo  8.071 8.060 ­0,14% 

4.  Solução por Auto­sintonia Global 

A  proposta  de  solução  para  evitar  a  existência  de  índices  fragmentados  utiliza  uma  heurística que possui dois pilares: enumeração de índices candidatos e acompanhamento  de índices (hipotéticos ou não), mantendo­lhes uma determinada carga de benefícios (ou  malefícios), calculada a partir de participações em comandos. 

Na  estratégia  utilizada  neste  trabalho,  uma  vez  detectado  que  um  determinado  índice  apresenta  um  benefício  acumulado  negativo,  cujo  módulo  supere  o  custo  de  criação  do  índice,  dispara­se  a  eliminação  do  mesmo.  Ocorre,  entretanto,  que  muitas  vezes  um  índice  poderia  continuar  sendo  útil,  uma  vez  que  este  fosse  reconstruído,  como no caso do índice estar fragmentado. Neste caso, o índice poderia ter acumulado  um benefício negativo pelo fato de estar fragmentado e não pelo fato de não estar sendo  utilizado  pelas  consultas  executadas  (ou  seja,  que  compõem  a  carga  de  trabalho  submetida ao SGBD).  

Desta  forma,  a  Heurística  de  Reconstrução  Automática  de  Índices  (HRAI),  proposta neste artigo, analisa casos de eliminação iminente e, caso julgue conveniente,  dispara  o  reindex,  isto  é,  executa  a  reconstrução  automática  do  índice.  A  HRAI  concebida estabelece regras que permitem decidir se um índice deve ser reconstruído ou  eliminado.  A  decisão  quanto  à  recriação  de  um  determinado  índice  deve  analisar  três 

fatores:  (i)  grau  de  fragmentação,  (ii)  o  tamanho  do  índice  e  (iii)  a  quantidade  de  varreduras (scans) executadas sobre o índice. 

No  caso  do  grau  de  fragmentação  de  um  índice,  este  pode  ser  estimado  comparando­se  a  razão  em  dois  momentos:  logo  após  a  criação,  quando  não  existe  fragmentação, e quando se deseja verificar se é relevante aplicar uma reconstrução, ou  simplesmente seu descarte. A razão é calculada da seguinte forma:   R = tuplas de uma tabela / quantidade de blocos do �ndice  Assim, propõe­se a seguinte fórmula para cálculo do grau de fragmentação de  um índice (Figura 6):  GrF = 100 ­ [ (Ra / Ri) * 100]  Figura 6. Fórmula que obtém o grau de fragmentação de um índice 

Onde  Ra  representaria  a  razão  atual  e  Ri  a  razão  inicial,  gerada  logo  após  a 

criação do índice.  

Por  exemplo,  suponha  que  se  identificou  um  determinado  índice, ir1,  criado  sobre  a  tabela Venda  (Figura  3)  com  879  blocos  comportando  400.000  tuplas  (Ri = 

455,06).  Após  várias  operações,  a  quantidade  de  blocos  aumentou  para  2.442  em  450.000 tuplas (Ra = 184,27). Desta forma, calcula­se GrF como 59,50% 

No que diz respeito ao fator tamanho, é fato que tabelas ocupando poucos blocos  não deveriam ter seus índices cogitados à reconstrução. Para exemplificar, suponhamos  que  a  tabela T1  tenha  1.000  tuplas  e  um  índice  com  apenas  dois  blocos  plenamente  ocupados (razão tuplas/blocos = 500). Quando ocorrer a inserção da milésima primeira  linha,  o  índice  ganhará  um  terceiro  bloco,  porém  sua  razão  cairá  para  333  (um  decréscimo  de  33,4%).  Apesar  de  apresentar  uma  considerável  queda  em  sua  razão  tuplas/blocos, a reconstrução do índice não proporcionaria uma ocupação com menos de  três blocos, ou seja, a reconstrução não traria benefícios quanto à diminuição do número  de blocos ocupados pelo índice. 

Por  fim,  deve­se  avaliar  a  quantidade  de  varreduras  realizadas  sobre  o  índice,  pois, consultas que executam operações de varredura sobre um determinado índice terão  seu  desempenho  degradado  caso  o  índice  utilizado  encontre­se  fragmentado.  Além  disso, o fato de um índice ter sido útil em uma consulta onde houve varreduras já faz  com que seja um candidato à reconstrução. 

4.1. Fator de Preenchimento 

Uma vez constatada a fragmentação de um índice, causada por sucessivas ocorrências  de particionamentos de páginas de disco, caso decida­se recriá­lo, recomenda­se fazê­lo  deixando  uma  margem  para  futuras  atualizações.  Normalmente,  SGBDs  possuem  mecanismos que permitem dosar quantos bytes podem ser gravados por bloco.  A heurística HRAI determina um fator de preenchimento de páginas com base  no histórico de operações de varreduras nas quais participou de forma positiva o índice  em vias de reconstrução. Como se utilizou PostgreSQL para realizar as implementações,  houve a necessidade de estender os comandos create index, reindex index e reindex  table para que aceitassem uma nova cláusula, fillfactor, valendo entre 1 e 9. O menor 

valor significa que apenas um décimo de cada página será sendo ocupada, enquanto que  o maior sinaliza 90% de ocupação. 

4.2. GETSIZE 

Um  índice  cuja  razão  blocos_índice/tuplas_tabela  (R)  apresente  valores  distantes  daqueles  medidos  imediatamente  após  sua  criação,  pode  ser  considerado  como  fragmentado.  Por  exemplo,  dada  uma  tabela  denominada  Venda  e  um  índice  denominado ix_num definido sobre o atributo num da tabela Venda, pode­se descobrir  as quantidades de blocos e tuplas, consultando­se a metabase do SGBD.  

A Figura 7 mostra um comando de consulta à metabase do PostgreSQL, o qual  revela  a  quantidades  de  páginas  e  tuplas  da  tabela Venda.  Vale  ressaltar  que  assim  como  em  qualquer  SGBD,  a  confiabilidade  dos  valores  da  metabase  depende  da  atualização periódica das estatísticas.  select relname, reltuples, relpages from pg_class   where relname in ('ix_num','venda') ;  relname | reltuples | relpages  ­­­­­­­­­+­­­­­­­­­­­+­­­­­­­­­­   ix_num  |    400000 |      879   venda   |    400000 |     3147 Figura 7. Investigação do tamanho de uma tabela em PostgreSQL. 

  As  decisões  quanto  às  utilizações  de  índices  realizadas  pelo  otimizador  de  qualquer SGBD são baseadas nas informações que constam na metabase. Caso esta não  seja atualizada periodicamente, corre­se o  risco  de acontecerem  otimizações errôneas.  Para  evitar  este  problema,  a  implementação  da  HRAI  precisou  obter  as  quantidades  reais de tuplas e páginas de tabelas e índices, por meios próprios.

Uma forma  alternativa de comprovar a quantidade real de páginas  e  tuplas de  uma  tabela  no  PostgreSQL  consiste  em  utilizar uma  ferramenta  opcional  denominada  pgstattable. Trata­se de uma função que checa todas as páginas da tabela, informando  quantidades reais. A Figura 8 mostra um exemplo de chamada à função e seu resultado.  select tuple_count, table_number_blocks from pgstattable  ('venda');  tuple_count  | table_number_blocks   ­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­+­­­        400000 |      3147   Figura 8. Exemplo de utilização da função pgstattable. 

Como  não  se  informam  dados  sobre  índices,  foi  necessário,  com  base  em  pgstattable,  desenvolver  uma  nova  função  para  o  PostgreSQL,  denominada  pgstatindex, que também checa todas as páginas de um dado índice. A Figura 9 mostra  um exemplo de chamada à função e seu resultado. 

select tuple_count, table_number_blocks, index_len,  index_number_blocks, index_tuple_count from pgstatindex ('venda', 'ix_num');  tuple_count|table_number_blocks|index_len|index_number_blocks|index_tuple_coun t ­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­ ­ 400000   |       3147   |17178624 |        2097   |    800000 Figura 9. Exemplo de utilização da função pgstatindex.  A nova função, pgstatindex, serviu de inspiração para o desenvolvimento de um  novo comando para o PostgreSQL, denominado getsize. Dado um nome de um índice, o  comando checa todas as tuplas da tabela correspondente e conta as páginas do índice.  Estes dois dados permitirão calcular as duas razõesRi (logo após sua criação) e Ra (uma 

vez detectada a possibilidade de eliminá­lo). Com as informações retornadas por getsize  pode­se derivar dois dos três fatores necessários à decisão de reconstruir, ou não, um  índice.  Fica  faltando  apenas  a  quantidade  de  varreduras  já  executadas  sobre  o  índice,  valor que será informado pelo Agente de Benefícios, como detalhado na próxima seção.  4.3. Heurística de Reconstrução Automática de Índices 

Diante da carência de recomendações quanto à reconstrução de índices fragmentados,  foi necessário desenvolver uma heurística própria que fosse capaz de reconstruir índices  fragmentados.  Assim  como  em  [Salles  2004],  a  heurística  será  executada  simultaneamente  com  outros  serviços  providos  pelo  SGBD,  fazendo  com  que  a  recriação seja realizada de forma automática e online. Para apresentar a heurística, devem­se definir cinco fatores:  • Tt: tamanho em tuplas de uma determinada tabela.  • Ti: tamanho em blocos de um determinado índice.  • R: razão entre Tt e Ti. • GrF: grau de fragmentação de um determinado índice, obtido através da  fórmula descrita na Figura 6.   • V: número de varreduras nas quais o índice participou desde sua criação.  A heurística é composta pelas funcionalidades:  i. Novo Índice: deve­se armazenar o nome de um índice recém criado; 

ii. Solicitação  de  Tamanho:  solicita­se  ao  SGBD  Ti  e  Tt  referentes  a  um 

determinado índice; 

iii. Cálculo de Razão: obtém­se R com base em Ti e Tt.

v. Cálculo de Grau de Fragmentação: obtém­se GrF com base em Ri e Ra; 

vi. Reconstrução: caso GrF, V e Ti atendam a determinados limites (thresholds), 

efetua­se a reconstrução. 

A  solicitação  de  tamanho  (segunda  funcionalidade)  e  o  cálculo  da  razão  (terceira)  ocorrem  para  cada  índice  armazenado  em  dois  momentos:  (1)  logo  após  a  criação (Ri) e (2) previamente à decisão de reconstruir ou não (Ra). A solicitação tem 

por conseqüência a execução do comando getsize pelo SGBD. 

Antes da reconstrução, deve­se calcular o novo fator de preenchimento segundo  a  equação  apresentada  na  Figura  10.  Quanto  mais  varreduras  um  índice  fragmentado  tiver  tido,  menor  será  o  seu  fillfactor  objetivando  reduzir  a  incidência  de  particionamento  de  páginas  de  disco.  Entretanto,  deve­se  ressaltar  que,  ao  reduzir  o  fillfactor, diminui­se a quantidade de informações por bloco, levando à necessidade de  maior alocação de páginas.  F = 10 ­ [ (V + 10) div 10]  Figura 10. Equação para obtenção do fator de preenchimento.  Por exemplo, supondo que um determinado índice tenha sofrido 34 varreduras,  seu novo fator de preenchimento é:  F = 10 – [ (34 + 10) div 10]  F = 10 – (44 div 10)  F = 6  Portanto, o índice terá suas páginas em nível folha preenchidas até 60%. 

Esta  heurística  possui  uma  limitação  que  poderia  ser  otimizada  para  que  ela  decida por uma reconstrução do índice antes de ele  ser eliminado, ou seja, não sendo  necessário  manter  um  índice  fragmentado  até  a  decisão  de  eliminá­lo  ou  recriá­lo,  usando informações da metabase sobre o particionamento de páginas. 

4.4. Estrutura Funcional  

O  componente  de  software  responsável  pela  implementação  da  heurística  HRAI  denomina­se Agente Desfragmentador. Sua construção seguiu a mesma linha de estudos  utilizada em [Salles 2004] e [Milanés 2004], onde foram adaptadas propostas presentes  em [Kendall 1999].   

O  Agente  Desfragmentador  interage  com  o  Agente  de  Benefícios,  cuja  implementação estendeu o trabalho proposto em [Salles 2004]. Enquanto o Agente de  Benefícios  cria  e  destrói  índices,  o  Agente  Desfragmentador  preocupa­se  com  as  reconstruções.  

4.5. Implementação no PostgreSQL 

Como não foi possível determinar valores ótimos para os fatores GrF, V e Ti, preferiu­se 

deixá­los como parâmetros de configuração para serem ajustados livremente pelo DBA.   No  caso  específico  do  PostgreSQL,  deve­se  alterar  o  arquivo  de  inicialização  denominado  postgresql.conf.  Nele  aparecem  as  configurações  necessárias  ao 

funcionamento do PostgreSQL, tais como área em memória destinada para dados, nível  de  depuração  do  ambiente,  padrões  de  comunicação  entre  clientes  e  servidor,  entre  outros.  

O  parâmetro  default_fillfactor  pode  ser  alterado  a  qualquer  momento.  Por  exemplo, após emitir o comando:  

Set default_fillfactor = 7; 

Todo  índice  criado  (create  index)  ou  recriado  (reindex  index)  que  não  utilize  explicitamente a cláusula fillfactor, assumirá 70% de preenchimento de páginas. 

4.6. Estrutura dos Testes Experimentais 

A  bateria  de  testes  seguiu  utilizando  como  referência  uma  única  tabela  da  base  de  dados. A tabela Venda com 400.000 tuplas. Procura­se observar o comportamento dos  dois agentes, bem como a criação, destruição e recriação de índices. 

Durante os testes utilizou­se o comando evaluate, cuja sintaxe aparece a seguir:  evaluate <complemento>

O  complemento,  que  pode  ser  qualquer  uma  das  seguintes  operações:  select,  update, delete ou insert, é repassado ao Agente de Benefícios. Este comando permite o  repasse  de  uma  operação  ao  agente  de  forma  a  eliminar  a  fase  de  execução,  sendo  possível a avaliação de todas as operações sem as etapas mais custosas; assim, por conta  da utilização do comando evaluate, se ganha em agilidade, já que não se perde tempo  com longas execuções. 

A  Tabela  2  revela  uma  seqüência  de  comandos  onde  verificam­se  todas  as  interações possíveis entre os agentes. 

Tabela 2. Resumo da bateria de testes  Instante 1 (8 execuç�es)

Comando evaluate select prodnum, valor, data, qtd  from venda where num = 10000; Status  do 

Sistema: Criado índice ri_venda_0 com benefícios acumulados de 65.151,9 e Bônus (benefício acumulado dividido pela quantidade de vezes que o  índice hipotético teria sido utilizado, caso fosse um índice real) igual a  8.143,99. Durante as sete execuções prévias, existiu um índice hipotético  denominado hi_venda_0.

Instante 2 (1 execução)

Comando  evaluate select prodnum, valor, data, qtd  from venda  where num=10000;

Status  do 

Sistema: Agente de Benefícios (AB) informa Agente Desfragmentador (AD) que um índice criado foi usado pela primeira vez; AD dispara getsize; Bi = 

879 Tt = 400.000 

Instante 3 (12 execuç�es)

Status  do 

Sistema: Como trata­se de uma consulta com varreduras, AB atualiza o atributo scans de ri_venda_0 para 12.  Instante 4 (1 execução)

Comando  update venda set num = num + 800000; Status  do 

Sistema: Como trata­se de uma atualização, AB decrementa os benefícios acumulados de ri_venda_0. Valor corrente: 160.730  Instante 5 (21 execuç�es)

Comando  evaluate update venda set num = num + 800000; Status  do 

Sistema: Benefícios acumulados de Eliminação: ­65.151,90  ri_venda_0: ­55.444,30. Custo de  Instante 6 (1 execução)

Comando  evaluate update venda set num = num + 800000; 

Mesmo  que  ocorra  um  comando  que  afete  negativamente  o  índice,  o  Agente de Benefícios somente o destruirá uma vez recebida a liberação. Status  do 

Sistema: AB notifica AD sobre a intenção de eliminar um índice; AB informa Varreduras a AD (12). AD dispara getsize; AD notifica AB sobre a  proibição de destruir um índice; Ba = 2.097   Ta = 400.000. Ri = 455,06   Ra = 190,74;   GrF = 58,08  Instante 7 Comando Reconstruído índice ri_venda_0 com fillfactor 8 (Figura 10) Status  do  Sistema: AD notifica AB que um determinado índice foi reconstruído. Instante 8 (1 execução)

Comando  Select table_number_blocks, index_len, index_number_blocks from pgstatindex ('venda', 'ri_venda_0'); Status  do 

Sistema: Informada a nova quantidade de páginas de ri_venda_0 (991), um pouco superior às 879 iniciais, devido ao fillfactor menor (8 ao invés de 9).  Instante 9 (1 execução)

Comando  evaluate select prodnum, valor, data, qtd from venda where num between 10000 and 10020; Status  do 

Sistema: Benefícios acumulados de ri_venda_0: 72.295,90  Instante 10 (9 execuç�es) Comando  evaluate update venda  set num = num + 800000;

Status  do 

Sistema: Benefícios acumulados de ­65.151,90. AB notifica AD sobre a intenção de eliminar um índice; AB ri_venda_0: ­92.524,30 Custo de Eliminação:  informa Varreduras a AD (12). AD dispara getsize; AD notifica AB  sobre a proibição de destruir um índice; Bi = 991   Ti = 400.000. Ri = 455,06   Ra = 403,63;   GrF = 11,03  Instante 11 Comando Índice ri_venda_0 destruído; Status  do 

Sistema: Como Gum determinado índice pode ser destruído. AB cria Índice hipotético rF foi inferior ao parâmetro dia_ratio(50), AD notifica AB que  hi_venda_0 com benefícios acumulados iguais a 92.524,10 

A  realização  da  bateria  de  testes  resumida  na  Tabela  2  permite  afirmar  com  segurança  que  a  implementação  da  heurística  obteve  sucesso.  Devido  à  limitações  de  espaço,  deixamos  para  explorar  em  trabalhos  futuros  situações  mais  complexas,  tais  como cargas de trabalho derivadas do benchmark TPC­C para confirmar a eficácia da  HRAI. 

5. Conclus�es 

Neste trabalho, apresentamos um mecanismo que possibilita automatizar a recriação de  índices fragmentados. A abordagem proposta executa de forma contínua e automática,  ou  seja,  independentemente  de  interações  com  seres  humanos.  Especificamente,  desenvolvemos  um  conjunto  de  heurísticas  que  executam  continuamente,  detectam  a  existência de índices fragmentados, e, sempre que necessário, reconstroem estes índices,  evitando  assim  os  malefícios  causados  pela  existência  de  índices  fragmentados.  A  arquitetura concebida neste trabalho foi implementada em um SGBD de código aberto,  o PostgreSQL.  

Como  próximos  passos  pretende­se  avaliar  a  possibilidade  de  pré­criação  de  índices envolvidos com  chaves primárias e  estrangeiras.  Além disso, a  HRAI poderia  decidir a reconstrução antes da eliminação iminente, isto é, não seria necessário manter  um índice fragmentado até o momento em que se decida eliminá­lo ou recriá­lo. Uma  opção interessante seria criar um conjunto de alertas nos quais o Agente poderia emitir  avisos  informando  que  um  determinado  índice  atingiu  níveis  preocupantes  de  fragmentação. Estes níveis poderiam ser pré­configurados utilizando thresholds.

Referências 

Bruno,  N.  e  Chaudhuri,  S.  (2005)  “Automatic  physical  database  tuning:  a  relaxation­ based  approach�,  In:  Proceedings  of  the  2005  ACM  SIGMOD  International  Conference on Management of Data,227­238, Baltimore.

Bruno, N. e Chaudhuri, S. (2007a) “An Online Approach to Physical Design Tuning�,  In: Proceedings of the ICDE Conference (ICDE07).  

Bruno,  N.  e  Chaudhuri,  S.  (2007b)  “Online  autoadmin:  (physical  design  tuning)�,  In:  Proceedings of the 2007 ACM SIGMOD International Conference on Management  of Data (SIGMOD07), 1067­1069, Beijing, China. 

Costa, R. L. C., Lifschitz, S., Noronha, M. e Salles, M. V. (2005) “Implementation of an  Agent  Architecture  for  Automated  Index  Tuning�,  In:  Proceedings  of  the  ICDE  Workshops. 

Kendall,  E.,  Krishna,  P.,  Pathak,  C.  e  Suresh,  C.  (1999)  “A  Framework  for  Agent  Systems�, In: Implementing Application Frameworks ­ Object­Oriented Frameworks  at Work, 113­154. 

Lifschitz,  S.  e  Morelli,  E.  T.  (2006)  “Towards  Autonomic  Index  Maintenance�,  In:  Proceedings of the Brazilian Symposium on Database.

Luhring, M., Sattler, K., Schmidt, K. e Schallehn, E. (2007) “Autonomous Management  of Soft Indexes�, In: Proceedings of the 23rd IEEE International Conference on Data  Engineering Workshop (ICDE'07). 

Milanés,  A.  (2004)  “Uma  arquitetura  para  auto­sintonia  global  de  SGBDs  usando  agentes�,  Dissertação  de  mestrado,  Departamento  de  Informática,  Pontifícia  Universidade Católica do Rio de Janeiro (PUC­Rio). 

Postgresql.  (2009)  “PostgreSQL:  The  world’s  most  advanced  open  source  database�,  disponível em: http://www.postgresql.org, acessado em maio de 2009.  Salles, M. V. (2004) “Criação Autônoma de Índices em Bancos de Dados�, Dissertação  de mestrado, Departamento de Informática, Pontifícia Universidade Católica do Rio  de Janeiro (PUC­Rio).   Salles, M. V. e Lifschitz, S. (2005) “Autonomic Index Management�, In: Proceedings of  the International Conference on Autonomic Computing (ICAC). 

Sattler,  K.,  Geist,  I.  e  Schallehn,  E.  (2003)  “QUIET:  Continuous  query­driven  index  tuning�,  In:  Proceedings  of  the  29th  international  conference  on  Very  large  data  bases (VLDB03), 1129­1132, Berlin, Germany.  

Sattler,  K.,  Schallehn,  E.,  Geist,  I.  (2004)  “Autonomous  Query­Driven  Index  Tuning�,In: Proceedings of the International Database Engineering and Applications  Symposium (IDEAS'04), 439­448, IEEE Computer Society. 

Schnaitter, K., Abiteboul, S., Milo, T. e Polyzotis,  N.  (2006)  “COLT: continuous on­ line  tuning�,  In:  Proceedings  of  the  ACM  SIGMOD  International  Conference  on  Management of Data (SIGMOD06), 793­795, Chicago, IL, USA. 

Shasha, D., Bonnet, P. (2003) “Database Tuning�, Morgan Kaufmann Publishers.   Zilio, D., Rao, J., Lightstone, S., Lohman, G., Storm, A., Garcia­Arellano, C. e Fadden, 

S.  (2004)  “Db2  Design  Advisor:  Integrated  Automatic  Physical  Database  Design�,  In: Proceedings of the International Conference on Very Large Databases (VLDB),  1087­1097.