Hi,
I was just doing some simple tests using DBI, and I''ve found that the
performance when using MySQL as the database is a lot slower than using
SQL Server.
Here is my setup:
- machine: Pentium 4, 2.80GHz, 1GB RAM
- OS: Windows XP Service Pack 2
- ruby: Ruby Installer for Windows, Ruby Version 1.8.5p12, Installer
Version 185-22
- DBI: 0.1.1
- MySQL: mysql-essential-5.0.27
- SQL Server: SQL Server Express 2005
Both database systems were installed with default options; in each, I
created the database "test" with table as follows:
CREATE TABLE t (id INT NOT NULL, s VARCHAR(50))
The simple test program I used was as follows (for SQL Server):
*****
require ''dbi''
t0 = Time.new.to_f
db = DBI.connect(''DBI:ODBC:Driver={SQL Native
Client};Server=(local)\\SQLEXPRESS;Database=test;Trusted_Connection=yes'')
0.upto(10000) do |id|
s = "String #{id}"
sql = "INSERT INTO t VALUES (#{id}, ''#{s}'')"
db.do(sql)
end
db.disconnect
t1 = Time.new.to_f
puts (t1 - t0)
****
and the only difference for when I tested with MySQL was the line
connecting to the database, as follows:
db = DBI.connect(''DBI:Mysql:test'',
''test_user'', ''test_pwd'')
The results I got were as follows:
(a) SQL Server : 10.xxxxx
(b) MySQL : 197.xxxxx
So now, I''m just curious as to why there is a big difference between
the
two? Is it because SQL Server by default installs with some sort of
optimised options whereas MySQL does not? Does it have to do with the
difference in the drivers being used (ODBC vs Mysql)?
(And just for fun, I did a similar test using C# with SQL Server, and
the result I got with that was 9.xxxx).
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
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''m not surprised at all by your findings. I was involved with a project that required optimal insert performance and was eventually forced to drop MySQL entirely after much tweaking and experimentation. After converting the application to use an OpenBase database, insert performance went up by about a factor of 10 and we were finally able to handled the high rate of inserts our application demanded. Don''t misunderstand me, I use MySQL wherever and whenever possible. But, if your looking for maximum performance, especially for inserts and updates, MySQL is likely not the right fit for the job. Others may have different experiences, but I was very impressed by the difference in performance between MySQL and OpenBase. Sorry I''ve never tried comparing MySQL and MS SQL Server since I don''t use Windows or any MS Technology. On Feb 19, 9:39 am, Rub Rub <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi, > > I was just doing some simple tests using DBI, and I''ve found that the > performance when using MySQL as the database is a lot slower than using > SQL Server. > > Here is my setup: > > - machine: Pentium 4, 2.80GHz, 1GB RAM > - OS: Windows XP Service Pack 2 > - ruby: Ruby Installer for Windows, Ruby Version 1.8.5p12, Installer > Version 185-22 > - DBI: 0.1.1 > - MySQL: mysql-essential-5.0.27 > - SQL Server: SQL Server Express 2005 > > Both database systems were installed with default options; in each, I > created the database "test" with table as follows: > > CREATE TABLE t (id INT NOT NULL, s VARCHAR(50)) > > The simple test program I used was as follows (for SQL Server): > > ***** > > require ''dbi'' > > t0 = Time.new.to_f > > db = DBI.connect(''DBI:ODBC:Driver={SQL Native > Client};Server=(local)\\SQLEXPRESS;Database=test;Trusted_Connection=yes'') > > 0.upto(10000) do |id| > s = "String #{id}" > sql = "INSERT INTO t VALUES (#{id}, ''#{s}'')" > db.do(sql) > end > > db.disconnect > > t1 = Time.new.to_f > > puts (t1 - t0) > > **** > > and the only difference for when I tested with MySQL was the line > connecting to the database, as follows: > > db = DBI.connect(''DBI:Mysql:test'', ''test_user'', ''test_pwd'') > > The results I got were as follows: > > (a) SQL Server : 10.xxxxx > (b) MySQL : 197.xxxxx > > So now, I''m just curious as to why there is a big difference between the > two? Is it because SQL Server by default installs with some sort of > optimised options whereas MySQL does not? Does it have to do with the > difference in the drivers being used (ODBC vs Mysql)? > > (And just for fun, I did a similar test using C# with SQL Server, and > the result I got with that was 9.xxxx). > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Rub Rub wrote:> Hi, > > I was just doing some simple tests using DBI, and I''ve found that the > performance when using MySQL as the database is a lot slower than using > SQL Server. > > Here is my setup: > > - machine: Pentium 4, 2.80GHz, 1GB RAM > - OS: Windows XP Service Pack 2 > - ruby: Ruby Installer for Windows, Ruby Version 1.8.5p12, Installer > Version 185-22 > - DBI: 0.1.1 > - MySQL: mysql-essential-5.0.27 > - SQL Server: SQL Server Express 2005 > > Both database systems were installed with default options; in each, I > created the database "test" with table as follows: > > CREATE TABLE t (id INT NOT NULL, s VARCHAR(50)) > > The simple test program I used was as follows (for SQL Server): > > ***** > > require ''dbi'' > > t0 = Time.new.to_f > > db = DBI.connect(''DBI:ODBC:Driver={SQL Native > Client};Server=(local)\\SQLEXPRESS;Database=test;Trusted_Connection=yes'') > > 0.upto(10000) do |id| > s = "String #{id}" > sql = "INSERT INTO t VALUES (#{id}, ''#{s}'')" > db.do(sql) > end > > db.disconnect > > t1 = Time.new.to_f > > puts (t1 - t0) > > **** > > and the only difference for when I tested with MySQL was the line > connecting to the database, as follows: > > db = DBI.connect(''DBI:Mysql:test'', ''test_user'', ''test_pwd'') > > The results I got were as follows: > > (a) SQL Server : 10.xxxxx > (b) MySQL : 197.xxxxx > > So now, I''m just curious as to why there is a big difference between the > two? Is it because SQL Server by default installs with some sort of > optimised options whereas MySQL does not? Does it have to do with the > difference in the drivers being used (ODBC vs Mysql)? > > (And just for fun, I did a similar test using C# with SQL Server, and > the result I got with that was 9.xxxx).One thing I noticed in your tests is that you include the connection time inside the benchmark. In my experience with using MySQL via ODBC, making the connection is very slow. However, you only need to do it once when you''re using Rails (when the application starts). I''m not familiar with using DBI directly, so I don''t know if you''re connecting to MySQL via ODBC or not. However, I think it would be prudent to move the connection code outside of the benchmark and see how it performs. -Bryan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Bryan Duxbury wrote:> I''m not familiar with using DBI directly, so I don''t know if you''re > connecting to MySQL via ODBC or not. However, I think it would be > prudent to move the connection code outside of the benchmark and see how > it performs.Did as you suggested, however MySQL is still an order of magnitude slower (results I got were around 194). Also, I thought I would do similar tests using SELECT, and again SQL Server outperformed MySQL considerably. Perhaps I''m missing some possible configuration tweaks to MySQL? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
> Perhaps I''m missing some possible configuration tweaks to MySQL?You''ll probably see a big improvement using the native mysql driver for ruby. -- Rick Olson http://weblog.techno-weenie.net http://mephistoblog.com --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
and compared also with postgres? which is the faster? mysql, sql server or postgres? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Rick Olson wrote:>> Perhaps I''m missing some possible configuration tweaks to MySQL? > > You''ll probably see a big improvement using the native mysql driver for > ruby. > > -- > Rick Olson > http://weblog.techno-weenie.net > http://mephistoblog.comNope, I still consistently get at 190+ using the MySQL native driver. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
nick wrote:> and compared also with postgres? which is the faster? mysql, sql server > or postgres?Did some more tests, and PostgreSQL 8.2 performed really well. Results below. For INSERTs: (a) MySQL : ~190.0 (b) SQL Server : ~ 10.9 *** BEST *** (c) PostgreSQL : ~ 11.1 For SELECTs: (a) MySQL : ~7.2 (b) SQL Server : ~8.2 (c) PostgreSQL : ~2.7 *** BEST *** Overall, I would say PostgreSQL comes out on top. Obviously, my tests are hardly conclusive. Do people find PostgreSQL to be faster than MySQL? (I know, the answer is probably "it depends", but I am just asking generally). -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 20/02/07, Rub Rub <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > nick wrote: > > and compared also with postgres? which is the faster? mysql, sql server > > or postgres? > > Did some more tests, and PostgreSQL 8.2 performed really well. Results > below. > > For INSERTs: > > (a) MySQL : ~190.0 > (b) SQL Server : ~ 10.9 *** BEST *** > (c) PostgreSQL : ~ 11.1 > > For SELECTs: > > (a) MySQL : ~7.2 > (b) SQL Server : ~8.2 > (c) PostgreSQL : ~2.7 *** BEST *** > > Overall, I would say PostgreSQL comes out on top. Obviously, my tests > are hardly conclusive. > > Do people find PostgreSQL to be faster than MySQL? (I know, the answer > is probably "it depends", but I am just asking generally).When trying to compare database performance, the answer is always ''it depends''. There are just so many factors to consider (the driver, the size of data set, the complexity of queries, indexes, optimization, etc.) that a fair comparison is pretty much impossible. I think MySQL, PostgreSQL and SQL Server have all proven to be fast enough to run high-performance web applications against. So the choice of which one to use for a given application is more a matter of taste. Personally, if I had free choice I would use PostgreSQL as I''ve found it a pleasure to work with. At work it''s mandated that we use SQL Server so we use that for all our rails development, and again, I''m pretty happy with it. Tom --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Tom Ward said the following on 02/20/2007 06:36 AM:> On 20/02/07, Rub Rub <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: >> [snip]>> >> For INSERTs: >> >> (a) MySQL : ~190.0 >> (b) SQL Server : ~ 10.9 *** BEST *** >> (c) PostgreSQL : ~ 11.1 >> >> For SELECTs: >> >> (a) MySQL : ~7.2 >> (b) SQL Server : ~8.2 >> (c) PostgreSQL : ~2.7 *** BEST *** >> >> Overall, I would say PostgreSQL comes out on top. Obviously, my tests >> are hardly conclusive.Indeed. For selects - and you don''t indicate the complexity or size or indexing - MySQL and SQLServer are within error bounds of each other. If the inserts are only occasional the disparity doesn''t matter. And if you are running something like Amazon on MySQL then there''s the bulk loader.>> Do people find PostgreSQL to be faster than MySQL? (I know, the answer >> is probably "it depends", but I am just asking generally).The "It depends" probably has error bars which are more than 100% of the results you obtained. For example, MySQL has different engines - InnoDB, MyISAM, (compared to MyISAM, InnoDB performs additional safeguards to guarantee data integrity), issues to do with logging, issues to do with checking authorization; the ability to ignore roll-back, and quite probably performs differently on different platforms (You ran this comparison on AIX, HP-UX?) For example: http://www.mysql.com/news-and-events/newsletter/2003-11/a0000000269.html> I think MySQL, PostgreSQL and SQL Server have all proven to be fast > enough to run high-performance web applications against.Indeed. And many of the corporate clients I''ve worked with that use three-tier applications use Big Iron of some kind, up to and including mainframes, as their database engines. Its been pointed out here that this kind of architecture lends itself to highly clustered web servers, so the database engine is, so long as you have a driver and its SQL-compliant, pretty arbitrary. I have no doubt that as Rails becomes poplar, it will address things like stored procedures and get drivers or proxy drivers for highly specialised databases. I certainly wouldn''t let any manager be misled on the basis of these tests. They are no more meaningful than compiler races or the speed tests of Apache vs IIS. "It All Depends". Have you ever looked under the hood on the leading databases and seen how many parameters you can tweak? There''s a lot of "it all depends" that it can depend on! -- It''s a funny thing about life; if you refuse to accept anything but the best, you very often get it. W. Somerset Maugham --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---