Alexey Kovyazin, 26-mei-2014

Enkele dagen geleden hebben we een artikel over onze tests gepubliceerd, gewijd aan de relatie tussen Firebird-prestaties en databasegroei, waarin we (onder andere) de 1,7 Terabyte Firebird SQL-database hebben getest. Hier vindt u meer details over zo’n grote database.

Tabellen

In tabel 1 vindt u een lijst van tabellen met de belangrijkste kenmerken. Deze statistieken zijn verzameld met gstat -a -r en geïnterpreteerd met onze IBAnalyst-tool.

Tabel	Records	RecLength, bytes	Datapagina’s	Tabelgrootte, Mb	Indexgrootte, Mb	Totaal, %
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

Tabel 1. Tabellen en hun belangrijkste parameters in de 1,7 Terabyte Firebird SQL-database

Zoals u kunt zien, is de grootste tabel ORDER_LINE - deze bevat meer dan 6 miljard records. De grootte is ongeveer 600 GB en er is 50 GB aan indexen voor deze tabel. Deze tabel beslaat 34% van de database.

Tabel STOCK is ook erg groot - deze bevat ongeveer 2 miljard records. Hoewel het aantal records kleiner is dan in tabel ORDER_LINE, neemt STOCK ongeveer 680 GB in de database in beslag (39%), omdat de recordlengte in STOCK 298,88 bytes is tegenover slechts 60,09 bytes in ORDER_LINE. Dienovereenkomstig is de grootte van de bijbehorende indexen voor STOCK slechts 15 GB.

De op twee na grootste tabel, CUSTOMER, heeft een nog grotere recordlengte - 577,52 bytes - en neemt 378 GB (21%) in beslag met slechts 630 miljoen records.

Indexen

Er zijn niet veel indexen in deze database, omdat deze alleen voor tests is ontworpen - de meeste tabellen hebben slechts 1 index - de primaire sleutel. In praktijksituaties maken ontwikkelaars veel indexen om aan specifieke gebruikersverzoeken te voldoen, maar hier zijn de indexen gericht op de snelste inserts en updates van de gegevens - als gevolg daarvan is de indexgrootte slechts 5-10% van de tabelgrootte, terwijl dit meestal 30-50% is (als u indexen heeft die meer dan 50% van de tabelgrootte beslaan, overweeg dan een herontwerp van het databaseschema, of verwijder mogelijk nutteloze of ineffectieve indexen).

In tabel 2 vindt u de lijst van alle indexen in deze database en hun belangrijkste kenmerken, afkomstig uit gstat-statistieken en geanalyseerd door IBAnalyst:

Index	Tabel	Diepte	Sleutels #	Sleutellengte, bytes	# unieke sleutels	Grootte, 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

Tabel 2. Indexen van de Firebird 1,7 TB-database

Zoals u kunt zien, hebben alle indexen een zeer kleine sleutelgrootte - de grootste is 1,41, wat betekent dat de index zeer effectief is gecomprimeerd - bijvoorbeeld de primaire sleutel voor ORDER_LINE bevat 6 miljard sleutels in 50 GB ruimte. Dit is een zeer goed resultaat.

Er zijn 2 indexen met een diepte van 4. Dit betekent dat de engine 4 leesbewerkingen van indexpagina’s moet uitvoeren om de gevraagde waarde te vinden. Het wordt aanbevolen dat de indexdiepte niet hoger is dan 3, en als deze hoger is, moet de databasepaginagrootte worden vergroot. We hebben echter al de maximale paginagrootte in deze database (16 KB), dus we moeten ermee leven.

Opwarmen

Voordat we verdergaan met queries, laten we eerst “opwarmen” uitvoeren. Wanneer de database groot is, zijn ook de systeemgegevens die bij tabellen horen groot, en het kost Firebird enige tijd om de benodigde systeempagina’s te cachen. Tabel ORDER_LINE heeft bijvoorbeeld 10110 pointerpagina’s. Opwarmen is noodzakelijk voor elke grote database.

Zonder opwarmen duurt de eerste uitvoering van queries aanzienlijk langer dan met opwarmen.

Om de databasecache op te warmen, voeren we een reeks eenvoudige queries uit om systeemgegevens in de cache te laden:

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;

Dit is voldoende voor zo’n eenvoudig gegevensschema. Voor grote en complexe databases met honderden tabellen kan het opwarmschema veel complexer zijn en moet het zorgvuldig worden ontwikkeld.

Als gevolg daarvan groeit het geheugengebruik van het Firebird-proces (Firebird SuperServer 2.5.2 64 bit):

Figuur 1. Opwarmen van de databasecache

Queries

Na het opwarmen kunnen we verschillende typische queries uitvoeren om te schatten hoe gebruikers met zo’n grote Firebird-database zullen werken: wat zullen de responstijden zijn voor typische queries en leesbewerkingen.

Query	Plan en statistieken	Beschrijving
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 van tabel WAREHOUSE (21000 records) met CUSTOMER (630 miljoen records), met een voorwaarde voor een specifiek magazijn
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	Tel records voor de vorige query.
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 op de grootste tabel ORDER_LINE (~6 miljard records) met een voorwaarde om order_line-records voor een opgegeven magazijn-ID te selecteren. Aangezien de primaire sleutel ORDER_LINE_PK samengesteld is en het magazijn-ID bevat (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), wordt deze effectief gebruikt in deze 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	Telling voor de vorige query. De tweede query met dezelfde parameters zal veel sneller zijn, maar queries met verschillende parameters tonen een vergelijkbaar resultaat.
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 met join en 2 voorwaarden.
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 om details weer te geven voor een specifieke klant in het specifieke magazijn en district. Dit toont de prestaties van complexere joins.

Samenvatting

Zoals u kunt zien, toont de 1,7 Terabyte Firebird-database behoorlijk goede prestaties voor gangbare leesqueries. Als een query goed is ontworpen en gebruikmaakt van goede indexen, zijn de prestaties zelfs op een zeer grote database goed. Natuurlijk zijn er pijnpunten, zoals lange fetch-tijden voor zeer grote datasets en lange tijden voor count-bewerkingen (aangezien Firebird alle pagina’s bezoekt om een exact aantal records voor de specifieke query in de specifieke transactie te krijgen), maar deze pijnpunten zijn bekend bij ervaren Firebird-ontwikkelaars en er zijn methoden om de database zo te ontwerpen dat deze worden omzeild.

Maar wat betreft prestatievermindering voor inserts en updates? Is het mogelijk om dit te compenseren met slimme afstemming van de Firebird-configuratie?

In het volgende artikel zullen we afstemmingsopties voor de Firebird 1,7 terabyte database bekijken - we zullen onze vogel een boost geven en hem laten vliegen.