search

h2oai / db-benchmark

reproducible benchmark of database-like ops
https://h2oai.github.io/db-benchmark
Mozilla Public License 2.0
323 stars 85 forks source link

ClickHouse tuning for groupby q10 on 1e9 data #96

Closed jangorecki closed 4 years ago

jangorecki commented 5 years ago

Despite of running ClickHouse using mergetree table engine we are hitting memory error in a single case. This is a matter of configuration and should be adjusted. We need to check that it won't impose performance overhead for rest of queries that are completing successfully. As of now issue is related to q10 and 1e9 rows data, results:

Received exception from server (version 19.11.3):
Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory li
mit (for query) exceeded: would use 120.11 GiB (attempt to allocate chunk of 134
217728 bytes), maximum: 120.00 GiB.

https://github.com/yandex/ClickHouse/issues/6576 https://github.com/ClickHouse/ClickHouse/issues/5618

jangorecki commented 4 years ago

This issue is not appearing anymore. Now all 1e9 tests are affected by CH server crash #112, quite likely that if CH wouldn't crash then this issue will be still valid, thus leaving as unresolved.

jangorecki commented 4 years ago

This problem is appearing again, now consistently for all 1e9 data cases. As a result of reducing working memory space for clickhouse from 120 GB to 100 GB. Other problems like fatal server crash #112 or segfault #133 are likely to be a result of too few memory left on that machine.

jangorecki commented 4 years ago

CH documentation has been updated, tuning according to documentation did not resolve the problem. Reported in https://github.com/ClickHouse/ClickHouse/issues/11773

jangorecki commented 4 years ago

After getting support in issue linked above, I was able to tune clickhouse script to finish all questions successfully. q1-q9 caches query answer into in-memory table, q10 caches into on-disk table. It is because of memory requirements of q10. Using CH options for on-disk caching was not enough to handle that alone.