Pourquoi créer une base de données Firebird d’un téraoctet ?

De nombreuses entreprises travaillent avec de grandes bases de données Firebird et comptent sur elles pour soutenir des opérations commerciales importantes. Certaines bases de données Firebird atteignent déjà des centaines de gigaoctets et continuent de croître (voir la section « Qui est grand ? »), et il est facile de prévoir le moment où elles deviendront 2, 3 ou 5 fois plus grandes. Ainsi, les administrateurs de bases de données et les éditeurs sont intéressés à étudier le comportement de Firebird avec de grandes bases de données et à obtenir des recommandations sur leur gestion.

De plus, une raison importante que nous avions en tête lors de la création de la base de données Firebird de 1 To était l’élimination définitive de la perception répandue de Firebird comme moteur de base de données pour les « petites bases de données ». Ce mythe semble désormais mort, mais certains analystes et journalistes le ressuscitent régulièrement, et nous espérons en finir une bonne fois pour toutes avec cette perception ridicule.


## Matériel

Firebird est bien connu pour son évolutivité incroyable et cette investigation l’a confirmé une fois de plus. Le but initial de cette expérience était simplement la création d’une base de données Firebird de 1 To, nous avons donc utilisé un ordinateur de bureau classique :

Tableau 1 : Matériel


Composant	Paramètres
CPU	AMDAthlon 64 x2 5200
RAM	4 Go
Carte mère	MSI K9N Platinum
HDD1 (système d’exploitation et temp)	ST3160815AS, 160 Go, SATA II
HDD2 (auxiliaire)	HDT721064SLA360, 640 Go, SATA II
HDD2 (auxiliaire)	HDS728080PLA380, 80 Go, SATA I
HDD3 (base de données)	ST31500341AS, 1,5 To, SATA II (Firmware CC1H)

En substance, nous avons installé le HDD de 1,5 To dans l’un de nos ordinateurs de bureau du bureau, sans aucune autre modification. Ce HDD a été formaté avec une taille de cluster de 16 Ko (la même que la taille de page de la base de données, comme vous pouvez le voir ci-dessous).

Logiciel

Comme il s’agit d’un ordinateur de bureau, le système d’exploitation est Windows XP Professional SP3, 32 bits. Pour effectuer réellement le test, nous avons utilisé le chargeur de la boîte à outils basée sur TPC (téléchargez-le sur http://ibdeveloper.com/tests/tpc-c/, les binaires et les sources sont disponibles).

Nous tenons à souligner que le chargeur insère les données comme elles le seraient dans un scénario réel : les enregistrements sont insérés et situés dans la base de données (et dans les zones physiques du disque) dans plusieurs tables maître-détail-sous-détail, et non table par table.

Tableau 2 : Logiciel


Logiciel	Version
Système d’exploitation	Windows XP Professional SP3, 32 bits
Firebird	2.1.3 SuperServer (snapshot)
Chargeur	Chargeur personnalisé issu du test basé sur TPC

Plan

Nous avions un plan très simple pour cette expérience :

Créer la base de données et la charger avec 1 To de données, sans index
Créer les clés primaires et les index appropriés (la taille réelle de la base de données est donc supérieure à 1 To)
Recueillir les statistiques de la base de données
Exécuter plusieurs requêtes SQL et estimer les performances de la base de données

Configuration de la base de données et du serveur Firebird

La base de données a une taille de page de 16384 octets, la même que le cluster du HDD, afin de maximiser le débit disque (lire/écrire 1 page en un seul cycle d’E/S).

Dans la configuration de Firebird, nous avons configuré un répertoire supplémentaire pour l’espace temporaire et l’avons pointé vers le disque de 640 Go (où ~300 Go étaient libres).

Étape de chargement

Les données ont été chargées dans cette base de données en plusieurs étapes. L’ordinateur a été utilisé pendant les opérations de chargement comme un bureau classique (nous avons MS Office, Firefox, IBAnalyst, etc. - environ 8 à 12 programmes exécutés en même temps). Si nous avions dédié le matériel uniquement à cette tâche, ce serait probablement plus rapide, donc veuillez considérer ces valeurs uniquement comme un exemple bas de gamme ; elles ne sont certainement pas les meilleurs résultats.

Tableau 3 : Opérations de chargement

| |


Description	Valeur
Temps de chargement	~70 heures
Total des enregistrements insérés	6,2 milliards
Vitesse d’insertion moyenne	24500 enregistrements/seconde
Taille moyenne d’un enregistrement	146 octets (min 13 octets, max - 600 octets)
Transactions	646489

Nous avons passé ~4 jours au chargement, et après cela nous avions une base de données Firebird d’exactement 1 To (soit 1 099 900 125 184 octets).

Ci-dessous, vous pouvez voir la croissance de la base de données et la dynamique des transactions dans FBDataGuard Viewer :

Index

Nous avons créé les index un par un et avons chronométré leur création ainsi que la taille appropriée du fichier temporaire utilisé pour le tri.

Le plus grand index a été créé pour la table ORDER_LINE. Sa clé primaire contient quatre champs (Smallint, Smallint, Integer et Smallint). Le fichier temporaire pour cet index de tri était de 182 Go, et la taille finale de l’index dans la base de données est de 29,3 Go.

Il est intéressant de constater que même l’index d’une table de 3,8 milliards d’enregistrements a une profondeur = 3, car la taille de page était de 16384 octets, il n’y a donc pas de surcoût lors de la recherche de données à l’aide de la clé primaire pour cette table.

Statistiques

Après cela, nous avons recueilli les statistiques de la base de données. Cela a pris 7 heures 32 minutes 45 secondes.

Nous avons regroupé les informations statistiques clés dans un seul tableau et inclus quelques requêtes et mesures de temps :

Tableau 4 : Statistiques consolidées pour la base de données de 1 To


Nom de la table	Nombre d’enregistrements	Taille, Go	*Temps d’exécution de select count()**	Temps de création de l’index	Taille du fichier tmp, Go	Taille de l’index, Go
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

Les statistiques de la base de données peuvent être téléchargées ici.

Vous pouvez utiliser le Viewer gratuit FBDataGuard Community Edition pour interpréter les données textuelles et voir non seulement les métriques de performance de la base de données, mais aussi la consommation de CPU et de mémoire.

Requêtes

Tout d’abord, nous avons exécuté des requêtes select count(*) sur plusieurs tables (voir la 4ème colonne du tableau 4 ci-dessus). Comme vous le savez, en raison de la nature multi-version de Firebird, select count(*) pour une table entière est une opération coûteuse pour le serveur car elle nécessite de parcourir chaque page, et les développeurs Firebird expérimentés n’utilisent pas select count(*), mais nous l’avons utilisé pour démontrer le ratio de performance global de la base de données et du matériel.

Après les requêtes select count, nous avons exécuté des requêtes issues d’un scénario réel et, pour être honnête, nous avons été étonnés par d’aussi bons résultats. Voyez par vous-même :


Requête	Statistiques	Description
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	Jointure simple de tables de 12400 et 372000000 enregistrements, sans conditions WHERE. « Avg fetch time = 6.08 ms » correspond au temps de récupération de la première ligne.
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	Jointure des mêmes tables avec une condition qui force la sélection des enregistrements récents. « Avg fetch time = 6.00 ms » correspond au temps de récupération de la première ligne.
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	Compte les enregistrements de la requête précédente

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	Requête sur la plus grande table (3,8 milliards d’enregistrements). « Avg fetch time = 7.23 ms » correspond au temps de récupération de la première ligne.

	`<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 | La même requête sur la plus grande table (3,8 Md d’enregistrements), mais cette fois nous avons récupéré tous les enregistrements (299245 enregistrements récupérés). | | | | | 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 | Jointure de tables de 1240 enregistrements et 372 M d’enregistrements. | | 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 | Comptage des enregistrements pour la requête précédente |

Résumé

Dans cette expérience, Firebird affiche les résultats suivants

Une capacité indéniable à gérer de grandes bases de données. Nous sommes convaincus qu’il est possible de créer et d’utiliser une base de données de 32 Tb sur un matériel approprié, et Firebird affichera les mêmes hautes performances que pour des bases de données plus petites (c’est-à-dire 1 Tb et moins).
Une bonne scalabilité et une empreinte étonnamment réduite. La base de données de 1 Tb a été créée sur un ordinateur de bureau ordinaire et, plus important encore, elle peut être utilisée pour exécuter des requêtes courantes : si vous ne récupérez pas des millions d’enregistrements, la vitesse des requêtes est la même que pour des bases de données de taille modérée (10-15 Gb).

Ce n’est pas la fin de cette expérience : nous avons l’intention d’exécuter d’autres requêtes, de recueillir des statistiques supplémentaires et de publier prochainement un rapport plus détaillé. Restez à l’écoute.

Contacts

Envoyez toutes vos questions et demandes à [email protected]