Leçon 1 sur 5
Architecture d'un SGBD et rôle de l'administrateur
Je sais décrire comment un SGBD est organisé en mémoire, en processus et en fichiers, comment il traite une requête et une transaction, et quelles sont les missions de l'administrateur de bases de données.
- 1 h 15
- 6 exercices corrigés
- QCM de 7 questions
À la fin de la leçon, vous saurez :
- Distinguer instance, serveur et base de données
- Décrire l'architecture mémoire, processus et fichiers de PostgreSQL et la comparer à celle d'Oracle
- Expliquer le trajet d'une requête et le rôle du journal des transactions
- Interroger le catalogue système pour connaître l'état d'un serveur
- Énumérer les missions et les responsabilités d'un administrateur de bases de données
Avant de commencer : Cours « Bases de données » (modèle relationnel, SQL, transactions) ; notions de systèmes d'exploitation.
1Je découvre
Djoliba Microfinance (entreprise fictive qui nous servira de fil rouge pendant tout le cours) a son siège à Kaloum et des agences à Kankan, Labé et N'Zérékoré. Toutes les opérations des clients (dépôts, retraits, remboursements de crédits) sont enregistrées dans une base PostgreSQL hébergée sur un serveur du siège.
Un lundi matin, les agents de Kankan se plaignent : « Le logiciel est très lent ». À 10 heures, le serveur s'arrête brutalement à cause d'une coupure de courant. Au redémarrage, le directeur demande, inquiet : « Les dépôts saisis juste avant la coupure sont-ils perdus ? Qui est responsable de ce serveur ? »
Mariama, récemment recrutée comme administratrice de bases de données (DBA), doit répondre. Pour cela, elle doit d'abord comprendre ce qui se passe « à l'intérieur » du SGBD : où sont les données, ce qui est en mémoire, ce qui est écrit sur le disque et à quel moment.
Comment un SGBD est-il organisé pour exécuter les requêtes rapidement sans jamais perdre une transaction validée, et quel est le rôle de celui qui l'administre ?
2Je comprends
1. Serveur, instance et base de données
Trois mots sont souvent confondus :
| Terme | Définition | Chez Djoliba |
|---|---|---|
| Serveur (machine) | l'ordinateur physique ou virtuel, avec son système d'exploitation | la machine Linux du siège |
| Instance | l'ensemble des processus et de la mémoire partagée du SGBD en cours d'exécution | le service PostgreSQL démarré |
| Base de données | l'ensemble des fichiers qui contiennent les données et les métadonnées | la base « djoliba » |
Une instance PostgreSQL (on parle aussi de cluster de bases) gère plusieurs bases dans un même répertoire de données. Chez Oracle, une instance est classiquement associée à une seule base (l'architecture multitenant permet aujourd'hui plusieurs bases « enfichables » par instance).
Si l'instance s'arrête, les fichiers restent sur le disque : la base existe toujours, mais personne ne peut plus y accéder.
2. L'architecture de PostgreSQL : processus, mémoire, fichiers
PostgreSQL suit une architecture multiprocessus :
Clients (agences) postgres (processus principal, écoute le port 5432)
│ │ │ │ crée un processus par connexion
▼ ▼ ▼ ▼
[backend 1] [backend 2] [backend 3]
\ | /
┌──────── MÉMOIRE PARTAGÉE ────────┐
│ shared_buffers (cache des pages) │
│ tampons WAL (journal) │
└──────────────────────────────────┘
| | |
checkpointer background walwriter autovacuum archiver
writer
▼ ▼ ▼
FICHIERS : données (base/), journal WAL (pg_wal/), configuration- Le processus principal (historiquement appelé postmaster) écoute les connexions et lance un processus serveur (backend) pour chaque client.
- La mémoire partagée contient surtout les shared_buffers, le cache des pages de données lues sur le disque. Chaque backend dispose en plus d'une mémoire privée (par exemple
work_mempour les tris). - Les processus d'arrière-plan : le background writer et le checkpointer écrivent sur le disque les pages modifiées ; le walwriter écrit le journal ; l'autovacuum nettoie les anciennes versions de lignes ; l'archiver copie les journaux pour la sauvegarde (leçon 4).
- Les données sont stockées en pages (ou blocs) de 8 Ko par défaut.
Comparaison avec Oracle :
| Élément | PostgreSQL | Oracle |
|---|---|---|
| Cache des données | shared_buffers | buffer cache (dans la SGA) |
| Mémoire partagée globale | mémoire partagée | SGA (System Global Area) |
| Mémoire privée d'une session | work_mem, etc. | PGA (Program Global Area) |
| Écriture des pages modifiées | background writer, checkpointer | DBWn |
| Écriture du journal | walwriter | LGWR |
| Journal des transactions | WAL (pg_wal) | fichiers redo log |
Les noms changent, les principes restent : un cache en mémoire, un journal, des processus qui écrivent en arrière-plan.
Exemple
Problème : le serveur de Djoliba dispose de 32 Go de RAM. La table des opérations contient 6 000 000 de lignes ; on suppose qu'une page de 8 Ko contient en moyenne 60 lignes.
Étape 1 : nombre de pages = 6 000 000 / 60 = 100 000 pages.
Étape 2 : taille de la table = 100 000 × 8 Ko = 800 000 Ko, soit environ 781 Mo (800 000 / 1 024).
Étape 3 : une valeur de départ courante pour shared_buffers est 25 % de la RAM d'un serveur dédié : 32 × 0,25 = 8 Go.
Conclusion : la table tient entièrement dans le cache ; une fois lue, elle peut être servie depuis la mémoire, bien plus vite que depuis le disque.
3. Le trajet d'une requête
Quand l'agent de Kankan demande le solde d'un compte, sa requête passe par plusieurs étapes dans le backend :
Méthode
- Connexion : le client s'authentifie ; le processus principal crée un backend dédié.
- Analyse (parser) : vérification de la syntaxe SQL et construction d'un arbre de requête.
- Réécriture : remplacement des vues par leur définition, application de règles.
- Planification (planner/optimiseur) : choix du meilleur plan d'exécution (lecture séquentielle ou par index, ordre et méthode des jointures) grâce aux statistiques sur les tables.
- Exécution (executor) : lecture des pages dans shared_buffers ; si une page n'y est pas, elle est lue sur le disque puis placée dans le cache.
- Renvoi du résultat au client.
SELECT solde
FROM compte
WHERE num_compte = 'KAN-004521';L'optimiseur est fondé sur les coûts : il estime le coût de plusieurs plans et retient le moins coûteux. C'est pourquoi des statistiques à jour sont essentielles (leçon 5).
4. Transactions et journal : ne jamais perdre une transaction validée
Une transaction est une suite d'opérations qui doit respecter les propriétés ACID :
| Propriété | Signification | Mécanisme principal |
|---|---|---|
| Atomicité | tout ou rien | journal, annulation (ROLLBACK) |
| Cohérence | la base passe d'un état valide à un état valide | contraintes, déclencheurs |
| Isolation | les transactions concurrentes ne se perturbent pas | verrous, MVCC (versions multiples des lignes) |
| Durabilité | une transaction validée survit à une panne | journal écrit sur disque avant la validation |
Le principe central est la journalisation anticipée (Write-Ahead Logging, WAL) : avant de confirmer un COMMIT, le SGBD écrit sur le disque la description des modifications dans le journal. Les pages de données modifiées, elles, peuvent rester en mémoire et être écrites plus tard.
BEGIN;
UPDATE compte SET solde = solde - 500000 WHERE num_compte = 'KAL-000117';
UPDATE compte SET solde = solde + 500000 WHERE num_compte = 'KAN-004521';
COMMIT;En cas de panne, au redémarrage, le SGBD rejoue le journal depuis le dernier point de contrôle (checkpoint, moment où toutes les pages modifiées ont été écrites sur le disque). Les transactions validées sont refaites ; celles qui n'étaient pas validées n'ont aucun effet.
Exemple
Problème : coupure à 10 h 00. Le dernier checkpoint date de 9 h 55. Le virement A a été validé à 9 h 58 ; le virement B était en cours (pas de COMMIT).
Étape 1 : au redémarrage, PostgreSQL relit le WAL à partir de 9 h 55.
Étape 2 : le virement A figure dans le journal avec sa validation : ses modifications sont réappliquées.
Étape 3 : le virement B n'a pas été validé : il est ignoré (atomicité).
Conclusion : les dépôts validés avant la coupure ne sont pas perdus, à condition que le disque contenant le WAL soit intact.
5. Le catalogue système : le SGBD se décrit lui-même
Le SGBD stocke ses propres métadonnées (tables, colonnes, utilisateurs, index, paramètres) dans un dictionnaire de données, appelé catalogue système dans PostgreSQL (schéma pg_catalog). Le schéma normalisé information_schema offre une vue plus portable.
SELECT version(); -- version du serveur
SELECT datname FROM pg_database; -- bases de l'instance
SHOW data_directory; -- répertoire des données
SELECT pid, usename, client_addr, state
FROM pg_stat_activity; -- sessions en cours
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'; -- tables d'un schémaChez Oracle, les vues équivalentes s'appellent DBA_TABLES, DBA_USERS, V$SESSION, etc.
6. Le métier d'administrateur de bases de données
| Mission | Contenu |
|---|---|
| Installation et configuration | installer le SGBD, régler les paramètres, organiser le stockage (leçon 2) |
| Sécurité | créer les comptes, attribuer les droits, auditer les accès (leçon 3) |
| Disponibilité | sauvegarder, tester les restaurations, préparer la reprise après sinistre (leçon 4) |
| Performance | surveiller, analyser les requêtes lentes, créer des index (leçon 5) |
| Évolution | appliquer les mises à jour de sécurité, migrer de version, prévoir la croissance |
| Conseil | aider les développeurs à concevoir des schémas et des requêtes efficaces |
On distingue souvent le DBA système (installation, sauvegarde, haute disponibilité) et le DBA d'études ou applicatif (modélisation, optimisation des requêtes). Le DBA travaille avec les administrateurs système et réseau, les développeurs et le responsable de la sécurité. Il documente tout : procédures, comptes, configuration, incidents.
3Je retiens
Je retiens
Instance = processus + mémoire ; base = fichiers sur le disque.
PostgreSQL crée un processus serveur (backend) par connexion.
shared_buffers est le cache partagé des pages de 8 Ko ; Oracle l'appelle buffer cache, dans la SGA.
Trajet d'une requête : analyse, réécriture, planification, exécution.
WAL : le journal est écrit sur disque avant de confirmer le COMMIT.
Après une panne, le SGBD rejoue le journal depuis le dernier checkpoint.
Le catalogue système (pg_catalog, information_schema) décrit tout le contenu du SGBD.
Le DBA installe, sécurise, sauvegarde, surveille, optimise et documente.
4Erreurs fréquentes
- Confondre l'instance (qui peut être arrêtée) et la base (les fichiers, qui restent sur le disque).
- Croire que les données sont écrites dans les fichiers de la table au moment du COMMIT : c'est le journal qui est écrit.
- Placer le journal WAL et les données sur le même disque fragile sans aucune sauvegarde.
- Penser qu'augmenter sans limite la mémoire de chaque session est sans danger : elle se multiplie par le nombre de connexions.
- Modifier directement les tables du catalogue système au lieu d'utiliser les commandes SQL prévues.
5Je m’exerce
1Exercice 1
(Questions de cours)
a) Définissez une instance et une base de données.
b) Citez quatre processus d'arrière-plan de PostgreSQL et leur rôle.
c) Que signifie WAL et quel est le principe de la journalisation anticipée ?
Voir le corrigéCacher le corrigé
a) L'instance est l'ensemble des processus et de la mémoire partagée du SGBD en cours d'exécution. La base de données est l'ensemble des fichiers qui contiennent les données et les métadonnées ; elle subsiste quand l'instance est arrêtée.
b) Le background writer et le checkpointer écrivent les pages modifiées sur le disque (le checkpointer réalise les points de contrôle) ; le walwriter écrit le journal ; l'autovacuum nettoie les versions mortes des lignes ; l'archiver copie les segments de journal vers la zone d'archivage.
c) Write-Ahead Logging. Avant de confirmer la validation d'une transaction, le SGBD écrit sur disque, dans le journal, la description des modifications ; les pages de données peuvent être écrites plus tard. En cas de panne, le journal permet de refaire les transactions validées.
2Exercice 2
Associez chaque élément PostgreSQL à son équivalent Oracle : shared_buffers ; walwriter ; WAL ; mémoire privée d'une session ; background writer.
Voir le corrigéCacher le corrigé
| PostgreSQL | Oracle |
|---|---|
| shared_buffers | buffer cache (dans la SGA) |
| walwriter | LGWR |
| WAL | fichiers redo log |
| mémoire privée d'une session | PGA |
| background writer | DBWn |
3Exercice 3
La table des clients de Djoliba compte 450 000 lignes ; on suppose qu'une page de 8 Ko contient en moyenne 45 lignes.
a) Combien de pages occupe la table ?
b) Quelle est sa taille en Mo (1 Mo = 1 024 Ko) ?
c) Si le disque lit 200 Mo par seconde, combien de temps faut-il pour lire toute la table depuis le disque ?
Voir le corrigéCacher le corrigé
a) 450 000 / 45 = 10 000 pages.
b) 10 000 × 8 = 80 000 Ko, soit 80 000 / 1 024 ≈ 78,1 Mo.
c) 78,1 / 200 ≈ 0,39 seconde. Lue depuis le cache en mémoire, la table serait servie beaucoup plus vite encore ; mais l'enjeu réel apparaît quand des dizaines de requêtes lisent en même temps de grosses tables.
4Exercice 4
Remettez dans l'ordre les étapes du traitement d'une requête : exécution ; planification ; connexion et création du backend ; analyse syntaxique ; renvoi du résultat ; réécriture.
Voir le corrigéCacher le corrigé
- Connexion et création du backend ; 2. analyse syntaxique ; 3. réécriture ; 4. planification ; 5. exécution ; 6. renvoi du résultat.
5Exercice 5
Écrivez les requêtes qui permettent : a) de connaître la version du serveur ; b) de lister les bases de l'instance ; c) de compter les sessions actuellement connectées ; d) de lister les tables du schéma public.
Voir le corrigéCacher le corrigé
SELECT version();
SELECT datname FROM pg_database;
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';Pour c), le filtre sur backend_type exclut les processus d'arrière-plan, qui apparaissent aussi dans pg_stat_activity.
6Exercice 6
(Type examen) Le serveur de Djoliba s'est arrêté à 15 h 20 sur une coupure de courant. Le dernier checkpoint date de 15 h 12. Trois transactions sont concernées : T1 (versement validé à 15 h 10), T2 (retrait validé à 15 h 17), T3 (remboursement commencé à 15 h 19, non validé).
a) Que devient chaque transaction après le redémarrage ? Justifiez.
b) Quelle propriété ACID garantit le sort de T3 ? et celui de T2 ?
c) Le directeur propose de désactiver l'écriture synchrone du journal « pour aller plus vite ». Quel risque prend-il ?
d) Rédigez, en cinq lignes, la fiche de poste du DBA de Djoliba.
Voir le corrigéCacher le corrigé
a) T1 a été validée avant le checkpoint : ses modifications sont déjà dans les fichiers de données ; elle est conservée. T2 a été validée après le checkpoint : sa validation est inscrite dans le WAL, écrit sur disque avant la confirmation du COMMIT ; elle est rejouée au redémarrage et conservée. T3 n'a pas été validée : elle n'a aucun effet.
b) T3 : l'atomicité (tout ou rien). T2 : la durabilité (une transaction validée survit à la panne).
c) S'il désactive l'écriture synchrone du journal au moment du COMMIT, les dernières transactions confirmées aux clients peuvent être perdues en cas de panne, alors que l'agent leur a annoncé l'opération comme faite. Pour une microfinance, c'est inacceptable sur les opérations financières. Il faut plutôt chercher la cause réelle de la lenteur (leçon 5). Désactiver complètement l'option fsync serait encore plus grave : la base elle-même pourrait être corrompue.
d) Exemple : « Le DBA installe et configure les serveurs PostgreSQL de Djoliba. Il crée les comptes et attribue les droits selon le principe du moindre privilège. Il organise les sauvegardes, teste régulièrement les restaurations et tient à jour le plan de reprise. Il surveille les performances et optimise les requêtes avec les développeurs. Il applique les mises à jour de sécurité et documente chaque procédure et chaque incident. »
Cherchez d’abord seul, sur votre cahier, puis ouvrez le corrigé pour comparer.
6Je vérifie
Choisissez une réponse pour chaque question : la correction s’affiche aussitôt.
Karamö, votre enseignant
Il a lu cette leçon. Posez-lui votre question : il vous répond ici.
Connectez-vous pour poser vos questions à Karamö.
Avec un compte Kharandi gratuit, vous cochez les leçons terminées et vous suivez votre progression, cours par cours.