关于clickhouse:解决clickhouse服务器启动异常Suspiciously-many-broken-parts-to-remove

产生起因

• 服务重启与断电起因

最近遇到一个对于clickhouse服务启动失败问题，报错输入如下

Application: DB::Exception: Suspiciously many (32) broken parts to remove.****

这个是产生在机器断电场景下的报错，查找起因是说因为写入数据造成的元数据和数据不统一问题

clickhouse在重启服务的时候会从新加载MergeTree表引擎数据，数据可能存在损坏状况

• clickhouse配置起因

配置参数当中蕴含一个参数max_suspicious_broken_parts，默认值是10，可选值范畴是任意正整数，如果单个分区中的损坏局部数量超过max_suspicious_broken_parts 配置的值，则回绝主动修复或者回绝删除损坏局部的数据，并且服务启动时候间接报错退出

目前须要尽量避免该谬误免得服务启动失败，举荐把该参数配置为1000或者更大的值

解决方案

单表配置形式

在创立MergeTree表的时候特地配置一下max_suspicious_broken_parts参数

CREATE TABLE foo(    `A` Int64)ENGINE = MergeTreeORDER BY tuple()SETTINGS max_suspicious_broken_parts = 1000;

命令行形式

应用ALTER TABLE ... MODIFY SETTING命令批改

ALTER TABLE foo    MODIFY SETTING max_suspicious_broken_parts = 1000;-- 复原默认值-- reset to default (use value from system.merge_tree_settings)ALTER TABLE foo    RESET SETTING max_suspicious_broken_parts;

配置文件形式

如果服务起不来了，就只能应用这个形式解决

新建文件max_suspicious_broken_parts.xml写入如下内容

<?xml version="1.0"?><yandex>     <merge_tree>         <max_suspicious_broken_parts>1000</max_suspicious_broken_parts>     </merge_tree></yandex>

clickhouse的配置文件举荐搁置在/etc/clickhouse-server/config.d/文件夹下失效

如果是在Ubuntu或者Centos下面以DEB或RPM安装包的模式启动的，须要把该文件放到/etc/clickhouse-server/config.d/，最初重启clickhouse就能够了

如果是docker compose形式启动

批改compose.yaml配置如下，次要也是把对应文件挂载进入容器外部相应地位

services:  clickhouse:    image: clickhouse/clickhouse-server    ulimits:      nofile:        soft: 262144        hard: 262144    restart: always    container_name: demo-clickhouse    environment:    - CLICKHOUSE_USER=demo    - CLICKHOUSE_PASSWORD=demo-pass    - CLICKHOUSE_DB=demo    ports:      - "8123:8123"      - "9000:9000"    volumes:      - ./max_suspicious_broken_parts.xml:/etc/clickhouse-server/config.d/max_suspicious_broken_parts.xml      - demo-clickhouse:/var/lib/clickhouse    healthcheck:      test: 'wget -O - http://127.0.0.1:8123 || exit 1'     volumes:  demo-clickhouse: {}

最初运行如下命令，容器就能够失常启动了

$ docker compose down$ docker compose up -d

验证配置是否失效

连贯到clickhouse之后执行查问

SELECT *FROM system.merge_tree_settingsWHERE name LIKE '%max_suspicious_broken_parts%'

浏览参考

Clickhouse MergeTree官网配置文档

Altinity Knowledge Base对于Suspiciously many broken parts报错解决方案