节点恢复状态检查¶
1、思路¶
- 合并碎文件
- 尽量降低需要恢复的表数量（如果是中间过程表数据，使用abort recover取消恢复这些表）
- 尽量停业务恢复
2、注意¶
- abort recovery操作需要进入unsafe模式才能设置
- 表数据truncate过后仍然会delete replay
3、有用的SQL¶
3.1 估算恢复进度¶
select r.node_name,r.current_completed,t.tables_remain,r.current_completed+t.tables_remain as all_tables,(tables_remain/current_completed)*(getdate()-node_recovery_start_time) as remain_time,getdate() from recovery_status r inner join table_recovery_status t on r.node_name=t.node_name and r.is_running and t.is_running;
3.2 正在恢复的表¶
select * from recovery_status where is_running;
select node_name,projection_name,method,status,detail from projection_recoveries where start_time > sysdate -1 and status = 'running' order by 1,3;
select node_name,table_name,phase,start_time,is_historical from table_recoveries where status = 'recovering' order by start_time desc;
SELECT * FROM projection_recoveries WHERE progress ILIKE '%running%';
select node_name,projection_name,method,status,start_time,rows_deleted_count,delete_estimate,rows_to_recover_count_estimate from vs_projection_recoveries_replay_delete order by 1,2;
select * from tuple_mover_operations where is_executing;
3.3 检查日志¶
grep 'remove it from tablelist' v_fb_node0053/normal/vertica.log |grep 'CatchUp'|wc -l
grep 'remove it from tablelist' v_fb_node0053/normal/vertica.log |grep 'RecoverTable'|wc -l
grep 'Fail to recover table' v_fb_node0053/normal/vertica.log
select close_all_sockets('blocks');
select close_all_sockets('all');
all = ready_recvs + ready_sends + issued_recvs + issued_sends + multi_sends + multi_recvs + blocks
select * from v_internal.vs_global_settings_p;
select set_recover_by_table('true');
select set_recover_by_table('false');
v_monitor.projection_recoveries
v_monitor.recovery_status
v_monitor.table_recoveries
v_monitor.table_recovery_status
select node_name,operation_name,operation_start_timestamp,operation_status,plan_type,projection_name,ros_count,table_schema,table_name,total_ros_used_bytes from v_monitor.tuple_mover_operations where is_executing order by 9,1;
3.4 设置表恢复优先级¶
3.4.1 设置表不恢复¶
需要进入UNSAFE模式操作。
ERROR 7084: ABORTRECOVERY is only allowed in UNSAFE mode
SELECT do_tm_task('abortrecovery','<schema.tbl_name>');
3.4.2 调整表恢复优先级¶
select case when ps.anchor_table_name ilike '%_mid%' then
'select set_table_recover_priority('''||anchor_table_schema||'.'||anchor_table_name||''',''10'');'
when ps.anchor_table_name ilike '%_tmp%' then
'select set_table_recover_priority('''||anchor_table_schema||'.'||anchor_table_name||''',''1'');'
end as set_priority_sql,
anchor_table_schema,anchor_table_name,recover_priority,
sum(used_bytes)//1024^2 sumMB
from projection_storage ps left join tables t on ps.anchor_table_id=t.table_id
where ps.anchor_table_name ilike '%_tmp%' or ps.anchor_table_name ilike '%_mid%'
group by 1,2,3,4
order by 5 desc,1
;
set_priority_sql | anchor_table_schema | anchor_table_name | recover_priority | sumMB
------------------+---------------------+-------------------+------------------+-------
(0 rows)
SELECT set_table_recover_priority('avro_basic', '1000');
3.5 找到需要恢复的表清单¶
3.5.1 按临时表、正式表统计最近N天访问过的表数量¶
select ANCHOR_TABLE_SCHEMA, TableTag, count(*) from
(select ANCHOR_TABLE_SCHEMA,
ANCHOR_TABLE_NAME,
CASE WHEN ANCHOR_TABLE_NAME ilike '%_tmp%' or ANCHOR_TABLE_NAME ilike '%_mid%' THEN 'TMPorMIDTable' ELSE 'StageTable' END as 'TableTag' ,
max(QUERY_START_TIMESTAMP) m
from vsbak.projection_usage
group by 1,2,3
) m
where m > sysdate - 2 /*最近2天*/
group by rollup(ANCHOR_TABLE_SCHEMA,TableTag)
order by 1,2,3 desc;
ANCHOR_TABLE_SCHEMA | TableTag | count
---------------------+---------------+-------
DW_BIZ | StageTable | 3632
DW_BIZ | TMPorMIDTable | 281
DW_BIZ | | 3913
KR_REPORT | StageTable | 4607
KR_REPORT | TMPorMIDTable | 8244
KR_REPORT | | 12851
MK_REPORT | StageTable | 1241
MK_REPORT | TMPorMIDTable | 1483
MK_REPORT | | 2724
SK_REPORT | StageTable | 3
SK_REPORT | | 3
SYS_SCHEMA | StageTable | 8
SYS_SCHEMA | | 8
USER_A | StageTable | 6
USER_A | TMPorMIDTable | 7
USER_A | | 13
DEV_PUBLIC | StageTable | 316
DEV_PUBLIC | TMPorMIDTable | 445
DEV_PUBLIC | | 761
USER_B | TMPorMIDTable | 1
USER_B | | 1
USER_C | StageTable | 2
USER_C | | 2
USER_D | StageTable | 1
USER_D | TMPorMIDTable | 2
USER_D | | 3
USER_E | StageTable | 6
USER_E | TMPorMIDTable | 8
USER_E | | 14
RWD_BUSI | StageTable | 272
RWD_BUSI | TMPorMIDTable | 220
RWD_BUSI | | 492
VSB | StageTable | 14
VSB | | 14
USER_F | StageTable | 1
USER_F | | 1
USER_G | StageTable | 13
USER_G | TMPorMIDTable | 10
USER_G | | 23
| | 20823
(40 rows)
3.5.2 按表统计最近N天访问过的表清单¶
select TableDef,
m.ANCHOR_TABLE_SCHEMA||'.'||m.ANCHOR_TABLE_NAME as table_name,
sum(used_bytes)//1024^2 as sumMB
from
(select ANCHOR_TABLE_SCHEMA,
ANCHOR_TABLE_NAME,
anchor_table_id,
CASE WHEN ANCHOR_TABLE_NAME ilike '%_tmp%' or ANCHOR_TABLE_NAME ilike '%_mid%' THEN 'TMPorMIDTable' ELSE 'StageTable' END as 'TableDef' ,
max(QUERY_START_TIMESTAMP) t
from vsbak.projection_usage
group by 1,2,3,4) m
left join v_monitor.projection_storage ps using(anchor_table_id)
where m.t > sysdate - 2
group by 1,2
order by 1,3 desc,2;
4、案例¶
某 Vertica 集群节点因硬件故障宕机，以下是恢复过程摘要（客户信息已脱敏）：
| 项目 | 说明 |
|---|---|
| 数据库版本 | 11.1.1-20 |
| 节点数量 | 135+3 |
| 宕机原因 | 节点主板故障 |
| 表数量 | 约8万 |
| 操作过程 | 节点硬件修复后启动恢复，经历多次带业务/停业务交替恢复，整体耗时约2周完成全部表恢复。关键操作包括：推进 AHM 加速恢复、TRUNCATE 大量临时表减少恢复量、使用 abortrecovery 跳过非关键表。 |
| 经验教训 | 1、待恢复表数量太多，可能会恢复不完。 2、使用make_ahm_now(true)推进过后，该节点所有的表都需要重头恢复，其过程与使用standby替换一样。 3、程序中的过程数据，尽量使用local/global temp table，这样可以减少每天的变化表数据量。 4、在UNSAFE模式中设置 abortrecovery速度非常快，4万张表用时不到10分钟。5、设置表 abortrecovery，启动节点过后，表可能存在buddy-projection数据不一致，甚至丢失部分数据的情况，所以对于正式业务表数据，不建议使用此方法。 |
扩展阅读¶
- Vertica 节点恢复过程 — 节点恢复的完整原理与阶段详解
- Vertica 数据库的启动和关闭 — 数据库启停操作
- Vertica 监控最佳实践 — 监控节点状态