A small comparison of the performance of the MongoDB vs ClickHouse DBMS

Since ClickHouse column DBMS (Yandex internal development) became available to everyone, I decided to use this DBMS instead of MongoDB for storing analytical data. Before using, I made a small performance test and want to share the results with the IT community.

For the test used nodejs. Libraries for a DBMS under nodejs (driver):
→ ClickHouse
→ MongoDB

Tested on hardware:

i5-3570
8gb DDR3 1333 MHz Kingston
CSSD-N128GB3-BK
OS: Linux Mint 18

Before starting the test, I installed ClickHouse and MongoDB with standard settings. Created a collection in MongoDB "testCollections" and a table in ClickHouse "test" with the MergeTree engine. Here is the structure of the test table in ClickHouse (in MongoDB the structure is dynamic):

create table test (event_date Date DEFAULT toDate(event_time), event_time DateTime DEFAULT now(), number UInt64, text String, month String) engine = MergeTree(event_date, (event_time, number, text, month), 8192); 

Fields event_ * for dates.
number - for a random number.
text - for random text.
month - for a random month.
In MongoDB I will also insert similar data with the same structure.
')
And here you can begin the test. Test data will be done on the "fly", that is, insert the data and watch the results of the implementation of the insert in milliseconds. The test will not update because ClickHouse does not have this operation.

Data structure to insert:

 MongoDB [ { event_date: '2017-01-29', event_time: '2017-01-29 16:00:40', number: 3, text: 'sequi voluptatibus labore', month: 'December' }, ... ] 

 ClickHouse [ [ '2017-01-29', '2017-01-29 16:00:40', 1, 'Et omnis consequatur id eaque suscipit error sed at. Eos ut omnis corporis unde. Tenetur ...', 'July' ], ... ] 

So, let's begin.

Data size: 500 thousand records

In this test we will insert and select data.

- Insert into the database.

MongoDB:



Insert took ~ 8289 ms.

ClickHouse:



Insert took ~ 17375 ms.

Total 17375-8289 = 9086 ms. MongoDB inserts data about 2 times faster than ClickHouse.

- Select data

We will choose how many times the month “December” is repeated in the table (collection).

MongoDB. Request:

 db.testCollections.aggregate([ { "$group" : { _id : "$month", count:{ $sum:1 } } } ]) 

Result 300 ms:



ClickHouse. Request:

 SELECT month, count() AS count FROM test GROUP BY month; 

Result 42 ms:



Total 300-42 = 258 ms. ClickHouse is about 7 times faster to select data with grouping than MongoDB.

Let's make another simple select with limit 20.

MongoDB:

  db.testCollections.find().limit(20); 

Result 13 ms:



ClickHouse:

 SELECT * FROM test limit 20; 

Result 24 ms:



Total for select with limit 20 ClickHouse spent approximately 2 more time than MongoDB.

Let's multiply the data 10 times and repeat the test for data sampling.

Data size: 5 million records

In this test, we will select the data (that is, the data described above is already inserted into both databases).
Result count in both databases:



- Select data

Same queries with grouping. MongoDB:



Sampling time 3145 ms.

ClickHouse:



Sampling time 112 ms.

ClickHouse with a given amount of data is ten times faster. Regular select with limit 20 was the same as with 500 thousand records.

Let's multiply the data 10 times and repeat the test for data sampling. (something I put on 500 thousand records more, there will be 50 500 000 records).

Data size: 50.5 million records

Result count in both databases:



- Select data

Same queries with grouping. MongoDB:

The mongodb client for nodejs fell off after 35009 ms. Here is the result from the client GUI for working with MongoDB:



Sampling time 1m 12,571 s.

ClickHouse:



Sampling time 931 ms.

ClickHouse is clearly leading the way.

Summary

ClickHouse is great for projects with BigData. Sampling speed is impressive. This DBMS is a good choice for storing data associated with analytical information.

Project with tests: see .