Comment définir Innodb_buffer_pool_size dans MySQL

Méthode de configuration Innodb_buffer_pool_size

Les données du cache pour les tables, index et autres tampons auxiliaires InnoDB sont stockées dans la zone mémoire du pool de tampons. La taille du pool de mémoire tampon est importante pour les performances du système. Un pool de mémoire tampon plus grand peut réduire les E/S disque pour plusieurs accès aux mêmes données de table. Il est possible de définir la taille du pool de mémoire tampon à 80 % de la taille de la mémoire physique de l'ordinateur sur un serveur de base de données dédié

Description des paramètres liés au pool de mémoire tampon

1) Paramètres des variables système

• Innodb_page_sizeInnodb_page_size

• InnoDB页面大小（默认为16KB）。页面中包含许多值，页面大小使它们可以轻松转换为字节。

• Innodb_buffer_pool_chunk_size

• innodb_buffer_pool_chunk_size 定义InnoDB缓冲池大小调整操作的块大小。默认128M。最大值可设置innodb_buffer_pool_size / innodb_buffer_pool_instances

• innodb_buffer_pool_instances

• InnoDB 缓冲池划分为的区域数。

• Innodb_buffer_pool_pages_data

• 数页在 InnoDB 缓冲池中包含的数据。该数字包括 脏页和干净页。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）。

• Innodb_buffer_pool_pages_total

• InnoDB 缓冲池 的总大小（以页为单位）。使用压缩表时，报告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （错误＃59550）

2）运行状态变量

• Innodb_buffer_pool_pages_flushed

• 从缓冲池刷新页面的请求数 。

• Innodb_buffer_pool_read_requests

• 表示从内存中读取逻辑的请求数。

• Innodb_buffer_pool_reads

• InnoDB 不能从缓冲池满足的逻辑读取的数量，必须直接从磁盘读取。

• Innodb_buffer_pool_wait_free

• Taille de la page InnoDB (16 Ko par défaut). Il existe de nombreuses valeurs contenues dans une page et la taille de la page permet de les convertir facilement en octets.

Innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size définit la taille des morceaux pour les opérations de redimensionnement du pool de tampons InnoDB. La valeur par défaut est 128 Mo. La valeur maximale peut être définie innodb_buffer_pool_instances / innodb_buffer_pool_instances

innodb_buffer_pool_instances

Le nombre de zones en lesquelles le pool de tampons InnoDB est divisé.

Innodb_buffer_pool_pages_data

Nombre de pages de données contenues dans le pool de tampons InnoDB. Ce nombre comprend les pages sales et les pages propres. Lors de l'utilisation de tables compressées, la valeur Innodb_buffer_pool_pages_data signalée peut être supérieure à Innodb_buffer_pool_pages_total (bogue n° 59550).

• Innodb_buffer_pool_pages_total

• La taille totale du pool de tampons InnoDB en pages. Lors de l'utilisation de tables compressées, la valeur Innodb_buffer_pool_pages_data signalée peut être supérieure à Innodb_buffer_pool_pages_total (Bogue n° 59550)
2) Variable d'état d'exécution

Innodb_buffer_pool_pages_flushed

Actualisez la page de le pool de mémoire tampon Nombre de requêtes. 🎜🎜🎜🎜Innodb_buffer_pool_read_requests🎜🎜🎜🎜 représente le nombre de requêtes de lecture de logique depuis la mémoire. 🎜🎜🎜🎜Innodb_buffer_pool_reads🎜🎜🎜🎜Le nombre de lectures logiques qu'InnoDB ne peut pas satisfaire à partir du pool de tampons et doivent être lues directement à partir du disque. 🎜🎜🎜🎜Innodb_buffer_pool_wait_free🎜🎜🎜🎜Normalement, les écritures dans le pool de tampons InnoDB se produisent en arrière-plan. Lorsqu'InnoDB a besoin de lire ou de créer une page et qu'aucune page propre n'est disponible, InnoDB vide d'abord certaines pages sales et attend la fin de l'opération. Ce compteur compte ces instances d'attente. S'il est défini correctement, innodb_buffer_pool_size doit avoir une valeur plus petite. 🎜🎜🎜🎜Définir raisonnablement les paramètres liés au pool de tampons🎜🎜🎜1. innodb_buffer_pool_size est défini par défaut sur 80 % de la mémoire système, puis ajusté selon les règles suivantes pour s'adapter à la situation réelle🎜🎜

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 1894874 |
+-------------------------------+---------+
1 row in set (0.00 sec)

mysql>  show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| Innodb_buffer_pool_pages_total | 1965960 |
+--------------------------------+---------+
1 row in set (0.00 sec)

# 计算是否应该添加内存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size
当结果 < 95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

🎜🎜2. défini sur 8 par défaut, minimum 1 , maximum 64🎜🎜🎜Pour les systèmes avec des pools de tampons de plusieurs gigaoctets, diviser le pool de tampons en plusieurs instances distinctes peut améliorer la concurrence en réduisant les conflits entre différents threads pour lire et écrire les pages de cache. Cette fonctionnalité est généralement utilisée sur les systèmes avec des tailles de pool de mémoire tampon de l'ordre de plusieurs Go. Plusieurs instances de pool de mémoire tampon sont configurées à l'aide de l'option de configuration innodb_buffer_pool_instances. Vous pouvez également ajuster la valeur innodb_buffer_pool_size. 🎜🎜De nombreuses demandes de données peuvent être satisfaites en les récupérant depuis la mémoire, surtout si le pool de tampons InnoDB est grand. Vous pouvez rencontrer un goulot d'étranglement lorsque plusieurs threads tentent d'accéder simultanément au pool de mémoire tampon. Vous pouvez activer plusieurs pools de mémoire tampon pour minimiser ce conflit. Les pages du pool de mémoire tampon sont stockées ou lues en mappant aléatoirement chaque page à un emplacement dans le pool de mémoire tampon à l'aide d'une fonction de hachage. Chaque pool de mémoire tampon gère sa propre liste libre, sa liste de vidage, sa LRU et toutes les autres structures de données connectées au pool de mémoire tampon, et est protégé par son propre mutex de pool de mémoire tampon. 🎜🎜Pour activer plusieurs instances de pool de mémoire tampon, définissez l'option de configuration innodb_buffer_pool_instances sur une valeur supérieure à 1 (par défaut), jusqu'à 64 (maximum). Cette option ne prend effet que lorsque innodb_buffer_pool_size est défini sur 1 Go ou plus. La taille totale que vous spécifiez sera allouée à tous les pools de mémoire tampon. Pour une efficacité optimale, spécifiez une combinaison de innodb_buffer_pool_instances et innodb_buffer_pool_size afin que chaque instance de pool de mémoire tampon fasse au moins 1 Go. 🎜🎜Résumé : 🎜

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系统内存%80
innodb_buffer_pool_instances = 大于8的情况下，每个缓冲池实例至少1GB
......

🎜Référence : 🎜🎜🎜🎜https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html🎜🎜🎜🎜https://dev.mysql.com/ doc/refman/5.7/en/server-status-variables.htm🎜🎜🎜🎜https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html🎜🎜🎜🎜 Définissez le paramètre innodb_buffer_pool_size sur la taille de la mémoire utilisée pour mettre en cache les index et les données. Bien sûr, plus il y en a, mieux c'est. La lecture et l'écriture des données en mémoire sont très rapides, ce qui réduit la lecture et l'écriture sur le disque. 🎜🎜Lorsque les données sont soumises ou que les conditions du point de contrôle sont remplies, les données de la mémoire sont immédiatement actualisées sur le disque. 🎜🎜Cependant, la mémoire est également utilisée par d'autres processus du système d'exploitation ou de la base de données. Selon l'expérience, il est recommandé de définir innodb-buffer-pool-size sur 75 % de la mémoire totale disponible du serveur. Si elle n'est pas configurée correctement, l'utilisation de la mémoire peut être gaspillée ou excessive. 🎜

对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total： innodb buffer pool的页总数目。单位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小，单位字节

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

4.在线调整InnoDB缓冲池大小，如果不设置，默认为128M

set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

• 当结果 < 95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!