MongoDB not that faster than MySQL?

I discovered mongodb some months ago,and after reading this post, I thought mongodb was really faster than mysql, so I decided to build my own bench, the problem is that I do not have the same result than the above post's author, especially for quering the database : mongodb seems to be slower than MyISAM tables. Could you have a look to my python code, may be there is something wrong 开发者_运维问答in it :

from datetime import datetime
import random
import MySQLdb
import pymongo

mysql_db=MySQLdb.connect(user="me",passwd="mypasswd",db="test_kv")
c=mysql_db.cursor()

connection = pymongo.Connection()
mongo_db = connection.test
kvtab = mongo_db.kvtab

nb=1000000
thelist=[]
for i in xrange(nb):
    thelist.append((str(random.random()),str(random.random())))
t1=datetime.now()

for k,v in thelist:
    c.execute("INSERT INTO key_val_tab (k,v) VALUES ('" + k + "','" + v + "')")

dt=datetime.now() - t1
print 'MySQL insert elapse :',dt

t1=datetime.now()
for i in xrange(nb):
    c.execute("select * FROM key_val_tab WHERE k='" + random.choice(thelist)[0] + "'")
    result=c.fetchone()

dt=datetime.now() - t1
print 'MySQL select elapse :',dt


t1=datetime.now()

for k,v in thelist:
    kvtab.insert({"key":k,"value":v})

dt=datetime.now() - t1
print 'Mongodb insert elapse :',dt
kvtab.ensure_index('key')
t1=datetime.now()
for i in xrange(nb):
    result=kvtab.find_one({"key":random.choice(thelist)[0]})

dt=datetime.now() - t1
print 'Mongodb select elapse :',dt

Notes:

• both MySQL and mongodb are on locahost.
• both MySQL and mongodb has the 'key' column indexed

MySQL Table:

CREATE TABLE IF NOT EXISTS `key_val_tab` (
  `k` varchar(24) NOT NULL,
  `v` varchar(24) NOT NULL,
  KEY `kindex` (`k`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Versions are:

• MySQL: 5.1.41
• mongodb : 1.8.3
• python : 2.6.5
• pymongo : 2.0.1
• Linux : Ubuntu 2.6.32 32Bits with PAE
• Hardware : Desktop core i7 2.93 Ghz

Results (for 1 million inserts/selects) :

MySQL insert elapse : 0:02:52.143803
MySQL select elapse : 0:04:43.675914
Mongodb insert elapse : 0:00:49.038416  -> mongodb much faster for insert
Mongodb select elapse : 0:05:10.409025  -> ...but slower for quering (thought was the opposite)

---

Sigh. These kind of benchmarks, and I use the term loosely in this case, usually break down from the very start. MySQL isn't a "slower" database than MongoDB. One is a relational database, the other a NoSQL document store. They will/should be faster in the functional areas that they were designed to cover. In the case of MySQL (or any RDBMS) and MongoDB this overlap isn't as big as a lot of people assume it is. It's the same kind of broken apples and oranges comparison you get with Redis vs. MongoDB discussions.

There are so many variables (app functional requirements, hardware resources, concurrency, configuration, scalability, etc.) to consider that any benchmark or article that ends with "MongoDB is faster than MySQL" or vice versa is generalizing results to the point of uselessness.

If you want to do benchmark first define a strict set of functional requirements and business rules and then implement them as efficiently as possible on both persistence solutions. The result will be that one is faster than the other and in almost all cases the faster approach has some relevant downsides that might still make the slower solution more viable depending on requirements.

All this is ignoring that the benchmark above doesn't simulate any sort of real world scenario. There wont be a lot of apps doing max throughput inserts without any sort of threading/concurrency (which impacts performance on most storage solutions significantly).

Finally, comparing inserts like this is a little broken too. MongoDB can achieve amazing insert throughput with fire and forget bulk inserts or can be orders of magnitude slower with fsynced, replicated writes. The thing here is that MongoDB offers you a choice where MySQL doesn't (or less so). So here the comparison only make sense of the business requirements allow for fire and forget type writes (Which boil down to, "I hope it works, but no biggy if it didn't")

TL;DR stop doing simple throughput benchmarks. They're almost always useless.

---

MySQL insert elapse : 0:02:52.143803
Mongodb insert elapse : 0:00:49.038416  -> mongodb much faster for insert

Mongodb inserts much faster because of mongodb insert all data into ram and then periodically flush data to the disc.

MySQL select elapse : 0:04:43.675914
Mongodb select elapse : 0:05:10.409025  -> ...but slower for quering (thought was

You can achieve best performance with mongodb when you will embedd/denormalize your data. In many situation mongodb allow us to avoid joins because of embedding/denormalization.

And when you just inserting data into one collection/table and reading back by index mongodb not supposed to be faster, read speed should be ~ same if compare with sql database.

BTW: In mongodb 2.0 indexes 25% faster, so i guess 2.0 will work faster then mysql.

---

It's wrong to look at python execution time and estimate database quality. Each request consist of at least 3 parts:

• request preparing (client side),
• request execution (server),
• response preparing (client side)

By my experience data convertion for MongoDB=>python takes much more time than for MySQL=>python.

Also you should use indexes in both databases. MongoDB works good only if you have indexes on fields that you use for queries. Talking about MySQL, I think it's better to test performance on innoDB, MyISAM doesn't support transactions, foreign keys, triggers and as for me it's a little bit outdated.