Rails Benchmarks with PostgreSQL and MySQL

[Matt White]

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:
1. Transactions are good. Use them when you can.
2. 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.
3. 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.

[Faisal N Jawdat]

On Sep 13, 2007, at 1:59 PM, Matt White wrote:
> 3. 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.

In my tests, PostgreSQL starts to pass MySQL as your queries grow
more complicated and your concurrent load increases. A lot depends
on application.

-faisal

[Philip Hallstrom]

> 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.

Re-run your tests, but instead of having a single rails app do this, spawn
50-100 apps and have them do it concurrently. I suspect you'll find that
the results are just about opposite of what you got below...

[gene tani]

On Sep 13, 11:36 am, Philip Hallstrom <ra...@philip.pjkh.com> wrote:

probably won't shock to find that benchmarking pg vs. my is a major
cottage industry. From google:

http://tweakers.net/reviews/657/6
http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/

http://blog.page2rss.com/2007/01/postgresql-vs-mysql-performance.html
http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html

[Mitch Pirtle]

Quoting gene tani <gene...@gmail.com>, who spaketh thusly:
>
> probably won't shock to find that benchmarking pg vs. my is a major
> cottage industry.

...and is also the database analogue to the whole vi-emacs debate. I'd
say each have their advantages, so it's more a decision based on your
requirements rather than a one-size-fits-all type of judgement.

I am a big fat PostgreSQL homer though *blush*

-- Mitch

[David Mitchell]

Rather than quoting feature lists and performance figures that could
be questioned endlessly, how about this approach?

Ask whoever's going to fill the DBA role for your project, and go with
their recommendation.

I'm not a fulltime DBA, but I play one sometimes as the situation
arises. If you were my customer and came to me and asked "I've got a
startup software project, and I can't decide whether I should use
Postgres or MySQL. Give me the answer, oh anointed database expert
and wise consultant", this would be the gist of my response:

Go with Postgres.

My reasons:
- I know Postgres much better than I know MySQL. In particular, I
know that the out of the box config is crap, and I know how to quickly
apply some generic tuning fixes that'll generally make it zing. If it
still works badly for your application, I know how to go about
tracking down the cause and solving it
- for just about any question involving the functionality of Postgres,
I know how to find an answer. It may not be the best answer, but at
least I'll be able to give you an answer
- I know how to maintain Postgres, how to keep it running, how to do
backups with minimal impact and how to verify that those backups are
OK. In short, if you choose Postgres, I can keep it running for you
- if/when your app reaches the limits of Postgres' single-box
scalability, I can help you with scaling it horizontally, and I can
even help you migrate from Postgres to e.g. Oracle or DB2 if that
rocks your boat. In other words, you won't hit scalability issues
that can't be resolved on my watch, sailor. It may cost you money,
but that's part of the cost of having a successful app
- if you ask for my advice, then go against my advice and use MySQL,
don't expect me to help you out later when you've got problems unless
you're rich. If you're rich *and* make a habit of taking bad advice,
well I've got some prime swampland real estate to sell you...
- if you decide to use MySQL, make sure you go find a DBA that you
trust and who can say exactly the same things as I did but
substituting Postgres for MySQL and vice versa

Honestly, it's not like your startup project is going to sink or fly
based on whether you've chosen MySQL or Postgres - they both work
reliably, they both work well, 95%+ of their features apply to both
products, and both are likely to be perfectly capable of meeting the
needs of 99.9% of startup projects where an open-source database is
actually an appropriate choice. If you have to change from one to the
other, it's generally not a major nightmare provided you haven't got
100,000 users expecting 24x7 availability (and even then the
challenges are primarily logistical rather than technical). Your
project's far more likely to sink if you've chosen technology that you
don't know how to maintain, or you don't have access to someone with
the skills and commitment to maintain it for you.

Regards

Dave M.