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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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...> > 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. > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 13, 11:36 am, Philip Hallstrom <ra...-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Quoting gene tani <gene.tani-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>, 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On 14/09/2007, Mitch Pirtle <mitch-FYK0AvAIAVJIdMtHoxk3LkEOCMrvLtNR@public.gmane.org> wrote:> > Quoting gene tani <gene.tani-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>, 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 > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---