Simple MySQL vs. MongoDB Insert benchmark.

readme.txt

Simple MySQL vs. MongoDB Insert benchmark.

I created this benchmark cause I'll soon start building a very
write intense project. It's undoubtedly not a very scientific
benchmark, but at least it is a benchmark.

I would be very happy to hear comments/suggestions.


-------------------------------------------------------------------
- Hardware & Software
-------------------------------------------------------------------

Machine:
 - MacBook Pro 15-inch (August 2007)
    - 2.4GHz Core 2 Duo
    - 4GB RAM
    - 320GB 7200rpm HHD

Software:
 - Mac OS X 10.6.1
 - Ruby 1.8.7p174 (64bit)
 - Ruby 1.9.1p243 (64bit)
 - MySQL 5.1.38 (64bit)
    - Gem: mysql 2.8.1
 - Mongo 1.0 (64bit)
    - Gem: mongodb-mongo 0.14.1
    - Gem: mongodb-mongo_ext 0.14.1


-------------------------------------------------------------------
- The Process
-------------------------------------------------------------------

For both MySQL and Mongo I ran the tests inserting 100,000 randomly
generated, and static rows/docs, as Ruby needed CPU to generate the
random data. Both tests for both databases were run using Ruby
1.8.7 and 1.9.1.

For each test, I ran the scripts three times, and calculated the
average between all three times. After each set of three runs, I
cleared the database table/collection from all rows/docs.


-------------------------------------------------------------------
- Running the Tests
-------------------------------------------------------------------

Here's an example of how I ran these tests from the terminal.

$ time /opt/local/bin/ruby mass_insert_mongo.rb     # Ruby 1.8.7
$ time /opt/local/bin/ruby1.9 mass_insert_mongo.rb  # Ruby 1.9.1
$ time mongo 127.0.0.1/test mass_insert_mongo.js    # Mongo Shell


-------------------------------------------------------------------
- Result Summary
-------------------------------------------------------------------

                     Ruby 1.8.7           Ruby 1.9.1
MySQL (random)    24.72 sec (4,044)    25.41 sec (3,935)
MySQL (static)    26.86 sec (3,723)    26.63 sec (3,754)
Mongo (random)    28.14 sec (3,553)    18.30 sec (5,463)
Mongo (static)    28.64 sec (3,491)    16.69 sec (5,990)

                     Mongo Shell
Mongo (static)    10.39 sec (9,623)

-------------------------------------------------------------------

Numbers within brackets are number of inserts per second.

From what I can gather, it seems I'm hitting the limit of what
MySQL can handle in all test. As for Mongo, the native Ruby driver
seems to be the main bottleneck.

With all the MySQL test, Ruby was using about 10% CPU, while MySQL
was eating up the other CPU core with roughly 95% usage. For Mongo
with Ruby 1.8, ruby was eating one core at about 90% and mongod
was using roughly 40% of the other core. While using Ruby 1.9,
Ruby's CPU usage was about the same, but mongod was around 60%.

As for the Mongo Shell run, the mongo (client shell) was using
95% CPU, while mongod was using about 70%.

From this, I would gather I never quite reached the pack of what
mongod (the server) can handle. The client drivers seem to be the
biggest bottleneck. Altho I'm sure someone more intelligent than
me in these matters could draw a more concrete summary.


-------------------------------------------------------------------
- Result Details
-------------------------------------------------------------------

                                  Ruby 1.8.7
                  1st Pass    2nd Pass    3rd Pass    Average
MySQL (random)    22.68 sec   25.25 sec   26.24 sec   24.72 sec
MySQL (static)    25.93 sec   26.70 sec   27.94 sec   26.86 sec
Mongo (random)    28.29 sec   27.87 sec   28.27 sec   28.14 sec
Mongo (static)    28.81 sec   28.47 sec   28.64 sec   28.64 sec

-------------------------------------------------------------------

                                  Ruby 1.9.1
                  1st Pass    2nd Pass    3rd Pass    Average
MySQL (random)    23.28 sec   25.84 sec   27.12 sec   25.41 sec
MySQL (static)    25.95 sec   26.73 sec   27.23 sec   26.63 sec
Mongo (random)    17.90 sec   18.79 sec   18.22 sec   18.30 sec
Mongo (static)    16.84 sec   16.61 sec   16.63 sec   16.69 sec

-------------------------------------------------------------------

                                  Mongo Shell
                  1st Pass    2nd Pass    3rd Pass    Average
Mongo (static)    10.23 sec   10.60 sec   10.34 sec   10.39 sec

-------------------------------------------------------------------

mass_insert_mongo.js

for (var i=0; i < 100000; i++) {
	db.mass_insert.save({
		"first_name": "James",
		"middle_name": "Richard",
		"last_name": "Windsgate",
		"email": "[email protected]"
	});
};

mass_insert_mongo.rb

#! /usr/bin/env ruby
require "rubygems"
require "mongo"

firstnames  = [ "John", "Jim", "James", "Sarah", "Anna", "Maria", "Sophia", "Martin", "Nick", "Bart" ]
middlenames = [ "", "Richard", "Hanna", "Drew", "Jonas", "Marie", "Linc", "Matthew", "David", "Mark" ]
lastnames   = [ "Smith", "Johnsson", "Andrews", "McCloud", "Windsgate",
                "Integra", "Hellfire", "Mickelsson", "Rickson", "Dickson" ]

limit = 100000

db = Mongo::Connection.new.db("test")
coll = db.collection("mass_insert")
# coll.clear

limit.times do |i|
  # 
  # Random data insert
  # 
  first = firstnames[rand(firstnames.count-1)]
  middle = middlenames[rand(middlenames.count-1)]
  last = lastnames[rand(lastnames.count-1)]
  email = "#{first}#{middle}#{last}@email.com".downcase
  coll.insert({"first_name" => first, "middle_name" => middle, "last_name" => last, "email" => email})
  # 
  # Static data insert
  # 
  # coll.insert({
  #   "first_name" => "James", "middle_name" => "Richard",
  #   "last_name" => "Windsgate", "email" => "[email protected]"
  # })
end

mass_insert_mysql.rb

#! /usr/bin/env ruby
require "rubygems"
require "mysql"

firstnames  = [ "John", "Jim", "James", "Sarah", "Anna", "Maria", "Sophia", "Martin", "Nick", "Bart" ]
middlenames = [ "", "Richard", "Hanna", "Drew", "Jonas", "Marie", "Linc", "Matthew", "David", "Mark" ]
lastnames   = [ "Smith", "Johnsson", "Andrews", "McCloud", "Windsgate",
                "Integra", "Hellfire", "Mickelsson", "Rickson", "Dickson" ]

limit = 100000

my = Mysql.new("localhost", "root", "")
my.query("use test;")
# my.query("truncate table mass_insert;")

limit.times do |i|
  # 
  # Random data insert
  # 
  first = firstnames[rand(firstnames.count-1)]
  middle = middlenames[rand(middlenames.count-1)]
  last = lastnames[rand(lastnames.count-1)]
  email = "#{first}#{middle}#{last}@email.com".downcase
  my.query("INSERT INTO `mass_insert` " +
    "(`first_name`,`middle_name`,`last_name`,`email`) VALUES " +
    "('#{first}','#{middle}','#{last}','#{email}');"
  )
  # 
  # Static data insert
  # 
  # my.query("INSERT INTO `mass_insert` " +
  #   "(`first_name`,`middle_name`,`last_name`,`email`) VALUES " +
  #   "('James','Richard','Windsgate','[email protected]');"
  # )
end

mongo_db_info.txt

> db.mass_insert.getIndexKeys();
[{"_id" :  ObjectId( "000000000000000000000000") },{"first_name" : 1},{"middle_name" : 1},{"last_name" : 1},{"email" : 1}]

mysql_db_info.sql

CREATE TABLE `mass_insert` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) DEFAULT NULL,
  `middle_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `first_name` (`first_name`),
  KEY `middle_name` (`middle_name`),
  KEY `last_name` (`last_name`),
  KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

this is quite nice, thanks for sharing!

@jimeh Awesome. you are inserting sequentially though ( as I saw in this file - https://gist.github.com/jimeh/189646#file-mass_insert_mysql-rb ).

limit.times do |i|
   ....
   coll.insert(pseudoDocx)
end

Did you consider inserting concurrently to the same collection with/without sharding, probably using 5 or 10 threads.