Ua hoʻomākaukau ʻia ka unuhi ʻana o ka ʻatikala no nā haumāna o ka papa .
he waihona kolamu kumu wehe. He wahi maikaʻi loa ia e hiki ai i nā haneli o nā mea loiloi ke nīnau wikiwiki i ka ʻikepili kikoʻī, ʻoiai ke hoʻokomo ʻia nā ʻumi piliona o nā moʻolelo hou i kēlā me kēia lā. Hiki ke kiʻekiʻe ke kumukūʻai o ka ʻoihana no ke kākoʻo ʻana i ia ʻōnaehana ma kahi o $100 i kēlā me kēia makahiki, a ʻo ka hapalua paha e pili ana i ka hoʻohana ʻana. I kekahi manawa, ua loaʻa i ka ClickHouse kahi hoʻonohonoho mai Yandex Metrics he 10 trillion moʻolelo. Ma waho aʻe o Yandex, ua loaʻa pū ʻo ClickHouse i ka kūleʻa me Bloomberg a me Cloudflare.
ʻElua makahiki i hala aku nei ua hoʻohana au ʻikepili me ka hoʻohana ʻana i hoʻokahi mīkini, a lilo ia manuahi waihona lako polokalamu aʻu i ʻike ai. Mai ia manawa, ʻaʻole i ho'ōki nā mea hoʻomohala i ka hoʻohui ʻana i nā hiʻohiʻona, me ke kākoʻo no Kafka, HDFS a me ZStandard compression. I ka makahiki i hala ua hoʻohui lākou i ke kākoʻo no nā ʻano hoʻopiʻi cascading, a ua hiki ke coding. Ke kaomi ʻana i ka ʻikepili manawa, hiki ke hoʻopili maikaʻi ʻia nā waiwai ana me ka hoʻohana ʻana i ka delta encoding, akā no nā helu helu ʻoi aku ka maikaʻi o ka hoʻohana ʻana i ka delta-by-delta encoding. Ua lilo ka hoʻopili maikaʻi i ke kī i ka hana a ClickHouse.
Aia ʻo ClickHouse he 170 tausani laina o ka code C++, me ka ʻole o nā hale waihona puke ʻaoʻao ʻekolu, a ʻo ia kekahi o nā codebase waihona liʻiliʻi loa. I ka hoʻohālikelike ʻana, ʻaʻole kākoʻo ʻo SQLite i ka hāʻawi ʻana a loaʻa iā 235 tausani mau laina o ka code C. Ma kēia kākau ʻana, ua hāʻawi nā mea ʻenehana 207 iā ClickHouse, a ke piʻi nei ka ikaika o nā hana i kēia manawa.
I Malaki 2017, ua hoʻomaka ʻo ClickHouse e alakaʻi ma ke ʻano he ala maʻalahi e mālama i ka hoʻomohala ʻana. Ua wāwahi pū lākou i ka faila palapala monolithic i kahi hierarchy file e pili ana iā Markdown. Hoʻopili ʻia nā pilikia a me nā hiʻohiʻona ma o GitHub, a ma ke ʻano maʻamau ua maʻalahi ka polokalamu i nā makahiki i hala.
Ma kēia ʻatikala, e nānā wau i ka hana o kahi hui ClickHouse ma AWS EC2 me ka hoʻohana ʻana i nā kaʻina hana 36-core a me ka waiho ʻana o NVMe.
UPDATE: I hoʻokahi pule ma hope o ka hoʻopuka mua ʻana i kēia pou, ua hana hou au i ka hoʻāʻo me kahi hoʻonohonoho hoʻomaikaʻi a loaʻa i nā hopena ʻoi aku ka maikaʻi. Ua hōʻano hou ʻia kēia pou e hōʻike i kēia mau loli.
Ke hoʻomaka nei i kahi AWS EC2 Cluster
E hoʻohana wau i ʻekolu mau manawa c5d.9xlarge EC2 no kēia pou. Loaʻa i kēlā me kēia o lākou nā CPU virtual 36, 72 GB o RAM, 900 GB o NVMe SSD mālama a kākoʻo i ka pūnaewele 10 Gigabit. Ua kūʻai lākou i $1,962 / hola i kēlā me kēia ma ka ʻāina eu-west-1 i ka wā e holo ana ma ke koi. E hoʻohana wau i ka Ubuntu Server 16.04 LTS e like me ka ʻōnaehana hana.
Hoʻonohonoho ʻia ka pā ahi i hiki i kēlā me kēia mīkini ke kamaʻilio me kekahi i kekahi me ka ʻole o ke kaohi ʻana, a ʻo kaʻu helu IPv4 wale nō i hoʻopaʻa inoa ʻia e SSH i loko o ka pūpū.
Hoʻokele NVMe i ke kūlana mākaukau hana
No ka hana ʻana o ClickHouse, e hana wau i kahi ʻōnaehana faila ma ka format EXT4 ma kahi kaʻa NVMe ma kēlā me kēia kikowaena.
$ sudo mkfs -t ext4 /dev/nvme1n1
$ sudo mkdir /ch
$ sudo mount /dev/nvme1n1 /chKe hoʻonohonohoʻia nā mea a pau, hiki iāʻoe keʻike i ka helu mauna a me ka 783 GB o ka hakahaka i loaʻa ma kēlā me kēia pūnaewele.
$ lsblkNAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
loop0 7:0 0 87.9M 1 loop /snap/core/5742
loop1 7:1 0 16.5M 1 loop /snap/amazon-ssm-agent/784
nvme0n1 259:1 0 8G 0 disk
└─nvme0n1p1 259:2 0 8G 0 part /
nvme1n1 259:0 0 838.2G 0 disk /ch$ df -hFilesystem Size Used Avail Use% Mounted on
udev 35G 0 35G 0% /dev
tmpfs 6.9G 8.8M 6.9G 1% /run
/dev/nvme0n1p1 7.7G 967M 6.8G 13% /
tmpfs 35G 0 35G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 35G 0 35G 0% /sys/fs/cgroup
/dev/loop0 88M 88M 0 100% /snap/core/5742
/dev/loop1 17M 17M 0 100% /snap/amazon-ssm-agent/784
tmpfs 6.9G 0 6.9G 0% /run/user/1000
/dev/nvme1n1 825G 73M 783G 1% /chʻO ka ʻikepili aʻu e hoʻohana ai i kēia hoʻāʻo ʻana he waihona ʻikepili aʻu i hana ai mai 1.1 biliona holo kaʻa i lawe ʻia ma New York City i ʻeono makahiki. Ma ka blog kikoʻī pehea aʻu i hōʻiliʻili ai i kēia pūʻulu ʻikepili. Mālama ʻia lākou ma AWS S3, no laila e hoʻonohonoho wau i ka AWS CLI me kaʻu komo a me nā kī huna.
$ sudo apt update
$ sudo apt install awscli
$ aws configureE hoʻonoho au i ka palena noi like a ka mea kūʻai aku i 100 i ʻoi aku ka wikiwiki o ka hoʻoiho ʻana i nā faila ma mua o nā hoʻonohonoho paʻamau.
$ aws configure set
default.s3.max_concurrent_requests
100E hoʻoiho au i ka ʻikepili kaʻa kaʻa mai AWS S3 a mālama iā ia ma kahi kaʻa NVMe ma ke kikowaena mua. ʻO kēia ʻikepili he ~104GB ma GZIP-compressed CSV format.
$ sudo mkdir -p /ch/csv
$ sudo chown -R ubuntu /ch/csv
$ aws s3 sync s3://<bucket>/csv /ch/csvHoʻokomo ʻia ʻo ClickHouse
E hoʻokomo wau i ka hāʻawi OpenJDK no Java 8 e like me ka mea e koi ʻia e holo Apache ZooKeeper, kahi i koi ʻia no ka hoʻokomo ʻana o ClickHouse ma nā mīkini ʻekolu.
$ sudo apt update
$ sudo apt install
openjdk-8-jre
openjdk-8-jdk-headless
A laila hoʻonoho wau i ka hoʻololi kaiapuni JAVA_HOME.
$ sudo vi /etc/profile
export JAVA_HOME=/usr
$ source /etc/profileE hoʻohana au i ka ʻōnaehana hoʻokele pūʻolo o Ubuntu e hoʻokomo iā ClickHouse 18.16.1, glances a me ZooKeeper ma nā mīkini ʻekolu.
$ sudo apt-key adv
--keyserver hkp://keyserver.ubuntu.com:80
--recv E0C56BD4
$ echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" |
sudo tee /etc/apt/sources.list.d/clickhouse.list
$ sudo apt-get update$ sudo apt install
clickhouse-client
clickhouse-server
glances
zookeeperdE hana wau i papa kuhikuhi no ClickHouse a e hana pū kekahi i nā hoʻonohonoho hoʻonohonoho ma nā kikowaena ʻekolu.
$ sudo mkdir /ch/clickhouse
$ sudo chown -R clickhouse /ch/clickhouse
$ sudo mkdir -p /etc/clickhouse-server/conf.d
$ sudo vi /etc/clickhouse-server/conf.d/taxis.confʻO kēia nā hoʻonohonoho hoʻonohonoho aʻu e hoʻohana ai.
<?xml version="1.0"?>
<yandex>
<listen_host>0.0.0.0</listen_host>
<path>/ch/clickhouse/</path>
<remote_servers>
<perftest_3shards>
<shard>
<replica>
<host>172.30.2.192</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>172.30.2.162</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>172.30.2.36</host>
<port>9000</port>
</replica>
</shard>
</perftest_3shards>
</remote_servers> <zookeeper-servers>
<node>
<host>172.30.2.192</host>
<port>2181</port>
</node>
<node>
<host>172.30.2.162</host>
<port>2181</port>
</node>
<node>
<host>172.30.2.36</host>
<port>2181</port>
</node>
</zookeeper-servers> <macros>
<shard>03</shard>
<replica>01</replica>
</macros>
</yandex>E holo wau iā ZooKeeper a me ka server ClickHouse ma nā mīkini ʻekolu.
$ sudo /etc/init.d/zookeeper start
$ sudo service clickhouse-server startHoʻouka i ka ʻikepili i ClickHouse
Ma ke kikowaena mua e hana au i papaʻaina huakaʻi (trips), ka mea e mālama i kahi papa helu o nā huakaʻi kaʻa me ka hoʻohana ʻana i ka mīkini Log.
$ clickhouse-client --host=0.0.0.0
CREATE TABLE trips (
trip_id UInt32,
vendor_id String,
pickup_datetime DateTime,
dropoff_datetime Nullable(DateTime),
store_and_fwd_flag Nullable(FixedString(1)),
rate_code_id Nullable(UInt8),
pickup_longitude Nullable(Float64),
pickup_latitude Nullable(Float64),
dropoff_longitude Nullable(Float64),
dropoff_latitude Nullable(Float64),
passenger_count Nullable(UInt8),
trip_distance Nullable(Float64),
fare_amount Nullable(Float32),
extra Nullable(Float32),
mta_tax Nullable(Float32),
tip_amount Nullable(Float32),
tolls_amount Nullable(Float32),
ehail_fee Nullable(Float32),
improvement_surcharge Nullable(Float32),
total_amount Nullable(Float32),
payment_type Nullable(String),
trip_type Nullable(UInt8),
pickup Nullable(String),
dropoff Nullable(String),
cab_type Nullable(String),
precipitation Nullable(Int8),
snow_depth Nullable(Int8),
snowfall Nullable(Int8),
max_temperature Nullable(Int8),
min_temperature Nullable(Int8),
average_wind_speed Nullable(Int8),
pickup_nyct2010_gid Nullable(Int8),
pickup_ctlabel Nullable(String),
pickup_borocode Nullable(Int8),
pickup_boroname Nullable(String),
pickup_ct2010 Nullable(String),
pickup_boroct2010 Nullable(String),
pickup_cdeligibil Nullable(FixedString(1)),
pickup_ntacode Nullable(String),
pickup_ntaname Nullable(String),
pickup_puma Nullable(String),
dropoff_nyct2010_gid Nullable(UInt8),
dropoff_ctlabel Nullable(String),
dropoff_borocode Nullable(UInt8),
dropoff_boroname Nullable(String),
dropoff_ct2010 Nullable(String),
dropoff_boroct2010 Nullable(String),
dropoff_cdeligibil Nullable(String),
dropoff_ntacode Nullable(String),
dropoff_ntaname Nullable(String),
dropoff_puma Nullable(String)
) ENGINE = Log;
A laila unuhi au a hoʻouka i kēlā me kēia o nā faila CSV i kahi papaʻaina huakaʻi (trips). Ua hoʻopau ʻia kēia mau mea i loko o 55 mau minuke a me 10 kekona. Ma hope o kēia hana, ʻo ka nui o ka papa kuhikuhi ʻikepili he 134 GB.
$ time (for FILENAME in /ch/csv/trips_x*.csv.gz; do
echo $FILENAME
gunzip -c $FILENAME |
clickhouse-client
--host=0.0.0.0
--query="INSERT INTO trips FORMAT CSV"
done)ʻO 155 MB ka wikiwiki o ka lawe ʻana mai o ka maʻiʻo CSV paʻa ʻole i kēlā me kēia kekona. Manaʻo wau no ka bottleneck i ka decompression GZIP. Ua ʻoi aku ka wikiwiki o ka wehe ʻana i nā faila gzip a pau me ka hoʻohana ʻana i xargs a laila hoʻouka i ka ʻikepili unzipped. Aia ma lalo kahi wehewehe o ka mea i hōʻike ʻia i ka wā o ka hoʻokomo ʻana i ka CSV.
$ sudo glancesip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 0:11:42
CPU 8.2% nice: 0.0% LOAD 36-core MEM 9.8% active: 5.20G SWAP 0.0%
user: 6.0% irq: 0.0% 1 min: 2.24 total: 68.7G inactive: 61.0G total: 0
system: 0.9% iowait: 1.3% 5 min: 1.83 used: 6.71G buffers: 66.4M used: 0
idle: 91.8% steal: 0.0% 15 min: 1.01 free: 62.0G cached: 61.6G free: 0
NETWORK Rx/s Tx/s TASKS 370 (507 thr), 2 run, 368 slp, 0 oth sorted automatically by cpu_percent, flat view
ens5 136b 2Kb
lo 343Mb 343Mb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command
100.4 1.5 1.65G 1.06G 9909 ubuntu 0 S 1:01.33 0 0 clickhouse-client --host=0.0.0.0 --query=INSERT INTO trips FORMAT CSV
DISK I/O R/s W/s 85.1 0.0 4.65M 708K 9908 ubuntu 0 R 0:50.60 32M 0 gzip -d -c /ch/csv/trips_xac.csv.gz
loop0 0 0 54.9 5.1 8.14G 3.49G 8091 clickhous 0 S 1:44.23 0 45M /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml
loop1 0 0 4.5 0.0 0 0 319 root 0 S 0:07.50 1K 0 kworker/u72:2
nvme0n1 0 3K 2.3 0.0 91.1M 28.9M 9912 root 0 R 0:01.56 0 0 /usr/bin/python3 /usr/bin/glances
nvme0n1p1 0 3K 0.3 0.0 0 0 960 root -20 S 0:00.10 0 0 kworker/28:1H
nvme1n1 32.1M 495M 0.3 0.0 0 0 1058 root -20 S 0:00.90 0 0 kworker/23:1HE hoʻokuʻu au i ka lewa ma ka NVMe drive ma ka holoi ʻana i nā faila CSV kumu ma mua o ka hoʻomau.
$ sudo rm -fr /ch/csvE hoʻohuli i ke ʻano kolamu
E mālama ka ʻenekini Log ClickHouse i ka ʻikepili ma kahi ʻano lālani. No ka huli ʻana i ka ʻikepili me ka wikiwiki, hoʻololi au iā ia i ke ʻano columnar me ka hoʻohana ʻana i ka mīkini MergeTree.
$ clickhouse-client --host=0.0.0.0Ua hoʻopau ʻia kēia mau mea i loko o 34 mau minuke a me 50 kekona. Ma hope o kēia hana, ʻo ka nui o ka papa kuhikuhi ʻikepili he 237 GB.
CREATE TABLE trips_mergetree
ENGINE = MergeTree(pickup_date, pickup_datetime, 8192)
AS SELECT
trip_id,
CAST(vendor_id AS Enum8('1' = 1,
'2' = 2,
'CMT' = 3,
'VTS' = 4,
'DDS' = 5,
'B02512' = 10,
'B02598' = 11,
'B02617' = 12,
'B02682' = 13,
'B02764' = 14)) AS vendor_id,
toDate(pickup_datetime) AS pickup_date,
ifNull(pickup_datetime, toDateTime(0)) AS pickup_datetime,
toDate(dropoff_datetime) AS dropoff_date,
ifNull(dropoff_datetime, toDateTime(0)) AS dropoff_datetime,
assumeNotNull(store_and_fwd_flag) AS store_and_fwd_flag,
assumeNotNull(rate_code_id) AS rate_code_id,
assumeNotNull(pickup_longitude) AS pickup_longitude,
assumeNotNull(pickup_latitude) AS pickup_latitude,
assumeNotNull(dropoff_longitude) AS dropoff_longitude,
assumeNotNull(dropoff_latitude) AS dropoff_latitude,
assumeNotNull(passenger_count) AS passenger_count,
assumeNotNull(trip_distance) AS trip_distance,
assumeNotNull(fare_amount) AS fare_amount,
assumeNotNull(extra) AS extra,
assumeNotNull(mta_tax) AS mta_tax,
assumeNotNull(tip_amount) AS tip_amount,
assumeNotNull(tolls_amount) AS tolls_amount,
assumeNotNull(ehail_fee) AS ehail_fee,
assumeNotNull(improvement_surcharge) AS improvement_surcharge,
assumeNotNull(total_amount) AS total_amount,
assumeNotNull(payment_type) AS payment_type_,
assumeNotNull(trip_type) AS trip_type,
pickup AS pickup,
pickup AS dropoff,
CAST(assumeNotNull(cab_type)
AS Enum8('yellow' = 1, 'green' = 2))
AS cab_type,
precipitation AS precipitation,
snow_depth AS snow_depth,
snowfall AS snowfall,
max_temperature AS max_temperature,
min_temperature AS min_temperature,
average_wind_speed AS average_wind_speed,
pickup_nyct2010_gid AS pickup_nyct2010_gid,
pickup_ctlabel AS pickup_ctlabel,
pickup_borocode AS pickup_borocode,
pickup_boroname AS pickup_boroname,
pickup_ct2010 AS pickup_ct2010,
pickup_boroct2010 AS pickup_boroct2010,
pickup_cdeligibil AS pickup_cdeligibil,
pickup_ntacode AS pickup_ntacode,
pickup_ntaname AS pickup_ntaname,
pickup_puma AS pickup_puma,
dropoff_nyct2010_gid AS dropoff_nyct2010_gid,
dropoff_ctlabel AS dropoff_ctlabel,
dropoff_borocode AS dropoff_borocode,
dropoff_boroname AS dropoff_boroname,
dropoff_ct2010 AS dropoff_ct2010,
dropoff_boroct2010 AS dropoff_boroct2010,
dropoff_cdeligibil AS dropoff_cdeligibil,
dropoff_ntacode AS dropoff_ntacode,
dropoff_ntaname AS dropoff_ntaname,
dropoff_puma AS dropoff_puma
FROM trips;ʻO kēia ke ʻano o ka puka aniani i ka wā o ka hana:
ip-172-30-2-200 (Ubuntu 16.04 64bit / Linux 4.4.0-1072-aws) Uptime: 1:06:09
CPU 10.3% nice: 0.0% LOAD 36-core MEM 16.1% active: 13.3G SWAP 0.0%
user: 7.9% irq: 0.0% 1 min: 1.87 total: 68.7G inactive: 52.8G total: 0
system: 1.6% iowait: 0.8% 5 min: 1.76 used: 11.1G buffers: 71.8M used: 0
idle: 89.7% steal: 0.0% 15 min: 1.95 free: 57.6G cached: 57.2G free: 0
NETWORK Rx/s Tx/s TASKS 367 (523 thr), 1 run, 366 slp, 0 oth sorted automatically by cpu_percent, flat view
ens5 1Kb 8Kb
lo 2Kb 2Kb CPU% MEM% VIRT RES PID USER NI S TIME+ IOR/s IOW/s Command
241.9 12.8 20.7G 8.78G 8091 clickhous 0 S 30:36.73 34M 125M /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml
DISK I/O R/s W/s 2.6 0.0 90.4M 28.3M 9948 root 0 R 1:18.53 0 0 /usr/bin/python3 /usr/bin/glances
loop0 0 0 1.3 0.0 0 0 203 root 0 S 0:09.82 0 0 kswapd0
loop1 0 0 0.3 0.1 315M 61.3M 15701 ubuntu 0 S 0:00.40 0 0 clickhouse-client --host=0.0.0.0
nvme0n1 0 3K 0.3 0.0 0 0 7 root 0 S 0:00.83 0 0 rcu_sched
nvme0n1p1 0 3K 0.0 0.0 0 0 142 root 0 S 0:00.22 0 0 migration/27
nvme1n1 25.8M 330M 0.0 0.0 59.7M 1.79M 2764 ubuntu 0 S 0:00.00 0 0 (sd-pam)Ma ka ho'āʻo hope loa, ua hoʻololi a helu hou ʻia kekahi mau kolamu. Ua ʻike au ʻaʻole hana hou kekahi o kēia mau hana e like me ka mea i manaʻo ʻia ma kēia waihona. No ka hoʻoponopono ʻana i kēia pilikia, ua wehe au i nā hana kūpono ʻole a hoʻouka i ka ʻikepili me ka ʻole o ka hoʻololi ʻana i nā ʻano granular.
Ka hāʻawi ʻana i ka ʻikepili ma ka pūʻulu
E puʻunaue au i ka ʻikepili ma waena o nā pūnana puʻupuʻu ʻekolu. No ka hoʻomaka, ma lalo wau e hana i kahi papaʻaina ma nā mīkini ʻekolu.
$ clickhouse-client --host=0.0.0.0CREATE TABLE trips_mergetree_third (
trip_id UInt32,
vendor_id String,
pickup_date Date,
pickup_datetime DateTime,
dropoff_date Date,
dropoff_datetime Nullable(DateTime),
store_and_fwd_flag Nullable(FixedString(1)),
rate_code_id Nullable(UInt8),
pickup_longitude Nullable(Float64),
pickup_latitude Nullable(Float64),
dropoff_longitude Nullable(Float64),
dropoff_latitude Nullable(Float64),
passenger_count Nullable(UInt8),
trip_distance Nullable(Float64),
fare_amount Nullable(Float32),
extra Nullable(Float32),
mta_tax Nullable(Float32),
tip_amount Nullable(Float32),
tolls_amount Nullable(Float32),
ehail_fee Nullable(Float32),
improvement_surcharge Nullable(Float32),
total_amount Nullable(Float32),
payment_type Nullable(String),
trip_type Nullable(UInt8),
pickup Nullable(String),
dropoff Nullable(String),
cab_type Nullable(String),
precipitation Nullable(Int8),
snow_depth Nullable(Int8),
snowfall Nullable(Int8),
max_temperature Nullable(Int8),
min_temperature Nullable(Int8),
average_wind_speed Nullable(Int8),
pickup_nyct2010_gid Nullable(Int8),
pickup_ctlabel Nullable(String),
pickup_borocode Nullable(Int8),
pickup_boroname Nullable(String),
pickup_ct2010 Nullable(String),
pickup_boroct2010 Nullable(String),
pickup_cdeligibil Nullable(FixedString(1)),
pickup_ntacode Nullable(String),
pickup_ntaname Nullable(String),
pickup_puma Nullable(String),
dropoff_nyct2010_gid Nullable(UInt8),
dropoff_ctlabel Nullable(String),
dropoff_borocode Nullable(UInt8),
dropoff_boroname Nullable(String),
dropoff_ct2010 Nullable(String),
dropoff_boroct2010 Nullable(String),
dropoff_cdeligibil Nullable(String),
dropoff_ntacode Nullable(String),
dropoff_ntaname Nullable(String),
dropoff_puma Nullable(String)
) ENGINE = MergeTree(pickup_date, pickup_datetime, 8192);A laila, e hōʻoia wau e hiki i ke kikowaena mua ke ʻike i nā nodes ʻekolu i loko o ka pūpū.
SELECT *
FROM system.clusters
WHERE cluster = 'perftest_3shards'
FORMAT Vertical;Row 1:
──────
cluster: perftest_3shards
shard_num: 1
shard_weight: 1
replica_num: 1
host_name: 172.30.2.192
host_address: 172.30.2.192
port: 9000
is_local: 1
user: default
default_database:Row 2:
──────
cluster: perftest_3shards
shard_num: 2
shard_weight: 1
replica_num: 1
host_name: 172.30.2.162
host_address: 172.30.2.162
port: 9000
is_local: 0
user: default
default_database:Row 3:
──────
cluster: perftest_3shards
shard_num: 3
shard_weight: 1
replica_num: 1
host_name: 172.30.2.36
host_address: 172.30.2.36
port: 9000
is_local: 0
user: default
default_database:
A laila, e wehewehe au i kahi papaʻaina hou ma ke kikowaena mua e pili ana i ka schema trips_mergetree_third a hoʻohana i ka ʻenekini Distributed.
CREATE TABLE trips_mergetree_x3
AS trips_mergetree_third
ENGINE = Distributed(perftest_3shards,
default,
trips_mergetree_third,
rand());A laila e kope wau i ka ʻikepili mai ka papa ʻaina MergeTree i nā kikowaena ʻekolu. Ua hoʻopau ʻia kēia mau mea i loko o 34 mau minuke a me 44 kekona.
INSERT INTO trips_mergetree_x3
SELECT * FROM trips_mergetree;Ma hope o ka hana ma luna, ua hāʻawi wau iā ClickHouse 15 mau minuke e neʻe aku mai ka māka pae kiʻekiʻe loa. Ua pau nā papa kuhikuhi ʻikepili i ka 264 GB, 34 GB a me 33 GB ma kēlā me kēia o nā kikowaena ʻekolu.
ClickHouse cluster hana loiloi
ʻO kaʻu mea i ʻike ai aʻe, ʻo ia ka manawa wikiwiki loa aʻu i ʻike ai e holo ana i kēlā me kēia nīnau ma ka papaʻaina i nā manawa he nui trips_mergetree_x3.
$ clickhouse-client --host=0.0.0.0Ua hoʻopau ʻia ka mea ma hope i 2.449 kekona.
SELECT cab_type, count(*)
FROM trips_mergetree_x3
GROUP BY cab_type;Ua hoʻopau ʻia ka mea ma hope i 0.691 kekona.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree_x3
GROUP BY passenger_count;Ua hoʻopau ʻia kēia mau mea i 0 kekona.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year;Ua hoʻopau ʻia ka mea ma hope i 0.983 kekona.
SELECT passenger_count,
toYear(pickup_date) AS year,
round(trip_distance) AS distance,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year,
distance
ORDER BY year,
count(*) DESC;No ka hoʻohālikelike, holo wau i nā nīnau like ma kahi papa ʻaina MergeTree e noho wale ana ma ke kikowaena mua.
Ka loiloi hana o hoʻokahi node ClickHouse
ʻO kaʻu mea i ʻike ai aʻe, ʻo ia ka manawa wikiwiki loa aʻu i ʻike ai e holo ana i kēlā me kēia nīnau ma ka papaʻaina i nā manawa he nui trips_mergetree_x3.
Ua hoʻopau ʻia ka mea ma hope i 0.241 kekona.
SELECT cab_type, count(*)
FROM trips_mergetree
GROUP BY cab_type;Ua hoʻopau ʻia ka mea ma hope i 0.826 kekona.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree
GROUP BY passenger_count;Ua hoʻopau ʻia ka mea ma hope i 1.209 kekona.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year;Ua hoʻopau ʻia ka mea ma hope i 1.781 kekona.
SELECT passenger_count,
toYear(pickup_date) AS year,
round(trip_distance) AS distance,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year,
distance
ORDER BY year,
count(*) DESC;Nā noʻonoʻo i nā hopena
ʻO kēia ka manawa mua i hiki ai i kahi waihona waihona CPU manuahi ke hoʻokō i kahi waihona waihona GPU i kaʻu mau hoʻokolohua. Ua hele aku kēlā waihona waihona GPU i ʻelua mau hoʻoponopono mai ia manawa, akā ʻo ka hana a ClickHouse i hāʻawi ʻia ma kahi node hoʻokahi he mea kupanaha loa.
I ka manawa like, i ka wā e hoʻokō ai i ka Query 1 ma kahi ʻenekini puʻunaue, ʻoi aku ka nui o nā kumukūʻai ma luna. Manaʻo wau ua hala kekahi mea i kaʻu noiʻi no kēia pou no ka mea maikaʻi ke ʻike i ka iho ʻana o nā manawa nīnau i ka wā e hoʻohui ai au i nā nodes i ka hui. Eia nō naʻe, maikaʻi ia i ka wā e holo ana i nā nīnau ʻē aʻe, hoʻonui ʻia ka hana ma kahi o 2 mau manawa.
He mea maikaʻi ke ʻike i ka ulu ʻana o ClickHouse i ka hiki ke hoʻokaʻawale i ka waiho ʻana a me ka helu ʻana i hiki iā lākou ke hoʻonui kūʻokoʻa. ʻO ke kākoʻo HDFS, i hoʻohui ʻia i ka makahiki i hala, hiki ke hana i kēia. Ma ke ʻano o ka helu ʻana, inā hiki ke hoʻolōʻihi ʻia kahi nīnau hoʻokahi ma ka hoʻohui ʻana i nā nodes hou aʻe i ka pūʻulu, a laila ʻoi aku ka mālamalama o ka wā e hiki mai ana o kēia polokalamu.
Mahalo iā ʻoe no ka lawe ʻana i ka manawa e heluhelu ai i kēia pou. Hāʻawi wau i nā lawelawe kūkākūkā, hoʻolālā, a hoʻomaʻamaʻa hoʻomohala i nā mea kūʻai aku ma ʻAmelika ʻĀkau a me ʻEulopa. Inā makemake ʻoe e kūkākūkā pehea e hiki ai i kaʻu mau manaʻo ke kōkua i kāu ʻoihana, e ʻoluʻolu e kelepona mai iaʻu ma .
Source: www.habr.com