Re: Hardware/OS recommendations for large databases - Mailing list pgsql-performance

From Ron
Subject Re: Hardware/OS recommendations for large databases
Date
Msg-id 6.2.5.6.0.20051127150022.03880848@earthlink.net
Whole thread Raw
In response to Re: Hardware/OS recommendations for large databases  ("Luke Lonergan" <llonergan@greenplum.com>)
List pgsql-performance
At 02:11 PM 11/27/2005, Luke Lonergan wrote:
>Ron,
>
>On 11/27/05 9:10 AM, "Ron" <rjpeace@earthlink.net> wrote:
>
> > Clever use of RAM can get a 5TB sequential scan down to ~17mins.
> >
> > Yes, it's a lot of data.  But sequential scan times should be in the
> > mins or low single digit hours, not days.  Particularly if you use
> > RAM to maximum advantage.
>
>Unfortunately, RAM doesn't help with scanning from disk at all.
I agree with you if you are scanning a table "cold", having never
loaded it before, or if the system is not (or can't be) set up
properly with appropriate buffers.

However, outside of those 2 cases there are often tricks you can use
with enough RAM (and no, you don't need RAM equal to the size of the
item(s) being scanned) to substantially speed things up.  Best case,
you can get performance approximately equal to that of a RAM resident scan.


>WRT using network interfaces to help - it's interesting, but I think what
>you'd want to connect to is other machines with storage on them.
Maybe.  Or maybe you want to concentrate your storage in a farm that
is connected by network or Fiber Channel to the rest of your
HW.  That's what a NAS or SAN is after all.

"The rest of your HW" nowadays is often a cluster of RAM rich
hosts.  Assuming 64GB per host, 5TB can be split across ~79 hosts if
you want to make it all RAM resident.

Most don't have that kind of budget, but thankfully it is not usually
necessary to make all of the data RAM resident in order to obtain if
not all of the performance benefits you'd get if all of the data was.

Ron



pgsql-performance by date:

Previous
From: "Luke Lonergan"
Date:
Subject: Re: Hardware/OS recommendations for large databases (
Next
From: David Lang
Date:
Subject: Re: Open request for benchmarking input