|
|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?用户注册
×
一、具体的问题
一套三副本 AlwaysOn:同城两台同步提交,异地一台异步提交兼只读报表,SQL Server 2019 CU19。
毛病很有规律:每天 22:00 日终报表开跑,23:30 索引维护跟上,异地副本的重做队列就从几百 MB 涨到三十多 GB,报表查出来的数据比主库晚四十分钟以上,第二天早上八点又自己追平。运维最初的判断是"专线带宽不够",为此把带宽从 100M 升到 300M,情况一点没变。
第一次上手我只做了一件很朴素的事:把两个队列分开看。主库上 log_send_queue_size 始终是 0,log_send_rate 稳在四千多 KB/s,说明日志早就全发过去了,跨机房根本没堵。真正堆着的是 redo_queue_size,三小时后仍有一千多万 KB 没重做。
一句话结论:卡在重做,不在传输。那次扩容等于买了个安慰。
再往下还有两条:异地副本上一条报表查询跑满 26 分钟,期间重做线程被压着不动,等待链上能查到是谁挡的;同城那台当晚做索引重建,一次性写了 42GB 日志,而且是一个事务,副本只能从头滚到尾。
这篇把这三层拆开:怎么用队列和速率判断瓶颈在哪一段、重做为什么会被一条只读查询卡住、以及索引维护这类日志突发怎么改才不给副本添货。
二、核心原理
1. 两个队列代表两段完全不同的链路
发送队列(log_send_queue_size)是主库上"还没发出去"的日志量,单位 KB,卡它的是网络、发送端和主库日志生成速度。重做队列(redo_queue_size)是辅助副本上"已收到、已固化、还没重放"的日志量,卡它的是副本的重做线程、磁盘 IO 和 CPU。
只看一个必然误判:发送队列为 0 而重做队列几十 GB,说明链路很好、副本处理不过来;发送队列一路涨而重做队列很浅,才该去查网络。用 redo_queue_size 除以 redo_rate 能粗算"追平还要多久",但 redo_rate 是自引擎启动以来按活跃时间算的平均值,重做断续运行时它比性能计数器 Database Replica:Redone Bytes/sec 偏高,这个商只能当量级参考,别写进 SLA。
2. 重做不是纯写日志,它要拿锁
重做线程在副本上重放日志记录,碰到 DDL(建索引、改表结构、页分裂后的元数据变更)时,必须在用户库里拿 schema 修改锁。只读副本上的查询虽然走行版本读、不取共享锁,但编译和执行期间会持 schema 稳定性锁,两者互斥。长查询一跑,重做就得排到后面。
SQL Server 为这件事留了专门的扩展事件 sqlserver.lock_redo_blocked,每次重做被挡住都会记一条,带着阻塞它的 session_id。有这条线索就不必靠猜。
同一机制还有第二个副作用:副本上的读靠行版本实现,版本存在副本本地 tempdb。老快照不释放,版本就清不掉,tempdb 版本存储跟着涨,撑不住时读查询直接报错。
3. 并行重做是共享池,会被别的库抢走
2016 起重做默认并行,每个库用多个 worker,而这些 worker 来自实例级共享池。一台实例挂了十几个 AG 库时,某个库分不到 worker 就退化为串行重做。表现是"同样配置、同样数据量,偏偏这个库慢",很容易被误当成磁盘问题。
4. 大事务的重做不可分割
重放单位是事务。主库上一条 DELETE 删两千万行、一次索引重建写 42GB 日志,这些在主库可以分批刷、可以延迟写,到副本上只能整条重放。"日志量大"这个说法不够准,要说"单事务日志量大"——后者才是副本真正的敌人。
三、实例参考(动手步骤)
步骤 0:确认现状基线
在主副本执行,一次看清所有副本的两段队列与重做状态:
- SELECT ag.name AS ag_name,
- ar.replica_server_name AS replica,
- DB_NAME(drs.database_id) AS db_name,
- drs.is_primary_replica,
- drs.synchronization_state_desc AS sync_state,
- drs.is_suspended,
- drs.suspend_reason_desc,
- drs.log_send_queue_size / 1024 AS send_queue_mb,
- drs.redo_queue_size / 1024 AS redo_queue_mb,
- drs.redo_rate AS redo_rate_kb_s,
- drs.secondary_lag_seconds AS lag_s,
- CASE WHEN ISNULL(drs.redo_rate, 0) = 0 THEN NULL
- ELSE drs.redo_queue_size / drs.redo_rate END AS eta_seconds
- FROM sys.dm_hadr_database_replica_states AS drs
- JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
- JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
- ORDER BY drs.redo_queue_size DESC;
复制代码
单点没意义,要看趋势。用下面的循环每 30 秒采一次,落到自己的表里,事后就能看出队列是"匀速涨"还是"某几分钟突然跳"——前者是持续能力不足,后者一定有个触发事件。
- CREATE TABLE dbo.ag_queue_sample(
- sample_time datetime2(0) DEFAULT SYSUTCDATETIME(), replica sysname,
- db_name sysname, redo_queue_mb bigint, redo_rate_kb_s bigint, lag_s int);
- DECLARE @i int = 0;
- WHILE @i < 20
- BEGIN
- INSERT dbo.ag_queue_sample(replica, db_name, redo_queue_mb, redo_rate_kb_s, lag_s)
- SELECT ar.replica_server_name, DB_NAME(drs.database_id),
- drs.redo_queue_size / 1024, drs.redo_rate, drs.secondary_lag_seconds
- FROM sys.dm_hadr_database_replica_states AS drs
- JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
- WHERE drs.is_local = 0;
- WAITFOR DELAY '00:00:30';
- SET @i += 1;
- END
复制代码
我这边采到的曲线:22:00 前重做队列 200~400MB,22:05 起一分钟内跳到 8GB,23:40 到峰值 36.9GB。跳变点对应报表开跑,峰值对应索引重建,两件事完全不同,得分开治。
步骤 1:判定瓶颈在发送还是在重做
判据只有三条,对号入座:
| 观测 | 含义 | 该动哪 | | 发送队列≈0,重做队列持续增长 | 链路通,副本处理不过来 | 副本侧:重做阻塞、IO、worker | | 发送队列增长,重做队列稳定 | 日志没送到 | 网络、主库发送端、带宽 | | 两个都小,但 lag_s 很大 | 数据移动停了 | 查 is_suspended,看 suspend_reason |
本例落在第一行,所以网络整改整条路都被排除。这一步省下来的时间比后面所有优化都值钱。
步骤 2:在副本上抓重做阻塞
到辅助副本执行,重做线程在 DMV 里的 command 是 DB STARTUP,并行重做还会出现 PARALLEL REDO TASK 一类:
- SELECT r.session_id, r.command, r.wait_type, r.wait_time,
- r.blocking_session_id, DB_NAME(r.database_id) AS db_name
- FROM sys.dm_exec_requests AS r
- WHERE r.command IN ('DB STARTUP','PARALLEL REDO TASK','PARALLEL REDO HELP TASK')
- OR r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests
- WHERE command = 'DB STARTUP');
复制代码
blocking_session_id 非零,就顺着它查 sys.dm_exec_sessions 拿到登录名,一条 26 分钟的报表查询就这么露出来的。为了以后不必蹲点,再挂一个扩展事件长期记录:
- CREATE EVENT SESSION [redo_blocked] ON SERVER
- ADD EVENT sqlserver.lock_redo_blocked
- ADD TARGET package0.ring_buffer
- WITH (STARTUP_STATE = ON);
- ALTER EVENT SESSION [redo_blocked] ON SERVER STATE = START;
复制代码
顺手把重做等待也看一眼,区分"被锁挡住"和"纯粹 IO 慢":
- SELECT TOP (10) wait_type, waiting_tasks_count, wait_time_ms
- FROM sys.dm_os_wait_stats
- WHERE wait_type LIKE 'PARALLEL_REDO%' OR wait_type LIKE 'PAGEIOLATCH%'
- ORDER BY wait_time_ms DESC;
复制代码
我这边是 lock_redo_blocked 一天 11 次,PARALLEL_REDO 等待反而很小——结论是锁等待,不是 IO,也不是 worker 不够。
步骤 3:把主库的日志突发切碎
索引重建改用可恢复模式并限流。中断或超时后用 RESUME 续做,单事务日志量从 42GB 降到个位数 GB:
- ALTER INDEX IX_OrderDetail_CreatedAt ON dbo.OrderDetail
- REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 4);
- -- 超窗口就打断,次日继续
- ALTER INDEX IX_OrderDetail_CreatedAt ON dbo.OrderDetail RESUME;
复制代码
历史数据清理改分批,每批 5000 行单独提交,让副本有机会逐批追上:
- WHILE 1 = 1
- BEGIN
- DELETE TOP (5000) FROM dbo.OrderDetail WHERE CreatedAt < '2024-01-01';
- IF @@ROWCOUNT = 0 BREAK;
- CHECKPOINT;
- END
复制代码
同时把索引维护窗口从 23:30 挪到报表结束后的 01:00,两件事不再抢同一个时段。
步骤 4:给只读报表上笼子
只锁查询本身没用,要在实例层限住。建独立资源池,报表账号通过分类函数自动落进去:
- CREATE RESOURCE POOL rp_report WITH (MAX_CPU_PERCENT = 20, MAX_MEMORY_PERCENT = 20);
- CREATE WORKLOAD GROUP wg_report WITH (MAX_DOP = 4, GROUP_MAX_REQUESTS = 4)
- USING rp_report;
- CREATE FUNCTION dbo.fn_classifier() RETURNS sysname WITH SCHEMABINDING
- AS BEGIN
- RETURN CASE WHEN SUSER_SNAME() = N'report_ro' THEN N'wg_report' ELSE N'default' END;
- END;
- ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_classifier);
- ALTER RESOURCE GOVERNOR RECONFIGURE;
复制代码
GROUP_MAX_REQUESTS = 4 是关键一条:同一账号最多 4 个并发,第 5 个排队,长查询再也堆不成一片。报表侧再补 SET LOCK_TIMEOUT 30000 和客户端命令超时 600 秒,让它自己在超时后退出,而不是无限期占着 schema 稳定性锁。
步骤 5:三条监控线守门
一个每 5 分钟跑的 Agent 作业,命中任一条件就发信:
- redo_queue_size 超过 2GB 且连续两次采样都在涨;
- secondary_lag_seconds 超过 60 且 is_suspended = 0;
- is_suspended = 1 立即告警,不看其他指标(它不会自动恢复);
- eta_seconds 超过 300,即按当前速率五分钟追不上。
治理前后对比
| 指标 | 治理前 | 治理后 | | 重做队列峰值 | 36.9 GB | 320 MB | | secondary_lag_seconds 峰值 | 2810 秒 | 8 秒 | | 估算追平时间 | 约 45 分钟 | 22 秒 | | 索引重建单事务日志 | 42 GB | 9.6 GB | | redo 被阻塞次数/日 | 11 次 | 0 次 | | 报表查询超时率 | 18% | 0 | | 计划切换实测 RTO | 52 分钟 | 1 分 40 秒 | | 主库 HADR_SYNC_COMMIT P95 | 4 ms | 4 ms |
最后一行是刻意留的:同步链路始终没被影响,再次说明问题从头到尾都在副本重做侧。如果哪天这一行也涨了,才轮到查同城网络。
四、实操检查清单
- [ ] 每天固定时段采一次两个队列,存表,不看单点看趋势;
- [ ] 判定顺序永远是先分发送/重做,再谈优化,别跳过;
- [ ] 副本上常驻 redo_blocked 扩展事件,ring_buffer 留够;
- [ ] 只读报表账号走独立资源池,GROUP_MAX_REQUESTS 设成个位数;
- [ ] 报表连接统一 SET LOCK_TIMEOUT,客户端设命令超时;
- [ ] 所有大表索引维护用 ONLINE + RESUMABLE,MAXDOP 不超过 4;
- [ ] 大删除、大更新一律分批提交,单事务日志量控制在 GB 级;
- [ ] 索引维护窗口与报表窗口错开至少一小时;
- [ ] is_suspended = 1 单列一条告警,处理方式是 ALTER DATABASE ... SET HADR RESUME;
- [ ] 每季度做一次计划切换演练,实测 RTO 而不是估算;
- [ ] 实例内 AG 库数量增长时,复核并行重做 worker 是否够分。
几个容易踩的坑
一、secondary_lag_seconds 在数据移动挂起时读数是 0。 这是最坑的一条:副本停止同步,延迟显示 0 秒,看起来完美。必须跟 is_suspended、synchronization_state 一起读,单看等于自欺。
二、发送队列为 0 不代表没有数据风险。 它只说明日志发出了,已发出但未在副本固化的部分,强制故障转移时照样会丢。RPO 评估要连 last_commit_time 一起算。
三、redo_rate 是活跃时间平均值,不是瞬时吞吐。 用它算 ETA 会偏乐观,尤其是重做断续运行时。要看瞬时值请用 Database Replica 的 Redone Bytes/sec 计数器。
四、is_suspended 不会自动恢复。 无论是网络抖动还是手工暂停,恢复都得显式执行 ALTER DATABASE [库名] SET HADR RESUME,在辅助副本上做。指望它自己好,等到天亮也不会好。
五、ONLINE 重建不等于低日志。 ONLINE 只保证表可用,日志量一点没少。真正降日志的是 RESUMABLE 分批加合理的填充因子,别把两个概念混在一起。
六、大事务的重做只能等。 已写成单个事务的日志,副本必须整条重放,没有暂停点。治的是下次怎么写,不是这次怎么跳过。
七、并行重做 worker 抢不到时,加内存没用。 先看 sys.dm_os_tasks 里重做线程数,再决定是减库数量、错开窗口,还是升版本。直接上跟踪标志把并行重做关掉,是把问题变慢而不是解决。
八、副本上查 DMV 的权限容易漏。 实例级要 VIEW SERVER STATE,只看单库要 VIEW DATABASE STATE,2019 之前给错权限会返回空行而不是报错,空结果很容易被误读成"没有阻塞"。
九、不要为了让面板好看去暂停数据移动。 暂停后队列清零、延迟归零,所有指标都漂亮,代价是副本彻底失去保护能力。这种"治理"比不治更危险。 |
|