Re: Poor Performance on a table

From: Pallav Kalva
Subject: Re: Poor Performance on a table
Date: ,
Msg-id: 41AF72EB.1070900@deg.cc
(view: Whole thread, Raw)
In response to: Re: Poor Performance on a table  (Tom Lane)
Responses: Re: Poor Performance on a table  (Tom Lane)
Re: Poor Performance on a table  (Josh Berkus)
List: pgsql-performance

Tree view

Poor Performance on a table  (Pallav Kalva, )
 Re: Poor Performance on a table  (Frank Wiles, )
  Re: Poor Performance on a table  (Pallav Kalva, )
   Re: Poor Performance on a table  (Frank Wiles, )
 Re: Poor Performance on a table  (Tom Lane, )
  Re: Poor Performance on a table  (Pallav Kalva, )
   Re: Poor Performance on a table  (Tom Lane, )
   Re: Poor Performance on a table  (Josh Berkus, )

Tom Lane wrote:

>Pallav Kalva <> writes:
>
>
>>    I have a table in my production database which has 500k rows and
>>from the pg_class it shows the number of "relpages" of
>>around 750K for this table, the same table copied to a test database
>>shows "relpages" as 35k. I run vacuumdb on the whole
>>database (not on the table individually but the whole database) daily.
>>
>>
>
>You're obviously suffering serious table bloat :-(.  Depending on how
>heavy the update traffic on that table is, it might be that once-a-day
>vacuum is simply not often enough.  Another likely problem is that you
>need to increase the FSM settings (how big is your whole database?)
>
 Yes, you are right this table is heavily updated, the whole database
size is of 1.5 gigs, right now i have default fsm settings how much
should i increase max_fsm_pages  and max_fsm_relations to ?

>
>
>
>>  Is there any way to fix this problem ?
>>
>>
>
>VACUUM FULL will fix the immediate problem.  You might well find CLUSTER
>to be a faster alternative, though.
>
>
I am hesitant to do vacuum full on the table because it is one of the
crucial table in our application and we cant afford to have exclusive
lock on this table for long time.  we can afford not to have writes and
updates but we need atleast reads on this table .
How does CLUSTER benefit me ? excuse me, i am new to this feature.

>            regards, tom lane
>
>
>




pgsql-performance by date:

From: "Iain"
Date:
Subject: Re: Normalization or Performance
From: Christopher Browne
Date:
Subject: Re: pg replication tools?