環(huán)境
- 每天機(jī)器已安裝單機(jī)版的ClickHouse，官方推薦使用 rpm 安裝，也可以安裝好一臺(tái)后復(fù)制文件過(guò)去
- CentOS Linux release 7.8.2003 (Core)
- ClickHouse 20.11.6.6
- 防火墻、selinux已關(guān)閉
| 分片 | IP，僅單副本 |
|---|---|
| 分片01 | 192.168.66.101:9000 |
| 分片02 | 192.168.66.102:9000 |
| 分片03 | 192.168.66.103:9000 |
限制
當(dāng)前配置為3分片1副本，也可以?xún)H使用2臺(tái)機(jī)器配置為2分片。
單副本多分片情況下，數(shù)據(jù)會(huì)進(jìn)行分片存儲(chǔ)，但若有一臺(tái)機(jī)器分配掛掉，就會(huì)導(dǎo)致整個(gè)集群不可用。
image.png
配置host【所有機(jī)器配置】
echo "192.168.66.101 yqtest1" >> /etc/hosts
echo "192.168.66.102 yqtest2" >> /etc/hosts
echo "192.168.66.103 yqtest3" >> /etc/hosts
安裝【所有都安裝】
安裝略，可參考前篇文章
集群配置
集群配置在配置文件中加入后立即生效
配置分片與副本信息【所有機(jī)器都需配置】
可配置完成一個(gè)后傳過(guò)去，也可以分別配置
vi /etc/clickhouse-server/config.d/metrika.xml
<?xml version="1.0"?>
<yandex>
<!--集群相關(guān)配置-->
<clickhouse_remote_servers>
<!--自定義集群名稱(chēng) ckcluster_3shards_1replicas-->
<ckcluster_3shards_1replicas>
<!--分片1-->
<shard>
<internal_replication>false</internal_replication>
<!--yqtest1 單副本-->
<replica>
<host>yqtest1</host>
<port>9000</port>
</replica>
</shard>
<!--分片2-->
<shard>
<internal_replication>false</internal_replication>
<!--yqtest2 單副本-->
<replica>
<host>yqtest2</host>
<port>9000</port>
</replica>
</shard>
<!--分片3-->
<shard>
<internal_replication>false</internal_replication>
<!--yqtest3 單副本-->
<replica>
<host>yqtest3</host>
<port>9000</port>
</replica>
</shard>
</ckcluster_3shards_1replicas>
</clickhouse_remote_servers>
<!--壓縮算法-->
<clickhouse_compression>
<case>
<min_part_size>10000000000</min_part_size>
<min_part_size_ratio>0.01</min_part_size_ratio>
<method>lz4</method>
</case>
</clickhouse_compression>
</yandex>
注意：internal_replication的參數(shù)設(shè)置很重要，當(dāng)有多個(gè)副本集群時(shí)
- 創(chuàng)建的表為非復(fù)制表
<internal_replication>false</internal_replication>
寫(xiě)分布式表：會(huì)將數(shù)據(jù)插入到所有副本的本地表中，副本表上的數(shù)據(jù)保持同步。寫(xiě)入本地單表，數(shù)據(jù)僅寫(xiě)入到當(dāng)前服務(wù)器單表上，會(huì)出現(xiàn)不同服務(wù)器查詢(xún)結(jié)果不同。
<internal_replication>true</internal_replication>
寫(xiě)分布式表：數(shù)據(jù)只插入到一個(gè)副本的本地表中，不會(huì)做同步，數(shù)據(jù)紊亂，官方不推薦使用 - 創(chuàng)建的表為復(fù)制表
<internal_replication>false</internal_replication>
寫(xiě)分布式表：數(shù)據(jù)會(huì)分片后插入到所有本地表中，會(huì)出現(xiàn)重復(fù)，但復(fù)制表會(huì)自動(dòng)刪除重復(fù)，這有性能損耗
<internal_replication>true</internal_replication>
寫(xiě)分布式表/本地表：會(huì)隨機(jī)寫(xiě)一份到分片上，然后自動(dòng)同步數(shù)據(jù)到復(fù)制副本分片上。官方推薦
配置信息引入config文件【所有服務(wù)器配置】
vi /etc/clickhouse-server/config.xml
搜索 metrika，在下面添加引入
<!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file.
By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element.
Values for substitutions are specified in /yandex/name_of_substitution elements in that file.
-->
查看集群信息【任何一臺(tái)】
clickhouse-client -h 192.168.66.101 --port 9000 --user default --query "select * from system.clusters";
clickhouse-client -h 192.168.66.102 --port 9000 --user default --query "select * from system.clusters";
clickhouse-client -h 192.168.66.103 --port 9000 --user default --query "select * from system.clusters";
image.png
- 在這遭遇到一個(gè)詭異的問(wèn)題，103怎么查詢(xún) is_local（圈出字段） 都是定位在 101端，重啟103端clickhouse解決
- 這邊不一致一定要重視，否則可能出現(xiàn)查詢(xún)分片表數(shù)據(jù)不一致
- 下面這些test的集群可以修改clickhouse-server/config.xml中的
數(shù)據(jù)導(dǎo)入測(cè)試
導(dǎo)入測(cè)試數(shù)據(jù)【101】
官方測(cè)試數(shù)據(jù)下載地址：
【405M】https://datasets.clickhouse.tech/visits/tsv/visits_v1.tsv.xz
僅需傳到其中一臺(tái)服務(wù)器上即可，本例放在了 101 上，數(shù)據(jù)庫(kù)需要預(yù)先所有庫(kù)創(chuàng)建好
# 解壓，解壓后2.5G
unxz visits_v1.tsv.xz
-- 創(chuàng)建庫(kù)【所有】
clickhouse-client --query "CREATE DATABASE IF NOT EXISTS yqtest"
-- 創(chuàng)建表【101】
clickhouse-client --query "CREATE TABLE yqtest.visits_v1 ( CounterID UInt32, StartDate Date, Sign Int8, IsNew UInt8, VisitID UInt64, UserID UInt64, StartTime DateTime, Duration UInt32, UTCStartTime DateTime, PageViews Int32, Hits Int32, IsBounce UInt8, Referer String, StartURL String, RefererDomain String, StartURLDomain String, EndURL String, LinkURL String, IsDownload UInt8, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, PlaceID Int32, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), IsYandex UInt8, GoalReachesDepth Int32, GoalReachesURL Int32, GoalReachesAny Int32, SocialSourceNetworkID UInt8, SocialSourcePage String, MobilePhoneModel String, ClientEventTime DateTime, RegionID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RemoteIP UInt32, RemoteIP6 FixedString(16), IPNetworkID UInt32, SilverlightVersion3 UInt32, CodeVersion UInt32, ResolutionWidth UInt16, ResolutionHeight UInt16, UserAgentMajor UInt16, UserAgentMinor UInt16, WindowClientWidth UInt16, WindowClientHeight UInt16, SilverlightVersion2 UInt8, SilverlightVersion4 UInt16, FlashVersion3 UInt16, FlashVersion4 UInt16, ClientTimeZone Int16, OS UInt8, UserAgent UInt8, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, NetMajor UInt8, NetMinor UInt8, MobilePhone UInt8, SilverlightVersion1 UInt8, Age UInt8, Sex UInt8, Income UInt8, JavaEnable UInt8, CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, BrowserLanguage UInt16, BrowserCountry UInt16, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), Params Array(String), Goals Nested(ID UInt32, Serial UInt32, EventTime DateTime, Price Int64, OrderID String, CurrencyID UInt32), WatchIDs Array(UInt64), ParamSumPrice Int64, ParamCurrency FixedString(3), ParamCurrencyID UInt16, ClickLogID UInt64, ClickEventID Int32, ClickGoodEvent Int32, ClickEventTime DateTime, ClickPriorityID Int32, ClickPhraseID Int32, ClickPageID Int32, ClickPlaceID Int32, ClickTypeID Int32, ClickResourceID Int32, ClickCost UInt32, ClickClientIP UInt32, ClickDomainID UInt32, ClickURL String, ClickAttempt UInt8, ClickOrderID UInt32, ClickBannerID UInt32, ClickMarketCategoryID UInt32, ClickMarketPP UInt32, ClickMarketCategoryName String, ClickMarketPPName String, ClickAWAPSCampaignName String, ClickPageName String, ClickTargetType UInt16, ClickTargetPhraseID UInt64, ClickContextType UInt8, ClickSelectType Int8, ClickOptions String, ClickGroupBannerID Int32, OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, FirstVisit DateTime, PredLastVisit Date, LastVisit Date, TotalVisits UInt32, TraficSource Nested(ID Int8, SearchEngineID UInt16, AdvEngineID UInt8, PlaceID UInt16, SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String), Attendance FixedString(16), CLID UInt32, YCLID UInt64, NormalizedRefererHash UInt64, SearchPhraseHash UInt64, RefererDomainHash UInt64, NormalizedStartURLHash UInt64, StartURLDomainHash UInt64, NormalizedEndURLHash UInt64, TopLevelDomain UInt64, URLScheme UInt64, OpenstatServiceNameHash UInt64, OpenstatCampaignIDHash UInt64, OpenstatAdIDHash UInt64, OpenstatSourceIDHash UInt64, UTMSourceHash UInt64, UTMMediumHash UInt64, UTMCampaignHash UInt64, UTMContentHash UInt64, UTMTermHash UInt64, FromHash UInt64, WebVisorEnabled UInt8, WebVisorActivity UInt32, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), Market Nested(Type UInt8, GoalID UInt32, OrderID String, OrderPrice Int64, PP UInt32, DirectPlaceID UInt32, DirectOrderID UInt32, DirectBannerID UInt32, GoodID String, GoodName String, GoodQuantity Int32, GoodPrice Int64), IslandID FixedString(16)) ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"
-- 導(dǎo)入數(shù)據(jù)【101】
cat visits_v1.tsv | clickhouse-client --query "INSERT INTO yqtest.visits_v1 FORMAT TSV" --max_insert_block_size=100000
-- 查看導(dǎo)入數(shù)據(jù)量，因?yàn)閮H在101導(dǎo)入，因此僅101存在【101】
yqtest1 :) select count(*) from visits_v1;
┌─count()─┐
│ 1681077 │
└─────────┘
創(chuàng)建本地表和分片表【所有機(jī)器】
本地表創(chuàng)建
clickhouse-client --query "CREATE TABLE yqtest.test_local ( CounterID UInt32, StartDate Date, Sign Int8, IsNew UInt8, VisitID UInt64, UserID UInt64, StartTime DateTime, Duration UInt32, UTCStartTime DateTime, PageViews Int32, Hits Int32, IsBounce UInt8, Referer String, StartURL String, RefererDomain String, StartURLDomain String, EndURL String, LinkURL String, IsDownload UInt8, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, PlaceID Int32, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), IsYandex UInt8, GoalReachesDepth Int32, GoalReachesURL Int32, GoalReachesAny Int32, SocialSourceNetworkID UInt8, SocialSourcePage String, MobilePhoneModel String, ClientEventTime DateTime, RegionID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RemoteIP UInt32, RemoteIP6 FixedString(16), IPNetworkID UInt32, SilverlightVersion3 UInt32, CodeVersion UInt32, ResolutionWidth UInt16, ResolutionHeight UInt16, UserAgentMajor UInt16, UserAgentMinor UInt16, WindowClientWidth UInt16, WindowClientHeight UInt16, SilverlightVersion2 UInt8, SilverlightVersion4 UInt16, FlashVersion3 UInt16, FlashVersion4 UInt16, ClientTimeZone Int16, OS UInt8, UserAgent UInt8, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, NetMajor UInt8, NetMinor UInt8, MobilePhone UInt8, SilverlightVersion1 UInt8, Age UInt8, Sex UInt8, Income UInt8, JavaEnable UInt8, CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, BrowserLanguage UInt16, BrowserCountry UInt16, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), Params Array(String), Goals Nested(ID UInt32, Serial UInt32, EventTime DateTime, Price Int64, OrderID String, CurrencyID UInt32), WatchIDs Array(UInt64), ParamSumPrice Int64, ParamCurrency FixedString(3), ParamCurrencyID UInt16, ClickLogID UInt64, ClickEventID Int32, ClickGoodEvent Int32, ClickEventTime DateTime, ClickPriorityID Int32, ClickPhraseID Int32, ClickPageID Int32, ClickPlaceID Int32, ClickTypeID Int32, ClickResourceID Int32, ClickCost UInt32, ClickClientIP UInt32, ClickDomainID UInt32, ClickURL String, ClickAttempt UInt8, ClickOrderID UInt32, ClickBannerID UInt32, ClickMarketCategoryID UInt32, ClickMarketPP UInt32, ClickMarketCategoryName String, ClickMarketPPName String, ClickAWAPSCampaignName String, ClickPageName String, ClickTargetType UInt16, ClickTargetPhraseID UInt64, ClickContextType UInt8, ClickSelectType Int8, ClickOptions String, ClickGroupBannerID Int32, OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, FirstVisit DateTime, PredLastVisit Date, LastVisit Date, TotalVisits UInt32, TraficSource Nested(ID Int8, SearchEngineID UInt16, AdvEngineID UInt8, PlaceID UInt16, SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String), Attendance FixedString(16), CLID UInt32, YCLID UInt64, NormalizedRefererHash UInt64, SearchPhraseHash UInt64, RefererDomainHash UInt64, NormalizedStartURLHash UInt64, StartURLDomainHash UInt64, NormalizedEndURLHash UInt64, TopLevelDomain UInt64, URLScheme UInt64, OpenstatServiceNameHash UInt64, OpenstatCampaignIDHash UInt64, OpenstatAdIDHash UInt64, OpenstatSourceIDHash UInt64, UTMSourceHash UInt64, UTMMediumHash UInt64, UTMCampaignHash UInt64, UTMContentHash UInt64, UTMTermHash UInt64, FromHash UInt64, WebVisorEnabled UInt8, WebVisorActivity UInt32, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), Market Nested(Type UInt8, GoalID UInt32, OrderID String, OrderPrice Int64, PP UInt32, DirectPlaceID UInt32, DirectOrderID UInt32, DirectBannerID UInt32, GoodID String, GoodName String, GoodQuantity Int32, GoodPrice Int64), IslandID FixedString(16)) ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192"
創(chuàng)建分片表
clickhouse-client --query "create table yqtest.test_all as yqtest.test_local ENGINE = Distributed(ckcluster_3shards_1replicas,yqtest,test_local,rand())"
插入數(shù)據(jù)
# 當(dāng)前表
yqtest1 :) show tables;
┌─name───────┐
│ test_all │ # 0
│ test_local │ # 0
│ visits_v1 │ # 1681077
└────────────┘
# 將數(shù)據(jù)插入分片表
insert into yqtest.test_all select * from visits_v1 limit 30000;
查看數(shù)據(jù)分布
- 查看本地表可看到數(shù)據(jù)被打散分布在3太機(jī)器上
- 查看分片表可看到在任意一臺(tái)數(shù)據(jù)量查詢(xún)到的都是相同的
-- 分別執(zhí)行
SELECT count(*) FROM yqtest.test_local
SELECT count(*) FROM yqtest.test_all
| 服務(wù)器 | 本地表[test_local] | 分片表[test_all] |
|---|---|---|
| 101 | 9983 | 30000 |
| 102 | 9966 | 30000 |
| 103 | 10051 | 30000 |
故障測(cè)試
假設(shè)【102】宕機(jī)，直接對(duì) 102 執(zhí)行了 poweroff
- 宕機(jī)任何一臺(tái)會(huì)導(dǎo)致整個(gè)集群不可用，分片表無(wú)法正常查詢(xún)
- 到每一臺(tái)機(jī)器上能查詢(xún)到local表的信息，但若102數(shù)據(jù)丟失，則扔為無(wú)用數(shù)據(jù)
- 102恢復(fù)后，集群恢復(fù)正常
-- 分別執(zhí)行
SELECT count(*) FROM yqtest.test_local
SELECT count(*) FROM yqtest.test_all
| 服務(wù)器 | 本地表[test_local] | 分片表[test_all] |
|---|---|---|
| 101 | 9983 | 報(bào)錯(cuò) |
| 102 | 宕機(jī) | 宕機(jī) |
| 103 | 10051 | 報(bào)錯(cuò) |
查詢(xún)分片表出現(xiàn)如下錯(cuò)誤：
← Progress: 2.00 rows, 8.21 KB (19.16 rows/s., 78.62 KB/s.)
Received exception from server (version 20.11.6):
Code: 279. DB::Exception: Received from localhost:9000. DB::Exception: All connection tries failed. Log:
Code: 32, e.displayText() = DB::Exception: Attempt to read after eof (version 20.11.6.6 (official build))
Code: 209, e.displayText() = DB::NetException: Timeout: connect timed out: 192.168.66.102:9000 (yqtest2:9000) (version 20.11.6.6 (official build))
Code: 209, e.displayText() = DB::NetException: Timeout: connect timed out: 192.168.66.102:9000 (yqtest2:9000) (version 20.11.6.6 (official build))
: While executing Remote.
0 rows in set. Elapsed: 0.299 sec.
