Benchmark :: MySQL Vs ColumnStore Vs Clickhouse

Recently one of our clients wanted to replicate data from MySQL to analytics database. As in MySQL we have to wait for hours to get output if the range was high and managing data size was another challenge that we had as a month data growth was around 300G.

Choosing the right database for OLAP is difficult as each product has its own design, SQL standards, features etc.. which cannot be matched with OLTP applications.

Also each application workload behave differently on analytic database products because of the server config, data size and mainly the application queries.

We started to benchmark Columnstore of MariaDB and Clickhouse of Yandex. Both are columnar storage.

Our workload was majorly time series data. This benchmark has really helped us to decide to move to the right product for our workload.

Data Size

MySQL - 298.95 G

Columnstore - 24.6 G

Clickhouse - 11.4 G

Wow. This is good. Can you believe ~300G came down to ~24G in Columnstore and ~11G in Clickhouse?

Query Performance

Note : Query fails on 6 month in MySQL and Columnstore

Verdict :

Clickhouse stands out in time series queries especially for larger data set, it’s performance is way better than MySQL and Columnstore for larger time series.

Note: This results cannot be matched with other application queries as each query behave differently.

Takeaways:

Our workload doesn’t have any updates or deletes, so we have chosen Clickhouse and we are in production now.