MySQL vs MongoDB 1000 reads

Question

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;
}
?>

User · Answer

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

User · Answer

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       lt br gt  lt Br gt            while the other chunk is spend yacking up a bunch of rand numbers   function get 15 random numbers          numbers   array        for  i 1  i lt  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

User · Answer

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

User · Answer

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

User · Answer

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  amp  reads  compared to MyISAM engine and InnoDb engine    This really should be part of your test   concurrency  amp  MySQL engine  Then  data schema design  amp  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

User · Answer

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

User · Answer

https   github com reoxey benchmark  benchmark  speed comparison of MySQL  amp  MongoDB in GOLANG1 6  amp  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  amp  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  amp  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  amp  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                 lt 1s               SELECT  amp  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                  lt 1s               FIND  amp  DISPLAY ------------------------------------------------ num of rows             time taken ------------------------------------------------ 1000000                 7s               UPDATE ------------------------------------------------ num of rows             time taken ------------------------------------------------ 1000000                 9s

User · Answer

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.

[mysql] MySQL vs MongoDB 1000 reads

The answer is

Examples related to mysql

Examples related to performance

Examples related to mongodb

Tags