Waarom een terabyte Firebird-database aanmaken?

Veel bedrijven werken met grote Firebird-databases en vertrouwen erop voor het ondersteunen van belangrijke bedrijfsprocessen. Sommige Firebird-databases zijn al honderden gigabytes groot en blijven groeien (zie het gedeelte “Wie is groot?”), en het is gemakkelijk te voorspellen wanneer ze 2, 3 of 5 keer zo groot worden. Databasebeheerders en leveranciers zijn er dus in geïnteresseerd om het gedrag van Firebird met grote databases te onderzoeken en aanbevelingen te krijgen over hoe ze te beheren.

Een andere belangrijke reden die we in gedachten hadden bij het aanmaken van de 1Tb Firebird-database was het definitief uitbannen van de wijdverbreide opvatting dat Firebird een database-engine is voor “kleine databases”. Deze mythe lijkt nu dood te zijn, maar sommige analisten en journalisten halen hem regelmatig weer uit de kast, en we hopen deze belachelijke opvatting eindelijk de wereld uit te helpen.


## Hardware

Firebird staat bekend om zijn ongelooflijke schaalbaarheid en dit onderzoek heeft dat nogmaals bevestigd. Het oorspronkelijke doel van dit experiment was alleen het aanmaken van een Firebird-database van 1Tb, dus gebruikten we een gewone desktopcomputer:

Tabel 1: Hardware


Component	Parameters
CPU	AMDAthlon 64 x2 5200
RAM	4GB
Moederbord	MSI K9N Platinum
HDD1 (besturingssysteem en temp)	ST3160815AS, 160GB, SATA II
HDD2 (hulp)	HDT721064SLA360, 640GB,SATAII
HDD2 (hulp)	HDS728080PLA380, 80GB, SATA I
HDD3 (database)	ST31500341AS, 1.5TB, SATA II (Firmware CC1H)

In wezen hebben we de 1,5Tb HDD in een van onze kantoor-desktops geplaatst, zonder enige andere aanpassingen. Deze HDD werd geformatteerd met clustergrootte 16Kb (hetzelfde als de paginagrootte van de database, zoals je hieronder kunt zien).

Software

Omdat het een desktopcomputer is, is het besturingssysteem Windows XP Professional SP3, 32bit. Om de test daadwerkelijk uit te voeren gebruikten we de loader uit de op TPC gebaseerde toolkit (download deze van http://ibdeveloper.com/tests/tpc-c/, zowel binaries als broncode zijn beschikbaar).

We willen benadrukken dat de loader gegevens invoegt zoals ze in een realistisch scenario zouden worden ingevoegd: records worden ingevoegd en bevinden zich in de database (en op fysieke schijfgebieden) in verschillende master-detail-subdetail-tabellen, niet tabel voor tabel.

Tabel 2: Software


Software	Versie
Besturingssysteem	Windows XP Professional SP3, 32bit
Firebird	2.1.3 SuperServer (snapshot)
Loader	Aangepaste loader uit op tpc gebaseerde test

Plan

We hadden een heel eenvoudig plan voor dit experiment:

Database aanmaken en vullen met 1Tb aan gegevens, zonder indices
Primaire sleutels en bijbehorende indices aanmaken (dus eigenlijk is de database groter dan 1Tb)
Databasestatistieken verzamelen
Verschillende SQL-query’s uitvoeren en de databaseprestaties inschatten

Database- en Firebird-serverconfiguratie

De database heeft paginagrootte 16384 bytes, hetzelfde als de HDD-cluster, om de schijfdoorvoerprestaties te maximaliseren (om 1 pagina per I/O-cyclus te lezen/schrijven).

In de Firebird-configuratie hebben we een extra map voor temp-ruimte geconfigureerd en deze naar de schijf van 640Gb verwezen (waar ~300Gb vrij was).

Laadstap

Gegevens werden in deze database geladen in verschillende stappen. De computer werd tijdens de laadoperaties gebruikt als gewone desktop (we hebben MS Office, Firefox, IBAnalyst, enz. - ongeveer 8-12 programma’s draaiden tegelijkertijd). Als we de hardware alleen voor deze taak zouden hebben gereserveerd, zou het waarschijnlijk sneller zijn geweest, dus beschouw deze waarden alleen als een low-end voorbeeld; het zijn beslist niet de topresultaten.

Tabel 3: Laadoperaties

| |


Beschrijving	Waarde
Tijd om te laden	~70 uur
Totaal ingevoegde records	6,2 miljard
Gemiddelde invoegsnelheid	24500 records/seconde
Gemiddelde recordgrootte	146 bytes (min 13 bytes, max - 600 bytes)
Transacties	646489

We hebben ~4 dagen besteed aan het laden, en daarna hadden we een Firebird-database met precies 1Tb grootte (d.w.z. 1 099 900 125 184 bytes).

Hieronder kun je de databasegroei en transactiedynamiek zien in FBDataGuard Viewer:

Indices

We hebben indices één voor één aangemaakt en de aanmaaktijd en de bijbehorende grootte van het tijdelijke bestand dat voor sorteren werd gebruikt, bijgehouden.

De grootste index werd aangemaakt voor tabel ORDER_LINE. De primaire sleutel bevat vier velden (Smallint, Smallint, Integer en Smallint). Het tijdelijke bestand voor deze sorteersindex was 182Gb, en de uiteindelijke indexgrootte in de database is 29,3Gb.

Het is interessant om te zien dat zelfs de index voor een tabel met 3,8 miljard records diepte = 3 heeft, omdat de paginagrootte 16384 bytes was, dus er is geen overhead bij het zoeken naar gegevens met behulp van de primaire sleutel voor deze tabel.

Statistieken

Daarna hebben we databasestatistieken verzameld. Dit duurde 7 uur 32 min 45 sec.

We hebben de belangrijkste statistische informatie in één tabel gezet en enkele query’s en tijdmetingen opgenomen:

Tabel 4: Geconsolideerde statistieken voor 1Tb-database


Tabelnaam	Recordaantallen	Grootte, gb	*Uitvoeringstijd van select count()**	Aanmaaktijd index	Grootte tmp-bestand, Gb	Indexgrootte, Gb
WAREHOUSE	1240	0.002	0s	0	0	0.0
ITEM	100000	0.012	0.7s	-	-	0.0
DISTRICT	124000	0.017	0.7s	6	-	0.0
NEW_ORDER	111600000	32	20m 00s	23m 00s	4.56	0.8
CUSTOMER	372000000	224	-	41m 00s	-	2.6
customer_last				1h 52m 32s	12.4	2.3
fk_cust_ware				2h 10m 51s	-	2.3
HISTORY	372000000	32	-	-	-	-
ORDERS	372000000	25	32m 00s	45m 41s	15.2	2.5
STOCK	1240000000	404	-	3h 34m 44s	41.5	9.2
ORDER_LINE	3720051796	359	-	12h 6m 18s	182.0	29.3

Databasestatistieken kunnen worden gedownload van hier.

Je kunt de gratis FBDataGuard Community Edition Viewer gebruiken om tekstgegevens te interpreteren en niet alleen databaseprestatiemetingen te zien, maar ook CPU- en geheugenverbruik.

Query’s

Allereerst hebben we select count(*)-query’s uitgevoerd op verschillende tabellen (zie de 4e kolom in Tabel 4 hierboven). Zoals je weet is vanwege de multi-versie-aard van Firebird select count(*) voor een hele tabel een dure operatie voor de server, omdat het bezoeken van elke pagina vereist, en ervaren Firebird-ontwikkelaars gebruiken geen select count(*), maar we gebruikten het om de algehele prestatieverhouding van database en hardware te demonstreren.

Na de select count-query’s hebben we query’s uit een realistisch scenario uitgevoerd en, om eerlijk te zijn, waren we verbaasd over zulke goede resultaten. Oordeel zelf:


Query	Statistieken	Beschrijving
select w_id, w_name, c_id, c_last from WAREHOUSE, customer where c_w_id = w_id	PLAN JOIN (WAREHOUSE NATURAL, CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 15ms Execute time = 79ms Avg fetch time = 6.08 ms Current memory = 272 264 476 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 82 Writes from cache to disk = 0 Fetches from cache = 3 648	Eenvoudige join van tabellen met 12400 en 372000000 records, geen WHERE-voorwaarden. “Avg fetch time = 6.08 ms” geldt voor het ophalen van de eerste rij.
select w_id, w_name, c_id, c_last from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 16ms Execute time = 78ms Avg fetch time = 6.00 ms Current memory = 272 266 148 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 88 Writes from cache to disk = 0 Fetches from cache = 3 656	Join van dezelfde tabellen met een voorwaarde die selectie van recente records afdwingt. “Avg fetch time = 6.00 ms” geldt voor het ophalen van de eerste rij.
select count(*) from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000 Result = 30000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Performance info —— Prepare time = 0ms Execute time = 453ms Avg fetch time = 453.00 ms Current memory = 272 263 844 Max memory = 272 514 048 Memory buffers = 16 384 Reads from disk to cache = 1 048 Writes from cache to disk = 0 Fetches from cache = 60 024	Records tellen voor de vorige query

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plan PLAN (ORDER_LINE INDEX (ORDER_LINE_PK)) ------ Performance info —— Prepare time = 0ms Execute time = 94ms Avg fetch time = 7.23 ms Current memory = 136 445 536 Max memory = 136 592 176 Memory buffers = 8 192 Reads from disk to cache = 150 Writes from cache to disk = 0 Fetches from cache = 2 402	Query op de grootste tabel (3,8 miljard records). “Avg fetch time = 7.23 ms” geldt voor het ophalen van de eerste rij.

	`<br>Plan<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Performance info ------<br>` `<br>Prepare time = 0ms<br>` `<br>Execute time = 3s 438ms<br>` `<br>Avg fetch time = 0.01 ms<br>` `<br>Current memory = 136 445 496<br>` `<br>Max memory = 136 592 176<br>` `<br>Memory buffers = 8 192<br>` `<br>Reads from disk to cache = 1 840<br>` `<br>Writes from cache to disk = 0<br>` `<br>Fetches from cache = 598 636<br>`

| SELECT * FROM ORDER_LINE
WHERE OL_W_ID = 500 | Dezelfde query op de grootste tabel (3,8 miljard records), maar dit keer hebben we alle records opgehaald (299245 records opgehaald). | | | | | select w_id, w_name, c_id, c_last
from WAREHOUSE, customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000) | Plan
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Performance info ——
Prepare time = 0ms
Execute time = 125ms
Avg fetch time = 9.62 ms
Current memory = 272 270 824
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 91
Writes from cache to disk = 0
Fetches from cache = 3 659 | Tabellen joinen met 1240 records en 372 miljoen records. | | select count(*)
from WAREHOUSE, customer
where c_w_id = w_id and (c_w_id > 8000) and (c_w_id < 10000)
Result = 59 970 000 | Plan
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE))
------ Performance info ——
Prepare time = 0ms
Execute time = 13m 4s 718ms
Avg fetch time = 784 718.00 ms
Current memory = 272 268 532
Max memory = 272 514 048
Memory buffers = 16 384
Reads from disk to cache = 2 332 583
Writes from cache to disk = 0
Fetches from cache = 119 977 902 | Records tellen voor de vorige query |

Samenvatting

In dit experiment laat Firebird de volgende resultaten zien

Een onbetwiste capaciteit om grote databases te verwerken. We zijn er vrij zeker van dat het mogelijk is om een database van 32 Tb te creëren en te gebruiken op geschikte hardware, en dat Firebird dezelfde hoge prestaties zal laten zien als bij kleinere databases (dat wil zeggen 1 Tb en kleiner).
Goede schaalbaarheid en een verbazingwekkend kleine footprint. Een database van 1 Tb werd gecreëerd op een gewone desktopcomputer en, belangrijker nog, kan worden gebruikt om algemene queries uit te voeren: als je niet miljoenen records ophaalt, is de snelheid van de query dezelfde als bij databases van gemiddelde grootte (10-15 Gb).

Dit is niet het einde van dit experiment: we zijn van plan om enkele queries uit te voeren, aanvullende statistieken te verzamelen en binnenkort een gedetailleerder rapport te publiceren. Houd ons in de gaten.

Contacten

Stuur al je vragen en verzoeken naar [email protected]