PostgreSQL vs. MySQL
On Friday 04 Jul 2003 11:03 am, Rafal Kedziorski wrote:
Hi,
has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with
InnoDB?
Lots of people probably. The big problem is that unless the tester's setup
matches your intended usage the results are of little worth.
For the tests to be meaningful, you need the same:
- hardware
- OS
- query complexity
- usage patterns
- tuning options
I'd suggest running your own tests with real data where possible. Just to make
the situation more interesting, the best way to solve a problem in PG isn't
necessarily the same in MySQL.
From my experience and general discussion on the lists, I'd say MySQL can win
for:
- simple selects
- some aggregates (e.g. count(*))
PG wins for:
- complex queries
- large numbers of clients
- stored procedures/functions
- SQL compliance
--
Richard Huxton
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).
The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
Brian Tarbox
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Rafal
Kedziorski
Sent: Friday, July 04, 2003 6:03 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] PostgreSQL vs. MySQL
Hi,
has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with
InnoDB?
Regards,
Rafal
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
I'm curious what the usage was. How many concurrent processes were
performing the complex queries? I've heard that Postgres does better when
the number of concurrent users is high and MySQL does better when the number
is low. I have no idea if that is true or not.
Michael
I'm actually leaving this list but I can answer this question. Our results
were with a single user and we were running Inodb. We were running on
RedHat 8.0 / 9.0 with vanilla linux settings.
Brian
-----Original Message-----
From: Michael Mattox [mailto:michael.mattox@verideon.com]
Sent: Friday, July 04, 2003 8:36 AM
To: Brian Tarbox; Rafal Kedziorski; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] PostgreSQL vs. MySQL
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
I'm curious what the usage was. How many concurrent processes were
performing the complex queries? I've heard that Postgres does better when
the number of concurrent users is high and MySQL does better when the number
is low. I have no idea if that is true or not.
Michael
I'm actually leaving this list but I can answer this question.
Our results
were with a single user and we were running Inodb. We were running on
RedHat 8.0 / 9.0 with vanilla linux settings.
That's funny, you make a statement that Postgres was 3 times slower than
MySQL and then you promptly leave the list! Just kidding.
It'd be interesting to see what happens if you test your system with a
hundred users. If it's a webapp you can use JMeter to do this really
easily.
Michael
On Friday 04 July 2003 18:16, Michael Mattox wrote:
I'm actually leaving this list but I can answer this question.
Our results
were with a single user and we were running Inodb. We were running on
RedHat 8.0 / 9.0 with vanilla linux settings.That's funny, you make a statement that Postgres was 3 times slower than
MySQL and then you promptly leave the list! Just kidding.It'd be interesting to see what happens if you test your system with a
hundred users. If it's a webapp you can use JMeter to do this really
easily.
Hundred users is a later scenario. I am curious about "vanilla linux settings"
What does that mean.
Postgresql communmity would always like to help who need it but this thread
so far gives me impression that OP isn't willing to provide sufficient
information..
Shridhar
On Friday 04 July 2003 17:57, Brian Tarbox wrote:
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
This invites the slew of questions thereof. Can you provide more information
on
1. Hardware
2. Postgresql version
3. Postgresql tuning you did
4. data size
5. nature of queries
6. mysql benchmarks to rate against.
Unless you provide these, it's difficult to help..
Shridhar
Unless you provide these, it's difficult to help..
http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php
Note the thread with Tom and Brian.
On 4 Jul 2003 at 9:11, Rod Taylor wrote:
Unless you provide these, it's difficult to help..
http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php
Well, even in that thread there wasn't enough information I asked for in other
mail. It was bit too vague to be a comfortable DB tuning problem.
Am I reading the thread wrong? Please correct me.
Bye
Shridhar
--
Ahead warp factor one, Mr. Sulu.
On Fri, 2003-07-04 at 09:20, Shridhar Daithankar wrote:
On 4 Jul 2003 at 9:11, Rod Taylor wrote:
Unless you provide these, it's difficult to help..
http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php
Well, even in that thread there wasn't enough information I asked for in other
mail. It was bit too vague to be a comfortable DB tuning problem.
Completely too little information, and it stopped with Tom asking for
additional information. I don't think Brian has any interest in being
helped. Many here would be more than happy to do so if the information
were to flow.
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
I have never found a query in MySQL that was faster than one in
PostgreSQL.
Chris
"Brian Tarbox" <btarbox@theworld.com> writes:
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).
The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
Could we see the details? It's not very fair to not give us a chance to
learn about problems.
regards, tom lane
Ok, I'll give more data :-)
Under both MySql and Postgres the tests were run on a variety of systems,
all with similar results. My own personal testing was done on a P4 2.4Mhz,
512 mb memory, latest production versions of each database. By vanilla
RedHat I mean that I installed RH on a clean system, said install everything
and did no customization of RH settings.
We had about 40 tables in the db, with joined queries on about 8-12 tables.
Some tables had 10,000 records, some 1000 records, other tables had dozens
of records. There were indexes on all join fields, and all join fields were
listed as foriegn keys. All join fields were unique primary keys in their
home table (so the index distribution would be very spread out). I'm not
permitted to post the actual tables as per company policy.
I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
analyze.
I'll also mention that comments like this one are not productive:
I don't think Brian has any interest in being helped.
Please understand the limits of how much information a consultant can submit
to an open list like this about a client's confidential information. I've
answered every question I _can_ answer and when I get hostility in response
all I can do is sigh and move on.
I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
hominin comments aren't helpful.
Brian
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Shridhar
Daithankar
Sent: Friday, July 04, 2003 8:54 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL vs. MySQL
On Friday 04 July 2003 17:57, Brian Tarbox wrote:
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
This invites the slew of questions thereof. Can you provide more information
on
1. Hardware
2. Postgresql version
3. Postgresql tuning you did
4. data size
5. nature of queries
6. mysql benchmarks to rate against.
Unless you provide these, it's difficult to help..
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On 4 Jul 2003 at 10:07, Brian Tarbox wrote:
Ok, I'll give more data :-)
Under both MySql and Postgres the tests were run on a variety of systems,
all with similar results. My own personal testing was done on a P4 2.4Mhz,
512 mb memory, latest production versions of each database. By vanilla
RedHat I mean that I installed RH on a clean system, said install everything
and did no customization of RH settings.
We had about 40 tables in the db, with joined queries on about 8-12 tables.
Some tables had 10,000 records, some 1000 records, other tables had dozens
of records. There were indexes on all join fields, and all join fields were
listed as foriegn keys. All join fields were unique primary keys in their
home table (so the index distribution would be very spread out). I'm not
permitted to post the actual tables as per company policy.I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
analyze.
No wonder pg bombed out so badly. In fact I am surprised it was slower only by
factor of 3.
Rule of thumb is if you have more than 1K records in any table, you got to tune
postgresql.conf. I don't think I need to elaborate what difference tuning in
postgresql.conf can make.
I'll also mention that comments like this one are not productive:
I don't think Brian has any interest in being helped.
Please understand the limits of how much information a consultant can submit
to an open list like this about a client's confidential information. I've
answered every question I _can_ answer and when I get hostility in response
all I can do is sigh and move on.
Well, definition of threshold of hostile response differ from person to person.
That is understood but by internet standards, I don't think you have received
any hostile response. But that's not the topic I would like to continue to
discuss.
What I would suggest you is to look at some other performance problem
description submitted earlier. I don't think these guys have permission to
disclose sensitive data either but they did everything they could in their
limits.
Look at, http://archives.postgresql.org/pgsql-performance/2003-06/msg00134.php
and the thread thereof. You can reach there from
http://archives.postgresql.org/pgsql-performance/2003-06/threads.php
There is a reason why Michael got so many and so detailed responses. Within
your limits, I am sure you could have posted more and earlier rather than
posting details when original thread is long gone.
I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
hominin comments aren't helpful.
I have no problems personally if postgresql does not work with you. The very
first reason I stick with postgresql is that it works best for me. The moment
it does not work for somebody else, there is a potential problem which I would
like to rectify ASAP. That is the idea of getting on lists and forums.
It's not about product as much it is about helping each other.
And certainly. I have posted weirder qeuries here and I disagree that you
couldn't post more. However this is a judgement from what you have posted and
by all chances it is wrong. Never mind that.
At the end, it's the problem and solution that matters. Peace..
Bye
Shridhar
--
Murphy's Laws: (1) If anything can go wrong, it will. (2) Nothing is as easy as
it looks. (3) Everything takes longer than you think it will.
Rod Taylor <rbt@rbt.ca> writes:
It was bit too vague to be a comfortable DB tuning problem.
Completely too little information, and it stopped with Tom asking for
additional information.
There was something awfully fishy about that. Brian was saying that he
got a seqscan plan out of "WHERE foo = 100", where foo is an integer
primary key. That's just not real credible, at least not once you get
past the couple of standard issues that were mentioned in the thread.
And we never did get word one of information about his join problems.
I don't think Brian has any interest in being helped.
I suspect he'd made up his mind already. Which is his privilege, but
it'd be nice to have some clue what the problem was ...
regards, tom lane
On Fri, Jul 04, 2003 at 10:07:46AM -0400, Brian Tarbox wrote:
512 mb memory, latest production versions of each database. By vanilla
RedHat I mean that I installed RH on a clean system, said install everything
and did no customization of RH settings.
Does that include no customization of the Postgres settings?
We had about 40 tables in the db, with joined queries on about 8-12 tables.
SELECTs only? because. . .
of records. There were indexes on all join fields, and all join fields were
listed as foriegn keys. All join fields were unique primary keys in their
. . .you know that FK constraints in Postgres are not cheap, right?
I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
analyze.
This appears to be a "yes" answer to my question above. Out of the
box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
it is not tuned for performance. Running without even tweaking the
shared buffers is guaranteed to get you lousy performance.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110Please understand the limits of how much information a consultant can submit
to an open list like this about a client's confidential information. I've
answered every question I _can_ answer and when I get hostility in response
all I can do is sigh and move on.
Is there any chance you could show us an EXPLAIN ANALYZE output of the
poor performing query in question?
I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
hominin comments aren't helpful.
It was me who gave the comment based upon previous threads which
requested information that had gone unanswered (not even a response
stating such information could not be provided).
The database you describe is quite small, so I'm not surprised MySQL
does well with it. That said, it isn't normal to experience poor
performance with PostgreSQL unless you've stumbled upon a poor spot (IN
based sub-queries used to be poor performing, aggregates can be slow,
mismatched datatypes, etc.).
Output of EXPLAIN ANALYZE of a contrived query representative of the
type of work done (that demonstrates the problem) with renamed tables
and columns would go a long way to helping us help you.
This appears to be a "yes" answer to my question above. Out of the
box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
it is not tuned for performance. Running without even tweaking the
shared buffers is guaranteed to get you lousy performance.
I see this as a major problem. How many people run postgres, decide it's
too slow and give up without digging into the documentation or coming to
this group? This seems to be pretty common. Even worst, they tell 10
others how slow Postgres is and then it gets a bad reputation.
In my opinion the defaults should be set up for a typical database server
machine.
Michael
"Brian Tarbox" <btarbox@theworld.com> writes:
I'm not permitted to post the actual tables as per company policy.
Nobody wants to see your data, only the table schemas and queries. If
you feel that even that contains some sensitive information, just rename
the table and field names to something meaningless. But the kinds of
problems I am interested in finding out about require seeing the column
datatypes and the form of the queries. The hardware and platform
details you gave mean nothing to me (and probably not to anyone else
either, given that you were comparing to MySQL on the same platform).
I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
analyze.
If you didn't at least bump up shared_buffers, you were deliberately
skewing the results against Postgres. Surely you can't have been
subscribed to pgsql-performance very long without knowing that the
default postgresql.conf settings are set up for a toy installation.
all I can do is sigh and move on.
You're still looking for reasons not to answer our questions, aren't
you? Do you actually want to find out what the problem was here?
If not, you're wasting our list bandwidth. I'd like to find out,
if only so I can try to fix it in future releases, but without useful
information I'll just have to write this off as an unsubstantiated report.
regards, tom lane
