From: Kevin Grittner <kgrittn@ymail.com>
To: Drazen Kacar <drazen.kacar@oradian.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Query cancellation on hot standby because of buffer pins
Date: Fri, 20 Feb 2015 22:48:09 +0000 (UTC)
Message-ID: <1328517318.859752.1424472489522.JavaMail.yahoo@mail.yahoo.com> (raw)
In-Reply-To: <CAFxrd3tm_1_4UUL4ULnPzB04JJbvM1FdHpOMuF3fC7dW_8kJ2Q@mail.gmail.com>
References: <CAFxrd3tm_1_4UUL4ULnPzB04JJbvM1FdHpOMuF3fC7dW_8kJ2Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Drazen Kacar <drazen.kacar@oradian.com> wrote:
> I'm seeing query cancellation on hot standby (Postgres 9.3.4,
> configured with hot_standby_feedback=on) because of buffer pins
> being held for too long.
>
> Database message looks like this:
>
> ERROR: canceling statement due to conflict with recovery
> DETAIL: User was holding shared buffer pin for too long.
> STATEMENT: ...
It would be helpful to see what that statement was that you
replaced with the ellipsis.
I you don't already have log_autovacuum_min_duration set to 0 (or
something very close to it) on the primary, I recommend you set
that; it would be useful to see if this corresponds to a vacuum on
the table.
What is your maintenance_work_mem setting on the primary? Does
increasing it change the behavior? (You may want to jack that up
really high for diagnostic purposes.)
If you can associate the problem with a particular table, seeing
the definition of that table wouldn't hurt, either.
Oh, and please update to 9.3.6; there are some nasty bugs fixed.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, drazen.kacar@oradian.com
Subject: Re: Query cancellation on hot standby because of buffer pins
In-Reply-To: <1328517318.859752.1424472489522.JavaMail.yahoo@mail.yahoo.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