In April, Avito engineers gathered for online gatherings with ClickHouse chief developer Alexei Milovidov and Kirill Shvakov, a Golang developer from Integros. They discussed how we use the database management system and what difficulties we are having.
Based on the meeting, we collected an article with expert answers to our and audience questions about backups, data resharding, external dictionaries, the Golang driver and ClickHouse version updates. It may be useful to developers who are already actively working with Yandex DBMS and are interested in its present and future. The default answer is Alexey Milovidov, unless otherwise written.
Caution, under the cut a lot of text. We hope that the content with questions will help you navigate.
Content
- ClickHouse is constantly updated, but our data is not. What to do with it?
- What are the best practices for backing up data from ClickHouse at the moment?
- Will it be possible to organize a controlled lag of replicas in the shafts?
- What if the table structure has changed?
- What are the best practices for data resharding now?
- ClickHouse clickhouse-copier. ?
- , . ?
- ?
- ClickHouse, ?
- Kill query , . ?
- ?
- ClickHouse, ?
- storage_configuration ?
- Low Cardinality?
- ?
- ClickHouse ?
- ?
- ?
- ClickHouse . , ?
- , OOM?
- Golang- ClickHouse?
- lazy_load. ?
- , system reload dictionaries , ?
- ClickHouse, ?
- :
, -. — .
ClickHouse , — . ?
ClickHouse , , optimize final , .
, - , . , , , , ClickHouse. , ?
, , , . , ClickHouse . , , - . , , ClickHouse . .
ClickHouse?
, optimize final, , , , , , ?
: - - . , , ?
. «.», . , .
, ClickHouse, . , . , , .
. — .
, :
- , — show create table.
- ClickHouse — select * from table . TabSeparated. — Native.
, . , ClickHouse. , MySQL .
ClickHouse . alter table freeze partition. alter table freeze — .
, . , . . - , .
. — . Directory-Detached attach partition. .
- — , . , «.». — , .
. ClickHouse , . , . . — LVM , — ZFS Linux.
, - . , , . , . , .
?
ClickHouse. ? .
- roll back ? , , , ?
, , , , ?
. , issue : « - , , ». , issue . , , ClickHouse. , 20.3.
ClickHouse — . , . , , .
-, , select, , . select .
-, — . . : , , . ClickHouse .
, . , ClickHouse , . - , , , alter drop column. .
20.3, , . — , . , , . , , , , - , . , , .
. alter delete alter update, , . .
, ?
, ? . Btrfs ZFS Linux LVM?
attach partition , ClickHouse , . . — MergeTree , attach, alter. attach , alter table move partition.
— Btrfs. , LVM, LVM , ext4, . Btrts . , , . , Btrfs .
?
. . — ClickHouse . , . , ClickHouse .
, . , , . , , , .
, , — alter table fetch partition. , , 2017 , — - . , - .
, . , . . , . , .
, , , . — . select Distributed , .
, , . , , , . , select — , , — .
. , - , , Distributed . ClickHouse optimize skip unused shards. select where , , . , . , .
, . , , .
, lead system administrator : , , , . , , , . , , , - .
: — , , . , . , . , , .
, , , . . , , . , , .
2019 . , 2019 , 2019 — , - . . , ad hoc , .
. , , . .
, . . — . — . — , . , .
, — . , select. select, , .
, CPU - - . , , . , , . , . , .
: , , . , ClickHouse .
, , . . , ClickHouse: « . - - , ».
, , , . .
: , , , , .
«.». — , . -. «.», — avito.ru, , .
— , . , «». , «» . , .
, , ? , ClickHouse «» — . , , ClickHouse .
— - . Distributed , , . , , , , .
. , , . , - . . avito.ru. , , — . .
. , . . .
? — , . . — -, , , MySQL. — . .
. , , . , - , . , . Distributed , . .
, , . clickhouse-copier.
— . , , , , 120 — 360 . , . «.» avito.ru, 120 . . , . Distributed , 120 .
— . , .
ClickHouse clickhouse-copier. ?
, . , , . , . .
, . , , , , .
, , . Distributed , clickhouse-copier, , , . , , , . , , .
clickhouse-copier? . , clickhouse-copier , , — insert select. , , , , -, . .
2017 , . ClickHouse. , . , ? .
, , . , , , . , , — .
?
TTL move to slow disk . , cron, ?
, - — . , . , , .
. — . - , . , — .
— . . , .
ClickHouse, ?
- ClickHouse , . 19.11 19.16 , , 19.16 20.3. , ?
«» . — changelog. , . . , , , , .
— , , — . . , , , , , . . - , — .
— . ClickHouse, .
20.3.4. 20 — 2020. , , , . — 20.3. — 3 — , - . ClickHouse - , . 20.4 ClickHouse . — 20.3.4. 4 — -, , - . 4 , .
, - . , - . , - , , . . -, ClickHouse .
ClickHouse , ClickHouse — , 20.4.1 — , . ? ClickHouse, , , , . ClickHouse , — .
: . - , , ClickHouse, . .
. , ClickHouse. — Hetzner , . , Ansible, , — .
? ClickHouse , — Docker, LXC, , Ansible playbook, . . , - . , , — . , . — .
, senior backend engineer : . ClickHouse, , . . -, . — , , , , . . , , .
: , «.». 600 - , 360, . — . ? . . , .
- -. , , .
. ClickHouse. , «.», , . , CI. .
. . , . . : , , . . . — , .
- . : « , . , . , . ». , , — . .
Kill query , . ?
, - , , ClickHouse. - — , . , CPU , . ClickHouse . : « , , process list, ».
kill. , . , ClickHouse - , , , . , ClickHouse, kill query . ? , kill query , .
. , kill query .
Kill query « , ». . , . , , . , . , .
, - . , , , , , . , . , . , , — ClickHouse.
?
, item — . — . , 1K RPS 1K item’?
, , — , , .
. select 1 ClickHouse , . , - key-value , . , . — 8192 . 64 1 . . .
. , . , , . 1000 RPS ClickHouse , , .
ClickHouse , — . , , .
, , ClickHouse . ? — . , , . , 64.
. min compress block size, max compress block size. , , . ClickHouse — key-value . — .
: , . , ClickHouse - . , - , - , - . MySQL, — MySQL duplicate key, PostgreSQL conflict — . .
, ClickHouse . , .
ClickHouse, ?
— 256 , ClickHouse 60—80 , — 130. , , , ?
, page cache . , cached free — , — , . , . , , .
, , ClickHouse . uncompressed cache. config.xml uncompressed cache size — , page cache.
, . — use uncompressed cache — . , , . — - . , .
storage_configuration ?
ClickHouse , data storage. fast SSD.
, volume hot memory. . select , , , ? prewhere ?
, .
.
. , — . tmpfs , - . , , .
- , , -, . , . , . , . ClickHouse , , , ZooKeeper . , , . .
, page cache . . , ClickHouse fsync insert.
, . select , , , . prewhere , , .
Low Cardinality?
Low Cardinality . , . -, , -, . — -, — , .
: — , — - Low Cardinality . fallback, , . , string, , , .
?
. — , ClickHouse — . , Elasticsearch Sphinx. , , , Elasticsearch ClickHouse.
? , Elasticsearch , , . , , ClickHouse, , . , - , . , - .
ClickHouse , . . , , - like. like ClickHouse — like, . — .
like — full scan. full scan CPU, . , - , . , , SSH .
. — , . ClickHouse -. Arenadata , , .
, , : - . , - , , . , .
ClickHouse . , -, , , , UTF-8 ASCII. , .
. X like or X like . , .
— . - , .
ClickHouse ?
, . , max concurrent queries, ?
, , . , , , . . , .
, . , .
, . «.» select count from hits, , . , , — , . .
, , . — force index by date. , . , . — where event date between . , and or — or URL match. , URL .
, ClickHouse . , . priority. priority ≠ 0, - , , , , , , .
, , . , , , .
OS thread priority. nice Linux. , - . nice — , — -19, CPU , .
— , . — . , , , ClickHouse , .
, : - — . , . . . min execution speed — , timeout before checking min execution speed — . , , , — .
. ClickHouse , . , , CPU, , — , . , , .
? ClickHouse . . , , . , . .
?
, . , , - , , . , , , ?
, . page cache , , , , , .
- , , - . pull request, , in join — .
, . — . , , limit 10. , limit 10,10. . , ? , .
, ClickHouse — ClickHouse Proxy.
: ClickHouse Proxy . , . Proxy , , , . , Proxy , ClickHouse .
Nginx , . Nginx , , , , . ClickHouse Proxy . ClickHouse, , . .
?
, - — , . - , . - , . ?
— . - « », ?
, . , , , .
. , , . “Ok” insert, , .
. “Ok”, . ClickHouse, , . , , . — . , , . .
, . , .
. , . , , . , , , . — insert. .
. . , , , , . , . , .
: . , , , — . , , , . , — . .
? , — , . - , . , , , . - , , .
API — ClickHouse . API : , , , . , , . , ClickHouse.
API - — , — , . , . - . , .
ClickHouse . , , ?
ClickHouse , . - , . , . , , , - .
ClickHouse, , , ? , ClickHouse — . , . .
, . - 60 ClickHouse, , , , - . «.». , . .
«» Grafana, . . , . , Graphite-web. . , Grafana, , .
. : CPU, , , . — , , , MergeTree, , , , . , , .
: , . Grafana. Grafana datasource, ClickHouse. Grafana ClickHouse. ClickHouse , . Grafana , . .
. — , Grafana , «» ?
: datasource, ClickHouse, Altinity. , . , «» - ClickHouse, . Altinity — , ClickHouse. , . , Grafana, — .
: ClickHouse . . , , . , , . - , .
, , — , . -, , . . , , : «, - Grafana?», : « Grafana, “”, , . , , ».
, OOM?
, , ReplacingMergeTree. . - .
, , OOM. , , , OOM. . .
, , . , . - . , , , — - ? , , , .
, «», , , . , . , , , , . ? , - , - , - , , . OOM . , .
, OOM, . .
: . , , , , , , , . 100 . 150 , 100 , 50 , OOM .
, OOM, 100 ? , ?
: , . , , - , , . — , , . , , , , .
, , « » OOM. , , - , , — . .
Golang- ClickHouse?
Golang-, , ClickHouse. ClickHouse, .
. — Vertica. python-, Vertica. , , - . . , , «» — issue, .
. Golang- — Golang ClickHouse. - http , . ? - breaking changes ? issue?
: — . , .
issue, . , . , - . - ClickHouse. , , ClickHouse . , .
ClickHouse, - . — , . , .
Go, , Go. — . - , , - . CloudFlare , - , . ClickHouse , .
- , ClickHouse . issues . , - . pull request - , .
. , , ClickHouse . , , . , 2 - .
, . . - , , . «» .
: . — , Go. - , .
. — , , -, - .
lazy_load. ?
lazy_load, . , . . - ClickHouse , , ?
ClickHouse, . ?
-, system reload dictionaries. -, — , , . , .
. , MySQL. - select, select . . — lazy_load. — , , , system reload dictionary , . . lazy_load, ClickHouse .
— , .
, system reload dictionaries , ?
system reload dictionaries. — , . System reload dictionaries , c system reload dictionary. ClickHouse?
. . , ClickHouse, . system reload dictionaries, , , .
ClickHouse, ?
, , . ClickHouse , .
ODBC. - ClickHouse, ?
— credentials odbc.ini, ClickHouse ODBC Data Source Name. — MySQL, . ODBC — , .
:
. , .
