Re: high throughput 9.3, master/slave question

From: bricklen <bricklen(at)gmail(dot)com>
To: Matthew Chambers <mchambers(at)wetafx(dot)co(dot)nz>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: high throughput 9.3, master/slave question
Date: 2014-03-01 16:08:23
Message-ID: CAGrpgQ-v-9+KZBW6qvNzEK4Nuq9DhooZZE1GtqP=kRC+1bcsDg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Feb 28, 2014 at 1:54 PM, Matthew Chambers <mchambers(at)wetafx(dot)co(dot)nz>wrote:

>
> Initially, I had my application servers using the slave for short, read
> only queries, but this turned out to be highly unstable. The slave would
> start refusing connections, and the logs would fill with:
>
> ERROR: canceling statement due to conflict with recovery
>
> I've tried these 2 settings:
>
> max_standby_archive_delay = -1
> max_standby_streaming_delay = -1
>
> But then I starting getting these:
> DETAIL: User transaction caused buffer deadlock with recovery.
>
> Read requests come in at anywhere between 200 and 1000/second.
>
> I was wondering if there is some combination of configuration settings
> that would safely let me use the slave for read only queries?
>

Have you tried setting max_standby_archive|streaming_delay to several
minutes (or whatever makes sense for your typical query durations), rather
than disabling those settings with -1?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Nolan 2014-03-01 16:13:19 Re: Moving data from M$ JetDB file to Postgres on Linux
Previous Message Rich Shepard 2014-03-01 15:55:07 Re: Moving data from M$ JetDB file to Postgres on Linux