環(huán)境

每天機(jī)器已安裝單機(jī)版的ClickHouse，官方推薦使用 rpm 安裝，也可以安裝好一臺后復(fù)制文件過去
CentOS Linux release 7.8.2003 (Core)
ClickHouse 20.11.6.6
防火墻、selinux已關(guān)閉
注意： 這邊配置為<internal_replication>false</internal_replication>，官方推薦是true+復(fù)制表，但是需要部署ZooKeeper,當(dāng)前示例未部署，僅測試多副本的場景

分片	副本01	副本02
分片01	192.168.66.101:9000	192.168.66.104:9000
分片02	192.168.66.102:9000	192.168.66.105:9000
分片03	192.168.66.103:9000	192.168.66.106:9000

限制

單副本多分片情況下，數(shù)據(jù)會進(jìn)行分片存儲，但若有一臺機(jī)器分配掛掉，就會導(dǎo)致整個集群不可用。
因此需要對數(shù)據(jù)進(jìn)行一份備份，做高可用，就算一份數(shù)據(jù)不可用也可讀取另一份數(shù)據(jù)，高可用方案可以如下配置

方案一【本例使用】

每臺分片服務(wù)器后面都掛一臺高可用使用的復(fù)制機(jī)器

優(yōu)點(diǎn)：性能能跑滿，兩臺都能用到（會隨機(jī)從機(jī)器上獲取數(shù)據(jù)）
缺點(diǎn)：相對而言，比較費(fèi)機(jī)器

image.png

方案二

一臺服務(wù)器上安裝倆個clickhouse,端口不同，做倆倆復(fù)制,掛掉任何一臺服務(wù)器不影響整個集群

優(yōu)點(diǎn)：省機(jī)器吧
缺點(diǎn)：資源無法全負(fù)荷使用，會有資源爭用情況

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
echo "192.168.66.104 yqtest4" >> /etc/hosts
echo "192.168.66.105 yqtest5" >> /etc/hosts
echo "192.168.66.106 yqtest6" >> /etc/hosts

安裝【所有都安裝】

安裝略，可參考前篇文章，主要修改下面參數(shù)

<log>//clickhouse/log/clickhouse-server.log</log>
<errorlog>//clickhouse/log/clickhouse-server.err.log</errorlog>
<path>/clickhouse/data/</path>
<tmp_path>/clickhouse/data/tmp/</tmp_path>
<user_files_path>/clickhouse/data/user_files/</user_files_path>
<path>/clickhouse/data/access/</path>
<format_schema_path>/clickhouse/data/format_schemas/</format_schema_path>

集群配置

集群配置在配置文件中加入后立即生效

配置分片與副本信息【所有機(jī)器都需配置】

可配置完成一個后傳過去，也可以分別配置

vi /etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?>
<yandex>
    <!--集群相關(guān)配置-->
    <clickhouse_remote_servers>
        <!--自定義集群名稱 ckcluster_3shards_2replicas-->
        <ckcluster_3shards_2replicas>
            <!--分片1-->
            <shard>
                <internal_replication>false</internal_replication>
                <!--yqtest1 單副本-->
                <replica>
                    <host>yqtest1</host>
                    <port>9000</port>
                </replica>
                <!--yqtest4 單副本-->
                <replica>
                    <host>yqtest4</host>
                    <port>9000</port>
                </replica>
            </shard>
            <!--分片2-->
            <shard>
                <internal_replication>false</internal_replication>
                <!--yqtest2 單副本-->
                <replica>
                    <host>yqtest2</host>
                    <port>9000</port>
                </replica>
                <!--yqtest5 單副本-->
                <replica>
                    <host>yqtest5</host>
                    <port>9000</port>
                </replica>
            </shard>
        <!--分片3-->
            <shard>
                <internal_replication>false</internal_replication>
                <!--yqtest3 單副本-->
                <replica>
                    <host>yqtest3</host>
                    <port>9000</port>
                </replica>
                <!--yqtest6 單副本-->
                <replica>
                    <host>yqtest6</host>
                    <port>9000</port>
                </replica>
            </shard>
        </ckcluster_3shards_2replicas>
    </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)有多個副本集群時

創(chuàng)建的表為非復(fù)制表 <internal_replication>false</internal_replication>
寫分布式表：會將數(shù)據(jù)插入到所有副本的本地表中，副本表上的數(shù)據(jù)保持同步。寫入本地單表，數(shù)據(jù)僅寫入到當(dāng)前服務(wù)器單表上，會出現(xiàn)不同服務(wù)器查詢結(jié)果不同。
<internal_replication>true</internal_replication>
寫分布式表：數(shù)據(jù)只插入到一個副本的本地表中，不會做同步，數(shù)據(jù)紊亂，官方不推薦使用
創(chuàng)建的表為復(fù)制表
<internal_replication>false</internal_replication>
寫分布式表：數(shù)據(jù)會分片后插入到所有本地表中，會出現(xiàn)重復(fù)，但復(fù)制表會自動刪除重復(fù)，這有性能損耗
<internal_replication>true</internal_replication>
寫分布式表/本地表：會隨機(jī)寫一份到分片上，然后自動同步數(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.
      -->
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

查看集群信息【任何一臺】

可查看到一個分配有兩個副本

clickhouse-client -h 192.168.66.101 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";
clickhouse-client -h 192.168.66.102 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";
clickhouse-client -h 192.168.66.103 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";
clickhouse-client -h 192.168.66.104 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";
clickhouse-client -h 192.168.66.105 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";
clickhouse-client -h 192.168.66.106 --port 9000 --user default --query "select * from system.clusters where cluster = 'ckcluster_3shards_2replicas'";

image.png

is_local字段與ip匹配，這邊不一致一定要重視，否則可能出現(xiàn)查詢分片表數(shù)據(jù)不一致

下面這些test的集群可以修改clickhouse-server/config.xml中的

數(shù)據(jù)導(dǎo)入測試

導(dǎo)入測試數(shù)據(jù)【101】

官方測試數(shù)據(jù)下載地址：
【405M】https://datasets.clickhouse.tech/visits/tsv/visits_v1.tsv.xz
僅需傳到其中一臺服務(wù)器上即可，本例放在了 101 上，數(shù)據(jù)庫需要預(yù)先所有庫創(chuàng)建好

# 解壓，解壓后2.5G
unxz visits_v1.tsv.xz

-- 創(chuàng)建庫【所有】
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=50000

-- 查看導(dǎo)入數(shù)據(jù)量，因?yàn)閮H在101導(dǎo)入，因此僅101存在【101】
[root@yqtest1 ~]# clickhouse-client --query "select count(*) from yqtest.visits_v1"
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) 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_2replicas,yqtest,test_local,rand())"

插入數(shù)據(jù)【101】

# 當(dāng)前表
yqtest1 :) show tables;
┌─name───────┐
│ test_all   │ # 0
│ test_local │ # 0
│ visits_v1  │ # 1681077
└────────────┘

# 將數(shù)據(jù)插入分片表
insert into yqtest.test_all select CounterID,StartDate,Sign,IsNew,VisitID,UserID,StartTime from visits_v1 limit 30000;

查看數(shù)據(jù)分布

查看本地表可看到數(shù)據(jù)被打散分布在3太機(jī)器上，對應(yīng)的副本上的數(shù)據(jù)與這些機(jī)器上的相同
查看分片表可看到在任意一臺數(shù)據(jù)量查詢到的都是相同的

-- 分別執(zhí)行
SELECT count(*) FROM yqtest.test_local
SELECT count(*) FROM yqtest.test_all

服務(wù)器	本地表[test_local]	分片表[test_all]
101	10008	30000
104	10008	30000
102	9924	30000
105	9924	30000
103	10068	30000
106	10068	30000

故障測試

假設(shè)【103】宕機(jī)，直接對 103 執(zhí)行了 poweroff

宕機(jī)任何一臺由于存在副本，不會出現(xiàn)集群不可用的情況，分片表可正常查詢，但測試發(fā)現(xiàn)首次查詢分片表時會拉取數(shù)據(jù)，會變慢，后面查詢就好了。
集群正常使用
關(guān)于宕機(jī)后首次查詢分片表變慢問題：

image.png

查看日志，做了合并聚合操作

image.png

-- 分別執(zhí)行
SELECT count(*) FROM yqtest.test_local
SELECT count(*) FROM yqtest.test_all

服務(wù)器	本地表[test_local]	分片表[test_all]
101	10008	30000
104	10008	30000
102	9924	30000
105	9924	30000
103	宕機(jī)	宕機(jī)
106	10068	30000

宕機(jī)后繼續(xù)有數(shù)據(jù)寫入分片表

【103】已宕機(jī)，繼續(xù)對分片表寫入了部分?jǐn)?shù)據(jù)，查看恢復(fù)后的【103】

insert into yqtest.test_all select CounterID,StartDate,Sign,IsNew,VisitID,UserID,StartTime from yqtest.visits_v1 limit 300;

服務(wù)器	本地表[test_local]	分片表[test_all]
101	10100	30300
104	10100	30300
102	10088	30300
105	10088	30300
103	宕機(jī)	宕機(jī)
106	10112	30300

【103】恢復(fù)后

服務(wù)器	本地表[test_local]	分片表[test_all]
101	10100	30300
104	10100	30300
102	10088	30300
105	10088	30300
103	10112	30300	# 恢復(fù)后數(shù)據(jù)會自動同步
106	10112	30300

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

ClickHouse 分片雙副本集群部署

ClickHouse 分片雙副本集群部署

環(huán)境

限制

方案一【本例使用】

方案二

配置host【所有機(jī)器配置】

安裝【所有都安裝】

集群配置

配置分片與副本信息【所有機(jī)器都需配置】

配置信息引入config文件【所有服務(wù)器配置】

查看集群信息【任何一臺】

數(shù)據(jù)導(dǎo)入測試

導(dǎo)入測試數(shù)據(jù)【101】

創(chuàng)建本地表和分片表【所有機(jī)器】

本地表創(chuàng)建

創(chuàng)建分片表

插入數(shù)據(jù)【101】

查看數(shù)據(jù)分布

故障測試

假設(shè)【103】宕機(jī)，直接對 103 執(zhí)行了 poweroff

宕機(jī)后繼續(xù)有數(shù)據(jù)寫入分片表

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

ClickHouse 分片雙副本集群部署

環(huán)境

限制

方案一【本例使用】

方案二

配置host【所有機(jī)器配置】

安裝【所有都安裝】

集群配置

配置分片與副本信息【所有機(jī)器都需配置】

配置信息引入config文件【所有服務(wù)器配置】

查看集群信息【任何一臺】

數(shù)據(jù)導(dǎo)入測試

導(dǎo)入測試數(shù)據(jù)【101】

創(chuàng)建本地表和分片表【所有機(jī)器】

本地表創(chuàng)建

創(chuàng)建分片表

插入數(shù)據(jù)【101】

查看數(shù)據(jù)分布

故障測試

假設(shè)【103】宕機(jī)，直接對 103 執(zhí)行了 poweroff

宕機(jī)后繼續(xù)有數(shù)據(jù)寫入分片表

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av