high throughput 9.3, master/slave question
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
On Fri, Feb 28, 2014 at 1:54 PM, Matthew Chambers <mchambers@wetafx.co.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 = -1But 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?
On 02/03/14 05:08, bricklen wrote:
On Fri, Feb 28, 2014 at 1:54 PM, Matthew Chambers
<mchambers@wetafx.co.nz <mailto:mchambers@wetafx.co.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 = -1But 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?
No, I have not tired this, but I'll give it a go. Is there anyone doing
this that has a configuration I can check out? Is this even possible to do?
-Matt