Thread: vacuum full problem
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.
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
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
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
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.
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.
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
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.
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.
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.
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
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.
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.
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
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