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
Note : Query fails even for two weeks in MySQL
Query fails for 6 months in 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:
| DB Size |  Clickhouse |
| Query Performance | Clickhouse |
| Window functions | MySQL Columnstore  Clickhouse |
| Insert | MySQL Columnstore Clickhouse |
| Update, Delete | MySQL Columnstore Clickhouse |
Our workload doesn’t have any updates or deletes, so we have chosen Clickhouse and we are in production now.
