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.Grittner@wicourts.gov> wrote:
Venkat Balaji <
venkat.balaji@verse.in> wrote:
> We CLUSTERED a table using mostly used Index. Application is
> performing better now.
(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