postgresql vs mysql performance comparison

Rick Schumeyer wrote:

I'm interested in comparing the performance of postgresql and mysql

on various combinations of user loads and database sizes. I have seen

a few statements to the effect of "mysql is faster for small, low use

applications, but postgresql is better for bigger stuff". I would like

to run some independent tests to see if this is true.

Before I do this, has anyone done this already, with the latest

versions of both? I think I've seen some comparisons that are

somewhat dated.

Does anyone have any pointers on what to do or not do? Or

would anyone be willing to comment on an experiment plan

and suggest improvements? Any help or references are appreciated.

mysql is finally getting to be closer in full capabilities to postgresql.

some of the newest data isn't yet tested, as they are just releasing a
version that can do what postgresql has done for a while.
free webcast on wednessday this week about it:

http://www.mysql.com/news-and-events/web-seminars/eds-goldengate-mysql.php

mysql has been optimised for speed of porcesses, not security and
enterprise class db apps.
postgresql has been more focusses on the later, as that is where the
real usage is.

On Mon, 2005-03-07 at 12:00, Rick Schumeyer wrote:

I’m interested in comparing the performance of postgresql and mysql

on various combinations of user loads and database sizes. I have seen

a few statements to the effect of “mysql is faster for small, low use

applications, but postgresql is better for bigger stuff”. I would
like

to run some independent tests to see if this is true.

Before I do this, has anyone done this already, with the latest

versions of both? I think I’ve seen some comparisons that are

somewhat dated.

Does anyone have any pointers on what to do or not do? Or

would anyone be willing to comment on an experiment plan

and suggest improvements? Any help or references are appreciated.

Look at the OSDL test suite, and run as much (or as little) of it as you
can against both databases. it's a rather large and complex couple of
suites.

Test under heavy parallel use.

Test returning large data sets from complex queries, esp OLAP type
queries that do a lot of work in one query to mine data in certain ways.

Tune both reasonably according the the best practices for each database.

On Mon, 2005-03-07 at 13:35 -0600, Scott Marlowe wrote:

Tune both reasonably according the the best practices for each database.

Also, you should consult people on the MySQL lists to make sure it's
being tuned properly as well. And make sure both databases have access
to the same hardware resources, i.e. don't start PostgreSQL with a low
shared_buffers setting and let MySQL use a ton of RAM.

Regards,
Jeff Davis

You may want to consider other things...

MySQL adds complexity in that it has several database engines: MyISAM,
InnoDB and MAXDB. All of which have different performance
characteristics. MyISAM is very fast for databases with few
transactions, but InnoDB is more comparable to Postgres in that it
allows superior locking and foreign key integrity etc.

Although not appropriate for a speed comparison, you might want to note
that the use of Mysql versions 4.0 upward now require commercial license
for clients, which are no longer LGPL, whereas Postgres is free (BSD
license). This makes transactions per dollar an interesting statistic
when comparing the Postgres and MySql!

I look forward to seeing the results.

Howard Cole
www.selestial.com

Rick Schumeyer wrote:

--- Howard Cole <howard.cole@selestial.com> wrote:

Although not appropriate for a speed comparison, you
might want to note
that the use of Mysql versions 4.0 upward now
require commercial license
for clients, which are no longer LGPL, whereas
Postgres is free (BSD
license). This makes transactions per dollar an
interesting statistic
when comparing the Postgres and MySql!

Reading over their site that doesn't appear true for
every case. The client libraries are under the GPL
and thus any application that links to them would also
be covered under the GPL. No commercial license is
required unless the terms of the GPL (ie: if you
distribute a binary to someone you must also be
willing to distribute your source code if asked) a
problem.

Regards,

Shelby Cain

__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/

On Tue, 2005-03-08 at 09:06, Shelby Cain wrote:

--- Howard Cole <howard.cole@selestial.com> wrote:

Although not appropriate for a speed comparison, you
might want to note
that the use of Mysql versions 4.0 upward now
require commercial license
for clients, which are no longer LGPL, whereas
Postgres is free (BSD
license). This makes transactions per dollar an
interesting statistic
when comparing the Postgres and MySql!

Reading over their site that doesn't appear true for
every case. The client libraries are under the GPL
and thus any application that links to them would also
be covered under the GPL. No commercial license is
required unless the terms of the GPL (ie: if you
distribute a binary to someone you must also be
willing to distribute your source code if asked) a
problem.

There have been some statements from MySQL in the past that implied they
might be taking a narrower view of what "distribution" meant than what
the GPL was saying. Also, it was impossible for PHP to be packaged with
MySQL libs due to incompatibilities with the GPL'd mysql connection
libs. It seems MySQL AB has clarified both on these pages:

http://www.mysql.com/company/legal/licensing/
http://www.mysql.com/company/legal/licensing/foss-exception.html
http://www.mysql.com/company/legal/licensing/faq.html

However, Fedora Core 2 still includes MySQL V 3.xx.yy because of the
issues wth V4.xx.yy's licensing. However, Suse does include the latest
version. So there's some difference of opinion on the issue from
different distros.

Scott Marlowe <smarlowe@g2switchworks.com> writes:

However, Fedora Core 2 still includes MySQL V 3.xx.yy because of the
issues wth V4.xx.yy's licensing. However, Suse does include the latest
version. So there's some difference of opinion on the issue from
different distros.

Not any more --- it's just that Red Hat's legal department wouldn't
sign off on the deal until after FC3 was released. RHEL4, FC4 and
up contain MySQL 4.x.

regards, tom lane

This will not answer you question, but documents some of the evidence
for you:

http://www.geocities.com/mailsoftware42/db/

Rick Casey, Research Associate
Institute for Behavioral Genetics
rick.casey@colorado.edu
303.735.3518

Rick Schumeyer wrote:

Scott Marlowe wrote:

On Tue, 2005-03-08 at 09:06, Shelby Cain wrote:

--- Howard Cole <howard.cole@selestial.com> wrote:

Although not appropriate for a speed comparison, you
might want to note
that the use of Mysql versions 4.0 upward now
require commercial license
for clients, which are no longer LGPL, whereas
Postgres is free (BSD
license). This makes transactions per dollar an
interesting statistic
when comparing the Postgres and MySql!

Reading over their site that doesn't appear true for
every case. The client libraries are under the GPL
and thus any application that links to them would also
be covered under the GPL. No commercial license is
required unless the terms of the GPL (ie: if you
distribute a binary to someone you must also be
willing to distribute your source code if asked) a
problem.

There have been some statements from MySQL in the past that implied they
might be taking a narrower view of what "distribution" meant than what
the GPL was saying. Also, it was impossible for PHP to be packaged with
MySQL libs due to incompatibilities with the GPL'd mysql connection
libs. It seems MySQL AB has clarified both on these pages:

http://www.mysql.com/company/legal/licensing/
http://www.mysql.com/company/legal/licensing/foss-exception.html
http://www.mysql.com/company/legal/licensing/faq.html

The real issue is that the GPL requires that derivative works are also
covered under the GPL or compatible license. IANAL, but I don't think
anyone in their right mind wants to ask the courts whether a work which
might or might not be (f. ex. links to a GPL'd library) is a derivative
work.

Best Wishes,
Chris Travers
Metatron Technology Consulting

On Tue, 8 Mar 2005, Scott Marlowe wrote:

On Tue, 2005-03-08 at 09:06, Shelby Cain wrote:

--- Howard Cole <howard.cole@selestial.com> wrote:

Although not appropriate for a speed comparison, you
might want to note
that the use of Mysql versions 4.0 upward now
require commercial license
for clients, which are no longer LGPL, whereas
Postgres is free (BSD
license). This makes transactions per dollar an
interesting statistic
when comparing the Postgres and MySql!

Reading over their site that doesn't appear true for
every case. The client libraries are under the GPL
and thus any application that links to them would also
be covered under the GPL. No commercial license is
required unless the terms of the GPL (ie: if you
distribute a binary to someone you must also be
willing to distribute your source code if asked) a
problem.

There have been some statements from MySQL in the past that implied they
might be taking a narrower view of what "distribution" meant than what
the GPL was saying. Also, it was impossible for PHP to be packaged with
MySQL libs due to incompatibilities with the GPL'd mysql connection
libs. It seems MySQL AB has clarified both on these pages:

http://www.mysql.com/company/legal/licensing/
http://www.mysql.com/company/legal/licensing/foss-exception.html
http://www.mysql.com/company/legal/licensing/faq.html

However, Fedora Core 2 still includes MySQL V 3.xx.yy because of the
issues wth V4.xx.yy's licensing. However, Suse does include the latest
version. So there's some difference of opinion on the issue from
different distros.

Or different policies.

One of the biggest problem of their dual licencing policy is that
no one in really interested in provinding them with patches. In other
words, they cannot accept third party contributions so easily.

_My_ patches are going to be, likely, GPL-only. So they can't use
them in their commercial product, unless they make two different
lines (which they claim they don't), or they get a (commercial) licence
from _me_ allowing _them_ to sell a work including _my_ patches.
So in order to accept patches from me, they need a lot of paperwork
(not to mention money, they're gonna pay for being able to sell my
work). Not pratical.

This is not the case of truly GPL software, such as the Linux kernel.
Patches, being a derived work, are GPL and they can include them
anytime.

Note that client libraries are optional. As long the protocol is
openly defined (we have open specs), you can write your own
client layer, and still connect to the GPL server. Which is _the_
thing. Protecting the client library (switching the licence
from LGPL to GPL) makes little sense, IMHO. It greatly reduces
the number of potential users, and protects little value.

If want to develop a commercial application that:
- runs under Linux - I can;
- uses HTTP as protocol, and connects to a GPL-ed web server - I can;
- uses MySQL as a database backend - I can't, unless I rewrite the
client library, or buy a commercial licence from them. Why?

With PostgreSQL you don't have to thing about these issues. A big win.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

Marco Colombo wrote:

If want to develop a commercial application that:
- runs under Linux - I can;
- uses HTTP as protocol, and connects to a GPL-ed web server - I can;
- uses MySQL as a database backend - I can't, unless I rewrite the
client library, or buy a commercial licence from them. Why?

With PostgreSQL you don't have to thing about these issues. A big win.

.TM.

1. Even microsoft client libraries are redistributable! The switch from
LGPL to dual licence for the MySQL client libraries was the sole reason
that I chose to switch to postgres.

2. Rick Casey wrote:

This will not answer you question, but documents some of the

evidence for you:

http://www.geocities.com/mailsoftware42/db/

These reports have many inconsistencies - speed tests were clearly done
with MyISAM tables and the nature of the tests clearly is 'read' biased
with little 'write' - where the MyISAM table level locking really slows
up MyISAM. Also, many of the feature comparissons are for the InnodB
engine or for the Beta release of the database.

When Rick Schumeyer does the tests, please could you include some InnoDB
vs. Postgres benchmarks. I am genuinely interested in the results.

Howard Cole
www.selestial.com