Karena ClickHouse adalah sistem khusus, penting untuk mempertimbangkan kekhasan arsitekturnya saat menggunakannya. Dalam pembicaraan kali ini, Alexey akan berbicara tentang contoh kesalahan umum saat menggunakan ClickHouse yang dapat menyebabkan pekerjaan tidak efektif. Contoh praktis akan menunjukkan bagaimana pilihan satu atau skema pemrosesan data lain dapat mengubah kinerja berdasarkan urutan besarnya.
Video:
! , ClickHouse.
-, , , ClickHouse. , . , . , , .
, , . . ClickHouse. , ClickHouse , , , . , .
- , , .
, ? . , , , , - .
, , , , inserts batches, . . inserts.
, ClickHouse insert, . .
, . , .. . 105 - . 700 . - .
MergeTree, . . – , 400 000 .
, , , 250 000 . – ClickHouse*.
* 2020 , .
, ? MergeTree 59 . 10 000 . ReplicatedMergeTree – 6 . , 2 . -, - . ? , ClickHouse . .
– . , , . , – . . , . . , , . , , . , . - .
, insert ClickHouse, memtable. log structure MergeTree, MergeTree, log’, memTable. , . 100 , 200 . .
: « ?», , - - ClickHouse.
1. . - . , Kafka. Kafka, . , , .
, Kafka – . , Kafka. , . , , . .
2. . - , . . , , , . cron, - daemon ClickHouse. , .
, , - , .
3. , . , - - daemon, . , . , , , , , ClickHouse.
kill -9 . , . , , . , .
4. . - . ClickHouse , . , http- transfer-encoding: chunked insert’. , , overhead .
ClickHouse . ClickHouse .
. , , , ClickHouse , insert. ClickHouse inserts . , . .
5. . - community – . , . , ClickHouse . open source, , , . – , GitHub, . , - .
* 2020 , KittenHouse.
6. – Buffer . , . Buffer .
, . MergeTree , buffer , . 10 000 , . , , . .
buffer . - , .
ClickHouse Kafka. – Kafka. . . Kafka .
, . community . «community », . , , .
* 2020 , RabbitMQ.
? insert values values - . , now() – . ClickHouse , . .
* , , VALUES .
, , . ClickHouse . , , . , , .
* ClickHouse write-ahead log, .
– .
, . – , , string. . .
, , , - , , ClickHouse , . - .
, IP-. . , 192.168.1.1. – UInt32*. 32 IPv4 .
-, , . , , . - .
.
IP-, . 137 . , 37 . , . . 4 .
, . - , IP- . , .
. , , , , , .
.
\1. , . , , , . ClickHouse. . .
, , . . : . , «», , , - . .
Ulnt32 250. 250 , , , -. , ClickHouse . , , . . , , , . .
, ClickHouse. Enum. Enum . , : , , , . 4 .
, . . alter table. alter table ClickHouse . Enum, . alter * , selects. alter , . . - .
* ClickHouse, ALTER .
ClickHouse – . ClickHouse , . , : MySQL, Mongo, Postgres. , http . ClickHouse , .
, join . . , . , , .
. .. . , , . . – , . MySQL.
, , hit rate 100 %. , MySQL. ClickHouse , – , , .
, – ClickHouse . . . , , . , .
, , . . – 64 .
, 64- , . , .
. , - .
. , , - , . , . .. , . , , , . , , - , .
– .
, , , . . , , ru – 2 . , , , 2 . , , .
, , , , . – . , - . , . , . , , , . . .
– , , join. join – , . , .
in place, .
- , , - .
, , . , , , .
, . ClickHouse , . , . , , RFC, , , .
. 166 . , 67 , . . . - , - , - , .
- , , . . . , .
, , 126 , 5 . 25 . 4 . . , 25 - -.
, , , - 4. - 25 . ? - . , . .
, , , , . IPv4, UInt32*. IPv6, FixedString(16), IPv6 – 128 , . . .
, IPv4 , IPv6? , . IPv4, IPv6. , IPv4 IPv6. , IPv4 , .
* ClickHouse IPv4, IPv6, , , , .
, . , - . , , ClickHouse, . - , .
, . , , , . . : 12.3. , , , . , . , . , . .
4 . , ClickHouse. ClickHouse – . , . 5 BrowserVersion, 5 . .
, , , . ClickHouse . ClickHouse - . - .
, . , 512. 512 – .
, ClickHouse, Log, . , , .
* ClickHouse LowCardinality .
. - . . , - , , MySQL 3.23.
, .
, , , .
- . , . , MyISAM . .
– , alter . . MySQL .
, , , .
ClickHouse , , -, , .
: « ClickHouse ?». , . , . . , .
, -, . , , , – . - . . . ClickHouse .
Alter ClickHouse , alter add/drop column.
, 10 10 000 , . ClickHouse – , throughput, latency, 10 .
. , .
, maintenance .
, , , - , . – StripeLog. TinyLog, .
* ClickHouse input.
– . , 5 , 6 . , . 5 , 1 000 . . , , 200 ClickHouse, . instance .
ClickHouse . instance ClickHouse . . . - , , 56 . , , 56 . 200 ClickHouse , , 10 000 . , .
, instances . - , - . instance, ClickHouse .
, TCP. , . .
, . .
, – . , 1 000 , . . . ClickHouse AggregatingMergeTree, .
, , . , , SummingMergeTree , 20 - . , .
. -, . , . – , , , . , .
? , , . . . , . ClickHouse alter . - C++. , C++.
ClickHouse, – , . ClickHouse , . , .
– . - production show processlist. , - .
, . , . url in .
– ? , . , , ru url = - . , url, . . ClickHouse .
- , , ClickHouse . , -. . , . , , .
, ClickHouse IN. , MySQL IN, , 100 - , MySQL 10 , .
– , ClickHouse, , , full scan, . . , . , .
. , , IN . . . *.
– , , . , 100 500 . , , 50 . .
* ; , .
, API. , - . - , API , - . - , .
. API, . , -. . .
ClickHouse – . . , , . .
. .
, , , ClickHouse , ClickHouse .
? pipeline . , , -. ClickHouse . , , - - . , .
. , rsync.
ClickHouse . , ZooKeeper. ZooKeeper , , , , - java-, ClickHouse – , C++, . ZooKeeper java. - , .
ClickHouse – . . , Distributed , failover. , , .
, table engines. ClickHouse – , . , , MergeTree. – , .
MergeTree , - . . , , default – 2000 . .
, . .
, . , , . Log.
– StripeLog TinyLog.
Memory , - .
ClickHouse .
. . . JOIN, , , ClickHouse Hash JOIN. , , JOIN *.
, , , inplace .
* ClickHouse merge join , , . .
, .
ClickHouse . *. . - , , , , .
* update delete batch .
, . , ReplaceMergeTree. merges. optimize table. , .
JOIN ClickHouse – .
, .
ClickHouse , select.*
* ClickHouse . , . ClickHouse – Catboost. , : « . !». .
, ClickHouse, . , . , – . - , , , , .
! ClickHouse?
.
ClickHouse. cli .
.
select’.
.
GitHub, .
.
, , .
.
. .
. . LZ4, ZSTD*. 64 1 .
* , .
?
. . , .
.
, , uniqExact , . . , uniqExact , . ? . . , , . , , ? - ? , , - .
, , . IP- . , , ClickHouse IP- . , . . uniqExact , . -.
? , user id, in, , , ?
. , , - – . , , , , , .
, ! ClickHouse! . ?
. . ?
-. MySQL, . . after, , .
. , , - . , .
, .
, , . . , . . . : -. , , , . ?
, , , C++.
C++ ?
-.
*.
* – pull request.
!
! ! , ClickHouse , . . , ClickHouse, , . , ZooKeeper . - ClickHouse, -, ?
, . , , set max_threads = 1. , . . . , , .
, ! . , ClickHouse . , , . . . ?
-, – , , . , , . , - , java, exception, . , . . , . ? – *.
* ClickHouse, " ", .
– ?
.
! ! , , . - WITH CTE?
. WITH . .
. !
! ! . , , - ?
. . , . *.
* .
- ? , ?
, deletes, updates , selects inserts.
. primary key. , , , ? , , , ?
.
. - primary key, «» , , ? , ?
.
Mungkin masuk akal untuk meletakkan bidang seperti itu di kunci utama, yang menurutnya datanya akan lebih baik dikompresi jika diurutkan berdasarkan bidang ini. Misalnya, ID pengguna. Pengguna, misalnya, membuka situs yang sama. Dalam hal ini, masukkan id pengguna dan waktu. Dan kemudian data Anda akan dikompresi dengan lebih baik. Adapun tanggal, jika Anda benar-benar tidak dan tidak pernah memiliki kueri rentang berdasarkan tanggal, maka Anda tidak dapat meletakkan tanggal di kunci utama.
OK, terima kasih banyak!