Nous utilisons des cookies pour vous proposer des contenus et services adaptés.
En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies.
Ce site respecte la loi RGPD du 25 mai 2018. Pour en savoir plus, cliquez ici
(+33) 02 47 61 20 22 Du lundi au vendredi de 10H à 16H30

MySQL 5 Audit et optimisation
Bonnes pratiques pour l'administrateur

MySQL 5  Audit et optimisation - eyrolles - 9782212126341 -
MySQL 5 Audit et optimisation 

Auteur : 

Editeur : Eyrolles

Date parution :

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, Marge, 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.

Auteurs :

Les auteurs : Pascal Borghino est architecte de bases de données chez Yahoo! International. II est confronté au quotidien à de nombreux problèmes, tant au niveau du design, de l'extensibilité que des performances. II est président et fondateur du MySOL User Group Francophone (LeMUG.fr) et créateur du blog www. dbnewz. com. Olivier Dasini a 10 ans d'expérience en tant que consultant et formateur certifié MySOL. Aujourd'hui expert en bases de données chez Orange Business Services, il milite pour la promotion des logiciels libres et est également fondateur du blog de vulgarisation autour de MySQL https://dasini.net/blog. II est co-fondateur du MySQL User Group Francophone LeMUG.fr. Arnaud Gadal est l'administrateur des bases de données MySQL de Virgin Mobile. II est certifié MySQL 5 (développement, administration, cluster), membre du MySQL User Group et principal auteur sur www.dbnewz.com


En suivant ce lien, retrouvez tous les livres dans la spécialité Informatique-Audiovisuel.

Descriptif : 

Reliure :
Broché
Nbr de pages :
266
ISBN 10 :
2212126344
ISBN 13 :
9782212126341
35,50 €
Sur commande
Expédié sous 8 à 14 jours (en savoir+)

Avis clients sur MySQL 5 Audit et optimisation - eyrolles -

(Ils sont modérés par nos soins et rédigés par des clients ayant acheté l'ouvrage)
Donnez votre avis

Sommaire

Table des mati?s

CHAPITRE 1

G?r une situation d'urgence avec MySQL 1

chaque degr?'urgence sa panoplie d'outils 1
Consultez les informations syst? :journal d'erreurs,
?iter que l'authentification des utilisateurs repose sur un DNS: l'erreur unauthenti-
ASTUCE Les tables statiques ?a rescousse Il

Temps de r?lution: dix minutes 2

?ape 0
: informez et communiquez!. .............................. .. 2

Ne restez pas seul et discutez avec d'autres administrateurs .............. .. 3

activit?disques etprocesseur... .................................... .. 3

Tentez de vous connecter ?a base ................................. .. 4

ATTENTION Pr?utions ?rendre avec une table MyISAM corrompue 4

ASTUCE D?lement page par page pour SHOW FULL PROCESSLIST 5

AVOIR ?iter l'empilement desrequ?s et d?ypterle SHOW PROCESSLIST 5

REMARQUE Se r?rer aux chapitres concern?6

Supprimer les requ?s les plus lourdes. .............................. .. 6

PRATIQUE Supprimer des requ?s rapidement 7

cated user .................................................... .. 8

Consulter son syst? de surveillance. .............................. .. 8

Tranche de vie d'une campagne marketing improvis?9

Temps de r?lution: une heure 10

La chasse aux requ?s lentes. .................................... .. 10

RE Un chapitre consacr? l'?de des journaux 10

R?rire lesrequ?s trop co?ses 10

Les probl?s de r?ication. .................................... .. 12

Temps de r?lution: une journ?13

LIRE Chapitre 8 consacr? la r?ication 13

PRATIQUE Un probl? peut en cacher un autre 14

Conseils g?raux face ?'urgence 15

Tirer profit du pass?.......................................... .. 15

Anticiper les probl?s. ........................................ .. 15

ATTENTION Modifications ?haud, en production 15

Enregistrer les donn? de l'incident , 16

L'?t d'esprit ?dopter dans l'urgence 16

Trouver de l'aide 17

CHAPITRE 2

Choisir son serveur MySQL 19

La mise ?our mat?elle, une ?pe n?ssaire? 20

Les questions ?e poser 20

ASTUCE Identifier les goulets d'?anglements 20

ASTUCE Optimiser son serveur 21

JARGON Scaling up, scaling out et scaling back 22

Du 64 bits oui mais partout! 22

Limites des syst?s 32 bits 23

AmNTION Ne soyez pas trop gourmands.' 23

RESSOURCES EN LIGNE Davantage de d?ils sur l'adressage m?ire 24

Choisir ses processeurs 25

?at des lieux 25

Les solutions face aux probl?s de mont?en charge 25

JARGON Architecture SMP vs NUMA 25

REMARQUE La comp?tion omnipr?nte entre les diff?nts acteurs 26

RE tGALEMENT ••••••••••••••••••••••••••••••••••••••••••••••••••• 27

Choisir son processeur: les crit?s de choix 27

Quelle est l'utilisation actuelle de vos processeurs ? 27

AVOIR MySQL et la gestion des threads 27

Fr?ence vs nombre de cœurs 28

JARGON OLTP, OLAP: deux cat?ries de syst?s ??r diff?mment 28

Benchmarks, encore et toujours 29

ALIRE tGALEMENT Mesurer les peifOrmances de son syst? 29

RAPPEL Configurer son serveur MySQL 30

ASTUCE Pour allerplus loin 31

Choisir ses disques et son syst? RAID 31

ASAVOIR Temps d'acc?m?ire vs temps d'acc?disques 32

Temps d'acc?versus taux de transfert 32

ETENIR Lecture/?iture al?oire ou s?entielle 33

La technologie RAID 34

AmNTION La r?ication et la mont?en charge des ?itures 34

Les principaux niveaux de RAID. .................................. 34

REMARQUE Les opposants au RAID 5 36

RESSOURCES EN LIGNE D'autres niveaux de RAID existent 37

Les deux impl?ntations du RAID: logicielle et mat?elle 38

REMARQUE Carte contr?r RAID,force etfaiblesse ?afois 38

Int?t du cache sur une carte contr?r RAID ...................... .. 39

RApPEL Le cache de requ?s en amont de la carte RAID 40

BON ASAVOIR Les outils pour v?fier les r?ages de sa carte contr?r 41

Indispensable batterie 42

EN PRATIQUE Dur?de vie de la batterie d'une carte RAID 42

Le cache interne des disques: une arme ?ouble tranchant. ............. .. 42

JARGON innodb-flush_method = O_DIRECT 43

RE EGALEMENT ••••••••••••••••••••••••••••••••••••••••••••••••••• 44

Les SSD
: futur hit? 44

MyS~ et la m?ire 45

Comment MySQL utilise-t-illa m?ire? 46

CHAPITRE 3

Les moteurs de stockage 49

M?nismes d'un moteur de stockage 49

Installation et suppression d'un moteur 52

AmNTION Suppression d'un moteur de stockage utilis?r une table 54

Les forces en pr?nce: moteurs utilis?ar l'application 54

B.A.·BA
Cr? ses tables ?artir de l'existant 55

CONVENTIONS TERMINOLOGIQUES Base de donn?, serveur, instance, sch? 57

Les crit?s de choix d'un moteur 57

Moteurs disponibles: InnoDB, MyISAM, Merge, Memory, Archive 58

Le moteur InnoDB 58

B.A-BA
Les propri?s ACID _ 60

BON ASAVOIR Le MVCC 60

ASTUCE Sortir une table d'un tablespace partag?3

POUR ALLER PLUSLOIN ••••••••••••••••••••••••••••••••••••••••••••••••• 63

MyISAM 63

M?nismes internes de MyISA.M etformats de stockage. .............. .. 67

BA-BA Chaud, froid ou ti?? . .... ... .... .... .... .... .... .... ... .... 67

RApPEL M?nisme d'une commande ALTER TABLE 68

Le moteur MERGE pour agr?r plusieurs tables MyISAM .. ' 69

Le moteur MEMORY (anciennement HEAP) 71

Le moteur ARCHIVE pour un archivage compress?2

Autres moteurs 72

XtraDB 72

Falcon 73

Federated. .................................................. .. 73

Example 73

Blackhole ................................................... .. 73

CSV 74

NDB (Network Database) ........................................ 74

Moteurs communautaires et autres 75

Maria 75

PBXT 75

BLOB Streaming Engine (MyBS) 76

Mdbtools 76

Kickfire. .................................................... .. 77

TokuDB 77

Spider 77

Rethinkdb. .................................................... 78

CHAPITRE 4

Surveiller son serveur MySQL 81

O?ouver les informations pertinentes? 82

Variables syst? et variables de statut 82

D?INITION Variables syst? ou de statut 82

ALTERNATIVE R?p?r les variables syst? ou de statut 82

Qyels outils choisir? 83

ASAVOIR Variables syst? et my.enl 83

ATTENTION Une valeur peut en cacher une autre 84

ASAVOIR DijJ?nce entre un client et un outil MySQL 84

Int?t des outils de surveillance 84

B.A.·BA
key_buffer_size 85

Outils et commandes fournis par MyS~ 85

ATTENTION Variables globales vs variables de session 86

Cat?rie General. ............................................ .. 87

B.A.·BA
MySQL vs mysqld 88

ASTUCE Lesjokers dans les commandes MySQL :% et _ 89

LE SAVIEZ-VOUS Deux descripteurs defichiers pour une table MylSAM 89

Cat?rie Performance 91

ASAVOIR Le cache de requ? (Quel] Cache) 91

RAPPEL Le cache MyISAM 93

RApPEL ••••••••••••••••••••••••••••••••••••••••••••••••••••••• 94

AVOIR Ajuster la taille du cache d'index 94

ATTENTION Sortir un serveur client de la liste noire d'un serveur MySQL 99

AVOIR Droits n?ssaires aux commandes SHOWSTATUS et SHOW VARiABLES . . 99

ASTUCE Optimiser et analyser une requ? avec USE INDEX/IGNORE INDEX 104

La commande SHOW ENGINE INNODB STATUS 104

AVOIR DijJ?nce entre mutex et s?phores 106

AsTUCE Cr? un deadlock d?b? ; 108

ALIRE ?ALEMENT Le MVCC 109

INFORMATION_SCHEMA 113

Conna?e et savoir exploiter les outils de surveillance 114

Qy'est-ce que la performance? 115

RE Technologie du disque 116

LVM
: la gestion des volumes logiques 117

JARGON Transactionnel et coh?nce, quelles diff?nces? 118

B.A.·BA
Les diff?nts types de sauvegardes (backups) 119

?ude de cas: analyse d'un serveur MySQL 120

RAPPEL write-throughlwrite-back 123

Mesurer l'activit?u serveur 124

Les outils syst? 124

La commande iostat .......................................... .. 125

La commande vmstat. ........................................ .. 126

Les commandes netstat et mpstat 127

AlTERNATIVE oprofile, dtrace,jincore etjilefrag 127

Outils d'audit: MySQ!..Tuner et mysqlreport 127

ASTUCE Surveiller son serveur ?istance 130

Outils d'analyse temsp r?: mytop, mtop, innotop et maatk.it 131

?aluerles performances d'un syst? 131

JARGON Le smoke test, un test aux limites 132

M?HOOE Dimensionnement: les bons tests 135

Bien dimensionner un syst? (capacity planning) 136

AVOIR La notion de seuil 138

RE La mont?en charge mat?elle 138

IRE Pour aller plus loin dans le domaine 139

CHAPITRE 5

Exploiter les journaux de MySQL. 141

Le journal des erreurs 142

ASTUCE Rotation des journaux avec logrotate 143

Identifier et r?udre les probl?s 143

Modifier le tablespace ou les journaux d'InnoDB 143

Param?e incorrect dans le fichier de configuration 144

Erreurs li? ?a r?ication 146

Erreurs diverses 147

ATTENTION Eff? de bord de l'option myisamJecover 148

Le journal des requ?s lentes 148

Principe de fonctionnement 148

ATTENTION Eff? de bord de l'option log_queries_not_using_indexes 149

ALTERNATIVE Autres outils d'analyse 151

Journaliser dans une table 152

Exemples d'utilisations de la journalisation g?rale? 154

La journalisation binaire 155

REMARQUE L'option syncbinlog peut avoir un impact sur les peif?nces 155

TRANCHE DE VIE La technique de Point In Time Recovery en pratique 157

REMARQUE Taille du journalbinaireenjOnctiondu mode dejournalisation 158

Bonnes pratiques 160

CHAPITRE 6

Optimiser sa base de donn?
: du sch? aux requ?s 163

Conception de la base de donn? 163

Normalisationld?rmalisation 164

BON ASAVOIR La normalisation 164

OllTilS Logiciels de mod?sation 165

Ajouter des colonnes dans une table 165

Cr?ion de tables d'agr?tion 166

Cr?ion de sch?s orient?167

Des types de donn? ajust?167

M?HODE Un type optimal ?n moment donn?68

Les jointures 169

Les index 172

Index B-tree 173

B,A.·BA
•••••••••••••••••••••••••••••••••••••••••••••••••••••• 173

BON ASAVOIR Index Fulltext (Plaintext) 174

Index B+tree 175

BON ASAVOIR La table de hachage 177

Index hash 177

ALTERNATIVE •••••••••••••••••••••••••••••••••••••••••••••••••••• 180

Optimisation des requ?s 181

Conna?e l'optimiseur pour mieux le comprendre 181

B.A.·BA
La s?ctivit?81

BONAsAVOIR Optimiseurs ?ase de r?es ou de co?cast based ou rules based) 182

La commande EXPLAIN pour analyser l'ex?tion des requ?s 182

PRATIQUE Visualiser le plan d'ex?tion d'un DELETE ou d'un UPDATE 183

REMARQUE Attention ?afonction RAND() 185

Ol1T1lS Repr?ntation graphique du plan d'ex?tion avec Maatkit 186

BON ASAVOIR Optimisation des index et r?ganisation des tables

avecANALYSE TABLE et OPTIMIZE TABLE 187

Exemple d'optimisation d'un plan d'execution 187

Indexer les premiers caract?s d'une colonne 189

Index couvrant (covering index) 190

Pr?xe d'index (leftmost prefix indexes). .......................... .. 190

BON ASAVOIR Pas de prifixes d'index pour les index hash 190

Taille des index ............................................. .. 190

R?pitulatif des bonnes pratiques d'optimisation des requ?s 190

D?uper les requ?s complexes en plusieurs plus simples 191

CHAPITRE 7

Optimiser son serveur mySQL 193

Tuning serveur: variables de session, variables globales, handlers 193

VOCABULAIRE Cache et bu.ffer (tampon) 194

Les variables de session 195

read_bu.ffer_size. ............................................ .. 195

read_rnd_bu1T~_size 195

sort_bzif.f~_size ............................................. .. 195

join_bu1T~_size ............................................. .. 196

tmpjable_size et max_heapjable_size. .......................... .. 196

Les variables globales au serveur 196

Le cache de table. ............................................ .. 196

Le cache de thread. ........................................... .. 197

Tablejocks_immediate et Table_locks_waited. ..................... .. 198

Aborted_clients. ............................................. .. 198

Aborted_connects ............................................ .. 198

Les handlers ................................................ .. 199

Exemple d'optimisation d'une requ? 199

Les droits des utilisateurs 203

Optimisations pour InnoDB, MyISAM et MEMORY 204

Optimisation InnoDB 204

Optimisation MyISAM 205

Cache d'index multiples ....................................... .. 206

Optimisation Memory 207

AmNTION Limiter la taille des tables 207

RAPPEL Limitations du moteur Memory 207

Le cache de requ?s (query cache) 207

Gestion du cache de requ?s 210

AmNTION Taille du cache de requ?s 210

Le partitionnement 211

Le partitionnement par RANGE 213

Le partitionnement par LIST 214

Le partitionnement par HASH 214

Le partitionnement par KEY 214

Partitionner sur diff?nts disques 215

BON ASAVOIR ?olution du partitionnement 215

CHAPITRE 8

La r?ication MySQL 217

Introduction ?a r?ication 217

Int?t de la r?ication 219

Le dimensionnement horizontal (scale out) 219

La sauvegarde ?haud (hot backup) 219

Le basculement automatique (Failover) 220

Redondance g?raphique 220

Le cas du d?sionnel 220

Tester une nouvelle version de MySQL 220

l'int?eur de la r?ication 221

Mise en place de la r?ication 221

Configuration du ma?e 222

AmNTION Mot de passe en clair 222

BON ASAVOIR Filtrage des donn? r?iqu? 224

Configuration de l'esclave 225

REMARQUE Mettre ?ur l'esclave avant le ma?e 226

AmNTION Ancienne m?ode de configuration 226

Configuration avanc?de l'esclave 226

Commandes de la r?ication 227

Sur l'esclave 228

ATTENTION Cons?ences d'un RESET SLAVE 228

ASTUCE Comment savoir si le serveur esclave a du retard? 230

La commande SHOW SLAVE STATUS 231

Sur le ma?e 232

ASTUCE D?nnexion d'un serveur esclave 232

AmNTION RESETMASTER peut casser la r?ication 233

Probl?s li??a r?ication 233

IO_THREAD stopp?34

SQL_THREAD stopp?34

DANGER Ignorer les erreurs peut provoquer des incoh?nces 235

BON ASAVOIR Tables temporaires et r?ication 236

Architectures de r?ication avanc? 237

REMARQUE MySQL Cluster 237

Dual master en actif/passif 237

DANGER Une r?ique n'est pas une sauvegarde 238

Configuration 239

OUTILS Supervision des serveurs 239

Exemple: switchoverpour une mise ?ur online des serveurs MySQL. .... 239

R?pitulatif. .............................................. .. 241

AlTERNATIVE Commencer par le ma?e 241

Dual master en actif/actif 241

R?pitulatif ......
:................................... .. 244

R?ication circulaire (nombre de r?ications> 2) 244

Esclave relais 244

Configuration. .............................................. .. 246

R?pitulatif. .............................................. .. 247

Partitionnement adapt?u d?sionnel 248

Configuration " 249

R?pitulatif 250

Bonnes pratiques 250

AVOIR Le sharding 252

CHAPITRE 9

O?ouver de l'aide '1 253

Trouver de l'aide en urgence 254
Outils et sources de MySQ!-259
La conf?nce MySQ!-259

Les ressources internes 254

Les ressources externes 254

Les moteurs de recherche 254

Le support officiel MySQL 254

Les organismes externes 255

Trouver de l'aide hors contexte d'urgence 256

Formations 256

O?ser votre question? 256

L'association LeMug 256

Les blogs 257

Les forums et mailing-lists MySQ!-officiels 257

Aller plus loin et enrichir ses connaissances 257

La blogosph? de la communaut?58

Les s?naires web 259

Les certifications 260

Index 261