live-hub-py/docs/生产服务器高负载排查与优化报告.md

生产服务器高负载排查与优化报告

• 事件时间：2026-10-03（排查 12:33 ~ 12:54，索引优化当日落地）
• 服务器：47.114.114.225（主机别名 order+douyu_huya）
• 规格：4 vCPU / 7.1 GiB RAM / Ubuntu 24.04（nproc + free -h 实测，见 §1）
• 涉及容器：
  • 本项目：live-hub-py-douyu-login-1、live-hub-py-mysql-1
  • 工单平台：order_site-postgres-1、order_site-backend-1

本文合并了并行记录的两份原始文档（《排查分析报告》与《优化落地实证报告》）， 去掉了重复段落，并修正了互相矛盾的两处事实（服务器规格、负载基线）。 两份原始文档对同一台机器的规格记录不一致（一处写 8 核/16GB）， 本文以带原始命令输出的 4 vCPU / 7.1 GiB 为准。

---

0. 结论速览

两个互相独立的根因同时压在一台 4 核机器上，任何一个单独出现都不会打穿负载：

#	归属	根因	处理	状态
①	order_site	PostgreSQL LLVM JIT 对复杂聚合反复编译，单次 7.2s 全花在代码生成	ALTER SYSTEM SET jit = off; 运行时止血	已止血，未根治（§5）
②	live-hub-py	douyu_tasks 按归属客服筛选时跨表 JOIN + Using temporary; Using filesort	迁移 20261003_0040 冗余 account_assigned_to + 复合索引	已根治（单次 400~500ms → 5.7ms，87 倍）

核心数字：Postgres CPU 197.75% → 1.58%；MySQL CPU 111.45%（尖刺）→ 1.86%11%； 宿主机 load 17.09 → 9.52（JIT 止血后） → 7.68.8（索引优化后）。

---

1. 现场取证：宿主机与容器负载

# uptime
12:33:44 up 43 days, 14:12,  3 users,  load average: 17.09, 13.75, 11.38

# nproc & free -h
4 vCPU
Mem:  total 7.1Gi, used 4.1Gi, free 351Mi, buff/cache 4.1Gi, available 3.0Gi
Swap: total 4.0Gi, used 577Mi, free 3.4Gi

# docker stats --no-stream
CONTAINER ID   NAME                        CPU %     MEM USAGE / LIMIT     MEM %     NET I/O           BLOCK I/O
301d6a15a032   live-hub-py-douyu-login-1   90.53%    593.4MiB / 2GiB       28.97%    2.95GB / 234MB    116MB / 22.2MB
04111b7b40ae   live-hub-py-mysql-1         49.07%    895.6MiB / 2GiB       43.73%    532MB / 11.8GB    384GB / 21.8GB
d1831428e110   order_site-postgres-1       197.75%   1.708GiB / 2.5GiB     68.34%    2.88GB / 24.9GB   6.79GB / 9.84GB
0fc436d47135   order_site-backend-1        39.75%    289.2MiB / 1GiB       28.24%    28GB / 14.8GB     278MB / 130MB

结论：CPU 合计超过 376%，4 核已饱和。order_site-postgres-1 占大头（~200%）， 本项目 douyu-login 第二（~90%），mysql 第三（~50%）。

---

2. 根因一：PostgreSQL JIT 编译放大（order_site 侧）

慢查询位置：/home/order_site/apps/backend/src/repositories/worker-platform/work-order-query-repo.ts:463 函数 getWorkerOrderOverview(workerId)，结构为多段 UNION 的 CTE 后按状态 GROUP BY 聚合。

EXPLAIN ANALYZE 关键指标（Worker 7，名下 6895 单）：

JIT:
   Functions: 63
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 82.769 ms, Inlining 2197.205 ms, Optimization 2832.841 ms, Emission 2098.693 ms, Total 7211.508 ms
Execution Time: 8803.367 ms

• 默认（JIT 开）：8803 ms，其中 7211 ms（82%）全耗在 LLVM 代码生成与优化上；
• SET jit = off：8803 ms → 1174 ms（该接口实测 369 ms），省掉 7.2s CPU 运算。

次要因素：worker_base 把 visible_orders（几千条）与 work_orders（10.2 万条）做 Hash Join 时 引发 work_orders 的大范围 Index Scan。

---

3. 根因二：douyu_tasks 跨表文件排序（live-hub-py 侧）

慢查询：客服列表轮询 GET /api/douyu/tasks?handbook_scope=elite

SELECT douyu_tasks.id AS douyu_tasks_id, ...
FROM douyu_tasks
INNER JOIN accounts ON accounts.id = douyu_tasks.account_id
LEFT OUTER JOIN accounts AS accounts_1 ON accounts_1.id = douyu_tasks.account_id
WHERE accounts.assigned_to = 13
  AND (douyu_tasks.handbook_scope = 'elite'
       OR douyu_tasks.handbook_scope = 'legacy' AND douyu_tasks.task_type IN (...))
ORDER BY douyu_tasks.id DESC
LIMIT 100;

EXPLAIN 诊断：

id: 1, table: accounts,    type: ref, key: ix_accounts_assigned_to,     rows: 734,
      Extra: Using index; Using temporary; Using filesort
id: 1, table: douyu_tasks, type: ref, key: ix_douyu_tasks_account_id,   rows: 19,
      Extra: Using where

accounts 作驱动表筛出 734 个账号、关联出几万条任务，而最终要按 douyu_tasks.id DESC 排序 —— MySQL 只能把全部关联结果写进临时表做 filesort。24 万条任务中符合条件的 1.1 万条全量排序， 导致 MySQL 周期性冲到 111.45% CPU、累计 384GB 磁盘读，日志频繁报 Out of sort memory。

3.1 优化方案（迁移 20261003_0040）

web/backend/migrations/versions/20261003_0040_douyu_tasks_account_assigned_to.py

1. douyu_tasks 新增 account_assigned_to（外键关联 users.id）；

2. 历史数据回填：

   UPDATE douyu_tasks dt
   JOIN accounts a ON a.id = dt.account_id
   SET dt.account_assigned_to = a.assigned_to;
   

3. 建立覆盖排序的复合索引：

   • ix_douyu_tasks_assigned_scope_id (account_assigned_to, handbook_scope, id)
   • ix_douyu_tasks_account_assigned_to_id (account_assigned_to, id)

3.2 配套代码改造

1. 创建任务时自动注入：douyu_service.py 的 create_douyu_tasks() 带入账号当前的 assigned_to；

2. 账号改分配时级联更新：accounts.py 的 assign_account() 同步更新该账号下任务的 account_assigned_to；

3. 彻底消除跨表排序：douyu.py 的 _visible_tasks_query() 改为纯单表条件：

   # 优化前：需要 JOIN Account 表
   # query = query.join(Account).filter(Account.assigned_to == current.id)
   # 优化后：纯单表条件，走复合索引
   query = query.filter(DouyuTask.account_assigned_to == current.id)
   

3.3 上线与实测

• 生产环境共回填 239,118 条历史任务；alembic_version 推进到 20261003_0040；
• /api/health 返回 {"status":"ok"}；WebSocket 正常，斗鱼批量赠送精英令 / 改名 / 改密任务并发执行无异常。

指标	优化前	优化后（生产实测）	改善
执行计划	Using temporary; Using filesort	Backward index scan	filesort 彻底消除
索引命中	仅 account_id，跨表无法索引排序	ix_douyu_tasks_assigned_scope_id (len 71)	直接命中最优复合索引
单次查询耗时	400 ~ 500 ms	5.7 ms	~87 倍
Out of sort memory	频繁	0 次	根治
MySQL 容器 CPU	尖刺 111.45%	平稳 1.86% ~ 11%	降低 90%+
系统平均负载	峰值 12.8 ~ 14.5	回落至 7.6 ~ 8.8	显著稳定

---

4. 为什么负载「忽高忽低」：四个周期脉冲重叠

对宿主机连续采样（12:50 ~ 12:54，2 秒间隔）：

12:51:51  Postgres 158%  MySQL 49%   Node 35%   Python 11%
12:51:55  Postgres 171%  MySQL 65%   Node 22%   Python 72%
12:51:59  Postgres 138%  MySQL 111%  Node 102%  Python 36%
12:52:04  Postgres 155%  MySQL 104%  Node 104%  Python 56%
12:52:09  Postgres 211%  MySQL 66%   Node 76%   Python 4%
12:52:33  Postgres 活跃查询归零，CPU 骤降

脉冲源	周期	触发动作	瞬间峰值
① MySQL 任务全表排序	35 s	客服工作台轮询 /api/douyu/tasks，24 万条里做 filesort	4% → 111%
② PostgreSQL 聚合风暴	515 s	getWorkOrderStatistics（10 万工单 × 28 万订单 LEFT JOIN 全量 SUM）、countWorkerAcceptedOrders、getWorkerOrderOverview	1.5% → 211%
③ Node 定时同步与派单	20 s	pdd_order_sync 拉单派发；每 60 s 超时检测与押金解冻	22% → 104%
④ Python 充值/绑定波峰	任务驱动	供应商直充下单、5 秒一轮的绑定状态轮询、代理池并发探测	4% → 73%

结论：关掉 JIT 后 Postgres 不再陷入 1020 秒的持续假死（这是 load 从 17 降下来的原因）； 但四个周期任务缺少防抖 / 短缓存 / 关键索引，一旦某一秒同时触发，4 核仍会出现 12 秒的 100% 尖刺 —— 这就是「忽高忽低」的机理。① 已在 §3 根治，②③ 见 §5。

---

5. 遗留与后续

#	事项	归属	说明
1	getWorkerOrderOverview 的 CTE 重构	order_site	UNION → UNION ALL，减少大集合 Hash 去重；合并 worker_order_cancel_requests / worker_order_feedbacks 的 EXISTS 检查
2	打手端概览加短缓存	order_site	5~10 秒的内存缓存（LRU / Redis / Node Map），避免切页与轮询直穿数据库
3	jit = off 只是止血	order_site	后续复测显示该容器 CPU 又回到 100%~222%（见 迁移方案 §9）。SQL 重构与缓存不做，问题会复发
4	单机混部缺硬隔离	运维	4 核上跑 2 个数据库（PG 16 + MySQL 8.4）与 2 套高并发业务。应在 docker-compose.yml 为各服务配 cpus 限额，防止单个慢查询占满宿主机

「严禁在生产上手工热修业务代码」的推进原则见 迁移方案-live-hub-py独立部署.md： 运行时参数属于紧急止血，源码改动一律走分支 → 本地验证 → 正规部署。

---

6. 时间线

时刻 (CST)	事件
12:33	采样确认 4 核饱和、load 17.09；定位 PG JIT 与 MySQL filesort
12:42	执行 ALTER SYSTEM SET jit = off; SELECT pg_reload_conf();
12:43	Postgres 197.75% → 1.58%，MySQL 49% → 4.46%，load 17.09 → 9.52
12:50 ~ 12:54	连续采样，拆出四个脉冲周期（§4）
当日	落地迁移 20261003_0040（回填 23.9 万行 + 复合索引）并发布，单次查询 87 倍提速