Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42701=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XCqqt-00010Z-48
 for pgsql-admin@arkaria.postgresql.org; Thu, 31 Jul 2014 13:57:03 +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+M42701=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1XCqqs-00017x-6C
 for pgsql-admin@arkaria.postgresql.org; Thu, 31 Jul 2014 13:57:02 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1XCqqq-00017m-SR
 for pgsql-admin@postgresql.org; Thu, 31 Jul 2014 13:57:01 +0000
Received: from mail-yh0-x229.google.com ([2607:f8b0:4002:c01::229])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1XCqqm-0006m9-Gw
 for pgsql-admin@postgresql.org; Thu, 31 Jul 2014 13:56:59 +0000
Received: by mail-yh0-f41.google.com with SMTP id b6so1652628yha.0
 for <pgsql-admin@postgresql.org>; Thu, 31 Jul 2014 06:56:53 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=d5Y6sb+Uawm17GVMhptouoeYb/aSVkjM9Y1oZ3VsWcQ=;
 b=OSc1LpCPRjP+QP7790xZ2CTwEySAnwzbWz22hNahm9fTZuYDvPBtsJ0Zbhyf6VC3VL
 zcYGBAPRUZNCud9ioTvkoUFgiZalEzpCkaRM253eTp+lb/1Pyx/e9zCDTtuqDKobmdHX
 2z8MSIbWA0upJaZlxQCLw/sEbyILgqQ1rabxC/+QqpWipqPi43q1PGExNDn2r3ot4na1
 yg/5nSimqzWVM2zVlqiHcorb8bWKjLKLA3rmBSpsKSOtrZEKpzuvjwescN++HhQEzzX+
 7uizjltwRp+2vwrwRDAQYRXS1fSbILH8eihCkAXKGmIt55v94iidZk79/BCzBwYFJKMX
 tELg==
X-Received: by 10.236.20.14 with SMTP id o14mr4740712yho.190.1406815013525;
 Thu, 31 Jul 2014 06:56:53 -0700 (PDT)
Received: from [192.168.254.4] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id 45sm9094267yhj.32.2014.07.31.06.56.52
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 31 Jul 2014 06:56:53 -0700 (PDT)
References: <26e211945432a41a56dd900440f27e6d.squirrel@shrek.krogh.cc>
 <20140731134025.GB5475@eldon.alvh.no-ip.org>
Mime-Version: 1.0 (1.0)
In-Reply-To: <20140731134025.GB5475@eldon.alvh.no-ip.org>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-Id: <5A546F03-0769-4D4A-B29F-C06CF235F42A@gmail.com>
Cc: "jesper@krogh.cc" <jesper@krogh.cc>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
X-Mailer: iPad Mail (11D257)
From: jayknowsunix@gmail.com
Subject: Re: Autovacuum not keeping up. (PG 9.2.9)
Date: Thu, 31 Jul 2014 09:56:53 -0400
To: Alvaro Herrera <alvherre@2ndquadrant.com>
X-Pg-Spam-Score: -2.0 (--)
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



Sent from my iPad

> On Jul 31, 2014, at 9:40 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wr=
ote:
>=20
> jesper@krogh.cc wrote:
>> Hi.
>>=20
>> I have a large  database with a message queue table, that has high
>> activity. The database supports 1-300 client connection concurrently,
>> having transactions open in up to 30 minutes each.
>>=20
>> Recently I am seeing autuvacuum being issued, but it takes
>> ages to get through the message queue table, with strace showing waiting
>> for semop's for 10's to 100's of seconds.
>=20
> Do you have data on how relfrozenxid advances for that table?
>=20
> Vacuuming needs to grab a "cleanup lock" on each page it's going to
> vacuum, which is a special kind of lock that requires that no other
> process is even looking at the page at that moment (we call this "to
> have the page pinned"), which is even weaker than having a shared lock
> on the page.  If traffic to some pages is high, it might be difficult
> for vacuum to acquire this.
>=20
> Normally, vacuum doesn't break much sweat about this: if it cannot
> acquire the cleanup lock, it ignores the page, keeps calm and carries
> on.  But if it's a for-wraparound vacuuming, it will need to wait until
> it is able to acquire cleanup lock.
>=20
> I think one idea might be to try to manually vacuum the table once in a
> while with a reduced value of min_freeze_table_age.  This will cause a
> full table scan (i.e. cleanup lock for all pages is waited for), which
> decreases the "frozen xid age", which moves the need to do this again
> further in the future; so the autovacuum-invoked vacuums will be able to
> skip the pages on which it cannot get cleanup lock.
>=20
> Another idea is to increase min_freeze_table_age for the queue table
> through ALTER TABLE, the idea being that you can delay forced vacuuming
> of hot pages for long enough that they can wait until they have cooled
> off.  Default value is 150 million transactions, which you can raise
> tenfold and even higher.
>=20
> See
> http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-=
VACUUM-FREEZE-TABLE-AGE
>=20
>=20
> The other idea is that heap truncation is what's causing the problem,
> but AFAICS that uses conditional lock acquisition so you shouldn't be
> seeing stalls in semop().
>=20
> --=20
> =C3=81lvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>=20
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

You should probably consider, if you haven't done already, to partition thi=
s table. Without knowing what's adding new transactions, it sounds like you=
 could break up the table with a timestamp rule. That would permit autovacu=
um to do parts of the table much quicker, and only once.
-/
Jay

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