Alexey Kovyazin, 26 mai 2014

Il y a quelques jours, nous avons publié un article sur nos tests, consacré à la relation entre les performances de Firebird et la croissance des bases de données, où nous avons testé (entre autres) la base de données Firebird SQL de 1,7 téraoctet. Vous trouverez ici plus de détails sur une si grande base de données.

Tables

Dans le tableau 1, vous trouverez la liste des tables avec leurs caractéristiques clés. Ces statistiques ont été obtenues avec gstat -a -r et interprétées avec notre outil IBAnalyst.

Table	Enregistrements	Longueur d’enregistrement, octets	Pages de données	Taille de la table, Mo	Taille des index, Mo	Total, %
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

Tableau 1. Tables et leurs principaux paramètres dans la base de données Firebird SQL de 1,7 téraoctet

Comme vous pouvez le voir, la plus grande table est ORDER_LINE - elle contient plus de 6 milliards d’enregistrements. Sa taille est d’environ 600 Go et il y a 50 Go d’index pour cette table. Cette table occupe 34 % de la base de données.

La table STOCK est également très grande - elle contient environ 2 milliards d’enregistrements. Bien que le nombre d’enregistrements soit inférieur à celui de la table ORDER_LINE, STOCK occupe environ 680 Go dans la base de données (39 %), car la longueur d’enregistrement dans STOCK est de 298,88 octets contre seulement 60,09 octets dans ORDER_LINE. Par conséquent, la taille des index associés pour STOCK n’est que de 15 Go.

La troisième plus grande table, CUSTOMER, a une longueur d’enregistrement encore plus grande - 577,52 octets, et occupe 378 Go (21 %) avec seulement 630 millions d’enregistrements.

Index

Il n’y a pas beaucoup d’index dans cette base de données, car elle est conçue uniquement pour des tests - la majorité des tables n’ont qu’un seul index - la clé primaire. Dans les applications réelles, les développeurs créent de nombreux index pour répondre aux demandes spécifiques des utilisateurs, mais ici, les index sont concentrés sur les insertions et mises à jour les plus rapides des données - par conséquent, la taille des index n’est que de 5 à 10 % de la taille de la table, alors qu’elle est généralement de 30 à 50 % (si vous avez une taille d’index supérieure à 50 % de la taille de la table, envisagez de redessiner le schéma de la base de données, ou peut-être de supprimer les index inutiles ou inefficaces).

Dans le tableau 2, vous trouverez la liste de tous les index de cette base de données et leurs caractéristiques clés, tirées des statistiques gstat et analysées par IBAnalyst :

Index	Table	Profondeur	Nombre de clés	Longueur de clé, octets	Nombre de clés uniques	Taille, Mo
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

Tableau 2. Index de la base de données Firebird 1,7 To

Comme vous pouvez le voir, tous les index ont une très petite taille de clé - la plus grande est de 1,41, ce qui signifie que l’index est très efficacement compressé - par exemple, la clé primaire pour ORDER_LINE contient 6 milliards de clés dans 50 Go d’espace. C’est un très bon résultat.

Il y a 2 index qui ont une profondeur = 4. Cela signifie que le moteur doit effectuer 4 lectures de pages d’index pour trouver la valeur demandée. Il est recommandé que la profondeur d’index ne soit pas supérieure à 3, et si elle est plus élevée, la taille de page de la base de données devrait être augmentée. Cependant, nous avons déjà la taille de page maximale dans cette base de données (16 Ko), nous devons donc vivre avec.

Échauffement

Avant de continuer avec les requêtes, procédons à un « échauffement ». Lorsque la base de données est grande, les données système associées aux tables sont également grandes, et il faut du temps à Firebird pour mettre en cache les pages système nécessaires. Par exemple, la table ORDER_LINE a 10110 pages de pointeurs. L’échauffement est nécessaire pour toute grande base de données.

Sans échauffement, la première exécution des requêtes prendra beaucoup plus de temps qu’avec un échauffement.

Pour chauffer le cache de la base de données, exécutons une série de requêtes simples pour charger les données système dans le cache :

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;

Cela suffit pour un schéma de données aussi simple. Pour les bases de données grandes et complexes avec des centaines de tables, le schéma d’échauffement pourrait être beaucoup plus complexe, il devrait être soigneusement développé.

En conséquence, la consommation mémoire du processus Firebird (Firebird SuperServer 2.5.2 64 bits) augmente :

Figure 1. Échauffement du cache de la base de données

Requêtes

Après l’échauffement, nous pouvons exécuter plusieurs requêtes typiques pour estimer comment les utilisateurs travailleront avec une si grande base de données Firebird : quels seront les temps de réponse pour les requêtes typiques et les opérations de lecture.

Requête	Plan et statistiques	Description
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> STATISTIQUES<br> Mémoire actuelle = 166919744<br> Delta mémoire = 5616<br> Mémoire max = 166977488<br> Temps écoulé = 0,08 sec<br> Buffers = 10000<br> Lectures = 1<br> Écritures 0<br> Fetch = 51	Jointure de la table WAREHOUSE (21000 enregistrements) avec CUSTOMER (630 millions d’enregistrements), avec condition pour un entrepôt spécifique
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> Mémoire actuelle = 166918560<br> Delta mémoire = -1184<br> Mémoire max = 166977488<br> Temps écoulé = 0,16 sec<br> Buffers = 10000<br> Lectures = 1175<br> Écritures 0<br> Fetch = 60040	Compte des enregistrements pour la requête précédente.
SELECT first 10 *<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050	PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> STATISTIQUES<br> Mémoire actuelle = 167013640<br> Delta mémoire = 95080<br> Mémoire max = 167055968<br> Temps écoulé = 0,80 sec<br> Buffers = 10000<br> Lectures = 10285<br> Écritures 0<br> Fetch = 10424	Requête sur la plus grande table ORDER_LINE (~6 milliards d’enregistrements) avec condition pour sélectionner les enregistrements order_line pour un identifiant d’entrepôt spécifié. Comme la clé primaire ORDER_LINE_PK est composite et contient l’identifiant d’entrepôt (CONSTRAINT ORDER_LINE_PK : Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER), elle est efficacement utilisée dans cette requête.
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> Mémoire actuelle = 167125464<br> Delta mémoire = -7160<br> Mémoire max = 167267976<br> Temps écoulé = 3,41 sec<br> Buffers = 10000<br> Lectures = 1994<br> Écritures 0<br> Fetch = 599170	Compte pour la requête précédente. La deuxième requête avec les mêmes paramètres sera beaucoup plus rapide, mais les requêtes avec des paramètres différents montrent un résultat similaire.
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> STATISTIQUES<br> Mémoire actuelle = 167687664<br> Delta mémoire = -651888<br> Mémoire max = 168931400<br> Temps écoulé = 0,19 sec<br> Buffers = 10000<br> Lectures = 31<br> Écritures 0<br> Fetch = 63	Requête avec jointure et 2 conditions.
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> STATISTIQUES<br> Mémoire actuelle = 166807128<br> Delta mémoire = 6016<br> Mémoire max = 166879200<br> Temps écoulé = 0,20 sec<br> Buffers = 9999<br> Lectures = 1<br> Écritures 0<br> Fetch = 4960	Requête pour afficher les détails d’un client spécifique dans un entrepôt et un district spécifiques. Elle démontre les performances de jointures plus complexes.

Résumé

Comme vous pouvez le voir, la base de données Firebird de 1,7 téraoctet montre de très bonnes performances pour les requêtes de lecture courantes. Si la requête est bien conçue et utilise de bons index, les performances sont bonnes même sur une très grande base de données. Bien sûr, il y a des points sensibles, comme le temps de fetch long pour de très grands ensembles de données et le temps long pour les opérations de comptage (car Firebird visite toutes les pages pour obtenir un nombre exact d’enregistrements pour une requête particulière dans une transaction spécifique), mais ces points sensibles sont bien connus des développeurs Firebird expérimentés, et il existe des méthodes pour concevoir la base de données afin de contourner ces problèmes.

Mais qu’en est-il de la dégradation des performances pour les insertions et les mises à jour ? Est-il possible de la compenser avec un réglage intelligent de la configuration de Firebird ?

Dans le prochain article, nous examinerons les options de réglage pour la base de données Firebird de 1,7 téraoctet - nous boosterons notre oiseau et le ferons voler.