Official Mafiree Blogs

Keeping you informed about Databases

Significance of using Invisible Primary key (GIPK) with MySQL 8.0
By Sukan   |   February 21, 2023   |   Posted in : MySQL
How Generated Invisible Primary Keys (GIPK) Can Boost Your Database Efficiency

Achieving High Availability Using Log Shipping
By Sujith   |   February 21, 2018   |   Posted in : SQL Server
Here we will get the detailed explanation of how we can achieve HA using Log Shipping.

SQL Server On Linux
By Sujith   |   March 30, 2019   |   Posted in : SQL Server
SQL Server 2017 brings the best features of the Microsoft relational database engine to the enterprise Linux ecosystem, including SQL Server Agent, Azure Active Directory (Azure AD) authentication, best-in-class high availability/ disaster recovery, and unparalleled data security.

Types of SQL Server Replication
By Sujith   |   February 19, 2018   |   Posted in : SQL Server
Understanding on the types of replication in SQL Server and the type apt for your traffic pattern.

Data File Splitting in SQL Server
By Murali   |   March 15, 2021   |   Posted in : SQL Server
Huge data blocks resides under single MDF file might cause a performance of the query and impact the application services. This blog help you to understand how we can the split the single MDF file into multiple data files.

 Home  /  Clickhouse  
Benchmark :: MySQL Vs ColumnStore Vs Clickhouse
By Sukan   |   May 27, 2019   |   Posted in : 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

 

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 yes Clickhouse
Query Performance yes Clickhouse
Window functions yes MySQL yes Columnstore no Clickhouse
Insert yes MySQL yes Columnstore yes Clickhouse
Update, Delete yes MySQL yes Columnstore no Clickhouse
  

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

 



0 Comments


Leave a Comment

Name *

Email *

Comment *



Search All Blogs



Need Support?


Solutions      Services      Resources      About Mafiree

More than 3000 servers monitored

Certified database Administrators

24X7X365 Support

Happy Clients


ENQUIRE NOW
For Sales Related Queries, Please Call Our Sales Experts at

 +91-80-41155993


Meet Mafiree on social networks!

     

PCI Certificate

Copyright © 2019 - All Rights Reserved - Mafiree