Chaidh eadar-theangachadh an artaigil ullachadh gu sònraichte airson oileanaich a’ chùrsa .
na stòr-dàta colbhan fosgailte. Is e àrainneachd fìor mhath a th’ ann far am faod ceudan de luchd-anailis ceasnachadh gu sgiobalta air dàta mionaideach, eadhon mar a thèid deichean de bhilleanan de chlàran ùra a chuir a-steach gach latha. Dh’ fhaodadh cosgaisean bun-structair airson taic a thoirt do leithid de shiostam a bhith cho àrd ri $100 sa bhliadhna, agus is dòcha leth sin a rèir cleachdadh. Aig aon àm, bha 10 trillean clàr anns an stàladh ClickHouse bho Yandex Metrics. A bharrachd air Yandex, tha ClickHouse cuideachd air soirbheachadh le Bloomberg agus Cloudflare.
Dà bhliadhna air ais chuir mi seachad stòran-dàta a’ cleachdadh aon inneal, agus thàinig e gu bhith Stòr-dàta bathar-bog a chunnaic mi a-riamh an-asgaidh. Bhon uairsin, tha luchd-leasachaidh air leantainn orra a’ cur feartan ris, a’ toirt a-steach taic airson teannachadh Kafka, HDFS agus ZStandard. An-uiridh chuir iad taic ris airson dòighean teannachaidh cascading, agus dh'fhàs còdadh comasach. Nuair a bhios tu a’ teannachadh dàta sreath ùine, faodar luachan tomhais a dhlùthadh gu math le bhith a’ cleachdadh còdachadh delta, ach airson cunntairean bhiodh e na b’ fheàrr còdachadh delta-by-delta a chleachdadh. Tha teannachadh math air a thighinn gu bhith na phrìomh dhòigh air coileanadh ClickHouse.
Tha ClickHouse air a dhèanamh suas de 170 mìle loidhne de chòd C ++, às aonais leabharlannan treas-phàrtaidh, agus is e aon de na stòran còd stòr-dàta sgaoilte as lugha. An coimeas ri sin, chan eil SQLite a 'toirt taic do chuairteachadh agus tha e a' gabhail a-steach 235 mìle loidhne de chòd C. Mar a tha an sgrìobhadh seo, tha innleadairean 207 air cur ri ClickHouse, agus tha dian nan geallaidhean air a bhith a 'dol am meud o chionn ghoirid.
Anns a’ Mhàrt 2017, thòisich ClickHouse a’ giùlan mar dhòigh furasta air sùil a chumail air leasachadh. Bhris iad cuideachd am faidhle sgrìobhainnean monolithic ann an rangachd faidhle stèidhichte air Markdown. Thathas a’ cumail sùil air cùisean agus feartan tro GitHub, agus san fharsaingeachd tha am bathar-bog air a bhith tòrr nas ruigsinneach anns na beagan bhliadhnaichean a dh’ fhalbh.
San artaigil seo, tha mi a’ dol a thoirt sùil air coileanadh brabhsair ClickHouse air AWS EC2 a ’cleachdadh pròiseasairean 36-core agus stòradh NVMe.
ÙRACHADH: Seachdain às deidh dhomh am post seo fhoillseachadh an toiseach, rinn mi an deuchainn a-rithist le rèiteachadh nas fheàrr agus choilean mi toraidhean mòran nas fheàrr. Chaidh am post seo ùrachadh gus na h-atharrachaidhean sin a nochdadh.
A’ cur air bhog Buidheann AWS EC2
Bidh mi a’ cleachdadh trì eisimpleirean c5d.9xlarge EC2 airson na dreuchd seo. Anns gach fear dhiubh tha 36 CPUs brìgheil, 72 GB de RAM, 900 GB de stòradh NVMe SSD agus taic do lìonra 10 Gigabit. Chosg iad $1,962/uair gach ceann ann an roinn eu-west-1 nuair a bhios iad a’ ruith air iarrtas. Bidh mi a’ cleachdadh Ubuntu Server 16.04 LTS mar an siostam-obrachaidh.
Tha am balla-teine air a rèiteachadh gus an urrainn do gach inneal conaltradh le chèile gun chuingealachaidhean, agus chan eil ach mo sheòladh IPv4 air a ghealltainn le SSH sa bhuidheann.
Dràibheadh NVMe ann an staid ullachaidh obrachaidh
Airson ClickHouse a bhith ag obair, cruthaichidh mi siostam faidhle ann an cruth EXT4 air draibhear NVMe air gach aon de na frithealaichean.
$ sudo mkfs -t ext4 /dev/nvme1n1
$ sudo mkdir /ch
$ sudo mount /dev/nvme1n1 /chAon uair ‘s gu bheil a h-uile càil air a rèiteachadh, chì thu am puing sreap agus 783 GB de rùm ri fhaighinn air gach siostam.
$ 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% /chIs e an dàta a chleachdas mi san deuchainn seo cnap dàta a chruthaich mi bho 1.1 billean turas tacsaidh a chaidh a ghabhail ann am Baile New York thairis air sia bliadhna. Air a’ bhlog mion-fhiosrachadh mar a chruinnich mi an seata dàta seo. Tha iad air an stòradh ann an AWS S3, agus mar sin rèitichidh mi an AWS CLI leis na h-iuchraichean ruigsinneachd agus dìomhair agam.
$ sudo apt update
$ sudo apt install awscli
$ aws configureSuidhichidh mi crìoch iarrtas co-aontach an neach-dèiligidh gu 100 gus am bi faidhlichean a’ luchdachadh sìos nas luaithe na na roghainnean bunaiteach.
$ aws configure set
default.s3.max_concurrent_requests
100Luchdaichidh mi sìos an stòr-dàta turasan tacsaidh bho AWS S3 agus stòraichidh mi e air draibhear NVMe air a’ chiad fhrithealaiche. Tha an stòr-dàta seo ~104GB ann an cruth CSV co-dhlùthaichte GZIP.
$ sudo mkdir -p /ch/csv
$ sudo chown -R ubuntu /ch/csv
$ aws s3 sync s3://<bucket>/csv /ch/csvStàladh ClickHouse
Stàlaichidh mi an cuairteachadh OpenJDK airson Java 8 oir tha feum air Apache ZooKeeper a ruith, a tha riatanach airson stàladh sgaoilte de ClickHouse air na trì innealan.
$ sudo apt update
$ sudo apt install
openjdk-8-jre
openjdk-8-jdk-headless
An uairsin shuidhich mi caochladair na h-àrainneachd JAVA_HOME.
$ sudo vi /etc/profile
export JAVA_HOME=/usr
$ source /etc/profileCleachdaidh mi an uairsin siostam riaghlaidh pacaid Ubuntu gus ClickHouse 18.16.1, sùil agus ZooKeeper a chuir a-steach air na trì innealan.
$ 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
zookeeperdCruthaichidh mi eòlaire airson ClickHouse agus nì mi cuideachd beagan rèiteachaidhean rèiteachaidh air na trì frithealaichean.
$ 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.confIs iad sin na roghainnean rèiteachaidh a bhios mi a’ cleachdadh.
<?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>Bidh mi an uairsin a’ ruith ZooKeeper agus am frithealaiche ClickHouse air na trì innealan.
$ sudo /etc/init.d/zookeeper start
$ sudo service clickhouse-server startA’ luchdachadh suas dàta gu ClickHouse
Air a’ chiad fhrithealaiche cruthaichidh mi clàr turais (trips), a chumas stòr-dàta de thursan tacsaidh a’ cleachdadh an einnsean 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;
Bidh mi an uairsin a’ toirt a-mach agus a’ luchdachadh gach faidhle CSV gu clàr turais (trips). Chaidh na leanas a chrìochnachadh ann an 55 mionaidean agus 10 diogan. Às deidh na h-obrachaidh seo, b’ e meud an eòlaire dàta 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)B’ e an astar in-mhalairt 155 MB de shusbaint CSV neo-bhrùichte gach diog. Tha amharas agam gur ann mar thoradh air cnap-starra ann an dì-dhùmhlachadh GZIP a bha seo. Is dòcha gu robh e na bu luaithe na faidhlichean gzipped gu lèir a unzip aig an aon àm a’ cleachdadh xargs agus an uairsin an dàta gun zip a luchdachadh. Gu h-ìosal tha tuairisgeul air na chaidh aithris rè pròiseas in-mhalairt 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:1HSaoraidh mi àite air an draibhear NVMe le bhith a’ cuir às do na faidhlichean CSV tùsail mus lean thu air adhart.
$ sudo rm -fr /ch/csvTionndaidh gu Foirm Colbh
Bidh an einnsean Log ClickHouse a’ stòradh dàta ann an cruth sreath. Gus dàta a cheasnachadh nas luaithe, bidh mi ga thionndadh gu cruth colbh a’ cleachdadh an einnsean MergeTree.
$ clickhouse-client --host=0.0.0.0Chaidh na leanas a chrìochnachadh ann an 34 mionaidean agus 50 diog. Às deidh na h-obrachaidh seo, b’ e meud an eòlaire dàta 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;Seo mar a bha an toradh sealladh coltach rè an obrachaidh:
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)Anns an deuchainn mu dheireadh, chaidh grunn cholbhan atharrachadh agus ath-àireamhachadh. Fhuair mi a-mach nach eil cuid de na gnìomhan sin ag obair tuilleadh mar a bhiodh dùil air an t-seata dàta seo. Gus an duilgheadas seo fhuasgladh, thug mi air falbh na gnìomhan neo-iomchaidh agus luchdaich mi an dàta gun a bhith air a thionndadh gu barrachd sheòrsan granular.
Sgaoileadh dàta air feadh na buidhne
Sgaoilidh mi an dàta thairis air na trì nodan cnuasachaidh. Gus tòiseachadh, gu h-ìosal cruthaichidh mi clàr air na trì innealan.
$ 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);An uairsin nì mi cinnteach gum faic a’ chiad fhrithealaiche na trì nodan sa bhuidheann.
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:
An uairsin mìnichidh mi clàr ùr air a’ chiad fhrithealaiche a tha stèidhichte air an sgeama trips_mergetree_third agus a’ cleachdadh an einnsean Distributed.
CREATE TABLE trips_mergetree_x3
AS trips_mergetree_third
ENGINE = Distributed(perftest_3shards,
default,
trips_mergetree_third,
rand());Nì mi lethbhreac den dàta bhon chlàr stèidhichte air MergeTree gu na trì frithealaichean. Chaidh na leanas a chrìochnachadh ann an 34 mionaidean agus 44 diogan.
INSERT INTO trips_mergetree_x3
SELECT * FROM trips_mergetree;Às deidh na h-obrach gu h-àrd, thug mi 15 mionaidean do ClickHouse airson gluasad air falbh bhon chomharra ìre stòraidh as àirde. Thàinig na clàran dàta gu crìch gu bhith 264 GB, 34 GB agus 33 GB fa leth air gach aon de na trì frithealaichean.
Measadh coileanadh brabhsair ClickHouse
B’ e an ath rud a chunnaic mi an uair a bu luaithe a chunnaic mi a’ ruith gach ceist air bòrd iomadh uair trips_mergetree_x3.
$ clickhouse-client --host=0.0.0.0Chrìochnaich na leanas ann an 2.449 diogan.
SELECT cab_type, count(*)
FROM trips_mergetree_x3
GROUP BY cab_type;Chrìochnaich na leanas ann an 0.691 diogan.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree_x3
GROUP BY passenger_count;Chrìochnaich na leanas ann an 0 diogan.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree_x3
GROUP BY passenger_count,
year;Chrìochnaich na leanas ann an 0.983 diogan.
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;Airson coimeas a dhèanamh, ruith mi na h-aon cheistean air clàr stèidhichte air MergeTree a tha a’ fuireach air a’ chiad fhrithealaiche a-mhàin.
Measadh dèanadais air aon nód ClickHouse
B’ e an ath rud a chunnaic mi an uair a bu luaithe a chunnaic mi a’ ruith gach ceist air bòrd iomadh uair trips_mergetree_x3.
Chrìochnaich na leanas ann an 0.241 diogan.
SELECT cab_type, count(*)
FROM trips_mergetree
GROUP BY cab_type;Chrìochnaich na leanas ann an 0.826 diogan.
SELECT passenger_count,
avg(total_amount)
FROM trips_mergetree
GROUP BY passenger_count;Chrìochnaich na leanas ann an 1.209 diogan.
SELECT passenger_count,
toYear(pickup_date) AS year,
count(*)
FROM trips_mergetree
GROUP BY passenger_count,
year;Chrìochnaich na leanas ann an 1.781 diogan.
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;Beachdan air na toraidhean
Is e seo a’ chiad uair a bha e comasach do stòr-dàta an-asgaidh stèidhichte air CPU coileanadh nas fheàrr na stòr-dàta stèidhichte air GPU anns na deuchainnean agam. Tha an stòr-dàta sin stèidhichte air GPU air a dhol tro dhà ath-sgrùdadh bhon uairsin, ach tha an coileanadh a lìbhrig ClickHouse air aon nód gu math drùidhteach a dh’ aindeoin sin.
Aig an aon àm, nuair a thathar a’ cur an gnìomh Ceist 1 air einnsean sgaoilte, tha na cosgaisean os cionn òrdugh meudachd nas àirde. Tha mi an dòchas gun do chaill mi rudeigin anns an rannsachadh agam airson na dreuchd seo oir bhiodh e math amannan ceiste fhaicinn a’ dol sìos agus mi a’ cur barrachd nodan ris a’ bhuidheann. Ach, tha e math nuair a chaidh ceistean eile a chuir an gnìomh, gun deach coileanadh àrdachadh mu 2 uair.
Bhiodh e math ClickHouse fhaicinn a’ tighinn air adhart gu bhith comasach air stòradh agus obrachadh a-mach a sgaradh gus an urrainn dhaibh sgèile gu neo-eisimeileach. Dh'fhaodadh taic HDFS, a chaidh a chur ris an-uiridh, a bhith na cheum a dh'ionnsaigh seo. A thaobh coimpiutaireachd, ma ghabhas aon cheist a luathachadh le bhith a’ cur barrachd nodan ris a’ bhuidheann, tha àm ri teachd a’ bhathar-bhog seo glè shoilleir.
Tapadh leibh airson an ùine a ghabhail gus am post seo a leughadh. Bidh mi a’ tabhann seirbheisean co-chomhairleachaidh, ailtireachd, agus leasachadh cleachdaidh do luchd-dèiligidh ann an Ameireaga a Tuath agus san Roinn Eòrpa. Ma tha thu airson bruidhinn mu mar as urrainn do na molaidhean agam do ghnìomhachas a chuideachadh, cuir fios thugam tro .
Source: www.habr.com