5 Définition de la taille d`une base de données
publicité
ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 1 SOMMAIRE 1PRESENTATION DE LA SEQUENCE 2 1.1DESCRIPTION DES SAVOIRS 1.2COMPETENCES ATTENDUES 2 2 2MODE DE STOCKAGE DES DONNEES 2 2.1FICHIER DE DONNEES 2.2EXTENSION 2.3 PAGE 2 3 3 3LE JOURNAL DES TRANSACTIONS 4 3.1RAPPEL SUR LES TRANSACTIONS 3.2LE JOURNAL DES TRANSACTIONS 3.3FONCTIONNEMENT DU JOURNAL DES TRANSACTIONS 4 4 4 4CREATION D’UNE BASE DE DONNEES 5 4.1COMMANDE SQL DE CREATION 4.2EXTENSION OU REDUCTION D’UNE BASE 4.3LES OPTIONS D’UNE BASE DE DONNEES 4.4GESTION D’UNE BASE DE DONNEES SUR PLUSIEURS DISQUES 5 5 6 7 5DEFINITION DE LA TAILLE D’UNE BASE DE DONNEES 8 5.1ESTIMATION DE LA TAILLE D’UNE TABLE 5.1.1Remarques préliminaires 5.1.2Etape 1 : Calcul de la taille d’une ligne de données 5.1.3Etape 2 : Calcul du nombre de pages de données 5.1.4Etape 3 : Calcul de la taille des lignes d'un index ordonné 5.1.5Etape 4 : Calcul du nombre de pages d'un index ordonné 5.1.6Etape 5 : Calcul du nombre total de pages 5.2CALCUL 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:\840909851.doc 8 8 8 8 9 9 9 9 1 ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 2 PRESENTATION DE LA SEQUENCE 1.1 Description des savoirs Il s’agit dans ce chapitre de comprendre comment une base de données est structurée pour répondre à la nécessité de fiabilité. On voit aussi 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. Avec SQL Server, il est nécessaire de pré-allouer l’espace de stockage c’est à dire qu’il faut calculer et réserver un espace disque suffisant pour y stocker la base. SQL Server utilise l’architecture suivante : 2.1 Fichier de Données C’est l’entité de base créée par le système d’exploitation (instruction Disk Init).Un fichier physique est alors créé, il possède un nom logique d’unité, un nom physique (de fichier), une taille et un numéro. Un fichier de données peut contenir : une ou plusieurs bases de données, un ou plusieurs journaux de transactions. ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 3 A l’installation, SQL Server génère trois fichiers de données: MASTER.DAT, MSDBDATA.DAT, MSDBLOG.DAT 2.2 Extension Une extension prend 64 Ko. Chaque table ou index est affecté d’une extension lors de sa création : l’extension initiale. A mesure que la table augmente de volume, le système lui attribue de nouvelles extensions : les extensions supplémentaires. C’est l’unité de stockage minimum d’une table ou d’un index. Il ne peut y avoir qu’une table ou un index par extension. 2.3 Page Chaque extension contient 8 pages de données, c’est dans cet espace que sont stockées les lignes des tables et index. Une ligne ne peut pas s’étendre sur plusieurs pages. La taille d’une page est de 8 Ko octets (dont 8192 octets utilisables), cependant la longueur maxi d’un enregistrement (hors champs text et image) est 8060 octets. Il est important de bien calculer la longueur des enregistrements lors de la conception. Exemple : si un enregistrement occupe 5 Ko, cette longueur ne permet pas de stocker 2 enregistrements par page (2*5 Ko = 10 Ko), de ce fait chaque enregistrement occupera une page, ce qui représente un gaspillage de près de 50% de l’espace. La procédure sp_estspace permet de calculer la taille d’une table en fonction de sa structure et du nombre d’enregistrements estimés. ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc 3 ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 4 LE JOURNAL DES TRANSACTIONS 3.1 Rappel sur les transactions Une transaction est un ensemble d’instructions SQL, traitées comme une unité unique de travail et de récupération. L’unité doit être exécutée en totalité ou pas du tout. Il existe deux sortes de transactions : Une transaction implicite dans laquelle chaque instruction UPDATE, INSERT ou DELETE s ‘exécute comme une transaction. Une transaction explicite définie par l’utilisateur qui regroupe les instructions encadrées par BEGIN TRANSACTION et COMMIT TRANSACTION. 3.2 Le journal des transactions Chaque transaction est enregistrée dans un journal des transactions pour maintenir la cohérence de la BD et permettre sa récupération en cas de défaillance du système. Le journal est une zone de stockage qui opère un suivi de toutes les modifications apportées à une base de données. Les modifications sont enregistrées dans le journal avant d’être écrites dans la BD. 3.3 Fonctionnement du journal des transactions ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc 4 ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 5 CREATION D’UNE BASE DE DONNEES 4.1 Commande SQL de création Pour créer une base de données, vous devez définir les éléments suivants : nom de la base de données taille de la base de données fichiers utilisés pour la stocker CREATE CREATE DATABASE DATABASE biblio biblio ON ON PRIMARY ( NAME=données_biblio, PRIMARY ( NAME=données_biblio, FILENAME='c:\mssql7\data\biblio.mdf', FILENAME='c:\mssql7\data\biblio.mdf', SIZE=10MB, SIZE=10MB, MAXSIZE=15MB, MAXSIZE=15MB, FILEGROWTH=20%) FILEGROWTH=20%) LOG LOG ON ON (( NAME=journ_biblio, NAME=journ_biblio, FILENAME='c:\mssql7\data\biblio.ldf', FILENAME='c:\mssql7\data\biblio.ldf', SIZE=3MB, SIZE=3MB, MAXSIZE=5MB, MAXSIZE=5MB, FILEGROWTH=1MB) FILEGROWTH=1MB) 4.2 Extension ou réduction d’une Base La taille de la base peut être réduite ou étendue par la suite au fur et à mesure des besoins. On utilise alors - l’interface graphique - l’instruction DBCC - l’instruction ALTER DATABASE MaBase MODIFY FILE … Une autre façon d’étendre une base de données est d’ajouter des fichiers de données ou de journal par - l’interface graphique - l’instruction ALTER DATABASE MaBase ADD FILE … ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 6 4.3 Les options d’une base de données Select into / bulkcopy : Cette option activée permet de ne pas écrire dans le journal de transaction, certaines opérations comme bcp ou SELECT INTO. Par défaut elle est désactivée. Colonnes NULL par défaut : Avec cette option, les colonnes ne spécifiant pas NULL ou NOT NULL (lors des opérations CREATE ou ALTER) acceptent la valeur NULL. Aucune synchronisation lors de la récupération : Si cette option est activée aucun enregistrement de synchronisation (entre des serveurs secondaires et le serveur primaire) n’est ajouté. L’option doit être activée pour permettre la restauration des journaux de transactions en provenance du serveur primaire. Lecture seule : Permet de déclarer la base en lecture seule, donc pas d’écriture possible. Utilisateur unique : Avec cette option, un seul utilisateur (n’importe lequel) peut avoir accès à la base, cette option est à distiguer de « Réservé au DBO » Réservé au DBO Le DBO est le propriétaire de la base, avec cette option, seul le DBO peut accéder à la base. Tronquer le journal lors de la synchronisation : Lorsque cette option est activée, le journal des transactions est validé au moment des points de synchronisation (chaque minute) est vidé. Cela représente un risque de ne pouvoir reconstruire les données perdues. ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 7 4.4 Gestion d’une base de données sur plusieurs disques ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 5 Le 17/04/17 page 8 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 5.1 Estimation de la taille d’une table 5.1.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. 5.1.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. 5.1.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. ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc ALSI S25 SQL Avancé et administration d’un SGBD Relationnel STS2 IG Application à SQL Server 6.5 Le 17/04/17 page 9 5.1.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. 5.1.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 5.1.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 5.2 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 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:\840909851.doc
