Alexey Kovyazin, 26-mag-2014

Qualche giorno fa abbiamo pubblicato un articolo sui nostri test, dedicato alla relazione tra le prestazioni di Firebird e la crescita dei database, dove abbiamo testato (tra gli altri) il database Firebird SQL da 1,7 Terabyte. Qui puoi trovare maggiori dettagli su un database così grande.

Tabelle

Nella tabella 1 puoi trovare l’elenco delle tabelle con le caratteristiche principali. Questa statistica è stata ottenuta con gstat -a -r e interpretata con il nostro strumento IBAnalyst.

Tabella	Record	LunghezzaRec, byte	Pagine dati	Dimensioni tabella, Mb	Dimensioni indici, Mb	Totale, %
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

Tabella 1. Tabelle e loro parametri principali nel database Firebird SQL da 1,7 Terabyte

Come puoi vedere, la tabella più grande è ORDER_LINE - contiene più di 6 miliardi di record. La sua dimensione è di circa 600Gb e ci sono 50Gb di indici per questa tabella. Questa tabella occupa il 34% del database.

Anche la tabella STOCK è molto grande - contiene circa 2 miliardi di record. Sebbene il numero di record sia inferiore rispetto alla tabella ORDER_LINE, STOCK occupa ~680Gb nel database (39%), perché la lunghezza dei record in STOCK è di 298.88 byte contro solo 60.09 byte in ORDER_LINE. Di conseguenza, la dimensione degli indici associati per STOCK è solo di 15Gb.

La terza tabella più grande, CUSTOMER, ha una lunghezza dei record ancora maggiore - 577.52 byte, e occupa 378Gb (21%) con solo 630 milioni di record.

Indici

Non ci sono molti indici in questo database, poiché è progettato solo per i test - la maggior parte delle tabelle ha solo 1 indice - la chiave primaria. Nelle applicazioni del mondo reale, gli sviluppatori creano molti indici per servire specifiche richieste degli utenti, ma qui gli indici sono concentrati sugli inserimenti e aggiornamenti più rapidi dei dati - di conseguenza, la dimensione degli indici è solo il 5-10% della dimensione delle tabelle, mentre di solito è il 30-50% (se hai indici più grandi del 50% della dimensione della tabella, considera di ridisegnare lo schema del database, o forse di eliminare indici inutili o non efficaci).

Nella tabella 2 puoi trovare l’elenco di tutti gli indici in questo database e le loro caratteristiche principali, prese dalle statistiche di gstat e analizzate da IBAnalyst:

Indice	Tabella	Profondità	Chiavi #	Lunghezza chiave, byte	# di chiavi uniche	Dimensioni, 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

Tabella 2. Indici del database Firebird da 1,7Tb

Come puoi vedere, tutti gli indici hanno una dimensione della chiave molto piccola - il massimo è 1.41, il che significa che l’indice è compresso molto efficacemente - ad esempio, la chiave primaria per ORDER_LINE contiene 6 miliardi di chiavi in 50 Gb di spazio. Questo è un ottimo risultato.

Ci sono 2 indici che hanno profondità = 4. Ciò significa che il motore deve eseguire 4 letture delle pagine dell’indice per trovare il valore richiesto. Si raccomanda che la profondità dell’indice non sia superiore a 3, e se è superiore, la dimensione della pagina del database dovrebbe essere aumentata. Tuttavia, abbiamo già la dimensione massima della pagina in questo database (16Kb), quindi dobbiamo conviverci.

Riscaldamento

Prima di continuare con le query, eseguiamo il “riscaldamento”. Quando il database è grande, anche i dati di sistema associati alle tabelle sono grandi, e Firebird impiega del tempo per memorizzare nella cache le pagine di sistema necessarie. Ad esempio, la tabella ORDER_LINE ha 10110 pagine puntatore. Il riscaldamento è necessario per qualsiasi database grande.

Senza riscaldamento, la prima esecuzione delle query richiederà significativamente più tempo rispetto a con riscaldamento.

Per riscaldare la cache del database, eseguiamo una serie di query semplici per caricare i dati di sistema nella 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;

Questo è sufficiente per uno schema dati così semplice. Per database grandi e complessi con centinaia di tabelle, lo schema di riscaldamento potrebbe essere molto più complesso e dovrebbe essere sviluppato con attenzione.

Di conseguenza, il consumo di memoria del processo Firebird (Firebird SuperServer 2.5.2 64 bit) cresce:

Figura 1. Riscaldamento della cache del database

Query

Dopo il riscaldamento possiamo eseguire diverse query tipiche per stimare come gli utenti lavoreranno con un database Firebird così grande: quali saranno i tempi di risposta per le query tipiche e le operazioni di lettura.

Query	Piano e statistiche	Descrizione
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	Join della tabella WAREHOUSE (21000 record) con CUSTOMER (630 milioni di record), con condizione per un magazzino specifico
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	Conteggio dei record per la query precedente.
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	Query alla tabella più grande ORDER_LINE (~6 miliardi di record) con condizione per selezionare i record order_line per un id di magazzino specifico. Poiché la chiave primaria ORDER_LINE_PK è composta e contiene l’ID del magazzino (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), viene utilizzata efficacemente in questa query.
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	Conteggio per la query precedente. La seconda query con gli stessi parametri sarà molto più veloce, ma query con parametri diversi mostrano risultati simili.
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	Query con join e 2 condizioni.
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	Query per mostrare i dettagli di un cliente specifico in un magazzino e distretto specifici. Dimostra le prestazioni di join più complessi.

Riepilogo

Come puoi vedere, il database Firebird da 1,7 Terabyte mostra prestazioni abbastanza buone per le query di lettura comuni. Se la query è ben progettata e utilizza buoni indici, le prestazioni sono buone anche su database molto grandi. Naturalmente, ci sono punti critici, come i tempi di recupero lunghi per set di dati molto grandi e i tempi lunghi per le operazioni di conteggio (poiché Firebird visita tutte le pagine per ottenere un numero esatto di record per la query specifica nella transazione specifica), ma questi punti critici sono ben noti agli sviluppatori Firebird esperti, e ci sono metodi per progettare il database in modo da aggirarli.

Ma che dire del degrado delle prestazioni per inserimenti e aggiornamenti? È possibile compensarlo con una messa a punto intelligente della configurazione di Firebird?

Nel prossimo articolo prenderemo in considerazione le opzioni di messa a punto per il database Firebird da 1,7 terabyte - daremo una spinta al nostro uccello e lo faremo volare.