Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-31 Thread Andrew Sullivan
On Fri, Mar 25, 2005 at 06:21:24PM -0500, Bruce Momjian wrote: Can we issue a LOCK TABLE with a statement_timeout, and only do the VACUUM FULL if we can get a lock quickly? That seems like a plan. I think someone else's remark in this thread is important, though: autovacuum shouldn't ever

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-27 Thread Matthew T. O'Connor
Well the simple answer is that pg_autovacuum didn't see 10,000 inserts updates or deletes. pg_autovacuum saw:476095 - 471336 = 4759 U/D's relevant for vacuuming and 634119 - 629121 = 4998 I/U/D's relevant for performing analyze. The tough question is why is pg_autovacuum not seeing all

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
Blomqvist [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Thursday, March 24, 2005 3:58 PM Subject: Re: [PERFORM] pg_autovacuum not having enough suction ? I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: It does not seem to be a Stats collector

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Matthew T. O'Connor
: Matthew T. O'Connor matthew@zeut.net To: Otto Blomqvist [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Thursday, March 24, 2005 3:58 PM Subject: Re: [PERFORM] pg_autovacuum not having enough suction ? I would rather keep this on list since other people can chime in. Otto Blomqvist wrote

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
Matthew T. O'Connor matthew@zeut.net writes: hmm the value in reltuples should be accurate after a vacuum (or vacuum analyze) if it's not it's a vacuum bug or something is going on that isn't understood. If you or pg_autovacuum are running plain analyze commands, that could explain the

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
: Re: [PERFORM] pg_autovacuum not having enough suction ? I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: It does not seem to be a Stats collector problem. oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname

lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?)

2005-03-25 Thread Tom Lane
I wrote: One thing that is possibly relevant here is that in 8.0 a plain VACUUM doesn't set reltuples to the exactly correct number, but to an interpolated value that reflects our estimate of the steady state average between vacuums. I wonder if that code is wrong, or if it's operating as

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
Another thing to check is whether the reltuples (and relpages!) that autovacuum is reporting are the same as what's actually in the pg_class row for the relation. I'm wondering if this could be a similar issue to the old autovac bug where it wasn't reading the value correctly. These values

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Matthew T. O'Connor
Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: hmm the value in reltuples should be accurate after a vacuum (or vacuum analyze) if it's not it's a vacuum bug or something is going on that isn't understood. If you or pg_autovacuum are running plain analyze commands, that

Re: lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?)

2005-03-25 Thread Tom Lane
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2005-03-25 at 15:22 -0500, Tom Lane wrote: 2. Dead tuples don't have that much influence on scan costs either, at least not once they are marked as known-dead. Certainly they shouldn't be charged at full freight. Yes, minor additional CPU time,

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
Otto Blomqvist wrote: This table is basically a queue full of records waiting to get transfered over from our 68030 system to the PG database. The records are then moved into folders (using a trigger) like file_92_myy depending on what month the record was created on the 68030. During normal

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Joshua D. Drake
I'm not sure if autovacuum could be taught to do that --- it could perhaps launch a vacuum as soon as it notices a large fraction of the table got deleted, but do we really want to authorize it to launch VACUUM FULL? It'd be better to issue the vacuum synchronously as part of the batch

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Tom Lane
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: I'm not sure if autovacuum could be taught to do that --- it could perhaps launch a vacuum as soon as it notices a large fraction of the table got deleted, but do we really want to authorize it to launch VACUUM FULL? One problem

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Bruce Momjian
Tom Lane wrote: Otto Blomqvist wrote: This table is basically a queue full of records waiting to get transfered over from our 68030 system to the PG database. The records are then moved into folders (using a trigger) like file_92_myy depending on what month the record was created on the

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: I'm not sure if autovacuum could be taught to do that --- it could perhaps launch a vacuum as soon as it notices a large fraction of the table got deleted, but do we really want to authorize it to launch VACUUM

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-25 Thread Otto Blomqvist
ok, Thanks a lot for your time guys ! I guess my table is pretty unusual and thats why this problem has not surfaced until now. Better late then never ;) I'll cron a manual vacuum full on the table. Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Otto Blomqvist wrote:

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Tom Lane
Otto Blomqvist [EMAIL PROTECTED] writes: Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could autovacuum let this happen ? What PG version is this? (The earlier autovacuum releases had some bugs with large tables, thus the question...) regards, tom

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Otto Blomqvist
Sorry about that. I'm Running 8.0.0 on Linux Redhat 8.0 Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Otto Blomqvist [EMAIL PROTECTED] writes: Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could autovacuum let this happen ? What PG version is

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Matthew T. O'Connor
The version that shipped with 8.0 should be fine. The only version that had the problem Tom referred to are in the early 7.4.x releases. Did you get my other message about information from the stats system (I'm not sure why my other post has yet to show up on the performance list). Matthew

Re: [PERFORM] pg_autovacuum not having enough suction ?

2005-03-24 Thread Matthew T. O'Connor
I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: It does not seem to be a Stats collector problem. oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del