高并发下PHP+MariaDB查询耗时陡增应用挂起问题咨询

问题概要 #

• 运行环境：PHP 7.3.19 + MariaDB 10.4.13 社区版，Linux系统部署
• 故障表现：20~25并发时查询正常，并发超30后查询耗时从0.18s飙升至21s以上，并发越高耗时越长，最终应用无响应，业务预期支撑500并发
• 前置操作：已为所有高频查询字段创建索引

参考慢日志 #

# 低并发正常查询
# Time: 220601  7:42:44
# User@Host: root[root] @ WIN-MN0PM3TE06L [10.15.51.252]
# Thread_id: 41274  Schema: mahabhumi_prod8  QC_hit: No
# Query_time: 0.188844  Lock_time: 0.000154  Rows_sent: 0  Rows_examined: 51848
# Rows_affected: 0  Bytes_sent: 10337
SET timestamp=1654049564;
SELECT * FROM (`kcc_requests`) WHERE `aadhar_num` =  'XXXXX';

# 高并发异常查询
# Time: 220601 14:12:13
# User@Host: root[root] @ WIN-MUQCJ3KLLE2 [10.15.51.250]
# Thread_id: 81466  Schema: mahabhumi_prod8  QC_hit: No
# Query_time: 21.017460  Lock_time: 0.000071  Rows_sent: 0  Rows_examined: 52692
# Rows_affected: 0  Bytes_sent: 10337
SET timestamp=1654072933;
SELECT * FROM (`kcc_requests`) WHERE `aadhar_num` =  'YYYYY';

根因判断 #

从慢日志特征可直接排除锁阻塞问题：两次查询Lock_time均低于0.2ms，无锁等待表现。低并发下同类型查询扫描5万行左右耗时仅0.18s，高并发下扫描行数无明显增长但耗时飙升至21s，本质是数据库资源（连接、内存、IO）被打满后请求排队等待，而非SQL索引缺失导致。

调优操作 #

1. MariaDB参数调整 #

修改my.cnf配置文件，按如下规则调整参数，修改完成后重启MariaDB服务生效：

• 连接数配置（适配500并发需求）

  # 最大连接数设置为预期并发的1.2倍，预留20%容量给运维操作
  max_connections = 600
  # 连接等待队列长度，避免高并发下连接直接被拒绝
  back_log = 300
  

• 内存配置（解决高并发下内存不足频繁刷磁盘IO的核心问题）

  # InnoDB缓冲池，数据库单独部署设为物理内存的70%，和PHP同机部署设为物理内存的50%
  # 例：16G内存同机部署设为8G，32G内存独立部署设为22G
  innodb_buffer_pool_size = 8G
  # 缓冲池拆分为多个实例，减少并发内存锁竞争，值与缓冲池GB数对应即可
  innodb_buffer_pool_instances = 8
  # 关闭查询缓存，MariaDB 10.4的查询缓存存在严重全局锁竞争，慢日志也显示所有查询QC_hit为No，开着无收益反而拖性能
  query_cache_type = 0
  query_cache_size = 0
  # 临时表内存阈值，避免高并发下大量临时表落盘
  tmp_table_size = 64M
  max_heap_table_size = 64M
  # 会话级缓冲区不要设置过大，每个连接独占该部分内存，过大会快速占满内存
  sort_buffer_size = 2M
  join_buffer_size = 2M
  read_buffer_size = 1M
  read_rnd_buffer_size = 1M
  

• InnoDB并发与IO配置

  # 内部并发线程数设置为CPU核心数的2倍，例：8核CPU设为16
  innodb_thread_concurrency = 16
  # 开启独立表空间，减少共享表空间IO竞争
  innodb_file_per_table = 1
  # 事务日志大小，减少刷盘频率
  innodb_log_file_size = 2G
  # 非金融级强一致场景设为2，性能可提升3~5倍，宕机最多丢失1秒内事务数据，可根据业务容忍度选择
  innodb_flush_log_at_trx_commit = 2
  

2. PHP侧配置调整 #

• 禁用数据库短连接，改用持久连接：短连接每次请求新建、销毁连接的开销在高并发下会被放大数十倍。mysqli扩展连接时加MYSQLI_CLIENT_PERSISTENT参数，PDO连接时设置PDO::ATTR_PERSISTENT => true。
• 调整PHP-FPM进程配置：pm.max_children设置为CPU核心数的20_{30倍，例：8核机器设为160}240，不要设置过大避免内存溢出，防止PHP请求本身排队拖慢整体响应。
• 替换SELECT *写法：当前查询单次返回10KB数据，高并发下大量无用字段传输会占满带宽，只查询业务实际需要的字段即可。

3. 有效性校验与架构优化 #

• 索引有效性校验：执行EXPLAIN SELECT * FROM kcc_requests WHERE aadhar_num = 'test';，确认输出的key字段显示为aadhar_num对应的索引名；若显示为NULL，检查字段类型是否匹配——aadhar_num为字符串类型时，查询传入数字值会触发隐式类型转换导致索引失效。
• 资源拆分：如果当前PHP和MariaDB部署在同一台服务器，优先拆分到独立服务器部署，避免CPU、内存、IO资源互相抢占。
• 缓存层加持：给aadhar_num维度的查询加Redis缓存，相同查询1~5分钟内重复请求直接读缓存，可承接90%以上的高频查询压力。

效果验证 #

调整完成后逐步加压到500并发，观察查询耗时是否稳定在0.2s以内；同时用top、iostat命令监控服务器状态，CPU使用率不超过80%、IO等待占比不超过10%即为配置生效。

内容的提问来源于stack exchange，提问作者EzyAccounting India