Wednesday, 6 August 2014

MySql vs MongoDB performance benchmark



 

MySql vs MongoDB performance benchmark


Problem

We’re searching for the fastest solution to solve the following two use cases:
  • fastest data retrieval to get 5000/50000 points out of 10 million points,
  • fastest data retrieval to get 5000/50000/500000 points out of 200 million points.
We use these points to generate JS charts.
For testing choose MongoDB vs MySql. There is no complex relation between tables/objects, the test case is very simple.
Beside the select time we also monitored the fetch time as we need the data to be fetched. We also tested with and without cache (where possible) – our specific project has the possibility to cache data. On this benchmark not tested scalability in detail but it seems that running the test on 8-20 threads the situation doesn’t change very much.

System and config

The benchmark machine is Linux (Ubuntu 12.04 64-bit) with 4 Cores and 10GB of RAM.
MySql cache config (in my.cnf):
query_cache_limit = 1024M
query_cache_type = 1
query_cache_size = 1512M

On Mongo used the default config with journal = true (because didn’t notice any considerably difference on runtime with journal = false and went for the safer option)

Test results

I) Retrieval of 5000/50000 out of 10 million points – lower database load
Generated a random database with 10 million rows. Every row has 5 fields: 2 integers and 3 random strings.
The insert operation is made on 8 threads, inserts are split among threads.
Also for select and fetch used threads (1,2,4 and 8 threads per run) but in our test case every thread runs the same operation.
On select and fetch selected the rows (5000 and 50000 rows) from the total of 10 million rows and made a similar fetch operation for both MySql and MongoDB.
The results output is streamed from shell scripts into csv.

5000 rows selected out of 10 million rows
database operation total rows threads no of selected rows run time (select + fetch) CPU usage (4 cores) select time
Mongo Insert 10000000 8 0 411121 ms 137%CPU 0
MySQL Insert 10000000 8 0 1130493 ms 149%CPU 0
Mongo Select+Fetch 10000000 1 5000 17411 ms 105%CPU 3 ms first run
MySQL Select+Fetch 10000000 1 5000 5836 ms 109%CPU 5369 ms first run
Mongo Select+Fetch 10000000 1 5000 6450 ms 116%CPU 2 ms
MySQL Select+Fetch 10000000 1 5000 512 ms 208%CPU 66 ms
Mongo Select+Fetch 10000000 2 5000 12507 ms 110%CPU 3 ms
MySQL Select+Fetch 10000000 2 5000 565 ms 236%CPU 69 ms
Mongo Select+Fetch 10000000 4 5000 28129 ms 106%CPU 2 ms
MySQL Select+Fetch 10000000 4 5000 592 ms 255%CPU 72 ms
Mongo Select+Fetch 10000000 8 5000 75047 ms 64%CPU 1 ms
MySQL Select+Fetch 10000000 8 5000 759 ms 275%CPU 66 ms

50000 rows selected out of 10 million rows
database operation total rows threads no of selected rows run time (select + fetch) CPU usage (4 cores) select time
Mongo Insert 10000000 8 0 410866 ms 137%CPU 0
MySQL Insert 10000000 8 0 1150706 ms 147%CPU 0
Mongo Select+Fetch 10000000 1 500000 13049 ms 118%CPU 1 ms first run
MySQL Select+Fetch 10000000 1 500000 6128 ms 117%CPU 5599 ms first run
Mongo Select+Fetch 10000000 1 500000 6955 ms 138%CPU 2 ms
MySQL Select+Fetch 10000000 1 500000 959 ms 212%CPU 447 ms
Mongo Select+Fetch 10000000 2 500000 12445 ms 142%CPU 3 ms
MySQL Select+Fetch 10000000 2 500000 1110 ms 264%CPU 533 ms
Mongo Select+Fetch 10000000 4 500000 30251 ms 133%CPU 2 ms
MySQL Select+Fetch 10000000 4 500000 1483 ms 307%CPU 676 ms
Mongo Select+Fetch 10000000 8 500000 82642 ms 99%CPU 3 ms
MySQL Select+Fetch 10000000 8 500000 2220 ms 346%CPU 1433 ms

On operation Select + Fetch on multiple threads ran the same operation for each thread. Ex: on 8 threads made 8 operations of select + fetch (one on each thread).
threads – represents the thread number

CPU usage – represents the CPU usage that the Java process took
first run – it’s the first run without any cache made, the other runs are with cache
run time – it’s the total runtime of select+fetch
select time – it’s the average time of select


Conclusions for this test case
On first run (no cache on MySql): total fetch + select Mongo time is 17.4s (3ms select time) at MySql it is 5.8s (5.3s select time). Notice here the Mongo select time 3 ms! This is probably caused by the fact that it has some lazy processing.
On next runs (the MySql cache enters): total fetch + select Mongo time is 6.9s (2ms select time) at MySql it is 0.9s (0.4s select time). Notice here the big impact of cache on MySql!

For a project of this scale that also has the possibility to cache the data requests it seems that overall MySql is much better for read operations.
Even If not possible to cache the data MySql would still be a winner. MySql better by a 2X – 5X speed for reading.
Notice the good insert speed on Mongo: it is 2-3X faster than MySql.

The 50000 test gives similar results.
II) Retrieval of 5000/50000/500000 out of 200 million points – higher database load
Generated a random database with 200 million rows. Every row has 5 fields: 2 integers and 3 random strings.
The insert operation is made on 8 threads, inserts are split among threads.
Also for select and fetch used threads (1,2,4 and 8 threads per run) but in our test case every thread runs the same operation.
On select and fetch selected the rows (5000, 50000 and 500000 rows) from the total of 200 million rows and made a similar fetch operation for both MySql and Mongo.
The results output is streamed from some shell scripts into csv.

5000 selected rows out of 200 million rows
database operation total rows threads no of selected rows run time (select + fetch) CPU usage (4 cores) select time
Mongo Insert 200000000 8 0 8283056 ms 134%CPU 0
MySQL Insert 200000000 8 0 22270948 ms 147%CPU 0
Mongo Select+Fetch 200000000 1 5000 185969 ms 100%CPU 4 ms first run
MySQL Select+Fetch 200000000 1 5000 325609 ms 100%CPU 324876 ms first run
Mongo Select+Fetch 200000000 1 5000 170406 ms 100%CPU 0 ms
MySQL Select+Fetch 200000000 1 5000 797 ms 131%CPU 64 ms
Mongo Select+Fetch 200000000 2 5000 366705 ms 100%CPU 3 ms
MySQL Select+Fetch 200000000 2 5000 1088 ms 151%CPU 71 ms
Mongo Select+Fetch 200000000 4 5000 626023 ms 100%CPU 3 ms
MySQL Select+Fetch 200000000 4 5000 846 ms 158%CPU 85 ms
Mongo Select+Fetch 200000000 8 5000 1564526 ms 73%CPU 2 ms
MySQL Select+Fetch 200000000 8 5000 1048 ms 181%CPU 38 ms

50000 selected rows out of 200 million rows
database operation total rows threads no of selected rows run time (select + fetch) CPU usage (4 cores) select time
Mongo Insert 200000000 8 0 8366874 ms 134%CPU 0
MySQL Insert 200000000 8 0 22592623 ms 148%CPU 0
Mongo Select+Fetch 200000000 1 50000 191197 ms 100%CPU 2 ms first run
MySQL Select+Fetch 200000000 1 50000 323260 ms 100%CPU 322279 ms first run
Mongo Select+Fetch 200000000 1 50000 172113 ms 100%CPU 3 ms
MySQL Select+Fetch 200000000 1 50000 1374 ms 120%CPU 144 ms
Mongo Select+Fetch 200000000 2 50000 349728 ms 100%CPU 3 ms
MySQL Select+Fetch 200000000 2 50000 991 ms 154%CPU 113 ms
Mongo Select+Fetch 200000000 4 50000 596883 ms 100%CPU 2 ms
MySQL Select+Fetch 200000000 4 50000 1160 ms 180%CPU 134 ms
Mongo Select+Fetch 200000000 8 50000 1446121 ms 75%CPU 2 ms
MySQL Select+Fetch 200000000 8 50000 1296 ms 201%CPU 196 ms

500000 selected rows out of 200 million rows
database operation total rows threads no of selected rows run time (select + fetch) CPU usage (4 cores) select time
Mongo Insert 200000000 8 0 8388447 ms 134%CPU 0
MySQL Insert 200000000 8 0 23010922 ms 148%CPU 0
Mongo Select+Fetch 200000000 1 500000 188207 ms 101%CPU 2 ms first run
MySQL Select+Fetch 200000000 1 500000 321247 ms 100%CPU 320165 ms first run
Mongo Select+Fetch 200000000 1 500000 172975 ms 101%CPU 2 ms
MySQL Select+Fetch 200000000 1 500000 1265 ms 162%CPU 460 ms
Mongo Select+Fetch 200000000 2 500000 371833 ms 103%CPU 3 ms
MySQL Select+Fetch 200000000 2 500000 1514 ms 188%CPU 511 ms
Mongo Select+Fetch 200000000 4 500000 626729 ms 104%CPU 0 ms
MySQL Select+Fetch 200000000 4 500000 1768 ms 230%CPU 711 ms
Mongo Select+Fetch 200000000 8 500000 1545454 ms 78%CPU 2 ms
MySQL Select+Fetch 200000000 8 500000 2403 ms 293%CPU 1374 ms
Conclusions for this test case
On first run (no cache on MySql): total fetch + select Mongo time is 85s (4ms select time) at MySql it is 325s (324s select time). Again the select at Mongo is fast: 4 ms. Probably at select Mongo makes nothing, the lazy processing implementation appears again and the fetch is slower.

On next runs (the MySql cache enters): total fetch + select Mongo time is 170s (0ms select time) at MySql it is 0.7s (0.064s select time). Notice here the big impact of cache on MySql!

For a project of this scale that also has the possibility to cache the data requests it seems that overall MySql is still much better for read operations.
If it is not possible to cache data Mongo would be almost 1.3X – 2X faster than MySql.
Notice the good insert speed on Mongo: it is 2-3X faster than MySql.

The 50000 and 500000 tests give similar results.

Source code used for tests

MySQL Code:
database drop and create:
database insert code executed on 8 threads:
mysqlgen3.argu – rows number (example 10 million) / threads (example 8 threads)
mysqlgen3.nrselect – it`s the number of the selected rows
select + fetch:
database insert code executed on 8 threads:
select + fetch:
On both MySQL and Mongo used threads for select+fetch and insert. Example of implemented threads:
threadCount is the number of threads and MyThread class contains the presented code above.
Related Posts Plugin for WordPress, Blogger...