Comment améliorer les performances du serveur MySQL avec une optimisation appropriée

7. Configuration du cache pour MyISAM

Définition de la taille de la clé tampon pour MyISAM

La clé tampon (key_buffer_size) est le cache principal utilisé par le moteur de stockage MyISAM pour stocker les index. Le bon réglage de la key_buffer_size est essentiel pour garantir des performances optimales, car les index fréquemment consultés sont stockés dans une mémoire tampon, ce qui réduit le besoin d’un accès plus lent au disque.

key_buffer_size : La taille de la clé tampon détermine la quantité de mémoire allouée pour stocker l’index MyISAM. Ce paramètre doit être ajusté en fonction de la quantité de mémoire physique disponible et de la quantité de données consultées périodiquement. En général, si votre serveur utilise exclusivement MyISAM, vous pouvez allouer entre 25 et 30 % de la mémoire physique totale à key_buffer_size.

Exemples de paramètres dans le fichier de configuration MySQL (my.cnf) :

[mysqld]

key_buffer_size = 4G

Cette personnalisation permet à MySQL de stocker plus d’index en mémoire, ce qui peut accélérer les recherches et les requêtes fréquemment exécutées.

Utilisation de plusieurs tampons de clés pour MyISAM

Sur certaines versions de MySQL, il est possible d’utiliser plusieurs caches de clés pour MyISAM. Cela peut être utile dans les situations où vous souhaitez allouer différents caches à différentes tables ou groupes de tables, améliorant ainsi les performances de manière plus contrôlée et granulaire.

Configuration de plusieurs tampons de clés :

Vous pouvez créer plusieurs tampons de clés et allouer des tables spécifiques à ces tampons. Pour ce faire, utilisez des commandes SQL telles que CACHE INDEX et LOAD INDEX INTO CACHE. Cependant, notez que cette fonctionnalité est utilisée moins fréquemment et peut ne pas être prise en charge dans toutes les versions de MySQL.

Exemples de paramètres pour l’utilisation de plusieurs tampons de clés :

— Créez des tampons de clés supplémentaires

SET GLOBAL keycache1.key_buffer_size=2G ;

SET GLOBAL keycache2.key_buffer_size=2G ;

— Allocation de tables à des tampons de clés spécifiques

CACHE INDEX tabel1, tabel2 IN keycache1 ;

CACHE INDEX tabel3 IN keycache2 ;

— Chargement des index dans le cache

LOAD INDEX INTO CACHE tabel1, tabel2, tabel3 ;

De cette façon, vous pouvez allouer différentes clés tampon à différentes tables, ce qui vous permet d’optimiser l’utilisation de la mémoire en fonction des modèles d’accès spécifiques à ces tables.

8. Configuration du cache pour InnoDB

Définition de la taille du pool de mémoires tampons pour InnoDB

Pour améliorer les performances d’InnoDB, l’une des étapes importantes consiste à définir la bonne taille de pool de mémoires tampons. Voici les étapes que vous pouvez suivre :

1. Déterminez la taille de pool de mémoires tampons appropriée :

• La taille idéale du pool de mémoires tampons pour InnoDB est généralement d’environ 80 % de la capacité de mémoire physique du serveur. Cependant, vous pouvez utiliser plus que cela si le serveur dispose de beaucoup de mémoire.
• Exemple : innodb_buffer_pool_size = 80 % de la mémoire physique totale.

2. Définissez la taille du pool de mémoires tampons en fonction des besoins :

• Si votre serveur dispose de beaucoup de mémoire, vous pouvez augmenter la taille de la mémoire tampon du pool de 90 % ou plus.
• Exemple : innodb_buffer_pool_size = 90 % de la mémoire physique totale.

3. Utiliser InnoDB pour améliorer les performances :

• InnoDB est conçu pour optimiser les performances en utilisant un grand pool de mémoires tampons pour stocker les données et les index.
• En utilisant InnoDB, vous pouvez réduire l’accès au disque et améliorer les performances des requêtes.

Utilisation d’InnoDB pour améliorer les performances

Pour utiliser InnoDB efficacement et améliorer les performances, vous pouvez suivre les étapes suivantes :

1. Définir les bons paramètres InnoDB :

• Assurez-vous que les paramètres InnoDB tels que innodb_buffer_pool_size, innodb_log_file_size et innodb_flush_log_at_trx_commit sont adaptés aux besoins de votre serveur.
• Exemple : innodb_buffer_pool_size = 80 % de la mémoire physique totale.

2. Utilisation de pools de mémoires tampons pour améliorer les performances :

• Utilisez un pool de mémoires tampons pour stocker les données et les index, ce qui peut réduire l’accès au disque et améliorer les performances des requêtes.
• Exemple : innodb_buffer_pool_size = 80 % de la mémoire physique totale.

3. Optimiser les performances à l’aide d’InnoDB :

• Utilisez InnoDB pour optimiser les performances en utilisant un pool de mémoires tampons volumineux pour stocker les données et les index.
• Exemple : innodb_buffer_pool_size = 80 % de la mémoire physique totale.

9. Tests et surveillance

Utilisation d’outils pour mesurer les performances du cache

Pour mesurer les performances du cache, vous pouvez utiliser plusieurs outils fournis par MySQL. Voici quelques façons de mesurer les performances du cache :

1. Utilisation de SHOW STATUS et SHOW VARIABLES :

Vous pouvez utiliser les commandes SHOW STATUS et SHOW VARIABLES pour mesurer les performances du cache. Exemple:

SHOW STATUS LIKE 'Key_reads' ;

SHOW STATUS LIKE 'Key_read_requests' ;

SHOW STATUS LIKE 'Key_blocks_unused' ;

SHOW STATUS LIKE 'key_buffer_size' ;

2. Utilisation d’innotop :

Innotop est un outil qui peut vous aider à mesurer les performances d’InnoDB plus en détail. Exemple:

innotop -i 10 --status

3. À l’aide de la commande mysqladmin :

Vous pouvez utiliser la commande mysqladmin pour mesurer en permanence les performances du cache. Exemple:

mysqladmin extended-status -r -i 10 | grep Key_reads

Calcul du taux d’accès au cache et du pourcentage de mémoire tampon utilisé

Pour calculer le taux d’accès au cache et le pourcentage de tampon utilisé, vous pouvez utiliser certaines équations fournies par MySQL. Voici quelques façons de le calculer :

1. Calcul du taux de réussite du cache :

Le taux d’accès au cache peut être calculé à l’aide de l’équation suivante :

Cache hit ratio =10 -(Key_reads *100) / Key_read_requests)

Exemple :

mysql> SHOW STATUS LIKE 'Key_reads';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Key_reads     | 100    |
+---------------+--------+
mysql> SHOW STATUS LIKE 'Key_read_requests';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_read_requests | 1000  |
+-------------------+--------+
mysql> SELECT 100 - ((100 * 100) / 1000);
+-----------------------+
| 99.00                 |
+-----------------------+

2. Calcul du pourcentage de tampons utilisés :

Le pourcentage de tampons utilisés peut être calculé à l’aide de l’équation suivante :

Pourcentage de tampon utilisé  = 100 - ((Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size)

Exemple:

mysql> SHOW STATUS LIKE 'Key_blocks_unused';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_blocks_unused  | 1000  |
+-------------------+--------+
mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------------+--------+
| Variable_name        | Value  |
+-----------------------+--------+
| key_buffer_size      | 1024M |
+-----------------------+--------+
mysql> SELECT 100 - ((1000 * 1024 * 1024) / (1024 * 1024));
+-----------------------+
| 99.00                 |
+-----------------------+