Por que criar um banco de dados Firebird de um terabyte?

Muitas empresas trabalham com grandes bancos de dados Firebird e dependem deles para sustentar operações de negócio importantes. Alguns dos bancos de dados Firebird já têm centenas de gigabytes de tamanho e continuam a crescer (veja a seção “Quem é grande?”), e é fácil prever o momento em que eles se tornarão 2, 3 ou 5 vezes maiores. Assim, administradores de banco de dados e fornecedores têm interesse em investigar o comportamento do Firebird com grandes bancos de dados e obter algumas recomendações sobre como gerenciá-los.

Também a razão importante que tínhamos em mente ao criar o banco de dados Firebird de 1Tb foi a eliminação definitiva da percepção predominante do Firebird como um mecanismo de banco de dados para “bancos de dados pequenos”. Esse mito parece estar morto agora, mas alguns analistas e jornalistas o ressuscitam regularmente, e esperamos acabar de uma vez por todas com essa percepção ridícula.


## Hardware

O Firebird é bem conhecido por sua incrível escalabilidade e esta investigação confirmou isso mais uma vez. O propósito inicial deste experimento era apenas a criação de um banco de dados Firebird de 1Tb de tamanho, então usamos um computador desktop comum:

Tabela 1: Hardware


Componente	Parâmetros
CPU	AMDAthlon 64 x2 5200
RAM	4GB
Placa-mãe	MSI K9N Platinum
HDD1 (sistema operacional e temp)	ST3160815AS, 160GB, SATA II
HDD2 (auxiliar)	HDT721064SLA360, 640GB,SATAII
HDD2 (auxiliar)	HDS728080PLA380, 80GB, SATA I
HDD3 (banco de dados)	ST31500341AS, 1.5TB, SATA II (Firmware CC1H)

Em essência, colocamos o HDD de 1,5Tb em um dos nossos desktops de escritório, sem quaisquer outras modificações. Este HDD foi formatado com tamanho de cluster de 16Kb (o mesmo que o tamanho de página do banco de dados, como você pode ver abaixo).

Software

Como é um computador desktop, o sistema operacional é Windows XP Professional SP3, 32bit. Para realmente executar o teste, usamos o carregador do kit de ferramentas baseado em TPC (baixe-o em http://ibdeveloper.com/tests/tpc-c/, tanto binários quanto fontes estão disponíveis).

Gostaríamos de enfatizar que o carregador insere dados como seriam inseridos em um cenário da vida real: os registros são inseridos e situados dentro do banco de dados (e em áreas físicas do disco) em várias tabelas master-detail-subdetail, não tabela por tabela.

Tabela 2: Software


Software	Versão
Sistema operacional	Windows XP Professional SP3, 32bit
Firebird	2.1.3 SuperServer (snapshot)
Carregador	Carregador personalizado do teste baseado em tpc

Plano

Tínhamos um plano muito direto para este experimento:

Criar o banco de dados e carregá-lo com 1Tb de dados, sem índices
Criar chaves primárias e índices apropriados (então, na verdade, o tamanho do banco de dados é superior a 1Tb)
Coletar estatísticas do banco de dados
Executar várias consultas SQL e estimar o desempenho do banco de dados

Configuração do banco de dados e do servidor Firebird

O banco de dados tem tamanho de página de 16384 bytes, o mesmo que o cluster do HDD, para maximizar o desempenho de disco (ler/gravar 1 página em um ciclo de I/O).

Na configuração do Firebird, configuramos um diretório adicional para espaço temporário e o apontamos para o disco de 640Gb (onde ~300Gb estavam livres).

Etapa de carregamento

Os dados foram carregados neste banco de dados em várias etapas. O computador foi usado durante as operações de carregamento como um desktop comum (temos MS Office, Firefox, IBAnalyst, etc - cerca de 8-12 programas rodando ao mesmo tempo). Se tivéssemos dedicado o hardware apenas para esta tarefa, provavelmente seria mais rápido, então considere esses valores apenas como um exemplo de baixo custo; eles definitivamente não são os melhores resultados.

Tabela 3: Operações de carregamento

| |


Descrição	Valor
Tempo de carregamento	~70 horas
Total de registros inseridos	6,2 bilhões
Velocidade média de inserção	24500 registros/segundo
Tamanho médio do registro	146 bytes (mín 13 bytes, máx - 600 bytes)
Transações	646489

Passamos ~4 dias carregando, e depois disso tínhamos um banco de dados Firebird com exatamente 1Tb de tamanho (ou seja, 1 099 900 125 184 bytes).

Abaixo você pode ver o crescimento do banco de dados e a dinâmica das transações no FBDataGuard Viewer:

Índices

Criamos os índices um a um e contamos o tempo de criação e o tamanho apropriado do arquivo temporário usado para ordenação.

O maior índice foi criado para a tabela ORDER_LINE. Sua chave primária contém quatro campos (Smallint, Smallint, Integer e Smallint). O arquivo temporário para este índice de ordenação foi de 182Gb, e o tamanho final do índice no banco de dados é de 29,3Gb.

É interessante ver que mesmo o índice para uma tabela com 3,8 bilhões de registros tem profundidade = 3, porque o tamanho de página era de 16384 bytes, então não há overhead ao buscar dados usando a chave primária para esta tabela.

Estatísticas

Depois disso, coletamos as estatísticas do banco de dados. Levou 7 horas 32 minutos e 45 segundos.

Colocamos as principais informações estatísticas em uma única tabela e incluímos algumas consultas e medições de tempo:

Tabela 4: Estatísticas consolidadas para o banco de dados de 1Tb


Nome da tabela	Contagem de registros	Tamanho, gb	*Tempo de execução de select count()**	Tempo de criação do índice	Tamanho do arquivo tmp, Gb	Tamanho do índice, Gb
WAREHOUSE	1240	0.002	0s	0	0	0.0
ITEM	100000	0.012	0.7s	-	-	0.0
DISTRICT	124000	0.017	0.7s	6	-	0.0
NEW_ORDER	111600000	32	20m 00s	23m 00s	4.56	0.8
CUSTOMER	372000000	224	-	41m 00s	-	2.6
customer_last				1h 52m 32s	12.4	2.3
fk_cust_ware				2h 10m 51s	-	2.3
HISTORY	372000000	32	-	-	-	-
ORDERS	372000000	25	32m 00s	45m 41s	15.2	2.5
STOCK	1240000000	404	-	3h 34m 44s	41.5	9.2
ORDER_LINE	3720051796	359	-	12h 6m 18s	182.0	29.3

As estatísticas do banco de dados podem ser baixadas aqui.

Você pode usar o FBDataGuard Community Edition Viewer gratuito para interpretar os dados de texto e ver não apenas as métricas de desempenho do banco de dados, mas também o consumo de CPU e memória.

Consultas

Em primeiro lugar, executamos consultas select count(*) em várias tabelas (veja a 4ª coluna na Tabela 4 acima). Como você sabe, devido à natureza multiversão do Firebird, select count(*) para a tabela inteira é uma operação cara para o servidor porque requer visitar cada página, e desenvolvedores Firebird experientes não usam select count(*), mas nós o usamos para demonstrar a proporção geral de desempenho do banco de dados e do hardware.

Após as consultas select count, executamos consultas de um cenário da vida real e, para ser honesto, ficamos impressionados com resultados tão bons. Veja por si mesmo:


Consulta	Estatísticas	Descrição
select w_id, w_name, c_id, c_last from WAREHOUSE, customer where c_w_id = w_id	PLAN JOIN (WAREHOUSE NATURAL, CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 15ms Execute time = 79ms Avg fetch time = 6.08 ms Current memory = 272 264 476 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 82 Writes from cache to disk = 0 Fetches from cache = 3 648	Junção simples de tabelas com 12400 e 372000000 registros, sem condições WHERE. “Avg fetch time = 6.08 ms” é para buscar a primeira linha.
select w_id, w_name, c_id, c_last from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 16ms Execute time = 78ms Avg fetch time = 6.00 ms Current memory = 272 266 148 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 88 Writes from cache to disk = 0 Fetches from cache = 3 656	Junção das mesmas tabelas com condição que força a seleção de registros recentes. “Avg fetch time = 6.00 ms” é para buscar a primeira linha.
select count(*) from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000 Result = 30000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 0ms Execute time = 453ms Avg fetch time = 453.00 ms Current memory = 272 263 844 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 1 048 Writes from cache to disk = 0 Fetches from cache = 60 024	Contagem de registros para a consulta anterior

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plan PLAN (ORDER_LINE INDEX (ORDER_LINE_PK)) ------ Performance info —— Prepare time = 0ms Execute time = 94ms Avg fetch time = 7.23 ms Current memory = 136 445 536 Max memory = 136 592 176 Memory buffers = 8 192 Reads from disk to cache = 150 Writes from cache to disk = 0 Fetches from cache = 2 402	Consulta à maior tabela (3,8B registros). “Avg fetch time = 7.23 ms” é para buscar a primeira linha.

	`<br>Plan<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Performance info ------<br>` `<br>Prepare time = 0ms<br>` `<br>Execute time = 3s 438ms<br>` `<br>Avg fetch time = 0.01 ms<br>` `<br>Current memory = 136 445 496<br>` `<br>Max memory = 136 592 176<br>` `<br>Memory buffers = 8 192<br>` `<br>Reads from disk to cache = 1 840<br>` `<br>Writes from cache to disk = 0<br>` `<br>Fetches from cache = 598 636<br>`

| SELECT * FROM ORDER_LINE
WHERE OL_W_ID = 500 | A mesma consulta na maior tabela (3,8B de registros), mas desta vez buscamos todos os registros (299245 registros buscados). | | | | | select w_id, w_name, c_id, c_last
from WAREHOUSE, customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000) | Plano
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Informações de desempenho ——
Prepare time = 0ms
Execute time = 125ms
Avg fetch time = 9.62 ms
Current memory = 272 270 824
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 91
Writes from cache to disk = 0
Fetches from cache = 3 659 | Junção de tabelas com 1240 registros e 372M de registros. | | select count(*)
from WAREHOUSE, customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000)
Result = 59 970 000 | Plano
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Informações de desempenho ——
Prepare time = 0ms
Execute time = 13m 4s 718ms
Avg fetch time = 784 718.00 ms
Current memory = 272 268 532
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 2 332 583
Writes from cache to disk = 0
Fetches from cache = 119 977 902 | Contagem de registros da consulta anterior |

Resumo

Neste experimento o Firebird apresenta os seguintes resultados

Capacidade indiscutível de lidar com grandes bancos de dados. Temos bastante certeza de que é possível criar e usar um banco de dados de 32 Tb em hardware apropriado, e o Firebird apresentará o mesmo alto desempenho que apresenta para bancos de dados menores (ou seja, 1Tb e abaixo).
Boa escalabilidade e footprint surpreendentemente pequeno. O banco de dados de 1Tb foi criado em um computador desktop comum e, mais importante, pode ser usado para executar consultas gerais: se você não buscar milhões de registros, a velocidade da consulta é a mesma que para bancos de dados de tamanho moderado (10-15Gb).

Este não é o fim deste experimento: pretendemos executar algumas consultas, coletar estatísticas adicionais e publicar um relatório mais detalhado em breve. Fique ligado.

Contatos

Envie todas as suas dúvidas e perguntas para [email protected]