Alexey Kovyazin, 26-maj-2014

Kilka dni temu opublikowaliśmy artykuł o naszych testach, poświęcony związkowi między wydajnością Firebird a wzrostem baz danych, w którym przetestowaliśmy (między innymi) bazę danych Firebird SQL o rozmiarze 1,7 terabajta. Tutaj znajdziesz więcej szczegółów na temat tak dużej bazy danych.

Tabele

W tabeli 1 znajdziesz listę tabel z kluczowymi charakterystykami. Statystyki te zostały pobrane za pomocą gstat -a -r i zinterpretowane przy użyciu naszego narzędzia IBAnalyst.

Tabela	Rekordy	Długość rekordu, bajty	Strony danych	Rozmiar tabeli, Mb	Rozmiar indeksów, Mb	Udział, %
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. Tabele i ich główne parametry w bazie danych Firebird SQL o rozmiarze 1,7 terabajta

Jak widać, największą tabelą jest ORDER_LINE - zawiera ona ponad 6 miliardów rekordów. Jej rozmiar to około 600 GB, a indeksy dla tej tabeli zajmują 50 GB. Tabela ta zajmuje 34% bazy danych.

Tabela STOCK jest również bardzo duża - zawiera około 2 miliardów rekordów. Mimo że liczba rekordów jest mniejsza niż w tabeli ORDER_LINE, STOCK zajmuje ~680 GB w bazie danych (39%), ponieważ długość rekordu w STOCK wynosi 298.88 bajtów, podczas gdy w ORDER_LINE tylko 60.09 bajtów. W związku z tym rozmiar powiązanych indeksów dla STOCK to tylko 15 GB.

Trzecia co do wielkości tabela, CUSTOMER, ma jeszcze większą długość rekordu - 577.52 bajtów i zajmuje 378 GB (21%) przy zaledwie 630 milionach rekordów.

Indeksy

W tej bazie danych nie ma wielu indeksów, ponieważ została zaprojektowana tylko do testów - większość tabel ma tylko 1 indeks - klucz główny. W rzeczywistych aplikacjach programiści tworzą wiele indeksów, aby obsłużyć konkretne zapytania użytkowników, ale tutaj indeksy są skoncentrowane na jak najszybszych operacjach wstawiania i aktualizacji danych - w rezultacie rozmiar indeksów to tylko 5-10% rozmiaru tabeli, podczas gdy zwykle wynosi 30-50% (jeśli rozmiar indeksów przekracza 50% rozmiaru tabeli, rozważ przeprojektowanie schematu bazy danych lub, być może, usunięcie bezużytecznych lub nieefektywnych indeksów).

W tabeli 2 znajdziesz listę wszystkich indeksów w tej bazie danych oraz ich kluczowe charakterystyki, pobrane ze statystyk gstat i przeanalizowane przez IBAnalyst:

Indeks	Tabela	Głębokość	Liczba kluczy	Długość klucza, bajty	Liczba unikalnych kluczy	Rozmiar, 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. Indeksy bazy danych Firebird 1.7 TB

Jak widać, wszystkie indeksy mają bardzo mały rozmiar klucza - największy to 1.41, co oznacza, że indeks jest bardzo skutecznie skompresowany - na przykład klucz główny dla ORDER_LINE zawiera 6 miliardów kluczy w 50 GB przestrzeni. To bardzo dobry wynik.

Istnieją 2 indeksy o głębokości = 4. Oznacza to, że silnik musi wykonać 4 odczyty stron indeksu, aby znaleźć żądaną wartość. Zaleca się, aby głębokość indeksu nie była większa niż 3, a jeśli jest większa, należy zwiększyć rozmiar strony bazy danych. Jednak w tej bazie danych mamy już maksymalny rozmiar strony (16 KB), więc musimy z tym żyć.

Podgrzewanie

Zanim przejdziemy do zapytań, wykonajmy „podgrzewanie”. Gdy baza danych jest duża, dane systemowe powiązane z tabelami również są duże, a Firebird potrzebuje czasu na buforowanie niezbędnych stron systemowych. Na przykład tabela ORDER_LINE ma 10110 stron wskaźnikowych. Podgrzewanie jest konieczne dla każdej dużej bazy danych.

Bez podgrzewania pierwsze wykonanie zapytań zajmie znacznie więcej czasu niż z podgrzewaniem.

Aby podgrzać bufor bazy danych, wykonajmy serię prostych zapytań, aby załadować dane systemowe do bufora:

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 wystarczy dla tak prostego schematu danych. W przypadku dużych i złożonych baz danych z setkami tabel schemat podgrzewania może być znacznie bardziej skomplikowany i powinien być starannie opracowany.

W rezultacie zużycie pamięci procesu Firebird (Firebird SuperServer 2.5.2 64-bit) rośnie:

Rysunek 1. Podgrzewanie bufora bazy danych

Zapytania

Po podgrzaniu możemy uruchomić kilka typowych zapytań, aby oszacować, jak użytkownicy będą pracować z tak dużą bazą danych Firebird: jakie będą czasy odpowiedzi dla typowych zapytań i operacji odczytu.

Zapytanie	Plan i statystyki	Opis
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	Połączenie tabeli WAREHOUSE (21000 rekordów) z CUSTOMER (630 milionów rekordów) z warunkiem dla konkretnego magazynu
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	Zliczanie rekordów dla poprzedniego zapytania.
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	Zapytanie do największej tabeli ORDER_LINE (~6 miliardów rekordów) z warunkiem wyboru rekordów order_line dla określonego identyfikatora magazynu. Ponieważ klucz główny ORDER_LINE_PK jest złożony i zawiera identyfikator magazynu (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), jest skutecznie używany w tym zapytaniu.
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	Zliczanie dla poprzedniego zapytania. Drugie zapytanie z tymi samymi parametrami będzie znacznie szybsze, ale zapytania z różnymi parametrami pokazują podobny wynik.
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	Zapytanie z połączeniem i 2 warunkami.
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	Zapytanie pokazujące szczegóły dla konkretnego klienta w konkretnym magazynie i dzielnicy. Demonstruje wydajność bardziej złożonych połączeń.

Podsumowanie

Jak widać, baza danych Firebird o rozmiarze 1,7 terabajta wykazuje całkiem dobrą wydajność dla typowych zapytań odczytu. Jeśli zapytanie jest dobrze zaprojektowane i używa dobrych indeksów, wydajność jest dobra nawet na bardzo dużej bazie danych. Oczywiście istnieją problematyczne punkty, takie jak długi czas pobierania bardzo dużych zestawów danych i długi czas operacji zliczania (ponieważ Firebird odwiedza wszystkie strony, aby uzyskać dokładną liczbę rekordów dla konkretnego zapytania w określonej transakcji), ale te problematyczne punkty są dobrze znane doświadczonym programistom Firebird i istnieją metody projektowania bazy danych w sposób, który pozwala je obejść.

Ale co z degradacją wydajności dla operacji wstawiania i aktualizacji? Czy można ją skompensować inteligentnym dostrojeniem konfiguracji Firebird?

W następnym artykule rozważymy opcje dostrajania dla bazy danych Firebird o rozmiarze 1,7 terabajta - rozpędzimy naszego ptaka i sprawimy, że poleci.