MonetDB Write Ahead Logs未定期清理致重启失败的解决方法问询

如相关文献3.3节所述，MonetDB本应按固定频率清理Write Ahead Logs（预写日志）：

服务运行期间，MonetDB服务器会按固定时间间隔（当前为30秒）、或当变更次数达到阈值（当前为100万次）时清理日志文件。

我所运维的环境每隔数月就会复现同类故障：MonetDB异常退出后，重启阶段需要处理大量积压的WAL日志，sql目录下存在大量编号连续的log.xxxxxx日志文件，文件列表如下：

ls sql/
log         log.106086  log.106095  log.106104  log.106113  log.106122  log.106131  log.106140  
log.106149  log.106078  log.106087  log.106096  log.106105  log.106114  log.106123  log.106132  
log.106141  log.106150  log.106079  log.106088  log.106097  log.106106  log.106115  log.106124  
log.106133  log.106142  log.106151  log.106080  log.106089  log.106098  log.106107  log.106116  
log.106125  log.106134  log.106143  log.106152  log.106081  log.106090  log.106099  log.106108  
log.106117  log.106126  log.106135  log.106144  log.106082  log.106091  log.106100  log.106109  
log.106118  log.106127  log.106136  log.106145  log.106083  log.106092  log.106101  log.106110  
log.106119  log.106128  log.106137  log.106146  log.106084  log.106093  log.106102  log.106111  
log.106120  log.106129  log.106138  log.106147  log.106085  log.106094  log.106103  log.106112  
log.106121  log.106130  log.106139  log.106148

启动monetdbd加载这些日志文件时会常规性失败，merovingian.log中记录的错误信息如下：

2022-06-03 11:06:02 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106078" (70% done)
2022-06-03 11:06:13 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106078" (97% done)
2022-06-03 11:06:25 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106079" (38% done)
2022-06-03 11:06:36 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106079" (77% done)
2022-06-03 11:06:54 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106080" (15% done)
2022-06-03 11:07:05 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106080" (52% done)
2022-06-03 11:07:16 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106080" (76% done)
2022-06-03 11:07:38 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106081" (40% done)
2022-06-03 11:07:49 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106081" (64% done)
2022-06-03 11:08:00 MSG warehouse[3172]: # still reading write-ahead log "/home/warehouse/warehouse/sql_logs/sql/log.106081" (93% done)
2022-06-03 11:08:07 ERR warehouse[3172]: #main thread: GDKmmap: !ERROR: requested too much virtual memory; memory requested: 31587172352, memory in use: 243815472, virtual memory in use: 4371382228016
2022-06-03 11:08:07 ERR warehouse[3172]: #main thread: mvc_init: !ERROR: Unable to create system tables
2022-06-03 11:08:07 ERR warehouse[3172]: #main thread: createExceptionInternal: !ERROR: SQLException:SQLinit:42000!Catalogue initialization failed
2022-06-03 11:08:07 ERR warehouse[3172]: #main thread: SQLprelude: !ERROR: Fatal error during initialization: SQLException:SQLinit:42000!Catalogue initialization failed
2022-06-03 11:08:09 MSG merovingian[2681]: database 'warehouse' (-1) has exited with exit status 1
2022-06-03 11:08:10 ERR control[2681]: !monetdbd: an internal error has occurred 'database 'warehouse' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details'
2022-06-03 11:08:14 ERR merovingian[2681]: database 'warehouse' has crashed after starting, manual intervention needed, check monetdbd's logfile (merovingian.log) for details

目前已知的应急恢复方式是手动删除部分日志文件，接受对应的数据丢失后即可启动数据库，但该方案会造成不必要的数据损失。现咨询如下问题：是否存在相关配置项可以调整MonetDB的WAL日志清理频率，让日志数据更频繁持久化落盘以避免日志积压？或是存在可手动触发日志清理的操作命令？当前环境中WAL日志清理周期长达数周甚至完全不执行，和文档说明的30秒清理间隔存在极大差异。

回答 #

首先纠正一个认知误区：文档提到的30秒间隔是WAL日志内容刷写到磁盘的间隔，不是WAL清理的间隔。WAL清理本质是checkpoint操作——将WAL中记录的变更合并到主数据文件后，才会删除已完成持久化的旧WAL日志，二者是完全独立的流程，这也是日志长期积压的核心原因。

1. WAL清理（checkpoint）可调配置项 #

MonetDB的checkpoint触发规则由以下三个参数控制，可通过monetdb set命令对指定数据库生效，不需要修改配置文件：

• gdk_vmtrim：默认值为no，内存充足时MonetDB会尽量延后checkpoint以提升写入性能，设为yes会启用更激进的内存回收和checkpoint策略，适合内存资源紧张、写入频繁的场景。
• sql_checkpoint_threshold：默认值为1000000，即文档提到的100万次变更阈值，累计达到该数量的事务变更后会自动触发checkpoint，写入密集场景可调低到20万~50万。
• checkpoint_interval：注意该参数单位是分钟，不是秒，默认值为0，代表禁用时间维度的定时checkpoint，这是绝大多数环境WAL长期不清理的核心原因。可根据业务压力设置为5_30，即每530分钟定时执行一次checkpoint。

配置生效示例：

# 对warehouse库设置每5分钟checkpoint，累计30万变更即触发checkpoint
monetdb set checkpoint_interval=5 warehouse
monetdb set sql_checkpoint_threshold=300000 warehouse
# 重启数据库使参数生效
monetdb restart warehouse

2. 手动触发WAL清理的命令 #

不需要重启数据库，连接到MonetDB后执行以下SQL即可立刻触发全量checkpoint，执行完成后所有已合并到主数据文件的旧WAL日志会被自动清理：

CALL sys.checkpoint(true);

如果不信任数据库自身的定时触发逻辑，可配合crontab定期调用mclient执行该语句，实现自定义周期的日志清理。

3. 已积压大量WAL时的无数据损失恢复方案 #

不要直接删除WAL文件，会丢失未持久化的事务数据。重启前先调整系统虚拟内存映射限制：

• 执行sysctl -w vm.max_map_count=2621440将进程最大内存映射数调大到256万以上（系统默认值通常为65530，处理大量WAL时mmap调用会超出该限制，触发日志中记录的虚拟内存申请错误）
• 再正常启动数据库，启动成功后第一时间执行CALL sys.checkpoint(true);手动触发全量checkpoint，等待执行完成后所有积压WAL会被自动清理，不会丢失任何已提交数据。

内容的提问来源于stack exchange，提问作者James Scott