Tag words

Menu Utente



RSS

Vedana.it RSS Vedana.it RSS
Testing MongoDB vs. MySQL with python scripting under Linux PDF Print E-mail
Written by Martino Vedana   
Tuesday, 11 January 2011 00:00
mongodb-mysql-postToday I'm pygoing to test two DBMS to understand which is the faster and how much in some specific cases. The focus is on simple read, write and delete of a different amount of data.
This is useful to evaluate if in a project which DBMS to use or if is better to use different DBMS's to cover different areas.
I'm going to test Oracle's MySQL and mongoDB. Why these Databases? Because the first one is the most used in small/medium web environments (and also is used in large installations like Facebook) and today can be defined the “base comparsion” to evaluate other DBMS. The second one is the most attractive DBMS in terms of popularity (the community is growing), performances (seems to be much more faster than other Relational DBMS) and features (like GridFS).
Let's take a look to what Wikipedia says about our two comers:

MongoDB (from "humongous") is an open source, scalable, high-performance, schema-free, document-oriented database written in the C++ programming language.
MongoDB combines the functionality of key-value stores - which are fast and highly scalable - and traditional RDBMS systems - which provide rich queries and deep functionality. It is designed for problems that are difficult to be solved by traditional RDBMs, for example databases spanning many servers.
The database is document-oriented so it manages collections of JSON-like documents. Many applications can, thus, model data in a more natural way, as data can be nested in complex hierarchies and still be query-able and indexable.
Development of MongoDB began in October 2007 by 10gen. The first public release was in February 2009.

MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is officially pronounced /maɪˌɛskjuːˈɛl/ ("My S-Q-L"), but is often also pronounced /maɪˈsiːkwəl/ ("My Sequel"). It is named after developer Michael Widenius' daughter, My. The SQL phrase stands for Structured Query Language.
The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.
Members of the MySQL community have created several forks (variations) such as Drizzle, OurDelta, Percona Server, and MariaDB. All of these forks were in progress before the Oracle acquisition; Drizzle was announced eight months before the Sun acquisition.
Free-software projects that require a full-featured database management system often use MySQL. Where the project may lead to something in commercial use, the license terms need careful study. Some free software project examples: WordPress, phpBB, Drupal and other software built on the LAMP software stack. MySQL is also used in many high-profile, large-scale World Wide Web products, including Wikipedia, Google and Facebook.

Let's Start!- What I'm testing


What I want to test is the speed of mySQL DBMS vs, mongoDB during INSERT, DELETE or SEARCH statement of small values such as decimal numbers or strings (identified by numeric keys). The range of rows is 100 ~ 1.000.000 so that means the probably all the rows are store in ram. Anyway in the future I'll test larger tables (more than 1.000.000 values).
Other target is to test the two DBMS using python (there's already a huge number of php/java tests done)
I tried to have the same conditions when working with MySQL and mongoDB so I wrote two python scripts, one for MySQL and one for mongoDB with the same structure.

Testing environment:

Software:
Python    Python 2.6.6    
PyMongo    01.07.00    
MySQLdb    01.02.02    
MySQL    5.1.49-1ubuntu8.1    
MongoDB     1:1.4.4-2ubuntu2    
Operating System:    Ubuntu Linux kernel v.2.6.35-24 (32 bit)    

Hardware:
Type : Intel® Core™2 Duo Processor T6600
Speed : 2.20GHz
FSB : 800MHz
Cache : 2MB L2 cache
RAM: 4GB (2 x 2GB) DDR3 1066 SDRAM

The testing scripts

The test core script insert, search and then delete 1000, 10.000, 100.000 and 1.000.000 values.
Note that the search part is a sequence of 5 search on a random key and the final value is an average between the five results.

The connections libraies are:
PyMongo v. 01.07.00 for MongoDB and MySQLdb v. 01.02.02 for MySQL

Tables structure

Mysql structure:
CREATE TABLE IF NOT EXISTS `test`
(    
`k` varchar(25) NOT NULL,
`v` varchar(25) NOT NULL,
PRIMARY KEY (`k`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

Mysql core script

def DeleteAll(self):
    self.cursor.execute("DELETE FROM test ")
def Search(self,k):
    self.cursor.execute ("SELECT v FROM test WHERE k=" + str(k))
    row = self.cursor.fetchone ()
def testInsert(self,c):
    ...
    for i in range(0,c):
        s = "INSERT INTO test (k,v) VALUES ('" + thelist[i] + "','" + str(random.random()) + "')"
    self.cursor.execute (s)
    ...

Download the complete script clicking below

MySQL testing scripts

mongoDB core script

# generic insert function 
def Insert(self,k,v): 
    self.db.test.insert({"key":k,"value":v}) 
# generic Delete all rows function 
def DeleteAll(self): 
    cur = self.db.test.find()    
    cur.collection.remove() 
# generic search function 
    def Search(self,k): 
    cur = self.db.test.find({"key":str(k)})
  Download the complete script clicking below

mongoDB testing scripts

Results


Let's start with MySQL results:
As you can see this table shows you the sec. needed to perform insertion, deleting or search on 100,1.000,10.000, 100.000 and 1.000.000 rows

MySQL


Rows Insert Delete Search
100 0,018083 0,000498 0,000829
1.000 0,122136 0,000482 0,000803
10.000 1,194098 0,000793 0,00526
100.000 12,609569 0,001876 0,033776
1.000.000 131,915952 0,013592 0,333886


And now the results of mongoDB test:

MongoDB


Rows Insert Delete Search
100 0,005402 0,000456 0,000034
1.000 0,085084 0,000092 0,000035
10.000 0,916512 0,000082 0,000036
100.000 4,78384 0,000906 0,00005
1.000.000 52,49221 0,002042 0,00005


As you can easily see in the results mongoDB is always much more faster than mySQL. In only one case mySQL is more or less equal to mongoDB: deleting of  100 rows. In all other cases mongodb is from 1,5 to 6000 times faster!!!

Lets see a comparsion table (how many times MongoDB was faster than MySQL):

Rows Insert Delete Search
100 3,35 1,09 24,38
1.000 1,44 5,24 22,94
10.000 1,30 9,67 146,11
100.000 2,64 2,07 675,52
1.000.000 2,51 6,66 6677,72


This means that for example inserting 100.000 rows in mongoDB is 1,30 times faster than in MySQL.
The more shocking result is that searching 1 key in a table containing 1.000.000 rows (indexed) is 6.677 times faster with mongoDB than mySQL in this environment.
I executed the same test on other testing machine and this value has changed a bit from 4000 to 7000 times faster. In my opinion is not a matter of 7.000 or 1.000; the key point is that we have a result with three zeros: thousands times faster!

Let's see graphically the three cases:

insert

search

delete


What can I say? Fast, fast and again: fast; mongoDB is in all cases faster than MySQL. Non relational DBMS were born to be faster than relational DBMSs and this goal was reached by mongoDB!

If you have notes, points to improve you can leave a note to continue the discussion about this test

Thanks to Flavio and Angel for supporting me during testing activities!
¡Gracias Amigos!