Thread: Vacuum full hang
I'm trying to vacuum full a table ( Postgres 7.2.3 ) but the command: vacuum full <my_table> hang and all processes that are trying to update that table hang too, the CPU occupation for the process that perform the vacuum is 0. What is going on? Ciao Gaetano
Hmm. VACUUM FULL is supposed to do that only i guess. since version 7.2 ordinary VACUUM does not locks the table. and VACUUM can be performed on a live system. VACUUM FULL does the disk space reclaimation and is supposed to lock table to prevent updates. i guess you can do a VACUUM ANALYZE on the table instead. regds Mallah. > I'm trying to vacuum full a table ( Postgres 7.2.3 ) > but the command: > vacuum full <my_table> > hang and all processes that are trying to update that table hang too, the CPU occupation for > the process that perform the > vacuum is 0. What is going on? > > > > Ciao > Gaetano > > > > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you > checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
<mallah@trade-india.com> wrote in message news:8162.203.122.61.30.1044192306.squirrel@mail.trade-india.com... > > > Hmm. VACUUM FULL is supposed to do that only i guess. > > since version 7.2 ordinary VACUUM does not locks the table. > and VACUUM can be performed on a live system. > > VACUUM FULL does the disk space reclaimation and is supposed to lock > table to prevent updates. > > i guess you can do a VACUUM ANALYZE on the table instead. > > regds > Mallah. Your guess is wrong, in my case the vacuum analyze is not enough, I have a lot of space wasted in my HD, I need a vacuum analyze and I don't care if some process during the vacuum hang ( just during the vacuum operation ) but here the strange is that also the "vacuum" hang. Ciao Gaetano
are u sure VACUUM FULL is hanging? run in verbose mode... VACUUM FULL VERBOSE ANALYZE <tablename> and you are really desperate get the pid of the backend who is doing the vacuum and strace -p <pid> i am sure u will see it spitting lots of read and writes. ;-) regds mallah. > <mallah@trade-india.com> wrote in message > news:8162.203.122.61.30.1044192306.squirrel@mail.trade-india.com... >> >> >> Hmm. VACUUM FULL is supposed to do that only i guess. >> >> since version 7.2 ordinary VACUUM does not locks the table. >> and VACUUM can be performed on a live system. >> >> VACUUM FULL does the disk space reclaimation and is supposed to lock table to prevent updates. >> >> i guess you can do a VACUUM ANALYZE on the table instead. >> >> regds >> Mallah. > > Your guess is wrong, in my case the vacuum analyze is not enough, I have a lot > of space wasted in my HD, I need a vacuum analyze and I don't care if some process > during the vacuum hang ( just during the vacuum operation ) but here the strange is > that also the "vacuum" hang. > > Ciao > Gaetano > > > > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and > unsubscribe commands go to majordomo@postgresql.org ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> I'm trying to vacuum full a table ( Postgres 7.2.3 )
> but the command:
> vacuum full <my_table>
> hang and all processes that are trying to update that table hang too,
> the CPU occupation for the process that perform the
> vacuum is 0. What is going on?
Are you sure it's actually hung, and not busy doing the vacuum? Vacuum
is generally I/O bound not CPU bound, so I place little faith in using
the CPU idle time to decide that it's not doing anything.
If it *is* waiting, the reason is probably that some other process has a
lock on the table and is failing to commit its transaction and release
the lock. The vacuum will be queued up waiting to get exclusive lock
--- and everything else that might want to access the table will queue
up behind the vacuum.
In 7.3 you could look at the pg_locks view to confirm that idea, but
in 7.2 you can't do much more than look at ps to see if there are any
"idle in transaction" processes. Those would be the most likely
culprits to be sitting on locks.
regards, tom lane
<mallah@trade-india.com> wrote in message
news:56131.203.122.61.30.1044193279.squirrel@mail.trade-india.com...
>
> are u sure VACUUM FULL is hanging?
>
> run in verbose mode...
>
> VACUUM FULL VERBOSE ANALYZE <tablename>
I don't see nothing going on
> and you are really desperate get the pid of the
> backend who is doing the vacuum and
>
> strace -p <pid> i am sure u will see it spitting lots of read and
writes.
> ;-)
I see only:
recv(9, "Qvacuum full;\0", 8192, 0) = 14
send(5, "\3\0\0\0%\0\0\0!\0\0\0wy\0\0\33H\26\0d\0\0\0vacuum f"..., 37, 0) =
37
time(NULL) = 1044264831
time([1044264831]) = 1044264831
getpid() = 31095
rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
send(8, "<135>Feb 3 10:33:51 postgres[31"..., 79, 0) = 79
rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
gettimeofday({1044264831, 553516}, NULL) = 0
time(NULL) = 1044264831
time([1044264831]) = 1044264831
getpid() = 31095
rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
send(8, "<135>Feb 3 10:33:51 postgres[31"..., 75, 0) = 75
rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
time(NULL) = 1044264831
time([1044264831]) = 1044264831
getpid() = 31095
rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
send(8, "<135>Feb 3 10:33:51 postgres[31"..., 84, 0) = 84
rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
open("/var/lib/pgsql/data/global/pgstat.stat", O_RDONLY) = 37
fstat64(37, {st_mode=S_IFREG|0600, st_size=52815, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x4001f000
read(37, "D\1\0\0\0\340\2662\10\0\0\0\0\216\6\0\0\0\0\0\0S\24\0\0"..., 4096)
= 4096
brk(0x83c8000) = 0x83c8000
read(37, "\0\0\0\0\0\0:\0\0\0\0\0\0\0\335*\224\1\0\0\0\0\217\353"..., 4096)
= 4096
brk(0x83cd000) = 0x83cd000
read(37, "\0\0\0\0\0\0\350\22\250\2\0\0\0\0$\243\247\2\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\25\4\0\0\0\0\0\0\0\0\0\0TS\352*\0\0\0\0\0"..., 4096)
= 4096
brk(0x83d6000) = 0x83d6000
read(37, "\0\0\0\0\0\0\0\0\0\0T\204\352*\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0T\256H\26\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\0\24\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
brk(0x83e7000) = 0x83e7000
read(37, "\0\0\0\0\0\0\0\24\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0"..., 4096)
= 4096
brk(0x83f0000) = 0x83f0000
read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0B\33H\26\0}\0\0\0<"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 4096
read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
= 3663
close(37) = 0
munmap(0x4001f000, 4096) = 0
lseek(25, 0, SEEK_END) = 8192
lseek(3, 0, SEEK_END) = 376832
gettimeofday({1044264831, 559402}, NULL) = 0
setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={1, 0}},
{it_interval={0, 0}, it_value={0, 0}}) = 0
semop(21037068, 0xbfffed00, 1) = -1 EINTR (Interrupted system call)
--- SIGALRM (Alarm clock) ---
sigreturn() = ? (mask now [])
semop(21037068, 0xbfffed00, 1
that's all, after this remain there for ever.
Is true that in other and some people here use to connect to DB
throw a JDBC interface and with the parameter Autocommit ON
there is always a process inside a transaction....
Ciao
Gaetano.
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> gettimeofday({1044264831, 559402}, NULL) = 0
> setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={1, 0}},
> {it_interval={0, 0}, it_value={0, 0}}) = 0
> semop(21037068, 0xbfffed00, 1) = -1 EINTR (Interrupted system call)
> --- SIGALRM (Alarm clock) ---
> sigreturn() = ? (mask now [])
> semop(21037068, 0xbfffed00, 1
> that's all, after this remain there for ever.
Looks like my guess was right: it's waiting for some other process to
release a lock. Check for open transactions that have accessed the
table in question.
regards, tom lane
sorry gaetano,
i am not knowledgable enough to help you in this situation
anymore.
regds
mallah.
On Monday 03 February 2003 03:07 pm, Gaetano Mendola wrote:
> <mallah@trade-india.com> wrote in message
> news:56131.203.122.61.30.1044193279.squirrel@mail.trade-india.com...
>
> > are u sure VACUUM FULL is hanging?
> >
> > run in verbose mode...
> >
> > VACUUM FULL VERBOSE ANALYZE <tablename>
>
> I don't see nothing going on
>
> > and you are really desperate get the pid of the
> > backend who is doing the vacuum and
> >
> > strace -p <pid> i am sure u will see it spitting lots of read and
>
> writes.
>
> > ;-)
>
> I see only:
>
> recv(9, "Qvacuum full;\0", 8192, 0) = 14
> send(5, "\3\0\0\0%\0\0\0!\0\0\0wy\0\0\33H\26\0d\0\0\0vacuum f"..., 37, 0) =
> 37
> time(NULL) = 1044264831
> time([1044264831]) = 1044264831
> getpid() = 31095
> rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
> send(8, "<135>Feb 3 10:33:51 postgres[31"..., 79, 0) = 79
> rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
> gettimeofday({1044264831, 553516}, NULL) = 0
> time(NULL) = 1044264831
> time([1044264831]) = 1044264831
> getpid() = 31095
> rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
> send(8, "<135>Feb 3 10:33:51 postgres[31"..., 75, 0) = 75
> rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
> time(NULL) = 1044264831
> time([1044264831]) = 1044264831
> getpid() = 31095
> rt_sigaction(SIGPIPE, {0x403248c8, [], 0x4000000}, {SIG_IGN}, 8) = 0
> send(8, "<135>Feb 3 10:33:51 postgres[31"..., 84, 0) = 84
> rt_sigaction(SIGPIPE, {SIG_IGN}, NULL, 8) = 0
> open("/var/lib/pgsql/data/global/pgstat.stat", O_RDONLY) = 37
> fstat64(37, {st_mode=S_IFREG|0600, st_size=52815, ...}) = 0
> old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
> 0) = 0x4001f000
> read(37, "D\1\0\0\0\340\2662\10\0\0\0\0\216\6\0\0\0\0\0\0S\24\0\0"...,
> 4096) = 4096
> brk(0x83c8000) = 0x83c8000
> read(37, "\0\0\0\0\0\0:\0\0\0\0\0\0\0\335*\224\1\0\0\0\0\217\353"..., 4096)
> = 4096
> brk(0x83cd000) = 0x83cd000
> read(37, "\0\0\0\0\0\0\350\22\250\2\0\0\0\0$\243\247\2\0\0\0\0\0"..., 4096)
> = 4096
> read(37, "\0\0\0\0\0\0\25\4\0\0\0\0\0\0\0\0\0\0TS\352*\0\0\0\0\0"..., 4096)
> = 4096
> brk(0x83d6000) = 0x83d6000
> read(37, "\0\0\0\0\0\0\0\0\0\0T\204\352*\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
> = 4096
> read(37, "\0\0T\256H\26\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
> 4096) = 4096
> read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
> = 4096
> read(37, "\0\0\0\0\0\0\0\24\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
> 4096) = 4096
> brk(0x83e7000) = 0x83e7000
> read(37, "\0\0\0\0\0\0\0\24\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"...,
> 4096) = 4096
> read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\1\0\0\0"..., 4096)
> = 4096
> brk(0x83f0000) = 0x83f0000
> read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0B\33H\26\0}\0\0\0<"..., 4096)
> = 4096
> read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
> = 4096
> read(37, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096)
> = 3663
> close(37) = 0
> munmap(0x4001f000, 4096) = 0
> lseek(25, 0, SEEK_END) = 8192
> lseek(3, 0, SEEK_END) = 376832
> gettimeofday({1044264831, 559402}, NULL) = 0
> setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={1, 0}},
> {it_interval={0, 0}, it_value={0, 0}}) = 0
> semop(21037068, 0xbfffed00, 1) = -1 EINTR (Interrupted system
> call) --- SIGALRM (Alarm clock) ---
> sigreturn() = ? (mask now [])
> semop(21037068, 0xbfffed00, 1
>
>
>
> that's all, after this remain there for ever.
>
> Is true that in other and some people here use to connect to DB
> throw a JDBC interface and with the parameter Autocommit ON
> there is always a process inside a transaction....
>
> Ciao
> Gaetano.
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Regds
Mallah
----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.