Explication détaillée de la mise en mémoire tampon MySQL et des paramètres de cache

Système de gestion de bases de données relationnelles MySQL

MySQL est un petit système de gestion de bases de données relationnelles open source développé par la société suédoise MySQL AB. MySQL est largement utilisé sur les sites Web de petite et moyenne taille sur Internet. En raison de sa petite taille, de sa vitesse rapide, de son faible coût total de possession et surtout des caractéristiques de l'open source, de nombreux sites Web de petite et moyenne taille choisissent MySQL comme base de données de site Web afin de réduire le coût total de possession d'un site Web.

Cet article vous explique principalement les deux paramètres les plus importants de mise en mémoire tampon et de cache dans le processus d'optimisation MySQL. J'espère que vous l'aimerez

Les paramètres réglables MySQL peuvent être appliqués à l'ensemble du processus mysqld ou à des sessions client individuelles.

Paramètres côté serveur

Chaque table peut être représentée comme un fichier sur le disque, qui doit d'abord être ouvert puis lu. Pour accélérer le processus de lecture des données des fichiers, mysqld met en cache ces fichiers ouverts jusqu'à un nombre maximum spécifié par table_cache dans /etc/mysqld.conf. Le listing 4 montre un moyen d'afficher l'activité liée à l'ouverture d'une table.

Listing 4. Afficher l'activité des tables ouvertes

mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 5000 |
| Opened_tables | 195  |
+---------------+-------+
2 rows in set (0.00 sec)

Listing 4 montre qu'il y a actuellement 5 000 tables ouvertes et 195 tables doivent être ouvertes car il n'y a pas de fichiers disponibles dans le cache . descripteur (les statistiques ayant été effacées précédemment, il ne peut y avoir que 195 enregistrements ouverts dans les 5 000 tables ouvertes). Si Opened_tables augmente rapidement lors de la réexécution de la commande SHOW STATUS, cela indique que le taux de réussite du cache est insuffisant. Si Open_tables est beaucoup plus petit que le paramètre table_cache, la valeur est trop grande (mais avoir de la place pour grandir n'est jamais une mauvaise chose). Par exemple, utilisez table_cache =5000 pour ajuster le cache de la table.

Semblable au cache de table, il existe également un cache pour les threads. mysqld génère des threads selon les besoins lors de la réception des connexions. Sur un serveur occupé où les connexions changent rapidement, la mise en cache des threads pour une utilisation ultérieure peut accélérer la connexion initiale.

Le listing 5 montre comment déterminer si suffisamment de threads sont mis en cache.

Listing 5. Affichage des statistiques d'utilisation des threads

mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name   | Value |
+-------------------+--------+
| Threads_cached  | 27   |
| Threads_connected | 15   |
| Threads_created  | 838610 |
| Threads_running  | 3   |
+-------------------+--------+
4 rows in set (0.00 sec)

La valeur importante ici est Threads_created, cette valeur est incrémentée à chaque fois que mysqld a besoin de créer un nouveau thread. Si ce nombre augmente rapidement lors de l'exécution de commandes SHOW STATUS successives, vous devez essayer d'augmenter le cache des threads. Par exemple, vous pouvez utiliser thread_cache = 40 dans my.cnf pour y parvenir.

Le tampon de mots clés contient le bloc d'index de la table MyISAM. Idéalement, les requêtes pour ces blocs devraient provenir de la mémoire plutôt que du disque. Le listing 6 montre comment déterminer combien de blocs ont été lus sur le disque et combien de blocs ont été lus depuis la mémoire.

Liste 6. Déterminer l'efficacité des mots clés

mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name   | Value   |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads     | 98247   |
+-------------------+-----------+
2 rows in set (0.00 sec)

Key_reads représente le nombre de requêtes arrivant sur le disque, et Key_read_requests est le nombre total. Le nombre de requêtes de lecture qui atteignent le disque divisé par le nombre total de requêtes de lecture correspond au taux d'échecs : dans ce cas, pour 1 000 requêtes, environ 0,6 échecs en mémoire. Si le nombre d'accès au disque dépasse 1 pour 1 000 requêtes, vous devez envisager d'augmenter la mémoire tampon des mots clés. Par exemple, key_buffer =384M définira le tampon sur 384 Mo.

Les tables temporaires peuvent être utilisées dans des requêtes plus avancées, où les données doivent être enregistrées dans une table temporaire avant un traitement ultérieur (comme une clause GROUPBY, idéalement, créez la table temporaire en mémoire). Mais si la table temporaire devient trop volumineuse, elle doit être écrite sur le disque. Le listing 7 donne des statistiques liées à la création de tables temporaires.

Listing 7. Détermination de l'utilisation de tables temporaires

mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files    | 2   |
| Created_tmp_tables   | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Chaque utilisation d'une table temporaire augmentera Created_tmp_tables ; les tables sur disque augmenteront également Created_tmp_disk_tables. Il n’y a pas de règles strictes pour ce ratio, car il dépend de la requête concernée. Regarder Created_tmp_disk_tables au fil du temps vous montrera le ratio de tables de disques créées et vous pourrez déterminer l'efficacité de votre configuration. tmp_table_size et max_heap_table_size contrôlent tous deux la taille maximale de la table temporaire, alors assurez-vous que les deux valeurs sont définies dans my.cnf.

Paramètres par session

Les paramètres suivants sont spécifiques à chaque session. Soyez très prudent lors du paramétrage de ces nombres car multipliés par le nombre de connexions pouvant exister, ces options représentent une grande quantité de mémoire ! Vous pouvez modifier ces numéros au sein d'une session via du code, ou modifier ces paramètres dans my.cnf pour toutes les sessions.

Lorsque MySQL doit trier, il alloue un tampon de tri pour stocker les lignes de données lors de la lecture des données sur le disque. Si les données à trier sont trop volumineuses, elles doivent être enregistrées dans un fichier temporaire sur le disque et triées à nouveau. Si la variable d'état sort_merge_passes est grande, cela indique une activité du disque. Le listing 8 donne des informations sur les compteurs d'état liés au tri.

Listing 8. Afficher les statistiques de tri

mysql> SHOW STATUS LIKE "sort%";
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| Sort_merge_passes | 1    |
| Sort_range    | 79192  |
| Sort_rows     | 2066532 |
| Sort_scan     | 44006  |
+-------------------+---------+
4 rows in set (0.00 sec)

如果 sort_merge_passes 很大，就表示需要注意sort_buffer_size。例如，sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如清单 9 所示。

清单 9. 确定表扫描比率

mysql> SHOW STATUS LIKE "com_select";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_select  | 318243 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE "handler_read_rnd_next";
+-----------------------+-----------+
| Variable_name     | Value   |
+-----------------------+-----------+
| Handler_read_rnd_next | 165959471 |
+-----------------------+-----------+
1 row in set (0.00 sec)

Handler_read_rnd_next /Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过4000，就应该查看 read_buffer_size，例如read_buffer_size = 4M。如果这个数字超过了8M，就应该与开发人员讨论一下对这些查询进行调优了！

查看数据库缓存配置情况

mysql> SHOW VARIABLES LIKE ‘%query_cache%';
+——————————+———+
| Variable_name | Value |
+——————————+———+
| have_query_cache | YES | –查询缓存是否可用
| query_cache_limit | 1048576 | –可缓存具体查询结果的最大值
| query_cache_min_res_unit | 4096 |
| query_cache_size | 599040 | –查询缓存的大小
| query_cache_type | ON | –阻止或是支持查询缓存
| query_cache_wlock_invalidate | OFF |
+——————————+———+

配置方法：

在MYSQL的配置文件my.ini或my.cnf中找到如下内容：

# Query cache is used to cache SELECT results and later returnthem

# without actual executing the same query once again. Having thequery

# cache enabled may result in significant speed improvements, ifyour

# have a lot of identical queries and rarely changing tables.See the

# "Qcache_lowmem_prunes" status variable to check if the currentvalue

# is high enough for your load.

# Note: In case your tables change very often or if your queriesare

# textually different every time, the query cache may result ina

# slowdown instead of a performance improvement.

query_cache_size=0

以上信息是默认配置，其注释意思是说，MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。

具体配置方法：

1． 将query_cache_size设置为具体的大小，具体大小是多少取决于查询的实际情况，但最好设置为1024的倍数，参考值32M。

2． 增加一行：query_cache_type=1

query_cache_type参数用于控制缓存的类型，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。但是这种情况下query_cache_size设置的大小系统是否要为其分配呢，这个问题有待于测试？

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

OK，配置完后的部分文件如下：

query_cache_size=128M

query_cache_type=1

保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：

mysql> show variables like '%query_cache%';

+——————————+———–+

| Variable_name      |Value  |

+——————————+———–+

| have_query_cache     |YES   |

| query_cache_limit     |1048576  |

| query_cache_min_res_unit  |4096   |

| query_cache_size     | 134217728|

| query_cache_type     |ON    |

| query_cache_wlock_invalidate | OFF   |

+——————————+———–+

6 rows in set (0.00 sec)

主要看query_cache_size和query_cache_type的值是否跟我们设的一致：

这里query_cache_size的值是134217728，我们设置的是128M，实际是一样的，只是单位不同，可以自己换算下：134217728 = 128*1024*1024。

query_cache_type设置为1，显示为ON，这个前面已经说过了。

总之，看到上边的显示表示设置正确，但是在实际的查询中是否能够缓存查询，还需要手动测试下，我们可以通过show statuslike '%Qcache%';语句来测试，现在我们开启了查询缓存功能，在执行查询前，我们先看看相关参数的值：

mysql> show status like '%Qcache%';

+————————-+———–+

| Variable_name    |Value  |

+————————-+———–+

| Qcache_free_blocks   |1    |

| Qcache_free_memory   | 134208800|

| Qcache_hits     |0    |

以上就是mysql缓冲和缓存设置详解的内容，更多相关内容请关注PHP中文网（www.php.cn）！