pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Armand du Plessis <adp@bank.io>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Transaction ID wrap limit is log entries
Date: Mon, 20 May 2013 11:41:30 +0200
Message-ID: <CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com> (raw)
In-Reply-To: <CANf99sXOekH0m0TBE2uQJuUai0nRqiOE1mUwmoiyj8qTcL8EYQ@mail.gmail.com>
References: <CANf99sUyKcOpuacR_Lms4JpAfOV1KnskJM4EFNSAB3x3-=zCRw@mail.gmail.com>
	<CA+U5nMKfm0xrMgy_ND=1LKBrYUCC0bU_g_=V7m_Jpa8zDe=scg@mail.gmail.com>
	<CANf99sXOekH0m0TBE2uQJuUai0nRqiOE1mUwmoiyj8qTcL8EYQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>

On Sun, May 19, 2013 at 6:12 PM, Armand du Plessis <adp@bank.io> wrote:

> On Sun, May 19, 2013 at 6:08 PM, Simon Riggs <simon@2ndquadrant.com>wrote:
>
>> On 19 May 2013 13:54, Armand du Plessis <adp@bank.io> wrote:
>>
>> > We started seeing 1000s of messages like the ones below in our logs
>> starting
>> > last night. There's been no changes but performance has dropped
>> > significantly.
>>
>> > Any ideas what would be causing this?
>>
>> The database has not been issuing VACUUMs on some or all of the
>> relations, so the system needs to issue "anti-transactional
>> wraparound" VACUUMs.
>>
>
> Thanks, once I logged in with another user I saw there's one relation that
> is being vacuumed to prevent tx wraparound. The sheer number of those log
> entries made it seem like the autovacuum is in a loop.
>
>
>>
>> --
>>  Simon Riggs                   http://www.2ndQuadrant.com/
>>  PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
The autovacuum completed (after many hours) however it didn't seem to have
frozen any old pages as it just kicks off again right away with the same
reason (VACUUM ANALYZE public.messages (to prevent wraparound))

Which makes sense if I look at the settings but what bugs me is that the
max xid (as seen below) is still > autovacuum_freeze_max_age (1000000000)
when it finished. Should this not be reset because now we're stuck in a
really crippling loop.

postgres=# select max(age(datfrozenxid)) from pg_database;
    max
------------
 1043200153

Looking at that table's vacuum stats this morning, it finished an
autovacuum "2013-05-20 07:25:51.040327+00" and immediately started again at
"2013-05-20 07:26:45.448314+00 "

As an interim measure I'm going to up the autovacuum_freeze_max_age and
bite the bullet and restart postgres but something seems not right here.
I'm planning to partition this specific table in the next week so it
shouldn't be a problem running with the higher setting but ideally would
like to track down what is happening here.

Kind regards,

Armand

view thread (8+ messages)  latest in thread

Message-ID: <CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com>
Permalink:  ../CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com/
Also on:    postgresql.org/message-id/CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: adp@bank.io
  Subject: Re: Transaction ID wrap limit is log entries
  In-Reply-To: <CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com>
  References: <CANf99sUyKcOpuacR_Lms4JpAfOV1KnskJM4EFNSAB3x3-=zCRw@mail.gmail.com> <CA+U5nMKfm0xrMgy_ND=1LKBrYUCC0bU_g_=V7m_Jpa8zDe=scg@mail.gmail.com> <CANf99sXOekH0m0TBE2uQJuUai0nRqiOE1mUwmoiyj8qTcL8EYQ@mail.gmail.com> <CANf99sXZ9m4J6QXxecFJprUF0Njs+WNn1RTWsiYHT9To_bnBSA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox