Por que criar um banco de dados Firebird de terabyte?

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

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


## 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 foi apenas a criação de um banco de dados Firebird de 1Tb, 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 temporário)	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, 32 bits. Para realmente executar o teste, usamos o carregador do kit baseado em TPC (baixe-o em http://ibdeveloper.com/tests/tpc-c/, tanto os binários quanto os 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 mestre-detalhe-subdetalhe, não tabela por tabela.

Tabela 2: Software


Software	Versão
Sistema operacional	Windows XP Professional SP3, 32 bits
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 é maior que 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 transferência do disco (para ler/gravar 1 página em um ciclo de E/S).

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 desktop normal (temos MS Office, Firefox, IBAnalyst, etc - cerca de 8-12 programas rodavam ao mesmo tempo). Se dedicássemos 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 para carregar	~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ínimo 13 bytes, máximo - 600 bytes)
Transações	646489

Gastamos ~4 dias no carregamento, 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 Visualizador FBDataGuard:

Índices

Criamos os índices um por um e contamos o tempo de suas criações 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 da página era de 16384 bytes, então não há sobrecarga ao pesquisar 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 45 segundos.

Colocamos as informações-chave das estatísticas em uma 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 Visualizador gratuito FBDataGuard Community Edition para interpretar 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

Primeiro de tudo, executamos consultas select count(*) em várias tabelas (veja a 4ª coluna na Tabela 4 acima). Como você sabe, devido à natureza multi-versão do Firebird, select count(*) para a tabela inteira é uma operação cara para o servidor porque requer visitar cada página, e desenvolvedores experientes de Firebird não usam select count(*), mas nós o usamos para demonstrar a proporção geral de desempenho do banco de dados e 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)) ------ Informações de desempenho —— Tempo de preparação = 15ms Tempo de execução = 79ms Tempo médio de busca = 6.08 ms Memória atual = 272 264 476 Memória máxima = 272 514 048 Buffers de memória = 16 384 Leituras do disco para cache = 82 Gravações do cache para disco = 0 Buscas do cache = 3 648	Junção simples de tabelas com 12400 e 372000000 registros, sem condições WHERE. “Tempo médio de busca = 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)) ------ Informações de desempenho —— Tempo de preparação = 16ms Tempo de execução = 78ms Tempo médio de busca = 6.00 ms Memória atual = 272 266 148 Memória máxima = 272 514 048 Buffers de memória = 16 384 Leituras do disco para cache = 88 Gravações do cache para disco = 0 Buscas do cache = 3 656	Junção das mesmas tabelas com condição que força a seleção de registros recentes. “Tempo médio de busca = 6.00 ms” é para buscar a primeira linha.
select count(*) from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000 Resultado = 30000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Informações de desempenho —— Tempo de preparação = 0ms Tempo de execução = 453ms Tempo médio de busca = 453.00 ms Memória atual = 272 263 844 Memória máxima = 272 514 048 Buffers de memória = 16 384 Leituras do disco para cache = 1 048 Gravações do cache para disco = 0 Buscas do cache = 60 024	Contar registros para a consulta anterior

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plano PLAN (ORDER_LINE INDEX (ORDER_LINE_PK)) ------ Informações de desempenho —— Tempo de preparação = 0ms Tempo de execução = 94ms Tempo médio de busca = 7.23 ms Memória atual = 136 445 536 Memória máxima = 136 592 176 Buffers de memória = 8 192 Leituras do disco para cache = 150 Gravações do cache para disco = 0 Buscas do cache = 2 402	Consulta à maior tabela (3.8B registros). “Tempo médio de busca = 7.23 ms” é para buscar a primeira linha.

	`<br>Plano<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Informações de desempenho ------<br>` `<br>Tempo de preparação = 0ms<br>` `<br>Tempo de execução = 3s 438ms<br>` `<br>Tempo médio de busca = 0.01 ms<br>` `<br>Memória atual = 136 445 496<br>` `<br>Memória máxima = 136 592 176<br>` `<br>Buffers de memória = 8 192<br>` `<br>Leituras do disco para cache = 1 840<br>` `<br>Gravações do cache para disco = 0<br>` `<br>Buscas do cache = 598 636<br>`

| SELECT * FROM ORDER_LINE
WHERE OL_W_ID = 500 | A mesma consulta para a maior tabela (3,8 bilhões de registros), mas desta vez buscamos todos os registros (299.245 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) | Plan
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Informações de desempenho ——
Tempo de preparação = 0ms
Tempo de execução = 125ms
Tempo médio de busca = 9,62 ms
Memória atual = 272 270 824
Memória máxima = 272 514 048
Buffers de memória = 16 384
Leituras do disco para o cache = 91
Gravações do cache para o disco = 0
Buscas do cache = 3 659 | Junção de tabelas com 1.240 registros e 372 milhões de registros. | | select count(*)
from WAREHOUSE, customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000)
Resultado = 59 970 000 | Plan
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Informações de desempenho ——
Tempo de preparação = 0ms
Tempo de execução = 13m 4s 718ms
Tempo médio de busca = 784 718,00 ms
Memória atual = 272 268 532
Memória máxima = 272 514 048
Buffers de memória = 16 384
Leituras do disco para o cache = 2 332 583
Gravações do cache para o disco = 0
Buscas do cache = 119 977 902 | Contagem de registros para a consulta anterior |

Resumo

Neste experimento, o Firebird apresenta os seguintes resultados:

Capacidade indiscutível de lidar com bancos de dados grandes. Temos certeza de que é possível criar e usar um banco de dados de 32 Tb em hardware adequado, e o Firebird apresentará o mesmo alto desempenho que mostra para bancos de dados menores (ou seja, 1 Tb ou menos).
Boa escalabilidade e pegada surpreendentemente pequena. Um banco de dados de 1 Tb 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-15 Gb).

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

Contatos

Envie todas as suas perguntas e consultas para [email protected]