Tout comme vous prenez soin de monitorer et maintenir vos applications à jour, il est également important de prendre soin des moteurs qui servent vos données car il s'agit de la brique la plus critique de votre application : si vos données sont corrompues ou ne peuvent pas être mises à jour dans le temps imparti, votre application sera directement impactée. Il est donc important d'auditer votre base de données, même dans le cas ou vous ne rencontrez pas encore de problème particulier mais afin de vous prévenir d'être victime de votre succès par la suite.

Dans cet article, j'ai volontairement choisi de me baser sur les bases de données MySQL / MariaDB, ayant eu une expérience d'audit sur ce moteur de base de données.

Avant tout, connaissez votre base de données

Le plus important pour gérer au mieux une base de données est de connaitre les données et de vous rendre compte de l'utilisation de chaque table.

La requête suivante vous aidera donc à vous donner un aperçu rapide d'une base de données:

SELECT table_name, engine, row_format, table_rows, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "size (mb)"
FROM information_schema.tables
WHERE table_schema = "video"
ORDER BY (data_length + index_length) DESC;

Cette requête vous retournera quelque-chose du type :

Les données remontées par ces champs sont les suivantes :

• table_name : Le nom de la table,
• engine : Le moteur utilisé pour stocker les données dans cette table (par défaut InnoDB depuis la version 5.5.5 de MySQL),
• row_format : Le type de formattage de la table (la valeur par défaut est définie par la variable innodb_default_row_format), sa valeur peut être DYNAMIC, COMPRESSED, REDUNDANT ou COMPACT,
• table_rows : Le nombre de ligne dans la table,
• size : La taille (en megabytes) de la table,

Formattage des fichiers contenant vos données

Comme il a été dit précédemment, InnoDB est maintenant le moteur par défaut de MySQL et MariaDB. InnoDB a évolué avec le temps et gère aujourd'hui deux types de format de fichiers : [Barracuda](https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_antelope" target="_blank">Antelope et <a href="https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_barracuda).

Antelope gère les formats de données (row_format) [COMPACT](https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_redundant_row_format" target="_blank">REDUNDANT et <a href="https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_compact_row_format). Dans l'ordre, le format REDUNDANT était utilisé par défaut dans MySQL puis il a été remplacé par le type COMPACT qui permettait de mieux gérer la représentation des valeurs nulles et la longueur des colonnes.

Barracuda est la dernière version de format de données qui prend en charge le formattage des données (row_format) [DYNAMIC](https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_compressed_row_format" target="_blank">COMPRESSED et <a href="https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_dynamic_row_format). Ces deux formats permettent de stocker des champs très volumineux en dehors du reste des données et l'ensemble des indexes et champs volumineux sont compressés, permettant de réaliser des économies de mémoire et d'espace disque. Le format DYNAMIC est une version améliorée qui permet également d'ajouter moins de données dans le buffer utilisé lorsque vous exécutez des requêtes, ce qui vous fait donc économiser des opérations I/O et des performances CPU et mémoire.

Pour résumer, vous préférerez aujourd'hui utiliser le moteur Barracuda (utilisé par défaut), plus récent et plus optimisé. De plus, il vous permet de mieux gérer les champs larges de type BLOB ou TEXT et permet de gérer des colonnes plus larges (varchar(255) avec utf8mb4).

Configuration système

La configuration système de votre serveur dépend beaucoup de l'utilisation de votre base de données, tant en terme de stockage que de requêtage.

InnoDB alloue par défaut 128MB de RAM pour son buffer et cette valeur peut être modifiée via le paramètre innodb_buffer_pool_size. Il est en général recommandé d'allouer à ce paramètre environ 60% de la mémoire physique disponible.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Cette valeur est donc la valeur par défaut (134217728 / 1024 / 1024 = 128).

Configuration MySQL

MySQL expose environ 500 variables de configuration, nous allons uniquement passer sur les plus importantes et celles qui peuvent rapidement vous apporter de l'aide. Ce sont ces valeur que vous devrez affiner en fonction des éléments que nous analyserons dans la suite de cet article.

• innodb_flush_log_at_trx_commit : Permet que le log de transaction soit écrit à chaque transaction et évite ainsi toute perte de données en cas de crash système ou machine (valeur 1), cependant, la valeur 2 est recommandée car elle permet de stocker sur le disque une fois par seconde et évite un gros afflux d'I/O disque,
• innodb_io_capacity : InnoDB se limite par défaut à 200 I/O par seconde pour ses opérations de flush, buffer et merge de données. En cas de forte activité, n'hésitez pas à augmenter cette limite à 400 I/O par seconde, ou plus si nécessaire.
• innodb_lock_wait_timeout : Il s'agit du temps d'attente par InnoDB lors de l'acquisition d'un vérou avant de le relâcher. Défini à 50 secondes par défaut, il peut être intéressant de baisser ou d'augmenter cette limite en fonction de votre utilisation afin d'éviter de vous retrouver avec des données vérouillées alors que votre application cherche à les modifier de nouveau.
• innodb_log_buffer_size : Cette valeur (définie à 16MB par défaut) permet de définir la taille du buffer utilisée pour les données de log : un buffer de log élargi vous permettra d'économiser en I/O disque avant que la transaction ne soit commitée.
• innodb_log_file_size : Ce paramètre est très important, en cas de forte charge, ce paramètre permet de transformer des opérations disques aléatoires en séquentielles, améliorant l'utilisation globale de la base de données. Par défaut, 48MB sont alloués, n'hésitez pas à augmenter cette valeur à 64 ou 128MB en fonction de l'utilisation de votre base.
• innodb_thread_concurrency : Il s'agit du nombre de threads alloués par InnoDB afin de servir les requêtes en concurrence. Il est recommandé d'allouer entre 2 et 8 fois le nombre de CPUs disponibles sur la machine (avec une valeur maximale de 256).
• key_buffer_size : En plus du buffer InnoDB, MySQL utilise ce buffer afin de stocker les tables temporaires qui peuvent être utilisées pour certaines de vos requêtes. Il est recommandé de garder une valeur de 64 à 128MB.
• max_connections : Ce paramètre limite le nombre de connexions à votre base MySQL, en fonction de son utilisation, ce paramètre peut être augmenté.
• max_heap_table_size : Ce paramètre permet de définir la taille maximale des tables temporaires implicites en mémoire.
• open_files_limit : Il s'agit du nombre maximal de fichiers pouvant être ouverts par MySQL, il est recommandé d'augmenter cette limite si votre base de données accepte beaucoup de connexions.
• query_cache_limit : Ce paramètre permet de définir la taille maximale des résultats retournés par votre requête qui seront mis en cache. Réduire cette valeur permet d'éviter qu'un gros résultat de requête ne soit caché et invalide le résultat de requêtes plus petites et plus fréquentes (ayant plus besoin du cache).
• query_cache_size : Limite la mémoire allouée au cache des résultats de vos requêtes. Une taille inférieure à 512 MB est généralement recommandée (en fonction de votre mémoire disponible).
• transaction-isolation : Le passage en READ-COMMITTED est recommandé pour ce paramètre, permettant que chaque lecture cohérente, même à l'intérieur d'une même transaction, établit et lit son propre snapshot frais (réduit le nombre de deadlocks).
• slow_query_log : Il est recommandé de laisser actif le log des requêtes lentes, en augmentant la valeur du paramètre long_query_time permettant de définir le seuil d'une requête lente.

N'hésitez pas à naviguer sur les différents paramètres afin de voir quelles seront les meilleures valeurs pour vous.

Analyse du trafic SQL

Afin de pouvoir définir ces valeurs, il convient également d'analyser le trafic de votre base de données et donc des requêtes effectuées sur votre base. Pour cela, vous pouvez décider d'activer le log général des requêtes pendant quelques secondes seulement. C'est ce que nous allons faire ici pendant 60 secondes :

SET GLOBAL general_log = 1; SELECT SLEEP(60); SET GLOBAL general_log = 0;

Vous pouvez également activer les slow query logs en adaptant la valeur du paramètre long_query_time :

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = 1;

Une fois vos logs générés, vous pouvez utiliser installer l'outil percona-tookit afin d'éffectuer une analyse plus poussée et des statistiques sur les différents logs :

$ apt-get install percona-toolkit

Vous pourrez ensuite générer ces statistiques à partir de l'outil pt-query-digest :

$ cd /var/log/mysql
$ pt-query-digest --type=genlog query.log > mysql.log.digest
$ pt-query-digest mysql-slow.log > mysql-slow.log.digest

Vous aurez ainsi dans ces fichiers un en-tête vous présentant des statistiques sur l'ensemble des requêtes enregistrées et ainsi voir les patterns de requêtes les plus effectués ou encore prenant le plus de temps :

# 270ms user time, 10ms system time, 26.00k rss, 14.00k vsz
# Current date: Sun Aug 26 14:17:47 2018
# Hostname: b85e368d128b
# Files: query.log
# Overall: 242 total, 4 unique, 0 QPS, 0x concurrency ___________________
# Time range: 2018-08-26T14:15:03.611597Z to 2018-08-26T14:16:03.612118Z
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time              0       0       0       0       0       0       0
# Query size       137.17k       8   1.89k  580.40   1.04k  472.00  299.03

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x87AF0AF11E8DFF98  0.0000  0.0%    40 0.0000  0.00 SELECT product
#    2 0xB5403927856EA576  0.0000  0.0%    40 0.0000  0.00 SELECT media media_image media_video
#    3 0x615E6B9AC6FB3863  0.0000  0.0%    27 0.0000  0.00 SELECT pattern
#    4 0x24A0450BEC6D831A  0.0000  0.0%    23 0.0000  0.00 SELECT category taxonomy

Puis, pour chaque requête, vous pouvez rechercher dans le fichier en vous basant sur l'identifiant de la requête (Query ID) afin d'obtenir plus de détail :

# Query 1: 0 QPS, 0x concurrency, ID 0x87AF0AF11E8DFF98 at byte 150147 ___
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2018-08-26T14:15:52.308078Z to 2018-08-26T14:15:52.583549Z
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16      40
# Exec time      0       0       0       0       0       0       0       0
# Query size     5   7.46k     190     191  190.90  183.58       0  183.58
# String:
# Databases    video
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `video` LIKE 'product'\G
#    SHOW CREATE TABLE `video`.`product`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT t0.pro_id AS pro_id_1, t0.pro_object_type AS pro_object_type_2, t0.pro_object_id AS pro_object_id_3 FROM product t0 WHERE t0.pro_object_type = 'media' AND t0.pro_object_id = 79 LIMIT 1\G

Profiling de requêtes SQL

Afin d'analyser plus en détail vos requêtes SQL, vous pouvez également activer le profiling sur votre session uniquement puis jouer vos requêtes :

SET SESSION profiling = 1;
SELECT * FROM mytable WHERE myfield LIKE '%toto';

Puis, pour avoir le résultat de vos profiles :

SHOW PROFILES;

Query_ID Duration Query
1 0.00026850 "SELECT * FROM mytable WHERE myfield LIKE '%toto'"

SHOW PROFILE CPU FOR QUERY 1;

Notez que le "CPU" n'est pas obligatoire, mais l'information peut être intéressante à avoir en retour. Ceci vous donnera le retour suivant :

Pour plus d'informations sur les éléments retournés par le profiler, rendez-vous sur l'URL suivante : https://dev.mysql.com/doc/refman/8.0/en/profiling-table.html

Analyse des compteurs MySQL

MySQL vient également avec tout un tas de compteurs, affichés via la requête :

SHOW GLOBAL STATUS;

Encore une fois, beaucoup d'éléments sont retournés mais un petit échantillon de compteurs vous aideront déjà à analyser l'activité de votre base de données.

Vous pourrez par exemple récupérer des informations sur les connexions :

• Aborted_clients : Il s'agit du nombre de connexions ayant été annulées car le client a échoué sans fermer la connexion,
• Aborted_connects : Il s'agit du nombre de connexions ayant échouées : un nombre important peut être indiqué dans le cas ou un check TCP (health check) serait effectué : en effet, un check TCP n'est pas suffisant pour déterminer si une base de données est fonctionnelle,

Des compteurs InnoDB sont également remontés :

• Innodb_buffer_pool_read_requests : Il s'agit du nombre de lectures effectuées.
• Innodb_buffer_pool_reads : Il s'agit du nombre de lectures effectuées depuis le disque, ne pouvant être satisfaites par le buffer pool.

Si le ratio descend en dessous de 99.95%, songez à augmenter la taille du buffer pool InnoDB afin de ne pas dégrader les performances de votre base.

• Created_tmp_disk_tables : Il s'agit du nombre de tables temporaires créées en utilisant le disque.
• Created_tmp_files : Il s'agit du nombre de fichiers créés pour satisfaire les tables temporaires.
• Created_tmp_tables : Il s'agit du nombre de tables temporaires créées depuis le démarrage du serveur.

Le ratio de tables temporaires crées sur le disque ne doit pas excéder 10-20%, idéalement, essayez de réduire le ratio en améliorant vos requêtes.

Les compteurs suivants vous donneront également des informations complémentaires sur l'utilisation du query cache MySQL :

• Qcache_free_blocks
• Qcache_free_memory
• Qcache_hits
• Qcache_inserts
• Qcache_lowmem_prunes
• Qcache_not_cached
• Qcache_queries_in_cache
• Qcache_total_blocks

Vous pouvez ainsi mesurer le ratio entre Inserts et Hits afin de vous rendre compte de l'utilisation du cache. Vous pourrez ainsi faire la chasse aux requêtes qui ne sont pas cachées. Très important : n'oubliez pas que les requêtes contenant un champ TEXT ou BLOB ou un résultat supérieur au paramètre "query_cache_limit" ne seront jamais cachées. Préferrez donc un VARCHAR(255) à un champ TEXT.

Enfin, vous trouverez également des informations sur les requêtes SELECT effectuées sur votre base :

• Select_full_join
• Select_full_range_join
• Select_range
• Select_range_check
• Select_scan

Ici, un nombre important de "Select_scan" signifie que beaucoup de "full table scan" sont effectués sur votre base, il convient alors d'analyser les requêtes avec les outils cités précédemment dans cet article pour observer ce qui ne va pas. Vous aurez certainement besoin de revoir vos indexes.

Optimisez vos requêtes SQL

Utilisez le mot-clé EXPLAIN (ou EXPLAIN EXTENDED) devant vos requêtes qui vous permettent de voir le plan d'exécution de MySQL et vous assurer que les indexes sont correctement utilisés.

Aussi, évitez les quotes dans vos requêtes lorsque vous effectuez des filtres sur des champs numériques afin d'éviter que MySQL ait besoin de faire des conversions sur les colonnes et donc de parcourir l'intégralité de la table.

Il est également important de surveiller vos clé primaires et valeurs d'auto_increment. Voici une requête qui vous permettra de voir le taux d'utilisation de vos clés primaires ainsi que le type de ceux-ci :

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE, DATA_TYPE, auto_increment, max_value, ROUND(auto_increment / max_value * 100,2) as 'pct_used'
FROM (
    SELECT t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.COLUMN_TYPE, t.auto_increment, IF(Locate('unsigned', column_type) = 0,
    CASE data_type
        WHEN 'tinyint' THEN '127'
        WHEN 'smallint' THEN '32767'
        WHEN 'mediumint' THEN '8388607'
        WHEN 'int' THEN '2147483647'
        WHEN 'bigint' THEN '9223372036854775807'
    END ,
    CASE data_type
        WHEN 'tinyint' THEN '255'
        WHEN 'smallint' THEN '65535'
        WHEN 'mediumint' THEN '16777215'
        WHEN 'int' THEN '4294967295'
        WHEN 'bigint' THEN '18446744073709551615'
    END) AS max_value
    FROM INFORMATION_SCHEMA.TABLES t
    JOIN INFORMATION_SCHEMA.COLUMNS c ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)
    WHERE c.COLUMN_KEY = 'PRI' AND EXTRA='auto_increment'
) bigq
ORDER BY pct_used DESC;

Vous aurez le retour suivant :

Dans le cas ou vous n'avez pas de clé primaire, InnoDB devra créer une clé primaire cachée qui ne sera pas du tout optimisée.

Faite également la chasse aux colonnes TEXT / BLOB afin de pouvoir bénéficier du cache query MySQL, voici une requête qui vous permettra de remonter toutes les colonnes de type BLOB ou TEXT :

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
FROM information_schema.columns
WHERE (COLUMN_TYPE LIKE '%TEXT%' OR COLUMN_TYPE LIKE '%BLOB%')
AND TABLE_SCHEMA NOT IN ('performance_schema', 'information_schema', 'mysql');

Si vous avez un doute sur un champ, vous pouvez rechercher la longueur maximale présente dans vos données pour cette clonne en jouant la requête suivante :

SELECT MAX(LENGTH(myfield)) FROM mytable;

J'espère que ces quelques informations auront pu vous aider à optimiser les performances de votre base de données, aussi bien en terme de requête qu'en terme de ressources physiques car les deux sont étroitement liées. N'hésitez pas à me contacter si je peux vous aider à aller de l'avant en vous fournissant plus d'informations.