M�todos de Acesso e Planos de Execu��o

Document Sample
M�todos de Acesso e Planos de Execu��o Powered By Docstoc
					Auto-sintonia de Índices Completa:
      create, drop e reindex
   automáticos no PostgreSQL

           Prof. Sérgio Lifschitz
         Depto Informática PUC-Rio
           sergio@inf.puc-rio.br

                ERBD 2008
             Florianópolis (SC)
                                     1
                  Contexto VLDB
• Vários GB de dados
  – terabytes ou terrorbytes
  – cartões de crédito, correios expressos
• Caso Petrobras coorporativo anos atrás
  –   5K tabelas e índices + 2K views
  –   Algumas tabelas com mais de 50M tuplas
  –   20 consultas por minuto
  –   12K usuários (200 simultâneos)
• SAP R/3 “básico”
  – 16K tabelas e 19K índices!

                                               2
      Sintonia de Bancos de Dados

• Tuning - Sintonia ou ajuste fino
  “Realizar ajustes em um sistemas de banco
  de dados de forma a obter um melhor tempo
  de resposta e/ou aumentar a vazão
  (throughput) para determinada aplicação”

    Buscar um bom desempenho em um
    sistema de banco de dados existente

         Hipótese: HW e SW não mudam!

                                              3
      Afinal, sintonia fina de quê?

• Seleção de índices
• Alocação de dados
• Controle de carga (ajuste de MPL)
• Política de substituição de páginas em
  memória
• Ajuste de tamanhos/quantidades de buffers
• Refino automático de estatísticas



                                              4
            Atividade de Sintonia

• Perceber que um recurso está sendo mal
  utilizado
  – monitoramento é parte fundamental do processo
• Localizar e entender a verdadeira fonte do
  problema
  – Mais de 90% do tempo para resolução de
    problemas de desempenho é gasto no
    diagnóstico.


                                                    5
               Problema típico

  (1)insert into SALES (prodNum, date, qty, value)
      values (4, current_timestamp, 20, 348);

  (2)select prodNum, date, sum(value) as total
      from   SALES
      where value > 1500000 and
             date between ‘20040101’ and ‘20040131’
      group by prodNum, date;




Indices para uma aplicação que contém
estas (e possivelmente muitas outras…) cláusulas SQL?

                                                        6
          Problema simples?


 update venda
 set valor = valor - 1
 where valor > 2375000;




Índices ajudam ou atrapalham?

                                7
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   8
             O que é preciso saber?

A atividade de sintonia fina envolve:
  –   Hardware e sistemas operacionais
  –   Gerência de memória e acesso a discos
  –   Controle de concorrência e recuperação
  –   Uso de índices adequados
  –   Otimização e reescrita de consultas
  –   Projeto de banco de dados adequado


Ajuda bastante conhecer SGBDs específicos!
                                               9
           Arquitetura Funcional
               Componentes de um SGBD
                                                Controle de
                                                Concorrência
       Processador de Consultas                   Gerência de
                                                   Bloqueios
SQL       Otimizador              Executor
                                                  Gerência de
                                                  Transação e
                                                  Recuperação

      Gerente de Armazenamento

                          Controle de Memória


                           Controle de Dados

         Meta-Dados e             Dados e             Log de
          Estatísticas             Índices          Transações
                                                                 10
       Processamento de Consultas

Parse Query

              Check de
              Semântica

                         Query Rewrite


                                   Otimização do
                                   Plano de Acesso

                                                Geração de
                                                Código       11
        Otimização de Consultas

• Problema NP-difícil:
  – muitas alternativas de planos
• O otimizador de consultas determina o plano de
  acesso através de:
  – Heurísticas
     • otimização por regras, RBO
  – Busca de plano de melhor custo
     • otimização por custo, CBO



                                                   12
         Planos de Execução
• É o resultado da otimização
• É especificado no plano de execução:
  – Ordem de acesso às tabelas
  – Ordem de operações
       • seleção, projeção e junção
  –   Índices utilizados
  –   Tipos de junção
  –   Ordenações
  –   Tabelas intermediárias



                                         13
            Métodos de Acesso

• Tipos básicos de operação:
  – Varreduras seqüenciais (full scan)
  – Indexadas (index scan)
• Implementação de operadores
    • Junções,
    • Uniões
    • Ordenações e eliminação de duplicatas




                                              14
          QEP: Query Execution Plan

    • Exemplo:

      SELECT     endereço, data-nascimento
      FROM       empregado
      WHERE      nome = ‘Guga Kuerten’

Execution Plan
---------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPREGADO'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)


                                                            15
             Acesso por Índices

• Estruturas auxiliares para permitir acesso
  mais rápido dados
  – Sem índices, para obter uma informação de uma
    tabela, todas as linhas devem ser lidas do
    arquivo
  – Com índice, pode ser feito acesso direto


          Índices em livros permitem
          que passemos diretamente
          para o capítulo desejado!
                                                    16
                 Tipos de Índices

• Podem ser de diversos tipos:
  –   Árvore B+
  –   Cluster ou não-cluster
  –   Bitmap
  –   ...


• Alguns SGBDs permitem:
  – índices com valores em ordem reversa
  – índices em resultados de funções

                                           17
              Caso Árvores B+


                            43             95   




           23             37              58         75   




03-22       23-35           37-42            43-54        58-74    75 -


                                                                          18
      Características de Árvores B

• Manutenção:
  – N chaves, m ponteiros por página (ordem)
  – número máximo de níveis d (pior caso):

           ☹ d  1  log m / 2   N  1 / 2 

   – N = 1.000.000             N = 10.000.000
   – m = 512                   m = 512
   – d  3.37  3 níveis       d  3.78  3 níveis




                                                     19
Índices Clusterizados




                        20
Índices Não-clusterizados




                            21
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   22
          Autonomic Computing

• Um grande desafio para a comunidade
  acadêmica e indústria
  – tornar os sistemas computacionais mais
   autônomos
• Visões de futuro
  – Asilomar Report on Database Research (1998)
  – IBM’s Autonomic Computing Manifesto (2001)



                                                  23
     Auto-Sintonia (Self-Tuning)

• Capacidade de auto-ajuste dos SGBDs ao
  ambiente existente para obtenção de melhor
  desempenho
• Alguns SGBDs comerciais já oferecem versões
  com algumas características automáticas
• Trabalhos científicos:
  – muitos artigos sendo publicados nos últimos 10 anos!



                                                           24
                Trabalhos Correlatos
 Projeto SMART (Self Managing and Resource Tuning) do Centro de
  Pesquisas IBM Almaden em parceria com os Laboratórios de Toronto e
  do Vale do Silício;     DB2: db2advis
                                               SQL Server
 Projeto AutoAdmin da Microsoft Research;     2005: Database
                                               Tuning Advisor
            Oracle 10g: Automatic
 Oracle;   Database Diagnostic Monitor
                                             Índices
 PostgreSQL;                                Hipotéticos
         Pg_autovacuum                       Survey

 Grupo de auto-sintonia em SGBDs            Auto-Sintonia
  do Departamento de Informática PUC-Rio.    Global
                                             Heurística de
                                             Benefícios 25/22
                                                                       25
Caso SQL Server




                  26
                     Possível Classificação

                                                Auto-sintonia de
                                                bancos de dados




                        Auto-sintonia                                Auto-sintonia local
                           global




Auto-sintonia global Auto-sintonia global   Projeto   Alocação     Controle   Substituição   Ajuste de    Refino de
  por construção       por adaptação        Físico    de dados     de carga   de páginas      buffers    estatísticas




                                                                                                                   27
            Self-Tuning de índices

• Sintonia fina de índices:
  – Índices podem auxiliar em consultas
  – Índices podem ser prejudiciais a atualizações
  – Quais índices criar?

• Dificuldades adicionais:
     • Quando criar ou destruir?
     • Risco de criar-recriar inúmeras vezes



                                                    28
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   29
       Ambiente de Implementação

• Uso de SGBD completo de código aberto
  • Simulação? Não!
  • PostgreSQL (por ora, v. 7.4 beta 3)
  • Linux
• Abordagem intrusiva
  • Código core modificado
  • Comando create hypothetical index




                                          30
                  Uso de Índices Hipotéticos

                Tutorial: estudo de caso para what-if



         Department                   Employee                 Product                      Sale

     id – int              id – int                 id – int                     id – int
     name – varchar(50)    name – varchar(50)       type – varchar(30)           year – int
     managerid – int       address – varchar(200)   description – varchar(150)   month – int
                           salary – numeric(10,2)   measure – varchar(30)        day – int
   Number of tuples: 200   depid – integer          price – numeric(5,2)         prodid – int
                                                                                 sellerid – int
                           Number of tupes: 100     Number of tuples: 250
                                                                                 Price – numeric(4,2)
                                                                                 Number of tuples: 250
Actual indexes are created for all of the tables’ primary keys.
Scripts to create the university database can be found here.

                                                                                                         31
                   Consultas Frequentes

The following query is very frequently issued by the university application:

       select d.name, e.name, e.salary
       from   employee e, department d
       where e.depid = d.id and
              e.salary between 1000 and 2500;


 Lets take a look at its query execution plan using the explain statement:



            explain
            select d.name, e.name, e.salary
            from    employee e, department d
            where e.depid = d.id and
                    e.salary between 1000 and 2500;


                                                                               32
                  Plano de Consulta

 Query Execution Plan




QUERY PLAN
---------------------------------------------------------------------------
Hash Join       (cost=1.32..314.96 rows=2499 width=50)
  Hash Cond: ("outer".depid = "inner".id)
  ->   Seq Scan on employee e     (cost=0.00..269.91 rows=2498 width=42)
           Filter: ((salary >= 1000::numeric) AND (salary <= 2500::numeric))
  ->   Hash     (cost=1.26..1.26 rows=26 width=16)
           ->   Seq Scan on department d   (cost=0.00..1.26 rows=26 width=16)
(6 rows)




  A sequential scan was chosen by the planner to access the
  employee table. Perhaps we could improve this by creating an
  index on the salary column.


                                                                                33
Criando Índices Hipotéticos

Although we could benefit from the existence of an index on the salary column, we
should be careful to create it. Firstly, we do not know if the DBMS will actually choose to
use an index in the salary column if it exists. Secondly, if we try to create an actual index
in this column, the DBMS will prevent writers from accessing the table. So it is hard to
experiment with new indexes and evaluate how good they are.
Instead of incurring the burden of creating an actual index on the column, we could
simulate if this index would be useful to the database. To do that, we create it as a
hypothetical index:




  create hypothetical index hi_employee_salary
  on employee(salary);




                                                                                                34
      Planos de Consultas com
         Índices Hipotéticos

The hypothetical index is not actually materialized in the database. Therefore, we will not
incur in heavy creation costs or obtain locks on the underlying table to create it. The
DBMS, however, cannot use the hypothetical index to answer a user query. If we query
the database again or use the explain statement, the system will still use a sequential
scan to access the employee table.

We can see how the DBMS would behave if the hypothetical index were materialized
using the explain hypothetical statement:



    explain       hypothetical
    select        d.name, e.name, e.salary
    from          employee e, department d
    where         e.depid = d.id and
                  e.salary between 1000 and 2500;


                                                                                              35
Análise de Custos na presença
    de Índices Hipotéticos
    Query Execution Plan


 QUERY PLAN
------------------------------------------------------------------------
Hash Join     (cost=1.32..169.42 rows=2499 width=50)
   Hash Cond: ("outer".depid = "inner".id)
   ->    Index Scan using hi_employee_salary on employee e
         (cost=0.00..124.37 rows=2498 width=42)
           Index Cond: ((salary >= 1000::numeric) AND (salary <= 2500::numeric))
   ->    Hash   (cost=1.26..1.26 rows=26 width=16)
           ->   Seq Scan on department d   (cost=0.00..1.26 rows=26 width=16)
(6 rows)00



        If the index hi_employee_salary was materialized, the DBMS
        would use it to process the query. The estimated cost to
        process the query would drop from 314.96 using the sequential
        scan to 169.42 using the index scan.


                                                                                   36
Índices Hipotéticos eventually
       se tornam reais


Now that we know that the index is beneficial to performance, we can
drop the hypothetical index and create a corresponding actual one:


  drop hypothetical index hi_employee_salary;

  create index i_employee_salary on
  employee(salary);




                                                                       37
          Plano de consulta com
            novo índice criado
Lets check the query execution plan for the query with the actual index created:




    explain select
           d.name, e.name, e.salary
    from   employee e, department d
    where e.depid = d.id and
           e.salary between 1000 and 2500;




                                                                                   38
                     Análise dos custos

     Query Execution Plan


   QUERY PLAN
  ------------------------------------------------------------------------
   Hash Join      (cost=1.32..139.15 rows=2491 width=50)
     Hash Cond: ("outer".depid = "inner".id)
     ->   Index Scan using i_employee_salary on employee e
          (cost=0.00..94.24 rows=2490 width=42)
             Index Cond: ((salary >= 1000::numeric) AND
             (salary <= 2500::numeric))
     ->   Hash    (cost=1.26..1.26 rows=26 width=16)
             ->   Seq Scan on department d   (cost=0.00..1.26 rows=26
                  width=16)
  (6 rows)




The cost estimated by the planner for the query using the hypothetical
index was 169.42. With the actual index, the planner gave us an
estimate of 139.15. Cost estimates for hypothetical indexes tend to be
conservative, but always close to the cost of using the actual index.

                                                                             39
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   40
Arquitetura do Componente
      de Auto-sintonia
                               Queries,
                               updates,
                              procedure
                                 calls


 DBMS

                               Indexing
                Index         Alternatives
                                              Parser
              Self-tuning
              Component

                                              Optimizer
    Index Creation
                                Statements,
        Requests
                                   Plans


             Index Creation                        Executor
                Routines
                                                              41
              Agentes de software
• Autonomia: capacidade de agir para
  atingir um objetivo sem intervenção      Agente em Camadas
  humana                                        Mobilidade

• Reatividade: capacidade de responder           Tradução
  a mudanças para atingir objetivos;            Colaboração

• Pró-atividade: agir para atingir seus            Ação
  objetivos, antecipando-se a mudanças           Raciocínio
  no ambiente;
                                                  Crença
• Sociabilidade: capacidade de interagir          Sensor
  com outros participantes do ambiente;


                                                              42
     Integrando agentes ao SGBD

PostgreSQL
                                                     Postgres Process
 Postgres Process
                                                             Statement
                                                             Processor
                         Shared Queue



  Statement
                       Index
                                                                 …
                     Self-tuning
  Processor
                       Agent
                                                       Postgres Process

                                                               Statement
                    Index Creation
                                                               Processor
                       Routines




                                        Storage Structures
                                                                           43
            Heurística de Benefícios
• Query Evaluation Strategy

       Benefit of Hypothetical Index =
           Cost of Query with Actual Indexes –
           Cost of Query with Hypothetical Indexes;
       Update Accumulated Benefit of Hypothetical Index;
       If (Accumulated Benefit of Hypothetical Index >
         Cost to Create Hypothetical Index)
       Then
         Reset Accumulated Benefit of Hypothetical Index;
         Materialize Hypothetical Index;
       End if;

• Updates follow similar rules, but consider index destruction
                                                                 44
         Resultados Experimentais
•   Testes com DBT-2 toolkit
•   Carga TPC-C
•   Estudos de caso:
    –   Sem índice: BD sem índices e agente desligado
    –   Índice Automático: BD sem índices e agente ligado
    –   Indexação estática: DB com índices propostos pelos
        projetistas do toolkit e agente desligado
• Resultados surpreendentes!
    –   Um índice dos projetistas não criado
    –   Um índice não projetado criado... Porém útil!



                                                             45
    Avaliação de Vazão (Throughput)
•   Escala do BD: # de armazéns
•   Teste de 90 min: período de aprendizado ok


                           60

                           50
     Throughput (tr/min)




                           40                                  No
                                                               indexing
                           30
                                                               Automatic
                                                               Indexing
                           20
                                                               Static
                           10                                  indexing


                            0
                                1       2         3        4
                                    Number of Warehouses
                                                                           46
       Análise dos Resultados

•   O componentes conseguiu obter boa
    vazão média para a aplicação em questão
•   O componente-agente passa por fase de
    aprendizado até atingir estabilidade
•   Quanto mais tempo o componente estiver
    ativo com carga estável, melhor é a vazão
    observada do sistema



                                                47
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   48
        Estudos com carga OLAP


•  TPC-H
  • Menos estável
  • 6 consultas representativas (das 22)
• Novo comando
    Evaluate
    select linenumber, quantity
    from lineitem
    where orderkey = 200 and linenumber = 2;


                                               49
Componentes = Agentes




                        50
           Resultados Experimentais
•       Testes com DBT-2 toolkit agora com carga TPC-H
•       Três estudos de caso como anteriormente
•       Mesma carga submetida para SGBDs comerciais
    –     SQL 2005 e Oracle 10g
    –     Novo índice criado.... E útil !!!
•       Heurística de benefícios alterada
    –     Critério histórico
    –     Bônus = Benefício Acumulado / Utilizações
•       O ideal seria calcular a contribuição exata de cada
        índice por comando submetido
    –     E.g. Chaudhuri et al – 2004
•       Malus?
                                                              51
            Ciclo create-drop


•  Há risco de destruir índices que serão
   recriados posteriormente
• Custos envolvidos não desprezíveis
• Análise fina da situação:
  • Por quê índice não está sendo útil?
• Reindex é usado por DBAs



                                            52
     Resultados Experimentais               (2)


•   Testes com DBT-2 toolkit novamente com TPC-C
•   Três estudos de caso como anteriormente
•   Mais consultas, ajustes na carga submetida
•   Captura de poucas consultas agora não é bom...
•   Índices eliminados foram recriados!!!




                                                     53
   Recriação de Índices
Malefícios Causados pela Fragmentação de Índices

   Fragmentação prejudica desempenho para
    varreduras: confirmado!
   Fragmentação aumenta espaço ocupado
    por um índice: confirmado!

   Fragmentação x Custo: confirmado?!


                                                   54
Fragmentação no Nível Folha




                              55
        Fragmentação de Índices
•    Idéia básica:
    • Índices fragmentados merecem nova chance!

•       Novos critérios antes de um drop
    •  Grau de fragmentação
      • G = 100 – [(Ra/Ri) * 100]
    • Tamanho do índice
    • Taxa de varreduras

•       Novo comando:   getsize

                                                  56
                            Agenda
o Introdução e Motivação
o Fundamentos
   o SGBD, Índices e Processamento de Consultas
o Estado da Arte
   o Auto-sintonia local e global
o Índices Hipotéticos
   o What-if
o Criação autônoma de índices
   o TPC-C, sintonia local, heurística de benefícios, PostgreSQL
o Destruição e reindex automáticos
   o TPC-H, sintonia global, fragmentação física
o Comentários Finais
                                                                   57
            Algumas conclusões
• DBA automatizado?
  – SIM! Vale a pena ...
  – Paper SQLmag: “Será o fim do DBA?”
• Resultados preliminares animadores
  – Ferramentas de mercado
  – Academia: e.g. postgreSQL
• Vários problemas em aberto
  – Sintonia de projeto físico
  – Grau de autonomia!


                                         58
                          Planos Hipotéticos

                                                          SELECT *
Monitoring                                                FROM member
  SQL                     Workload Obtainment             WHERE last_name = ‘Randall’




                          Candidate Index Selection                    Costs


                                    + Candidate Indexes
                                                                      Modified Plan

             Statistics
                               Final Index Selection
                                                                            Indexes
                                                                            Benefits
                                        Final Indexes
                                       Recommendations

        BD
                                       Database Executer                   DDL
                    Workload                                              Clauses



                                                                                        59
         Atuais e Próximos Passos

•   Planos hipotéticos na prática
•   Wizards para PostgreSQL e versão 8
•   Parceria com UFC e aplicação real
•   Gráfico de planos reais e hipotéticos
•   Interação com DBA para tomada de decisão
•   Aviso de iminência de criação do índice
•   Consulta da metabase com índices hipotéticos
•   .... E muitas outras idéias ainda surgindo!

                                                   60
              Acknowledgments

•   Rogério Costa
•   Marcos Antonio Vaz Salles
•   Maíra Noronha
•   Anolan Milanes
•   Eduardo Morelli
•   José Maria Monteiro …


         www.inf.puc-rio.br/~postgresql
                                          61
OBRIGADO!




            62

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:10/5/2012
language:Latin
pages:62