Master DBA Cours3: Architecture des Bases de Données PDF

Administration et gestion de données Cours 3: Architecture de bases des données Hassane TAHIR UFR de MATHÉMATIQUES et INFORMATIQUE Master Informatique M1: Parcours Données, Connaissances et Intelligence  UNIVERSITÉ DE PARIS 2 Feuille de route Co...

Administration et gestion de données Cours 3: Architecture de bases des données Hassane TAHIR UFR de MATHÉMATIQUES et INFORMATIQUE Master Informatique M1: Parcours Données, Connaissances et Intelligence  UNIVERSITÉ DE PARIS 2 Feuille de route Cours 3 : Architecture des bases de données →Composants clés de l'architecture des bases de données →Présentation générale de la base de données PostgreSQL →Fonctionnalités clés de PostgreSQL 9 à 17 →Architecture de la base de données PostgreSQL →Liens entre répertoires et fichiers dans PostgreSQL →Présentation générale de la base de données Oracle →Fonctionnalités clés de Oracle 9 à 23ai →Architecture de la base de données Oracle →Présentation générale de la base de données MySQL →Fonctionnalités clés de MySQL 3.x à 8.x →Architecture de la base de données MySQL  UNIVERSITÉ DE PARIS 3 Composants clés de l'architecture des bases de données Composants mémoire Buffer Cache/Pool: Zone mémoire où sont stockées temporairement les pages de données et les index fréquemment utilisés. Journalisation (Transaction Logs):Enregistre les modifications pour garantir la durabilité des données et permettre des récupérations en cas de panne. Cache des requêtes (Query Cache): Stocke les résultats de requêtes fréquemment exécutées. Composants de processus Serveur de base de données: Exécute les requêtes SQL, gère les connexions des utilisateurs et l’accès aux fichiers de données. Processus de fond (ou d’arrière plan) (Background Processes) : Responsables de tâches spécifiques, comme la gestion des journaux, le nettoyage des données inutiles, la sauvegarde ou la récupération après incident. Assurent les tâches essentielles (gestion des journaux, récupération, etc.).  UNIVERSITÉ DE PARIS 4 Présentation générale de la base de données PostgreSQL PostgreSQL est un système de gestion de base de données relationnelle et objet (héritage des tables). Créé en 1986 par l’Université de Californie à Berkeley et devient open source en 1996. PostgreSQL est multi-plateformes (Linux, Windows, macOS) et conforme aux standards SQL. Reconnu pour sa robustesse, sa performance et son extensibilité ou scalabilité (Transactions ACID et Index avancés). Nouvelles fonctionnalités JSON/JSONB pour les données semi-structurées. Réplication et haute disponibilité (Streaming, Logical replication). Extensions comme PostGIS pour les données géospatiales. PostgreSQL suit un modèle client-serveur/multi-processus. Il est composé d’une mémoire partagée, des processus serveurs (Postmaster, Autovacuum, BgWriter, WAL Writer). Permettre les transactions et WAL (Write-Ahead Logging). Utilisation le mécanisme de Concurrence MVCC (Multi-Version Concurrency Control) qui permet de gérer l'accès concurrent aux données sans blocage.  UNIVERSITÉ DE PARIS 5 Fonctionnalités clés de PostgreSQL 9 à 17 Version Année Fonctionnalités Clés 9.x 2010 Introduction de la réplication en continu, des transactions multiversion (MVCC), du support JSONB, et de la requête parallèle pour améliorer les performances et la flexibilité des données. 10 2017 Réplication logique, partitionnement de table déclaratif et amélioration du parallélisme des requêtes. 11 2018 Améliorations de la robustesse et des performances du partitionnement, support des transactions dans les procédures stockées et compilation à la volée (JIT) pour les expressions. 12 2019 Améliorations des performances des requêtes, support des expressions de chemin SQL/JSON et colonnes générées. 13 2020 Gains d'espace et de performances grâce à la déduplication des entrées d'index B-tree et amélioration des performances pour les requêtes utilisant des agrégats ou des tables partitionnées. 14 2021 Ajout des clauses SEARCH et CYCLE conformes au standard SQL pour les expressions de table communes et possibilité d'ajouter DISTINCT aux clauses GROUP BY. 15 2022 Améliorations de performance, notamment pour le tri et la compression des fichiers WAL avec LZ4 et Zstandard. Ajout de commandement SQL standard MERGE pour simplifier les opérations d'insertion, de mise à jour et de suppression conditionnelles. Amélioration de la réplication logique et les permissions du schéma PUBLIC. 16 2023 Améliorations de performance avec le parallélisme des jointures FULL et RIGHT, l'accélération CPU pour les chaînes ASCII et JSON, et le chargement massif amélioré. Ajout de la réplication logique à partir des serveurs secondaires et la réplication bidirectionnelle pour une haute disponibilité et la reprise après sinistre. 17 2024 Introduit d’une nouvelle structure de mémoire pour le processus de vacuum. Amélioration des performances des requêtes avec des clauses IN utilisant des index B-tree et optimisation de l'accès au stockage. Ajout de la commande SQL/JSON JSON_TABLE pour une meilleure gestion des données JSON  UNIVERSITÉ DE PARIS 6 Architecture de la base de données PostgreSQL Mémoire Partagée Shared Buffer : Chaque fois que PostgreSQL a besoin de lire ou d’écrire des données, il les charge d’abord dans le Shared Buffer. Les données fréquemment accédées sont chargées dans ce cache, ce qui améliore les performances globales. WAL Buffer : prend en charge l’archivage des transcations. Les données sont d’abord enregistrées dans ce journal avant d’écrire les modifications sur le disque. Il permet de restaurer les transactions en cas de panne ou de crash. CLOG Buffers : stockent en mémoire une partie du Commit Log pour les transactions récentes. Cela permet d'accéder rapidement à l'état des transactions en cours, sans avoir besoin de lire les informations directement depuis le disque.. Processus Serveur Postmaster (ou Processus principal) : Gère les connexions entrantes des clients. Il Lance des processus enfants dédiés pour chaque client connecté. Il s supervise aussi les processus auxiliaires nécessaires au bon fonctionnement du serveur. Processus Enfants et Auxiliaires Backend (Processus dédiés aux connexions). Autovacuum : Nettoie et analyse automatiquement les tables Writer (Background Writer) : Écrit périodiquement les pages modifiées du cache sur le disque. WAL Writer : S’occupe d’écrire les transactions dans le journal WAL. Checkpointer : Gère les checkpoints (i.e., écriture des données sur le disque pour réduire les pertes en cas de crash). Stats Collector (ou collecteur de statistiques): est un processus d’arrière-plan dédié à la collecte et à la mise à jour des statistiques sur l'activité de la base de données.. Archiver : Sauvegarde les journaux WAL dans un emplacement sécurisé pour une éventuelle récupération. Fichiers physiques Data files : Ils permettent de stocker les données. WAL files : C’est l’endroit où sont écrites les données (et aussi sur le shared buffer) avant d’être écrites sur le disque Log files : Ils permettent d’enregistrer les messages d’erreur, les connexions au serveur, etc.  UNIVERSITÉ DE PARIS Architecture de PostgreSQL 7 Architecture de la base de données PostgreSQL Processus Serveur Mémoire Fichiers Physiques Shared Buffers WAL Buffers Postgre Fichiers de données CLOG Buffers Autres Buffers Postmaster Fichiers WAL Client Processus utilitaires Fichiers Log Stats Collector Checkpointer WAL Writer Interface Client Auto Vacuum Logging Background Launcher Collector Writer Archive Logs Application Client Archiver  UNIVERSITÉ DE PARIS 8 Liens entre répertoires et fichiers dans PostgreSQL Type fichiers/ Rôle Emplacement Linux Emplacement Windows répertoires Journaux de Contient les fichiers de /var/lib/postgresql//main/pg_wal C:\Program transactions journaux WAL pour la Files\PostgreSQL\\data\pg_wal (pg_wal) durabilité et la réplication. Statuts des Stocke les informations sur /var/lib/postgresql//main/pg_xact C:\Program transactions l'état des transactions Files\PostgreSQL\\data\pg_xact (pg_xact) (committed, aborted, etc.). Tablespaces Contient des liens /var/lib/postgresql//main/pg_tblspc C:\Program (pg_tblspc) symboliques vers les Files\PostgreSQL\\data\pg_tblspc emplacements des tablespaces. Traces (log) Journaux générés (fichiers /var/log/postgresql/ C:\Program texte) dans un répertoire Ou nom définit dans log_directory (Paramètre dans Files\PostgreSQL\\data\log défini par la configuration. le fichier postgresql.conf) Configuration définir les paramètres /etc/postgresql//main/ (contient les C:\Program (etc) globaux de PostgreSQL fichiers postgresql.conf, pg_hba.conf, pg_ident.conf) Files\PostgreSQL\\data\ Données Stocke les données /var/lib/postgresql//main/ C:\Program (var/lib) persistantes de PostgreSQL Files\PostgreSQL\\data\ et transactions. Binaire Contient les fichiers /usr/lib/postgresql//bin/ C:\Program principal binaires pour exécuter et Files\PostgreSQL\\bin\ (/usr/bin) administrer PostgreSQL  UNIVERSITÉ DE PARIS 9 Présentation générale de la base de données Oracle Système de gestion de bases de données relationnel (RDBMS) crée par Larry Ellison, Bob Miner et Ed Oates et lancée initialement en1979 (version 2). Licence Commerciale (avec certaines éditions gratuites comme Oracle Express). Dédiée aux entreprises pour gérer des données critiques, supporte les transactions complexes et l’analyse avancée. Composée d’une instance (processus et mémoire) et une base de données (fichiers physiques contenant les données) Composants clés de la base Oracle SGA (System Global Area) PGA (Program Global Area) Tablespaces Datafiles (Fichiers de données) Points forts Fiabilité : Oracle est réputé pour sa robustesse et sa capacité à gérer des données critiques. Scalabilité : Adapté aux petites bases comme aux environnements massifs. Écosystème riche : Intégration avec Oracle Cloud et autres produits Oracle (EBS, Fusion, etc.).  UNIVERSITÉ DE PARIS 10 Fonctionnalités clés de Oracle 9 à 23ai Version Année Fonctionnalités Clés Oracle 9 2001 Oracle Real Application Clusters (RAC), Oracle XML DB, Data Mining, Streams Oracle 10g 2003 Infrastructure Grid, Oracle ASM, Flashback Database, Automatic Database Diagnostic Monitor Oracle 10gR2 2005 Test de l'application réelle, Database Vault, Indexation en ligne, Compression avancée Oracle 11g 2007 Active Data Guard, Secure Files, Exadata Oracle 11gR2 2009 Data Redaction, Compression hybride en colonne, Cluster File System, Golden Gate Replication Oracle 12c 2013 Architecture multitenant, In-Memory Column Store, JSON natif, SQL Pattern Matching Oracle 12cR2 2016 Sharding natif, Zero Data Loss Recovery Appliance, Exadata Cloud Service Oracle 18c 2018 Base de données autonome, Data Guard Multi-Instance Redo Apply, Polymorphic Table Functions Oracle 19c 2019 Améliorations de la performance et de la sécurité Oracle 21c 2021 Fonctionnalités avancées pour les développeurs et les administrateurs Recherche vectorielle IA, JSON Relational Duality, Validation du schéma JSON, Microservices transactionnels, OKafka, Graphes de propriétés opérationnels, SQL/PGQ, Privilèges de schéma, Rôle Oracle 23ai 2024 développeur, Pare-feu SQL en-database, TLS 1.3, Intégration avec Azure Active Directory OAuth2, True Cache pour le caching intermédiaire, Lecture par PDB  UNIVERSITÉ DE PARIS 11 Architecture de la base de données Oracle Composants physiques Fichiers de données (Datafiles) : Stockent les données utilisateur et les métadonnées. Fichier de contrôle (Control file) : Contient des informations essentielles pour le fonctionnement de la base (structure, checkpoints). Fichiers de journaux de reprise (Redo logs) : Utilisés pour la récupération en cas de panne. Composants mémoires SGA (System Global Area) : Mémoire partagée utilisée par tous les processus. PGA (Program Global Area) : Mémoire privée dédiée aux processus serveur. Composants de processus Processus de fond (ou d’arrière plan) (Background Processes) : Assurent les tâches essentielles (gestion des journaux, récupération, etc.). Processus utilisateur : Interagissent avec la base pour exécuter des requêtes et manipuler les données Interactions des composants Les processus utilisateurs envoient des commandes SQL via le listener (processus réseau). Les données sont lues/écrites entre les fichiers physiques et la mémoire (SGA). Les journaux de reprise permettent d’assurer la durabilité des transactions.  UNIVERSITÉ DE PARIS 12 Architecture de la base de données Oracle (Source: https://docs.oracle.com/)  UNIVERSITÉ DE PARIS 13 Présentation générale de la base de données MySQL Système MySQL est un Système de Gestion de Bases de Données Relationnelles (SGBDR), Initialement développé en 1995 par MySQL AB. Disponible gratuitement (Open-source) avec des éditions commerciales (MySQL Enterprise). Optimisé pour traiter un grand volume de données. Sécurité avancée (Authentification, gestion des utilisateurs et éléments de chiffrement). Fonctionne sur Windows, Linux, macOS et autres OS. Support des moteurs de stockage (InnoDB, MyISAM) Points forts de MySQL Fiabilité et stabilité Performances élevées Facilité d’intégration Scalabilité (Adapté à des bases de données petites comme gigantesques). Coûts réduits. Gestion des transactions Réplication pour la haute disponibilité Sauvegardes et restaurations rapides.  UNIVERSITÉ DE PARIS 14 Fonctionnalités clés de MySQL 3.x à 8.x Version Année Fonctionnalités Clés 3.x 1996 3.19 (fin 1996) : Première version publique de MySQL. 3.20 (janvier 1997) : Améliorations des performances et de la stabilité. 3.21 (1998) : Version de production stable. 3.22 (1998) : Introduction des versions alpha et bêta pour les tests. 3.23 (janvier 2001) : Version de production stable avec des améliorations significatives. 4.x 2003 4.0 (mars 2003) : Introduction des requêtes UNION. 4.1 (octobre 2004) : Ajout des sous-requêtes, des instructions préparées, et des index R-trees et B-trees. 5.x 2005 5.0 (octobre 2005) : Introduction des curseurs, des procédures stockées, des déclencheurs, des vues et des transactions XA. 5.1 (novembre 2008) : Ajout du planificateur d'événements, du partitionnement, de l'API des plugins, de la réplication basée sur les lignes et des tables de journaux serveur. 5.5 (2010) : InnoDB devient le moteur de stockage par défaut, avec des améliorations de performances et de la prise en charge du multiprocessing. 5.6 (février 2013) : Améliorations de l'optimiseur de requêtes, du débit transactionnel d'InnoDB, et ajout d'API NoSQL de type memcached. 5.7 (octobre 2015) : Support natif du type de données JSON, améliorations des performances et de la sécurité. 8.x 2018 8.0 (avril 2018) : Introduction du Document Store NoSQL, des transactions atomiques et sûres en cas de crash, de la syntaxe JSON étendue, et de nouvelles fonctions pour le traitement des données JSON. 8.0.38 (avril 2018) : Compression des fichiers WAL, Commande SQL standard MERGE, Réplication logique améliorée.  UNIVERSITÉ DE PARIS 15 Architecture de la base de données MySQL Composants principaux de MySQL Composants physiques Fichiers de données (Datafiles) : Contiennent les tables et les index. Fichiers de logs : Journaux binaires (Binary Logs) et journaux de transactions (Redo Logs) pour assurer la durabilité et la récupération des données. Fichiers de configuration : Paramètres de configuration (ex. : my.cnf ou my.ini). Composants mémoire Buffer Pool (InnoDB) : Gère le cache des pages, les transactions et le stockage temporaire des modifications. Query Cache (facultatif) : Mémorise les résultats de certaines requêtes SQL pour accélérer les performances. Processus MySQL Serveur MySQL : Gère les connexions, les requêtes, et l'accès aux données. Thread unique par connexion : Chaque connexion utilisateur est traitée par un thread distinct. Connecteurs : Interfaces client pour communiquer avec la base via différents langages de programmation (ex. : PHP, Python, Java). Moteurs de stockage InnoDB : Moteur transactionnel par défaut, avec support ACID. MyISAM : Ancien moteur non transactionnel, plus rapide pour les lectures. Autres : Memory, CSV, Federated (moteur de stockage disponible dans MySQL permettant d’accéder à des données dans une base de données distante et d'exécuter des requêtes SQL sur des tables distantes), etc.  UNIVERSITÉ DE PARIS 16 Architecture de la base de données MySQL (Source: https://dev.mysql.com/