mysql - 为 MySQL 性能更改内存 - MySQLTuner

Question

我最近遇到了系统与 MySQL(从技术上讲是 MariaDB)的连接耗尽的问题。

我已将 max_connections 设置为 250(从 151 增加)。但我对如何分配 RAM 感到困惑。该机器有 32GB 的 RAM，如果我正确地从 mysqltuner 读取结果......我只允许使用最多 1GB 的内存。但是在 250 个连接 * 2.8M/线程时，它应该只能达到 700M + 全局 328M？

看起来我们已经达到了 755M 的峰值。但是，由于剩下所有这些额外的内存，我是否应该打开一些东西让 MariaDB 喘口气？

我没看错吗？

这台机器兼作 apache 和数据库服务器。即使在全速运行时，我也很少看到机器使用超过 3 或 4GB 的总系统 RAM

我运行了 mysqltuner，这是性能结果:

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 34d 1h 32m 42s (74M q [25.213 qps], 26M conn, TX: 46G, RX: 42G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.5G
[--] Max MySQL memory    : 1.0G
[--] Other process memory: 647.4M
[--] Total buffers: 328.0M global + 2.8M per thread (250 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 755.5M (2.34% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (3.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/74M)
[OK] Highest usage of available connections: 60% (152/250)
[OK] Aborted connections: 0.00%  (2/26423553)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 45.7% (21M cached / 47M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (541 temp sorts / 32K sorts)
[!!] Joins performed without indexes: 24537
[OK] Temporary tables created on disk: 1% (644 on disk / 51K total)
[OK] Thread cache hit rate: 98% (383K created / 26M connections)
[!!] Table cache hit rate: 0% (120 open / 36K opened)
[OK] Open file limit used: 0% (24/4K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)

Answer 1

您必须了解 MySQLtuner 对“最大 MySQL 内存”的计算完全是胡说八道。

它基于理论上的最大值 — 这只有在您最大化 max_connections 时才有可能，然后每个连接都在 恰好 的同一时刻运行查询，并且每个连接这些查询使用最大可能的排序缓冲区、读取缓冲区和连接缓冲区。实际上，这永远不会发生在真实服务器中。

当我在我支持的大多数生产数据库服务器上运行 MySQLTuner 时，“最大 MySQL 内存”被报告为比服务器上的实际物理 RAM 大数百倍。这不是问题，因为它是理论上的最大值，实际上永远不会发生。

如果您在数据库服务器上运行 SHOW PROCESSLIST，即使所有 150 或 250 个线程都已连接，您也只会看到 6-8 个线程在运行一个查询，而大多数其他线程都在一个“ sleep ”状态。

这就像您使用 ssh 登录到服务器并且您的终端在 shell 提示符下就绪。你在运行任何命令吗？不，您的 shell 处于空闲状态。但您仍然连接。

MySQL也是如此。您的应用程序可能已连接到数据库，但您的应用程序尚未运行查询。即使它确实运行查询，它也可能不会使用允许的最大资源。然后它将快速完成并再次将连接返回到空闲状态。

在任何给定时刻，连接的线程数可能很高，即使那些实际运行查询的连接数很小。您可以比较:

mysql> show global status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 510   |
| Threads_running   | 13    |
+-------------------+-------+

以上是繁忙的生产 MySQL 实例的典型数字。根据我的经验，连接的线程与运行的线程的比例在 10:1 到 100:1 之间。

但是 MySQLTuner 的计算并不现实——它们假设连接的线程与运行的线程的比率是 1:1。

鉴于此，您分配给 MySQL 的内存与您允许的 max_connections 无关。

您可能会发现增加一些调整选项有助于提高性能，但它更多地与您的数据大小和您针对该数据运行的查询类型有关。

我推荐阅读 https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

或者如果您想深入研究，请阅读:High Performance MySQL, 3rd Edition