Optimisation basique d'une base de données MySQL

Pas de formule magique, l'optimisation d'une base dépend de l'usage qui est en fait.

Voici un guide simple pour optimiser rapidement une base. Le fine-tuning fera l'objet d'un article séparé.

Utiliser la RAM pour les données les plus utilisées

Les IO disques sont très couteux par rapport à la RAM. L'utilisation d'un disque RAM pour les données temporaires est bénéfique.
Les tables de référence fréquemment utilisées peuvent également être dupliquées au lancement de l'application dans des tables mémoire.

Contrôler les paramètres clés

Les indicateurs de fonctionnement de MySQL sont disponibles via cette commande :

 mysqladmin -uroot -p extended-status variables

Cacher les index

Les variables les plus importantes sont table_open_cache et key_buffer_size.
Le moteur MySQL doit pouvoir ouvrir suffisamment de fichiers en parallèle pour effectuer ses requêtes et mettre l'intégralité de ses index en mémoire :

• table_open_cache définit le nombre maximum de tables ouvertes par le moteur mysqld. Ajuster l'indicateur pour que d(Opened_tables) < table_open_table. Commencer avec la valeur 256
• key_buffer_size définit la taille du cache pour les index du moteur. Il doit être ajusté pour que le ratio Key_reads / Key_read_requests reste inférieur à 0.01. Commencer avec la valeur 16M

Cacher les résultats des requêtes

En cachant les résultats des requêtes, les temps de réponse pour une même requête vont être divisés par un facteur 100 à 1000.
Editer le fichier de configuration /etc/mysql/my.cnf :

• query_cache_type active le cache implicite des résultats. Mettre à 1
• query_cache_size définit la taille du cache résultat. Commencer à 16M. Augmenter tant que la mémoire libre est consommée et que le cache hit ratio n'est pas proche de 100% ou que le turn-over du cache n'est pas proche de zéro. Les indicateurs à suivre sont Qcache_free_memory, Qcache_lowmem_prunes et qcache_hits / (com_select + qcache_hits)
• query_cache_min_res_unit délimite la taille minimale des réponses cachées. Commencer avec 4K. Une valeur basse augmente le cache hit ratio mais augmente aussi la fragmentation du cache. Si la mémoire est trop fragmentée, le moteur peut procéder à des purges et le cache devient moins performant. Il faut alors augmenter cette valeur. Surveiller le paramètre Qcache_lowmem_prunes
• query_cache_limit délimite la taille maximale des résultats cachés pour éviter une purge des autres résultats. Commencer avec 1-10% de query_cache_size. Surveiller le paramètre Qcache_lowmem_prunes
• Sort_merge_passes dénombre le nombre de passe nécessaire pour faire un tri. Augmenter sort_buffer si la valeur augmente.

Surveiller la consommation de la mémoire

La taille limite maximale des caches est fixée par la mémoire disponible.
Il existe des buffers associés au deamon MySQL et des buffers associé aux threads de travail :

• RAM disponible > global buffers + (thread buffers * connections)
• Global buffers = key_buffer + innodb_buffer_pool + innodb_log_buffer + innodb_additional_mem_pool + net_buffer
• Thread_buffers = sort_buffer + myisam_sort_buffer + read_buffer + join_buffer + read_rnd_buffer

Optimiser le modèle et les requêtes

La base de données doit être simple et efficace à l'usage. Le modèle doit donc être amélioré en amont de l'optimisation des requêtes SQL. Une requête de mauvaise qualité restera peu performante même avec un cache.

Différents indicateurs sont à surveiller :

• Select_full_join indique les jointures faites sur des colonnes sans utilisation d'index. Ajouter les index manquants ou optimiser la requête.
• Table_locks_waited compte les verrouillages de table aboutissant à des contentions, dramatique en terme de performance et responsable des principals pannes de CMS. Optimiser les requêtes.
• Handler_read_rnd_next dénombre les "table-scan". Ce comportement peut être plus performant que l'usage des index mais nécessite un contrôle manuel.
• Slow_queries compte le nombre de requêtes SQL lentes. Celle-ci doivent être étudiées à l'aide de la commande EXPLAIN.

Les tables peuvent être dénormalisées - redondances des données - pour éviter les jointures.
Les bases supportant des mises à jour volumineuses seront optimisées en utilisant des triger, traitements pre et post insertion de données. Les résultats de requêtes complexes pourront ainsi être pré-calculées et stockés dans des tables temporaires.

Optimiser la communication entre les serveurs

Les connexions cryptées doublent les volumes réseau échangés. Remplacer les si possible par une architecture matérielle sécurisée.

Utiliser une ferme de serveur

Une ferme de bases répliquées répartit mieux la charge issus de nombreuses connexions majoritairement en consultation. Un système avec une base en READ-ONLY et une autre en READ/WRITE régulièrement synchronisée sera très efficace.

Flickr a communiqué son infrastructure en 2008.
Elle se résumait à 3 fermes de cluster (maître-esclaves) :

• une ferme de serveurs MySQL utilisant le moteur InnoDB pour générer les mises à jour transactionnelles répliquée à
• une ferme de serveurs MySQL MyISAM utilisée pour les affichages des frontaux Web
• une ferme de serveurs MySQL MyISAM utilisée pour les recherches des internautes.

La séparation des consultations et des recherches permet à Flickr de limiter les contentions.