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 s’appuient sur elles pour soutenir des opérations commerciales importantes. Certaines bases de données Firebird font déjà des centaines de gigaoctets et continuent de croître (voir la section « Qui est grand ? »), et il est facile de prédire le moment où elles deviendront 2, 3 ou 5 fois plus grandes. Les administrateurs de bases de données et les fournisseurs sont donc intéressés à étudier le comportement de Firebird avec de grandes bases de données et à obtenir des recommandations sur la façon de les gérer.

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


## Matériel

Firebird est réputé pour son incroyable évolutivité 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 ordinaire :

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 temporaire)	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 placé le disque dur de 1,5 To dans l’un de nos ordinateurs de bureau, sans aucune autre modification. Ce disque dur a été formaté avec une taille de cluster de 16 Ko (identique à 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 le test, nous avons utilisé le chargeur de la boîte à outils basée sur TPC (téléchargez-le depuis 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 sur 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 (instantané)
Chargeur	Chargeur personnalisé 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 (donc la taille réelle de la base de données est supérieure à 1 To)
Collecter 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, identique au cluster du disque dur, pour maximiser les performances du disque (lire/écrire 1 page en un cycle d’E/S).

Dans la configuration 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 habituel (nous avions MS Office, Firefox, IBAnalyst, etc. - environ 8 à 12 programmes tournaient en même temps). Si nous avions dédié le matériel uniquement à cette tâche, cela aurait probablement été plus rapide, veuillez donc considérer ces valeurs uniquement comme un exemple bas de gamme ; ce ne sont certainement pas les meilleurs résultats.

Tableau 3 : Opérations de chargement

| |


Description	Valeur
Temps de chargement	~70 heures
Total d’enregistrements insérés	6,2 milliards
Vitesse moyenne d’insertion	24500 enregistrements/seconde
Taille moyenne d’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 (c’est-à-dire 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 la visionneuse FBDataGuard :

Index

Nous avons créé les index un par un et compté leur temps de création et 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 voir que même l’index pour 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 surcharge lors de la recherche de données à l’aide de la clé primaire pour cette table.

Statistiques

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

Nous avons placé les informations statistiques clés dans un 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 d’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 la visionneuse gratuite FBDataGuard Community Edition pour interpréter les données texte et voir non seulement les métriques de performance de la base de données, mais aussi la consommation CPU et 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 visiter 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 de 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)) ------ Informations de performance —— Temps de préparation = 15ms Temps d’exécution = 79ms Temps moyen de récupération = 6,08 ms Mémoire actuelle = 272 264 476 Mémoire maximale = 272 514 048 Tampons mémoire = 16 384 Lectures du disque vers le cache = 82 Écritures du cache vers le disque = 0 Récupérations du cache = 3 648	Jointure simple de tables avec 12400 et 372000000 enregistrements, sans conditions WHERE. « Temps moyen de récupération = 6,08 ms » concerne la 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)) ------ Informations de performance —— Temps de préparation = 16ms Temps d’exécution = 78ms Temps moyen de récupération = 6,00 ms Mémoire actuelle = 272 266 148 Mémoire maximale = 272 514 048 Tampons mémoire = 16 384 Lectures du disque vers le cache = 88 Écritures du cache vers le disque = 0 Récupérations du cache = 3 656	Jointure des mêmes tables avec une condition qui force la sélection des enregistrements récents. « Temps moyen de récupération = 6,00 ms » concerne la récupération de la première ligne.
select count(*) from WAREHOUSE, customer where c_w_id = w_id and c_w_id = 10000 Résultat = 30000	PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK), CUSTOMER INDEX (FK_CUST_WARE)) ------ Informations de performance —— Temps de préparation = 0ms Temps d’exécution = 453ms Temps moyen de récupération = 453,00 ms Mémoire actuelle = 272 263 844 Mémoire maximale = 272 514 048 Tampons mémoire = 16 384 Lectures du disque vers le cache = 1 048 Écritures du cache vers le disque = 0 Récupérations du cache = 60 024	Compter les enregistrements pour la requête précédente

SELECT * FROM ORDER_LINE WHERE OL_W_ID = 500	Plan PLAN (ORDER_LINE INDEX (ORDER_LINE_PK)) ------ Informations de performance —— Temps de préparation = 0ms Temps d’exécution = 94ms Temps moyen de récupération = 7,23 ms Mémoire actuelle = 136 445 536 Mémoire maximale = 136 592 176 Tampons mémoire = 8 192 Lectures du disque vers le cache = 150 Écritures du cache vers le disque = 0 Récupérations du cache = 2 402	Requête sur la plus grande table (3,8 milliards d’enregistrements). « Temps moyen de récupération = 7,23 ms » concerne la récupération de la première ligne.

	`<br>Plan<br>` `<br>PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br>` `<br> <br>` `<br>------ Informations de performance ------<br>` `<br>Temps de préparation = 0ms<br>` `<br>Temps d'exécution = 3s 438ms<br>` `<br>Temps moyen de récupération = 0,01 ms<br>` `<br>Mémoire actuelle = 136 445 496<br>` `<br>Mémoire maximale = 136 592 176<br>` `<br>Tampons mémoire = 8 192<br>` `<br>Lectures du disque vers le cache = 1 840<br>` `<br>Écritures du cache vers le disque = 0<br>` `<br>Récupérations du 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 milliards d’enregistrements), mais cette fois nous avons récupéré tous les enregistrements (299 245 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 avec 1 240 enregistrements et 372 millions 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 | Compte des enregistrements pour la requête précédente |

Résumé

Dans cette expérience, Firebird démontre 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 To 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 To et moins).
Une bonne évolutivité et une empreinte étonnamment réduite. Une base de données de 1 To a été créée sur un ordinateur de bureau classique et, plus important encore, elle peut être utilisée pour exécuter des requêtes générales : si vous ne récupérez pas des millions d’enregistrements, la vitesse des requêtes est identique à celle des bases de données de taille modérée (10-15 Go).

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

Contacts

Envoyez toutes vos questions et demandes à [email protected]