Alexey Kovyazin, 26-Maio-2014

Há alguns dias publicamos um artigo sobre nossos testes, dedicado à relação entre o desempenho do Firebird e o crescimento de bancos de dados, onde testamos (entre outros) o banco de dados Firebird SQL de 1,7 Terabytes. Aqui você pode encontrar mais detalhes sobre um banco de dados tão grande.

Tabelas

Na tabela 1 você pode encontrar a lista de tabelas com as principais características. Esta estatística foi obtida com gstat -a -r e interpretada com nossa ferramenta IBAnalyst.

Tabela	Registros	TamanhoReg, bytes	Páginas de Dados	Tamanho da Tabela, Mb	Tamanho dos Índices, Mb	Total, %
ORDER_LINE	6300024797	60.09	38880344	607505.3	50582.3	34
STOCK	2100000000	298.88	43783806	684121.9	15809.61	39
ORDERS	630004141	29.00	2692324	42067.56	4250.38	2
HISTORY	630000434	48.77	3446455	53850.86	0.00	3
CUSTOMER	630000000	577.52	24226054	378532.0	8675.78	21
NEW_ORDER	188999981	13.00	623764	9746.31	1260.84	1
DISTRICT	210000	103.92	1860	29.06	1.27	~0
ITEM	100000	82.73	756	11.81	0.52	~0
WAREHOUSE	21000	97.90	179	2.80	0.11	~0

Tabela 1. Tabelas e seus principais parâmetros no banco de dados Firebird SQL de 1,7 Terabytes

Como você pode ver, a maior tabela é ORDER_LINE - ela contém mais de 6 bilhões de registros. Seu tamanho é de cerca de 600Gb e há 50Gb de índices para esta tabela. Esta tabela ocupa 34% do banco de dados.

A tabela STOCK também é muito grande - contém cerca de 2 bilhões de registros. Embora o número de registros seja menor que na tabela ORDER_LINE, STOCK ocupa ~680Gb no banco de dados (39%), porque o tamanho do registro em STOCK é de 298.88 bytes contra apenas 60.09 bytes em ORDER_LINE. Consequentemente, o tamanho dos índices associados para STOCK é de apenas 15Gb.

A terceira maior tabela, CUSTOMER, tem um tamanho de registro ainda maior - 577.52 bytes, e ocupa 378Gb (21%) com apenas 630 milhões de registros.

Índices

Não há muitos índices neste banco de dados, pois ele foi projetado apenas para testes - a maioria das tabelas tem apenas 1 índice - a chave primária. Em aplicações do mundo real, os desenvolvedores criam muitos índices para atender a solicitações específicas dos usuários, mas aqui os índices estão concentrados nas inserções e atualizações mais rápidas dos dados - como resultado, o tamanho dos índices é de apenas 5-10% do tamanho da tabela, enquanto normalmente é de 30-50% (se você tiver um tamanho de índice superior a 50% do tamanho da tabela, considere redesenhar o esquema do banco de dados, ou talvez remover índices inúteis ou ineficazes).

Na tabela 2 você pode encontrar a lista de todos os índices neste banco de dados e suas principais características, obtidas das estatísticas do gstat e analisadas pelo IBAnalyst:

Índice	Tabela	Profundidade	Chaves #	Tamanho da Chave, bytes	# de chaves únicas	Tamanho, Mb
ORDER_LINE_PK	ORDER_LINE	4	6300024797	1.41	6300024797	50582.34
STOCK_PK	STOCK	4	2100000000	1.00	2100000000	15809.61
ORDERS_PK	ORDERS	3	630004141	1.01	630004141	4250.38
CUSTOMER_LAST	CUSTOMER	3	630000000	0.00	1000	4029.64
CUSTOMER_PK	CUSTOMER	3	630000000	1.01	188999981	1260.84
NEW_ORDER_PK	NEW_ORDER	3	188999981	1.01	188999981	1260.84
DISTRICT_PK	DISTRICT	2	210000	1.37	210000	1.27
ITEM_PK	ITEM	2	100000	1.00	100000	0.52
WAREHOUSE_PK	WAREHOUSE	2	21000	1.00	21000	0.11

Tabela 2. Índices do banco de dados Firebird de 1,7Tb

Como você pode ver, todos os índices têm um tamanho de chave muito pequeno - o maior é 1.41, o que significa que o índice é comprimido de forma muito eficaz - por exemplo, a chave primária de ORDER_LINE contém 6 bilhões de chaves em 50 Gb de espaço. Este é um resultado muito bom.

Há 2 índices que têm profundidade = 4. Isso significa que o mecanismo precisa realizar 4 leituras de páginas de índice para encontrar o valor solicitado. É recomendado que a profundidade do índice não seja superior a 3, e se for maior, o tamanho da página do banco de dados deve ser aumentado. No entanto, já temos o tamanho máximo de página neste banco de dados (16Kb), então precisamos conviver com isso.

Aquecimento

Antes de continuarmos com as consultas, vamos realizar o “aquecimento”. Quando o banco de dados é grande, os dados do sistema, associados às tabelas, também são grandes, e leva algum tempo para o Firebird armazenar em cache as páginas de sistema necessárias. Por exemplo, a tabela ORDER_LINE tem 10110 páginas de ponteiro. O aquecimento é necessário para qualquer banco de dados grande.

Sem o aquecimento, a primeira execução das consultas levará significativamente mais tempo do que com o aquecimento.

Para aquecer o cache do banco de dados, vamos executar uma série de consultas simples para carregar os dados do sistema no cache:

select first 1 * from ORDER_LINE; select first 1 * from STOCK; select first 1 * from customer; select first 1 * from ORDERS; select first 1 * from ITEM;

Isso é suficiente para um esquema de dados tão simples. Para bancos de dados grandes e complexos com centenas de tabelas, o esquema de aquecimento pode ser muito mais complexo e deve ser cuidadosamente desenvolvido.

Como resultado, o consumo de memória do processo do Firebird (Firebird SuperServer 2.5.2 64 bits) cresce:

Figura 1. Aquecimento do cache do banco de dados

Consultas

Após o aquecimento, podemos executar várias consultas típicas para estimar como os usuários trabalharão com um banco de dados Firebird tão grande: quais serão os tempos de resposta para consultas típicas e operações de leitura.

Consulta	Plano e estatísticas	Descrição
select first 10 w_id, w_name, c_id, c_last<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE NATURAL,<br> CUSTOMER INDEX (CUSTOMER_PK))<br> STATISTICS<br> Current memory = 166919744<br> Delta memory = 5616<br> Max memory = 166977488<br> Elapsed time= 0.08 sec<br> Buffers = 10000<br> Reads = 1<br> Writes 0<br> Fetches = 51	Junção da tabela WAREHOUSE (21000 registros) com CUSTOMER (630 milhões de registros), com condição para um armazém específico
select count(*)<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> COUNT<br> ============<br> 30000<br> <br> Current memory = 166918560<br> Delta memory = -1184<br> Max memory = 166977488<br> Elapsed time= 0.16 sec<br> Buffers = 10000<br> Reads = 1175<br> Writes 0<br> Fetches = 60040	Contagem de registros para a consulta anterior.
SELECT first 10 *<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050	PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> STATISTICS<br> Current memory = 167013640<br> Delta memory = 95080<br> Max memory = 167055968<br> Elapsed time= 0.80 sec<br> Buffers = 10000<br> Reads = 10285<br> Writes 0<br> Fetches = 10424	Consulta à maior tabela ORDER_LINE (~6 bilhões de registros) com condição para selecionar registros de order_line para um ID de armazém específico. Como a chave primária ORDER_LINE_PK é composta e contém o ID do armazém (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), ela é usada de forma eficaz nesta consulta.
SELECT count(*)<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050;	PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> <br> COUNT<br> ============<br> 299509<br> Current memory = 167125464<br> Delta memory = -7160<br> Max memory = 167267976<br> Elapsed time= 3.41 sec<br> Buffers = 10000<br> Reads = 1994<br> Writes 0<br> Fetches = 599170	Contagem para a consulta anterior. A segunda consulta com os mesmos parâmetros será muito mais rápida, mas consultas com parâmetros diferentes mostram resultado semelhante.
Select first 10 w_id, w_name, c_id, c_last<br> rom WAREHOUSE, CUSTOMER<br> where c_w_id = w_id and (c_w_id > 8000)<br> and (c_w_id < 10000)	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> <br> STATISTICS<br> Current memory = 167687664<br> Delta memory = -651888<br> Max memory = 168931400<br> Elapsed time= 0.19 sec<br> Buffers = 10000<br> Reads = 31<br> Writes 0<br> Fetches = 63	Consulta com junção e 2 condições.
select first 10 c1.C_ID, c1.C_FIRST, c1.C_LAST, o1.O_ID,<br> o1.O_OL_CNT, i1.I_NAME, i1.I_PRICE, ol1.OL_AMOUNT<br> from customer c1<br> join orders o1 on (c1.c_w_id = o1.O_w_ID<br> and c1.C_D_ID = o1.O_D_ID and c1.C_ID = o1.O_C_ID)<br> join ORDER_LINE ol1 on (ol1.ol_w_id = c1.C_W_ID<br> and ol1.OL_D_ID = c1.C_D_ID<br> and ol1.OL_O_ID = o1.O_ID)<br> join Item i1 on (ol1.OL_I_ID = i1.i_id)<br> where o1.o_d_id = 1 and o1.o_w_id = 1 and c1.c_id = 10;	PLAN JOIN (C1 INDEX (CUSTOMER_PK),<br> O1 INDEX (ORDERS_PK),<br> OL1 INDEX (ORDER_LINE_PK),<br> I1 INDEX (ITEM_PK))<br> <br> STATISTICS<br> Current memory = 166807128<br> Delta memory = 6016<br> Max memory = 166879200<br> Elapsed time= 0.20 sec<br> Buffers = 9999<br> Reads = 1<br> Writes 0<br> Fetches = 4960	Consulta para mostrar detalhes de um cliente específico em um armazém e distrito específicos. Ela demonstra o desempenho de junções mais complexas.

Resumo

Como você pode ver, o banco de dados Firebird de 1,7 Terabytes mostra um desempenho bastante bom para consultas de leitura comuns. Se a consulta for bem projetada e usar bons índices, o desempenho é bom mesmo em um banco de dados muito grande. É claro que há pontos problemáticos, como o longo tempo de busca para conjuntos de dados muito grandes e o longo tempo para operações de contagem (já que o Firebird visita todas as páginas para obter um número exato de registros para a consulta específica na transação específica), mas esses pontos problemáticos são bem conhecidos por desenvolvedores experientes de Firebird, e existem métodos para projetar o banco de dados de forma a contorná-los.

Mas e quanto à degradação do desempenho para inserções e atualizações? É possível compensá-la com um ajuste inteligente da configuração do Firebird?

No próximo artigo, consideraremos opções de ajuste para o banco de dados Firebird de 1,7 terabyte - vamos turbinar nosso pássaro e fazê-lo voar.