Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42136=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WAgCv-0003u4-86
 for pgsql-admin@arkaria.postgresql.org; Tue, 04 Feb 2014 13:38:33 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42136=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1WAgCu-00083T-L5
 for pgsql-admin@arkaria.postgresql.org; Tue, 04 Feb 2014 13:38:32 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jesper@krogh.cc>) id 1WAgCt-00083M-Hd
 for pgsql-admin@postgresql.org; Tue, 04 Feb 2014 13:38:31 +0000
Received: from 2605ds1-ynoe.3.fullrate.dk ([90.185.142.83]
 helo=shrek.krogh.cc)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jesper@krogh.cc>) id 1WAgCm-00086Y-8q
 for pgsql-admin@postgresql.org; Tue, 04 Feb 2014 13:38:30 +0000
Received: from localhost (localhost [127.0.0.1])
 by shrek.krogh.cc (Postfix) with ESMTP id A1C8E1D6A6D
 for <pgsql-admin@postgresql.org>; Tue,  4 Feb 2014 14:38:21 +0100 (CET)
X-Virus-Scanned: Debian amavisd-new at shrek.krogh.cc
Received: from shrek.krogh.cc ([127.0.0.1])
 by localhost (shrek.krogh.cc [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id IKR1bJ2QsSkd for <pgsql-admin@postgresql.org>;
 Tue,  4 Feb 2014 14:38:15 +0100 (CET)
Received: from shrek.krogh.cc (localhost [127.0.0.1])
 by shrek.krogh.cc (Postfix) with ESMTP id 491A81D69AB
 for <pgsql-admin@postgresql.org>; Tue,  4 Feb 2014 14:38:15 +0100 (CET)
Received: from 94.101.220.16 (SquirrelMail authenticated user jesper@krogh.cc)
 by shrek.krogh.cc with HTTP; Tue, 4 Feb 2014 14:38:15 +0100 (CET)
Message-ID: <f279e42bf733a46d8031b42a8dbcdc15.squirrel@shrek.krogh.cc>
Date: Tue, 4 Feb 2014 14:38:15 +0100 (CET)
Subject: Autovacuum progressing slow (with high activity on the system).
From: jesper@krogh.cc
To: pgsql-admin@postgresql.org
User-Agent: SquirrelMail/1.4.15
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
Importance: Normal
X-Pg-Spam-Score: 0.8 (/)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi.

The database is a large database (3TB+) and I have 200+ backend
processes polling a message queuetable for work to do. On this message
queue table, when activity rises autovacuum fails to make progress and I
cannot get what is actually holding it back:

Stracing the process gives this pattern: (strace -T -p <pid of autovacuum>):
....

semop(18317336, {{1, 1, 0}}, 1)         = 0 <0.000021>
semop(19562558, {{10, -1, 0}}, 1)       = 0 <141.819414>
lseek(156, 566099968, SEEK_SET)         = 566099968 <0.000015>
read(156, "\233\230\0\0`\226\347\332\1\0\0\0\260\3\220\21\360\37\4
\0\0\0\0\340\237 \0\220\221 \0"..., 8192) = 8192 <0.000027>
...
read(156, "\234\230\0\0\210hx\240\1\0\0\0\350\4\260\f\360\37\4
\0\0\0\0\340\237 \0\260\214 \0"..., 8192) = 8192 <0.000018>
semop(19562558, {{10, -1, 0}}, 1)       = 0 <32.707159>
lseek(30, 706174976, SEEK_SET)          = 706174976 <0.000025>
write(30, "\235\230\0\0\240\353\25v\1\0\0\0\30\0\370\37\370\37\4 \
...

In other words .. large "sleeps" on semops on this semop(19562558, {{10,
-1, 0}}, 1)" .. thus no progress while waiting for this lock. If I pick
the
pg_locks view at the same time, it does not show that is is waiting for
anything.

Can I somehow get deeper into what competing activity that causes
autovacuum to fail making progress?

Thanks.

Jesper



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin