[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] high throughput 9.3, master/slave question
From:       Matthew Chambers <mchambers () wetafx ! co ! nz>
Date:       2014-02-28 21:54:48
Message-ID: 531105A8.30107 () wetafx ! co ! nz
[Download RAW message or body]

Hi,

I have a postgres server that is fairly high traffic. (about 5MB/second 
in writes to SSD backed Netapp NFS storage).  The outbound network 
traffic for this machine averages 61MB/second, but gets over 
250MB/second during peal times.  (includes data to slave + user requests)

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?

-Matt




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]