Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Gaetano Mendola
Bruce Momjian wrote: Gaetano Mendola wrote: Bruce Momjian wrote: Scott Marlowe wrote: I use a checkpoint_segments = 16 but in my pg_xlog I have 35 files. Why 35 files ? You have 35 because the max files in pg_xlog is 2*checkpoint_segments +1 or something like that. This is documented in

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Matthew T. O'Connor
Gaetano Mendola wrote: Well, today I stop the pg_autovacuum and I did a vacuum full and I reindexed all big tables and other 500 MB were reclamed. Could be the pg_autovacuum running yesterday the responsible for these 500MB not reclamed during a vacuum full and reindex already performed yesterday

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Gaetano Mendola
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew T. O'Connor wrote: | Gaetano Mendola wrote: | | Well, today I stop the pg_autovacuum and I did a vacuum full and I | reindexed | all big tables and other 500 MB were reclamed. Could be the pg_autovacuum | running yesterday the responsible for

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Matthew T. O'Connor
Gaetano Mendola wrote: I'm pretty sure, see the attached graph. Each morning at 7 a script stop the autovacuum, vacuum full the database and reindex the eavy updated tables and restart of course the autovacuum. Note also that for all the day I didn't have the usual disk usage increment. I don't

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Gaetano Mendola
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew T. O'Connor wrote: | Gaetano Mendola wrote: | | I'm pretty sure, see the attached graph. Each morning at 7 a script stop | the autovacuum, vacuum full the database and reindex the eavy updated | tables | and restart of course the autovacuum.

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Rod Taylor
I don't know why the 1st VACUUM FULL wasn't able to reclaim the same amount of space as the 2nd one, but I would guess that it wasn't able to get a lock on some table. It could have been autovac if it was doing a vacuum at that moment, but it could have been something else too. Or there

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Simon Riggs
On Wed, 2004-07-21 at 18:54, Rod Taylor wrote: I don't know why the 1st VACUUM FULL wasn't able to reclaim the same amount of space as the 2nd one, but I would guess that it wasn't able to get a lock on some table. It could have been autovac if it was doing a vacuum at that moment, but

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Rod Taylor
What happens when a transaction fails to either commit or abort as a result of a serious error? That looks like a transaction-in-progress doesn't it? Would that prevent VACUUM from doing its work? It should be able to check the last startup xid to check that isn't the case, but suppose a

Re: [HACKERS] check point segments leakage ?

2004-07-21 Thread Tom Lane
... Would that prevent VACUUM from doing its work? It should be able to check the last startup xid to check that isn't the case, but suppose a backend had exited without taking down the postmaster. There is no such thing as a backend crashing without the postmaster noticing (at least not

[HACKERS] check point segments leakage ?

2004-07-20 Thread Gaetano Mendola
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, today I add 4 new columns to a table with 4E+06 rows, I also update to an initial value these new columns. The new columns are 3 INTEGER one of type DOUBLE. The table have also 5 indexes. Immediately after the operation my partition data had an

Re: [HACKERS] check point segments leakage ?

2004-07-20 Thread Joshua D. Drake
Hello, Perhaps you have an open transaction that isn't closing and thus the pg_xlog continues to grow? Sincerely, Joshua D. Drake Gaetano Mendola wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, today I add 4 new columns to a table with 4E+06 rows, I also update to an initial value

Re: [HACKERS] check point segments leakage ?

2004-07-20 Thread Scott Marlowe
Bruce said the other day open transactions can't cause this problem. I wonder what all can? On Tue, 2004-07-20 at 16:32, Joshua D. Drake wrote: Hello, Perhaps you have an open transaction that isn't closing and thus the pg_xlog continues to grow? Sincerely, Joshua D. Drake

Re: [HACKERS] check point segments leakage ?

2004-07-20 Thread Gaetano Mendola
Joshua D. Drake wrote: Hello, Perhaps you have an open transaction that isn't closing and thus the pg_xlog continues to grow? Sincerely, Joshua D. Drake I was thinking about it but unfortunately there is no transaction open. On my development database, were I simulate the same operation

Re: [HACKERS] check point segments leakage ?

2004-07-20 Thread Bruce Momjian
Scott Marlowe wrote: I use a checkpoint_segments = 16 but in my pg_xlog I have 35 files. Why 35 files ? You have 35 because the max files in pg_xlog is 2*checkpoint_segments +1 or something like that. This is documented in the SGML. -- Bruce Momjian|

Re: [HACKERS] check point segments leakage ?

2004-07-20 Thread Bruce Momjian
Gaetano Mendola wrote: Bruce Momjian wrote: Scott Marlowe wrote: I use a checkpoint_segments = 16 but in my pg_xlog I have 35 files. Why 35 files ? You have 35 because the max files in pg_xlog is 2*checkpoint_segments +1 or something like that. This is documented in the SGML.