Thread: What to do about a vacuum...

What to do about a vacuum...

From
"Robert M. Meyer"
Date:
IN my infinite wisdom, in an effort to resolve my performance problems,
I started a full vacuum of my database last night around 4:00AM.  It's
still running at 9;20AM and it has all of the tables locked.  Is it safe
to cancel the query from inside pgmonitor?  I've pretty much determined
that ripping the thread out by the roots is a bad thing.

This goes back to my earlier thread about postgres performance slowly
getting worse.  I made the assumption that we were doing full vacuums
knew for sure.  I checked the script yesterday and found that the '-f'
flag was not turned on...so I turned it on.

It has gotten to the part where it pegs the CPU (99.9% of one cpu) and
it's been there for a while.  So far, it's rung up 3:13 of CPU time
since it started.

Can I cancel the query and what will that do to me?

Thanks...

Cheers!

Bob

--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451


Re: What to do about a vacuum...

From
Tom Lane
Date:
"Robert M. Meyer" <rmeyer@installs.com> writes:
> IN my infinite wisdom, in an effort to resolve my performance problems,
> I started a full vacuum of my database last night around 4:00AM.  It's
> still running at 9;20AM and it has all of the tables locked.

Huh?  Vacuum should only lock one table at a time.

> Is it safe to cancel the query from inside pgmonitor?

Yes, a SIGINT should be safe enough.  You'll lose the benefit of whatever
vacuuming work has been done so far on the current table.

            regards, tom lane

Re: What to do about a vacuum...

From
"Robert M. Meyer"
Date:
Well, 'kill -INT pid' doesn't seem to have worked.  This is 7.2.1.  We
really don't want to whack the system but we need to get back online...

Any other ideas?

Bob

On Fri, 2002-08-09 at 09:47, Tom Lane wrote:
> "Robert M. Meyer" <rmeyer@installs.com> writes:
> > IN my infinite wisdom, in an effort to resolve my performance problems,
> > I started a full vacuum of my database last night around 4:00AM.  It's
> > still running at 9;20AM and it has all of the tables locked.
>
> Huh?  Vacuum should only lock one table at a time.
>
> > Is it safe to cancel the query from inside pgmonitor?
>
> Yes, a SIGINT should be safe enough.  You'll lose the benefit of whatever
> vacuuming work has been done so far on the current table.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451


Re: What to do about a vacuum...

From
Tom Lane
Date:
"Robert M. Meyer" <rmeyer@installs.com> writes:
> Well, 'kill -INT pid' doesn't seem to have worked.  This is 7.2.1.  We
> really don't want to whack the system but we need to get back online...

You sure you sigint'ed the right process?  I can't see that VACUUM could
take more than a few seconds to respond to a cancel --- it checks before
moving onto each new page.

            regards, tom lane

Re: What to do about a vacuum...

From
"Robert M. Meyer"
Date:
Yes, I checked it.  Tried three times.  It's not listening and it's got
>96% of one of the CPUs.  I'm about to try the 'terminate' button in
pgmonitor.  It ignored the 'Cancel button'.

Cheers!

Bob

On Fri, 2002-08-09 at 11:20, Tom Lane wrote:
> "Robert M. Meyer" <rmeyer@installs.com> writes:
> > Well, 'kill -INT pid' doesn't seem to have worked.  This is 7.2.1.  We
> > really don't want to whack the system but we need to get back online...
>
> You sure you sigint'ed the right process?  I can't see that VACUUM could
> take more than a few seconds to respond to a cancel --- it checks before
> moving onto each new page.
>
>             regards, tom lane
--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451


Re: What to do about a vacuum...

From
Andrew Sullivan
Date:
On Fri, Aug 09, 2002 at 11:20:06AM -0400, Tom Lane wrote:
> "Robert M. Meyer" <rmeyer@installs.com> writes:
> > Well, 'kill -INT pid' doesn't seem to have worked.  This is 7.2.1.  We
> > really don't want to whack the system but we need to get back online...
>
> You sure you sigint'ed the right process?  I can't see that VACUUM could
> take more than a few seconds to respond to a cancel --- it checks before
> moving onto each new page.

Will vacuum respond to SIGINT if it's blocked waiting for a
transaction to commit?

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


Re: What to do about a vacuum...

From
Tom Lane
Date:
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Fri, Aug 09, 2002 at 11:20:06AM -0400, Tom Lane wrote:
>> You sure you sigint'ed the right process?  I can't see that VACUUM could
>> take more than a few seconds to respond to a cancel --- it checks before
>> moving onto each new page.

> Will vacuum respond to SIGINT if it's blocked waiting for a
> transaction to commit?

I believe so --- that's just a special case of waiting for a lock, and
SIGINT should be able to interrupt anything that's waiting for a lock.

            regards, tom lane