pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow growing table
6+ messages / 4 participants
[nested] [flat]
* slow growing table
@ 2005-06-06 16:48 Jone C <jonecster@gmail.com>
2005-06-06 17:00 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-07 22:21 ` Re: slow growing table Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 6+ messages in thread
From: Jone C @ 2005-06-06 16:48 UTC (permalink / raw)
To: pgsql-performance
HI!
I have a table that I use for about a month. As the month progresses,
COPYs performed to this table get much much slower than they were at
the beginning, for the same number of rows (about 100,000 and
growing).
I'm essentially doing a delete for a given day, then a COPY as a big
transaction. This is done about 12 times a day.
When the table is new it's very fast, towards the end of the month
it's taking almost 10 times longer, yet I'm deleting and COPYing in
the same amount of data. Other operations on this table slow down,
too, that were fast before using the same criteria.
I do a VACUUM ANALYZE after each delete / COPY process, I tried
experimenting with CLUSTER but saw no real difference.
this is psql 7.45 on Linux server, dedicated for this purpose. About 5
indexes, no FKs on this table.
happy to provide any other info might need, suggestions appreciated
all my best,
Jone
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow growing table
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
@ 2005-06-06 17:00 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-06 17:12 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 1 reply; 6+ messages in thread
From: Steinar H. Gunderson @ 2005-06-06 17:00 UTC (permalink / raw)
To: pgsql-performance
On Mon, Jun 06, 2005 at 09:48:26AM -0700, Jone C wrote:
> When the table is new it's very fast, towards the end of the month
> it's taking almost 10 times longer, yet I'm deleting and COPYing in
> the same amount of data. Other operations on this table slow down,
> too, that were fast before using the same criteria.
You might have a problem with index bloat. Could you try REINDEXing the
indexes on the table and see if that makes a difference?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow growing table
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
2005-06-06 17:00 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2005-06-06 17:12 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-21 15:05 ` Re: slow growing table Jone C <jonecster@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Steinar H. Gunderson @ 2005-06-06 17:12 UTC (permalink / raw)
To: pgsql-performance
On Mon, Jun 06, 2005 at 07:00:37PM +0200, Steinar H. Gunderson wrote:
> You might have a problem with index bloat. Could you try REINDEXing the
> indexes on the table and see if that makes a difference?
On second thought... Does a VACUUM FULL help? If so, you might want to
increase your FSM settings.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow growing table
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
2005-06-06 17:00 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-06 17:12 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2005-06-21 15:05 ` Jone C <jonecster@gmail.com>
2005-06-21 15:19 ` Re: slow growing table John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jone C @ 2005-06-21 15:05 UTC (permalink / raw)
To: pgsql-performance
> On second thought... Does a VACUUM FULL help? If so, you might want to
> increase your FSM settings.
Thank you for the reply, sorry for delay I was on holiday.
I tried that it had no effect. I benchmarked 2x before, peformed
VACUUM FULL on the table in question post inserts, then benchmarked 2x
after. Same results...
Should I try your suggestion on deleting the indexes? This table needs
to be accessible for reads at all times however though...
thank you kindly
On 6/6/05, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote:
> On Mon, Jun 06, 2005 at 07:00:37PM +0200, Steinar H. Gunderson wrote:
> > You might have a problem with index bloat. Could you try REINDEXing the
> > indexes on the table and see if that makes a difference?
>
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow growing table
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
2005-06-06 17:00 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-06 17:12 ` Re: slow growing table Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-21 15:05 ` Re: slow growing table Jone C <jonecster@gmail.com>
@ 2005-06-21 15:19 ` John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 6+ messages in thread
From: John A Meinel @ 2005-06-21 15:19 UTC (permalink / raw)
To: Jone C <jonecster@gmail.com>; +Cc: pgsql-performance
Jone C wrote:
>>On second thought... Does a VACUUM FULL help? If so, you might want to
>>increase your FSM settings.
>>
>>
>
>Thank you for the reply, sorry for delay I was on holiday.
>
>I tried that it had no effect. I benchmarked 2x before, peformed
>VACUUM FULL on the table in question post inserts, then benchmarked 2x
>after. Same results...
>
>Should I try your suggestion on deleting the indexes? This table needs
>to be accessible for reads at all times however though...
>
>thank you kindly
>
>
I believe dropping an index inside a transaction is only visible to that
transaction. (Can someone back me up on this?)
Which means if you did:
BEGIN;
DROP INDEX <index in question>;
CREATE INDEX <same index> ON <same stuff>;
COMMIT;
The only problem is that if you are using a unique or primary key index,
a foreign key which is referencing that index would have to be dropped
and re-created as well. So you could have a pretty major cascade effect.
A better thing to do if your table only has one (or at least only a few)
indexes, would be to CLUSTER, which is effectively a VACUUM FULL + a
REINDEX (plus sorting the rows so that they are in index order). It
holds a full lock on the table, and takes a while, but when you are
done, things are cleaned up quite a bit.
You might also try just a REINDEX on the indexes in question, but this
also holds a full lock on the table. (My DROP + CREATE might also as
well, I'm not really sure, I just think of it as a way to recreate
without losing it for other transactions)
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow growing table
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
@ 2005-06-07 22:21 ` Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 6+ messages in thread
From: Simon Riggs @ 2005-06-07 22:21 UTC (permalink / raw)
To: Jone C <jonecster@gmail.com>; +Cc: pgsql-performance
On Mon, 2005-06-06 at 09:48 -0700, Jone C wrote:
> HI!
>
> I have a table that I use for about a month. As the month progresses,
> COPYs performed to this table get much much slower than they were at
> the beginning, for the same number of rows (about 100,000 and
> growing).
>
> I'm essentially doing a delete for a given day, then a COPY as a big
> transaction. This is done about 12 times a day.
>
> When the table is new it's very fast, towards the end of the month
> it's taking almost 10 times longer, yet I'm deleting and COPYing in
> the same amount of data. Other operations on this table slow down,
> too, that were fast before using the same criteria.
>
> I do a VACUUM ANALYZE after each delete / COPY process, I tried
> experimenting with CLUSTER but saw no real difference.
>
> this is psql 7.45 on Linux server, dedicated for this purpose. About 5
> indexes, no FKs on this table.
>
> happy to provide any other info might need, suggestions appreciated
>
Search the archives for details within 4 months of a similar issue.
The consensus was that this was because the indexes had become too big
to fit in memory, hence the leap in response times.
The workaround is to split the table into smaller pieces.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-21 15:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-06 16:48 slow growing table Jone C <jonecster@gmail.com>
2005-06-06 17:00 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-06 17:12 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-06-21 15:05 ` Jone C <jonecster@gmail.com>
2005-06-21 15:19 ` John A Meinel <john@arbash-meinel.com>
2005-06-07 22:21 ` Simon Riggs <simon@2ndquadrant.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