Skip to content

Instantly share code, notes, and snippets.

@dennyabraham
Forked from jimeh/mass_insert_mongo.js
Created October 5, 2011 00:29
Show Gist options
  • Save dennyabraham/1263267 to your computer and use it in GitHub Desktop.
Save dennyabraham/1263267 to your computer and use it in GitHub Desktop.
Simple MySQL vs. MongoDB Insert benchmark.
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
-------------------------------------------------------------------
for (var i=0; i < 100000; i++) {
db.mass_insert.save({
"first_name": "James",
"middle_name": "Richard",
"last_name": "Windsgate",
"email": "jamesrichardwindsgate@email.com"
});
};
#! /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" => "jamesrichardwindsgate@email.com"
# })
end
#! /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','jamesrichardwindsgate@email.com');"
# )
end
> db.mass_insert.getIndexKeys();
[{"_id" : ObjectId( "000000000000000000000000") },{"first_name" : 1},{"middle_name" : 1},{"last_name" : 1},{"email" : 1}]
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment