马来西亚服务器上的MySQL如何优化内存,避免缓存配置挤占系统资源?

发布人:A5数据发布时间:2026-11-13 09:20阅读量:51

在马来西亚服务器上优化 MySQL 内存,核心不是把 innodb_buffer_pool_size 调到物理内存的某个固定比例,而是先给操作系统、Web 服务、应用进程、连接峰值和临时操作留出预算,再用 p95 延迟、吞吐量、swap、CPU 与 I/O 指标验证调整是否有效。

缓存配置挤占系统资源,通常不是单个参数过大,而是多个“看起来不会立即占满内存”的上限同时被放大:InnoDB 缓冲池过大、max_connections 过高、每连接缓冲区被频繁使用、临时表内存上限过高,以及应用连接池和应用缓存没有纳入同一份内存预算。合理方案应当让热点数据尽量留在内存中,同时保证高峰期不会触发持续换页或 OOM。

A5idc数据提供马来西亚吉隆坡物理服务器,覆盖不同CPU、内存、磁盘与国际线路组合,可为MySQL数据库、业务后台和接口服务提供独立硬件资源。针对缓存、连接并发与数据读写并行的场景,产品可结合大内存、SSD或NVMe存储及相应网络配置,形成更贴合业务负载的部署基础。

先把目标定义成可测的性能问题

优化前需要先确定目标。对于同一组业务请求,至少同时观察以下指标:

指标关注内容用途
p50、p95、p99 延迟平均响应之外的尾延迟判断是否出现排队、换页或 I/O 抖动
吞吐量QPS、TPS 或每秒完成的事务数判断缓存调整是否真正提升处理能力
并发量Threads_running、应用连接池等待数判断连接数是否成为资源放大器
CPUuser、system、iowait区分计算瓶颈、内核等待和存储等待
内存available、swap、内存压力判断是否发生资源挤压
I/OIOPS、吞吐、await、设备利用率判断缓冲池不足或临时表落盘
SQL 工作量rows examined、慢查询、临时表判断问题来自 SQL 和索引,还是来自内存

优化目标不应简单写成“内存利用率达到 80%”或“缓冲池命中率达到 99%”。Linux 会将空闲内存用于文件缓存,MySQL 也会逐渐填充 InnoDB 缓冲池,因此“已使用内存较高”并不等于内存不足。更关键的是高峰时是否存在持续 swap、内存压力、延迟尾部上升和 I/O 等待。

吞吐量、延迟与并发之间还存在直接关系:

并发中的请求数 ≈ 吞吐量(请求/秒)× 平均延迟(秒)

例如,平均吞吐量为 450 请求/秒、平均延迟为 0.2 秒时,系统中大约同时存在 90 个未完成请求。如果平均延迟升到 0.8 秒,即使吞吐量没有变化,未完成请求数量也可能接近 360 个,应用连接池和 MySQL 活跃连接都会受到影响。这里的并发请求不等同于 Threads_running,但可以帮助解释为什么延迟升高后内存压力会进一步放大。

马来西亚服务器还需要单独测量网络因素。如果应用和数据库不在同一台服务器、同一内网或同一地域,应用请求总延迟还包括连接池等待、网络往返、结果传输和数据库执行时间。此时,MySQL 执行时间没有明显上升,但接口 p95 变慢,扩大缓冲池通常不会解决问题。

指标如何对应到内存问题

延迟和吞吐量要结合观察

单独看平均延迟容易掩盖问题。内存紧张时,部分请求可能因为访问了被换出的页面而突然变慢,平均值变化不大,但 p95 和 p99 会明显升高。

建议在调整前后固定以下条件:

  • 使用相同的 SQL 组合、数据量和请求比例。
  • 先预热缓冲池,再开始正式采样。
  • 至少覆盖一个业务高峰,或连续观察 15 至 30 分钟。
  • 同时记录应用请求延迟和数据库执行延迟。
  • 调整期间不要同时更换索引、存储类型和应用代码,否则无法判断效果来源。

如果吞吐量提高、p95 降低、swap 保持为零,且磁盘读取没有异常增加,说明调整方向可能有效。如果吞吐量下降但缓冲池命中率上升,可能是连接并发、CPU 或锁等待成为新的瓶颈。

内存和 swap 是优先级更高的信号

在 Linux 服务器上,可以先使用以下命令观察基础资源:

free -h
vmstat 1
iostat -xz 1
cat /proc/pressure/memory

free -h 中应重点查看 available,而不是只看 free。vmstat 1 中的 si 和 so 分别表示换入、换出活动;对于延迟敏感的数据库,业务高峰期持续出现非零换入或换出通常需要优先处理。iostat -xz 1 中的 await 和 %util 可以辅助判断存储是否已经饱和。iostat 需要服务器已安装相应的系统工具。

/proc/pressure/memory 提供了 Linux 内存压力信息。如果 some 或 full 在高峰期间持续增加,说明进程因为内存回收而等待,即使 free 命令显示仍有少量可用内存,也不能简单认为资源充足。

InnoDB 缓冲池命中率不能单独决定配置

可以查看以下状态:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_wait_free',
  'Innodb_buffer_pool_pages_data',
  'Innodb_buffer_pool_pages_dirty'
);

Innodb_buffer_pool_read_requests 是逻辑读请求累计值,Innodb_buffer_pool_reads 是需要从存储读取的累计值。两次采样之间的近似逻辑命中率为:

1 - 两次采样期间的 Innodb_buffer_pool_reads 增量 ÷ Innodb_buffer_pool_read_requests 增量

必须使用一段时间内的增量,而不是直接拿服务启动以来的累计值计算。累计值可能掩盖当前数据集变化。

如果缓冲池命中率较高,但 CPU 已经饱和、rows examined 很大或锁等待严重,继续增加内存不会改善慢查询。相反,如果 SQL 访问路径合理、磁盘读取频繁、内存仍有稳定余量,适当扩大缓冲池才有意义。

影响内存的变量不只有缓冲池

InnoDB 缓冲池是主要项目,但不是全部内存

innodb_buffer_pool_size 主要用于缓存 InnoDB 数据页和索引页,是数据库内存规划中最重要的参数。但服务器还需要为以下项目预留空间:

  • InnoDB 数据字典、锁、后台线程和其他内部结构。
  • redo log 缓冲区以及事务处理所需的内存。
  • Performance Schema 的监控开销。
  • 表定义缓存和打开表缓存。
  • 复制、备份、监控代理和日志处理进程。
  • Web 服务、应用运行时和应用层缓存。

MySQL 8.0 已经移除传统 Query Cache,不应在新版本中寻找 query_cache_size 作为主要优化手段。应用层缓存也不能视为“免费缓存”,如果它与 MySQL 部署在同一台马来西亚服务器上,也必须从总内存预算中扣除。

连接数会放大每连接内存

以下变量与连接或执行操作有关:

参数内存行为常见风险
max_connections允许的最大连接数,不代表启动时一次性分配全部内存上限过高时,异常流量可能同时创建大量会话
sort_buffer_size排序操作可能按连接使用全局调大后,复杂排序会产生内存峰值
join_buffer_size某些连接操作可能使用不能替代索引,多个并发连接会放大开销
read_buffer_size顺序读取操作可能使用对所有连接统一放大通常收益有限
read_rnd_buffer_size排序后读取等操作可能使用高并发下容易增加瞬时内存
tmp_table_size内部临时表的内存上限之一聚合、排序和分组集中出现时会形成峰值
max_heap_table_sizeMEMORY 表及部分内部临时表的上限数值过大时,单个会话可能占用过多内存

这些参数不是简单的“参数值乘以 max_connections”就能得到实际占用,因为许多缓冲区只有在特定操作发生时才分配。但是进行容量规划时,不能假设所有连接都只执行简单查询。应使用业务高峰期的 Threads_running、慢查询类型和临时表比例估算峰值。

max_connections 更像安全上限,而不是性能目标。如果应用有 5 个实例,每个实例连接池上限为 40,还要加上管理连接、后台任务和监控连接,那么 MySQL 需要覆盖的是总池容量,而不是某一台应用服务器的连接数。盲目把 max_connections 设置为 500 或 1000,通常只会推迟连接失败的时间,并不会提高数据库吞吐。

表缓存和临时表缓存应按证据调整

table_open_cache 和 table_definition_cache 缓存的是表句柄和表定义,不是数据页。它们过小可能导致频繁打开表,过大则会消耗内存和文件描述符。可以观察:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Open_tables',
  'Opened_tables',
  'Table_open_cache_hits',
  'Table_open_cache_misses',
  'Table_open_cache_overflows'
);

只有在表缓存未命中、溢出与打开表操作确实较多时,才有理由逐步扩大相关参数。修改前还应检查系统文件描述符限制,避免缓存变大后触发文件打开失败。

临时表落盘也不能直接等同于“临时表内存太小”。复杂的 GROUP BY、ORDER BY、聚合、宽字段排序和缺少合适索引,都可能产生大量临时表。先查看执行计划和 rows examined,再决定是否适度调整临时表上限。

用内存预算替代固定比例

可采用下面的估算方式:

可分配给 InnoDB 缓冲池的上限
≈ 物理内存
- 操作系统、监控和日志进程
- Web 服务与应用进程
- MySQL 非缓冲池内存
- 峰值连接与临时操作预留
- 安全余量

以一台 8 GiB、同时运行 Web 服务和 MySQL 的马来西亚服务器为例,以下是一组用于说明方法的容量分配:

内存项目规划值说明
操作系统、监控、日志、Web 服务1.5 GiB同机部署时不能归零
MySQL 非缓冲池内存0.6 GiB包括内部结构、表缓存和监控开销
峰值连接与临时操作1.2 GiB按高峰连接和复杂 SQL 预留
安全余量0.7 GiB应对流量波动和后台任务
InnoDB 缓冲池4.0 GiB作为初始配置,而非永久固定值
合计8.0 GiB按 GiB 口径计算

如果服务器只运行数据库,应用和 Web 服务的预留可以减少,缓冲池可能有更大的空间;如果服务器还运行搜索、队列、备份压缩或应用缓存,缓冲池比例就应相应降低。所谓“专用数据库服务器可以把大部分内存给缓冲池”也不是无条件成立,复制、备份、临时查询和数据增长都需要余量。

对于连接内存,可以用较保守的方式估算:

峰值连接内存预留 ≈ 峰值活跃连接数 × 复杂操作的平均每连接内存

例如观察到高峰期有 120 个活跃连接,并以每个连接约 8 MiB 的操作内存做初始规划,预留约 960 MiB。这个 8 MiB 只是容量估算系数,不代表每个连接始终实际占用该数值。后续应结合操作系统内存曲线和 Performance Schema 进行修正。

一个缓存过大反而变慢的对照

下面是一组模拟对照数据,用于说明判断过程,不代表某个具体马来西亚节点的实测结果。两组测试使用相同的 SQL 和请求比例,应用与数据库位于同一地域。

一个缓存过大反而变慢的对照配图

项目配置 A:缓存和连接上限偏大配置 B:按预算控制
服务器内存8 GiB8 GiB
innodb_buffer_pool_size5.5 GiB4 GiB
max_connections300150
tmp_table_size128 MiB32 MiB
max_heap_table_size128 MiB32 MiB
高峰 Max_used_connections180102
峰值可用内存约 0.2 GiB约 1.4 GiB
swap 使用约 1.2 GiB0
磁盘 iowait14%3%
核心请求 p95820 ms240 ms
吞吐量约 410 QPS约 455 QPS

配置 A 的问题不是缓冲池完全没有效果,而是它和高连接上限、较大的临时表上限叠加后,挤压了操作系统和连接操作的空间。内存不足时,部分 MySQL 页面或系统页可能被换出;后续访问这些页面又会触发磁盘读取,导致 p95 延迟上升。此时继续扩大缓冲池,反而可能增加换页风险。

配置 B 的缓冲池更小,但连接与临时操作有明确边界,系统保留了更多可用内存。若同一负载下 Innodb_buffer_pool_reads 没有明显恶化,且磁盘等待下降,那么较小的缓冲池可能带来更稳定的尾延迟。

反过来,如果配置 B 出现以下情况,就不能简单认为“缓冲池越小越好”:

  • 缓冲池物理读取量明显上升。
  • 存储设备 await 持续增加。
  • SQL 和索引已经合理,但热点数据无法容纳。
  • 系统仍有足够可用内存且没有 swap。
  • p95 延迟随着缓冲池缩小而稳定变差。

这时可以逐步扩大缓冲池,并在每次调整后重新观察,而不是一次增加数 GiB。

调整前先建立基线

先确认 MySQL 版本和当前配置:

SELECT VERSION();

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_log_buffer_size',
  'max_connections',
  'tmp_table_size',
  'max_heap_table_size',
  'sort_buffer_size',
  'join_buffer_size',
  'read_buffer_size',
  'read_rnd_buffer_size',
  'table_open_cache',
  'table_definition_cache',
  'performance_schema'
);

然后记录连接、临时表和缓冲池状态:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections',
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_wait_free',
  'Opened_tables',
  'Table_open_cache_hits',
  'Table_open_cache_misses',
  'Table_open_cache_overflows'
);

如果启用了 Performance Schema,还可以查看已被记录的内存项目:

SELECT
  EVENT_NAME,
  CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;

Performance Schema 只能显示已经被相应工具记录的内存,结果为空或不完整时,不能据此认定 MySQL 没有其他内存占用,仍需要结合操作系统指标判断。

在同一高峰窗口内至少采样两次,间隔可以是 60 秒。对于累计计数器,使用前后差值;对于 Threads_connected、CPU、available 和 swap,则记录时间序列。这样可以区分“服务启动以来的历史数据”和“当前负载下的真实表现”。

一个保守的配置起点

下面的配置只适合作为 8 GiB、同机运行应用与数据库场景的示例,不应直接复制到所有服务器:

[mysqld]
# 仅作为容量规划示例
innodb_buffer_pool_size=4G
max_connections=150
tmp_table_size=32M
max_heap_table_size=32M
table_open_cache=2000
table_definition_cache=1000

使用这组参数前,需要先确认:

  • 应用所有实例的连接池总和没有超过 MySQL 的连接上限。
  • 高峰期管理连接、后台任务和备份连接也有余量。
  • 32 MiB 的临时表上限不会导致关键查询大量落盘。
  • 表数量和文件描述符限制能够支持相应的表缓存。
  • 服务器确实有 8 GiB 左右的可用物理内存,而不是受到容器或虚拟化限制。

如果运行的是 MySQL 8.0,还可以查看 TempTable 相关变量:

SHOW VARIABLES LIKE 'temptable%';

不同 MySQL 版本对内部临时表引擎的处理方式存在差异,因此不应只调整 tmp_table_size 和 max_heap_table_size,却忽略版本对应的 TempTable 内存上限。

修改配置文件前应备份当前配置,并记录修改前的 SHOW VARIABLES 和 SHOW GLOBAL STATUS 结果。实际加载的 option 文件路径不要凭经验猜测,可以使用:

mysqld --verbose --help

查看默认配置文件搜索顺序,再结合发行版服务配置确认实际使用的文件。配置文件变更本身通常不修改数据,但重启 MySQL 会中断现有连接,必须安排维护窗口,并准备好原配置、数据库备份或快照。回滚时恢复原配置,再按照实际核对出的服务名重启,不要直接套用其他发行版的服务名。

部分参数可以运行时修改,但运行时修改与配置文件修改并不完全等价:

  • SET GLOBAL max_connections 主要影响后续新连接。
  • SET GLOBAL tmp_table_size 和 SET GLOBAL max_heap_table_size 通常影响新会话的默认值,已有会话可能仍保留旧的会话值。
  • 缓冲池动态调整是否可用、调整过程和影响范围与 MySQL 版本有关,应先确认版本和业务窗口。
  • 运行时验证通过后,再将参数持久化到实际配置文件,避免重启后恢复旧值。

结果解释:不同现象对应不同动作

观测现象更可能的原因处理方向
swap 持续换入换出,内存压力上升,p95 同时变差总内存预算超过峰值需求先降低缓冲池或连接、临时表上限,恢复余量
无 swap,但物理读很多,磁盘 await 高,SQL 访问路径合理热点数据无法留在缓冲池在有余量时逐步扩大缓冲池,或增加内存
CPU 高、rows examined 高,但内存和 I/O 正常慢查询、索引缺失或扫描范围过大优先优化 SQL 和索引,不要先加缓存
Created_tmp_disk_tables 比例高,但内存仍充足查询产生大量排序、分组或宽临时结果检查执行计划,必要时小幅增加临时表上限
Threads_running 长时间高,CPU 或磁盘已接近饱和并发查询过多或单条查询执行时间过长限制连接池并发,优化慢 SQL,避免继续提高连接上限
Max_used_connections 接近 max_connections,但 CPU 和内存尚有余量连接上限或应用池容量不足核对所有应用实例的连接总量后再增加,并保留内存预算
MySQL 执行时间稳定,但接口 p95 变差网络往返、连接池等待或应用处理占比上升分离测量网络、池等待和 SQL 执行时间
表缓存未命中和溢出明显增加表缓存偏小或表数量变化在文件描述符允许时逐步增加表缓存

使用执行计划时,应特别关注实际扫描行数、访问索引、排序和临时表。MySQL 8.0 的 EXPLAIN ANALYZE 会执行查询本身,适合在低峰、只读环境或经过确认的测试数据上使用,不能在不了解副作用的情况下对写入型语句直接执行。

例如,针对一条查询,可以先查看执行计划:

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 42
  AND created_at >= '2025-01-01'
ORDER BY id DESC
LIMIT 50;

如果查询扫描了大量不需要的行,即使扩大缓冲池,也只是让低效访问路径在内存中运行,不能从根本上减少 CPU 和锁竞争。

决策边界:什么时候扩大,什么时候收缩

适合扩大缓冲池的情况

同时满足以下条件时,可以考虑小幅扩大:

  • 高峰期没有持续 swap 或明显内存压力。
  • 操作系统仍有稳定的 available 余量。
  • SQL 和索引已经经过基本检查。
  • 缓冲池物理读取量较高,且存储等待影响延迟。
  • 扩大后 p95、p99 和 I/O 指标有可重复改善。
  • 应用缓存、备份和监控进程没有明显抢占内存。

扩大时建议分阶段进行。例如在小型服务器上每次增加几百 MiB 到 1 GiB,观察一个完整业务高峰后再决定下一步,而不是一次把缓冲池提升到物理内存的大部分。

适合收缩缓存或连接上限的情况

出现以下情况时,优先释放内存:

  • swap 在业务高峰持续增长。
  • vmstat 的换入换出持续非零。
  • 内存压力指标上升,并伴随 p95、p99 抖动。
  • MySQL 进程被系统 OOM 终止,或出现连接突然中断。
  • 高连接数并没有带来更高吞吐,反而让 CPU、I/O 和锁等待恶化。
  • 大量临时操作只在极少数查询中出现,却为所有会话设置了很高的上限。

如果需要快速止损,可以先恢复此前稳定的连接池和临时表配置,再分阶段降低 innodb_buffer_pool_size。每次改变后都要验证 Innodb_buffer_pool_wait_free、物理读和延迟,避免为了释放内存而把工作集全部推向磁盘。

不应通过加缓存解决的情况

以下情况通常需要从 SQL、索引、并发或网络方向处理:

  • 过滤条件缺少有效索引。
  • 单条查询扫描数百万行,但最终只返回少量数据。
  • 连接池等待占比高,数据库执行时间并未增加。
  • CPU 已经饱和,增加连接只会产生更多上下文切换。
  • 磁盘设备本身 I/O 能力不足,缓冲池无法消除所有随机读。
  • 应用和数据库之间的地域网络延迟占总延迟大部分。

马来西亚服务器的地域因素应作为独立变量测量。可以分别记录应用到数据库的网络往返时间、连接池等待时间、SQL 执行时间和结果传输时间。只有数据库执行时间和物理读同时改善,才说明内存调整确实解决了数据库缓存问题。

复测条件与容量判断

容量判断不应以“缓冲池占用了多少内存”作为唯一标准,而应以固定负载下的稳定边界为准。可以按以下方式复测:

  1. 记录调整前的配置、版本、数据量、应用实例数和连接池总容量。
  2. 预热数据库,使测试不受首次加载数据页影响。
  3. 在相同 SQL 比例下运行至少 15 至 30 分钟,并覆盖高峰请求。
  4. 同时采集 p95、p99、QPS、Threads_running、Max_used_connections、available、swap、CPU、iowait 和缓冲池物理读。
  5. 每次只改变一组相关参数,例如先处理缓冲池,再处理连接池,最后评估临时表配置。
  6. 以“无持续 swap、延迟满足业务目标、吞吐没有下降、资源仍有余量”作为通过条件。

在共享部署的 8 GiB 服务器上,可以把高峰期保留约 15% 至 20% 的物理内存作为初始安全余量,但这不是所有业务的硬性标准。更可靠的方式是逐步增加负载:每次增加约 10% 至 20%,直到出现 p99 超标、swap、CPU 饱和、I/O 等待或连接池排队,再把上一个稳定负载点作为当前容量边界。

当数据量增长、热点数据集变化、应用实例增加,或 Max_used_connections 长期接近上限时,应重新进行预算和复测。只要高峰期仍有足够内存余量、SQL 访问路径合理且尾延迟稳定,就不必为了追求更高的缓存命中率继续扩大缓存;当资源边界被触及时,增加服务器内存、拆分应用与数据库或优化查询,往往比继续堆高缓存参数更可靠。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/9091.html

相关推荐