X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A4BA2529FA for ; Wed, 3 Aug 2005 10:41:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 42059-10 for ; Wed, 3 Aug 2005 13:41:39 +0000 (GMT) Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.200]) by svr1.postgresql.org (Postfix) with ESMTP id 0C51152824 for ; Wed, 3 Aug 2005 10:41:36 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id c2so31650nfe for ; Wed, 03 Aug 2005 06:41:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HxEStnoj7HxgL9WgFA6oKIoyvsnWX9zZWyjMYHGoax6al99xTqhB1j+xtPizC8d/X3ImF7ZMcny3d6Jrut3BXvJm/ug0bRkx4efSKSZv92akO8ZxFm/jmCiLskU7OVmeDp1zHrDkkxoEF0o8C6HQbUb/zeuHo8QOOOQ7A9f1/8s= Received: by 10.48.143.9 with SMTP id q9mr23442nfd; Wed, 03 Aug 2005 06:41:38 -0700 (PDT) Received: by 10.48.144.16 with HTTP; Wed, 3 Aug 2005 06:41:38 -0700 (PDT) Message-ID: Date: Wed, 3 Aug 2005 14:41:38 +0100 From: Guido Barosio To: Scott Barvick Subject: Re: Vacuum doesn't end Cc: pgsql-admin@postgresql.org In-Reply-To: <1123075702.3469.14.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1729_22160076.1123076498226" References: <1123075702.3469.14.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.185 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200508/30 X-Sequence-Number: 18349 ------=_Part_1729_22160076.1123076498226 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Usually, on this situations, you've got a deadlock or alike. What I do is to trace the pid of the vacuum (strace -p pid) and try to gues= s=20 if the vacuum is working or expecting some semaphore to get green (that would be a proble= m=20 on the db side). If this is the problem, a quick look into the procs running in the=20 pg_Stat_activity table will give you an idea of the situation. Prolly, nothing was going on when you started= =20 your vacuum but somehow, a proc acquired a lock somewhere and the vacuum reached that= =20 somewhere causing somekinda wait.=20 At least, this is very often in my scenery, using 7.4.3 and linux also. Regards, Guido On 8/3/05, Scott Barvick wrote: >=20 >=20 > I have an application that may add a couple million rows per day so I > vacuum nightly. The tables never get to more than about 10 million rows > before I move off the interesting information to other media. Somewhat > often, my vacuums don't complete as shown from this ps command (usually > VACUUMs take a couple minutes) >=20 > 4442 ? R 949:07 postgres: postgres tag 127.0.0.1(33420) VACUUM >=20 > and the CPU for postmaster (hyperthreaded linux) will be in the high > 90s. >=20 > The locks look like: > scouts=3D# select * from pg_locks; > relation | database | transaction | pid | mode | > granted >=20 > ----------+----------+-------------+-------+--------------------------+--= ------- > | | 12826125 | 11642 | ExclusiveLock | > t > 16839 | 17230 | | 11642 | AccessShareLock | > t > 17251 | 17230 | | 4442 | RowExclusiveLock | > t > 17251 | 17230 | | 4442 | ShareUpdateExclusiveLock | > t > 17246 | 17230 | | 4442 | ShareUpdateExclusiveLock | > t > 17246 | 17230 | | 4442 | ShareUpdateExclusiveLock | > t > | | 12817402 | 4442 | ExclusiveLock | > t > (7 rows) >=20 >=20 > I all inserts and maintenance through JDBC and may have inserts going on > while a different java thread calls the VACUUM command. >=20 > Any thoughts? Can I recover without dropping the server? >=20 > Thanks for any help, > Scott >=20 >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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 >=20 --=20 "Adopting the position that you are smarter than an automatic optimization algorithm is generally a good way to achieve less performance, not more" - Tom Lane. ------=_Part_1729_22160076.1123076498226 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Usually, on this situations, you've got a deadlock or alike.

What I do is to trace the pid of the vacuum (strace -p pid) and try to gues= s if the vacuum
is working or expecting some semaphore to get green (that would be a proble= m on the db side).

If this is the problem, a quick look into the procs running in the pg_Stat_= activity table will give
you an idea of the situation. Prolly, nothing was going on when you started= your vacuum
but somehow, a proc acquired a lock somewhere and the vacuum reached that s= omewhere causing
somekinda wait.

At least, this is very often in my scenery, using 7.4.3  and linux als= o.

Regards,
Guido

On 8/3/05, Scott Barvick <s= barvick@comcast.net> wrote:

I have an application that may add a couple million rows per day so Ivacuum nightly.  The tables never get to more than about 10 mil= lion rows
before I move off the interesting information to other media.&= nbsp; Somewhat
often, my vacuums don't complete as shown from this ps command (usually=
VACUUMs take a couple minutes)

4442 ?        R    9= 49:07 postgres: postgres tag 127.0.0.1(33420) VACUUM

and the CPU for postm= aster (hyperthreaded linux) will be in the high
90s.

The locks lo= ok like:
scouts=3D# select * from pg_locks;
relation | database | transaction |  pid  |       &nbs= p;   mode           |
grant= ed
----------+----------+-------------+-------+-------------------------= -+---------
          = |          |  &= nbsp; 12826125 | 11642 | ExclusiveLock          &n= bsp; |
t
    16839 |    17230 |             | 11642 | AccessShareLock          = |
t
    17251 |    17230 |             |  4442 | RowExclusiveLock         |
t
=     17251 |    17230 |             |  4442 | ShareUpdateExclusiveLock |
t
   &n= bsp;17246 |    17230 |             |  4442 | ShareUpdateExclusiveLock |
t
   &n= bsp;17246 |    17230 |             |  4442 | ShareUpdateExclusiveLock |
t
   &n= bsp;      |     &nbs= p;    |    12817402 |  4442 | ExclusiveLock          &n= bsp; |
t
(7 rows)


I all inserts and maintenance throu= gh JDBC and may have inserts going on
while a different java thread call= s the VACUUM command.

Any thoughts?  Can I recover without= dropping the server?

Thanks for any help,
Scott




------------------= ---------(end of broadcast)---------------------------
TIP 1: if posting= /reading through Usenet, please send an appropriate
   &n= bsp;   subscribe-nomail command to=20 majordomo@postgresql.org so= that your
       message can get through = to the mailing list cleanly


--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
perfo= rmance, not more" - Tom Lane. ------=_Part_1729_22160076.1123076498226--