Thread: vacuum full problem

vacuum full problem

From
pginfo
Date:
Hi,
I am running pg 7.3.4 on linux red hat 9.0.

If I try to execute vacuum full analyze verbose, the pg vacuum some
tables and hang after this lines:

INFO:  --Relation pg_toast.pg_toast_16408--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_16408_index: Pages 1; Tuples 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.

After rebooting I can vacuum without any problems.
I do not find nothing in my log-file that can point me to this problem.
Also the problem comes constantly by me.
Any idea or info about this problem?
Also I am using pg + java (jboss), but I think vacuum is working normal
with it and I have many instalations without this problem.

regards,
ivan.


Re: vacuum full problem

From
Antonis Antoniou
Date:

pginfo wrote:

>Hi,
>I am running pg 7.3.4 on linux red hat 9.0.
>
>If I try to execute vacuum full analyze verbose, the pg vacuum some
>tables and hang after this lines:
>
>INFO:  --Relation pg_toast.pg_toast_16408--
>INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
>Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
>0/0; EndEmpty/Avail. Pages 0/0.
>        CPU 0.00s/0.00u sec elapsed 0.00 sec.
>INFO:  Index pg_toast_16408_index: Pages 1; Tuples 0.
>        CPU 0.00s/0.00u sec elapsed 0.00 sec.
>
>After rebooting I can vacuum without any problems.
>I do not find nothing in my log-file that can point me to this problem.
>Also the problem comes constantly by me.
>Any idea or info about this problem?
>Also I am using pg + java (jboss), but I think vacuum is working normal
>with it and I have many instalations without this problem.
>
Try to "reindex table pg_toast.pg_toast_16408;" Maybe an index is broken.

Many Thanks
Antonis



Re: vacuum full problem

From
pginfo
Date:
Hi,
I try to reindex and reindex run ok, but the vacuum still not running.
regards,
ivan.

Antonis Antoniou wrote:

> pginfo wrote:
>
> >Hi,
> >I am running pg 7.3.4 on linux red hat 9.0.
> >
> >If I try to execute vacuum full analyze verbose, the pg vacuum some
> >tables and hang after this lines:
> >
> >INFO:  --Relation pg_toast.pg_toast_16408--
> >INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
> >Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
> >0/0; EndEmpty/Avail. Pages 0/0.
> >        CPU 0.00s/0.00u sec elapsed 0.00 sec.
> >INFO:  Index pg_toast_16408_index: Pages 1; Tuples 0.
> >        CPU 0.00s/0.00u sec elapsed 0.00 sec.
> >
> >After rebooting I can vacuum without any problems.
> >I do not find nothing in my log-file that can point me to this problem.
> >Also the problem comes constantly by me.
> >Any idea or info about this problem?
> >Also I am using pg + java (jboss), but I think vacuum is working normal
> >with it and I have many instalations without this problem.
> >
> Try to "reindex table pg_toast.pg_toast_16408;" Maybe an index is broken.
>
> Many Thanks
> Antonis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster




Re: vacuum full problem

From
Antonis Antoniou
Date:

Guys, does anyone has any idea?

pginfo wrote:
Hi,
I try to reindex and reindex run ok, but the vacuum still not running.
regards,
ivan.

Antonis Antoniou wrote:
 
pginfo wrote:
   
Hi,
I am running pg 7.3.4 on linux red hat 9.0.

If I try to execute vacuum full analyze verbose, the pg vacuum some
tables and hang after this lines:

INFO:  --Relation pg_toast.pg_toast_16408--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.      CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_16408_index: Pages 1; Tuples 0.      CPU 0.00s/0.00u sec elapsed 0.00 sec.

After rebooting I can vacuum without any problems.
I do not find nothing in my log-file that can point me to this problem.
Also the problem comes constantly by me.
Any idea or info about this problem?
Also I am using pg + java (jboss), but I think vacuum is working normal
with it and I have many instalations without this problem.
     
Try to "reindex table pg_toast.pg_toast_16408;" Maybe an index is broken.

Many Thanks
Antonis

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

 

Re: vacuum full problem

From
Stephan Szabo
Date:
On Tue, 11 Nov 2003, Antonis Antoniou wrote:

> Guys, does anyone has any idea?

Are you sure that vacuum is not just waiting on a lock that some other
transaction is holding?  I'd suggest checking the contents of pg_locks.

Re: vacuum full problem

From
pginfo
Date:
Hi,
I can not be sure if it is not the case.
But we are usin this system on a number of servers and it happen only by
one.
Can I with a pg_locks help detect the query that is running?

regards,
ivan.

Stephan Szabo wrote:

> On Tue, 11 Nov 2003, Antonis Antoniou wrote:
>
> > Guys, does anyone has any idea?
>
> Are you sure that vacuum is not just waiting on a lock that some other
> transaction is holding?  I'd suggest checking the contents of pg_locks.




Re: vacuum full problem

From
Gaetano Mendola
Date:
pginfo wrote:

> Hi,
> I can not be sure if it is not the case.
> But we are usin this system on a number of servers and it happen only by
> one.
> Can I with a pg_locks help detect the query that is running?

No the pg_locks detect only which process handle a lock on a
database object.
With pg_stat_activity you see the queries running.

Also the command

ps -eafwww | grep post

show you the connection and his state, I bet
you have some connection "IDLE in TRANSACTION"

Regards
Gaeatano Mendola


Re: vacuum full problem

From
Stephan Szabo
Date:
On Tue, 11 Nov 2003, pginfo wrote:

> Hi,
> I can not be sure if it is not the case.
> But we are usin this system on a number of servers and it happen only by
> one.
> Can I with a pg_locks help detect the query that is running?

It won't directly tell you what statement is running (although my guess is
also that it's probably something like an idle in transaction state
backend), but it can tell you if that's the problem probably.

You'd see something like:
    ...
    21488 |    17057 |             | 8613 | AccessShareLock | t
    ...
    21488 |    17057 |             | 8626 | AccessExclusiveLock | f

Which is basically saying that the command in pid 8626 is waiting on the
lock that pid 8613 has.

Re: vacuum full problem

From
pginfo
Date:
Hi,
Thanks for the help.

The result by is:
acc01=# select * from pg_locks;
 relation | database | transaction |  pid  |      mode       | granted
----------+----------+-------------+-------+-----------------+---------
    16757 |    16976 |             | 23169 | AccessShareLock | t
    17062 |    16976 |             |  1372 | AccessShareLock | t
    17060 |    16976 |             |  1372 | AccessShareLock | t
          |          |      116303 |  1372 | ExclusiveLock   | t
          |          |      166846 | 23169 | ExclusiveLock   | t
    16995 |    16976 |             |  1372 | AccessShareLock | t
    17056 |    16976 |             |  1372 | AccessShareLock | t
    17038 |    16976 |             |  1372 | AccessShareLock | t
(8 rows)

And only transaction 116303 is the problem.
Also it is production system with many transaction/sec and I am looking dor a
way to detect what query is in this transaction ( if possible).
The system is relativ big and it is dificult to detect this problem only from
transacton number, but we will read the code to find the bug.
At this point is is clear that we do not have pg bug and it is good news for
me.

regards,
ivan

Stephan Szabo wrote:

> On Tue, 11 Nov 2003, pginfo wrote:
>
> > Hi,
> > I can not be sure if it is not the case.
> > But we are usin this system on a number of servers and it happen only by
> > one.
> > Can I with a pg_locks help detect the query that is running?
>
> It won't directly tell you what statement is running (although my guess is
> also that it's probably something like an idle in transaction state
> backend), but it can tell you if that's the problem probably.
>
> You'd see something like:
>         ...
>     21488 |    17057 |             | 8613 | AccessShareLock | t
>         ...
>     21488 |    17057 |             | 8626 | AccessExclusiveLock | f
>
> Which is basically saying that the command in pid 8626 is waiting on the
> lock that pid 8613 has.




Re: vacuum full problem

From
Stephan Szabo
Date:
On Tue, 11 Nov 2003, pginfo wrote:

> The result by is:
> acc01=# select * from pg_locks;
>  relation | database | transaction |  pid  |      mode       | granted
> ----------+----------+-------------+-------+-----------------+---------
>     16757 |    16976 |             | 23169 | AccessShareLock | t
>     17062 |    16976 |             |  1372 | AccessShareLock | t
>     17060 |    16976 |             |  1372 | AccessShareLock | t
>           |          |      116303 |  1372 | ExclusiveLock   | t
>           |          |      166846 | 23169 | ExclusiveLock   | t
>     16995 |    16976 |             |  1372 | AccessShareLock | t
>     17056 |    16976 |             |  1372 | AccessShareLock | t
>     17038 |    16976 |             |  1372 | AccessShareLock | t
> (8 rows)

Is that while the vacuum is running or just a general state that you might
run the vacuum in?

> And only transaction 116303 is the problem.
> Also it is production system with many transaction/sec and I am looking dor a
> way to detect what query is in this transaction ( if possible).
> The system is relativ big and it is dificult to detect this problem only from
> transacton number, but we will read the code to find the bug.

Well, you can turn on the statement statistics stuff and/or the
statement logging stuff to help you find what the various transactions
are doing.  I'd wonder if you're maybe not closing a transaction after
it's completed its work though and so the locks are sitting around.


Re: vacuum full problem

From
pginfo
Date:
Hi,

Stephan Szabo wrote:

> On Tue, 11 Nov 2003, pginfo wrote:
>
> > The result by is:
> > acc01=# select * from pg_locks;
> >  relation | database | transaction |  pid  |      mode       | granted
> > ----------+----------+-------------+-------+-----------------+---------
> >     16757 |    16976 |             | 23169 | AccessShareLock | t
> >     17062 |    16976 |             |  1372 | AccessShareLock | t
> >     17060 |    16976 |             |  1372 | AccessShareLock | t
> >           |          |      116303 |  1372 | ExclusiveLock   | t
> >           |          |      166846 | 23169 | ExclusiveLock   | t
> >     16995 |    16976 |             |  1372 | AccessShareLock | t
> >     17056 |    16976 |             |  1372 | AccessShareLock | t
> >     17038 |    16976 |             |  1372 | AccessShareLock | t
> > (8 rows)
>
> Is that while the vacuum is running or just a general state that you might
> run the vacuum in?
>

It looks as general state.

> > And only transaction 116303 is the problem.
> > Also it is production system with many transaction/sec and I am looking dor a
> > way to detect what query is in this transaction ( if possible).
> > The system is relativ big and it is dificult to detect this problem only from
> > transacton number, but we will read the code to find the bug.
>
> Well, you can turn on the statement statistics stuff and/or the
> statement logging stuff to help you find what the various transactions
> are doing.  I'd wonder if you're maybe not closing a transaction after
> it's completed its work though and so the locks are sitting around.

I will do it.
It is possible to be one not closed transaction, but in this case nobody will be
able to modify this table (tables) and
the system will stop to respond. The paradox is that the system works well without
any problems and on this basis I wrote that (at the beginning) that the problem is
by vacuum.
How big is the penalty for statistic on?

Generaly I think we will be able to found the problem quick.

regards,
ivan


Re: vacuum full problem

From
Stephan Szabo
Date:
On Tue, 11 Nov 2003, pginfo wrote:

> Stephan Szabo wrote:
>
> > On Tue, 11 Nov 2003, pginfo wrote:
> >
> > > The result by is:
> > > acc01=# select * from pg_locks;
> > >  relation | database | transaction |  pid  |      mode       | granted
> > > ----------+----------+-------------+-------+-----------------+---------
> > >     16757 |    16976 |             | 23169 | AccessShareLock | t
> > >     17062 |    16976 |             |  1372 | AccessShareLock | t
> > >     17060 |    16976 |             |  1372 | AccessShareLock | t
> > >           |          |      116303 |  1372 | ExclusiveLock   | t
> > >           |          |      166846 | 23169 | ExclusiveLock   | t
> > >     16995 |    16976 |             |  1372 | AccessShareLock | t
> > >     17056 |    16976 |             |  1372 | AccessShareLock | t
> > >     17038 |    16976 |             |  1372 | AccessShareLock | t
> > > (8 rows)
> >
> > Is that while the vacuum is running or just a general state that you might
> > run the vacuum in?
> >
>
> It looks as general state.
>
> > > And only transaction 116303 is the problem.
> > > Also it is production system with many transaction/sec and I am looking dor a
> > > way to detect what query is in this transaction ( if possible).
> > > The system is relativ big and it is dificult to detect this problem only from
> > > transacton number, but we will read the code to find the bug.
> >
> > Well, you can turn on the statement statistics stuff and/or the
> > statement logging stuff to help you find what the various transactions
> > are doing.  I'd wonder if you're maybe not closing a transaction after
> > it's completed its work though and so the locks are sitting around.
>
> I will do it.
> It is possible to be one not closed transaction, but in this case nobody will be
> able to modify this table (tables) and
> the system will stop to respond. The paradox is that the system works well without

Not necessarily. People are going to be able to insert/update/delete from
the tables (the locks are AccessShareLock) because those don't get a
conflicting table lock.  They're not going to be able to do things like
vacuum full or alter table however because those do.

> How big is the penalty for statistic on?

I'm not sure, but you can turn it on until you find it and then turn it
off again.

Re: vacuum full problem

From
pginfo
Date:

Stephan Szabo wrote:

> On Tue, 11 Nov 2003, pginfo wrote:
>
> > Stephan Szabo wrote:
> >
> > > On Tue, 11 Nov 2003, pginfo wrote:
> > >
> > > > The result by is:
> > > > acc01=# select * from pg_locks;
> > > >  relation | database | transaction |  pid  |      mode       | granted
> > > > ----------+----------+-------------+-------+-----------------+---------
> > > >     16757 |    16976 |             | 23169 | AccessShareLock | t
> > > >     17062 |    16976 |             |  1372 | AccessShareLock | t
> > > >     17060 |    16976 |             |  1372 | AccessShareLock | t
> > > >           |          |      116303 |  1372 | ExclusiveLock   | t
> > > >           |          |      166846 | 23169 | ExclusiveLock   | t
> > > >     16995 |    16976 |             |  1372 | AccessShareLock | t
> > > >     17056 |    16976 |             |  1372 | AccessShareLock | t
> > > >     17038 |    16976 |             |  1372 | AccessShareLock | t
> > > > (8 rows)
> > >
> > > Is that while the vacuum is running or just a general state that you might
> > > run the vacuum in?
> > >
> >
> > It looks as general state.
> >
> > > > And only transaction 116303 is the problem.
> > > > Also it is production system with many transaction/sec and I am looking dor a
> > > > way to detect what query is in this transaction ( if possible).
> > > > The system is relativ big and it is dificult to detect this problem only from
> > > > transacton number, but we will read the code to find the bug.
> > >
> > > Well, you can turn on the statement statistics stuff and/or the
> > > statement logging stuff to help you find what the various transactions
> > > are doing.  I'd wonder if you're maybe not closing a transaction after
> > > it's completed its work though and so the locks are sitting around.
> >
> > I will do it.
> > It is possible to be one not closed transaction, but in this case nobody will be
> > able to modify this table (tables) and
> > the system will stop to respond. The paradox is that the system works well without
>
> Not necessarily. People are going to be able to insert/update/delete from
> the tables (the locks are AccessShareLock) because those don't get a
> conflicting table lock.  They're not going to be able to do things like
> vacuum full or alter table however because those do.
>

Can you point me to any place in docs to read more detailed about locks and statistic (
I have idea, butt also I will to know more if possible).

> > How big is the penalty for statistic on?
>
> I'm not sure, but you can turn it on until you find it and then turn it
> off again.

  regards,
ivan.


Re: vacuum full problem

From
Stephan Szabo
Date:
On Tue, 11 Nov 2003, pginfo wrote:

> > > It is possible to be one not closed transaction, but in this case nobody will be
> > > able to modify this table (tables) and
> > > the system will stop to respond. The paradox is that the system works well without
> >
> > Not necessarily. People are going to be able to insert/update/delete from
> > the tables (the locks are AccessShareLock) because those don't get a
> > conflicting table lock.  They're not going to be able to do things like
> > vacuum full or alter table however because those do.
> >
>
> Can you point me to any place in docs to read more detailed about locks and statistic (
> I have idea, butt also I will to know more if possible).

Well, for a list of the lock levels and some examples of where they're
used you might want to see:
http://www.postgresql.org/docs/7.3/static/explicit-locking.html

Re: vacuum full problem

From
pginfo
Date:

Stephan Szabo wrote:

> On Tue, 11 Nov 2003, pginfo wrote:
>
> > > > It is possible to be one not closed transaction, but in this case nobody will be
> > > > able to modify this table (tables) and
> > > > the system will stop to respond. The paradox is that the system works well without
> > >
> > > Not necessarily. People are going to be able to insert/update/delete from
> > > the tables (the locks are AccessShareLock) because those don't get a
> > > conflicting table lock.  They're not going to be able to do things like
> > > vacuum full or alter table however because those do.
> > >
> >
> > Can you point me to any place in docs to read more detailed about locks and statistic (
> > I have idea, butt also I will to know more if possible).
>
> Well, for a list of the lock levels and some examples of where they're
> used you might want to see:
> http://www.postgresql.org/docs/7.3/static/explicit-locking.html
>

Thanks, it is good.regards,
ivan.

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org