ALSI / S25 SGBD R Support de Cours 2TSIG
publicité
ALSI S25 Administration d’un SGBD Relationnel STS2 IG Volumétrie des tables et des index Le 27/05/17 page 1 SOMMAIRE 1 PRESENTATION DE LA SEQUENCE 1.1 1.2 2 DESCRIPTION DES SAVOIRS COMPETENCES ATTENDUES 2 2 2 MODE DE STOCKAGE DES DONNEES 2 3 DEFINITION DE LA TAILLE D’UNE BASE DE DONNEES 2 3.1 ESTIMATION DE LA TAILLE D’UNE TABLE POUR SQLSERVER 6.5 3.1.1 Remarques préliminaires 3.1.2 Etape 1 : Calcul de la taille d’une ligne de données 3.1.3 Etape 2 : Calcul du nombre de pages de données 3.1.4 Etape 3 : Calcul de la taille des lignes d'un index ordonné 3.1.5 Etape 4 : Calcul du nombre de pages d'un index ordonné 3.1.6 Etape 5 : Calcul du nombre total de pages 3.2 CALCUL DE LA TAILLE D'UNE TABLE DE 9 MILLIONS DE LIGNES DOTEE D'UN INDEX ORDONNE ALSI / S25 SGBD R Support de Cours 2TSIG D:\769910567.doc 2 3 3 3 3 3 3 3 1 ALSI S25 Administration d’un SGBD Relationnel STS2 IG Volumétrie des tables et des index Le 27/05/17 page 2 PRESENTATION DE LA SEQUENCE 1.1 Description des savoirs Il s’agit dans ce chapitre de comprendre comment estimer l’espace disque nécessaire à une BD. 256 Administration de la base de données 1.2 Compétences attendues administrer une BdD non répartie et en assurer la sécurité 2 MODE DE STOCKAGE DES DONNEES La première étape à réaliser lors de la mise en place d’une base de données consiste à réserver un espace disque destiné au stockage de toutes les données de la base. 3 DEFINITION DE LA TAILLE D’UNE BASE DE DONNEES Facteurs à prendre en compte lors de l'évaluation de la taille d'une base de données Fichier Fichierjournal journal Fichier Fichierde de données données Activité Fréquence Tables Tables Utilisateur et système Nombre de lignes Index Index Valeur de clé Nombre de lignes Facteur de remplissage Taille des transactions Sauvegarde 3.1 Principe d’une estimation On estime la taille Des données Des index Les règles de gestion du stockage et donc la taille réservée aux informations de gestion (« non utiles ») varient ensuite d’un SGBD à l’autre. Elles sont fournies dans la documentation. ALSI / S25 SGBD R Support de Cours 2TSIG D:\769910567.doc ALSI S25 Administration d’un SGBD Relationnel STS2 IG Volumétrie des tables et des index Le 27/05/17 page 3 3.2 Estimation de la taille d’une table pour SQLSERVER 6.5 3.2.1 Remarques préliminaires Les calculs diffèrent un peu selon que les champs sont de longueurs fixes (longueur fixe et défini avec l’option NOT NULL) ou de longueur variable (longueur variable ou longueur fixe et défini avec l'option NULL). Dans le second cas, il faut faire les calculs avec la longueur moyenne du champ et la formule de calcul tient compte du nombre de champs de longueur variable dans une ligne. On simplifie ici, en prenant la taille maxi de chaque colonne et en estimant des champs de longueur fixe. On prend le cas particulier d’un index ordonné : les feuilles d’un index ordonné sont les pages de données elles-même. Dans le cas d’un index non ordonné, il y a un niveau d’indexation supplémentaire et le nombre d’octets réservé pour les servitudes logicielles diffère un peu. 3.2.2 Etape 1 : Calcul de la taille d’une ligne de données 2 + (Somme des octets de toutes les colonnes) = Taille d’une ligne de données La valeur 2 correspond aux servitudes logicielles que SQL Server mobilise pour le stockage interne. 3.2.3 Etape 2 : Calcul du nombre de pages de données Pour calculer le nombre de pages de données: 8060 / (Taille de la ligne de données) = Nombre de lignes de données par page (Nombre de lignes) / (Nombre de lignes de données par page) = Nombre de pages de données requises. 3.2.4 Etape 3 : Calcul de la taille des lignes d'un index ordonné 5 + (Somme des octets des clés d'index )) = Taille d’une ligne d’index ordonné La valeur 5 correspond aux servitudes logicielles que SQL Server mobilise pour le stockage interne. 3.2.5 Etape 4 : Calcul du nombre de pages d'un index ordonné Après avoir calculé la taille d'une ligne de l'index ordonné, vous pouvez calculer le nombre de pages requises par celui-ci. (8060 / Taille d'une ligne de l'index ordonné) 2 = Nombre de lignes d'index ordonné par page (Nombre de pages de données) / (Nombre de lignes d'index ordonné par page) = Nombre de pages d'index au niveau 0 Si le résultat obtenu est supérieur à 1, répétez les étapes de division en utilisant chaque fois le quotient obtenu précédemment comme nouveau dividende, jusqu'à ce que vous obteniez un résultat égal à 1. Dès que vous aurez obtenu un quotient égal à 1, vous aurez atteint le niveau racine de l'index. (Nombre de pages d'index au dernier niveau) / (Nombre de lignes d'index ordonné par page) = Nombre de pages d'index au niveau suivant 3.2.6 Etape 5 : Calcul du nombre total de pages Pour calculer le nombre total de pages de données: (Nombre de pages de données) + (Nombre de pages d'index au niveau 0) + (Nombre de pages d'index au niveau suivant) + (Nombre de pages d'index au niveau suivant) [...] = Nombre total de pages de données 3.3 Calcul de la taille d'une table de 9 millions de lignes dotée d'un index ordonné L'exemple suivant illustre comment calculer la taille (en nombre de pages de 8 Ko) des données et de l'index ordonné d'une table présentant les caractéristiques suivantes: 9 millions de lignes Somme des octets des colonnes = 158 ALSI / S25 SGBD R Support de Cours 2TSIG D:\769910567.doc ALSI S25 Administration d’un SGBD Relationnel STS2 IG Volumétrie des tables et des index Le 27/05/17 page 4 Clé d'index ordonné de longueur fixe = 4 octets Procédez comme suit: DONNEES 1. Calculez la taille d'une ligne de données: 2 (Servitudes logicielles) + 158 Somme des octets de toutes les colonnes 160 Taille d'une ligne de données INDEX 3.Calculez la taille des lignes de l'index ordonné: 5 Servitudes logicielles +4 Somme des octets des clés d'index 9 Taille des lignes de l'index ordonné 4.Calculez le nombre de pages de l'index ordonné: 2. Calculez le nombre de pages de données: 8060 / 160 = 50 lignes de données par page 9 000 000 / 50 = 180 000 pages de données 5. Calculez le nombre total de pages: Totaux: Niveau 1 Niveau 0 Données Nombre total de pages de 8 Ko ALSI / S25 SGBD R Support de Cours 2TSIG (8060 / 9) - 2 = 895 lignes d'index ordonné par page 180 000 / 895 = 201 pages d'index (Niveau 0) 1 / 895 = 1 pages d'index (Niveau 1) Pages 1 201 180 000 180 202 D:\769910567.doc
