Re: Some help on buffers and other performance tricks

The point Gentlemen, was that Good Architecture is King. That's what I was trying to emphasize by calling proper DB architecture step 0. All other things being equal (and they usually aren't, this sort of stuff is _very_ context dependent), the more of your critical schema that you can fit into RAM during normal operation the better.

...and it all starts with proper DB design. Otherwise, you are quite right in stating that you risk wasting time, effort, and HW.

Ron

-----Original Message-----
From: Frank Wiles <frank(at)wiles(dot)org>
Sent: Nov 9, 2005 6:53 PM
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: rjpeace(at)earthlink(dot)net, yves(dot)vindevogel(at)implements(dot)be, pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Some help on buffers and other performance tricks

On Wed, 9 Nov 2005 20:07:52 -0300
Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> IMHO you should really be examining your queries _before_ you do any
> investment in hardware, because later those may prove unnecessary.

It all really depends on what you're doing. For some of the systems
I run, 4 GBs of RAM is *WAY* overkill, RAID 1+0 is overkill, etc.

In general I would slightly change the "order of operations" from:

1) Buy tons of RAM
2) Buy lots of disk I/O
3) Tune your conf
4) Examine your queries

to

1) Tune your conf
2) Spend a few minutes examining your queries
3) Buy as much RAM as you can afford
4) Buy as much disk I/O as you can
5) Do in depth tuning of your queries/conf

Personally I avoid planning my schema around my performance at
the start. I just try to represent the data in a sensible,
normalized way. While I'm sure I sub-consciously make decisions
based on performance considerations early on, I don't do any major
schema overhauls until I find I can't get the performance I need
via tuning.

Obviously there are systems/datasets/quantities where this won't
always work out best, but for the majority of systems out there
complicating your schema, maxing your hardware, and THEN tuning
is IMHO the wrong approach.

---------------------------------
Frank Wiles <frank(at)wiles(dot)org>
http://www.wiles.org
---------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings