CODEWITHSUNDEEP
performanceclickhousethroughput
Winter Z.
Winter Z.

Reputation: 93

How to speed up big query in ClickHouse?

Backgroud:

I submitted a local query in ClickHouse (without using cache), and it processed 414.43 million rows, 42.80 GB. The query lasted 100+ seconds. My ClickHouse instances were installed on AWS c5.9xlarge EC2 with 12T st1 EBS

During this query, the IOPS is up to 500 and read throughput is up to 20M/s. And as a comparison, st1 EBS max IOPS is 500 and max throughput is 500M/s.

Here is my question:

  1. Does 500 IOPS actually limit my query (file-reading) speed? (never mind the cache) Should I change EBS volume type to gp2 or io1 to increase IOPS?
  2. Is there any setting can improve throughput under the same IOPS? (as I can see, it's far from ceiling actually) I tried increasing 'max_block_size' to read more file at one time, but it doesn't seem to work.
  3. How to extend the cache time?Big query took minutes. Cache took seconds. But cache doesn't seem to last very long.
  4. How can I warm-up columns to meet all requirements? Please show sqls.

Upvotes: 0

Views: 2241

Answers (1)

Denny Crane
Denny Crane

Reputation: 13360

Does 500 IOPS actually limit my query (file-reading) speed?

yes

Should I change EBS volume type to gp2 or io1 to increase IOPS?

yes

Is there any setting can improve throughput under the same IOPS?

tune max_bytes_to_read

reduce number of columns (in select)

reduce number of parts (in select)

How to extend the cache time?

min_merge_bytes_to_use_direct_io=1

How can I warm-up columns to meet all requirements? Please show sqls.

select a,b,c,d from T Format Null

Upvotes: 2

Related Questions