Tuning Et Optimisation Des Performances De Mysql

Tuning et Optimisation des performances de MySQL Mezgani Ali August 31, 2009

Cet article va projeter quelques astuces afin de mieux comprendre l’importance de l’optimisation de la base de donn´ee MySQL, avant de s’attaquer au codage (ou autre chose). D´efinir les objectifs est la premi`ere ´etape de la conception d’un projet, Cette ´etape cruciale est trop souvent ignor´ee ou bˆacl´ee se qui m`ene `a des projets incomplets ou bien une fois le projet compl´et´e `a des applications inutiles des vraies usines ` a gaz. Objectifs : * Tirer les performances maximales du mat´eriel disponible. * Eviter les temps d’arrˆet des applications critiques. * Eviter le ralentissement des applications en p´eriode de pointe. Je devrais installer une machine pour une application faisant un usage intensif du SGBD MySQL avec n concurrences, sur Linux. Cependant l’optimisation de MySQL passera par trois composants : * Optimisation du serveur MySQL * Optimisation de la base de donn´ees * Optimisation des requˆetes

1

Optimisation du serveur MySQL :

La machine en question est une HP BL680c avec Six noyaux (core) Xeon, dont une cadence de 2.40GHz chacun, un cache memory de 12288 KB et 16GB de RAM, attach´e via fibre channel (FCP-2) `a un SAN storage qui n’est que le MSA 2010fc. C’est une machine puissante vu sa configuration, donc pour profiter de ces performances au maximum, et pour avoir une installation de MySQL optimale, il ne faut un peu de tuning de cette derni`ere, tout en choisissant le RAID correspondant, le meilleur FS, passant par l’OS et ainsi jusqu’`a la couche applicative, chose qui va impliquer largement le rendement et le temps de r´eponse de la machine en question. Pour question esth´etique nommons notre machine nibiru.

1

RAID (Redundant Array of Inexpensive Disks) : Une des premi`eres questions ` a se poser, quel niveau de RAID nous allons utilis´e pour notre serveur de base de donn´ees. Probablement vers le RAID 10 si nous avons plusieurs ´ecritures sur la base, le RAID 10 est une meilleurs solution aussi la plus ch`ere pour les base de donn´ees n´ecessitantes une grande performance I/O vu sa grande vitesse de lecture/´ecriture, par rapport aux autres RAID. Donc pour impl´ementer le RAID 10 sur un spare de disque de 7TB, nous ne profitons que du 3.5TB d’espace, chose que nous pouvons pas impl´ementer pour le moment. Le cas ou nous avons plusieurs lectures par rapport aux nombre ´ecritures, le RAID 5 reste un bon choix, malgr´e qu’il est rapide en lecture et lent en ´ecriture, mais comme mˆeme rapide en ´ecriture par rapport `a un seul disque.

HDD (Hard disk drive) et espace de stockage : Notre espace de stockage est un MSA 2012fc qui contient 10 Seagate disque SAS (Serial Attached SCSI system) de 750GB de 10k rpm, avec un transfert de data de 3 jusqu’` a 6 Gbit/s. La valeur moyenne de lecture avec activation du cache disque depuis nibiru est de 5656 MB/s, cependant la valeur moyenne de lecture avec activation du tampon disque est de 199 MB/s. La vitesse moyenne d’´ecriture est de 500 MB/s qui est une vitesse optimale par rapport aux autres bus de transfert de donn´ee disque/ordinateur tel le SATA, PATA (IDE). Dans le cadre de ce projet, nous aurons une partition libre de 3TB et qui en sera compl`etement d´edi´ee.

IO (Input/Output) : Les I/O subsystem ou bien les ordonnanceurs des E/S, sont un ensemble de processus responsable du d´eplacement des blocs de donn´ee entre le disque et la m´emoire. Ils sont compl`etement param´etrables, g´en´eralement les I/O subsystem ne se comportent pas comme des simple FIFO (first in first out), mais ils reposent sur certains algorithmes afin de g´erer les blocs de donn´ees, on peut en citer : cfq, noop,deadline, anticipatory. Le choix d’un bon scheduler peut remarquablement influencer les performances de notre machine nibiru, depuis la version 2.6.18 du kernel l’algorithme d’ordonnancement des E/S CFQ est activ´e par d´efault, et qui permet des importantes performances, surtout pour les grosses applications qui n´ecessitent un grand nombre d’op´erations I/O. Le principe du cfq (Completely Fair Queuing) comme son nom l’indique est maintenir une queue afin de distribuer la bande passante E/S sur l’ensemble de requˆete E/S, selon certaines r`egles. Pour plus d’information sur le temps de r´eponse des ces algorithmes consulter le tableau d’apr`es : Timing MB/s — scheduler algorithms cached reads buffered disk reads disk writes

noop 5669 192 528

anticipatory 5637 192 516

deadline 5638 193 451

cfq 5683 222 507

Ces tests sont bas´es sur le kernel 2.6.18-92.1.22.el5PAE, pour des r´esultats

2

plus compl`etes nous devons developper un peu plus cet ´etude, en prenant consid´eration de plusieurs kernels et plusieurs syst`emes de fichiers. Ces tests peuvent ˆetre automatiser grˆ ace au superbe tool sysbench developp´e par Alexey Kopytov (ing´enieur logiciel @ MySQL AB).

OS (Operating system) : Le serveur MySQL est pr´econis´e pour un fonctionnement optimal sur SOLARIS, n´eanmoins, il est possible de l’optimiser sur notre OS pour se rapprocher de son rendement id´eal. le syst`eme d’exploitation existant actuellement sur nibiru est la CentOS release 5.2 (Final), sur une architecture i686 avec le kernel 2.6.18-92.1.22.el5PAE, donc si vous faites attention vous pouvez remarquer que la pr´esence du module PAE (Physical address extension), a priori c’est une CentOS pour un processeur 32bit, pourtant notre machine est une intel 64bit, pourquoi ne pas installer une CentOS x86-64 ? La r´eponse est simple le fait de passer de 32 bits `a 64 bits augmente la consommation de m´emoire. Donc si un programme consomme 100 MB en 32 bits il consommera automatiquement plus en 64 bits. Pour le moment l’OS correspond parfaitement `a notre besoin pour 16GB de RAM.

FS (File system) : En travaillant sur l’optimisation d’une base de donn´ee, penser au syst`eme de fichiers correpondant est une ´etape obligatoire, tout au long de se projet d’optimisation. A vrai dire et comme vous le savez un syst`eme de fichiers est une structure de donn´ee qui sert ` a stocker les data sous format lisible ordonn´ee, tel que les fichiers. chaque fichier est d´ecrit par des m´etadata tel que les droit d’acc`es, le propri´etaire, ... Cette proc´edure de stockage changent de file system `a un autre, puisque il existe plusieurs sur le terrain propri´etaire et free tel que GPFS, QFS, XFS, EXT2/3/4, JFS, REISERFS... J’ai travaill´e sur un benchmarking de trois types de syst`eme de fichiers (JFS, XFS, EXT3) sur une machine modeste et le r´esultat ´etait bien ´evidement pour le JFS, dˆ u a son faible coup de consomation de CPU. JFS rep´eresente le syst`eme de fichiers journalis´e mis au point par IBM et qui est disponible sous la licence GPL. Pour plus de d´etails sur cette ´etude vous pouvez consultez mon blog Notez que ce benchmarking ne projete pas r´eellement le comportement des syst`eme fichiers (JFS, XFS, EXT3) face au MySQL. L’outil utilis´e m`ene ses tests en cr´eant un grand nombre de fichiers (ce qui ne refl`ete pas vraiment ce que fait un SGBD).

MySQL : Il est pr´econis´e d’utiliser la version code source du serveur MySQL et de la compiler en prenant en consid´eration les diff´erents param`etres du syst`eme `a savoir le jeu de caract`ere ` a utiliser, le micro-processeur ... Pour question de rapidit´e et facilit´e durant le process d’installation et mise `a jour, nous pouvons biensure l’installer depuis les repository de la CentOS.

3

2

Optimisation de la base de donn´ ees :

Avant de passer au tuning de notre SGBDR, plusieurs questions viennent `a l’esprit : Quel moteur de stockage choisir InnoDB ou bien MyISAM ? Quel sont les Input/Ouput methodes (random, sequential) ? Quel est le nombre de connections par seconde ? Quel est le nombre de threads cr´ee par seconde ? Quel est le nombre maximal de connections ?

Dans le cas de plusieurs connections concurrentes, notre choix du MySQL engine sera Innodb qui est de plus en plus utilis´e grace `a sa m´ethode d’indexage, InnoDB peut cr´eer des index selon une table de hashages pour les requetes les plus fr´equentes et qui est plus rapide qu’aux index ordinaires bas´e sur les arbres binaires. InnoDB supporte des transactions conforme aux propri´et´es ACID, similaire ` a celui de PostgreSQL, ainsi que la gestion des cl´es ´etrang`eres. InnoDB scale tr`es bien ce qui refl`ete son utilisation dans la gestion des grands volumes. Parmis les mesures ` a prendre en consid´eration afin d’adapter MySQL `a notre besoin, la modification du fichier my.cnf. Ci-dessous quelques examples : * La sp´ecification de la taille du tampon m´emoire d’InnoDB (buffer memory), pour ses dictionnaires d’informations, et ses structures internes de donn´ees. * Innodb utilise un tampon de traitement (buffer pool) pour mettre en cache les donn´ees et les index de tables. Sa valeur devrait ˆetre adjuster de 60 `a 70 % de la m´emoire physique du serveur, cela semblerait suffisant car avec une valeur trop grande la machine utilsera le swap. * Nous allons limit´e la taille du fichier log du 25-100% du tampon m´emoire. Ainsi le temps de restauration ne sera pas long. * Le tampon de logs doit ˆetre assez grand `a peu pr`es de 4 x la taille du file de log. * Eviter de trop augmenter le nombre des threads concurrents, a priori cela d´epend de notre algorithme d’ordonnancement des E/S. Donc une grand valeur risque de crasher le service. Dans le cas ou vous n’avez pas besoin de transaction ni de cl´e ´etrang`ere, MyISAM est votre choix pourvue ses importantes performances, notez que MyISAM est fournit par d´efault par MySQL.

3

Optimisation des requˆ etes :

MySQL permet d’analyser les requˆetes et de connaˆıtre le temps et le plan d’ex´ecution. Ces informations permettent de comprendre ce qui rends les requˆetes lentes et d’en optimiser l’ex´ecution. Un des pi`eges de SQL est que c’est un langage de haut niveau. Le rapport entre la commande qu’on tape et le travail que doit faire la machine est beaucoup moins direct et beaucoup plus dur `a saisir 4

qu’avec l’assembleur. Il est donc fr´equent qu’une requˆete SQL qui n’aie pas l’air bien m´echante prenne des heures `a s’ex´ecuter. Heureusement, MySQL dispose d’une excellent commande EXPLAIN qui explique ce qu’elle va faire et ce qui va prendre du temps. Cependant prenez toujours votre temps afin de detecter les requˆetes lentes, et profiter de la commande EXPLAIN pour les analyser. Arrivons vers la fin de ce draft, je pense que le meilleurs outil mis `a dispositions de chaque ing´enieur syst`emes, est le livre ainsi, J’aimerais en profiter pour recommender l’excellent livre High Performance MySQL sur l’´editeur oreilly, d’ailleurs qui devrait ˆetre dans la biblioth`eque de la FONDEP. Et ceci est un autre projet :)

5