La mémoire utilisée est un élément important à superviser sur une instance MySQL.
Une surconsommation mémoire peut déclencher l’arrêt de l’instance.
Cet article présente plusieurs solutions pour réduire l’utilisation de la mémoire notamment pendant l’import de données.

 
 
 
 
 
En cas de crash de l’instance, il est conseillé de vérifier que Out-of-Memory (OOM) killer n’est pas responsable de l’arrêt de l’instance.

egrep -i "err|fail|fault|mysql|kill" /var/log/*

La consommation mémoire d’une instance MySQL peut être définie de la manière suivante :

Global Usage = key_buffer_size + query_cache_size + 1.1 * innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size
Per Thread = thread_stack + 2 * net_buffer_length
Per Query = "buffer for reading rows" + "sorting" + "full joins" + "binlog cache" + "index preload" + "internal tmp tables"
 = max(read_buffer_size, read_rnd_buffer_size)
 + max(sort_buffer_size/2, "avg queries with scan" * "avg scans with merge" * sort_buffer_size)
 + "avg full joins" * join_buffer_size
 + "avg binlog cache use" * binlog_cache_size
 + preload_buffer_size
 + "avg tmp tables" * min(tmp_table_size, max_heap_table_size)
Total = "Global Usage" + ( max_used_connections * ("Per Thread" + "Per Query") )

Le code suivant permet d’afficher la mémoire utilisée par l’instance :

echo "SHOW VARIABLES; SHOW STATUS;" | mysql | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'

Exemple de résultat :

+------------------------------------------+--------------------+
|                          key_buffer_size |          16.000 MB |
|                         query_cache_size |           1.000 MB |
|                  innodb_buffer_pool_size |         160.000 MB |
|          innodb_additional_mem_pool_size |           0.000 MB |
|                   innodb_log_buffer_size |          16.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |         193.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.500 MB |
|                         read_buffer_size |           0.250 MB |
|                     read_rnd_buffer_size |           0.500 MB |
|                         join_buffer_size |           0.250 MB |
|                             thread_stack |           0.250 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          16.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          17.781 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                  7 |
|                          max_connections |                151 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |         317.469 MB |
|                              TOTAL (MAX) |        2877.969 MB |
+------------------------------------------+--------------------+

Après la mise à jour en version MySQL 5.7 d’une instance avec de nombreuses tables InnoDB, l’utilisation de la mémoire peut être plus élevée.
L’activation de l’instrumentation de mémoire dans le schéma de performances montre que l’utilisation principale de la mémoire provient de l’utilisation du composant memory/innodb/mem0mem :

SELECT * FROM sys.memory_global_by_current_bytes;

+--------------------------------------------+---------------+
| event_name                                 | current_alloc |
+--------------------------------------------+---------------+
| memory/innodb/mem0mem                      |       |
.                                            .               .
|                                            |               |
+--------------------------------------------+---------------+

L’utilisation supérieure de la mémoire avec MySQL 5.7 est principalement due à l’ajout de nouvelles fonctionnalités pour optimiser l’usage des tables temporaires sur disques.
Ce problème est normalement corrigé en version MySQL 5.7.19.
A partir de la version MySQL 5.6, en présence de procédures stockées de plus de 1000 lignes, mysqldump provoque une utilisation excessive de la mémoire lors du vidage des procédures stockées.
MySQL stocke les procédures dans le cache de procédure, qui est un cache alloué par thread. Cela peut utiliser plusieurs dizaines de giga de mémoire.
Dans ce cas, il faut réduire la taille des procédures stockées en factorisant le code (bonnes pratiques).
Par défaut, le client ligne de commande MySQL utilise l’API mysql_store_result qui stocke le « result set » dans une zone mémoire.
Une solution pour réduire la mémoire est d’utiliser le paramètre –quick. Cela force le client MySQL a utiliser l’API mysql_use_result (à la place de mysql_store_result) et d’envoyer le résultat ligne à ligne.
Le cache de requête ajoute une surcharge pour toutes les instructions DML.
Il est possible de désactiver le cache de requêtes pendant l’import pour améliorer les performances.

SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = OFF;

Pendant l’importation, il est possible de réduire le coût de la validation des transactions (InnoDB Flush Logs).
Cela supprime la propriété D (durabilité) dans ACID, mais cela ne doit pas poser de problème s’il n’y a aucune activité sur la base de données.

SET GLOBAL innodb_flush_log_at_trx_commit=0;

Performance Schema peut également consommer une quantité de mémoire non significative.
Pour contrôler la mémoire utilisé par Performance Schema, utiliser la commande suivante :

mysql> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

A partir de la version MySQL 5.7,

SELECT SUM(CURRENT_NUMBER_OF_BYTES_USED)
FROM   performance_schema.memory_summary_global_by_event_name
WHERE 'EVENT_NAME' LIKE 'memory/performance_schema%';

Pour désactiver Performance Schema, il faut modifier le fichier d’options, ajouter la ligne suivante et redémarrer l’instance :

[mysqld]
performance_schema = OFF