Thread: slow growing table
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
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/
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/
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
> 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 >
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 =:->