WordPress: optimisation avancée de la base de données

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 TABLES nous 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 REPAIR aprè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 OPTMIZE ré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).

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *