Thread: postgresql vs mysql performance comparison
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.
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.
Attachment
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: > I’m interested in comparing the performance of postgresql and mysql > > on various combinations of user loads and database sizes. > >
--- 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: > 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. >
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