clickhouse停止和避免mutations操作

问题描述

在使用clickhouse时，使用了alter查询的delete和update操作进行数据的删除和更新，当大量使用这类操作时，造成clickhouse服务无法连接，观察日志发现，出现了大量mutation的操作，严重占用clickhouse资源。
日志如下：

2022.10.14 15:43:01.896327 [ 1978 ] {} <Debug> ads.dw_wxt_means_download_statistics_day (ee73b1ab-dbe7-4ba2-b30f-027803650f2a): Loading mutation: mutation_285350.txt entry, commands size: 1
2022.10.14 15:43:01.905333 [ 1978 ] {} <Debug> ads.dw_wxt_means_download_statistics_day (ee73b1ab-dbe7-4ba2-b30f-027803650f2a): Loading mutation: mutation_309364.txt entry, commands size: 1
......
1
2
3

经过研究，发现ALTER DELETE和UPDATE表数据的实现机制是mutations。
对于MergeTree表，mutations机制的操作会重写整个数据块（rewriting whole data parts），所以是一个重操作，会严重占用服务资源。尤其是 ALTER TABLE … DELETE 和 ALTER TABLE … UPDATE。
所以为了保证clickhouse服务的可靠性和可用性，应该尽量避免使用mutations机制的操作。

问题处理

在大量执行mutations操作时，会影响clickhouse服务的正常连接，此时可以先停止未完成的mutations操作，保证服务可用性。
正常的停止方法：

-- Cancel and remove all mutations of the single table:
KILL MUTATION WHERE database = 'default' AND table = 'table'

-- Cancel the specific mutation:
KILL MUTATION WHERE database = 'default' AND table = 'table' AND mutation_id = 'mutation_3.txt'
1
2
3
4
5

当mutations操作非常多，导致无法连接clickhouse执行上述sql时，可以通过手动删除mutation文件的方式，停止未完成的mutations操作。
mutation文件存在于clickhouse表目录下，文件名类似 mutation_3.txt，只是数字不同。
clickhosue数据存储目录可以从配置文件中找到，这里以参数 $CLICKHOUSE_PATH 代替。
假设，bi数据库下的表t1存在mutation文件，删除方法如下：

建议先备份mutatiuon文件，方便故障恢复。

systemctl stop clickhouse-server
cd $CLICKHOUSE_PATH/data/bi/t1
rm -rf mutation*
systemctl start clickhouse-server
1
2
3
4

优雅删除和更新数据

但是在日常使用中，不可避免的要进行大量数据删除和更新，如何优雅的执行数据删除和更新，是一个问题。
经过研究clikhouse相关资料，发现可以使用 ReplacingMergeTree。
ReplacingMergeTree类似Hbase，拥有列版本号，当执行merges时，相同sort key的行，只留下一行：

• 当没有设置列版本号时，默认留下最后插入的行。
• 当设置列版本号时，会留下版本号最大的行，如果版本号相同，按照未设置版本号的规则执行，例如留下最后插入的行。

使用ReplacingMergeTree，对于新增数据和更新数据，统一执行 INSERT INTO 即可，在merge时，会自动删除旧数据。
在查询时，为了保证使用最新数据，需要在执行查询时，使用 FINAL。
考虑到每次查询都需要注意加上 FINAL 不太易用，可以通过创建view的方式解决，view中使用 FINAL 查询物理表，查询时使用view进行查询。

https://clickhouse.com/docs/en/sql-reference/statements/alter/#mutations
https://clickhouse.com/docs/en/operations/system-tables/mutations/#system_tables-mutations
https://clickhouse.com/docs/en/sql-reference/statements/kill/#kill-mutation
https://clickhouse.com/docs/en/manage/tuning-for-cloud-cost-efficiency/#avoid-mutations
https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/replacingmergetree
https://blog.csdn.net/yunqiinsight/article/details/106532398