Thread: What to do about a vacuum...
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
"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
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
"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
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
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
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