TPC-H BENCHMARKING NO MYSQL

Abel S. Camati Zacarias João Holden Dulo Bioco University of Coimbra University of Coimbra DEI – CISUC DEI Coimbra, Portugal Coimbra, Portugal uc2013102030@student.dei.uc.pt uc2012127148@student.dei.uc.pt RESUMO Devido a padronização existente nos motores de base de dados, a diferença entre os produtos dos vários fornecedores passa a ser o nível de desempenho proporcionado. (Lorena, 2007). O presente trabalho faz um estudo sobre a performance do Mysql; Para tal, utilizou-se o TPC-H, um benchmark de apoio a decisão que é fornecido gratuitamente pela TPC (Transaction Processing Performance Council), O TPC é uma organização sem fins lucrativos, tendo como objetivo principal estabelecer critérios para se obter informações a respeito da performance de processamento de transações e de base de dados através de benchmarks. O TPC-H é um dos testes padronizados para obter resultados de performance, e posteriormente são divulgados os dados reais dessa performance. Os testes permitem que organizações escolham motores em função do resultado da performance. No TPC-H, o benchmark é definido como a execução de um teste de carregamento seguido por um teste de desempenho. Os testes de desempenho começaram desde a criação das tabelas, carregamento de dados (foram carregados 6 GB de dados) até a criação de índices e chaves, ou seja todas as actividades necessárias para levar o sistema a ser testado. O teste de desempenho consistiu na execução de dez consultas add hoc todas com a anotação dos tempos de duração, e posteriormente fez-se um conjunto de tabelas e gráficos espelhando os resultados (tempos) tanto da fase de carregamento como na execução das consultas.  Key words- TPC-H, Brenchmark, MySql, Base de Dados, performance e query.                          

  1  

1- INTRODUÇÃO  Avaliação de desempenho consiste em avaliar um sistema (computacional ou não),

buscar uma métrica que indique quantidade ou qualidade, por exemplo, de um

serviço prestado ou ainda determinar a eficiência com a qual um sistema atinge seus

objetivos, determinar a eficiência com a qual um sistema atinge as necessidades e

expectativas de seus utilizadores e de seus desenvolvedores, para uma dada

aplicação.(http://disciplinas.stoa.usp.br/pluginfile.php/37733/mod_resource/content/

1/aula1introducao.pdf)

Neste artigo (ponto 2) fez-se uma descrição geral sobre o funcionamento do TPC -H

Benchmark, focalizando todos os aspectos tidos em conta nos testes de

performance; Uma abordagem sobre Benchmarking de apoio a decisão foi feita no

ponto seguinte (3) mostrando a importância de Benchmarks tanto para clientes

como vendedores de motores de base de dados; No ponto 4 são apresentadas todas a

características da máquina utilizada no teste de performance, são apresentados os

resultados dos tempos de carregamento, tempos de criação dos índices e tempo de

cada query; No ponto 5 tirou-se as conclusões mais genéricas sobre aquilo que foi

analisado no artigo.

  2  

2- TPC-H BENCHMARK O TPC-H Benchmark, simula um sistema de apoio a decisão ou um banco de dados

em ambientes Business Inteligence. O desempenho de um sistema com estas

características é medido quando o sistema é encarregado de fornecer respostas sobre

um conjunto de dados.

Os componentes da base de dados do TPC-H é constituído por um conjunto de oito

tabelas, em que cada uma possui um conjunto de linhas diferente de cada tabela e

bem como o espaço ocupado difere de cada tabela.

A seguir apresentamos uma figura retirada do site do TPC-H, ilustrando a forma

como as tabelas estão organizadas e relacionadas.

O TPC Benchmark ( TPC- H) é um Benchmark de apoio à decisão . Consiste num

conjunto de consultas ad-hoc orientados ao negócios. As consultas e os dados que

constituem o banco de dados têm sido escolhido por terem grande relevância em

toda a indústria , mantendo um grau suficiente de facilidade de implementação.

Este Benchmark é um sistema de apoio à decisão que examine grandes volumes de

dados ; Executa consultas com um elevado grau de complexidade ; Dá respostas a

questões críticas de negócios. TPC- H avalia o desempenho de vários sistemas de

apoio à decisão pela execução de conjuntos de consultas em um banco de dados

padrão, sob condições controladas. As consultas do TPC-H dão resposta as questões

de negócios do mundo real.

As operações de TPC - H são modelados da seguinte maneira : O banco de dados é

continuamente disponível 24 horas por dia, 7 dias por semana, para consultas ad-

hoc e modificações de dados de vários utilizadores finais em todas as tabelas , com

exceções de manutenção que podem ocorrer uma vez por mês; Devido à natureza de

todo o conjunto dos dados do negócio armazenados no banco de dados do TPC- H,

as consultas e as funções de atualização podem ser executadas no banco de dados a

qualquer momento. Além disso, esta mistura de consultas e funções de atualização

está sujeita a requisitos específicos ACID, desde consultas e atualizações

executadas concorrentemente;

A base de dados mínima necessária para rodar o Benchmark contém dados de

negócios de 10 mil fornecedores. Ela contém cerca de dez milhões de linhas que

representam uma capacidade de armazenamento bruto de cerca de 1 gigabyte. Em

conformidade, a implementação também pode ser feita com maior quantidade de

dados, (por exemplo, 100 gigabytes).

  3  

O Benchmark TPC-H baseia-se em um modelo de dados relativamente simples,

como mostra a figura a baixo. Trata-se de um problema genérico envolvendo

clientes, fornecedores e compras de itens subdividido em componentes.

 Figura- Esquema TPC-H. Fonte (http://www.tpc.org/tpch/- página oficial) De acordo com o esquema acima apresentado pode dizer-se que o número que

aparece logo abaixo do nome da tabela representa a sua cardinalidade. Esta pode ser

fixa (tabelas region e nation), ou variável, onde multiplica-se uma constante por um

scale factor determinado no momento da criação da base de dados. Por exemplo,

caso SF valha 3, haverá 450.000 linhas na tabela de clientes (customer).

TPC Benchmark™ H Standard Specification Revision 2.8.0 Page 12

2.2 Database Entities, Relationships, and Characteristics

The components of the TPC-H database are defined to consist of eight separate and individual tables (the Base

Tables). The relationships between columns of these tables are illustrated in Figure 2: The TPC-H Schema.

Figure 2: The TPC-H Schema

Legend:

• The parentheses following each table name contain the prefix of the column names for that table;

• The arrows point in the direction of the one-to-many relationships between tables;

• The number/formula below each table name represents the cardinality (number of rows) of the table. Some are

factored by SF, the Scale Factor, to obtain the chosen database size. The cardinality for the LINEITEM table is

approximate (see Clause 5.2.5).

PARTKEY

NAME

MFGR

BRAND

TYPE

SIZE

CONTAINER

COMMENT

RETAILPRICE

PARTKEY

SUPPKEY

AVAILQTY

SUPPLYCOST

COMMENT

SUPPKEY

NAME

ADDRESS

NATIONKEY

PHONE

ACCTBAL

COMMENT

ORDERKEY

PARTKEY

SUPPKEY

LINENUMBER

RETURNFLAG

LINESTATUS

SHIPDATE

COMMITDATE

RECEIPTDATE

SHIPINSTRUCT

SHIPMODE

COMMENT

CUSTKEY

ORDERSTATUS

TOTALPRICE

ORDERDATE

ORDER-PRIORITY

SHIP-PRIORITY

CLERK

COMMENT

CUSTKEY

NAME

ADDRESS

PHONE

ACCTBAL

MKTSEGMENT

COMMENT

PART (P_)SF*200,000

PARTSUPP (PS_)SF*800,000

LINEITEM (L_)SF*6,000,000

ORDERS (O_)SF*1,500,000

CUSTOMER (C_)SF*150,000

SUPPLIER (S_)SF*10,000

ORDERKEY

NATIONKEY

EXTENDEDPRICE

DISCOUNT

TAX

QUANTITY

NATIONKEY

NAME

REGIONKEY

NATION (N_)25

COMMENT

REGIONKEY

NAME

COMMENT

REGION (R_)5

  4  

Estão representadas cinco regiões (continentes), que congregam vinte e cinco

nações (tabelas region e nation, respectivamente). Clientes e Fornecedores (tabelas

supplier e customer) estão associados às nações. Enquanto os primeiros realizam

pedidos de compras (tabela orders), os segundos fornecem componentes (tabela

part) de itens de compra. Como um fornecedor pode oferecer vários itens e um item

pode ser disponibilizado por vários fornecedores, existe uma tabela para registar

esta relação NxN (partsupp). Finalmente, a tabela mais volumosa do modelo

(lineitem) associa itens de compra a pedidos.

Os parênteses ao lado dos nomes das tabelas indicam o prefixo utilizado para

denominar os campos da tabela em questão. Desta forma, a chave primária da tabela

de fornecedores chama-se S_SUPPKEY;

As flechas indicam as associações entre chaves primárias e estrangeiras. Assim,

verifica-se que a chave primária da tabela nation está vinculada ao campo nationkey

na tabela de fornecedores (tabela supplier).

Apresenta-se abaixo a descrição de cada consulta TPC-H escolhida. As consultas

escolhidas foram de 1 a 10.

Consulta 1

Totais consolidados para transações financeiras ocorridas num determinado período

de tempo;

Consulta 2

Seleciona qual fornecedor deve ser escolhido para fazer um pedido de um

determinado componente em uma determinada região;

Consulta 3

Esta consulta retorna os 10 pedidos não enviado com o valor mais alto;

Consulta 4

Verifica a eficácia do sistema de controlo de prioridades de pedidos e analisa os

níveis de satisfação dos clientes;

Consulta 5

Lista o volume de receitas feita através de fornecedores locais;

Consulta 6

Quantifica o volume de receitas que teriam resultado da eliminação certos descontos

em toda a empresa num determinado intervalo percentual num determinado ano.

  5  

Consulta 7

Determina o valor de mercadorias embarcadas entre certos países para ajudar na

renegociação de contratos de transporte.

Consulta 8

Esta consulta determina como a quota de mercado de um determinado país dentro

de uma região tem mudado ao longo de dois anos para um tipo de peça.

Consulta 9

Esta consulta determina o lucro feito sobre uma determinada linha de peças,

divididas por país fornecedor e ano.

Consulta 10

Identifica clientes que podem ter tido problemas com entregas.

3- BENCHMARKING DE APOIO A DECISÃO.

Benchmarkings de apoio a decisão são de grande importância tanto para as

empresas que utilizam os motores de bases de dados como os fornecedores.

Permitem por exemplo ao cliente saber que motor deve adquirir, ou seja que motor

será necessário implementar na sua empresa. Por outra, um potencial cliente pode ir

ao site do TPC -H e verificar a performance e preço para saber qual motor adquirir.

Os fornecedores com base nos testes de performance publicados no TPC -H podem

por exemplo melhorar a performance dos seus motores de base de dados.

 4-­‐  SETUP  EXPERIMENTAL  Os testes de desempenho foram executados em um portátil MacBookPro, com um

processador de Intel Core i5 com uma frequência de 2.4 GHz, 8GB de memória

RAM DDR3 com uma frequência de 1600 MHz, com 3 MB de cache L3 partilhada

e uma memória flash de 256GB.

Para a realização do presente teste utilizamos a versão 5.5.33 do MySql, instalada

sobre o Sistema Operativo OS X versão 10.9.1, e as consultas foram feitas na

aplicação Cliente phpMyAdmim rodando localmente, e a versão 2.16.0 do TPC-H.

Apresentamos a seguir as tabelas com os resultados obtidos no ato do carregamento

da Base de Dados.

  6  

5- RESULTADO

5.1- CRIAÇÃO DAS TABELAS

Tal como foi anteriormente dito, o objectivo é fazer o teste com a Base de Dados

disponibilizada no site do TPC-H.

A seguir apresenta-se o tempo que levou a criação das tabelas na base de dados,

bem como uma ilustração gráfica dos resultados obtidos.

Nº O TABELA LINHAS TEMPO DE CARREGAMENTO 1 CUSTOMER ~901,212 0,0140 Seg. 2 LINEITEM ~35,929,531 0,0145 Seg. 3 NATION 0,0293 Seg. 4 ORDERS ~9,100,406 0,0159 Seg. 5 PART ~1,199,875 0,0152/0.0350 Seg. 6 PARTSUPP ~4,834,174 0,0147 Seg. 7 REGION 0,0221 Seg. 8 SUPPLIER ~59,856 0,0295 Seg. TOTAL 8 Tabelas 52,025,083 Linhas Tabela1-­‐  Tempo  obtido  na  criação  das  tabelas  

Grafico1- Criação das Tabelas na Base de Dados.

5.2- CARREGAMENTO DA INFORMAÇÃO NAS TABELAS

A tabela que se segue, ilustra o número de linhas bem como o espaço ocupado em

disco de cada uma das tabelas da Base de Dados.

Nº  O   TABELA   LINHAS   TEMPO  DE  CARREG.   ESPAÇO  OCUPADO  1   CUSTOMER   900.000   7,2178  Sec   311.6MB  

0,014  0,0145  

0,0293  

0,0159  0,0152  

0,0147  

0,0221  

0,0295  

TEMP/  SEGUNDO  

CUSTOMER  

LINEITEM  

NATION  

ORDERS  

PART  

PARTSUPP  

REGION  

SUPPLIER  

  7  

2   LINEITEM   360.00.147   309,8828  Sec   7.7GB  3   NATION   25   0,002  Sec   64  KB  4   ORDERS   9000.000   62,8393  Sec   1.5GB  5   PART   1.200.000   8,5028  Sec   265.6MB  6   PARTSUPP   4.800.000   43,5401  Sec   1GB  7   REGION   5   0,0090  Sec   32KB  8   SUPPLIER   60.000   0,4197  Sec   13.5MB  TOTAL   8  Tabelas   51.960.177  Linhas     10.8GB  Tabela2-­‐  Resultados  do  carregamento  das  tabelas    

 Gráfico2-­‐  Tempo  de  carregamento  dos  dados  nas  tabelas  por  segundos.    O gráfico acima, apresenta o tempo de carregamento de cada uma das 8 tabelas

existente na Base de Dados. E pelos resultados pode-se verificar que a tabela

LINEITEM é a que mais tempo de execução levou, cerca de 71,66% em relação as

outras. Este tempo de execução que a mesma levou, deve-se principalmente a

quantidade de linhas (360.00.147linhas) que a mesma possui bem como a

quantidade de informação nela contida (7.7GB). A tabela que menos tempo de

execução levou foi a tabela NATION, cerca de 0,00046%.

5.3- CARREGAMENTO DAS CHAVES PRIMÁRIAS E ESTRANGEIRAS.

Criou-se de seguida as chaves primárias e secundarias nas tabelas da Base de

Dados, com o objectivo de garantir a integridade dos dados.

7,2178  

309,8828  

0,002  

62,8393  

8,5028  43,5401  

0,009   0,4197  

TEMPO  DE  EXECUÇÃO/  SEC  

CUSTOMER  

LINEITEM  

NATION  

ORDERS  

PART  

PARTSUPP  

REGION  

SUPPLIER  

  8  

5.3.1- CHAVES PRIMÁRIAS

A tabela abaixo e o respectivo gráfico, mostram os tempos (em segundos),

de carregamento das chaves primárias.

Nº O TABELAS Temp. Carreg. Primary Key/ Seg

1 CUSTOMER 8,1814

3 LINEITEM 677,1544

4 NATION 0,0209

4 ORDERS 79,5134

5 PART 10,4987

6 PARTSUPP 46,2618

7 REGION 0,0375

8 SUPPLIER 0,5324 Tabela3- Carregamento das chaves Primária

Gráfico3- Carregamento das chaves Primarias.

5.3.2- CHAVES ESTRANGEIRAS

Pode-se verificar, na tabela e gráfico abaixo os resultados obtidos na criação

das chaves estrangeiras.

8,1814  

677,1544  

0,0209  

79,5134  

10,4987  

46,2618  

0,0375   0,5324  

Tempo  de  Carreg.  

CUSTOMER  

LINEITEM  

NATION  

ORDERS  

PART  

PARTSUPP  

REGION  

SUPPLIER  

Nº O TABELAS Temp. Carreg. Foreign Key/ Seg

1 CUSTOMER 9,4518

2 LINEITEM 9291,1391

3 NATION 0,0582

  9  

Tabela4- Carregamento das Chaves Estrangeiras.  

 Gráfico4-­‐  Carregamento  das  Chaves  Estrangeiras.  

5.4- TEMPO DE CRIAÇÃO DOS ÍNDECES

Os índices foram criados com base nos campos utilizados nos critérios de busca.

TABELA   ÍNDECE   TEMPO/SEC  CUSTOMER   INDEX_CUSTOMER   2,972  LINEITEM   INDEX_LINEITEM   288,8038  NATION   INDEX_NATION   0,0452  ORDERS   INDEX_ORDERS   35,9125  PART   INDEX_PART   5,5986  PARTSUPP   INDEX_PARTSUPP   16,0626  REGION   INDEX_REGION   0,0662  SUPPLIER   INDEX_SUPPLIER   0,1889     Tabela5- Tempo da criação dos índices por tabelas

9,4518  

9291,1391  

0,0582  

1660,2618  

115,5943  

0,7479  

Temp.  Carreg.  Foreign  Key  

CUSTOMER  

LINEITEM  

NATION  

ORDERS  

PARTSUPP  

SUPPLIER  

4 ORDERS 1660,2618

5 PARTSUPP 115,5943

6 SUPPLIER 0,7479

  10  

Gráfico5- Criação de índices

5.5-­‐  PESQUISA  Na próxima tabela e gráfico, mostra-se os resultados de cada uma das queries

efetuadas na Base de Dados. Apresentamos na mesma tabela e gráfico o tempo que

levou para a execução de cada query. O tempo apresentado está expresso em

segundos.  

Query   Rows   Tempo  de  Execução/Seg  Q1   4   104,2518  Q  2   2.809   84,1047  Q  3   68.496   466,2878  4   5   44,6434  5   5   299,5164  6   1   20,0214  7   4   248,6847  8   2   523,8567  9   175   1.840,9723  10   228.580   738,6247  Total 300.081 4.370,9639 Tabela6-­‐  Tempo  do  carregamento  das  chaves  primárias.    

2,972  

288,8038  

0,0452  

35,9125  5,5986  

16,0626  

0,0662  

0,1889  

TEMPO/SEC  INDEX_CUSTOMER  

INDEX_LINEITEM  

INDEX_NATION  

INDEX_ORDERS  

INDEX_PART  

INDEX_PARTSUPP  

INDEX_REGION  

INDEX_SUPPLIER  

  11  

 Gráfico6-­‐  Carregamento  das  chaves  primárias.    Pode-se também verificar o tempo de execução de cada uma das 10 queries

escolhidas na Base de Dados de teste. Constatou-se que a query número 9 é a que

mais tempo levou para a execução (1.840,9723segundos) cerca de 42% em relação

as demais consultas. Isto deve-se ao facto de a consulta número 9 englobar 7 das 8

tabelas (part, supplier, lineitem, partsupp, orders, nation ) o que corresponde a

87,5% das tabelas e isso torna a consulta muito lenta.

                                             

104,2518  84,1047  

466,2878  44,6434  

299,5164  

20,0214   248,6847  523,8567  

1840,9723  738,6247  

Q1  Q2  Q3  Q4  Q5  Q6  Q7  Q8  Q9  Q10  

0   200   400   600   800   1000   1200   1400   1600   1800   2000  

TIME  EXECUTION/  SEC  

  12  

6 –  CONCLUSÕES    Com  base  nos  testes  feitos,  constatou-­‐se  o  seguinte:    

! Quanto  maior   for  o  volume  de  dados,  menor  é  desempenho  do  MySql,  obrigando  o  escalonamento  vertical  ou  horizontal;  

! Servidores   com   maiores   capacidade   de   processamento   e  armazenamento   contribuem   para   o  melhor   desempenho   do  MySql   no  que  diz  respeito  ao  carregamento  de  dados  e  execução  de  consultas;  

! As  consultas  com  elevadas  junções,  agregações  de  tabelas  foram  as  mais  lentas  no  que  diz  respeito  ao  tempo  de  execução;  

! MySql  pode  suportar  bases  de  dados  transacionais,  mas  não  é  o  motor  adequado  para  implementar  uma  base  de  dados  de  apoio  a  decisão.  

                                                                       

  13  

7-­‐  REFERÊNCIAS    1- http://en.wikipedia.org/wiki/Transaction_Processing_Performance_Council

2-

http://disciplinas.stoa.usp.br/pluginfile.php/37733/mod_resource/content/1/aula1introduco.

pdf

3- http://www.tpc.org/tpch/spec/tpch2.8.0.pdf

4- http://www2.dbd.puc-rio.br/pergamum/tesesabertas/0410827_06_postextual.pdf

5- http://wiki.locaweb.com.br/ptbr/Otimizações_nas_consultas_ao_banco_de_dados

6- http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/pt/create-index.html

7- http://www.quest.com/documents/dsd-benchmark-us-ag-20100122.pdf