Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A21ED9FB3AD for ; Fri, 28 Sep 2007 11:28:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 22217-01 for ; Fri, 28 Sep 2007 11:28:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id 25B4D9F9CF5 for ; Fri, 28 Sep 2007 11:28:06 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so1584539ugf for ; Fri, 28 Sep 2007 07:28:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=jVwapu1IFGwZDzUOYKJTEFGE25F1T+RWTRGfjg3Kdew=; b=Kpkm+I2pkszFoUmWOBVAi/mIJXEALDWYyDt/iKoyUp3PgO7f+lttKhLekewGpc5fnkD7fUahpAY3BYeOM6kVLfLSYTHaV2oXnm3ob43wJRbv07soeJLW4USH6l5mdIXJ5ZqvDKjqdaZQvApC+HUJX8AiTCsYUp7pXrUwwzdJvtU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=BXkgei1tf6czYOH510mPZaZaA1OLddqeMJPX7/cNs6M2881GnYXJmF5Hr5CaPqp7Erp05FhEGMwsHyePnlDgCLw/A66//FwggMESWDHAO1lIZGDeSWQrquTR9fBfA+u4yeRys/7c3D35eOpi/Gnj73is1eAErZTc1rpkdIlyoWQ= Received: by 10.66.239.2 with SMTP id m2mr5114292ugh.1190989683751; Fri, 28 Sep 2007 07:28:03 -0700 (PDT) Received: by 10.67.98.4 with HTTP; Fri, 28 Sep 2007 07:28:03 -0700 (PDT) Message-ID: Date: Fri, 28 Sep 2007 10:28:03 -0400 From: "Radhika S" To: pgsql-performance@postgresql.org Subject: Postgres 7.4.2 hanging when vacuum full is run MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_564_19045722.1190989683743" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/467 X-Sequence-Number: 27012 ------=_Part_564_19045722.1190989683743 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi - This has been happening more recently. Our database hangs after a VACUUM and is unresponsive when we come in next morning. The vacuum job runs at 03:00 am daily. The command is : /usr/local/pgsql/bin/vacuumdb --full -d DbName Also, what exactly does this mean VACUUM waiting. Is there a reason why it is never emerging from the VACUUM job? I understand that doing a vacuumdb --full causes the tables to lock (not really sure about the workings of vacuum). Any light on this would be really appreciated. Thanks, Radhika Below is what ps -ef |grep postgres shows: 5530 ? S 0:13 /usr/local/pgsql/bin/postmaster -i 5534 ? S 0:01 postgres: stats buffer process 5535 ? S 0:04 postgres: stats collector process 5621 ? S 0:53 postgres: slony myDB 10.142.20.50 idle 5626 ? S 0:51 postgres: slony myDB 10.142.20.50 idle 5627 ? S 0:34 postgres: slony myDB 10.142.20.50 idle 5628 ? S 5:40 postgres: slony myDB 10.142.20.50 idle 5637 ? S 2:09 postgres: slony myDB 10.132.20.26 idle 5638 ? S 1:56 postgres: slony myDB 10.132.20.26 idle 5745 ? S 42:08 postgres: abc myDB [local] idle 20774 ? S 4:29 postgres: abc myDB [local] idle 20775 ? S 0:00 postgres: abc myDB [local] idle in transaction 20776 ? S 0:00 postgres: abc myDB [local] idle 17509 ? S 0:06 postgres: abc myDB [local] VACUUM waiting 24656 ? S 0:00 postgres: abc myDB [local] INSERT waiting 30489 ? S 0:00 postgres: abc myDB [local] SELECT waiting 30637 ? S 0:00 postgres: abc myDB [local] UPDATE waiting 30647 ? S 0:00 postgres: abc myDB [local] UPDATE waiting 30668 ? S 0:00 postgres: abc myDB [local] UPDATE waiting -- It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall ------=_Part_564_19045722.1190989683743 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi -

This has been happening more recently. Our database  hangs after a VACUUM and is unresponsive when we come in next morning.

The vacuum job runs at 03:00 am daily.
The command is : /usr/local/pgsql/bin/vacuumdb --full -d DbName

Also, what exactly does this mean VACUUM waiting. Is there a reason why it is never emerging from the VACUUM job?
I understand that doing a vacuumdb --full causes the tables to lock (not really sure about the workings of vacuum).

Any light on this would be really appreciated.

Thanks,
Radhika


Below is what ps -ef |grep postgres shows:

 5530 ?        S      0:13 /usr/local/pgsql/bin/postmaster -i
     5534 ?        S      0:01 postgres: stats buffer process   
     5535 ?        S      0:04 postgres: stats collector process  
     5621 ?        S      0:53 postgres: slony myDB 10.142.20.50 idle
     5626 ?        S      0:51 postgres: slony myDB 10.142.20.50 idle
     5627 ?        S      0:34 postgres: slony myDB 10.142.20.50 idle
     5628 ?        S      5:40 postgres: slony myDB 10.142.20.50 idle
     5637 ?        S      2:09 postgres: slony myDB 10.132.20.26 idle
     5638 ?        S      1:56 postgres: slony myDB 10.132.20.26 idle
     5745 ?        S     42:08 postgres: abc myDB [local] idle 
    20774 ?        S      4:29 postgres: abc myDB [local] idle 
    20775 ?        S      0:00 postgres: abc myDB [local] idle in transaction
    20776 ?        S      0:00 postgres: abc myDB [local] idle 
    17509 ?        S      0:06 postgres: abc myDB [local] VACUUM waiting
    24656 ?        S      0:00 postgres: abc myDB [local] INSERT waiting
    30489 ?        S      0:00 postgres: abc myDB [local] SELECT waiting
    30637 ?        S      0:00 postgres: abc myDB [local] UPDATE waiting
    30647 ?        S      0:00 postgres: abc myDB [local] UPDATE waiting
    30668 ?        S      0:00 postgres: abc myDB [local] UPDATE waiting


--
It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall ------=_Part_564_19045722.1190989683743--