Subject: PostgreSQL vs. MySQL

From: Rafal Kedziorski <[email-masked]>
Date: Fri, 04 Jul 2003 12:03:03 +0200

Hi,

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

Regards,
Rafal

---

From: Richard Huxton <[email-masked]>
Date: Fri, 4 Jul 2003 12:12:25 +0100

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

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: "Brian Tarbox" <[email-masked]>
Date: Fri, 4 Jul 2003 08:27:36 -0400

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

---

From: "Michael Mattox" <[email-masked]>
Date: Fri, 4 Jul 2003 14:36:00 +0200

> 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

---

From: "Brian Tarbox" <[email-masked]>
Date: Fri, 4 Jul 2003 08:43:24 -0400

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:[email-masked]]
Sent: Friday, July 04, 2003 8:36 AM
To: Brian Tarbox; Rafal Kedziorski; [email-masked]
Subject: RE: [PERFORM] PostgreSQL vs. MySQL

Michael

---

From: "Michael Mattox" <[email-masked]>
Date: Fri, 4 Jul 2003 14:46:15 +0200

> 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

---

From: Shridhar Daithankar <[email-masked]>
Date: Fri, 4 Jul 2003 18:21:26 +0530

> hundred users.  If it's a webapp you can use JMeter to do this really

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

---

From: Shridhar Daithankar <[email-masked]>
Date: Fri, 4 Jul 2003 18:23:40 +0530

> 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

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 09:11:10 -0400

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

---

From: "Shridhar Daithankar" <[email-masked]>
Date: Fri, 04 Jul 2003 18:50:19 +0530

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

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 09:45:30 -0400

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

---

From: Christopher Kings-Lynne <[email-masked]>
Date: Fri, 4 Jul 2003 21:50:26 +0800 (WST)

> 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Jul 2003 09:59:13 -0400

> 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

---

From: "Brian Tarbox" <[email-masked]>
Date: Fri, 4 Jul 2003 10:07:46 -0400

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: [email-masked]
[mailto:[email-masked]]On Behalf Of Shridhar
Daithankar
Sent: Friday, July 04, 2003 8:54 AM
To: [email-masked]
Subject: Re: [PERFORM] PostgreSQL vs. MySQL

1. Hardware
2. Postgresql version

 Shridhar

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email-masked]

---

From: "Shridhar Daithankar" <[email-masked]>
Date: Fri, 04 Jul 2003 19:51:30 +0530

> I did no tuning of MySql.  The only tuning for PG was to vacuum and vacuum

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.

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

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Jul 2003 10:23:24 -0400

Rod Taylor <[email-masked]> writes:
>> It was bit too vague to be a comfortable DB tuning problem.

> 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

---

From: Andrew Sullivan <[email-masked]>
Date: Fri, 4 Jul 2003 10:28:00 -0400

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

> 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

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

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 10:34:20 -0400

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

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

---

From: "Michael Mattox" <[email-masked]>
Date: Fri, 4 Jul 2003 16:35:03 +0200

> 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Jul 2003 10:35:54 -0400

"Brian Tarbox" <[email-masked]> 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

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

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 10:49:01 -0400

Moving to -hackers.

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

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.

---

From: "Shridhar Daithankar" <[email-masked]>
Date: Fri, 04 Jul 2003 20:19:16 +0530

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

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.

---

From: Rafal Kedziorski <[email-masked]>
Date: Fri, 04 Jul 2003 16:55:04 +0200

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?

Rafal

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 11:06:03 -0400

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

---

From: Shridhar Daithankar <[email-masked]>
Date: Fri, 4 Jul 2003 20:48:39 +0530

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

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

---

From: Andrew Sullivan <[email-masked]>
Date: Fri, 4 Jul 2003 11:26:20 -0400

> 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

---

From: Shridhar Daithankar <[email-masked]>
Date: Fri, 4 Jul 2003 20:58:12 +0530

> complain, "It just doesn't work," and move on without asking about

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

---

From: "Kevin Schroeder" <[email-masked]>
Date: Fri, 4 Jul 2003 10:42:52 -0500

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" <[email-masked]>
To: <[email-masked]>
Sent: Friday, July 04, 2003 10:28 AM
Subject: Re: [PERFORM] PostgreSQL vs. MySQL

> TIP 4: Don't 'kill -9' the postmaster
>

---

From: "Brian Tarbox" <[email-masked]>
Date: Fri, 4 Jul 2003 12:10:46 -0400

>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

---

From: Achilleus Mantzios <[email-masked]>
Date: Fri, 4 Jul 2003 14:14:02 -0200 (GMT+2)

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)

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

---

From: "Bjoern Metzdorf" <[email-masked]>
Date: Fri, 4 Jul 2003 18:22:43 +0200

> 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

---

From: "Kevin Schroeder" <[email-masked]>
Date: Fri, 4 Jul 2003 11:39:49 -0500

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

---

From: Stephan Szabo <[email-masked]>
Date: Fri, 4 Jul 2003 10:05:56 -0700 (PDT)

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

---

From: Andrew Sullivan <[email-masked]>
Date: Fri, 4 Jul 2003 13:08:32 -0400

> The queries represent code, thus intellectual property, thus I can't post

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

---

From: Josh Berkus <[email-masked]>
Date: Fri, 4 Jul 2003 10:12:36 -0700

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.

> The queries represent code, thus intellectual property, thus I can't post

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.

> 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

---

From: "Arjen van der Meijden" <[email-masked]>
Date: Fri, 4 Jul 2003 20:07:18 +0200

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

---

From: "Arjen van der Meijden" <[email-masked]>
Date: Fri, 4 Jul 2003 20:15:21 +0200

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

---

From: Andrew Sullivan <[email-masked]>
Date: Fri, 4 Jul 2003 14:28:53 -0400

> value for the shared_memory of 64 is too low and that 4000 is a nice

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

> existence of.

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

A

---

From: Josh Berkus <[email-masked]>
Date: Fri, 4 Jul 2003 11:37:58 -0700

> buffers setting.  See Chapter 16 for details on what tuning options

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Jul 2003 15:18:43 -0400

> 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

---

From: Josh Berkus <[email-masked]>
Date: Fri, 4 Jul 2003 12:20:59 -0700

Tom,

> post-feature-freeze, but if so that would change the terms of this

+1

-- 
-Josh Berkus

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

---

From: Rod Taylor <[email-masked]>
Date: 04 Jul 2003 19:29:38 +0000

> post-feature-freeze, but if so that would change the terms of this

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

---

From: johnnnnnn <[email-masked]>
Date: Fri, 4 Jul 2003 15:39:57 -0500

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

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

---

From: "Brian Tarbox" <[email-masked]>
Date: Fri, 4 Jul 2003 17:14:01 -0400

>made your decision between postgresql and mysql, taking mysql because it is

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.

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

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

over and out.

---

From: Tim Conrad <[email-masked]>
Date: Fri, 4 Jul 2003 17:28:32 -0400

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:
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

---

From: "Bjoern Metzdorf" <[email-masked]>
Date: Sat, 5 Jul 2003 00:24:18 +0200

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

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

---

From: Grega Bremec <[email-masked]>
Date: Sat, 5 Jul 2003 18:39:37 +0200

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

---

From: Hilmar Lapp <[email-masked]>
Date: Sat, 5 Jul 2003 16:40:52 -0700

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

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

---

From: Hannu Krosing <[email-masked]>
Date: 06 Jul 2003 03:11:58 +0300

> The results we got was that Postgres was fully 3 times slower than MySql.

For each and every query ??

> 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

---

From: "scott.marlowe" <[email-masked]>
Date: Mon, 7 Jul 2003 11:35:24 -0600 (MDT)

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

---

From: "Brian Tarbox" <[email-masked]>
Date: Mon, 7 Jul 2003 14:16:16 -0400

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:[email-masked]]
Sent: Monday, July 07, 2003 1:35 PM
To: Brian Tarbox
Cc: [email-masked]; Rafal Kedziorski;
[email-masked]
Subject: Re: [PERFORM] PostgreSQL vs. MySQL

---

From: "scott.marlowe" <[email-masked]>
Date: Mon, 7 Jul 2003 13:58:24 -0600 (MDT)

> consultant I complied.  I'm not going to touch if that was a smart

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.

---

From: Shridhar Daithankar <[email-masked]>
Date: Sun, 13 Jul 2003 15:41:08 +0530

On Sunday 13 July 2003 10:23, Ron Johnson wrote:
> On Fri, 2003-07-04 at 09:49, Shridhar Daithankar 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

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 20 Jul 2003 19:23:08 -0400 (EDT)

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:
> TIP 7: don't forget to increase your free space map settings
> 

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

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 20 Jul 2003 19:24:23 -0400 (EDT)

> consultant I complied.  I'm not going to touch if that was a smart

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

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 16 Aug 2003 20:36:57 -0400 (EDT)

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

---

From: Andrew Sullivan <[email-masked]>
Date: Sun, 17 Aug 2003 11:06:19 -0400

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 26 Sep 2003 13:47:22 -0400 (EDT)

> 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
  [email-masked]               |  (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>

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 8 Oct 2003 13:28:53 -0400 (EDT)

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

---

From: Josh Berkus <[email-masked]>
Date: Wed, 8 Oct 2003 10:49:55 -0700

Bruce,

>     <![%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

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 8 Oct 2003 13:58:57 -0400 (EDT)

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

---

From: Josh Berkus <[email-masked]>
Date: Wed, 8 Oct 2003 11:05:47 -0700

Bruce,

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 8 Oct 2003 14:15:58 -0400 (EDT)

Totally agree.

---

From: Neil Conway <[email-masked]>
Date: Wed, 08 Oct 2003 14:28:45 -0400

> 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

---

From: Vivek Khera <[email-masked]>
Date: Wed, 08 Oct 2003 15:58:03 -0400

>>>>> "JB" == Josh Berkus <[email-masked]> 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: [email-masked]       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---

From: Andrew Sullivan <[email-masked]>
Date: Thu, 9 Oct 2003 05:53:38 -0400

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