Surveillance du serveur MySQL

Executer

SHOW ENGINE INNODB STATUS\G

Surveillez notamment le Buffer pool hit rate qui, s'il est élevé (par exemple 999/1000), indique que mysql va majoritairement chercher ses données en mémoire plutôt que sur le disque.

Pour plus de détails, voir

• https://www.rathishkumar.in/2017/01/how-to-allocate-innodb-buffer-pool-size-in-mysql.html
• http://mysqlopt.blogspot.com/2012/01/mysql-server-tuning.html

Pour demander à MySQL une liste des requêtes actuellement en cours d'exécution, on peut utiliser la requête :

SHOW FULL processlist;

Inconvénient : On récupère un tableau de la situation à l'instant de la demande. On ne sait rien de ce qui s'est passé avant, ni de ce qui se passe après.

. /root/check_serveur_bdd.sh

SHOW FULL processlist
SHOW ENGINE INNODB STATUS\G

(documenter la manière d'activer cette fonctionnalité…)

Le traçage exhaustif de ces requêtes suspectes se trouve dans le fichier de log :

/var/log/mysql/mysql-slow.log

Il concerne le jour courant. logrotate archive les logs des jours précédents dans les fichier mysql-slow.log.n.gz ('n' = itération)

Pour suivre l'évolution dans le temps de certains indicateurs clés, on peut ajouter une tache cron au serveur, avec crontab -e :

*/1 * * * * /root/check_serveur_bdd.sh

Dans le script check_serveur_bdd.sh, placer :

echo `date +"%d/%m/%Y %H:%M:%S"` = `mysql -u root -p<pw...> -h bddmoodle.unicaen.fr information_schema -BNe 'select count(*) from processlist'` >> nb_bdd_queries.log
echo `date +"%d/%m/%Y %H:%M:%S"` = `mysql -u root -p<pw...> -h bddmoodle.unicaen.fr information_schema -BNe "show status like 'opened_files'"` >> nb_bdd_opened_files.log

(avec <pw…> pour le mot de passe root du serveur mysql)

Ensuite les 2 fichiers générés (nb_bdd_….log) peuvent être

• soit regardé sur place, par exemple avec

tail nb_bdd_*

• soit récupérés (par exemple sur un poste de travail via un scp) et ouverts dans Libre Office Calc (ajouter '=' comme séparateur de champs au moment de l'ouverture) pour faire l'objet de graphiques.

Pour surveiller en temps réel l'activité du serveur MySQL, et voir chaque nouvelle requête qui lui est demandée, il faut configurer le serveur MySQL pour avoir ces informations dans une sortie de log verbeuse.

• Avantage : temps réel
• Inconvénients :
  • c'est très verbeux et peu vite devenir illisible
  • génère potentiellement de très gros fichier de log
  • impact sur les performances

(voir la doc MySQL officielle)

Se connecter à la base de données avec le client SQL de son choix et taper

SET GLOBAL general_log=1;

Pour voir en direct les requêtes exécutées, sur le serveur mariadb, taper :

tail -f /var/lib/mysql/$(hostname).log

(synthèse d'informations déduites du ticket 116024)

Parmi les paramètres importants concernant les performances d'ecampus, il y a :

• innodb_buffer_pool_size qui définit l'espace mémoire alloué à MySQL (à son moteur innodb en particulier)
• join_buffer_size taille du buffer disponible pour effectuer des jointures
• table_definition_cache (lié à innodb_open_files, voir https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_definition_cache)

Pour consulter la valeur d'une variable de configuration, par exemple sur ecampus :

mysql -u root -p ecampus -e "show variables like 'innodb_buffer_pool_size';"

(qui retourne ici 2147483648, ce qui signifie que MySQL dispose de 2Go de RAM pour travailler).

Pour modifier une variable de façon durable, il faut le faire dans le fichier de configuration MySQL.

service mysql restart