pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor Performance on a table
8+ messages / 4 participants
[nested] [flat]
* Poor Performance on a table
@ 2004-12-02 19:11 Pallav Kalva <pkalva@deg.cc>
0 siblings, 2 replies; 8+ messages in thread
From: Pallav Kalva @ 2004-12-02 19:11 UTC (permalink / raw)
To: pgsql-performance
Hi ,
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.
I think because of this most of queries are slowing down which used to
run much faster before.
Is there any way to fix this problem ?
Thanks!
Pallav
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 19:20 Frank Wiles <frank@wiles.org>
parent: Pallav Kalva <pkalva@deg.cc>
1 sibling, 1 reply; 8+ messages in thread
From: Frank Wiles @ 2004-12-02 19:20 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
On Thu, 02 Dec 2004 14:11:46 -0500
Pallav Kalva <pkalva@deg.cc> wrote:
> Hi ,
>
> 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.
>
> I think because of this most of queries are slowing down which used to
>
> run much faster before.
> Is there any way to fix this problem ?
Try a VACUUM FULL, this will clean up unused space. You might also
want to adjust your free space map so that you don't have to do FULL
vacuums as often ( or at all ). It is controlled by max_fsm_pages
and max_fsm_relations.
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 19:32 Pallav Kalva <pkalva@deg.cc>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 1 reply; 8+ messages in thread
From: Pallav Kalva @ 2004-12-02 19:32 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
Hi Frank,
Thanks! for the quick reply, here are my current default fsm setting .
max_fsm_pages = 20000 and max_fsm_relations = 1000
What are the appropriates settings for these parameters ? are there
any guidlines ? postgres docs doesnt give much information on setting
these values.
Thanks!
Pallav
Frank Wiles wrote:
>On Thu, 02 Dec 2004 14:11:46 -0500
>Pallav Kalva <pkalva@deg.cc> wrote:
>
>
>
>>Hi ,
>>
>> 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.
>>
>>I think because of this most of queries are slowing down which used to
>>
>>run much faster before.
>> Is there any way to fix this problem ?
>>
>>
>
> Try a VACUUM FULL, this will clean up unused space. You might also
> want to adjust your free space map so that you don't have to do FULL
> vacuums as often ( or at all ). It is controlled by max_fsm_pages
> and max_fsm_relations.
>
> ---------------------------------
> Frank Wiles <frank@wiles.org>
> http://www.wiles.org
> ---------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 19:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pallav Kalva <pkalva@deg.cc>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2004-12-02 19:36 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> 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?)
> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 19:38 Frank Wiles <frank@wiles.org>
parent: Pallav Kalva <pkalva@deg.cc>
0 siblings, 0 replies; 8+ messages in thread
From: Frank Wiles @ 2004-12-02 19:38 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
On Thu, 02 Dec 2004 14:32:53 -0500
Pallav Kalva <pkalva@deg.cc> wrote:
> Hi Frank,
>
> Thanks! for the quick reply, here are my current default fsm
> setting .
> max_fsm_pages = 20000 and max_fsm_relations = 1000
> What are the appropriates settings for these parameters ? are there
>
> any guidlines ? postgres docs doesnt give much information on setting
> these values.
There really aren't any guidelines on these because it really depends
on your data and how you use the database. If you insert/update 99%
of the time and only delete 1% of the time, the defaults are probably
perfect for you. Probably up to a 80% insert/update, 20% delete
ratio.
If however you're constantly deleting entries from your database, I
would suggest slowly raising those values in step with each other
over the course a few weeks and see where you're at. It is really
a matter of trial an error.
With my databases, I can afford to do VACUUM FULLs fairly often
so I typically don't need to increase my fsm values.
---------------------------------
Frank Wiles <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 19:54 Pallav Kalva <pkalva@deg.cc>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Pallav Kalva @ 2004-12-02 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> 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
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-02 20:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pallav Kalva <pkalva@deg.cc>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-12-02 20:10 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> writes:
> Tom Lane wrote:
>> 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 ?
A lot --- factor of 10 at least. Try "vacuum verbose" and look at the
last couple lines of output.
>> VACUUM FULL will fix the immediate problem. You might well find CLUSTER
>> to be a faster alternative, though.
> How does CLUSTER benefit me ?
It'll probably take less time to rebuild the table. VACUUM FULL is
really optimized for the case of moving a relatively small fraction
of the table around, but it sounds like you need a complete rebuild.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Poor Performance on a table
@ 2004-12-03 05:57 Josh Berkus <josh@agliodbs.com>
parent: Pallav Kalva <pkalva@deg.cc>
1 sibling, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2004-12-03 05:57 UTC (permalink / raw)
To: pgsql-performance; +Cc: Pallav Kalva <pkalva@deg.cc>; Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-12-03 05:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-02 19:11 Poor Performance on a table Pallav Kalva <pkalva@deg.cc>
2004-12-02 19:20 ` Frank Wiles <frank@wiles.org>
2004-12-02 19:32 ` Pallav Kalva <pkalva@deg.cc>
2004-12-02 19:38 ` Frank Wiles <frank@wiles.org>
2004-12-02 19:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-02 19:54 ` Pallav Kalva <pkalva@deg.cc>
2004-12-02 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-03 05:57 ` Josh Berkus <josh@agliodbs.com>
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