Suite et fin de la série sur l’optimisation de la base de données de WordPress. Après avoir vu les optimisations de base, puis comment supprimer les informations non utilisées, nous allons tenter quelque chose de plus expérimental, en étudiant l’indexation, et les statistiques de la base de données.
Je ne suis pas DBA (Database Administrator), cette étude est donc certainement perfectible. Si des experts ou des puristes ont des remarques à faire, je suis preneur.
Dans le fonctionnement intime d’une base de données, deux paramètres peuvent jouer un rôle dans les performances: les index, et les statistiques. Nous pourrions également citer l‘intégrité, mais la perte de performance n’est pas la conséquence la plus grave d’une perte d’intégrité: un défaut de ce type peut conduire à un crash sévère.
Les index
Les index réduisent considérablement les temps de recherche, en permettant une identification des enregistrements d’une table selon des critères autres que l’identifiant de l’enregistrement lui-même.
Prenons le cas d’une bibliothèque:
- En général les livres sont numérotés. Ce numéro correspond à l’identifiant du livre,
- Mais nous cherchons rarement un livre par cet identifiant (qui est un numéro interne, dédié à la gestion de la bibliothèque),
- Si nous cherchons le livre par son titre, il nous faudrait parcourir toute la bibliothèque, livre par livre pour trouver celui qui nous intéresse. Il en va de même si nous cherchons le livre en connaissant uniquement son auteur,
- Le bibliothécaire crée donc des séries fiches, qu’il organise en classeurs. Sur la première série, chaque fiche contiendra le titre du livre, et la série sera triée par ordre alphabétique. La recherche est donc très simple (grâce au tri alphabétique). Une fois trouvé la bonne fiche, nous avons immédiatement le numéro du livre, le numéro de l’étagère … Même raisonnement avec des fiches « Auteurs ».
Les index jouent exactement le même rôle que les séries de fiche.
Lors de son installation, WordPress crée les tables de la base de données, ainsi que les index associés. Nous pouvons obtenir la liste de ces index de deux façons:
- via PHPMyAdmin, dans le menu structure de chaque table,
- avec la commande SQL:
SHOW INDEX FROM <le nom de la table>(SHOW TABLESnous donne la liste des tables).
La collecte des index de la base de WordPress, nous donne le tableau suivant:
| Tables | Index | Colonnes | Unique? |
|---|---|---|---|
| wp_comments | PRIMARY | comment_ID | O |
| comment_approved | comment_approved | N | |
| comment_post_ID | comment_post_ID | N | |
| comment_approved_ date_gmt | comment_approved comment_date_gmt |
N | |
| comment_date_gmt | comment_date_gmt | N | |
| wp_posts | PRIMARY | ID | O |
| post_name | post_name | N | |
| type_status_date | post_type post_status post_date ID |
N | |
| post_parent | post_parent | N | |
| post_similar | post_title post_content |
N | |
| wp_postmeta | PRIMARY | meta_id | O |
| post_id | post_id | N | |
| meta_key | meta_key | N | |
| wp_term_ relationships | PRIMARY | object_id term_taxonomy_id |
O |
| term_taxonomy_id | term_taxonomy_id | N | |
| wp_terms | PRIMARY | term_id | O |
| slug | slug | N | |
| name | name | N | |
| wp_term_ taxonomy | PRIMARY | term_taxonomy_id | O |
| term_id_taxonomy | term_id taxonomy |
O | |
| wp_options | PRIMARY | option_id blog_id option_name |
O |
| option_name | option_name | N | |
| wp_users | PRIMARY | ID | O |
| user_login_key | user_login | N | |
| user_nicename | user_nicename | N | |
| wp_usermeta | PRIMARY | umeta_id | O |
| user_id | user_id | N | |
| meta_key | meta_key | N | |
| wp_links | PRIMARY | link_id | O |
| link_category | link_category | N | |
| link_visible | link_visible | N |
Deux questions:
- Peut-on encore ajouter des index pour améliorer les performances?
- Peut-on les optimiser?
Il est toujours possible d’ajouter des index, mais il ne faut pas oublier qu’ils sont mis à jour à chaque modification de la base, et ces mises à jour peuvent prendre du temps.
Dans notre cas, les index sont déjà relativement nombreux, et appliqués sur les principales clés de recherche. Si l’on prend la table wp_posts qui contient tous les articles et leurs révisions, les champs ID, name, status, date sont indexés, de quoi retrouver rapidement des articles en fonction de l’un de ces 4 critères, ou une combinaison des 4.
Concernant l’optimisation: les index sont mis à jour au fur et à mesure des modifications de la base. Mais ils sont, comme les enregistrements standards des tables, soumis à une fragmentation. Lorsque l’on efface un enregistrement, sont index est également effacés, créant un trou.
Sur certaines bases de données, lorsque l’on constate un écroulement des performances, l’optimisation consiste à effacer, puis recréer les index. Avec MYSQL, l’opération est plus simple: en relisant la documentation de la commande OPTIMIZE, j’ai découvert qu’elle défragmente les tables, mais agit également sur les index, en les défragmentant, et en les triant.
Les statistiques
Les statistiques sont avant tout des informations internes à la base de données.
Dans le cas de requêtes « complexes », ces informations permettent à la base de savoir dans quel ordre elle doit exécuter les différentes parties de ces requêtes. MySQL utilise notamment les statistiques pour décider dans quel ordre les tables doivent être rassemblées lors des jointures qui ne s’effectuent pas sur une constante.
Calculer les statistiques régulièrement permet donc de maintenir un bon niveau de performance. La commande MySQL pour lancer le calcul des statistiques est ANALYZE TABLE .... Mais ce calcul est également exécuté par la commande OPTIMIZE TABLE (encore elle).
L’intégrité
L’intégrité ne concerne pas vraiment les performances. J’en parle ici, parce que vérifier l’intégrité de sa base, doit faire partie des opérations de maintenance à effectuer régulièrement.
Un défaut d’intégrité est plus grave qu’un simple problème de performance, puisqu’il correspond souvent à une perte d’information, ou un manque de cohérence entre des informations stockées dans la base.
La commande CHECK TABLE tbl_name[,tbl_name...] [option [option...]] vérifie l’intégrité des tables spécifiées.
Il existe deux solutions pour résoudre un problème d’intégrité:
- Exécuter la commande
REPAIR TABLE, - Effectuer une restoration complète de la base.
La commande REPAIR ne sera efficace que si les dégâts sont limités. Au-delà la restoration devient obligatoire. Il subsiste cependant un problème: la base était peut-être déjà corrompue au moment de la sauvegarde. Il faut donc
- Toujours garder plusieurs exemplaires de vos sauvegardes (les trois dernières par exemple),
- Lancer un
REPAIRaprès une restoration faisant suite à un défaut d’intégrité.
Efficacité de ces commandes
Le passage régulier de ces différentes commandes est-il réellement efficace?
Dans le cas de WordPress, nous pouvons réellement nous poser la question. En effet, la base est très majoritairement sollicitée en lecture (requêtes pour accéder aux informations). Les requêtes en écriture sont beaucoup moins nombreuses (ajout d’un article, d’un lien, d’une page, ou d’un commentaire), et les requêtes en effacement sont quasiment inexistantes (les révisions et les spams).
Le taux de fragmentation des tables, et des index est donc assez faible, et le recalcul des statistiques quasiment inutile.
La commande OPTIMIZE réalisant toutes les opérations nécessaires, la maintenance de la base peut donc se résumer à l’exécution régulière (1 ou 2 fois par mois) de cette commande.
Conclusion
Cette petite étude a été très intéressante pour moi, car elle m’a permis de défricher un sujet que je connaissais peu. Elle m’a également fait découvrir le champ d’action (très vaste) de la commande OPTIMIZE, qui finalement suffit amplement à nos besoins d’optimisation.
La maintenance d’une base de données « WordPress » se résume donc à
- Effectuer les purges décrites dans l’article précédent, OU (et c’est la méthode que je préconise), limiter la gestion des révisions, et n’installer les nouvelles extensions que si l’on est sûre de les utiliser,
- Exécuter la commande
OPTMIZErégulièrement, - Effectuer des tests d’intégrité périodiquement avec la commande
CHECK.
Pour en savoir un peu plus sur l’optimisation des bases de données, je vous recommande la lecture de l’article du site Database Journal (en anglais).
Partager Commentaires & rétroliens
Commentaires
Pas de commentaire
Rétroliens
Pas de rétrolien.
English