clickhouse集群，双实例多副本
原创clickhouse集群，双实例多副本
原创
用户9066404
发布于 2023-09-06 17:14:27
发布于 2023-09-06 17:14:27
4台机器，clickhouse集群搭建了双副本双实例，查询统计多次出现不同数据结果，具体如下 ：
出正常数据结果客户端查询按9000和9100分开，对应端口查询分布式表的结果应该只有一种数据，但结果却出现3种数据，分布式表查询结果不一致！烦请论坛大神帮指点。拜谢！
1、端配置的两个XML文件metrika-shard.xml，因为两个xml内容基本一样，只有宏不一样，就只贴一个XML文件了；用9000和9100的端口分开，具体配置如下：
<?xml version="1.0"?>
<yandex>
<remote_servers>
#分片名称，自定义
<ck_cluster>
<shard>#1
<internal_replication>true</internal_replication>
<replica>
<host>192.168.3.34</host>
<port>9000</port>
<user>default</user>
<password>123456</password>
</replica>
<replica>
<host>192.168.3.35</host>
<port>9100</port>
<user>default</user>
<password>123456</password>
</replica>
</shard>
<shard>#2
<internal_replication>true</internal_replication>
<replica>
<host>192.168.3.35</host>
<port>9000</port>
<user>default</user>
<password>123456</password>
</replica>
<replica>
<host>192.168.3.36</host>
<port>9100</port>
<user>default</user>
<password>123456</password>
</replica>
</shard>
<shard>#3
<internal_replication>true</internal_replication>
<replica>
<host>192.168.3.36</host>
<port>9000</port>
<user>default</user>
<password>123456</password>
</replica>
<replica>
<host>192.168.3.37</host>
<port>9100</port>
<user>default</user>
<password>123456</password>
</replica>
</shard>
<shard>#4
<internal_replication>true</internal_replication>
<replica>
<host>192.168.3.37</host>
<port>9000</port>
<user>default</user>
<password>123456</password>
</replica>
<replica>
<host>192.168.3.34</host>
<port>9100</port>
<user>default</user>
<password>123456</password>
</replica>
</shard>
</ck_cluster>
</remote_servers>
<zookeeper>
<node>
<host>192.168.3.34</host>
<port>2181</port>
</node>
<node>
<host>192.168.3.35</host>
<port>2181</port>
</node>
<node>
<host>192.168.3.36</host>
<port>2181</port>
</node>
<node>
<host>192.168.3.37</host>
<port>2181</port>
</node>
</zookeeper>
<macros>
<shard>02</shard>
<replica>rep_2_1</replica>
</macros>
</yandex>2、集群查询结果如下： select * from system.clusters;
|cluster |shard_num|shard_weight|replica_num|host_name |host_address|port |is_local|user |
|----------|---------|------------|-----------|------------|------------|-----|--------|-------|
|ck_cluster|1 |1 |1 |192.168.3.34|192.168.3.34|9,000|1 |default|
|ck_cluster|1 |1 |2 |192.168.3.35|192.168.3.35|9,100|0 |default|
|ck_cluster|2 |1 |1 |192.168.3.35|192.168.3.35|9,000|0 |default|
|ck_cluster|2 |1 |2 |192.168.3.36|192.168.3.36|9,100|0 |default|
|ck_cluster|3 |1 |1 |192.168.3.36|192.168.3.36|9,000|0 |default|
|ck_cluster|3 |1 |2 |192.168.3.37|192.168.3.37|9,100|0 |default|
|ck_cluster|4 |1 |1 |192.168.3.37|192.168.3.37|9,000|0 |default|
|ck_cluster|4 |1 |2 |192.168.3.34|192.168.3.34|9,100|0 |default|3、测试插入数据SQL
CREATE DATABASE db_test_S2RAB ON CLUSTER 'ck_cluster';
CREATE TABLE IF NOT EXISTS db_test_S2RAB.TEST_0A_LOCAL ON CLUSTER 'ck_cluster'
(
`id` String DEFAULT 'NULL' COMMENT '编号1',
`repo` String DEFAULT 'NULL' COMMENT '编号2',
A VARCHAR(30),
PRIMARY KEY(A)
) ENGINE =
--ReplicatedMergeTree('/clickhouse/tables/{shard}/db_test_S2RAB/TEST_0A_LOCAL','{replica}')
--ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
ReplicatedMergeTree('/clickhouse/tables/db_test_S2RAB/db_test_S2RAB/{shard}', '{replica}')
ORDER BY(A) SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS db_test_S2RAB.TEST_0A ON CLUSTER 'ck_cluster' AS
db_test_S2RAB.TEST_0A_LOCAL ENGINE = Distributed(ck_cluster,db_test_S2RAB,TEST_0A_LOCAL,rand());
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '1');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '2');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '3');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '4');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '5');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '6');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '7');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '8');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '9');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '10');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '11');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '12');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '13');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '14');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '15');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '16');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '17');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '18');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '19');
INSERT INTO db_test_S2RAB.TEST_0A(id, repo, A)VALUES('a', 'b', '20');
---查询本地表与分布式表
SELECT * FROM db_test_S2RAB.TEST_0A;
SELECT * FROM db_test_S2RAB.TEST_0A_LOCAL;SELECT COUNT(1) FROM db_test_S2RAB.TEST_0A; --分布式表
SELECT COUNT(1) FROM db_test_S2RAB.TEST_0A_LOCAL;--本地表4、问题来了：在集群任意某一个节点：
查询本地表数据正常（SELECTCOUNT(1)FROM db_test_S2RAB.TEST_0A_LOCAL;）
查询分布式表：SELECTCOUNT(1)FROM db_test_S2RAB.TEST_0A;数据不正常，多次变化，分布式表查询结果不一致：
SELECTCOUNT(1)FROM db_test_S2RAB.TEST_0A;
第一次结果：
|count()|
|-------|
|20 |
第二次结果：
|count()|
|-------|
|14 |
第三次结果：
|count()|
|-------|
|26 |
多次执行count(*)语句，多次重复出现这三种结果。具体是什么原因，烦请哪位大神帮指点下，拜谢！原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录