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

On Wed, 2005-11-09 at 22:20, Ron Peacetree wrote:
> 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.

Very valid point. It's the reason, in my last job, we had a mainline
server with dual 2800MHz CPUs and a big RAID array.

And our development, build and test system was a Dual Pentium Pro 200
with 256 Meg of ram. You notice slow queries real fast on such a box.

On Thu, 10 Nov 2005 09:16:10 -0600
Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:

> Very valid point. It's the reason, in my last job, we had a mainline
> server with dual 2800MHz CPUs and a big RAID array.
>
> And our development, build and test system was a Dual Pentium Pro 200
> with 256 Meg of ram. You notice slow queries real fast on such a box.

I know several people who use this development method. It can
sometimes lead to premature optimizations, but overall I think it is
a great way to work.

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

On Thu, 2005-11-10 at 09:25, Frank Wiles wrote:
> On Thu, 10 Nov 2005 09:16:10 -0600
> Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> > Very valid point. It's the reason, in my last job, we had a mainline
> > server with dual 2800MHz CPUs and a big RAID array.
> >
> > And our development, build and test system was a Dual Pentium Pro 200
> > with 256 Meg of ram. You notice slow queries real fast on such a box.
>
> I know several people who use this development method. It can
> sometimes lead to premature optimizations, but overall I think it is
> a great way to work.

Hehe. Yeah, you get used to things running a bit slower pretty
quickly. Keep in mind though, that the test box is likely only
supporting one single application at a time, whereas the production
server may be running dozens or even hundreds of apps, so it's important
to catch performance issues before they get to production.

Plus, the Dual PPRo 200 WAS running a decent RAID array, even if it was
a linux kernel software RAID and not hardware. But it was on 8 9
gigabyte SCSI drives, so it was quite fast for reads.

In actuality, a lot of the folks developed their code on their own
workstations (generally 1+GHz machines with 1G or more of ram) then had
to move them over to the ppro 200 for testing and acceptance. So that
kind of helps stop the premature optimizations. We were mainly looking
to catch stupidity before it got to production.