Re: : Performance Improvement Strategy

Thanks a lot Kevin !

This email has deepened my understanding on the clustering concept.

Keeping this in mind, I have recommended a new disk layout at the OS level
for our production servers so that IOs will be balanced on the disks as
well.

Currently, we do not have mount points divided according to the type of IOs.

I will share my recommended plan in an different email thread.

Thanks again for this detailed explanation.

Regards,
VB

On Mon, Oct 3, 2011 at 9:45 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov>wrote:

> Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> wrote:
>
> > We CLUSTERED a table using mostly used Index. Application is
> > performing better now.
>
> CLUSTER can help in at least four ways:
>
> (1) It eliminates bloat in the table heap.
>
> (2) It eliminates bloat in the indexes.
>
> (3) It can correct fragmentation in the underlying disk files.
>
> (4) It can put tuples which are accessed by the same query into
> adjacent locations on disk, reducing physical disk access.
>
> An aggressive autovacuum configuration can generally prevent the
> first two from coming back to haunt you, and the third may not be a
> big problem (depending on your OS and file system), but that last
> one is a benefit which will degrade over time in most use cases --
> the order in the heap is set by the cluster, but not maintained
> after that. If this ordering is a significant part of the
> performance improvement you're seeing, you may want to schedule some
> regular CLUSTER run. It's hard to say what frequency would make
> sense, but if performance gradually deteriorates and a CLUSTER fixes
> it, you'll get a sense of how often it pays to do it.
>
> -Kevin
>