当前位置： 文江博客 话题详情

ClickHouse 1节点中的3个不同步的节点

发布于 2025-02-01 09:03:08 字数 7783 浏览 1 评论 0 原文

我有一个3节点的clickhouse群集，几乎与。

我在3个节点上运行了创建表语句，如下所示：


CREATE TABLE replica_test.hits_shard
(
 `WatchID` UInt64,
 `JavaEnable` UInt8,
 `Title` String,
 `GoodEvent` Int16,
 `EventTime` DateTime,
 `EventDate` Date,
 `CounterID` UInt32,
 `ClientIP` UInt32,
 `ClientIP6` FixedString(16),
 `RegionID` UInt32,
 `UserID` UInt64,
 `CounterClass` Int8,
 `OS` UInt8,
 `UserAgent` UInt8,
 `URL` String,
 `Referer` String,
 `URLDomain` String,
 `RefererDomain` String,
 `Refresh` UInt8,
 `IsRobot` UInt8,
 `RefererCategories` Array(UInt16),
 `URLCategories` Array(UInt16),
 `URLRegions` Array(UInt32),
 `RefererRegions` Array(UInt32),
 `ResolutionWidth` UInt16,
 `ResolutionHeight` UInt16,
 `ResolutionDepth` UInt8,
 `FlashMajor` UInt8,
 `FlashMinor` UInt8,
 `FlashMinor2` String,
 `NetMajor` UInt8,
 `NetMinor` UInt8,
 `UserAgentMajor` UInt16,
 `UserAgentMinor` FixedString(2),
 `CookieEnable` UInt8,
 `JavascriptEnable` UInt8,
 `IsMobile` UInt8,
 `MobilePhone` UInt8,
 `MobilePhoneModel` String,
 `Params` String,
 `IPNetworkID` UInt32,
 `TraficSourceID` Int8,
 `SearchEngineID` UInt16,
 `SearchPhrase` String,
 `AdvEngineID` UInt8,
 `IsArtifical` UInt8,
 `WindowClientWidth` UInt16,
 `WindowClientHeight` UInt16,
 `ClientTimeZone` Int16,
 `ClientEventTime` DateTime,
 `SilverlightVersion1` UInt8,
 `SilverlightVersion2` UInt8,
 `SilverlightVersion3` UInt32,
 `SilverlightVersion4` UInt16,
 `PageCharset` String,
 `CodeVersion` UInt32,
 `IsLink` UInt8,
 `IsDownload` UInt8,
 `IsNotBounce` UInt8,
 `FUniqID` UInt64,
 `HID` UInt32,
 `IsOldCounter` UInt8,
 `IsEvent` UInt8,
 `IsParameter` UInt8,
 `DontCountHits` UInt8,
 `WithHash` UInt8,
 `HitColor` FixedString(1),
 `UTCEventTime` DateTime,
 `Age` UInt8,
 `Sex` UInt8,
 `Income` UInt8,
 `Interests` UInt16,
 `Robotness` UInt8,
 `GeneralInterests` Array(UInt16),
 `RemoteIP` UInt32,
 `RemoteIP6` FixedString(16),
 `WindowName` Int32,
 `OpenerName` Int32,
 `HistoryLength` Int16,
 `BrowserLanguage` FixedString(2),
 `BrowserCountry` FixedString(2),
 `SocialNetwork` String,
 `SocialAction` String,
 `HTTPError` UInt16,
 `SendTiming` Int32,
 `DNSTiming` Int32,
 `ConnectTiming` Int32,
 `ResponseStartTiming` Int32,
 `ResponseEndTiming` Int32,
 `FetchTiming` Int32,
 `RedirectTiming` Int32,
 `DOMInteractiveTiming` Int32,
 `DOMContentLoadedTiming` Int32,
 `DOMCompleteTiming` Int32,
 `LoadEventStartTiming` Int32,
 `LoadEventEndTiming` Int32,
 `NSToDOMContentLoadedTiming` Int32,
 `FirstPaintTiming` Int32,
 `RedirectCount` Int8,
 `SocialSourceNetworkID` UInt8,
 `SocialSourcePage` String,
 `ParamPrice` Int64,
 `ParamOrderID` String,
 `ParamCurrency` FixedString(3),
 `ParamCurrencyID` UInt16,
 `GoalsReached` Array(UInt32),
 `OpenstatServiceName` String,
 `OpenstatCampaignID` String,
 `OpenstatAdID` String,
 `OpenstatSourceID` String,
 `UTMSource` String,
 `UTMMedium` String,
 `UTMCampaign` String,
 `UTMContent` String,
 `UTMTerm` String,
 `FromTag` String,
 `HasGCLID` UInt8,
 `RefererHash` UInt64,
 `URLHash` UInt64,
 `CLID` UInt32,
 `YCLID` UInt64,
 `ShareService` String,
 `ShareURL` String,
 `ShareTitle` String,
 `ParsedParams` Nested(
 Key1 String,
 Key2 String,
 Key3 String,
 Key4 String,
 Key5 String,
 ValueDouble Float64),
 `IslandID` FixedString(16),
 `RequestNum` UInt32,
 `RequestTry` UInt8
)
ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/tables/hits', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID);


CREATE TABLE replica_test.hits_distributed
(
 `WatchID` UInt64,
 `JavaEnable` UInt8,
 `Title` String,
 `GoodEvent` Int16,
 `EventTime` DateTime,
 `EventDate` Date,
 `CounterID` UInt32,
 `ClientIP` UInt32,
 `ClientIP6` FixedString(16),
 `RegionID` UInt32,
 `UserID` UInt64,
 `CounterClass` Int8,
 `OS` UInt8,
 `UserAgent` UInt8,
 `URL` String,
 `Referer` String,
 `URLDomain` String,
 `RefererDomain` String,
 `Refresh` UInt8,
 `IsRobot` UInt8,
 `RefererCategories` Array(UInt16),
 `URLCategories` Array(UInt16),
 `URLRegions` Array(UInt32),
 `RefererRegions` Array(UInt32),
 `ResolutionWidth` UInt16,
 `ResolutionHeight` UInt16,
 `ResolutionDepth` UInt8,
 `FlashMajor` UInt8,
 `FlashMinor` UInt8,
 `FlashMinor2` String,
 `NetMajor` UInt8,
 `NetMinor` UInt8,
 `UserAgentMajor` UInt16,
 `UserAgentMinor` FixedString(2),
 `CookieEnable` UInt8,
 `JavascriptEnable` UInt8,
 `IsMobile` UInt8,
 `MobilePhone` UInt8,
 `MobilePhoneModel` String,
 `Params` String,
 `IPNetworkID` UInt32,
 `TraficSourceID` Int8,
 `SearchEngineID` UInt16,
 `SearchPhrase` String,
 `AdvEngineID` UInt8,
 `IsArtifical` UInt8,
 `WindowClientWidth` UInt16,
 `WindowClientHeight` UInt16,
 `ClientTimeZone` Int16,
 `ClientEventTime` DateTime,
 `SilverlightVersion1` UInt8,
 `SilverlightVersion2` UInt8,
 `SilverlightVersion3` UInt32,
 `SilverlightVersion4` UInt16,
 `PageCharset` String,
 `CodeVersion` UInt32,
 `IsLink` UInt8,
 `IsDownload` UInt8,
 `IsNotBounce` UInt8,
 `FUniqID` UInt64,
 `HID` UInt32,
 `IsOldCounter` UInt8,
 `IsEvent` UInt8,
 `IsParameter` UInt8,
 `DontCountHits` UInt8,
 `WithHash` UInt8,
 `HitColor` FixedString(1),
 `UTCEventTime` DateTime,
 `Age` UInt8,
 `Sex` UInt8,
 `Income` UInt8,
 `Interests` UInt16,
 `Robotness` UInt8,
 `GeneralInterests` Array(UInt16),
 `RemoteIP` UInt32,
 `RemoteIP6` FixedString(16),
 `WindowName` Int32,
 `OpenerName` Int32,
 `HistoryLength` Int16,
 `BrowserLanguage` FixedString(2),
 `BrowserCountry` FixedString(2),
 `SocialNetwork` String,
 `SocialAction` String,
 `HTTPError` UInt16,
 `SendTiming` Int32,
 `DNSTiming` Int32,
 `ConnectTiming` Int32,
 `ResponseStartTiming` Int32,
 `ResponseEndTiming` Int32,
 `FetchTiming` Int32,
 `RedirectTiming` Int32,
 `DOMInteractiveTiming` Int32,
 `DOMContentLoadedTiming` Int32,
 `DOMCompleteTiming` Int32,
 `LoadEventStartTiming` Int32,
 `LoadEventEndTiming` Int32,
 `NSToDOMContentLoadedTiming` Int32,
  `FirstPaintTiming` Int32,
 `RedirectCount` Int8,
 `SocialSourceNetworkID` UInt8,
 `SocialSourcePage` String,
 `ParamPrice` Int64,
 `ParamOrderID` String,
 `ParamCurrency` FixedString(3),
 `ParamCurrencyID` UInt16,
 `GoalsReached` Array(UInt32),
 `OpenstatServiceName` String,
 `OpenstatCampaignID` String,
 `OpenstatAdID` String,
 `OpenstatSourceID` String,
 `UTMSource` String,
 `UTMMedium` String,
 `UTMCampaign` String,
 `UTMContent` String,
 `UTMTerm` String,
 `FromTag` String,
 `HasGCLID` UInt8,
 `RefererHash` UInt64,
 `URLHash` UInt64,
 `CLID` UInt32,
 `YCLID` UInt64,
 `ShareService` String,
 `ShareURL` String,
 `ShareTitle` String,
 `ParsedParams.Key1` Array(String),
 `ParsedParams.Key2` Array(String),
 `ParsedParams.Key3` Array(String),
 `ParsedParams.Key4` Array(String),
 `ParsedParams.Key5` Array(String),
 `ParsedParams.ValueDouble` Array(Float64),
 `IslandID` FixedString(16),
 `RequestNum` UInt32,
 `RequestTry` UInt8
)
ENGINE = Distributed('hivestack', replica_test, 'hits_shard', rand());

我对Node-2有一个不同步的问题，如下所示：

  • 如果我将数据插入Node-2，则仅通过Node-2看到数据
    • 插入...中node2 =＆gt;然后，hits_shard选择计数（*）fom node1 =＆gt; hits_shard结果是0
  • 如果我将数据插入Node-1或Node-3中，则所有3个节点都可以看到。
SELECT * from system.replicas:
replica_test,hits_shard,ReplicatedMergeTree,1,1,0,0,0,0,/clickhouse/tables/02/tables/hits,clickhouse02,/clickhouse/tables/02/tables/hits/replicas/clickhouse02,-1,0,0,0,0,1969-12-31 19:00:00,1969-12-31 19:00:00,1969-12-31 19:00:00,1969-12-31 19:00:00,"","","",0,0,1969-12-31 19:00:00,0,1,1,"","",{'clickhouse02':1}

选择 *来自System.Replication_queue 是空的

单击室版本：ClickHouse/ClickHouse-Server：22.4.5.9 Zookeeper版本：Bitnami/Zookeeper：3.8.0

我验证了所有3个节点都可以通过clickhouse-lient与彼此交谈，而NC 9000

会感谢有关如何调试/修复此问题的任何提示

原文

I have a 3-node clickhouse cluster, almost identical to the setup in https://github.com/tetafro/clickhouse-cluster.

I ran the CREATE TABLE statement on the 3 nodes, as follows:


CREATE TABLE replica_test.hits_shard
(
 `WatchID` UInt64,
 `JavaEnable` UInt8,
 `Title` String,
 `GoodEvent` Int16,
 `EventTime` DateTime,
 `EventDate` Date,
 `CounterID` UInt32,
 `ClientIP` UInt32,
 `ClientIP6` FixedString(16),
 `RegionID` UInt32,
 `UserID` UInt64,
 `CounterClass` Int8,
 `OS` UInt8,
 `UserAgent` UInt8,
 `URL` String,
 `Referer` String,
 `URLDomain` String,
 `RefererDomain` String,
 `Refresh` UInt8,
 `IsRobot` UInt8,
 `RefererCategories` Array(UInt16),
 `URLCategories` Array(UInt16),
 `URLRegions` Array(UInt32),
 `RefererRegions` Array(UInt32),
 `ResolutionWidth` UInt16,
 `ResolutionHeight` UInt16,
 `ResolutionDepth` UInt8,
 `FlashMajor` UInt8,
 `FlashMinor` UInt8,
 `FlashMinor2` String,
 `NetMajor` UInt8,
 `NetMinor` UInt8,
 `UserAgentMajor` UInt16,
 `UserAgentMinor` FixedString(2),
 `CookieEnable` UInt8,
 `JavascriptEnable` UInt8,
 `IsMobile` UInt8,
 `MobilePhone` UInt8,
 `MobilePhoneModel` String,
 `Params` String,
 `IPNetworkID` UInt32,
 `TraficSourceID` Int8,
 `SearchEngineID` UInt16,
 `SearchPhrase` String,
 `AdvEngineID` UInt8,
 `IsArtifical` UInt8,
 `WindowClientWidth` UInt16,
 `WindowClientHeight` UInt16,
 `ClientTimeZone` Int16,
 `ClientEventTime` DateTime,
 `SilverlightVersion1` UInt8,
 `SilverlightVersion2` UInt8,
 `SilverlightVersion3` UInt32,
 `SilverlightVersion4` UInt16,
 `PageCharset` String,
 `CodeVersion` UInt32,
 `IsLink` UInt8,
 `IsDownload` UInt8,
 `IsNotBounce` UInt8,
 `FUniqID` UInt64,
 `HID` UInt32,
 `IsOldCounter` UInt8,
 `IsEvent` UInt8,
 `IsParameter` UInt8,
 `DontCountHits` UInt8,
 `WithHash` UInt8,
 `HitColor` FixedString(1),
 `UTCEventTime` DateTime,
 `Age` UInt8,
 `Sex` UInt8,
 `Income` UInt8,
 `Interests` UInt16,
 `Robotness` UInt8,
 `GeneralInterests` Array(UInt16),
 `RemoteIP` UInt32,
 `RemoteIP6` FixedString(16),
 `WindowName` Int32,
 `OpenerName` Int32,
 `HistoryLength` Int16,
 `BrowserLanguage` FixedString(2),
 `BrowserCountry` FixedString(2),
 `SocialNetwork` String,
 `SocialAction` String,
 `HTTPError` UInt16,
 `SendTiming` Int32,
 `DNSTiming` Int32,
 `ConnectTiming` Int32,
 `ResponseStartTiming` Int32,
 `ResponseEndTiming` Int32,
 `FetchTiming` Int32,
 `RedirectTiming` Int32,
 `DOMInteractiveTiming` Int32,
 `DOMContentLoadedTiming` Int32,
 `DOMCompleteTiming` Int32,
 `LoadEventStartTiming` Int32,
 `LoadEventEndTiming` Int32,
 `NSToDOMContentLoadedTiming` Int32,
 `FirstPaintTiming` Int32,
 `RedirectCount` Int8,
 `SocialSourceNetworkID` UInt8,
 `SocialSourcePage` String,
 `ParamPrice` Int64,
 `ParamOrderID` String,
 `ParamCurrency` FixedString(3),
 `ParamCurrencyID` UInt16,
 `GoalsReached` Array(UInt32),
 `OpenstatServiceName` String,
 `OpenstatCampaignID` String,
 `OpenstatAdID` String,
 `OpenstatSourceID` String,
 `UTMSource` String,
 `UTMMedium` String,
 `UTMCampaign` String,
 `UTMContent` String,
 `UTMTerm` String,
 `FromTag` String,
 `HasGCLID` UInt8,
 `RefererHash` UInt64,
 `URLHash` UInt64,
 `CLID` UInt32,
 `YCLID` UInt64,
 `ShareService` String,
 `ShareURL` String,
 `ShareTitle` String,
 `ParsedParams` Nested(
 Key1 String,
 Key2 String,
 Key3 String,
 Key4 String,
 Key5 String,
 ValueDouble Float64),
 `IslandID` FixedString(16),
 `RequestNum` UInt32,
 `RequestTry` UInt8
)
ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/tables/hits', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID);


CREATE TABLE replica_test.hits_distributed
(
 `WatchID` UInt64,
 `JavaEnable` UInt8,
 `Title` String,
 `GoodEvent` Int16,
 `EventTime` DateTime,
 `EventDate` Date,
 `CounterID` UInt32,
 `ClientIP` UInt32,
 `ClientIP6` FixedString(16),
 `RegionID` UInt32,
 `UserID` UInt64,
 `CounterClass` Int8,
 `OS` UInt8,
 `UserAgent` UInt8,
 `URL` String,
 `Referer` String,
 `URLDomain` String,
 `RefererDomain` String,
 `Refresh` UInt8,
 `IsRobot` UInt8,
 `RefererCategories` Array(UInt16),
 `URLCategories` Array(UInt16),
 `URLRegions` Array(UInt32),
 `RefererRegions` Array(UInt32),
 `ResolutionWidth` UInt16,
 `ResolutionHeight` UInt16,
 `ResolutionDepth` UInt8,
 `FlashMajor` UInt8,
 `FlashMinor` UInt8,
 `FlashMinor2` String,
 `NetMajor` UInt8,
 `NetMinor` UInt8,
 `UserAgentMajor` UInt16,
 `UserAgentMinor` FixedString(2),
 `CookieEnable` UInt8,
 `JavascriptEnable` UInt8,
 `IsMobile` UInt8,
 `MobilePhone` UInt8,
 `MobilePhoneModel` String,
 `Params` String,
 `IPNetworkID` UInt32,
 `TraficSourceID` Int8,
 `SearchEngineID` UInt16,
 `SearchPhrase` String,
 `AdvEngineID` UInt8,
 `IsArtifical` UInt8,
 `WindowClientWidth` UInt16,
 `WindowClientHeight` UInt16,
 `ClientTimeZone` Int16,
 `ClientEventTime` DateTime,
 `SilverlightVersion1` UInt8,
 `SilverlightVersion2` UInt8,
 `SilverlightVersion3` UInt32,
 `SilverlightVersion4` UInt16,
 `PageCharset` String,
 `CodeVersion` UInt32,
 `IsLink` UInt8,
 `IsDownload` UInt8,
 `IsNotBounce` UInt8,
 `FUniqID` UInt64,
 `HID` UInt32,
 `IsOldCounter` UInt8,
 `IsEvent` UInt8,
 `IsParameter` UInt8,
 `DontCountHits` UInt8,
 `WithHash` UInt8,
 `HitColor` FixedString(1),
 `UTCEventTime` DateTime,
 `Age` UInt8,
 `Sex` UInt8,
 `Income` UInt8,
 `Interests` UInt16,
 `Robotness` UInt8,
 `GeneralInterests` Array(UInt16),
 `RemoteIP` UInt32,
 `RemoteIP6` FixedString(16),
 `WindowName` Int32,
 `OpenerName` Int32,
 `HistoryLength` Int16,
 `BrowserLanguage` FixedString(2),
 `BrowserCountry` FixedString(2),
 `SocialNetwork` String,
 `SocialAction` String,
 `HTTPError` UInt16,
 `SendTiming` Int32,
 `DNSTiming` Int32,
 `ConnectTiming` Int32,
 `ResponseStartTiming` Int32,
 `ResponseEndTiming` Int32,
 `FetchTiming` Int32,
 `RedirectTiming` Int32,
 `DOMInteractiveTiming` Int32,
 `DOMContentLoadedTiming` Int32,
 `DOMCompleteTiming` Int32,
 `LoadEventStartTiming` Int32,
 `LoadEventEndTiming` Int32,
 `NSToDOMContentLoadedTiming` Int32,
  `FirstPaintTiming` Int32,
 `RedirectCount` Int8,
 `SocialSourceNetworkID` UInt8,
 `SocialSourcePage` String,
 `ParamPrice` Int64,
 `ParamOrderID` String,
 `ParamCurrency` FixedString(3),
 `ParamCurrencyID` UInt16,
 `GoalsReached` Array(UInt32),
 `OpenstatServiceName` String,
 `OpenstatCampaignID` String,
 `OpenstatAdID` String,
 `OpenstatSourceID` String,
 `UTMSource` String,
 `UTMMedium` String,
 `UTMCampaign` String,
 `UTMContent` String,
 `UTMTerm` String,
 `FromTag` String,
 `HasGCLID` UInt8,
 `RefererHash` UInt64,
 `URLHash` UInt64,
 `CLID` UInt32,
 `YCLID` UInt64,
 `ShareService` String,
 `ShareURL` String,
 `ShareTitle` String,
 `ParsedParams.Key1` Array(String),
 `ParsedParams.Key2` Array(String),
 `ParsedParams.Key3` Array(String),
 `ParsedParams.Key4` Array(String),
 `ParsedParams.Key5` Array(String),
 `ParsedParams.ValueDouble` Array(Float64),
 `IslandID` FixedString(16),
 `RequestNum` UInt32,
 `RequestTry` UInt8
)
ENGINE = Distributed('hivestack', replica_test, 'hits_shard', rand());

I have an out-of-sync issue with node-2 as follows:

  • if i insert data to node-2, the data is only seen by node-2
    • insert ... into node2 => hits_shard then select count(*) fom node1 => hits_shard the result is 0
  • if i insert data into node-1 or node-3, it's seen by all 3 nodes.
SELECT * from system.replicas:
replica_test,hits_shard,ReplicatedMergeTree,1,1,0,0,0,0,/clickhouse/tables/02/tables/hits,clickhouse02,/clickhouse/tables/02/tables/hits/replicas/clickhouse02,-1,0,0,0,0,1969-12-31 19:00:00,1969-12-31 19:00:00,1969-12-31 19:00:00,1969-12-31 19:00:00,"","","",0,0,1969-12-31 19:00:00,0,1,1,"","",{'clickhouse02':1}

select * from system.replication_queue is empty

clickhouse version: clickhouse/clickhouse-server:22.4.5.9
zookeeper version: bitnami/zookeeper:3.8.0

I verified that all 3 nodes can talk to each others via clickhouse-client and nc 9000

Would appreciate any tips on how to debug/fix this

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

鼻尖触碰 2025-02-08 09:03:08

您确定所有3个节点上都有相同的Zookeeper Clickhouse设置？

您是插入分布式表还是插入 ReplicatedMergetree ？

对于第一种情况，请检查Node-2

SELECT * FROM system.distribution_queue FORMAT Vertical
SELECT * FROM system.clusters FORMAT Vertical

并比较＆lt; remote_servers＆gt; 在您的配置中的所有3个节点

Are you sure you have the same zookeeper clickhouse settings on all 3 nodes?

Do you insert into Distributed table or insert into ReplicatedMergeTree?

For first case, check on node-2

SELECT * FROM system.distribution_queue FORMAT Vertical
SELECT * FROM system.clusters FORMAT Vertical

and compare <remote_servers> section in your config for all 3 nodes

回复 原文
~没有更多了~

关于作者

分開簡單

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

身边

文章 0 评论 0

qq_oxT0yE

文章 0 评论 0

卷着的草席

文章 0 评论 0

￡冰雨忧蓝°

文章 0 评论 0

我还不会笑

文章 0 评论 0

Unbroken

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文