Thread: slow growing table

slow growing table

From
Jone C
Date:
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

Re: slow growing table

From
"Steinar H. Gunderson"
Date:
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/

Re: slow growing table

From
"Steinar H. Gunderson"
Date:
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/

Re: slow growing table

From
Simon Riggs
Date:
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


Re: slow growing table

From
Jone C
Date:
> 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
>

Re: slow growing table

From
John A Meinel
Date:
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
=:->


Attachment