MySQL vs MongoDB 1000 reads
MysqlPerformanceMongodbMysql Problem Overview
I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'.
I wanted to compare speed with MongoDB and I ran a test which would get and print 15 records randomly from our huge databases. I ran the query about 1,000 times each for mysql and MongoDB and I am suprised that I do not notice a lot of difference in speed. Maybe MongoDB is 1.1 times faster. That's very disappointing. Is there something I am doing wrong? I know that my tests are not perfect but is MySQL on par with MongoDb when it comes to read intensive chores.
Note:
- I have dual core + ( 2 threads ) i7 cpu and 4GB ram
- I have 20 partitions on MySQL each of 1 million records
Sample Code Used For Testing MongoDB
<?php
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
$time_taken = 0;
$tries = 100;
// connect
$time_start = microtime_float();
for($i=1;$i<=$tries;$i++)
{
$m = new Mongo();
$db = $m->swalif;
$cursor = $db->posts->find(array('id' => array('$in' => get_15_random_numbers())));
foreach ($cursor as $obj)
{
//echo $obj["thread_title"] . "<br><Br>";
}
}
$time_end = microtime_float();
$time_taken = $time_taken + ($time_end - $time_start);
echo $time_taken;
function get_15_random_numbers()
{
$numbers = array();
for($i=1;$i<=15;$i++)
{
$numbers[] = mt_rand(1, 20000000) ;
}
return $numbers;
}
?>
Sample Code For Testing MySQL
<?php
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
$BASE_PATH = "../src/";
include_once($BASE_PATH . "classes/forumdb.php");
$time_taken = 0;
$tries = 100;
$time_start = microtime_float();
for($i=1;$i<=$tries;$i++)
{
$db = new AQLDatabase();
$sql = "select * from posts_really_big where id in (".implode(',',get_15_random_numbers()).")";
$result = $db->executeSQL($sql);
while ($row = mysql_fetch_array($result) )
{
//echo $row["thread_title"] . "<br><Br>";
}
}
$time_end = microtime_float();
$time_taken = $time_taken + ($time_end - $time_start);
echo $time_taken;
function get_15_random_numbers()
{
$numbers = array();
for($i=1;$i<=15;$i++)
{
$numbers[] = mt_rand(1, 20000000);
}
return $numbers;
}
?>
Mysql Solutions
Solution 1 - Mysql
MongoDB is not magically faster. If you store the same data, organised in basically the same fashion, and access it exactly the same way, then you really shouldn't expect your results to be wildly different. After all, MySQL and MongoDB are both GPL, so if Mongo had some magically better IO code in it, then the MySQL team could just incorporate it into their codebase.
People are seeing real world MongoDB performance largely because MongoDB allows you to query in a different manner that is more sensible to your workload.
For example, consider a design that persisted a lot of information about a complicated entity in a normalised fashion. This could easily use dozens of tables in MySQL (or any relational db) to store the data in normal form, with many indexes needed to ensure relational integrity between tables.
Now consider the same design with a document store. If all of those related tables are subordinate to the main table (and they often are), then you might be able to model the data such that the entire entity is stored in a single document. In MongoDB you can store this as a single document, in a single collection. This is where MongoDB starts enabling superior performance.
In MongoDB, to retrieve the whole entity, you have to perform:
- One index lookup on the collection (assuming the entity is fetched by id)
- Retrieve the contents of one database page (the actual binary json document)
So a b-tree lookup, and a binary page read. Log(n) + 1 IOs. If the indexes can reside entirely in memory, then 1 IO.
In MySQL with 20 tables, you have to perform:
- One index lookup on the root table (again, assuming the entity is fetched by id)
- With a clustered index, we can assume that the values for the root row are in the index
- 20+ range lookups (hopefully on an index) for the entity's pk value
- These probably aren't clustered indexes, so the same 20+ data lookups once we figure out what the appropriate child rows are.
So the total for mysql, even assuming that all indexes are in memory (which is harder since there are 20 times more of them) is about 20 range lookups.
These range lookups are likely comprised of random IO — different tables will definitely reside in different spots on disk, and it's possible that different rows in the same range in the same table for an entity might not be contiguous (depending on how the entity has been updated, etc).
So for this example, the final tally is about 20 times more IO with MySQL per logical access, compared to MongoDB.
This is how MongoDB can boost performance in some use cases.
Solution 2 - Mysql
Do you have concurrency, i.e simultaneous users ? If you just run 1000 times the query straight, with just one thread, there will be almost no difference. Too easy for these engines :)
BUT I strongly suggest that you build a true load testing session, which means using an injector such as JMeter with 10, 20 or 50 users AT THE SAME TIME so you can really see a difference (try to embed this code inside a web page JMeter could query).
I just did it today on a single server (and a simple collection / table) and the results are quite interesting and surprising (MongoDb was really faster on writes & reads, compared to MyISAM engine and InnoDb engine).
This really should be part of your test : concurrency & MySQL engine. Then, data/schema design & application needs are of course huge requirements, beyond response times. Let me know when you get results, I'm also in need of inputs about this!
Solution 3 - Mysql
Source: https://github.com/webcaetano/mongo-mysql
10 rows
mysql insert: 1702ms
mysql select: 11ms
mongo insert: 47ms
mongo select: 12ms
100 rows
mysql insert: 8171ms
mysql select: 10ms
mongo insert: 167ms
mongo select: 60ms
1000 rows
mysql insert: 94813ms (1.58 minutes)
mysql select: 13ms
mongo insert: 1013ms
mongo select: 677ms
10.000 rows
mysql insert: 924695ms (15.41 minutes)
mysql select: 144ms
mongo insert: 9956ms (9.95 seconds)
mongo select: 4539ms (4.539 seconds)
Solution 4 - Mysql
man,,, the answer is that you're basically testing PHP and not a database.
don't bother iterating the results, whether commenting out the print or not. there's a chunk of time.
foreach ($cursor as $obj)
{
//echo $obj["thread_title"] . "<br><Br>";
}
while the other chunk is spend yacking up a bunch of rand numbers.
function get_15_random_numbers()
{
$numbers = array();
for($i=1;$i<=15;$i++)
{
$numbers[] = mt_rand(1, 20000000) ;
}
return $numbers;
}
then theres a major difference b/w implode and in.
and finally what is going on here. looks like creating a connection each time, thus its testing the connection time plus the query time.
$m = new Mongo();
vs
$db = new AQLDatabase();
so your 101% faster might turn out to be 1000% faster for the underlying query stripped of jazz.
urghhh.
Solution 5 - Mysql
https://github.com/reoxey/benchmark
benchmark
speed comparison of MySQL & MongoDB in GOLANG1.6 & PHP5
system used for benchmark: DELL cpu i5 4th gen 1.70Ghz * 4 ram 4GB GPU ram 2GB
Speed comparison of RDBMS vs NoSQL for INSERT, SELECT, UPDATE, DELETE executing different number of rows 10,100,1000,10000,100000,1000000
Language used to execute is: PHP5 & Google fastest language GO 1.6
________________________________________________
GOLANG with MySQL (engine = MyISAM)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
INSERT
------------------------------------------------
num of rows time taken
------------------------------------------------
10 1.195444ms
100 6.075053ms
1000 47.439699ms
10000 483.999809ms
100000 4.707089053s
1000000 49.067407174s
SELECT
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 872.709µs
SELECT & DISPLAY
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 20.717354746s
UPDATE
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 2.309209968s
100000 257.411502ms
10000 26.73954ms
1000 3.483926ms
100 915.17µs
10 650.166µs
DELETE
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 6.065949ms
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
________________________________________________
GOLANG with MongoDB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
INSERT
------------------------------------------------
num of rows time taken
------------------------------------------------
10 2.067094ms
100 8.841597ms
1000 106.491732ms
10000 998.225023ms
100000 8.98172825s
1000000 1m 29.63203158s
SELECT
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 5.251337439s
FIND & DISPLAY (with index declared)
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 21.540603252s
UPDATE
------------------------------------------------
num of rows time taken
------------------------------------------------
1 1.330954ms
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
________________________________________________
PHP5 with MySQL (engine = MyISAM)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
INSERT
------------------------------------------------
num of rows time taken
------------------------------------------------
10 0.0040680000000001s
100 0.011595s
1000 0.049718s
10000 0.457164s
100000 4s
1000000 42s
SELECT
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 <1s
SELECT & DISPLAY
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 20s
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
________________________________________________
PHP5 with MongoDB
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
INSERT
------------------------------------------------
num of rows time taken
------------------------------------------------
10 0.065744s
100 0.190966s
1000 0.2163s
10000 1s
100000 8s
1000000 78s
FIND
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 <1s
FIND & DISPLAY
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 7s
UPDATE
------------------------------------------------
num of rows time taken
------------------------------------------------
1000000 9s
Solution 6 - Mysql
Here is a little research that explored RDBMS vs NoSQL using MySQL vs Mongo, the conclusions were inline with @Sean Reilly's response. In short, the benefit comes from the design, not some raw speed difference. Conclusion on page 35-36:
RDBMS vs NoSQL: Performance and Scaling Comparison > The project tested, analysed and compared the performance and > scalability of the two database types. The experiments done included > running different numbers and types of queries, some more complex than > others, in order to analyse how the databases scaled with increased > load. The most important factor in this case was the query type used > as MongoDB could handle more complex queries faster due mainly to its > simpler schema at the sacrifice of data duplication meaning that a > NoSQL database may contain large amounts of data duplicates. Although > a schema directly migrated from the RDBMS could be used this would > eliminate the advantage of MongoDB’s underlying data representation of > subdocuments which allowed the use of less queries towards the > database as tables were combined. Despite the performance gain which > MongoDB had over MySQL in these complex queries, when the benchmark > modelled the MySQL query similarly to the MongoDB complex query by > using nested SELECTs MySQL performed best although at higher numbers > of connections the two behaved similarly. The last type of query > benchmarked which was the complex query containing two JOINS and and a > subquery showed the advantage MongoDB has over MySQL due to its use of > subdocuments. This advantage comes at the cost of data duplication > which causes an increase in the database size. If such queries are > typical in an application then it is important to consider NoSQL > databases as alternatives while taking in > account the cost in storage and memory size resulting from the larger > database size.
Solution 7 - Mysql
Honestly even if MongoDB is slower, MongoDB definitely makes me and you code faster.... no need to worry about silly table columns, row or entity migrations...
With MongoDB, you just instantiate a class and save!
Solution 8 - Mysql
On Single Server, MongoDb would not be any faster than mysql MyISAM on both read and write, given table/doc
sizes are small 1 GB to 20 GB.
MonoDB will be faster on Parallel Reduce on Multi-Node clusters, where Mysql can NOT scale horizontally.