-
-
Save dennyabraham/1263267 to your computer and use it in GitHub Desktop.
Simple MySQL vs. MongoDB Insert benchmark.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
------------------------------------------------------------------- |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
for (var i=0; i < 100000; i++) { | |
db.mass_insert.save({ | |
"first_name": "James", | |
"middle_name": "Richard", | |
"last_name": "Windsgate", | |
"email": "jamesrichardwindsgate@email.com" | |
}); | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
> db.mass_insert.getIndexKeys(); | |
[{"_id" : ObjectId( "000000000000000000000000") },{"first_name" : 1},{"middle_name" : 1},{"last_name" : 1},{"email" : 1}] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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