Alexey Kovyazin, 26-Mai-2014

Vor einigen Tagen haben wir einen Artikel über unsere Tests veröffentlicht, der sich mit dem Zusammenhang zwischen Firebird-Leistung und Datenbankwachstum befasst, in dem wir (unter anderem) die 1,7 Terabyte große Firebird SQL Datenbank getestet haben. Hier finden Sie weitere Details zu einer so großen Datenbank.

Tabellen

In Tabelle 1 finden Sie eine Liste der Tabellen mit den wichtigsten Merkmalen. Diese Statistiken wurden mit gstat -a -r erfasst und mit unserem IBAnalyst-Tool interpretiert.

Tabelle	Datensätze	Satzlänge, Bytes	Daten-Seiten	Tabellengröße, Mb	Indexgröße, Mb	Gesamt, %
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

Tabelle 1. Tabellen und ihre Hauptparameter in der 1,7 Terabyte Firebird SQL Datenbank

Wie Sie sehen können, ist die größte Tabelle ORDER_LINE - sie enthält mehr als 6 Milliarden Datensätze. Ihre Größe beträgt etwa 600 GB und es gibt 50 GB an Indizes für diese Tabelle. Diese Tabelle belegt 34 % der Datenbank.

Die Tabelle STOCK ist ebenfalls sehr groß - sie enthält etwa 2 Milliarden Datensätze. Obwohl die Anzahl der Datensätze geringer ist als in der Tabelle ORDER_LINE, belegt STOCK etwa 680 GB in der Datenbank (39 %), da die Satzlänge in STOCK 298,88 Bytes beträgt, verglichen mit nur 60,09 Bytes in ORDER_LINE. Dementsprechend beträgt die Größe der zugehörigen Indizes für STOCK nur 15 GB.

Die drittgrößte Tabelle, CUSTOMER, hat eine noch größere Satzlänge - 577,52 Bytes - und belegt 378 GB (21 %) mit nur 630 Millionen Datensätzen.

Indizes

Es gibt nicht viele Indizes in dieser Datenbank, da sie nur für Tests konzipiert ist - die Mehrheit der Tabellen hat nur einen Index - den Primärschlüssel. In realen Anwendungen erstellen Entwickler viele Indizes, um bestimmte Benutzeranfragen zu bedienen, aber hier sind die Indizes auf die schnellsten Einfügungen und Aktualisierungen der Daten konzentriert - als Ergebnis beträgt die Indexgröße nur 5-10 % der Tabellengröße, während sie normalerweise 30-50 % beträgt (wenn Sie eine Indexgröße von mehr als 50 % der Tabellengröße haben, sollten Sie ein Redesign des Datenbankschemas in Betracht ziehen oder möglicherweise nutzlose oder ineffektive Indizes entfernen).

In Tabelle 2 finden Sie die Liste aller Indizes in dieser Datenbank und ihre wichtigsten Merkmale, entnommen aus der gstat-Statistik und analysiert mit IBAnalyst:

Index	Tabelle	Tiefe	Schlüssel #	Schlüssellänge, Bytes	# eindeutiger Schlüssel	Größe, 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

Tabelle 2. Indizes der Firebird 1,7 TB Datenbank

Wie Sie sehen können, haben alle Indizes eine sehr kleine Schlüsselgröße - der größte ist 1,41, was bedeutet, dass der Index sehr effektiv komprimiert ist - zum Beispiel enthält der Primärschlüssel für ORDER_LINE 6 Milliarden Schlüssel in 50 GB Speicherplatz. Das ist ein sehr gutes Ergebnis.

Es gibt 2 Indizes mit einer Tiefe von 4. Das bedeutet, dass die Engine 4 Lesevorgänge von Indexseiten durchführen muss, um den gewünschten Wert zu finden. Es wird empfohlen, dass die Indextiefe nicht höher als 3 sein sollte, und wenn sie höher ist, sollte die Datenbankseitengröße erhöht werden. Wir haben jedoch bereits die maximale Seitengröße in dieser Datenbank (16 KB), also müssen wir damit leben.

Aufwärmen

Bevor wir mit den Abfragen fortfahren, führen wir ein „Aufwärmen“ durch. Wenn die Datenbank groß ist, sind auch die Systemdaten, die mit den Tabellen verbunden sind, groß, und es dauert einige Zeit, bis Firebird die notwendigen Systemseiten zwischenspeichert. Zum Beispiel hat die Tabelle ORDER_LINE 10110 Zeigerseiten. Das Aufwärmen ist für jede große Datenbank notwendig.

Ohne Aufwärmen dauert die erste Ausführung von Abfragen erheblich länger als mit Aufwärmen.

Um den Datenbank-Cache aufzuwärmen, führen wir eine Reihe einfacher Abfragen durch, um Systemdaten in den Cache zu 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;

Dies reicht für ein so einfaches Datenschema aus. Für große und komplexe Datenbanken mit Hunderten von Tabellen könnte das Aufwärmschema viel komplexer sein und sollte sorgfältig entwickelt werden.

Als Ergebnis wächst der Speicherverbrauch des Firebird-Prozesses (Firebird SuperServer 2.5.2 64 Bit):

Abbildung 1. Aufwärmen des Datenbank-Caches

Abfragen

Nach dem Aufwärmen können wir mehrere typische Abfragen ausführen, um abzuschätzen, wie Benutzer mit einer so großen Firebird-Datenbank arbeiten werden: Wie werden die Antwortzeiten für typische Abfragen und Leseoperationen sein?

Abfrage	Plan und Statistiken	Beschreibung
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 der Tabelle WAREHOUSE (21000 Datensätze) mit CUSTOMER (630 Millionen Datensätze), mit Bedingung für ein bestimmtes Lager
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	Zählt die Datensätze für die vorherige Abfrage.
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	Abfrage auf die größte Tabelle ORDER_LINE (~6 Milliarden Datensätze) mit der Bedingung, order_line-Datensätze für eine bestimmte Lager-ID auszuwählen. Da der Primärschlüssel ORDER_LINE_PK zusammengesetzt ist und die Lager-ID enthält (CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), wird er in dieser Abfrage effektiv verwendet.
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	Zählt für die vorherige Abfrage. Die zweite Abfrage mit denselben Parametern wird viel schneller sein, aber Abfragen mit anderen Parametern zeigen ein ähnliches Ergebnis.
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	Abfrage mit Join und 2 Bedingungen.
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	Abfrage zur Anzeige von Details für einen bestimmten Kunden in einem bestimmten Lager und Bezirk. Sie demonstriert die Leistung komplexerer Joins.

Zusammenfassung

Wie Sie sehen können, zeigt die 1,7 Terabyte große Firebird-Datenbank eine ziemlich gute Leistung für allgemeine Leseabfragen. Wenn die Abfrage gut gestaltet ist und gute Indizes verwendet, ist die Leistung auch bei sehr großen Datenbanken gut. Natürlich gibt es Problemstellen, wie lange Abrufzeiten für sehr große Datensätze und lange Zeiten für Zähloperationen (da Firebird alle Seiten besucht, um eine genaue Anzahl von Datensätzen für die jeweilige Abfrage in der spezifischen Transaktion zu erhalten), aber diese Problemstellen sind erfahrenen Firebird-Entwicklern gut bekannt, und es gibt Methoden, die Datenbank so zu gestalten, dass diese umgangen werden können.

Aber was ist mit Leistungsabfall bei Einfügungen und Aktualisierungen? Ist es möglich, dies durch intelligente Optimierung der Firebird-Konfiguration auszugleichen?

Im nächsten Artikel werden wir Optimierungsoptionen für die 1,7 Terabyte Firebird-Datenbank betrachten - wir werden unseren Vogel aufpäppeln und ihn fliegen lassen.