From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Pallav Kalva <pkalva@deg.cc>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Poor Performance on a table
Date: Thu, 2 Dec 2004 21:57:00 -0800
Message-ID: <200412022157.00254.josh@agliodbs.com> (raw)
In-Reply-To: <41AF72EB.1070900@deg.cc>
References: <41AF68F2.1050206@deg.cc>
<29227.1102016219@sss.pgh.pa.us>
<41AF72EB.1070900@deg.cc>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, pkalva@deg.cc, tgl@sss.pgh.pa.us
Subject: Re: Poor Performance on a table
In-Reply-To: <200412022157.00254.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox