Since ClickHouse is a specialized system, it is important to take into account the peculiarities of its architecture when using it. In this talk, Alexey will talk about examples of common mistakes when using ClickHouse that can lead to ineffective work. Practical examples will show how the choice of one or another data processing scheme can change performance by orders of magnitude.
Video:
Hello! My name is Alexey, I am doing ClickHouse.
Firstly, I am in a hurry to please you, I will not tell you today what ClickHouse is. To be honest, I'm sick of this. Every time I tell you what it is. And, probably, everyone already knows.
, , . . ClickHouse. , ClickHouse , , , . , .
- , , .
, ? . , , , , - .
, , , , inserts batches, . . inserts.
, ClickHouse insert, . .
, . , .. . 105 - . 700 . - .
MergeTree, . . – , 400 000 .
, , , 250 000 . – ClickHouse*.
, ? 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, «» , , ? , ?
.
Maybe it makes sense to put such a field in the primary key, according to which the data will be better compressed if they are sorted by this field. For example, user ID. User, for example, goes to the same site. In this case, put the user id and time. And then your data will be better compressed. As for the date, if you really do not have and never have range queries by dates, then you can not put the date in the primary key.
OK thank you very much!