Audit et optimisation MySQL 5

Arnaud Gadal, Olivier Dasini, Pascal Borghino

Description

La grande majorité des applications web s'adossent à la base de données MySQL et imposent à l'administrateur de base de données des contraintes de performances et de fiabilité.

Un concentré d'expertise pour le DBA MySQL : les bonnes pratiques, de la conception à l'optimisation

Cet ouvrage s'adresse à tous ceux qui conçoivent, exploitent et maintiennent une base de données MySQL et souhaitent optimiser les performances de leurs serveurs ou rencontrent des problèmes de charge. Il répond aux questions de l'administrateur : que faire en cas de problèmes de performances liés à la base de données ? Quelles directions prendre face à un serveur MySQL réticent, en pleine situation d'urgence alors que les utilisateurs grondent ? Qu'il s'agisse d'une mauvaise gestion de la mémoire vive, de disques saturés, d'une gestion perfectible des index, de requêtes trop gourmandes, de moteurs de stockage inadaptés, etc., cet ouvrage aidera l'administrateur ou le développeur MySQL à trouver le goulet d'étranglement en cause. Non sans décortiquer le fonctionnement du serveur MySQL et de ses différents moteurs (InnoDB, MyISAM, Merge, Memory/HEAP, Archive....), les auteurs guident le DBA à travers toutes les bonnes pratiques d'audit et d'optimisation, de la conception du schéma de la base jusqu'à la résolution des problèmes liés à la réplication, sans oublier de l'éclairer sur les choix matériels à faire pour ses serveurs.

À qui s'adresse cet ouvrage ?

• 
  À tous les administrateurs de bases de données en quête de performances ;


• 
  Aux DBA MySQL souhaitant aiguiser leur capacité à auditer et optimiser leurs bases ;


• 
  Aux administrateurs système souhaitant approfondir leur compréhension des relations entre une base MySQL et les matériel et système d'exploitation sous-jacents ;


• 
  Aux développeurs (Java, PHP, Ruby, Python...) utilisant MySQL et rencontrant des problèmes de performances.

• 
  Gérer une situation d'urgence avec MySQL


• 
  Choisir son serveur MySQL


• 
  Les moteurs de stockage


• 
  Surveiller son serveur MySQL


• 
  Exploiter les journaux de MySQL


• 
  Optimiser sa base de données : du schéma aux requêtes


• 
  Optimiser son serveur MySQL


• 
  La réplication MySQL


• 
  Où trouver de l'aide ?

Sujets

• Sciences & Techniques
• Informatique
• Bases de données
• Systèmes d'exploitation
• Données
• Programmation
• Open Source
• Langages
• Computers
• Techniques
• GNU/Linux
• Código abierto
• Unix
• Apache
• Web
• SQL
• Système d'exploitation
• Langage informatique
• Internet
• Linux
• Application Web
• Donnée
• Développement de logiciel
• Technique
• LAMP
• Apache (homonymie)
• Langage humain
• MySQL
• Serveur Web
• World Wide Web
• PHP
• Programmation informatique
• Base de données

Informations

• Publié par: Editions Eyrolles
• Date de parution: 07 juillet 2011
• Nombre de lectures: 95
• EAN13: 9782212412413
• Langue: Français
• Poids de l'ouvrage: 1 Mo

Extrait

Audit et optimisation MySQL 5


Bonnes pratiques pour l’administrateur


Pascal Borghino

Olivier Dasini

Arnaud Gadal
Groupe Eyrolles
61, bd Saint-Germain
75240 Paris cedex 05
www.editions-eyrolles.com

En application de la loi du 11 mars 1957, il est interdit de reproduire intégralement ou partiellement le présent ouvrage, sur quelque support que ce soit, sans autorisation de l’éditeur ou du Centre Français d’Exploitation du Droit de Copie, 20, rue des Grands-Augustins, 75006 Paris.

ISBN 978-2-212-12634-1
© Groupe Eyrolles

Le format ePub a été préparé par Isako www.isako.com à partir de l'édition papier du même ouvrage.
Table
Couverture
Titre
Licence
Table
Avant-propos
Un ouvrage en français pour aller plus loin
À qui s’adresse cet ouvrage ?
Structure de l’ouvrage
Remerciements
1 - Gérer une situation d’urgence avec MySQL
À chaque degré d’urgence sa panoplie d’outils
Temps de résolution : dix minutes
Étape 0 : informez et communiquez !
Ne restez pas seul et discutez avec d’autres administrateurs
Consultez les informations système : journal d’erreurs, activités disques et processeur...
Tentez de vous connecter à la base
Supprimer les requêtes les plus lourdes
Éviter que l’authentification des utilisateurs repose sur un DNS : l’erreur unauthenticated user
Consulter son système de surveillance
Temps de résolution : une heure
La chasse aux requêtes lentes
Réécrire les requêtes trop coûteuses
Les problèmes de réplication
Temps de résolution : une journée
Conseils généraux face à l’urgence
Tirer profit du passé
Anticiper les problèmes
L’entraînement à l’urgence
Enregistrer les données de l’incident
L’état d’esprit à adopter dans l’urgence
2 - Choisir son serveur MySQL
La mise à jour matérielle, une étape nécessaire ?
Les questions à se poser
Du 64 bits oui... mais partout !
Limites des systèmes 32 bits
Choisir ses processeurs
État des lieux
Les solutions face aux problèmes de montée en charge
Choisir son processeur : les critères de choix
Quelle est l’utilisation actuelle de vos processeurs ?
Fréquence vs nombre de cœurs
Benchmarks, encore et toujours
Choisir ses disques et son système RAID
Temps d’accès versus taux de transfert
La technologie RAID
Les principaux niveaux de RAID
RAID 0 (ou volume agrégé par bande/stripping )
RAID 1 (ou mirroring)
RAID 5 (ou volume agrégé par bande à parité répartie)
RAID 10 (1+0)
Les deux implémentations du RAID : logicielle et matérielle
Intérêt du cache sur une carte contrôleur RAID
Indispensable batterie
Le cache interne des disques : une arme à double tranchant
Les SSD : futur hit ?
MySQL et la mémoire
Comment MySQL utilise-t-il la mémoire ?
3 - Les moteurs de stockage
Mécanismes d’un moteur de stockage
Installation et suppression d’un moteur
Les forces en présence : moteurs utilisé par l’application
Les critères de choix d’un moteur
Moteurs disponibles : InnoDB, MyISAM, Merge, Memory, Archive
Le moteur InnoDB
MyISAM
Mécanismes internes de MyISAM et formats de stockage
Le moteur MERGE pour agréger plusieurs tables MyISAM
Le moteur MEMORY (anciennement HEAP)
Le moteur ARCHIVE pour un archivage compressé
Autres moteurs
XtraDB
Falcon
Federated
Example
Blackhole
CSV
IBMDB2I
NDB (Network Database)
Moteurs communautaires et autres
Maria
PBXT
BLOB Streaming Engine (MyBS)
Mdbtools
Kickfire
TokuDB
Spider
Rethinkdb
4 - Surveiller son serveur MySQL
Où trouver les informations pertinentes ?
Variables système et variables de statut
Quels outils choisir ?
Intérêt des outils de surveillance
Outils et commandes fournis par MySQL
Catégorie General
Catégorie Performance
Query Cache
Keys
Sort
Networking
La commande SHOW ENGINE INNODB STATUS
Les clés étrangères
Les deadlocks
Le MVCC
Le buffer pool et la mémoire
INFORMATION_SCHEMA
Connaître et savoir exploiter les outils de surveillance
Qu’est-ce que la performance ?
LVM : la gestion des volumes logiques
Étude de cas : analyse d’un serveur MySQL
Mesurer l’activité du serveur
Les outils système
La commande iostat
La commande vmstat
Les commandes netstat et mpstat
Outils d’audit : MySQLTuner et mysqlreport
Outils d’analyse temsp réel : mytop, mtop, innotop et maatkit
Évaluer les performances d’un système
Bien dimensionner un système (capacity planning)
5 - Exploiter les journaux de MySQL
Le journal des erreurs
Identifier et résoudre les problèmes
Modifier le tablespace ou les journaux d’InnoDB
Paramètre incorrect dans le fichier de configuration
Erreurs liées à la réplication
Erreurs diverses
Le journal des requêtes lentes
Principe de fonctionnement
Journaliser dans une table
Le journal général des connexions et requêtes
Exemples d’utilisations de la journalisation générale ?
La journalisation binaire
Bonnes pratiques
6 - Optimiser sa base de données : du schéma aux requêtes
Conception de la base de données
Normalisation/dénormalisation
Ajouter des colonnes dans une table
Création de tables d’agrégation
Création de schémas orientés
Des types de données ajustés
Les jointures
Les index
Index B-tree
Index B+tree
Index hash
Optimisation des requêtes
Connaître l’optimiseur pour mieux le comprendre
La commande EXPLAIN pour analyser l’exécution des requêtes
Exemple d’optimisation d’un plan d’execution
Indexer les premiers caractères d’une colonne
Index couvrant (covering index)
Préfixe d’index (leftmost prefix indexes)
Taille des index
Récapitulatif des bonnes pratiques d’optimisation des requêtes
Avant de créer votre requête, se demander si elle est utile
Les données ont-elles leur place dans la base ?
Ramener seulement les enregistrements nécessaires
Jeux d’essais
Superviser sa base de données
Tester et valider ses changements avant de les appliquer en production
Utiliser les types de champs les plus petits possible
Prêter une attention particulière aux colonnes de jointures
Penser aux summary tables
Découper les requêtes complexes en plusieurs plus simples.
Import massif de données
Attention aux mythes
7 - Optimiser son serveur mySQL
Tuning serveur : variables de session, variables globales, handlers
Les variables de session
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
tmp_table_size et max_heap_table_size
Les variables globales au serveur
Le cache de table
Le cache de thread
Table_locks_immediate et Table_locks_waited
Aborted_clients
Aborted_connects
Les handlers
Exemple d’optimisation d’une requête
Les droits des utilisateurs
Optimisations pour InnoDB, MyISAM et MEMORY
Optimisation InnoDB
Optimisation MyISAM
Cache d’index multiples
Optimisation Memory
Le cache de requêtes (query cache)
Gestion du cache de requêtes
Le partitionnement
Le partitionnement par RANGE
Le partitionnement par LIST
Le partitionnement par HASH
Le partitionnement par KEY
Partitionner sur différents disques
Partitionner sur différents disques avec MyISAM
8 - La réplication MySQL
Introduction à la réplication
Intérêt de la réplication
Le dimensionnement horizontal (scale out)
La sauvegarde à chaud (hot backup)
Le basculement automatique (Failover)
Redondance géographique
Le cas du décisionnel
Tester une nouvelle version de MySQL
À l’intérieur de la réplication
Mise en place de la réplication
Configuration du maître
Configuration de l’esclave
Configuration avancée de l’esclave
Commandes de la réplication
Sur l’esclave
Sur le maître
Problèmes liés à la réplication
IO_THREAD stoppé
SQL_THREAD stoppé
Architectures de réplication avancées
Dual master en actif/passif
Configuration
Exemple : switchover pour une mise à jour online des serveurs MySQL
Récapitulatif
Dual master en actif/actif
Récapitulatif
Réplication circulaire (nombre de réplications > 2)
Esclave relais
Configuration
Récapitulatif
Partitionnement adapté au décisionnel
Configuration
Récapitulatif
Bonnes pratiques
9 - Où trouver de l’aide ?
Trouver de l’aide en urgence
Les ressources internes
Les ressources externes
Les moteurs de recherche
Le support officiel MySQL
Les organismes externes
Trouver de l’aide hors contexte d’urgence
Formations
Où poser votre question ?
L’association LeMug
Les blogs
Les forums et mailing-lists MySQL officiels
Aller plus loin et enrichir ses connaissances
La blogosphère de la communauté
Les séminaires web
Outils et sources de MySQL
La conférence MySQL
Les certifications
Index
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Avant-propos

Un ouvrage en français pour aller plus loin
L’idée de départ de cet ouvrage fut d’écrire ce que nous-mêmes aurions aimé trouveren librairie au rayon MySQL.
Le contenu que nous vous proposons aujourd’hui, et qui sera détaillé dans quelquesparagraphes, est le fruit de la diversité de nos expériences respectives (un administrateur base de données, un expert MySQL et un architecte bases de données). En parcourant ce livre, vous profiterez de nos expériences acquises auprès de grandscomptes de l’univers Internet, tels que Orange Business Services, Virgin Mobile ouencore Yahoo!
Nous souhaitions écrire un livre permettant de mieux comprendre à la fois MySQLen tant que pièce logicielle, mais aussi son lien avec le serveur physique sur lequelcette base de données est installée. Cette subtile interaction entre logiciel et matérieloù interviennent plusieurs centaines de variables et autres paramètres, est susceptibled’atteindre de hautes performances lorsque tous agissent de concert.
Cependant, il ne s’agit pas uniquement de décrire les mécanismes responsables desmultiples comportements de MySQL observés ; il faut surtout les expliquer.
Directement issu de nos