数据库优化 – 硬件与配置

在使用 Frappe 框架时，优化性能的一个重要部分就是优化数据库的性能。幸运的是，MariaDB/MySQL 的性能是一个被广泛理解的概念，有大量资源可以深入学习。Frappe 与许多基于相同数据库构建的其他 Web 应用并无不同，所以如果你知道如何为 MySQL 做优化，那你已经知道在使用 Frappe 框架时该怎么做了。

架构与硬件

我们建议将数据库服务器与应用服务器分开部署。以下是这样建议的一些原因：

  • 应用服务器经常会出现负载高峰和动态的内存使用模式。将它和数据库服务器放在一起会影响整体性能。
  • 数据库需要持续稳定的硬件资源才能表现良好，包括尽可能多的 RAM 和 IOPS。
  • 数据库和应用服务器通常受益于不同类型的硬件调优和配置。

CPU

我们建议至少从 2 个 vCPU 起步。vCPU 的数量决定了你的数据库服务器能执行多少”真正并行”的工作。你应该在数据库服务器上设置一些监控（参见下面的监控部分）。当你的利用率持续达到 60%，或长时间保持在 50% 以上时，你可能需要升级 vCPU 数量，以提供更多并行计算资源。

内存

从磁盘访问数据比从内存访问慢好几个数量级。数据库使用 RAM 作为”缓冲池”，充当磁盘上实际数据的缓存。一个调优良好、配置充足的数据库在超过 99% 的情况下都不会因为读取数据而访问磁盘。随着数据库增长，当你需要大量数据能够被非常快速地访问时，你可能需要考虑增加更多 RAM，并重新配置服务器以利用可用的 RAM。

我们建议至少从 8GB RAM 起步。

> 注意：建议数据库服务器不要依赖 SWAP。数据库本身已经实现了在有限内存下工作所需的所有逻辑，添加 swap 只是重复了这项工作。你应该保留少量 SWAP 内存作为安全保障，但将 swappiness 降到尽可能低的值。

磁盘与 IOPS

不要在磁盘存储和 IOPS 上省钱。考虑购买至少是实际使用量 3 倍的存储。你的数据库会随着时间自然增长，你不会想每次磁盘用完就升级。例如 AWS，额外的存储也会给你更多 IOPS。我们建议从大约 2000-3000 IOPS 起步，并监控使用情况，以判断是否需要更多还是可以减少。

至少从 50GB 磁盘起步，并考虑大约是你当前数据库大小的 3 倍。例如，如果你的数据库是 20 GB，那么考虑购买 60GB 存储的服务器或可挂载卷。

扩展资源

我们建议尽可能长时间地进行垂直扩展，大致来说就是垂直扩展到至少 64vCPU/128GB RAM。你可以利用监控设置中的洞察来决定需要升级哪个组件。请记住，大多数云服务商会将资源捆绑成”实例”，所以只升级系统的某一部分并不那么简单。

如果你认为已经达到了单台机器所能达到的极限，那么可以考虑设置数据库副本。Frappe 框架可以配置为将所有繁重的读取操作路由到数据库副本，以减轻主服务器的负载。

配置

数据库开箱即提供了数百个可调参数，用于调整数据库性能的不同方面。至少要把最重要的参数调好，这样才能最大化你的硬件能力。

缓冲池 – innodb-buffer-pool-size = 5G

你需要调整的最重要的参数是缓冲池大小。由于历史原因，它的默认配置也是最差的，所以如果不去配置它，你根本没有利用好你的硬件。

如前所述，缓冲池充当磁盘上数据的缓存。缓冲池大小的经验法则是：

“缓冲池大小应该能容纳你的工作数据集”

说起来简单，但很难量化。你的数据”工作集”是你每天都会访问的数据，是你希望非常快速访问、不能接受随机变慢的数据。让我们以 ERP 为例，来理解如何选择比较理想的缓冲池大小，进而确定 RAM 大小。

  1. 你的站点有 80GB 的数据库。
  2. 这个数据库是 4 年间积累生成的。
  3. 你认为去年数据库增长了 20GB，而在 ERP 中，你主要访问的是 1 年的数据，用于各种报表和验证。
  4. 所以起步时理想的缓冲池大小是 20GB。
  5. 理想情况下，缓冲池应占用约 60% 的 RAM。所以你应该选择 20 / 0.6 ≈ 33GB RAM。

你可以对缓冲池使用情况设置监控，以了解是否需要更多或更少。两个最重要的指标：

  1. 缓冲池命中率：如果超过 99%，那么很可能你不需要增加更多缓冲池大小。
  2. 利用率：如果你的利用率没有接近 95%-100%，那你肯定不需要增加更多缓冲池。

缓冲池缓存会随时间填满，因此你需要在运行几天后进行测量。你可以输入 SHOW ENGINE INNODB STATUS 来查看这些统计数据。这会输出大量内容，但以下是重要部分：

----------------------
BUFFER POOL AND MEMORY
----------------------
Buffer pool size   2790528
Free buffers       1594
Buffer pool hit rate 999 / 1000

其他参数

以下是我们在 Frappe 使用的配置。你可以从这开始，并调整某些参数。最重要的参数将在下面更详细地解释。

  • innodb-flush-method = O_DIRECT：绕过某些操作并执行“直接 IO”。
  • innodb-flush-log-at-trx-commit = 1：通过在每次提交时写入日志来提高系统的持久性。不要为了小幅性能提升而牺牲这种持久性。
  • innodb-file-per-table = 1：为每个表创建一个文件。
  • slow-query-log = 1 和 slow-query-log-file = /var/lib/mysql/mysql-slow.log：将运行时间过长的任何查询存储在日志文件中。这对于事后找出哪些查询应优化很有用。
  • query-cache-type = 0 和 query-cache-size = 0：这会禁用查询缓存，尽管名字如此，但现在普遍认为它对性能有害。
[mysqld]
# GENERAL #
default-storage-engine          = InnoDB

# MyISAM #
key-buffer-size                 = 32M
myisam-recover                  = FORCE,BACKUP

# SAFETY #
max-allowed-packet              = 256M
max-connect-errors              = 1000000
innodb                          = FORCE

# DATA STORAGE #
datadir                         = /var/lib/mysql/

# BINARY LOGGING #
log-bin                         = /var/lib/mysql/mysql-bin
log_bin_index                   = /var/lib/mysql/mysql-bin.index
expire-logs-days                = 14
sync-binlog                     = 1

# CACHES AND LIMITS #
tmp-table-size                  = 32M
max-heap-table-size             = 32M
query-cache-type                = 0
query-cache-size                = 0
max-connections                 = 500
thread-cache-size               = 50
open-files-limit                = 65535
table-definition-cache          = 4096
table-open-cache                = 10240
tmp-disk-table-size             = 5120M
max-statement-time              = 10800

# INNODB #
innodb-flush-method             = O_DIRECT
innodb-log-files-in-group       = 2
innodb-log-file-size            = 512M
innodb-flush-log-at-trx-commit  = 1
innodb-file-per-table           = 1
# NOTE: This has to be configured based on RAM. Consider ~60% of available RAM. This example assumes 8GB RAM.
innodb-buffer-pool-size         = 4.8G
innodb-file-format              = barracuda
innodb-large-prefix             = 1
innodb-old-blocks-time          = 5000
collation-server                = utf8mb4_unicode_ci
character-set-server            = utf8mb4
character-set-client-handshake  = FALSE
max_allowed_packet              = 512M

# LOGGING #
log-error                       = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes   = 0
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysql/mysql-slow.log

[mysql]
default-character-set           = utf8mb4

[mysqldump]
max_allowed_packet              = 512M

监控

Frappe 没有针对 MariaDB 的特定监控工具，我们建议设置可观测性工具，如 Prometheus 配合 Grafana 来制作仪表板。

  • 内置慢查询日志：查看慢查询被记录的频率，并使用 Percona toolkit 中的 pt-query-digest 等工具找出最常见的慢查询。

  • 使用以下导出器设置 Prometheus 和 Grafana：

    • Node Exporter – 提供整体系统统计信息。对于监控 CPU、RAM 和 I/O 很有用。
    • MySQLD Exporter – 从 MariaDB 的数据库引擎提供大量有用的指标。
    • PMM – Percona 的工具包，带有预构建的仪表板。

我们认为以下指标值得跟踪：

  1. CPU 使用率 – 持续接近 100% 使用率意味着你可能需要更多 CPU 能力来处理负载。
  2. RAM – 配置良好的数据库的 RAM 使用率会徘徊在 90% 左右。使用率接近 100% 意味着你可能会交换或导致系统崩溃。使用率显著低于 90% 意味着你没有将所有可用 RAM 用于缓冲池缓存。
  3. 磁盘 I/O – 一旦数据库服务器充分预热，磁盘读取应该最少。所有读取负载都将从缓冲池提供，只有更新需要写入磁盘。如果你看到大量磁盘 I/O，这可能意味着缓冲池大小不足或查询需要将临时文件写入磁盘。
  4. “InnoDB 行读取” – 读取次数突然增加可能意味着流量增加或引入了未优化的查询。
  5. “BP 未命中率” – 大部分情况下应小于 1%。如果很高，那么你的缓冲池大小可能不够。
  6. “Innodb 行锁等待负载” – 高锁等待负载意味着你有争用问题，多个工作进程试图写入相同资源但被阻塞。这个问题的典型解决方案是将工作排队而不是并行执行。也可能有未优化的查询导致锁被长时间持有。
  7. “InnoDB 缓冲池 LRU 子链变动” – 缓冲池将数据库页面列表维护为近似 LRU。逻辑备份等活动可能会将磁盘上的整个数据库读入缓冲池，导致最常用的页面被驱逐。理想情况下，在非工作时间进行备份以避免这种情况。