Configuration et optimisation de MySQL

Introduction

Dans ce laboratoire, vous apprendrez les bases de la configuration et de l'optimisation des performances du serveur MySQL. Vous commencerez par inspecter les paramètres actuels du serveur, puis vous modifierez un paramètre clé de performance, le innodb_buffer_pool_size, pour observer comment les changements sont appliqués.

Le laboratoire vous guidera à travers l'édition du fichier de configuration MySQL, le redémarrage du serveur pour appliquer les changements, et la vérification que les nouveaux paramètres sont actifs. Enfin, vous apprendrez une méthode de base pour analyser les performances des requêtes à l'aide du profileur intégré de MySQL. Cela vous fournira une base pour optimiser votre base de données pour différentes charges de travail.

Afficher la configuration actuelle de MySQL

Avant d'apporter des modifications, il est crucial de comprendre la configuration actuelle de votre serveur MySQL. Dans cette étape, vous vous connecterez à MySQL et inspecterez la valeur d'une variable de performance critique, innodb_buffer_pool_size.

Tout d'abord, ouvrez le terminal depuis votre bureau.

Connectez-vous au serveur MySQL en tant qu'utilisateur root. Dans cet environnement de laboratoire, vous pouvez utiliser sudo pour vous connecter sans mot de passe.

sudo mysql -u root

Une fois connecté, vous verrez l'invite MySQL (mysql>).

Les variables système contrôlent le comportement du serveur MySQL. La variable innodb_buffer_pool_size détermine la quantité de mémoire allouée pour mettre en cache les données et les index des tables InnoDB. Un pool de tampons correctement dimensionné est essentiel pour de bonnes performances.

Utilisez la commande SHOW VARIABLES avec une clause LIKE pour trouver la valeur actuelle de cette variable.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Vous verrez une sortie similaire à la suivante, indiquant la valeur par défaut en octets. Notez cette valeur, car vous la modifierez à l'étape suivante.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+--------------------------+-----------+
1 row in set (0.01 sec)

Maintenant que vous avez vérifié la configuration actuelle, vous pouvez quitter le shell MySQL.

exit

Modifier le fichier de configuration MySQL

Les paramètres MySQL peuvent être modifiés temporairement pour la session en cours ou de manière permanente dans un fichier de configuration. Pour qu'un changement persiste après les redémarrages du serveur, vous devez modifier le fichier de configuration. Dans cette étape, vous allez modifier /etc/mysql/my.cnf pour augmenter le innodb_buffer_pool_size.

Ouvrez le fichier de configuration MySQL en utilisant l'éditeur de texte nano avec les privilèges sudo.

sudo nano /etc/mysql/my.cnf

Faites défiler vers le bas pour trouver la section [mysqld]. Cette section contient les paramètres spécifiques au démon du serveur MySQL. Ajoutez la ligne suivante sous l'en-tête [mysqld] pour définir la taille du pool de tampons à 256 mégaoctets.

innodb_buffer_pool_size=256M

Votre section [mysqld] devrait maintenant ressembler à ceci :

[mysqld]
innodb_strict_mode=OFF
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=256M

Maintenant, enregistrez le fichier et quittez nano. Appuyez sur Ctrl+X, tapez Y pour confirmer les modifications, puis appuyez sur Entrée pour écrire dans le fichier.

Les modifications apportées au fichier de configuration ne prendront effet qu'après le redémarrage du serveur MySQL. Utilisez la commande service pour le redémarrer.

sudo service mysql restart

Vous avez maintenant mis à jour la configuration de manière permanente. Dans l'étape suivante, vous vérifierez que le changement est actif.

Vérifier la modification de la configuration

Après avoir modifié le fichier de configuration et redémarré le serveur, vous devez vérifier que le nouveau paramètre a été appliqué correctement. Dans cette étape, vous vous reconnecterez à MySQL et vérifierez à nouveau la variable innodb_buffer_pool_size.

Connectez-vous au serveur MySQL.

sudo mysql -u root

Maintenant, exécutez à nouveau la commande SHOW VARIABLES pour voir la nouvelle valeur.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

La sortie devrait maintenant afficher la nouvelle valeur en octets. MySQL convertit automatiquement 256M (256 mégaoctets) en 268435456 octets.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)

La comparaison de cette valeur avec celle que vous avez notée à l'étape 1 confirme que votre modification de configuration a réussi et est maintenant active.

Vous pouvez maintenant quitter le shell MySQL.

exit

Analyser les performances des requêtes

L'optimisation des variables du serveur est effectuée pour améliorer les performances des requêtes. Bien qu'une analyse approfondie soit complexe, vous pouvez utiliser le profileur de requêtes intégré de MySQL pour obtenir une mesure de base du temps d'exécution des requêtes. Dans cette étape, vous allez créer une table de test, insérer des données et analyser une requête simple.

Connectez-vous d'abord au serveur MySQL.

sudo mysql -u root

Créez une nouvelle base de données nommée testdb et basculez vers celle-ci.

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

Ensuite, créez une table nommée employees pour stocker des données d'exemple.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

Insérez quelques enregistrements dans la table employees.

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Engineering', 80000),
('Charlie', 'Sales', 65000),
('David', 'Marketing', 55000);

Maintenant, activez le profileur de requêtes pour votre session. Cela enregistrera les données de performance pour les requêtes ultérieures.

SET profiling = 1;

Exécutez une requête que vous souhaitez analyser. Par exemple, recherchons tous les employés du département 'Sales'.

SELECT * FROM employees WHERE department = 'Sales';

Pour voir les résultats de performance, utilisez la commande SHOW PROFILES. Celle-ci liste les requêtes que vous avez exécutées depuis l'activation du profilage et leurs durées.

SHOW PROFILES;

La sortie ressemblera à ceci, montrant la durée de chaque requête en secondes.

+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00038500 | SELECT * FROM employees WHERE department = 'Sales'    |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Cette colonne Duration vous donne une référence pour les performances des requêtes. Dans un scénario réel, vous utiliseriez cet outil pour comparer les vitesses des requêtes avant et après les modifications de configuration sur des ensembles de données beaucoup plus importants.

Vous avez utilisé avec succès le profileur pour analyser une requête. Vous pouvez maintenant quitter le shell MySQL.

exit;

Résumé

Dans ce laboratoire, vous avez appris le processus de base de configuration et d'optimisation d'un serveur MySQL. Vous avez pratiqué la visualisation de la configuration actuelle du serveur en inspectant des variables système telles que innodb_buffer_pool_size.

Vous avez acquis une expérience pratique en modifiant le fichier de configuration MySQL (my.cnf) pour apporter des changements permanents, en redémarrant le serveur pour les appliquer et en vérifiant que les nouveaux paramètres sont actifs. Enfin, vous avez été initié à une technique d'analyse de performance de base en utilisant le profileur de requêtes intégré de MySQL pour mesurer la durée des requêtes.

Ces compétences fondamentales sont essentielles pour tout développeur ou administrateur responsable du maintien d'une base de données MySQL saine et performante.