Alexey Kovyazin, 26-květen-2014

Před několika dny jsme zveřejnili článek o našich testech, věnovaný vztahu mezi výkonem Firebirdu a růstem databází, kde jsme testovali (mimo jiné) 1,7 terabajtovou Firebird SQL databázi. Zde najdete více podrobností o tak velké databázi.

Tabulky

V tabulce 1 najdete seznam tabulek s klíčovými charakteristikami. Tato statistika byla získána pomocí gstat -a -r a interpretována naším nástrojem IBAnalyst.

Tabulka	Záznamy	Délka záznamu, bajty	Datové stránky	Velikost tabulky, Mb	Velikost indexů, Mb	Celkem, %
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

Tabulka 1. Tabulky a jejich hlavní parametry v 1,7 terabajtové Firebird SQL databázi

Jak vidíte, největší tabulkou je ORDER_LINE - obsahuje více než 6 miliard záznamů. Její velikost je asi 600 GB a na indexy pro tuto tabulku připadá 50 GB. Tato tabulka zabírá 34 % databáze.

Tabulka STOCK je také velmi velká - obsahuje přibližně 2 miliardy záznamů. Přestože je počet záznamů menší než v tabulce ORDER_LINE, STOCK zabírá v databázi přibližně 680 GB (39 %), protože délka záznamu v STOCK je 298.88 bajtů oproti pouze 60.09 bajtům v ORDER_LINE. Velikost souvisejících indexů pro STOCK je proto pouze 15 GB.

Třetí největší tabulka, CUSTOMER, má ještě větší délku záznamu - 577.52 bajtů, a zabírá 378 GB (21 %) s pouhými 630 miliony záznamů.

Indexy

V této databázi není mnoho indexů, protože je navržena pouze pro testy - většina tabulek má pouze 1 index - primární klíč. V reálných aplikacích vývojáři vytvářejí mnoho indexů, aby obsloužili konkrétní požadavky uživatelů, ale zde jsou indexy zaměřeny na nejrychlejší vkládání a aktualizace dat - výsledkem je, že velikost indexů je pouze 5-10 % velikosti tabulky, zatímco obvykle to bývá 30-50 % (pokud máte velikost indexů větší než 50 % velikosti tabulky, zvažte redesign databázového schématu, nebo možná odstraňte zbytečné či neefektivní indexy).

V tabulce 2 najdete seznam všech indexů v této databázi a jejich klíčové charakteristiky, převzaté ze statistik gstat a analyzované nástrojem IBAnalyst:

Index	Tabulka	Hloubka	Počet klíčů #	Délka klíče, bajty	Počet unikátních klíčů #	Velikost, 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

Tabulka 2. Indexy Firebird 1.7Tb databáze

Jak vidíte, všechny indexy mají velmi malou velikost klíče - největší je 1.41, což znamená, že index je velmi efektivně komprimován - například primární klíč pro ORDER_LINE obsahuje 6 miliard klíčů v 50 GB prostoru. To je velmi dobrý výsledek.

Existují 2 indexy s hloubkou = 4. To znamená, že engine musí provést 4 čtení indexových stránek, aby našel požadovanou hodnotu. Doporučuje se, aby hloubka indexu nebyla vyšší než 3, a pokud je vyšší, měla by být zvýšena velikost stránky databáze. My však již máme v této databázi maximální velikost stránky (16 KB), takže s tím musíme žít.

Zahřívání

Než budeme pokračovat s dotazy, provedeme „zahřívání“. Když je databáze velká, systémová data spojená s tabulkami jsou také velká a Firebirdu trvá určitou dobu, než uloží potřebné systémové stránky do mezipaměti. Například tabulka ORDER_LINE má 10110 ukazatelových stránek. Zahřívání je nezbytné pro každou velkou databázi.

Bez zahřívání bude první provedení dotazů trvat výrazně déle než se zahříváním.

Pro zahřátí mezipaměti databáze provedeme sérii jednoduchých dotazů pro načtení systémových dat do mezipaměti:

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;

To stačí pro takové jednoduché datové schéma. Pro velké a komplexní databáze se stovkami tabulek může být schéma zahřívání mnohem složitější a mělo by být pečlivě navrženo.

Výsledkem je, že spotřeba paměti procesu Firebird (Firebird SuperServer 2.5.2 64 bit) roste:

Obrázek 1. Zahřívání mezipaměti databáze

Dotazy

Po zahřívání můžeme spustit několik typických dotazů, abychom odhadli, jak budou uživatelé pracovat s tak velkou Firebird databází: jaké budou doby odezvy pro typické dotazy a čtecí operace.

Dotaz	Plán a statistika	Popis
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	Spojení tabulky WAREHOUSE (21000 záznamů) s CUSTOMER (630 milionů záznamů) s podmínkou pro konkrétní sklad
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	Počet záznamů pro předchozí dotaz.
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	Dotaz na největší tabulku ORDER_LINE (~6 miliard záznamů) s podmínkou pro výběr záznamů order_line pro zadané ID skladu. Protože primární klíč ORDER_LINE_PK je složený a obsahuje ID skladu (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), je v tomto dotazu efektivně použit.
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	Počet pro předchozí dotaz. Druhý dotaz se stejnými parametry bude mnohem rychlejší, ale dotazy s různými parametry ukazují podobný výsledek.
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	Dotaz se spojením a 2 podmínkami.
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	Dotaz pro zobrazení podrobností o konkrétním zákazníkovi v konkrétním skladu a okrese. Demonstruje výkon složitějších spojení.

Shrnutí

Jak vidíte, 1,7 terabajtová Firebird databáze vykazuje docela dobrý výkon pro běžné čtecí dotazy. Pokud je dotaz dobře navržen a používá dobré indexy, výkon je dobrý i na velmi velké databázi. Samozřejmě existují problematická místa, jako je dlouhá doba načítání pro velmi velké datové sady a dlouhá doba pro operace count (protože Firebird navštěvuje všechny stránky, aby získal přesný počet záznamů pro konkrétní dotaz v konkrétní transakci), ale tato problematická místa jsou dobře známá zkušeným vývojářům Firebirdu a existují metody, jak navrhnout databázi tak, aby se jim vyhnuli.

Ale co degradace výkonu pro vkládání a aktualizace? Je možné ji kompenzovat chytrou konfigurací Firebirdu?

V příštím článku se podíváme na možnosti ladění pro 1,7 terabajtovou Firebird databázi - posílíme našeho ptáka a necháme ho létat.