vacuuming not working? 
Author Message
 vacuuming not working?

Quote:

> Me again,

> I have a problem with VACUUM. I searched the list and don't seem to see
> anyone else reporting this behavior so I'm wondering if i'm doing something
> wrong
> NOTICE:  Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep 40000, UnUsed

                                                            ^^^^^^^^^^

The "Keep" number in VACUUM means some transaction can still see those
rows.  Check for long-lived transactions{*filter*} around.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 25 Sep 2004 02:46:25 GMT
 vacuuming not working?

That's why I isolated it down to a standalone example ... There is no other
process looking at that table ... no foreign keys or other things that could
cause those records to be "in use" ... That was the first thing that ran
through my mind when my application was starting to get really slow ...

Also, you'll see from my script that I exit and re-enter psql between each
operation (INSERT, DELETE, VACUUM) so if the transaction wasn't committing,
then the rows wouldn't exist the next time I entered ...

Is there a query I can run to see who is holding those tuples which is
causing VACUUM to leave them in place?

-dave

Quote:
> -----Original Message-----


> Sent: Monday, April 08, 2002 2:45 PM
> To: David Esposito
> Cc: Postgres general mailing list
> Subject: Re: [GENERAL] vacuuming not working?


> > Me again,

> > I have a problem with VACUUM. I searched the list and don't seem to see
> > anyone else reporting this behavior so I'm wondering if i'm
> doing something
> > wrong

> > NOTICE:  Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep
> 40000, UnUsed
>                                                             ^^^^^^^^^^

> The "Keep" number in VACUUM means some transaction can still see those
> rows.  Check for long-lived transactions{*filter*} around.

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 25 Sep 2004 03:06:50 GMT
 vacuuming not working?
Is there some sort of time delay involved with VACUUM that I should be aware
of? I ran a VACUUM FULL on the table that I had run the test on this morning
and sure enough, it shrunk down to zero size (there were no rows in the
table) but then I reran the test and then ran VACUUM FULL and it DID NOT
shrink the file on disk ...

Is there some sort of cache or something that's never getting timed out (the
out of control 500 meg table file i made mention of, gets dumped and
repopulated every 2 minutes ... is there something that's never getting
removed from a LRU cache?)

Here's my VACUUM command ...

Quote:
> -----Original Message-----

> Sullivan
> Sent: Monday, April 08, 2002 2:55 PM
> To: David Esposito
> Subject: Re: [GENERAL] vacuuming not working?


> > Me again,

> > I have a problem with VACUUM. I searched the list and don't seem to see
> > anyone else reporting this behavior so I'm wondering if i'm
> doing something
> > wrong

> > Summary: Vacuuming does not seem to mark the space as
> "available for reuse"
> > as the documentation says.

> You haven't read the documentation carefully enough, or else you're
> reading old docs.

> > Platform: Postgres 7.2.1 on RedHat 7.0
>                        ^
> > The problem is that the file on disk is over 500 megs! and it
> appears to be
> > growing pretty much without bound ... I do a VACUUM every night (not a
> > VACUUM FULL, just a straight VACUUM)
>   ^^^^^^^^^^^

> Only the VACUUM FULL makes the disk space available for reuse (by
> other programs; postgres will be able to reuse some of the space with
> lazy VACUUM).

> A

> --
> ----
> Andrew Sullivan                               87 Mowat Avenue
> Liberty RMS                           Toronto, Ontario Canada

>                                          +1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly


Sat, 25 Sep 2004 03:17:02 GMT
 vacuuming not working?
damnit ... i hit send too soon .. ;)

blahdb=# vacuum full verbose analyze test_high_turnover;
NOTICE:  --Relation test_high_turnover--
NOTICE:  Pages 1061: Changed 0, reaped 101, Empty 0, New 0; Tup 60000: Vac
0, Keep/VTL 60000/0, UnUsed 5013, MinLen 123, MaxLen 125; Re-using:
Free/Avail. Space 756440/641816; EndEmpty/Avail. Pages 0/84.
        CPU 0.09s/0.02u sec elapsed 0.10 sec.
NOTICE:  Rel test_high_turnover: Pages: 1061 --> 981; Tuple(s) moved: 4989.
        CPU 0.05s/0.04u sec elapsed 0.17 sec.
NOTICE:  Analyzing test_high_turnover
VACUUM

Quote:
> -----Original Message-----

> Sent: Monday, April 08, 2002 3:09 PM
> To: Andrew Sullivan

> Subject: RE: [GENERAL] vacuuming not working?

> Is there some sort of time delay involved with VACUUM that I
> should be aware of? I ran a VACUUM FULL on the table that I had
> run the test on this morning and sure enough, it shrunk down to
> zero size (there were no rows in the table) but then I reran the
> test and then ran VACUUM FULL and it DID NOT shrink the file on disk ...

> Is there some sort of cache or something that's never getting
> timed out (the out of control 500 meg table file i made mention
> of, gets dumped and repopulated every 2 minutes ... is there
> something that's never getting removed from a LRU cache?)

> Here's my VACUUM command ...

> > -----Original Message-----

> > Sullivan
> > Sent: Monday, April 08, 2002 2:55 PM
> > To: David Esposito
> > Subject: Re: [GENERAL] vacuuming not working?


> > > Me again,

> > > I have a problem with VACUUM. I searched the list and don't
> seem to see
> > > anyone else reporting this behavior so I'm wondering if i'm
> > doing something
> > > wrong

> > > Summary: Vacuuming does not seem to mark the space as
> > "available for reuse"
> > > as the documentation says.

> > You haven't read the documentation carefully enough, or else you're
> > reading old docs.

> > > Platform: Postgres 7.2.1 on RedHat 7.0
> >                        ^
> > > The problem is that the file on disk is over 500 megs! and it
> > appears to be
> > > growing pretty much without bound ... I do a VACUUM every night (not a
> > > VACUUM FULL, just a straight VACUUM)
> >   ^^^^^^^^^^^

> > Only the VACUUM FULL makes the disk space available for reuse (by
> > other programs; postgres will be able to reuse some of the space with
> > lazy VACUUM).

> > A

> > --
> > ----
> > Andrew Sullivan                               87 Mowat Avenue
> > Liberty RMS                           Toronto, Ontario Canada

> >                                          +1 416 646 3304 x110

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sat, 25 Sep 2004 03:19:10 GMT
 vacuuming not working?

Quote:

> That's why I isolated it down to a standalone example ... There is no other
> process looking at that table ... no foreign keys or other things that could
> cause those records to be "in use" ...

Whether there *is* something looking at that table is not the issue.
The issue is whether there is an open transaction old enough that if
it chose to look at the table, it would see now-deleted rows.  If so,
VACUUM can't remove those rows, since it doesn't have any way to know
whether the old transaction will later choose to look.

Given your later message, I suspect there was such an open transaction
and it exited.  There is not any really good way to look for this
situation, although the pg_stat_activity view (in 7.2) is better than
nothing.  Perhaps in future releases, we should add columns to
pg_stat_activity that would indicate how old each backend's open
transaction is.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sat, 25 Sep 2004 03:39:37 GMT
 vacuuming not working?

Quote:

> i took a peek at pg_stat_activity and it doesn't appear as though any of the
> processes are doing anything ... they all have blanks for "current_query"

Read
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitorin...

Quote:
> Is there any way to get timestamps turned on in the log file?

There's a flag in postgresql.conf.  You might wanna turn on log_pid too.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------



Sat, 25 Sep 2004 03:54:39 GMT
 vacuuming not working?
You can also do a ps -x to see current connections and whether they are
performing a query, idle or idle in transaction.

Greg

Quote:
----- Original Message -----




Sent: Monday, April 08, 2002 3:47 PM
Subject: Re: [GENERAL] vacuuming not working?

> i took a peek at pg_stat_activity and it doesn't appear as though any of
the
> processes are doing anything ... they all have blanks for "current_query"
> ... (when i'm vacuuming and when i'm running the populate/clear queries)

> Is there any way to get timestamps turned on in the log file? Is there a
> chance that this "defunct" transaction is cleaned when the transaction
logs
> rotate? I see this in my log file but i haven't got the foggiest idea when
> it happened ...  ;) ... and whether it resulted in me being able to vacuum
> the table successfully ...

> DEBUG:  recycled transaction log file 0000000100000039

> > -----Original Message-----

> > Sent: Monday, April 08, 2002 3:38 PM
> > To: David Esposito
> > Cc: Jeffrey W. Baker; Postgres general mailing list
> > Subject: Re: [GENERAL] vacuuming not working?


> > > That's why I isolated it down to a standalone example ... There
> > is no other
> > > process looking at that table ... no foreign keys or other
> > things that could
> > > cause those records to be "in use" ...

> > Whether there *is* something looking at that table is not the issue.
> > The issue is whether there is an open transaction old enough that if
> > it chose to look at the table, it would see now-deleted rows.  If so,
> > VACUUM can't remove those rows, since it doesn't have any way to know
> > whether the old transaction will later choose to look.

> > Given your later message, I suspect there was such an open transaction
> > and it exited.  There is not any really good way to look for this
> > situation, although the pg_stat_activity view (in 7.2) is better than
> > nothing.  Perhaps in future releases, we should add columns to
> > pg_stat_activity that would indicate how old each backend's open
> > transaction is.

> > regards, tom lane

> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

> http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sat, 25 Sep 2004 04:02:28 GMT
 vacuuming not working?
i took a peek at pg_stat_activity and it doesn't appear as though any of the
processes are doing anything ... they all have blanks for "current_query"
... (when i'm vacuuming and when i'm running the populate/clear queries)

Is there any way to get timestamps turned on in the log file? Is there a
chance that this "defunct" transaction is cleaned when the transaction logs
rotate? I see this in my log file but i haven't got the foggiest idea when
it happened ...  ;) ... and whether it resulted in me being able to vacuum
the table successfully ...

DEBUG:  recycled transaction log file 0000000100000039

Quote:
> -----Original Message-----

> Sent: Monday, April 08, 2002 3:38 PM
> To: David Esposito
> Cc: Jeffrey W. Baker; Postgres general mailing list
> Subject: Re: [GENERAL] vacuuming not working?


> > That's why I isolated it down to a standalone example ... There
> is no other
> > process looking at that table ... no foreign keys or other
> things that could
> > cause those records to be "in use" ...

> Whether there *is* something looking at that table is not the issue.
> The issue is whether there is an open transaction old enough that if
> it chose to look at the table, it would see now-deleted rows.  If so,
> VACUUM can't remove those rows, since it doesn't have any way to know
> whether the old transaction will later choose to look.

> Given your later message, I suspect there was such an open transaction
> and it exited.  There is not any really good way to look for this
> situation, although the pg_stat_activity view (in 7.2) is better than
> nothing.  Perhaps in future releases, we should add columns to
> pg_stat_activity that would indicate how old each backend's open
> transaction is.

>                    regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sat, 25 Sep 2004 04:12:34 GMT
 vacuuming not working?
Ok, some further experimentation has proven that this problem does not
manifest itself if there are ZERO connections to the database (aside from
the actual connection doing the insert/delete/vacuum) ... The vacuum command
correctly reclaims the space and the on-disk file never grows beyond a
certain size ...

So, here's the million dollar question ... and one that may or may not be a
postgres question ... I normally have a pool of connections connected to
postgres from my application server (jboss) ... they are all normally in the
"idle in transaction" state (if i do a ps -ax, they are all listed as "idle
in transaction") ... the question is, are these other connections actually
holding open a transaction that would prevent the table from being properly
vacuumed? if so, is there anything that i can do about it ... (yes, i
suppose i need to figure out why the JDBC connections have transactions
started before they're actually needed) ... and was this the case with
postgres 7.1.3?

but i figured i'd run it by the experts too ... ;)

-dave

Quote:
> -----Original Message-----


> Sent: Monday, April 08, 2002 3:53 PM
> To: David Esposito
> Cc: Jeffrey W. Baker; Postgres general mailing list
> Subject: Re: [GENERAL] vacuuming not working?


> > i took a peek at pg_stat_activity and it doesn't appear as
> though any of the
> > processes are doing anything ... they all have blanks for
> "current_query"

> Read
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monito
> ring-stats.html

> > Is there any way to get timestamps turned on in the log file?

> There's a flag in postgresql.conf.  You might wanna turn on log_pid too.

>                    regards, tom lane

> ---------------------------(end of broadcast)---------------------------


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Sat, 25 Sep 2004 05:37:08 GMT
 vacuuming not working?

Quote:

> So, here's the million dollar question ... and one that may or may not be a
> postgres question ... I normally have a pool of connections connected to
> postgres from my application server (jboss) ... they are all normally in the
> "idle in transaction" state (if i do a ps -ax, they are all listed as "idle
> in transaction") ... the question is, are these other connections actually
> holding open a transaction that would prevent the table from being properly
> vacuumed?

Yes.  If the ps status display says it's in a transaction, then it is.

Quote:
> if so, is there anything that i can do about it ... (yes, i
> suppose i need to figure out why the JDBC connections have transactions
> started before they're actually needed) ...

Darn if I know.  You might try asking pgsql-jdbc list.

Quote:
> and was this the case with postgres 7.1.3?

VACUUM has always acted that way, as far back as I've been involved.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Sat, 25 Sep 2004 05:39:03 GMT
 vacuuming not working?

Quote:
> -----Original Message-----

> Sullivan
> Sent: Tuesday, April 09, 2002 3:39 PM
> To: David Esposito
> Subject: Re: [GENERAL] vacuuming not working?


> > So, here's the million dollar question ... and one that may or
> may not be a
> > postgres question ... I normally have a pool of connections connected to
> > postgres from my application server (jboss) ... they are all
> normally in the
> > "idle in transaction" state (if i do a ps -ax, they are all
> listed as "idle
> > in transaction")

> That's your problem.

> > ... the question is, are these other connections actually
> > holding open a transaction that would prevent the table from
> being properly
> > vacuumed?

> It _is_ being properly vacuumed.  Nothing that was in the table at
> the time the transaction started can be removed (because the idle
> transaction _could_ be looking for it).

disclaimer: i've never looked at the source code and, unfortunately, do not
have the time to do so ... so please, don't flog me ...

but it seems as though the VACCUMING process is intended to bring the
physical data storage in sync with the logical data storage ... that is, it
removes pages on the disk that are no longer used by the database to store
data ... so then, why would vacuum give a damn about other transactions that
might be in progress? if everyone is using SERIALIZABLE for their isolation
level, i could see why this might be necessary ... but since all of my
transactions are using READ_COMMITTED, it shouldn't matter if i do a logical
delete (DELETE FROM) followed by a vacuum (physical cleanup of the unused
pages) as far as the other transactions are concerned ... there is no
guarantee made to those transactions that the rows in my table will still
exist when they attempt to query them ... (SELECT, UPDATE, DELETE, etc...)

am I missing something here? ...

-dave

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sun, 26 Sep 2004 04:49:10 GMT
 vacuuming not working?
It's not a fault of the JDBC driver, you're right that it won't hold open a
transaction ... it's with my application server ... which, i unfortunately
don't have control over ... for some reason it seems to keep all of its
connections in an open transaction ...

the really mysterious part is the way that it has always been ... and prior
to my upgrade to 7.2.1, i ran a vacuum and a reindex every night and the
space was reclaimed ... that's what brought this whole thing on ...
something has changed ...

-dave

Quote:
> -----Original Message-----


> Sent: Tuesday, April 09, 2002 3:41 PM
> To: PostgreSQL general list
> Subject: Re: [GENERAL] vacuuming not working?



> > > if so, is there anything that i can do about it ... (yes, i
> > > suppose i need to figure out why the JDBC connections have
> transactions
> > > started before they're actually needed) ...

> > Darn if I know.  You might try asking pgsql-jdbc list.

> I _believe_ this is a feature of the autocommit=off setting.  I think
> you can turn autocommit on and start and end your transactions
> yourself.  Check the docs, though -- I've been shown to have faulty
> memory.

> A

> --
> ----
> Andrew Sullivan                               87 Mowat Avenue
> Liberty RMS                           Toronto, Ontario Canada

>                                          +1 416 646 3304 x110

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly


Sun, 26 Sep 2004 04:49:28 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. VACUUM FULL not working with persistent connections

2. VACUUM FULL not working with persistent connections in v7.2

3. Question regarding effects of Vacuum, Vacuum Analyze, and Reindex

4. Vacuum return codes (vacuum as db integrity check?)

5. VACUUM AND VACUUM ANALYSE

6. Vacuuming and re-indexing (was Re: Vacuum meaning)

7. VACUUM vs VACUUM ANALYZE

8. Vacuum and Vacuum analyse

9. Still confused about VACUUM vs. VACUUM FULL

10. Vacuum VS Vacuum Analyze

11. pg_class.reltuples not reset by VACUUM?

12. a vacuum thread is not the answer


 
Powered by phpBB® Forum Software