在 MySQL 性能调优中，很少有变量像 innodb_buffer_pool_size 那样具有如此大的影响力。 这个重要的 MySQL 配置参数直接影响 MySQL 中最常用的存储引擎 InnoDB 的性能。在本文中，我们将研究 innodb_buffer_pool_size 配置参数，说明如何计算最佳缓存池大小并了解其对数据库性能的深远影响。
1. 了解 innodb_buffer_pool_size
innodb_buffer_pool_size 是一个关键变量，它决定了InnoDB存储引擎使用的内存缓冲池的大小。 该缓冲池是 InnoDB 存储表中数据和索引的缓存，有助于快速访问常用信息。
将 InnoDB 缓冲池想象为 MySQL 服务器跳动的心脏，随着数据和索引的命脉而脉动。 毫不夸张地说，这个单一的配置变量可以区分缓慢、性能不佳的数据库和闪电般快速、响应迅速的数据库。
2. 对数据库性能的影响
InnoDB 存储了一个经常访问的数据页和索引块的副本。 这些不仅仅是随机数据片段， 它们代表了数据库最依赖的信息。
当应用程序向数据库发送查询时，MySQL 服务器会检查请求的数据是否已存在于缓冲池中。 如果是，则可以认为这是直接命中缓存，数据几乎是即时获取的，因为它已经在服务器的内存中。
所以，综上所述，innodb_buffer_pool_size直接影响MySQL的性能有以下几个方面：
- 提高读取性能：更大的缓冲池允许在内存中存储更多数据，从而减少从磁盘读取的需要。 这显著提高了读取性能，因为从内存访问数据比从磁盘获取数据要快得多。
- 增强查询执行：借助相当大的缓冲池，经常访问的数据保留在内存中，从而加快查询执行速度。 涉及扫描大型数据集的复杂查询从这种优化中受益匪浅。
- 优化写入性能：InnoDB采用两阶段提交机制，更大的缓冲池可以通过高效的写入缓冲最大限度地减少磁盘写入，从而增强写入性能。
- 减少磁盘 I/O：通过将大部分频繁访问的数据保留在内存中，可以显着减少磁盘 I/O 操作的数量，从而实现更快的 QPS（每秒查询数）吞吐量，同时减少整体磁盘磨损。
3. 计算合适的 innodb_buffer_pool_size
计算合适的 innodb_buffer_pool_size 需要在内存分配和性能增益之间取得平衡。 以下是计算最佳缓冲池大小的分步方法：
- 评估可用 RAM 总量：首先确定 MySQL 服务器上的可用 RAM 总量。 这是决定可以分配多少内存给缓冲池的基本因素。
- 分配大部分 RAM：通常建议将大部分可用 RAM 分配给缓冲池。 根据经验，将大约 70-80% 的可用 RAM 用于缓冲池是一个很好的起点，特别是对于专用数据库服务器。
- 监控性能指标：设置缓冲池后，密切监控关键性能指标，例如缓存命中率和磁盘读取。 如果缓存命中率较高且磁盘读取较低，则缓冲池大小可能足够。 如果不是，请考虑根据这些指标调整大小。
- 实验和微调：随着时间的推移，根据性能监控和不断变化的工作负载，尝试不同的缓冲池大小并观察对数据库性能的影响。 相应地微调大小以实现最佳性能。
当InnoDB缓冲池显示0%的可用空间，表明innodb_buffer_pool_size已达到其容量。 出现这种情况是因为 InnoDB 数据空间为 134 GB，而 innodb_buffer_pool_size 设置为 45 GB。 然而，服务器安装的物理内存只有59GB，不足以容纳数据大小。
为了应对这一挑战，请考虑减少数据大小或通过添加更多 RAM 来升级服务器 - 特别是在遇到数据库性能或吞吐量挑战时。
如果您正在管理非常大或非常高吞吐量的 MySQL 数据库，您可能需要更高级的 MySQL 监控。 例如，在上面的示例中，如果大部分 MySQL 数据不被频繁访问，那么 innodb_buffer_pool_size=45G 不会造成性能大幅下降。
然而，如果 134G 的 90% 被频繁访问，而 RAM 中最多只存储 45G，那么三分之二的请求将频繁地从磁盘（HD、NVMe）提供服务，速度要慢得多。
在这种情况下，密切关注数据库、观察缓慢查询的指标和任何其他性能下降的信号就变得至关重要。
以下是我们的战略合作伙伴 Datadog 关于进一步 MySQL 监控的宝贵见解：
“MySQL 公开了数百个有关查询执行和数据库性能的有价值的指标和统计数据。 为了持续收集这些指标，Datadog Agent 会不断收集 MySQL 统计数据和指标，这可以让您识别近期和长期的性能趋势，并可以帮助您识别和调查出现的问题。”
– datadoghq.com
4. 微调 innodb_buffer_pool_size
由于上述MySQL服务器是专用服务器，因此我们使用innodb_dedicated_server变量来自动设置innodb_buffer_pool_size和innodb_redo_log_capacity。
“仅当 MySQL 实例驻留在可以使用所有可用系统资源的专用服务器上时，才考虑启用 innodb_dedicated_server。 例如，如果您在 Docker 容器或仅运行 MySQL 的专用 VM 中运行 MySQL Server，请考虑启用 innodb_dedicated_server。 如果MySQL实例与其他应用程序共享系统资源，则不建议启用innodb_dedicated_server。”
这是一种平衡行为； 向缓冲池分配太多内存，您可能会面临服务器上其他重要进程内存匮乏的风险，从而可能导致性能瓶颈或崩溃。 如果分配太少内存，您就无法充分利用宝贵的资源，从而使数据库经常依赖于较慢的磁盘读取。
innodb_buffer_pool_size 的最佳最佳点可能会因以下几个因素而有所不同：
- 数据库大小：较大的数据库通常需要较大的缓冲池来有效缓存大部分数据。
- 工作负载类型：考虑数据库的工作负载。 读取密集型工作负载可以从更大的缓冲池中受益匪浅，以缓存经常访问的数据，而写入密集型工作负载可能需要平衡的方法。
- 服务器配置：考虑服务器的整体配置，尤其是可用 RAM 的数量。 向缓冲池分配过多的内存可能会导致内存争用问题。
- 监控：持续监控数据库的性能，并根据观察到的性能增益或损失调整缓冲池大小。
5. 结论
innodb_buffer_pool_size 变量无疑是 MySQL 性能调优的关键。 达到正确的平衡并向缓冲池分配最佳的内存量可以极大地提高数据库性能。
通过仔细的计算、监控和微调，您可以确保您的 MySQL 实例有效地利用其资源，提供响应灵敏且高性能的数据库体验。
实验和持续评估将指导您找到与您的特定工作负载和服务器资源相协调的 innodb_buffer_pool_size 的最佳位置，使 MySQL 成为您的应用程序的真正动力源。
致力于性能优化，考虑采用整体方法，不仅解决缓冲池问题，还解决索引、查询优化、硬件增强和战略维护实践的问题。
借助经过充分优化的 MySQL 数据库，您的应用程序可以高效扩展和执行，满足现代数据密集型环境的需求。
原文地址：https://haydenjames.io/innodb_buffer_pool_size-mysql-performance/
