Re: Poor Performance on a table

Pallav,

> 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 ?

1) fix the table (see below)
2) run the system for another day
3) run VACUUM FULL ANALYZE VERBOSE
4) if you're running 7.4 or better, at the end you'll see a total of FSM pages
needed. If you're running something earlier, you'll need to get out a
calculator and do the math yourself.

Of course, if you're getting heavy update/delete activity, vacuuming more
often might be wise. Post the output of the above command if you have
questions.

> 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 .

You're going to have to do at least one or the table will just keep getting
worse. Schedule it for 3am. Once you've set FSM correctly, and are
vacuuming with the right frequency, the need to run VACUUM FULL will go away.

Oh, and it's likely that any indexes on the table need to be REINDEXed.

--
Josh Berkus
Aglio Database Solutions
San Francisco