ESTUDO DE VIABILIDADE DE UMA PLATAFORMA DE BAIXO CUSTO PARA DATA WAREHOUSE

1. ESTUDO DE VIABILIDADE DE UMA PLATAFORMA DE BAIXO CUSTO PARA DATA WAREHOUSE Eduardo Cunha de Almeida Orientador: Prof. Dr. Marcos Sunye Agosto / 2004

2. Agenda • Motivação • Objetivo • Data Warehouse • PostgreSQL • Metodologias de Benchmark • Resultados • Conclusão

3. Motivação • Data Warehouse • Demanda crescente pela implementação de Data Warehouses • SW “Open Source” e HW de baixo custo • Três maiores desafios em Data Warehouse (KIM, W., 2003) • Limpeza de dados • Seleção de Fontes • Desempenho

4. Objetivo • Viabilidade de uma Plataforma de Baixo Custo para um Data Warehouse • SGBD PostgreSQL • SO LINUX • HW – Equipamentos de baixo custo • Futuras implementações no PostgreSQL visando Data Warehouse • Fomentar: • Desenvolvimento de softwares “open source” para os outros componentes do ambiente Data Warehouse (OLAP e ETL)

5. Conceito DW é um grande repositório de dados coletados de diversas fontes que destina-se a gerar informações para o nível gerencial sendo fonte para tomadas de decisão. • Orientado a consultas • Exige grande capacidade de armazenamento • Não volátil • Permite redundância • Poucos usuários • Consultas complexas • Não possui metodologia padrão, apenas recomendações de metodologias. Características Data Warehouse

6. Carregamento • Armazenado histórico de dados, ou seja, grande quantidade de dados • Grandes segmento de rollback • Carregamento periódico Ex.1: GVT carrega diariamente 8 milhões de registros. Atualmente o DW possui 2,5 TB de dados. Ex.2: BCP carrega diariamente 30 milhões de registros • Varredura completa de tabela • Agregações • Múltiplas junções Consultas Índices • Índices de Bitmap utilizados por SGBDs como Oracle e DB2, também chamados de índices de HG pelo Sybase IQ Data Warehouse – Poder de processamento

7. O PostgreSQL é um SGBD de código aberto que deriva seu desenvolvimento do SGBD Ingres. • SGBD de código aberto mais avançado • Integridade referencial • Suporte as especificações da SQL92 e SQL99 • Controle de concorrência, evitando bloqueios de leitura quando existe uma escrita no banco de dados Características Próxima versão • Tablespace • Índice multi-coluna • Melhorias no otimizador (rewriter) PostgreSQL

8. PostgreSQL – Execução de Consultas 1 - A consulta é submetida ao parser que verifica as definições dos objetos no dicionário de dados; 2 - É realizado a reescrita das consultas; 3 - O planner constrói um plano de execução orientado pela consulta reescrita e pelas estatísticas coletadas pelo DBA; 4 - É executado o plano criado pelo planner.

9. Metodologias de Benchmark para DW Visão Geral Metodologia para simular a execução de uma carga de trabalho do mundo real. Metodologias Utilizadas • TPC-H – Mantida pelo Transaction Processing Performance Council (TPC) • OSDL DBT3 – Mantida pelo Open Source Development Lab (OSDL) O TPC é uma corporação sem fim lucrativo fundado para definir processamento de transações e benchmarks de banco de dados. O propósito do benchmark TPC é prover dados de desempenho relevantes e objetivos para a indústria. O OSDL é uma organização sem fim lucrativo que fornece o “estado da arte” em computação e ambientes de teste para acelerar o crescimento e adoção do SO Linux nas empresas.

10. Metodologia TPC-H Visão Geral TPC-H é compreendido de consultas de negócio projetadas para exercitar as funcionalidades de um sistema buscando representar aplicações complexas de análises de negócios. São 22 consultas de natureza ad-hoc, com vários tipos de acesso, alto grau de complexidade e que examinam uma grande porcentagem dos dados disponíveis. Estas consultas são executadas de forma seqüencial e formam uma seqüência de consultas. São executadas também 2 consultas de atualização. Para a execução dos testes o driver foi desenvolvido em Java/Shell script. As escalas para o benchmark estão divididas em: 1 GB, 10 GB, 30 GB, 100 GB, 300 GB, 1.000 GB, 3.000 GB e 10.000 GB. Operações realizadas • Varredura seqüencial de grandes volumes de dados; • Agregações de grandes volumes de dados; • Junções de múltiplas tabelas; • Ordenações.

11. Metodologia TPC-H Regras de execução Cada seqüência de consultas deve corresponder a uma sessão; Power Test Execução de uma seqüência de consulta entre a execução das duas consultas de atualização. Throughput Test Execução em paralelo de várias seqüência de consulta entre a execução das duas consultas de atualização. A quantidade de seqüências é definida de acordo com a escala utilizada. O sistema deverá ser o mesmo para os dois testes. Medidas • TPC-H Power; • TPC-H Query-por-hora ou Consulta-por-hora; • TPC-H Price/Performance ou Preço / Desempenho.

12. O OSDL DBT3 é uma adaptação do TPC-H para testar o sistema operacional Linux e sua pilha de softwares de código aberto. Diferenças entre DBT3 e TPC-H Metodologia OSDL DBT3 • Possibilidade de reescrita das consultas, pois o PostgreSQL não resolve de maneira eficaz como será demonstrado. A reescrita é proibida pelo TPC; • Pode ser utilizado qualquer fator de escala e não somente os utilizados pelo TPC-H. Apesar disto foram utilizados neste trabalho as escalas 1GB e 100GB; • No TPCH algumas consultas são restringidas no retorno de linhas e esta restrição não é aplicada pelo DBT3. Neste trabalho utilizamos a mesma opção do DBT3; • Não é realizado o teste de ACID no DBT3; • Não é utilizada a métrica de preço/desempenho.

13. Configuração do Ambiente Benchmark • OSDL DBT3 versão 1.4 e TPC-H versão 2.0.0 • Fator de escala de 1GB e 100GB • Carregamento realizado utilizando arquivos (flat file) Software • PostgreSQL 7.4.2 • Mandrake Linux 64bits (kernel 2.6.5) • Java SDK 1.4.2_04 • PostgreSQL JDBC pg74.1jdbc3.jar • Utilitários TPC-H (DBGEN and QGEN) Hardware • Dual Opteron 64bits Model 240 1.4GHz • 4 GB RAM • 960 GB Disk em RAID 0

14. Resultados Carregamento TPC-H (100 GB) • Sybase / Solaris (2 CPU) : 6,27 H • MS SQL Server / Win 2003 (2 CPU): 4.79 H • DB2 / Linux (16 CPU): 0,63 H DBT3 (1 GB) • PostgreSQL / RedHat (8 CPU) : 00:42:47 H • PostgreSQL / RedHat (4 CPU) : 00:39:54 H

15. Resultados – TPC-H Procedimentos realizados 1 – Execução com clausula de timeout (25000 s) PostgreSQL nunca foi submetido a uma carga de trabalho TPC-H de 100 GB 2 – Verificação dos resultados Verificação das consultas que interromperam por timeout 3 – Execução sem clausula de timeout Verificar o tempo total da execução do benchmark 4 – Interrupção do teste após 72 H 5 – Verificação dos resultados Verificação das consultas que foram interrompidas 6 – Estudo das consultas (texto e plano de execução) Identificação dos problemas de demora na execução das consultas

16. Resultados – TPC-H

17. Resultados – TPC-H e DBT3 Texto das consultas Algumas consultas rodaram próximas ou mais rápido que os SGBDs Sybase e MS SQL Server. Estas consultas são as de numero 11 e 18. As consultas que consumiram o maior tempo mostraram algumas deficiências do PostgreSQL em comparação com outros SGBDs. Estas consultas são as de numero 4, 8, 9, 10, 19, 20 e 22. As operações realizadas foram: • Sub-consultas dentro de outra sub-consulta; • Operadores EXISTS e NOT EXISTS • Agregações quando utilizado visões in-line, que são sub-consultas dentro da clausula FROM Plano de execução PostgreSQL gerou planos de execução ruins sendo necessário a reescrita das consultas. Motivo para a execução de outro benchmark que permita a reescrita.

18. Resultados – TPC-H e DBT3 Comparação entre consultas Consulta 19 - Original Consulta 19 - Reescrita Select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = ‘[BRAND1]' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )or ( p_partkey = l_partkey and p_brand = '[BRAND2]' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' )or ... Select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where p_partkey = l_partkey and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' and ( ( p_brand = '[BRAND1]' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] +10 and p_size between 1 and 5 ) or ( p_brand = '[BRAND2]' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] +10 and p_size between 1 and 10 )or ...

19. Resultados – TPC-H e DBT3 Comparação entre planos de execução

20. Resultados – DBT3

21. Resultados – DBT3 Índices gerados • Power@size = 332,35 • Throughput@size = 224,85 • Composite = 273,37 Resultado dos benchmarks

22. Conclusão • Atual versão do PostgreSQL (7.4.x) não executou satisfatoriamente o TPC-H sendo necessário executar o DBT3; • O PostgreSQL não possui um problema estrutural que impeça a execução das consultas, apenas não as executa de um forma adequada; • Implementação de estruturas que aumentem o desempenho de um bancos de dados de DW: • Páginas PAX • Índices de bitmap • Paralelismo intra-query • Diante do exposto concluímos que, atualmente, somente é viável utilizar o PostgreSQL num projeto de data warehouse de maneira restrita: • Escassez de recursos financeiros para adquirir SGBDs mais maduros • Monitorar constantemente as consultas submetidas ao banco de dados • Aceitar o baixo desempenho nas consultas apontadas neste trabalho