Re: [PERFORM] slow growing table
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 ; CREATE INDEX ON ; 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 =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] slow growing table
> 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] slow growing table
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] slow growing table
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/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] slow growing table
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/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
