Clickhouse performance test on AWS EC2

[Maxim Ulanovskiy]

Hello,

We're testing Clickhouse on AWS EC2.

Test env:

EC2: 1 x i3.large node (2 cores, 15.25 GB RAM, 1 x 0.475 NVMe SSD )

Dataset: 3 months of data, 1.41 billion rows, Table size ~100GB on disk, Engine: MergeTree

Clickhouse-server: v 1.1.54383

OS: Ubuntu 16.04

SELECT uniqExact(user_id) AS uniques FROM test.merge_tree_logs

┌──uniques─┐
│ 32287758 │
└──────────┘

1 rows in set. Elapsed: 232.710 sec. Processed 1.41 billion rows, 61.75 GB (6.06 million rows/s., 265.33 MB/s.)

SELECT uniq(user_id) AS uniques FROM test.merge_tree_logs

┌──uniques─┐
│ 32273105 │
└──────────┘

1 rows in set. Elapsed: 90.572 sec. Processed 1.41 billion rows, 61.75 GB (15.57 million rows/s., 681.73 MB/s.)

We were running just 1 query at the time.

I understand that this is simple scan which doesn't utilize index.

I also understand that EC2 is slower than bare metal.

Questions:

1) Is this expected performance for given query and hardware?

2) What can we do to reduce query time?

3) Would clickhouse take advantage of multi-core CPUs for single query above?  In other words does it parallelize execution of 1 query within single node.

4) Clickhouse didn't hit memory limit for queries above however I wonder if more RAM would help in this case?

Thanks,

Max

[Denis Zhuravlev]

>Table size ~100GB on disk, Engine: MergeTree

>SELECT uniqExact(user_id) AS uniques FROM test.merge_tree_logs

To solve this query CH will read all 100GB. If you system can read 1GB/s, the query execution time will be ~100s.

>1) Is this expected performance for given query and hardware?

probably yes. Check disk utilization using AWS console and linux tools (sar -d 1 10)

>2) What can we do to reduce query time?

a. more memory, more disks.

b. Is this real requirement or just speed test?

In real life I would create some aggregating MV and pre-calculate this numbers. If you store count by day (month,hour,,,), you will need just sum a couple numbers for 0.0 seconds.

>3) Would clickhouse take advantage of multi-core CPUs for single query above?  In other words does it parallelize execution of 1 query within single node.

Yes, CH executes queries by several threads, check max_threads parameter (select * from system.settings where name = 'max_threads') by default it will cpu_cores/2

>4) Clickhouse didn't hit memory limit for queries above however I wonder if more RAM would help in this case?

Basically CH does not have own disk buffer. It uses OS disk cache. So if you upgrade your system to 100GB RAM, CH will execute your query less than 0.5 second.

[Maxim Ulanovskiy]

Thanks for your answer, Denis

Very helpful comments!

I checked disk usage stats and it looks like this query hits disk read limits indeed.

However I still have a few questions if you don't mind:

>SELECT uniqExact(user_id) AS uniques FROM test.merge_tree_logs

To solve this query CH will read all 100GB. If you system can read 1GB/s, the query execution time will be ~100s.

 100GB is a size of the whole table. Query output is 'Processed 1.41 billion rows, 61.75 GB'.

Does CH read data from specific column or whole dataset?

The thing is that we have 20 columns in this test table and query only uses user_id.

I don't think this column alone may take 61.75 GB of space.

My best guess is that 61GB is uncompressed user_id column size in RAM.

Another thing I don't understand is difference in processing speed between uniq and uniqExact functions: 265.33 MB/s vs 681.73 MB/s. 

681.73 MB/s is faster than max disk read speed so what does it actually show?

b. Is this real requirement or just speed test?

In real life I would create some aggregating MV and pre-calculate this numbers. If you store count by day (month,hour,,,), you will need just sum a couple numbers for 0.0 seconds.

We currently have Spark which aggregates data and stores reports in Cassandra. This works but doesn't allow us to do uniques for custom dimensions. 

Now we would like to implement user segmentation with custom filters and ad-hoc reports (ideally realtime) which is not possible with current architecture.

Storage of CH data in memory would work probably but it's too expensive given our limited budget and TBs of data.

Thanks,

Max

[Denis Zhuravlev]

> 100GB is a size of the whole table. Query output is 'Processed 1.41 billion rows, 61.75 GB'. ah, yes, my mistake. CH reads only one column (compressed) to solve this query. Check a table system.columns (columns data_compressed_bytes / data_uncompressed_byte). data_uncompressed_byte will match 61.75 GB

>Another thing I don't understand is difference in processing speed between uniq and uniqExact functions: 265.33 MB/s vs 681.73 MB/s.  >681.73 MB/s is faster than max disk read speed so what does it actually show?

As I understand this speed just a simple calculation (bytes_processed / query_time).

The problem that time depends on cache hit rate. So the second execution usually faster because data already in linux cache. I don't pay attention this speed. 

Processed .... rows -- more useful number and allows evaluate query complexity.

>We currently have Spark which aggregates data and stores reports in Cassandra. This works but doesn't allow us to do uniques for custom dimensions. 

>Now we would like to implement user segmentation with custom filters and ad-hoc reports (ideally realtime) which is not possible with current architecture.

>Storage of CH data in memory would work probably but it's too expensive given our limited budget and TBs of data.

SELECT uniqExact(user_id) AS uniques FROM test.merge_tree_logs

This SQL depends on the disk bandwidth and nothing can be done.

But CH has powerful handles (partitions / sortkeys / summing / aggregating engins) which allows to improve speed for more complex SQL.

On Saturday, 4 August 2018 12:46:51 UTC-3, Maxim Ulanovskiy wrote: