ESTUDO DE VIABILIDADE DE UMA PLATAFORMA DE
BAIXO CUSTO PARA DATA WAREHOUSE
Eduardo Cunha de Almeida
Orientador: Prof. Dr. Marcos Sunye
Agosto / 2004
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
1
Agenda
Motivação
✔
Objetivo
✔
Data Warehouse
✔
PostgreSQL
✔
Metodologias de Benchmark
✔
Resultados
✔
Conclusão
✔
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
2
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
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
3
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)
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
4
Data Warehouse
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
Características
• Permite redundância
• Poucos usuários
• Consultas complexas
• Não possui metodologia padrão, apenas
recomendações de metodologias.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
5
Data Warehouse – Poder de processamento
• Armazenado histórico de dados, ou seja, grande
quantidade de dados
• Grandes segmento de rollback
Carregamento
• 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
Consultas
• Agregações
• Múltiplas junções
Índices
• Índices de Bitmap utilizados por SGBDs como Oracle e
DB2, também chamados de índices de HG pelo Sybase
IQ
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
6
PostgreSQL
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
Características
• Suporte as especificações da SQL92 e SQL99
• Controle de concorrência, evitando bloqueios de leitura
quando existe uma escrita no banco de dados
• Tablespace
Próxima versão
• Índice multi-coluna
• Melhorias no otimizador (rewriter)
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
7
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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
8
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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
9
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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
10
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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
11
Metodologia OSDL DBT3
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
• 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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
12
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
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
13
Resultados
Carregamento
Distribuição
Debian 32bits
Mandrake 64bits
Mandrake 64bits
Mandrake 64bits
Kernel Scala(GB) Script
Data load
2.4.24
100 Monolitico
11:37:59
2.6.5
100 Monolitico
07:24:23
2.6.5
100 Distribuído 06:14:30
2.6.5
1 Distribuído 00:03:40
Tempo(Hr)
PK and index Total load time
12:22:04
24:00:04
13:42:31
21:06:54
09:58:31
16:13:01
00:03:39
00:07:19
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
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
14
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
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
15
Resultados – TPC-H
13.821
621
6.843
25.000
12.568
1.383
15.734
25.000
25.000
25.001
389
2.381
3.371
1.374
4.273
3.171
6.780
2.429
25.000
25.001
5.577
25.001
25
.0
00
22
.5
00
20
.0
00
17
.5
00
15
.0
00
12
.5
00
10
.0
00
7.
50
0
5.
00
0
645
61
2.
50
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
RF1
RF2
-
Consultas
TPCH 100 GB - PostgreSQL 7.4.2
Tempo (segundos)
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
16
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.
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
17
Resultados – TPC-H e DBT3
Comparação entre consultas
Consulta 19 - Original
Select sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
Consulta 19 - Reescrita
Select sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
p_partkey = l_partkey
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 l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
and
((
and p_size between 1 and 5
p_brand = '[BRAND1]'
and l_shipmode in ('AIR', 'AIR REG')
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_shipinstruct = 'DELIVER IN PERSON'
and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] +10
)or
(
and p_size between 1 and 5
) 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'
(
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
...
)or
...
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
18
Resultados – TPC-H e DBT3
Comparação entre planos de execução
Plano de execução original
Aggregate (cost=672136305127.42..672136305127.43 rows=1 width=22)
-> Nested Loop (cost=7117.00..672136305127.15 rows=108 width=22)
Join Filter: (...........)
-> Seq Scan on lineitem (cost=0.00..218010.15 rows=6001215 width=79)
-> Materialize (cost=7117.00..9117.00 rows=200000 width=36)
-> Seq Scan on part (cost=0.00..7117.00 rows=200000 width=36)
(6 rows)
Plano de execução após reescrita
Aggregate (cost=288750.64..288750.64 rows=1 width=22)
-> Hash Join (cost=7617.00..288750.37 rows=104 width=22)
Hash Cond: ("outer".l_partkey = "inner".p_partkey)
Join Filter: ((("inner".p_brand = 'Brand#44'::bpchar) AND (("inner".p_container = 'SM CASE'::bpchar) OR ("inner".p_container = 'SM BOX'::bpchar) OR
("inner".p_container = 'SM PACK'::bpchar) OR ("inner".p_container = 'SM PKG'::bpchar)) AND ("outer".l_quantity >= 6::numeric) AND ("outer".l_quantity <=
16::numeric) AND ("inner".p_size >= 1) AND ("inner".p_size <= 5)) OR (("inner".p_brand = 'Brand#21'::bpchar) AND (("inner".p_container = 'MED BAG'::bpchar) OR
("inner".p_container = 'MED BOX'::bpchar) OR ("inner".p_container = 'MED PKG'::bpchar) OR ("inner".p_container = 'MED PACK'::bpchar)) AND ("outer".l_quantity
>= 11::numeric) AND ("outer".l_quantity <= 21::numeric) AND ("inner".p_size >= 1) AND ("inner".p_size <= 10)) OR (("inner".p_brand = 'Brand#21'::bpchar) AND
(("inner".p_container = 'LG CASE'::bpchar) OR ("inner".p_container = 'LG BOX'::bpchar) OR ("inner".p_container = 'LG PACK'::bpchar) OR ("inner".p_container = 'LG
PKG'::bpchar)) AND ("outer".l_quantity >= 23::numeric) AND ("outer".l_quantity <= 33::numeric) AND ("inner".p_size >= 1) AND ("inner".p_size <= 15)))
-> Seq Scan on lineitem (cost=0.00..263019.26 rows=219565 width=36)
Filter: (((l_shipmode = 'AIR'::bpchar) OR (l_shipmode = 'AIR REG'::bpchar)) AND (l_shipinstruct = 'DELIVER IN PERSON'::bpchar))
-> Hash (cost=7117.00..7117.00 rows=200000 width=36)
-> Seq Scan on part (cost=0.00..7117.00 rows=200000 width=36)
(8 rows)
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
19
Resultados – DBT3
Q01
Q02
Q03
Q04
Q05
Q06
Q07
Q08
Q09
Q10
Q11
Q12
Q13
Q14
Q15
Q16
Q17
Q18
Q19
Q20
Q21
Q22
RF1
RF2
Pow er
40
0
35
0
30
0
25
0
20
0
15
0
10
0
Throughput
50
-
Consultas
OSDL DBT3 1 GB - PostgreSQL 7.4.2
Consulta
Q1
Q2
Q3
Q4
Q5
Q6
Q7
Q8
Q9
Q10
Q11
Q12
Q13
Q14
Q15
Q16
Q17
Q18
Q19
Q20
Q21
Q22
RF1
RF2
Power
Throughput
135,292
138,627
1,748
1,995
39,181
102,552
19,531
11,838
28,017
44,982
20,448
16,562
33,927
36,587
6,215
13,040
377,367
72,558
33,418
46,324
7,094
10,326
23,916
29,520
25,430
35,261
16,145
15,716
34,547
31,695
16,760
19,610
1,406
2,218
22,827
31,860
21,923
21,008
68,576
3,821
12,329
27,868
4,789
4,962
5,468
51,683
0,013
0,027
Tempo (segundos)
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
20
Resultados – DBT3
Índices gerados
• Power@size = 332,35
• Throughput@size = 224,85
• Composite = 273,37
Resultado dos benchmarks
Timeout
Timeout por
Escala
Benchmark
Situação
consulta
(GB)
(seg)
TPC-H
100 Executado (*)
25.000
TPC-H
1
DBT3
1
(*) somente power test
Sem Timeout
Tempo final
(seg)
256.424
-
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
Situação
Tempo final
Interrompido > 72Hr
Interrompido > 24Hr
Executado 2.482 seg
21
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
Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse
22