Aller au contenu
Administration de bases de données · L3

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 :

TermeDéfinitionChez Djoliba
Serveur (machine)l'ordinateur physique ou virtuel, avec son système d'exploitationla machine Linux du siège
Instancel'ensemble des processus et de la mémoire partagée du SGBD en cours d'exécutionle service PostgreSQL démarré
Base de donnéesl'ensemble des fichiers qui contiennent les données et les métadonnéesla 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_mem pour 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émentPostgreSQLOracle
Cache des donnéesshared_buffersbuffer cache (dans la SGA)
Mémoire partagée globalemémoire partagéeSGA (System Global Area)
Mémoire privée d'une sessionwork_mem, etc.PGA (Program Global Area)
Écriture des pages modifiéesbackground writer, checkpointerDBWn
Écriture du journalwalwriterLGWR
Journal des transactionsWAL (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

  1. Connexion : le client s'authentifie ; le processus principal crée un backend dédié.
  2. Analyse (parser) : vérification de la syntaxe SQL et construction d'un arbre de requête.
  3. Réécriture : remplacement des vues par leur définition, application de règles.
  4. 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.
  5. 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.
  6. 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éSignificationMécanisme principal
Atomicitétout ou rienjournal, annulation (ROLLBACK)
Cohérencela base passe d'un état valide à un état validecontraintes, déclencheurs
Isolationles transactions concurrentes ne se perturbent pasverrous, MVCC (versions multiples des lignes)
Durabilitéune transaction validée survit à une pannejournal é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éma

Chez Oracle, les vues équivalentes s'appellent DBA_TABLES, DBA_USERS, V$SESSION, etc.

6. Le métier d'administrateur de bases de données

MissionContenu
Installation et configurationinstaller 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)
Performancesurveiller, analyser les requêtes lentes, créer des index (leçon 5)
Évolutionappliquer les mises à jour de sécurité, migrer de version, prévoir la croissance
Conseilaider 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é
PostgreSQLOracle
shared_buffersbuffer cache (dans la SGA)
walwriterLGWR
WALfichiers redo log
mémoire privée d'une sessionPGA
background writerDBWn

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é
  1. 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.

1Une instance PostgreSQL est constituée :
Voir la réponse

Réponse B : des processus et de la mémoire partagée en cours d'exécution. Les fichiers forment la base ; l'instance est la partie « vivante » du SGBD.

2Dans PostgreSQL, chaque nouvelle connexion cliente :
Voir la réponse

Réponse B : reçoit son propre processus serveur (backend). L'architecture de PostgreSQL est multiprocessus : un backend par connexion.

3La taille par défaut d'une page de données PostgreSQL est :
Voir la réponse

Réponse B : 8 Ko. 8 Ko par défaut ; 16 Mo est la taille par défaut d'un segment de journal WAL.

4Au moment du COMMIT, ce qui est obligatoirement écrit sur le disque, c'est :
Voir la réponse

Réponse B : l'enregistrement correspondant dans le journal WAL. C'est le principe de la journalisation anticipée.

5L'équivalent Oracle de shared_buffers est :
Voir la réponse

Réponse B : le buffer cache de la SGA. La PGA est la mémoire privée ; LGWR écrit le journal.

6Quelle étape choisit entre une lecture séquentielle et une lecture par index ?
Voir la réponse

Réponse C : la planification (optimiseur). L'optimiseur estime le coût des plans possibles et choisit le moins coûteux.

7Une transaction non validée au moment d'une panne :
Voir la réponse

Réponse B : n'a aucun effet après la reprise. Atomicité : tout ou rien.

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ö.

Vous avez fini la leçon ?

Avec un compte Kharandi gratuit, vous cochez les leçons terminées et vous suivez votre progression, cours par cours.

Karamö
Un point pas clair ? Dites à Karamö ce qui vous bloque dans cette leçon : il vous l’explique autrement, pas à pas, sans faire le travail à votre place. Demander à Karamö
Signaler une erreur dans cette leçon

Toutes les leçons de Administration de bases de données

Pose ta question à Karamö