Hello,
I did some simple benchmarking tests on a PostgreSQL database and
MySQL database. This isn’t meant to prove that one is better than the
other, but is just an experiment. The tests were run on my laptop with
the database hosted on an idle quad-core Xeon server accessed over a
LAN (latency is pretty low, < 1ms). Not much extra configuration and
tuning has been done on these databases so I’m sure they can both go
faster under various circumstances. I didn’t spend a lot of time on
this so I’m sure a lot of different optimizations can be done but it
would be interesting to get feedback from people with ideas as to how
I can get my Rails apps to perform faster. I’d be happy to re-run the
benchmarks with new ideas. Some of my conclusions are at the bottom.
If you have more to add or details to fill in, please do so. Thanks.
Benchmarking key:
“Rails” indicates no optimization, just a basic Rails app with code to
create the records looking like:
users.each do |user|
User.create(user)
end
“Rails with transaction” indicates the exact same Rails app except
code creating the records looking like:
User.transaction do
users.each do |user|
User.create(user)
end
end
“Ruby” indicates using the specified DB adapter and the code to create
the records looking like:
users.each do |user|
db.query(“INSERT INTO users(name, address, city, state, zip,
country, phone, email) VALUES(’#{user[:name]}’, ‘#{user[:address]}’,
‘#{user[:city]}’, ‘#{user[:state]}’, ‘#{user[:zip]}’,
‘#{user[:country]}’, ‘#{user[:phone]}’, ‘#{user[:email]}’)”)
end
Creating 10000 records on a table with 1 int(11) column and 8
varchar(255) columns, time measured in seconds:
MySQL (InnoDB engine)
Rails: 255.546999931335
Rails with transaction: 35.875
Ruby: 109.18799996376
MySQL (MyISAM engine)
Rails: 88.6099998950958
Rails with transaction: 51.3910000324249
Ruby: 1.2350001335144
PostgreSQL
Rails: 260.375
Rails with transaction: 144.546999931335
Ruby: 36.2340002059937
Conclusions:
- Transactions are good. Use them when you can.
- MyISAM is usually faster than InnoDB, and sometimes WAY faster in
the case of the simple Ruby script (you read that right, 1.235
seconds), but certainly not when transactions come into play. - MySQL out of the box is often faster than PostgreSQL out of the
box. I’ve heard, however, that with tuning and multiple cores,
PostgreSQL has a marked advantage.