Hi,

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


Regards,
Rafal 


Fri, 04 Jul 2003 12:03:03 +0200
Rafal Kedziorski <rafcio@polonium.de>
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

Fri, 4 Jul 2003 12:12:25 +0100
Richard Huxton <dev@archonet.com>
Re: 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.

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?

               http://archives.postgresql.org


Fri, 4 Jul 2003 08:27:36 -0400
"Brian Tarbox" <btarbox@theworld.com>
Re: 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



Fri, 4 Jul 2003 14:36:00 +0200
"Michael Mattox" <michael.mattox@verideon.com>
Re: PostgreSQL vs. MySQL
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


Fri, 4 Jul 2003 08:43:24 -0400
"Brian Tarbox" <btarbox@theworld.com>
Re: PostgreSQL vs. MySQL
> 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



Fri, 4 Jul 2003 14:46:15 +0200
"Michael Mattox" <michael.mattox@verideon.com>
Re: PostgreSQL vs. MySQL
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


Fri, 4 Jul 2003 18:21:26 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: 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


Fri, 4 Jul 2003 18:23:40 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
> 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.
04 Jul 2003 09:11:10 -0400
Rod Taylor <rbt@rbt.ca>
Re: PostgreSQL vs. MySQL
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.


Fri, 04 Jul 2003 18:50:19 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
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.
04 Jul 2003 09:45:30 -0400
Rod Taylor <rbt@rbt.ca>
Re: 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 have never found a query in MySQL that was faster than one in
PostgreSQL.

Chris



Fri, 4 Jul 2003 21:50:26 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: PostgreSQL vs. MySQL
"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

Fri, 04 Jul 2003 09:59:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL vs. MySQL 
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


Fri, 4 Jul 2003 10:07:46 -0400
"Brian Tarbox" <btarbox@theworld.com>
Re: PostgreSQL vs. MySQL
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.


Fri, 04 Jul 2003 19:51:30 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
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

Fri, 04 Jul 2003 10:23:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL vs. MySQL 
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 x110


Fri, 4 Jul 2003 10:28:00 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: PostgreSQL vs. MySQL
> 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.
04 Jul 2003 10:34:20 -0400
Rod Taylor <rbt@rbt.ca>
Re: PostgreSQL vs. MySQL
> 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



Fri, 4 Jul 2003 16:35:03 +0200
"Michael Mattox" <michael.mattox@verideon.com>
Re: PostgreSQL vs. MySQL
"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

Fri, 04 Jul 2003 10:35:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL vs. MySQL 
Moving to -hackers.

> In my opinion the defaults should be set up for a typical database server
> machine.

Ok.. thats fair.  The first problem would be to define typical for
current PostgreSQL installations, and typical for non-postgresql
installations (the folks we want to convert).

After that, do we care if it works with the typical OS installation or
with all default OS installations (think shared memory settings)?


I agree, this is something we should tackle somewhere along the 7.5
timeline.
04 Jul 2003 10:49:01 -0400
Rod Taylor <rbt@rbt.ca>
Re: PostgreSQL vs. MySQL
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.


Fri, 04 Jul 2003 20:19:16 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
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


Fri, 04 Jul 2003 16:55:04 +0200
Rafal Kedziorski <rafcio@polonium.de>
Re: PostgreSQL vs. MySQL
> 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.
04 Jul 2003 11:06:03 -0400
Rod Taylor <rbt@rbt.ca>
Re: PostgreSQL vs. MySQL
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


Fri, 4 Jul 2003 20:48:39 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Fri, 4 Jul 2003 11:26:20 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: 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


Fri, 4 Jul 2003 20:58:12 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: PostgreSQL vs. MySQL
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" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
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
>


Fri, 4 Jul 2003 10:42:52 -0500
"Kevin Schroeder" <kschroeder@mirageworks.com>
Re: PostgreSQL vs. MySQL
> 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


Fri, 4 Jul 2003 12:10:46 -0400
"Brian Tarbox" <btarbox@theworld.com>
Re: PostgreSQL vs. MySQL 

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


Fri, 4 Jul 2003 14:14:02 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com>
Re: 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



Fri, 4 Jul 2003 18:22:43 +0200
"Bjoern Metzdorf" <bm@turtle-entertainment.de>
Re: PostgreSQL vs. MySQL 
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" <bm@turtle-entertainment.de>
To: "Postgresql Performance" <pgsql-performance@postgresql.org>
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 majordomo@postgresql.org
>


Fri, 4 Jul 2003 11:39:49 -0500
"Kevin Schroeder" <kschroeder@mirageworks.com>
Re: PostgreSQL vs. MySQL 

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


Fri, 4 Jul 2003 10:05:56 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: PostgreSQL vs. MySQL 
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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Fri, 4 Jul 2003 13:08:32 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: PostgreSQL vs. MySQL
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

Fri, 4 Jul 2003 10:12:36 -0700
Josh Berkus <josh@agliodbs.com>
Re: PostgreSQL vs. MySQL
> 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
:)




Fri, 4 Jul 2003 20:07:18 +0200
"Arjen van der Meijden" <acm@tweakers.net>
Re: PostgreSQL vs. MySQL
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




Fri, 4 Jul 2003 20:15:21 +0200
"Arjen van der Meijden" <acm@tweakers.net>
Re: PostgreSQL vs. MySQL 
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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Fri, 4 Jul 2003 14:28:53 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: PostgreSQL vs. MySQL
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


Fri, 4 Jul 2003 11:37:58 -0700
Josh Berkus <josh@agliodbs.com>
Re: PostgreSQL vs. MySQL
Josh Berkus <josh@agliodbs.com> 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

Fri, 04 Jul 2003 15:18:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL vs. MySQL 
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 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco


Fri, 4 Jul 2003 12:20:59 -0700
Josh Berkus <josh@agliodbs.com>
Re: PostgreSQL vs. MySQL
> 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.
04 Jul 2003 19:29:38 +0000
Rod Taylor <rbt@rbt.ca>
Re: PostgreSQL vs. MySQL
On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote:
> > In my opinion the defaults should be set up for a typical database
> > server machine.
> 
> Ok.. thats fair.  The first problem would be to define typical for
> current PostgreSQL installations, and typical for non-postgresql
> installations (the folks we want to convert).

It's been a while since the last one of these discussions, so stop me
if this has been suggested before, but...

Do we actually want to have a default configuration file?

Seriously, if we provide, say, 4 or 5 files based on various system
assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever),
then we might be able to get away with not providing an actual
default. Change the installation instructions to say

>>>
PostgreSQL requires a configuration file, which it expects to be
located in $DIR. Provided are several example configurations (in
$DIR/eg/). If you're just starting with PostrgreSQL, we recommend
reading through those and selecting one which most closely matches
your machine.

If you're in doubt as to which file to use, try $AVERAGE. If you're
still having difficulty getting PostgreSQL to run, try
$MINIMAL. $MINIMAL should work on every supported platform, but is not
optimized for modern hardware -- PostgreSQL will not run well in this
configuration.
<<<

This makes the installation process slightly less simple, but only in
the way that we want it to be. That is, it forces the end user to the
realization that there actually is configuration to be done, and
forces them into a minimally interactive way to deal with it.

It also doesn't require any kernel-test coding, or really any
development at all, so we should theoretically be able to get it
finished and ready to go more quickly.

Thoughts?

-johnnnnnnnnnnn

Fri, 4 Jul 2003 15:39:57 -0500
johnnnnnn <john@phaedrusdeinus.org>
Re: PostgreSQL vs. MySQL
>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.






Fri, 4 Jul 2003 17:14:01 -0400
"Brian Tarbox" <btarbox@theworld.com>
Re: PostgreSQL vs. MySQL 
Being new to Postgres, I understand how frustrating tuning is. I've been
working on some very basic queries, and trying to get some decent performance.
I know the problem isn't with io on the system, since I can use other tests
that far exceed the amount of data being written through postgres, so I can
only assume that the  rdbms needs to be tuned.

even beyond doing different configuration files (as mysql does, fyi), just
having 'guidelines' for where to start with tuning  the various items in
postresql.conf would be helpful. Something like

foo_val = 100 # 0-1024; higher numbers for more complex queries

I don't think that'd be too difficult. Anyone who's worked with a database
of any type, understands that tuning needs to happen.

Having the three different sizes of servers (as suggested below) would be 
helpful. MySQL does this, and i've used their default configurations in the
past to help me troubleshoot probllems i was having with a complex query on
sub-standard hardware. After changing some of the values around, the query
ran slowly, but it actually ran, whereas before, it didn't. 

Slightly off subject, as I've been advocating use of Postgres, and people 
have been trying it, some of the quirks that one runs into when goinng from 
rdbms to rdbms are frustrating as well. Granted, these things happen with
going from any rdbms to another, but it'd be nice if there were a guide to say
something like, in MySQL, you use 'show tables from tablename', and in 
Postgres, you use \d tablename to achieve the same results.

Just my $.02 worth.

Tim


On Fri, Jul 04, 2003 at 03:39:57PM -0500, johnnnnnn wrote:
> On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote:
> > > In my opinion the defaults should be set up for a typical database
> > > server machine.
> > 
> > Ok.. thats fair.  The first problem would be to define typical for
> > current PostgreSQL installations, and typical for non-postgresql
> > installations (the folks we want to convert).
> 
> It's been a while since the last one of these discussions, so stop me
> if this has been suggested before, but...
> 
> Do we actually want to have a default configuration file?
> 
> Seriously, if we provide, say, 4 or 5 files based on various system
> assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever),
> then we might be able to get away with not providing an actual
> default. Change the installation instructions to say
> 
> >>>
> PostgreSQL requires a configuration file, which it expects to be
> located in $DIR. Provided are several example configurations (in
> $DIR/eg/). If you're just starting with PostrgreSQL, we recommend
> reading through those and selecting one which most closely matches
> your machine.
> 
> If you're in doubt as to which file to use, try $AVERAGE. If you're
> still having difficulty getting PostgreSQL to run, try
> $MINIMAL. $MINIMAL should work on every supported platform, but is not
> optimized for modern hardware -- PostgreSQL will not run well in this
> configuration.
> <<<
> 
> This makes the installation process slightly less simple, but only in
> the way that we want it to be. That is, it forces the end user to the
> realization that there actually is configuration to be done, and
> forces them into a minimally interactive way to deal with it.
> 
> It also doesn't require any kernel-test coding, or really any
> development at all, so we should theoretically be able to get it
> finished and ready to go more quickly.
> 
> Thoughts?
> 
> -johnnnnnnnnnnn
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Fri, 4 Jul 2003 17:28:32 -0400
Tim Conrad <conrad@external.timconrad.org>
Re: PostgreSQL vs. MySQL
>> 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


Sat, 5 Jul 2003 00:24:18 +0200
"Bjoern Metzdorf" <bm@turtle-entertainment.de>
Re: PostgreSQL vs. MySQL 
...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/

Sat, 5 Jul 2003 18:39:37 +0200
Grega Bremec <gregab@noviforum.si>
Re: PostgreSQL vs. MySQL

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


Sat, 5 Jul 2003 16:40:52 -0700
Hilmar Lapp <hlapp@gmx.net>
Re: PostgreSQL vs. MySQL
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

06 Jul 2003 03:11:58 +0300
Hannu Krosing <hannu@tm.ee>
Re: 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.



Mon, 7 Jul 2003 11:35:24 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: PostgreSQL vs. MySQL
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:scott.marlowe@ihs.com]
Sent: Monday, July 07, 2003 1:35 PM
To: Brian Tarbox
Cc: michael.mattox@verideon.com; Rafal Kedziorski;
pgsql-performance@postgresql.org
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.


Mon, 7 Jul 2003 14:16:16 -0400
"Brian Tarbox" <btarbox@world.std.com>
Re: PostgreSQL vs. MySQL
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.


Mon, 7 Jul 2003 13:58:24 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: PostgreSQL vs. MySQL
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


Sun, 13 Jul 2003 15:41:08 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: Tuning PostgreSQL

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
  pgman@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

Sun, 20 Jul 2003 19:23:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL vs. MySQL
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
  pgman@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

Sun, 20 Jul 2003 19:24:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL vs. MySQL

Do we need to add a mention of the need for tuning to the install docs?

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

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.
> 
> A
> 
> -- 
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Sat, 16 Aug 2003 20:36:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PERFORM] PostgreSQL vs. MySQL
On Sat, Aug 16, 2003 at 08:36:57PM -0400, Bruce Momjian wrote:
> 
> Do we need to add a mention of the need for tuning to the install docs?

Wouldn't be a bad idea, as far as I'm concerned.

A

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


Sun, 17 Aug 2003 11:06:19 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: [PERFORM] PostgreSQL vs. MySQL
Andrew Sullivan wrote:
> On Sat, Aug 16, 2003 at 08:36:57PM -0400, Bruce Momjian wrote:
> > 
> > Do we need to add a mention of the need for tuning to the install docs?
> 
> Wouldn't be a bad idea, as far as I'm concerned.

OK, I added a 'Tuning' section to the install instructions.  I can make
adjustments.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Index: doc/src/sgml/installation.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/installation.sgml,v
retrieving revision 1.143
diff -c -c -r1.143 installation.sgml
*** doc/src/sgml/installation.sgml	13 Sep 2003 17:01:09 -0000	1.143
--- doc/src/sgml/installation.sgml	26 Sep 2003 17:40:53 -0000
***************
*** 1156,1161 ****
--- 1156,1181 ----
    <title>Post-Installation Setup</title>
  
    <sect2>
+    <title>Tuning</title>
+ 
+    <indexterm>
+     <primary>tuning</primary>
+    </indexterm>
+ 
+    <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 <xref linkend="runtime-config-resource-memory">. Other
+     paramters in <xref linkend="runtime-config-resource"> also affect 
+     performance.
+    </para>
+   </sect2>
+ 
+   <sect2>
     <title>Shared Libraries</title>
  
     <indexterm>
Fri, 26 Sep 2003 13:47:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PERFORM] PostgreSQL vs. MySQL
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
  pgman@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

Wed, 8 Oct 2003 13:28:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL vs. MySQL
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

Wed, 8 Oct 2003 10:49:55 -0700
Josh Berkus <josh@agliodbs.com>
Re: PostgreSQL vs. MySQL
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
  pgman@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

Wed, 8 Oct 2003 13:58:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL vs. MySQL
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

Wed, 8 Oct 2003 11:05:47 -0700
Josh Berkus <josh@agliodbs.com>
Re: PostgreSQL vs. MySQL

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
  pgman@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

Wed, 8 Oct 2003 14:15:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL vs. MySQL
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



Wed, 08 Oct 2003 14:28:45 -0400
Neil Conway <neilc@samurai.com>
Re: PostgreSQL vs. MySQL
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> 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: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Wed, 08 Oct 2003 15:58:03 -0400
Vivek Khera <khera@kcilink.com>
Re: PostgreSQL vs. MySQL
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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Thu, 9 Oct 2003 05:53:38 -0400
Andrew Sullivan <andrew@libertyrms.info>
Re: PostgreSQL vs. MySQL