Обсуждение: PostgreSQL vs. MySQL

От:
Rafal Kedziorski
Дата:

Hi,

has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with InnoDB?


Regards,
Rafal


От:
Achilleus Mantzios
Дата:

PostgreSQL (as being a really advanced RDBMS),
generally requires some tuning in order to get
the best performance.

Your best bet is to try both.

Also check to see IF mysql has
-Referential integrity
-subselects
-transactions
-(other usefull features like arrays,user defined types,etc..)
(its probable that you will need some of the above)

On Fri, 4 Jul 2003, Rafal Kedziorski wrote:

> 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?
>
>                http://archives.postgresql.org
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill at matrix dot gatewaynet dot com
        mantzios at softlab dot ece dot ntua dot gr


От:
Richard Huxton
Дата:

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

От:
"Brian Tarbox"
Дата:

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: 
[mailto:]On Behalf Of Rafal
Kedziorski
Sent: Friday, July 04, 2003 6:03 AM
To: 
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?

               http://archives.postgresql.org


От:
"Michael Mattox"
Дата:

> 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



От:
"Brian Tarbox"
Дата:

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:]
Sent: Friday, July 04, 2003 8:36 AM
To: Brian Tarbox; Rafal Kedziorski; 
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


От:
"Michael Mattox"
Дата:

> 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



От:
Shridhar Daithankar
Дата:

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


От:
Shridhar Daithankar
Дата:

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


От:
Rod Taylor
Дата:

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

От:
"Shridhar Daithankar"
Дата:

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.


От:
Rod Taylor
Дата:

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.

От:
Christopher Kings-Lynne
Дата:

> 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



От:
Tom Lane
Дата:

"Brian Tarbox" <> 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

От:
"Brian Tarbox"
Дата:

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: 
[mailto:]On Behalf Of Shridhar
Daithankar
Sent: Friday, July 04, 2003 8:54 AM
To: 
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 


От:
"Shridhar Daithankar"
Дата:

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.


От:
Tom Lane
Дата:

Rod Taylor <> 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

От:
Andrew Sullivan
Дата:

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
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
Rod Taylor
Дата:

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

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.


От:
"Michael Mattox"
Дата:

> 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



От:
Tom Lane
Дата:

"Brian Tarbox" <> 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

От:
"Shridhar Daithankar"
Дата:

On 4 Jul 2003 at 16:35, Michael Mattox wrote:

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

Well, there are few major reasons defaults are the way they are and the reason
it hurts the way they are

1. Postgresql expects to start on every machine on which it can run. Now some
of the arcane platforms need kernel recompilation to raise SHMMAX and defaults
to 1MB.

2. Postgresql uses shared memory being process based architecture. Mysql uses
process memory being threaded application. It does not need  kernel settings to
work and usually works best it can.

3. We expect users/admins to be reading docs. If one does not read docs, it
does not matter what defaults are. Sooner or later, it is going to fall on it's
face.

4. Unlike likes of Oracle, postgresql does not pre-claim resources and starts
hogging the system, replacing OS whereever possible. No it does not work that
way..

One thing always strikes me. Lot of people(Not you Michael!..:-)) would
complain that postgresql is slow and needs tweaking are not bothered by the
fact that oracle needs almost same kind of and same amount of tweaking to get
somewhere. Perception matterrs a lot.

I would have whined for java as well but this is not the forum for that..:-)

On a positive note, me and Josh are finishing a bare bone performance article
that would answer lot of your questions. I am counting on you to provide
valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm
that..



Bye
 Shridhar

--
Theorem: a cat has nine tails.Proof:    No cat has eight tails. A cat has one tail
more than no cat.    Therefore, a cat has nine tails.


От:
Rafal Kedziorski
Дата:

hi,

At 20:19 04.07.2003 +0530, Shridhar Daithankar wrote:
[...]

>On a positive note, me and Josh are finishing a bare bone performance article

where will be this article published?

>that would answer lot of your questions. I am counting on you to provide
>valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm
>that..


Rafal


От:
Rod Taylor
Дата:

> 2. Postgresql uses shared memory being process based architecture. Mysql uses
> process memory being threaded application. It does not need  kernel settings to
> work and usually works best it can.

MySQL has other issues with the kernel due to their threading choice
such as memory limits per process, or poor threaded SMP support on some
platforms (inability for a single process to use more than one CPU at a
time regardless of thread count).

Threads aren't an easy way around kernel limitations, which is probably
why Apache has gone for a combination of the two -- but of course that
adds complexity.

От:
Shridhar Daithankar
Дата:

On Friday 04 July 2003 20:36, Rod Taylor wrote:
> > 2. Postgresql uses shared memory being process based architecture. Mysql
> > uses process memory being threaded application. It does not need  kernel
> > settings to work and usually works best it can.
>
> MySQL has other issues with the kernel due to their threading choice
> such as memory limits per process, or poor threaded SMP support on some
> platforms (inability for a single process to use more than one CPU at a
> time regardless of thread count).
>
> Threads aren't an easy way around kernel limitations, which is probably
> why Apache has gone for a combination of the two -- but of course that
> adds complexity.

Correct. It's not debate about whether threading is better or not. But it
certainly affects the default way with which these two applications work.

 Shridhar


От:
Andrew Sullivan
Дата:

On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:

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

There have been various proposals to do things of this sort.  But
there are always problems with it.  For instance, on many OSes,
Postgres would not run _at all_ when you first compiled it if its
defaults were set more agressively.  Then how many people would
complain, "It just doesn't work," and move on without asking about
it?

I cannot, for the life of me, understand how anyone can install some
software which is supposed to provide meaningful results under
production conditions, and not bother to read even the basic
"quickstart"-type stuff that is kicking around.  There is _no secret_
that Postgres is configured as a toy out of the box.  One presumes
that DBAs are hired to do _some_ little bit of work.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
Shridhar Daithankar
Дата:

On Friday 04 July 2003 20:56, Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:
> > 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.
>
> There have been various proposals to do things of this sort.  But
> there are always problems with it.  For instance, on many OSes,
> Postgres would not run _at all_ when you first compiled it if its
> defaults were set more agressively.  Then how many people would
> complain, "It just doesn't work," and move on without asking about
> it?

There was a proposal to ship various postgresql.conf.sample like one for large
servers, one for medium, one for update intensive purpose etc.

I was thinking over it. Actaully we could tweak initdb script to be
interactiev and get inputs from users and tune it accordingly. Of course it
would be nowhere near the admin reading the docs. but at least it won't fall
flat on performance groundas the way falls now.

 Shridhar


От:
"Kevin Schroeder"
Дата:

That would be something that I'd like to see.  Being new to PostgreSQL some
of the basics of tuning the database were a little hard to find.  The reason
people go with MySQL is because it's fast and easy to use.  That's why I had
been using it for years.  Then when a problem came along and I couldn't use
MySQL I checked out PostgreSQL and found that it would fill the gap, but I
had been able to get by on doing very little in terms of administration for
MySQL (which performed well for me) and I was expecting PostgreSQL to be
similar.  As with many people I have the hat of DB admin, server admin,
programmer and designer and the less I have to do in any of those areas
makes my life a lot easier.

When I first started using PostgreSQL I installed it and entered my data
without any thought of having to tune it because I never had to before.  If
there were some program that could be inserted to the end of the make
process or something it might help dimwits like me :-) realize that there
was more that needs to be done once the installation has been completed.

Kevin


----- Original Message -----
From: "Shridhar Daithankar" <>
To: <>
Sent: Friday, July 04, 2003 10:28 AM
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


> On Friday 04 July 2003 20:56, Andrew Sullivan wrote:
> > On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:
> > > 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.
> >
> > There have been various proposals to do things of this sort.  But
> > there are always problems with it.  For instance, on many OSes,
> > Postgres would not run _at all_ when you first compiled it if its
> > defaults were set more agressively.  Then how many people would
> > complain, "It just doesn't work," and move on without asking about
> > it?
>
> There was a proposal to ship various postgresql.conf.sample like one for
large
> servers, one for medium, one for update intensive purpose etc.
>
> I was thinking over it. Actaully we could tweak initdb script to be
> interactiev and get inputs from users and tune it accordingly. Of course
it
> would be nowhere near the admin reading the docs. but at least it won't
fall
> flat on performance groundas the way falls now.
>
>  Shridhar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


От:
"Brian Tarbox"
Дата:

> I don't think Brian has any interest in being helped.
>I suspect he'd made up his mind already.


With all due respect Tom, I don't think I'm the one demonstrating a closed
mind.
Rather than trying to figure out whats going on in my head, how about
figuring out whats going on in my database?  :-)

I'm answering every question I can.  I supplied HW info because someone
asked, and then Tom said: "The hardware and platform details you gave mean
nothing to me...".  Which would you like guys??

I am not allowed to share schemas...sorry but thats what the contract says.
The queries represent code, thus intellectual property, thus I can't post
them.

I posted an Explain output at some point and was told my database was too
small to be fast.  So, I added 10,000 records, vacummed, and my selects were
still the same speed.

How many people on this list have asked for a tuning/performance doc?  I
hear that there is one coming soon.  Thats great.  Saying RTM is fine too,
if the manual is clear.  Look at Michael Mattox's thread on this very topic
on 6/24.  Michael said:
"I think the biggest area of confusion for me was that the various
parameters
are very briefly described and no context is given for their parameters.?

Shridhar then suggested he change OSes, upgrade his kernel (with specific
patches), get different HW, etc.  That goes a bit beyond casual tuning.


I'm not saying (and never did say) that postgres could not be fast.  All I
ever said was that with the same minimal effort applied to both DBs,
postgres was slower.

I really wasn't looking for battle this fine day....I'm going outside to
BBQ!  (and if you conclude from that that I'm not interested in this or
that, there's nothing I can do about that.  It is a beautiful day out and
bbq does sound more fun than this list.  sorry)

Brian


От:
"Bjoern Metzdorf"
Дата:

> I'm not saying (and never did say) that postgres could not be fast.
> All I ever said was that with the same minimal effort applied to both
> DBs, postgres was slower.

Afaik, your original posting said postgresql was 3 times slower than mysql
and that you are going to leave this list now. This implied that you have
made your decision between postgresql and mysql, taking mysql because it is
faster.

Now you say your testing setup has minimal effort applied. Well, it is not
very surprising that mysql is faster in standard configurations. As Shridhar
pointed out, postgresql has very conservative default values, so that it
starts on nearly every machine.

If I was your client and gave you the task to choose a suitable database for
my application and you evaluated suitable databases this way, then something
is seriously wrong with your work.

Regards,
Bjoern



От:
"Kevin Schroeder"
Дата:

My goodness people!!  If you are just going to bash people who are trying to
learn PostgreSQL then you have no chance of ever getting new people using
it!  Cut out this crap and do what this list is meant to do, which is, I'm
assuming, helping people figure out why their installations aren't running
as fast as they would like.  This is pathetic!!

Kevin

----- Original Message -----
From: "Bjoern Metzdorf" <>
To: "Postgresql Performance" <>
Sent: Friday, July 04, 2003 11:22 AM
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


> > I'm not saying (and never did say) that postgres could not be fast.
> > All I ever said was that with the same minimal effort applied to both
> > DBs, postgres was slower.
>
> Afaik, your original posting said postgresql was 3 times slower than mysql
> and that you are going to leave this list now. This implied that you have
> made your decision between postgresql and mysql, taking mysql because it
is
> faster.
>
> Now you say your testing setup has minimal effort applied. Well, it is not
> very surprising that mysql is faster in standard configurations. As
Shridhar
> pointed out, postgresql has very conservative default values, so that it
> starts on nearly every machine.
>
> If I was your client and gave you the task to choose a suitable database
for
> my application and you evaluated suitable databases this way, then
something
> is seriously wrong with your work.
>
> Regards,
> Bjoern
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
>


От:
Stephan Szabo
Дата:

On Fri, 4 Jul 2003, Brian Tarbox wrote:

> > I don't think Brian has any interest in being helped.
> >I suspect he'd made up his mind already.
>
>
> With all due respect Tom, I don't think I'm the one demonstrating a closed
> mind.
> Rather than trying to figure out whats going on in my head, how about
> figuring out whats going on in my database?  :-)

Well, in the case of getting a sequential scan on something like
select * from foo where col=10;
where col is a primary key, the things I can think of to check
are does select * from foo where col='10'; give a different plan?

In general for cases where you can't post queries or schema we're kinda
stuck and not really able to give intelligent advice since it's often
schema/query specific, so the general questions/comments are things like
(which you've probably heard, but I think they should get put into this
thread if only to move the thread towards usefulness)

What is the relative costs/plan if you set enable_seqscan to false before
explain analyzing the query?  If there are places you think that it should
be able to do an index scan and it still doesn't, make sure that there
aren't cross datatype issues (especially with int constants).

Also, using explain analyze, where is the time being taken, it's often not
where the cost factor would expect it.

Do the row estimates match reality in the explain analyze output, if not
does analyzing help, if not does raising the statistics target (to say 50,
100, 1000) with alter table and then analyzing help?

Does vacuuming help, what about vacuum full?  If the latter does and the
former doesn't, you may need to look at raising the fsm settings.

If shared_buffers is less than 1000, does setting it to something between
1000-8000 raise performance?

How much memory does the machine have that's being used for caching, if
it's alot, try raising effective_cache_size to see if that helps the
choice of plan by making a more reasonable guess as to cache hit rates.

Are there any sorts in the query, if so, how large would expect the result
set that's being sorted to be, can you afford to make sort_mem cover that
(either permanently by changing conf files or before the query with a set
command)?

Is it possible to avoid some sorts in the plan with a multi-column index?

For 7.3 and earlier, does the query use IN or =ANY, if so it might help to
try to convert to an exists form.

Does the query use any mix/max aggregates, it might help to look for a
workaround, this is one case that is truly slow.



PostgreSQL really does require more than minimal optimization at start,
effective_cache_size, shared_buffers, sort_mem and the fsm settings really
need to be set at a level for the machine/queries you have.  Without the
queries we can't be too specific. Big speed losses I can think of are the
datatype mismatch confusion, followed quickly by row estimates that don't
match reality (generally requiring a greater statistics target on the
column) and issues with correlation (I'm not really sure there's a good
solution for this currently, maybe someone will know -- I've not run into
it really on anything I've looked at).


От:
Andrew Sullivan
Дата:

On Fri, Jul 04, 2003 at 12:10:46PM -0400, Brian Tarbox wrote:
> I am not allowed to share schemas...sorry but thats what the contract says.
> The queries represent code, thus intellectual property, thus I can't post
> them.

If you ask for help, but say, "I can't tell you anything," no-one
will be able to help you.

I think what people are reacting to angrily is that you complain that
PostgreSQL is slow, it appears you haven't tuned it correctly, and
you're not willing to share with anyone what you did.  In that case,
you shouldn't be reporting, "MySQL was faster that PostgreSQL for
me." You should at most be reporting, "MySQL was faster than
PostgreSQL for me, but I haven't any idea how to tune PostgreSQL, and
didn't know how to learn to do so."  That, at least, gives people a
fighting chance to evaluate the utility of your report.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
Josh Berkus
Дата:

Brian,

Howdy!  I'm Josh Berkus, I'm also on the Core Team for PostgreSQL, and I
wanted to give some closure on your issue before you quit with a bad taste in
your mouth.

Your posting hit a sore point in the collective PostgreSQL community, so you
got a strong reaction from several people on the list -- probably out of
proportion to your posting.

Or, to put it another way, you posted something intended to offend people out
of your frustration, and got a very offended reaction back.

> Rather than trying to figure out whats going on in my head, how about
> figuring out whats going on in my database?  :-)
> I am not allowed to share schemas...sorry but thats what the contract says.
> The queries represent code, thus intellectual property, thus I can't post
> them.

I think you recognize, now, that this list cannot help you under those
circumstances?

A significant portion of my income derives from clients who need tuning help
under NDA.  If, however, you don't need any capabilites that PostgreSQL has
which MySQL doesn't, hiring a consultant would not be money well spent.

> I really wasn't looking for battle this fine day....I'm going outside to
> BBQ!  (and if you conclude from that that I'm not interested in this or
> that, there's nothing I can do about that.  It is a beautiful day out and
> bbq does sound more fun than this list.  sorry)

No arguments there ... wish I didn't have to work :-(

--
Josh Berkus
Aglio Database Solutions
San Francisco

От:
"Arjen van der Meijden"
Дата:

> Andrew Sullivan wrote:

> I cannot, for the life of me, understand how anyone can
> install some software which is supposed to provide meaningful
> results under production conditions, and not bother to read
> even the basic "quickstart"-type stuff that is kicking
> around.
Then please point out where it sais, in the documentation, that the
value for the shared_memory of 64 is too low and that 4000 is a nice
value to start with?

Please, also point out the part of the documentation that explains how
high the fsm-settings should be, what the impact of a lower or higher
sort_mem-setting is, what kind of value the effective_cache_size should
have and the best way to determine that.

If you can find the above in the default-documentation, like the
"getting started"-documents or the administration documentation, than be
so kind to give direct links or quotes to that. I was unable to find
that, now in a 15 minute search in the docs themselves and I have read
most part of them (in the past)...

Especially in chapter 10 "Performance hints" I was surprised not to see
such information, although it could be considered an administration
task, but there it wasn't in chapter 10 (monitoring database usage)
either.


I'm sorry to put this in a such a confronting manner, but you simply
can't expect people to search for information that they don't know the
existence of... Actually, that doesn't appear to exist, at least not on
the places you'd expect that information to be placed. I, myself, have
read Bruce's document on performance tuning, but even that document
doesn't provide the detail of information that can be read in this
mailing-list.

Having said that, this list only has 461 subscribers and I can hardly
believe that that are _all_ users of postgresql, as long as it's not the
default way of trying to gather data, it shouldn't be expected that
anyone actually tries to find his information in this list.

Anyway, I saw that there has been done some effort to create a document
that does describe such parameters, I'd be happy to see and read that :)

> There is _no secret_ that Postgres is configured as
> a toy out of the box.  One presumes that DBAs are hired to do
> _some_ little bit of work.
I don't see it on the frontpage, nor in the documentation. Anyway, see
above :)

Regards,

Arjen

Btw, I've tried to tune my postgresql database using the administration
and tech documents, and saw quite a few queries run quite a lot faster
on mysql, I'll try to set up a more useful test environment and supply
this list with information to allow me to tune it to run more or less
equal to mysql. I do see 3x runs, even with the shared memory and sort
mem settings cranked up and having done little to none tuning on mysql
:)




От:
"Arjen van der Meijden"
Дата:

Why is such a simple list of questions not somewhere in the
documentation? :(

Of course a few of your questions are relatively case-dependent, but the
others are very general. Such information should be in the documentation
and easy to access :)

Regards,

Arjen

> Stephan Szabo wrote a nice list of helpful questions




От:
Andrew Sullivan
Дата:

On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > Andrew Sullivan wrote:
> > results under production conditions, and not bother to read
> > even the basic "quickstart"-type stuff that is kicking
> > around.
> Then please point out where it sais, in the documentation, that the
> value for the shared_memory of 64 is too low and that 4000 is a nice
> value to start with?

I think I did indeed speak too soon, as the criticism is a fair one:
nowhere in the installation instructions or the "getting started"
docs does it say that you really ought to do some tuning once you
have the system installed.  Can I suggest for the time being that
something along these lines should go in 14.6.3, "Tuning the
installation":

---snip---
By default, PostgreSQL is configured to run on minimal hardware.  As
a result, some tuning of your installation will be necessary before
using it for anything other than extremely small databases.  At the
very least, it will probably be necessary to increase your shared
buffers setting.  See Chapter 16 for details on what tuning options
are available to you.
---snip---

> I'm sorry to put this in a such a confronting manner, but you simply
> can't expect people to search for information that they don't know the
> existence of.

No need to apologise; I think you're right.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
Josh Berkus
Дата:

People:

> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed.  Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
>
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware.  As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases.  At the
> very least, it will probably be necessary to increase your shared
> buffers setting.  See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---

I think we actually need much more than this.  Kaarel on the Advocacy list has
volunteered to try to extend our "getting started" section to encompass some
basic tuning stuff.  Of course, more people would be better.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


От:
Tom Lane
Дата:

Josh Berkus <> writes:
>> ---snip---
>> By default, PostgreSQL is configured to run on minimal hardware.  As
>> a result, some tuning of your installation will be necessary before
>> using it for anything other than extremely small databases.  At the
>> very least, it will probably be necessary to increase your shared
>> buffers setting.  See Chapter 16 for details on what tuning options
>> are available to you.
>> ---snip---

> I think we actually need much more than this.

I am about to propose a patch that will cause the default shared_buffers
to be more realistic, say 1000, on machines where the kernel will allow
it.  Not sure if people will let me get away with applying it
post-feature-freeze, but if so that would change the terms of this
debate noticeably.

            regards, tom lane

От:
Josh Berkus
Дата:

Tom,

> I am about to propose a patch that will cause the default shared_buffers
> to be more realistic, say 1000, on machines where the kernel will allow
> it.  Not sure if people will let me get away with applying it
> post-feature-freeze, but if so that would change the terms of this
> debate noticeably.

+1

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology     
    and data management solutions     (415) 565-7293
   for law firms, small businesses      fax 621-2533
    and non-profit organizations.     San Francisco


От:
Rod Taylor
Дата:

> I am about to propose a patch that will cause the default shared_buffers
> to be more realistic, say 1000, on machines where the kernel will allow
> it.  Not sure if people will let me get away with applying it
> post-feature-freeze, but if so that would change the terms of this
> debate noticeably.

It's not a feature change, it's a bug fix -- bug being an oversight.

От:
"Brian Tarbox"
Дата:

>Afaik, your original posting said postgresql was 3 times slower than mysql
>and that you are going to leave this list now. This implied that you have
>made your decision between postgresql and mysql, taking mysql because it is
>faster.

Well, that shows what you get for making implications.  The client is
sticking with postgres and we are coding around the issue in other ways.


>If I was your client and gave you the task to choose a suitable database
for
>my application and you evaluated suitable databases this way, then
something
>is seriously wrong with your work.
>
>Regards,
>Bjoern

Glad to see you're not getting personal with this. Ad hominin attacks are
for folks with no better answers.

Please go read the posts by Kevin Schroeder and Arjen va der Meijden before
slinging any more 'help'.

over and out.






От:
"Bjoern Metzdorf"
Дата:

>> Afaik, your original posting said postgresql was 3 times slower than
>> mysql and that you are going to leave this list now. This implied
>> that you have made your decision between postgresql and mysql,
>> taking mysql because it is faster.
>
> Well, that shows what you get for making implications.  The client is
> sticking with postgres and we are coding around the issue in other
> ways.

As many other guys here pointed out, there are numerous ways to tune
postgresql for maximum performance. If you are willing to share more
information about your particular project, we might be able to help you out
and optimize your application, without the need to code around the issue as
much as you may be doing right now.
Even if it is not possible for you to share enough information, there are a
lot of places where you can read about performance tuning (if not in the
docs then in the archives).

>> If I was your client and gave you the task to choose a suitable
>> database for my application and you evaluated suitable databases
>> this way, then something is seriously wrong with your work.
>>
> Glad to see you're not getting personal with this. Ad hominin attacks
> are for folks with no better answers.

Yep, you're right. Sorry for that, I didn't mean to get personal. I was
somehow irritated that you come here, post your database comparison and want
to leave right afterwards, without going into detail (what should be the
case normally).

Again our offer: Post (possibly obfuscated) schema information, and we will
certainly be able to help you with performance tuning.

Regards,
Bjoern


От:
Grega Bremec
Дата:

...and on Sat, Jul 05, 2003 at 12:24:18AM +0200, Bjoern Metzdorf used the keyboard:
> >> Afaik, your original posting said postgresql was 3 times slower than
> >> mysql and that you are going to leave this list now. This implied
> >> that you have made your decision between postgresql and mysql,
> >> taking mysql because it is faster.
> >
> > Well, that shows what you get for making implications.  The client is
> > sticking with postgres and we are coding around the issue in other
> > ways.
>
> As many other guys here pointed out, there are numerous ways to tune
> postgresql for maximum performance. If you are willing to share more
> information about your particular project, we might be able to help you out
> and optimize your application, without the need to code around the issue as
> much as you may be doing right now.
> Even if it is not possible for you to share enough information, there are a
> lot of places where you can read about performance tuning (if not in the
> docs then in the archives).
>

Also, I should think the clients would not be too offended if Brian posted
some hint about the actual quantity of data involved here, both the total
expected database size and some info about the estimated "working set" size,
such as a sum of sizes of tables most commonly used in JOIN queries and the
percentage of data being shuffled around in those. Are indexes big? Are
there any multicolumn indexes in use? Lots of sorting expected? Lots of
UPDATEs/INSERTs/DELETEs?

Also, it would be helpful to know just how normalized the database is, to
provide some advice about possible query optimization, which could again
prove helpful in speeding the machinery up.

Another useful piece of information would be the amount of memory consumed
by other applications vs. the amount of memory reserved by the OS for cache,
and the nature of those other applications running - are they big cache
consumers, such as Apache with static content and a large load would be,
or do they keep a low profile?

I think this would, in combination with the information already posted, such
as the amount of memory and I/O subsystem info, at least enable us to advise
about the recommended shared_buffers, effective_cache_size, sort_mem,
vacuum_mem, and others, without compromising the intellectual property of
Brian's clients.

> > over and out.

I CC'd this post over to you, Brian, 'cause this signoff made me rather
unsure as to whether or not you're still on the list. Hope you don't mind.

Sincerely,
--
    Grega Bremec
    System Administration & Development Support
    grega.bremec-at-noviforum.si
    http://najdi.si/
    http://www.noviforum.si/

От:
Hilmar Lapp
Дата:

On Friday, July 4, 2003, at 07:07  AM, Brian Tarbox wrote:

> We had about 40 tables in the db, with joined queries on about 8-12
> tables.
>

A while ago a tested a moderately complex schema on MySQL, Pg, and
Oracle. I usually heavily normalize schemas and then define views as a
denormalized API, which sends MySQL to the book of toys already. The
views more often than not would join anywhere from 6-12 tables, using
plain (as opposed to compound) foreign keys to primary key straight
joins.

I noticed that Pg was more than an order of magnitude slower for joins
 > 8 tables than Oracle. I won't claim that none of this can have been
due to lack of tuning. My point is the following though. After I dug in
it turned out that of the 4 secs Pg needed to execute the query it
spent 3.9 secs in the planner. The execution plan Pg came up with was
pretty good - it just needed an extraordinary amount of time to arrive
at it, spoiling its own results.

Asking this list I then learned how to tweak GEQO such that it would
pick up the planning and do it faster than it would otherwise. I was
able to get the planner time down to a quarter - still a multitude of
the actual execution time.

I was told on this list that query planning suffers from combinatorial
explosion very quickly - and I completely buy that. It's just - Oracle
planned the same query in a fraction of a second, using the cost-based
optimizer, on a slower machine. I've seen it plan 15-table joins in
much less than a second, and I have no idea how it would do that. In
addition, once you've prepared a query in Oracle, the execution plan is
pre-compiled.

If I were a CS student I'd offer myself to the hall of humiliation and
set out to write a fast query planner for Pg ...

    -hilmar
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------


От:
Hannu Krosing
Дата:

Brian Tarbox kirjutas R, 04.07.2003 kell 15:27:
> 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.

For each and every query ??

> We were on this  list a fair bit looking for answers and tried all the
> standard answers.

Could you post the list of "standard answers" you tried ?

>  It was still much  much much slower.

Was this with InnoDB ?

what kind of joins were they (i.e
"FROM a JOIN b on a.i=b.i"
or "FROM a,b WHERE a.i = b.i" ?

What was the ratio of planning time to actual execution time in pgsql?

Where the queries originally optimized for MySQL ?

----------------
Hannu

От:
"scott.marlowe"
Дата:

On Mon, 7 Jul 2003, Brian Tarbox wrote:

> Oddly enough, the particular application in question will have an extremely
> small user base...perhaps a few simultainous users at most.
>
> As to the testing, I neglected to say early in this thread that my manager
> instructed me _not_ to do further performance testing...so as a good
> consultant I complied.  I'm not going to touch if that was a smart
> instruction to give :-)

But remember, you can always rename your performance testing as
compliance testing and then it's ok, as long as you don't keep any
detailed records about the time it took to run the "compliance testing"
queries.

Definitely look at the output from explain analyze select ... to see what
the planner THINKS the query is gonna cost versus what it really costs.
If you see a huge difference between, say estimated rows and actual rows,
or some other value, it points to the analyzer not getting the right data
for the planner.  You can adjust the percentage of a table sampled with
alter table to force more data into analyze.


От:
"scott.marlowe"
Дата:

On Fri, 4 Jul 2003, Brian Tarbox 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.

Hi Brian, I just wanted to add that if you aren't testing your setup for
multiple users, you are doing yourself a disservice.  The performance of
your app with one user is somewhat interesting, the performance of the
system with a dozen or a hundred users is of paramount importance.

A server that dies under heavy parallel load is useless, no matter how
fast it ran when tested for one user.  Conversely, one would prefer a
server that was a little slow for single users but can hold up under load.

When I first built my test box a few years ago, I tested postgresql /
apache / php at 100 or more parallel users.  That's where things start
getting ugly, and you've got to test for it now, before you commit to a
platform.

Postgresql is designed to work on anything out of the box, which means
it's not optimized for high performance, but for running on old Sparc 2s
with 128 meg of ram.  If you're going to test it against MySQL, be fair to
yourself and performance tune them both before testing, they're
performance on vanilla linux with vanilla configuration tuning teachs you
little about how they'll behave in production on heavy iron.

Good luck on your testing, and please, don't quit testing at the first
sign one or the other is faster, be throrough and complete, including
heavy parallel load testing with reads AND writes.  Know the point at
which each system begins to fail / become unresponsive, and how they
behave in overload.



От:
"Brian Tarbox"
Дата:

Oddly enough, the particular application in question will have an extremely
small user base...perhaps a few simultainous users at most.

As to the testing, I neglected to say early in this thread that my manager
instructed me _not_ to do further performance testing...so as a good
consultant I complied.  I'm not going to touch if that was a smart
instruction to give :-)

Brian


-----Original Message-----
From: scott.marlowe [mailto:]
Sent: Monday, July 07, 2003 1:35 PM
To: Brian Tarbox
Cc: ; Rafal Kedziorski;

Subject: Re: [PERFORM] PostgreSQL vs. MySQL


On Fri, 4 Jul 2003, Brian Tarbox 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.

Hi Brian, I just wanted to add that if you aren't testing your setup for
multiple users, you are doing yourself a disservice.  The performance of
your app with one user is somewhat interesting, the performance of the
system with a dozen or a hundred users is of paramount importance.

A server that dies under heavy parallel load is useless, no matter how
fast it ran when tested for one user.  Conversely, one would prefer a
server that was a little slow for single users but can hold up under load.

When I first built my test box a few years ago, I tested postgresql /
apache / php at 100 or more parallel users.  That's where things start
getting ugly, and you've got to test for it now, before you commit to a
platform.

Postgresql is designed to work on anything out of the box, which means
it's not optimized for high performance, but for running on old Sparc 2s
with 128 meg of ram.  If you're going to test it against MySQL, be fair to
yourself and performance tune them both before testing, they're
performance on vanilla linux with vanilla configuration tuning teachs you
little about how they'll behave in production on heavy iron.

Good luck on your testing, and please, don't quit testing at the first
sign one or the other is faster, be throrough and complete, including
heavy parallel load testing with reads AND writes.  Know the point at
which each system begins to fail / become unresponsive, and how they
behave in overload.


От:
Shridhar Daithankar
Дата:

On Sunday 13 July 2003 10:23, Ron Johnson wrote:
> On Fri, 2003-07-04 at 09:49, Shridhar Daithankar wrote:
> > On 4 Jul 2003 at 16:35, Michael Mattox wrote:
>
> [snip]
>
> > On a positive note, me and Josh are finishing a bare bone performance
> > article that would answer lot of your questions. I am counting on you to
> > provide valuable feedback. I expect it out tomorrow or on sunday..Josh
> > will confirm that..
>
> Hello,
>
> Is this doc publicly available yet?

Yes. See http://www.varlena.com/GeneralBits/

I thought I announved it on performance.. anyways..

 Shridhar


От:
Bruce Momjian
Дата:

I think the issue with multiple users is that a car is good for moving a
few people, but it can't move lots of large boxes. A truck can move
large boxes, but it can't move a few people efficiently.  PostgreSQL is
more like a truck, while MySQL is more like a car.

As an aside, I think Solaris is slower than other OS's because it is
built to scale efficiently to many CPU's, and that takes a performance
hit in a machine with just a few CPU's, though they are working on
tuning those cases.

Of course, this is all just a generalization.

---------------------------------------------------------------------------

scott.marlowe wrote:
> On Fri, 4 Jul 2003, Brian Tarbox 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.
>
> Hi Brian, I just wanted to add that if you aren't testing your setup for
> multiple users, you are doing yourself a disservice.  The performance of
> your app with one user is somewhat interesting, the performance of the
> system with a dozen or a hundred users is of paramount importance.
>
> A server that dies under heavy parallel load is useless, no matter how
> fast it ran when tested for one user.  Conversely, one would prefer a
> server that was a little slow for single users but can hold up under load.
>
> When I first built my test box a few years ago, I tested postgresql /
> apache / php at 100 or more parallel users.  That's where things start
> getting ugly, and you've got to test for it now, before you commit to a
> platform.
>
> Postgresql is designed to work on anything out of the box, which means
> it's not optimized for high performance, but for running on old Sparc 2s
> with 128 meg of ram.  If you're going to test it against MySQL, be fair to
> yourself and performance tune them both before testing, they're
> performance on vanilla linux with vanilla configuration tuning teachs you
> little about how they'll behave in production on heavy iron.
>
> Good luck on your testing, and please, don't quit testing at the first
> sign one or the other is faster, be throrough and complete, including
> heavy parallel load testing with reads AND writes.  Know the point at
> which each system begins to fail / become unresponsive, and how they
> behave in overload.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Bruce Momjian
Дата:

Brian Tarbox wrote:
> Oddly enough, the particular application in question will have an extremely
> small user base...perhaps a few simultainous users at most.
>
> As to the testing, I neglected to say early in this thread that my manager
> instructed me _not_ to do further performance testing...so as a good
> consultant I complied.  I'm not going to touch if that was a smart
> instruction to give :-)

Performance is probably 'good enough', and you can revisit it later when
you have more time.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Bruce Momjian
Дата:

Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > > Andrew Sullivan wrote:
> > > results under production conditions, and not bother to read
> > > even the basic "quickstart"-type stuff that is kicking
> > > around.
> > Then please point out where it sais, in the documentation, that the
> > value for the shared_memory of 64 is too low and that 4000 is a nice
> > value to start with?
>
> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed.  Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
>
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware.  As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases.  At the
> very least, it will probably be necessary to increase your shared
> buffers setting.  See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---
>
> > I'm sorry to put this in a such a confronting manner, but you simply
> > can't expect people to search for information that they don't know the
> > existence of.
>
> No need to apologise; I think you're right.

Agreed.  Text added to install docs:

   <para>
    By default, <productname>PostgreSQL</> is configured to run on minimal
    hardware.  This allows it to start up with almost any hardware
    configuration. However, the default configuration is not designed for
    optimum performance. To achieve optimum performance, several server
    variables must be adjusted, the two most common being
    <varname>shared_buffers</varname> and <varname> sort_mem</varname>
    mentioned in <![%standalone-include[the documentation]]>
    <![%standalone-ignore[<xref linkend="runtime-config-resource-memory">]]>.
    Other parameters in <![%standalone-include[the documentation]]>
    <![%standalone-ignore[<xref linkend="runtime-config-resource">]]>
    also affect performance.
   </para>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Josh Berkus
Дата:

Bruce,

> Agreed.  Text added to install docs:
>
>    <para>
>     By default, <productname>PostgreSQL</> is configured to run on minimal
>     hardware.  This allows it to start up with almost any hardware
>     configuration. However, the default configuration is not designed for
>     optimum performance. To achieve optimum performance, several server
>     variables must be adjusted, the two most common being
>     <varname>shared_buffers</varname> and <varname> sort_mem</varname>
>     mentioned in <![%standalone-include[the documentation]]>
>     <![%standalone-ignore[<xref
> linkend="runtime-config-resource-memory">]]>. Other parameters in
> <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref
> linkend="runtime-config-resource">]]> also affect performance.
>    </para>

What would you think of adding a condensed version of my and Shridhar's guide
to the install docs?  I think I can offer a 3-paragraph version which would
cover the major points of setting PostgreSQL.conf.

--
Josh Berkus
Aglio Database Solutions
San Francisco

От:
Bruce Momjian
Дата:

Josh Berkus wrote:
> Bruce,
>
> > Agreed.  Text added to install docs:
> >
> >    <para>
> >     By default, <productname>PostgreSQL</> is configured to run on minimal
> >     hardware.  This allows it to start up with almost any hardware
> >     configuration. However, the default configuration is not designed for
> >     optimum performance. To achieve optimum performance, several server
> >     variables must be adjusted, the two most common being
> >     <varname>shared_buffers</varname> and <varname> sort_mem</varname>
> >     mentioned in <![%standalone-include[the documentation]]>
> >     <![%standalone-ignore[<xref
> > linkend="runtime-config-resource-memory">]]>. Other parameters in
> > <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref
> > linkend="runtime-config-resource">]]> also affect performance.
> >    </para>
>
> What would you think of adding a condensed version of my and Shridhar's guide
> to the install docs?  I think I can offer a 3-paragraph version which would
> cover the major points of setting PostgreSQL.conf.

Yes, I think that is a good idea --- now, does it go in the install
docs, or in the docs next to each GUC item?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Josh Berkus
Дата:

Bruce,

> Yes, I think that is a good idea --- now, does it go in the install
> docs, or in the docs next to each GUC item?

Hmmm ... both, I think.   The Install Docs should have:

"Here are the top # things you will want to adjust in your PostgreSQL.conf:
1) Shared_buffers <link>
2) Sort_mem <link>
3) effective_cache_size <link>
4) random_page_cost <link>
5) Fsync <link>
etc."

Then next to each of these items in the Docs, I add 1-2 sentences about how to
set that item.

Hmmm ... do we have similar instructions for setting connection options and
pg_hba.conf?  We should have a P telling people they need to do this.

Barring an objection, I'll get to work on this.

--
Josh Berkus
Aglio Database Solutions
San Francisco

От:
Bruce Momjian
Дата:

Totally agree.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Bruce,
>
> > Yes, I think that is a good idea --- now, does it go in the install
> > docs, or in the docs next to each GUC item?
>
> Hmmm ... both, I think.   The Install Docs should have:
>
> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
> 1) Shared_buffers <link>
> 2) Sort_mem <link>
> 3) effective_cache_size <link>
> 4) random_page_cost <link>
> 5) Fsync <link>
> etc."
>
> Then next to each of these items in the Docs, I add 1-2 sentences about how to
> set that item.
>
> Hmmm ... do we have similar instructions for setting connection options and
> pg_hba.conf?  We should have a P telling people they need to do this.
>
> Barring an objection, I'll get to work on this.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Neil Conway
Дата:

On Wed, 2003-10-08 at 14:05, Josh Berkus wrote:
> Hmmm ... both, I think.   The Install Docs should have:
>
> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
> 1) Shared_buffers <link>
> 2) Sort_mem <link>
> 3) effective_cache_size <link>
> 4) random_page_cost <link>
> 5) Fsync <link>
> etc."

> Barring an objection, I'll get to work on this.

I think this kind of information belongs in the documentation proper,
not in the installation instructions. I think you should put this kind
of tuning information in the "Performance Tips" chapter, and include a
pointer to it in the installation instructions.

-Neil



От:
Vivek Khera
Дата:

>>>>> "JB" == Josh Berkus <> writes:

JB> Hmmm ... both, I think.   The Install Docs should have:

JB> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
JB> 1) Shared_buffers <link>
JB> 2) Sort_mem <link>
JB> 3) effective_cache_size <link>
JB> 4) random_page_cost <link>
JB> 5) Fsync <link>
JB> etc."

Add:

max_fsm_relations (perhaps it is ok with current default)
max_fsm_pages

I don't think you really want to diddle with fsync in the name of
speed at the cost of safety.

and possibly:

checkpoint_segments (if you do a lot of writes to the DB for extended
                    durations of time)  With 7.4 it warns you in the
                    logs if you should increase this.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet:        Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

От:
Andrew Sullivan
Дата:

On Wed, Oct 08, 2003 at 01:28:53PM -0400, Bruce Momjian wrote:
>
> Agreed.  Text added to install docs:

[&c.]

I think this is just right.  It tells a user where to find the info
needed, doesn't reproduce it all over the place, and still points out
that this is something you'd better do.  Combined with the new
probe-to-set-shared-buffers bit at install time, I think the reports
of 400 billion times worse performance than MySQL will probably
diminish.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110