Démarche de diagnostic et d'optimisation pour améliorer les performances d'un site Web.

Les symptômes : votre application se comporte bien lorsque la base de données est vide. Mais lorsque la base de données se remplit avec des volumétries de production, l'application ralentit. La base de données consomme la plupart des ressources du système : la consommation CPU ou les accès disques atteignent 100 %. Les logs MySQL « slow queries » montrent que certaines requêtes SELECT sont lentes à exécuter.

La solution :en termes de base de données, la première solution à examiner est de vérifier que les index sont bien définis.

Qu'est-ce qu'un index ?

Imaginons un bibliothécaire devant rechercher un livre parmi une collection. Si ces livres sont empilés dans le désordre, il va devoir parcourir sa collection livre par livre pour trouver le livre qui l'intéresse. De la même manière, dans une base de données, si une table « livre » contient un million d'enregistrements et que l'on cherche un livre par son « nom », le moteur de la base de données devoir parcourir un à un tous les enregistrements. Le bibliothécaire va bien sûr classer les ouvrages. Une fois classés, il sera capable de retrouver un livre très rapidement, par simple dichotomie. En termes de bases de données, ce classement s'appelle un « index ». Si la colonne « nom » est indexée, la base de données pourra retrouver un ouvrage par son nom sans parcourir l'intégralité de la table.

De la même manière que le bibliothécaire va passer d'une journée de recherche à quelques minutes, la base de données passera de quelques secondes de recherches à quelques millisecondes. Et l'efficacité des index augmente avec la taille de la base !

L'avantage des bases de données par rapport aux classements physiques est de permettre plusieurs tris à la fois. On peut créer un index sur le nom, sur l'auteur, et sur le numéro ISBN à la fois.

Coût d'un index : bien sûr, lorsqu'un nouveau livre arrive, il faut l'indexer. Ainsi, si les index permettent de gagner en temps de recherche, ils ralentissent légèrement l'insertion des données. Un index occupe aussi de la place sur le disque dur. Cependant, dans une application web typique, les données sont lues bien plus souvent qu'elles ne sont écrites. De manière quasi systématique, le gain de performance apporté par un index dépasse largement son coût.

Analyser les requêtes : lorsque l'on crée un index sur une base de données, il est important de tracer les améliorations que l'index apporte sur le temps d'exécution de la requête. Le plus souvent, l'amélioration est flagrante (on passe de plusieurs secondes à quelques millisecondes).

La lecture des analyses n'est pas aisée, mais c'est un des meilleurs moyens de comprendre le fonctionnement de la base de données. Dans ce rapport, vous devez vous assurer qu'il n'y a plus de « full-scan » sur les tables. Les full-scan correspondent à un parcours de tous les enregistrements sans utiliser d'index.

Indexer les colonnes des requêtes les plus fréquentes/les plus complexes : les logs de « slow queries » MySQL devraient mettre en évidence les requêtes les plus lentes. Analysez ces requêtes et vérifiez notamment la partie « WHERE » et « ORDER BY » de la requête. Les colonnes utilisées devraient être indexées. Vérifiez également si votre requête effectue une jointure entre plusieurs tables, car les clefs étrangères devraient être indexées.

L'option log_queries_not_using_indexes permet d'ajouter aux logs de « slow queries » toutes les requêtes qui effectuent un « full-scan ». Elles pourront ainsi être rapidement repérées.

Si votre requête SQL à optimiser contient plusieurs colonnes dans la clause WHERE, créer un index sur chacune des colonnes ne sera pas optimal. La base de données utilisera en effet un des index, mais ne peut pas utiliser plusieurs index sur la même table simultanément. La base de données va alors chercher à utiliser l'index le plus efficace (le plus discriminant). Pour ces cas, il est possible d'utiliser des index multicolonnes.

En reprenant l'exemple de la bibliothèque, on pourrait créer un index sur les auteurs, puis les noms d'ouvrage. Les livres seraient d'abord classés par auteurs, puis par nom d'ouvrage. Ainsi, il serait rapide de retrouver un livre dont on connaît l'auteur et le nom.

Il peut se produire des cas où la base de données dispose de la possibilité d'utiliser plusieurs index pour une même requête et doit faire le choix entre deux index à utiliser. Elle prendra alors généralement l'index le plus discriminant, c'est-à-dire l'index qui dispose du plus grand nombre de valeurs différentes pour une même colonne. On parle de « cardinalité » de l'index.

Par exemple, si notre table d'ouvrage possède une colonne « auteur » qui contient le nom de l'auteur, et une colonne « type » qui contient soit « livre », soit « magazine », la cardinalité de l'index « type » sera de 2, alors que la cardinalité de l'index « auteur » sera égale au nombre d'auteurs.

Si une base de données a le choix entre les deux index, elle prendra l'index « auteur » à chaque fois.

La cardinalité est estimée par la base de données au fur et à mesure des opérations. Pour les tables MyISAM, si on effectue un très grand nombre d'opérations de suppression/insertion, la cardinalité d'un index peut être faussée. La base MySQL prendra alors le mauvais index ce qui peut nuire aux performances. La commande « ANALYZE TABLE » réeffectue le calcul de cardinalité pour corriger ce problème. Attention cependant, cette commande bloque la table le temps de son exécution. Il ne faut donc surtout pas l'exécuter pendant un pic de charge !

Dans le reste de ce paragraphe dédié aux index, nous allons cependant aborder un type particulier d'index : les index FULL-TEXT.

Reprenons l'exemple de notre bibliothécaire. Il vient de classer tous les ouvrages par ordre alphabétique. Quelqu'un arrive, et lui demande de retrouver un ouvrage dont il ne se souvient pas complètement du titre. Il se rappelle cependant que le titre finit par « de la méthode ».

Notre bibliothécaire est bien embêté. Son classement alphabétique fonctionne, à condition que l'on connaisse le début de nom du livre, pas la fin. Il ne va pas avoir d'autre choix que de parcourir sa collection de livres entière. De la même manière, un index « classique » sur la colonne nom ne pourra pas être utilisé sur une requête du type :

SELECT * FROM books WHERE name LIKE '? la méthode'

Si ce type de requête vous pose des problèmes de performances, il faudra créer un index FULL-TEXT. Les index FULL-TEXT permettent de rechercher rapidement du texte placé au milieu ou à la fin d'une colonne.

Vous avez appliqué toutes les optimisations ci-dessus, vous avez créé des index de manière optimale, et malgré cela, vos requêtes sont toujours lentes.

Dans le cas de notre table « livres », il s'agirait d'un client souhaitant retrouver un livre dont le contenu contienne le mot « collier », mais aussi le mot « girafe ». Le mécanisme devrait être suffisamment souple pour permettre les fautes d'orthographe (par exemple « giraffe » au lieu de « girafe »), et ne retourner que les livres publiés après 2009.

Dans ce type d'exemple, il faut envisager de revoir l'architecture de l'application. De toute évidence, MySQL n'est pas bien adapté aux recherches FULL-TEXT complexes, et est incapable de réaliser des requêtes approximatives (fuzzy-searches).

Si vous souhaitez réaliser des recherches de ce type, et si vous avez des problèmes de performance à cause de ces requêtes, il existe des bases de données spécialisées pour la recherche dans une grande quantité de documents « full-text ».

Lucene : Lucene est un moteur de recherche full-text. À l'origine implémenté en Java, il en existe une implémentation PHP dans le Zend Framework : http://framework.zend.com/manual/fr/zend.search.lucene.html

Lucene stocke des « documents » et permet de rechercher en full-text dans le document ou ses métadonnées. Il permet d'effectuer des « fuzzy-searches » et des requêtes complexes.

L'implémentation PHP de Lucene est relativement lente par rapport à son homologue Java. Aussi, Lucene ne devrait pas être utilisé si la volumétrie est trop grande. On se tournera alors vers Solr.

Apache Solr :serveur basé sur la version Java de Lucene. Il s'interroge via web-services (sur un modèle REST), et permet facilement de stocker les documents puis de les chercher. Si vos problèmes de performances sont dus à une fonctionnalité de recherche full-text et que l'implémentation de Lucene dans le Zend Framework ne suffit plus, Apache Solr est la solution dont vous avez besoin :http://lucene.apache.org/solr/

L'une des techniques parmi les plus simples à mettre en œuvre pour agréger des données consiste à créer un script (PHP ou autre) qui s'exécute régulièrement pour effectuer des précalculs (batch).

Ce script devra être lancé via une tache automatique. Le temps entre deux exécutions est critique. En effet, il y a plusieurs réponses pour un même problème :

• exécuter très souvent le script : ceci permet d'avoir des données proches du temps réel, et peut être exécuté rapidement, à condition que la base de données s'y prête. Il est possible que la requête la plus simple mette plusieurs minutes, auquel cas il sera impossible de lancer souvent le script sous peine de saturer le serveur ;
• exécuter une fois par jour : même si le script est long, il ne bloquera pas le serveur. Il est conseillé de l'exécuter au moment où la charge serveur est la plus faible (souvent en pleine nuit).

Avantage : la solution est souvent facile à développer et à déployer, grande liberté dans le code.

Inconvénient : les données présentées ne sont pas le reflet exact de la production. Selon les volumes, les tests sont parfois longs et complexes à réaliser.

Le trigger permet de créer l'agrégation directement en base de données. À chaque nouvel enregistrement dans une table, il est possible de créer un trigger qui effectuera le précalcul et l'enregistrera directement dans la table agrégée. Il faut cependant faire attention à la limite des bases de données.

Avantage : temps réel.

Inconvénient : maintenance, migration de base de données et limites en écriture.

Exemple : à chaque insert dans la table principale, vous ajoutez trois lignes dans la table d'agrégation. Ainsi au lieu de faire un insert, il y en aura quatre. La base saturera donc beaucoup plus rapidement s'il y a de nombreuses écritures.

Si votre application effectue un trop grand nombre de requêtes, la base de données peut être la raison des mauvaises performances. En effet, en dehors du temps d'exécution de la requête, il faut compter le temps de latence à la base de données. Or, ce temps de latence n'est pas négligeable, car il est parfois plus important que le temps d'exécution de la requête elle-même ! De manière générale, il faut donc préférer une requête importante plutôt que plusieurs petites requêtes afin de factoriser le temps de latence.

Par exemple :

$result = mysql_query("SELECT * FROM users");
while ($user = db_fetch_object($result)){
    $countResult = mysql_query("SELECT count(1) 
    FROM cars 
    WHERE user_id = $user->id");
    // ...
}

Ce script exécutera une première requête pour récupérer tous les utilisateurs, puis, pour chaque utilisateur retourné, une nouvelle requête sera effectuée pour connaître le nombre de voitures qu'il possède. Une meilleure pratique consiste à lancer une seule requête pour tout récupérer en une seule fois :

$result = mysql_query("
SELECT u.*, count(c.id) 
FROM users u JOIN cars c ON u.id=c.user_id 
GROUP BY u.id");
while ($user = db_fetch_object($result)){
    // ...
}

Pour optimiser les appels à la base de données, on est souvent tenté de traiter en priorité les requêtes les plus longues à être exécutées.

En réalité, si une requête met 10 secondes à s'exécuter, mais qu'elle est peu appelée, l'optimisation n'améliorera pas les performances de l'application. Il faut plutôt s'intéresser aux requêtes qui consomment le plus de temps sur un intervalle de temps représentatif d'une utilisation normale de l'application.

Une solution pour savoir quelles sont les requêtes qui doivent être optimisées est d'identifier l'ensemble des requêtes exécutées sur une période représentative (la mise en place du log des slow queries avec un seuil à 0 permet de les voir toutes), puis d'analyser le résultat en comptant le nombre d'occurrences des différentes requêtes (au paramètre près). En pondérant ensuite le temps d'exécution par le nombre d'appels de chaque requête, vous obtiendrez un classement des requêtes les plus coûteuses.

L'exécution de requêtes imbriquées implique la création d'une table temporaire. Si elle peut être évitée (ce n'est pas toujours le cas), il vaut mieux effectuer des jointures.

Exemple :

SELECT *
FROM users
WHERE user.role_id IN (
SELECT uid FROM users_roles
WHERE rid IN (    SELECT rid
FROM role
WHERE name IN ('administrateur', 'client')
)
)

Cette requête utilise deux requêtes imbriquées, alors que l'usage de jointures serait plus performant :

SELECT *
FROM users u 
JOIN users_roles ur on ur.user_id = u.id
JOIN role r on r.id=ur.role_id
WHERE r.name IN ('administrateur', 'client')

Le choix entre MyISAM et InnoDB n'est pas anodin. La plupart du temps, le choix est dicté par les fonctionnalités de la base. Par exemple, seul InnoDB permet de déclarer des contraintes d'intégrité sur la base de données. InnoDB dispose aussi d'un journal qui garantit que les données seront toujours dans un état stable, même si le serveur de base de données s'arrête en plein milieu d'une opération d'écriture.

À contrario, MyISAM est le seul à proposer un index full-text. Si vous avez besoin de cette fonctionnalité, le choix sera donc imposé directement.

Vous avez bien sûr la possibilité de mélanger les moteurs, chaque table pouvant utiliser un moteur qui lui est propre. Cette solution est à utiliser avec précaution. En effet, chaque moteur dispose de ses propres restrictions, notamment en termes de backup. En utilisant les deux moteurs, vous cumulez les deux limitations.

En termes de performance, chaque moteur possède ses caractéristiques propres. Si vous avez choisi MyISAM ou InnoDB pour l'une des fonctionnalités qui lui est propre, mieux vaut rester à votre choix initial que décider de changer pour un problème de performances. Si, par contre, votre choix a été effectué « parce que c'est le moteur par défaut », vous pouvez alors légitimement vous poser la question d'un changement de moteur.

InnoDB semble être le moteur préféré de la plupart des développeurs. D'un point de vue des performances uniquement, il se différencie sur deux grands points : il dispose d'un journal, et il supporte le « row-levellocking ».

Pour une base de données, un journal est un espace dans lequel la base va écrire toutes les actions d'écriture effectuées. Dans la pratique, cela signifie que les actions sont écrites deux fois. Bien sûr, d'un point de vue des performances, cela a un coût puisque les données sont écrites en double. Cependant, c'est la seule manière de permettre de gérer des transactions (d'effectuer des « rollbacks »), et surtout, cela garantit de toujours pouvoir récupérer la base de données dans un état stable, quelles que soient les coupures du serveur.

Le fait que MyISAM ne dispose pas de journal ne le rend cependant pas systématiquement plus performant. En effet, lors d'une opération d'écriture en base avec MyISAM, ce dernier bloque toute la table. À contrario, InnoDB ne bloque que les lignes (le fameux row-level locking).

Ainsi, InnoDB est plus efficace que MyISAM pour les applications à haute concurrence d'accès (typiquement une application web).

InnoDB possède cependant des limitations qu'il est bon de connaître. La plus connue étant la récupération du compte de lignes dans une table qui effectue un parcours de table complet (très lent). Il faut donc éviter les requêtes du type :

SELECT COUNT(*) FROM books

Enfin, InnoDB est activement développé alors que MyISAM est plus stable. On peut donc s'attendre à voir de nombreuses améliorations de performance arriver sur InnoDB.

InnoDB étant un système plus complet que MyISAM, il dispose également de plus de paramètres. Alors que MyISAM est assez facile à configurer, InnoDB demandera plus de temps pour obtenir un système optimal.

La liste de tous les paramètres InnoDB est présente sur cette page :

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

Même si cette liste est longue, si vous souhaitez faire du fine-tuning MySQL, nous vous conseillons de la parcourir en entier, certains paramètres ayant des impacts sur d'autres.

Nous ne parcourons pas l'intégralité des paramètres dans ce livre blanc (ce n'est pas le sujet), mais allons présenter les principaux.

Le paramètre principal influençant les performances MySQL sera bien sûr la taille du cache. Plus le cache est grand, moins MySQL devra aller chercher de données sur le disque lors de requêtes, et donc plus la base sera rapide.

Sous InnoDB, la taille du cache est gérée par le paramètre innodb_buffer_pool_size. Par défaut, il est initialisé à 128 Mo. Sur un serveur de base de données dédié, on peut monter jusqu'à 80 % de la RAM du serveur. Attention cependant, MySQL aura besoin de RAM également pour stocker le programme, la structure de la base, etc. De plus, si vous utilisez MyISAM en parallèle d'InnoDB, il faudra également laisser de la place en RAM pour le cache MyISAM !

Le nombre de connexions simultanées à la base est limité par le paramètre max_connections.

Si le nombre de connexions maximal est atteint, la base de données retournera une erreur (Toomany connections).

Il est donc très important que ce paramètre ne soit pas trop bas. Chaque thread PHP pouvant ouvrir une connexion à la base de données, le nombre de connexions maximum demandées à la base de données devrait être environ égal au nombre de threads PHP autorisés dans Apache (voir à ce sujet la section Autres paramétrages Apache-PHP).

Note : la commande « SHOW STATUS » MySQL permet de voir le nombre maximal de connexions simultanées ouvertes sur le serveur, ce qui permet de voir en production si on se rapproche ou non du maximum autorisé.

Attention : ne modifiez pas le paramètre max_connections sans modifier le paramètre table_cache qui lui est associé. table_cache représente le nombre de fichiers ouverts simultanément par MySQL. En MyISAM, pour chaque connexion, lorsque MySQL accède à une table, MySQL ouvre un fichier. Si MySQL fait une requête avec un join sur 2 tables, il ouvre 2 fichiers, etc.

Dans l'idéal, on devrait donc avoir table_cache = max_connections * nombre de join maximum.

Dans le cas d'InnoDB, ce nombre peut être différent puisque selon la configuration d'InnoDB, celui-ci écrit toutes les tables dans le même fichier, ou écrit une table par fichier (suivant la valeur du paramètre innodb_file_per_table).

Par défaut, MySQL n'ouvre pas plus de 8 threads pour InnoDB.

Le paramètre régissant ce comportement est innodb_thread_concurrency : http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_thread_concurrency

Si vous disposez d'un serveur dédié puissant (par exemple avec 24 processeurs), vous pourriez vouloir adapter ce paramètre.

La recommandation MySQL est de configurer ce paramètre à « deux fois le nombre de processeurs + le nombre de disques ».

Nous vous recommandons cependant d'effectuer des tests avec des valeurs très différentes pour voir le comportement de votre application. En effet, en augmentant le nombre de threads, vous augmentez la concurrence et donc le risque de locks.

Lorsqu'on utilise InnoDB, MySQL écrit un journal. Comme expliqué précédemment, l'idée du journal est d'écrire l'ancien état de la base avant de l'écraser avec le nouvel état. En cas de crash, on peut ainsi toujours revenir en arrière. L'écriture sur le disque est effectuée en double. Ceci est inhérent à toutes les bases de données respectant les normes ACID (Atomicité, Cohérence, Isolement, Durabilité), c'est-à-dire quasiment toutes les bases SQL sauf MySQL en mode MyISAM. Il existe dans MySQL des paramètres permettant d'assouplir l'écriture dans le journal (par exemple de faire l'écriture une fois par seconde plutôt qu'à chaque UPDATE ou INSERT). Le paramètre innodb_flush_log_at_trx_commit régit ce fonctionnement :

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/