MySQL slower than SQL Server?

Rails
1

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

2

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.

3

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

4

Bryan D. 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?

5

and compared also with postgres? which is the faster? mysql, sql server
or postgres?

6

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 O.
http://weblog.techno-weenie.net
http://mephistoblog.com

7

Rick O. 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 O.
http://weblog.techno-weenie.net
http://mephistoblog.com

Nope, I still consistently get at 190+ using the MySQL native driver.

8

On 20/02/07, Rub R. [email protected] wrote:

(a) MySQL : ~190.0
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

9

Tom W. said the following on 02/20/2007 06:36 AM:

On 20/02/07, Rub R. [email protected] wrote:

[snip]

(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

10

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 ***
© PostgreSQL : ~ 11.1

For SELECTs:

(a) MySQL : ~7.2
(b) SQL Server : ~8.2
© 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).