Thank you Johannes for your feedback. It will be very useful.
On Tue, Nov 6, 2018 at 4:08 PM Johannes Truschnigg <johannes@xxxxxxxxxxxxxxx> wrote:
Hi Mark,
On Tue, Nov 06, 2018 at 12:13:18PM -0500, Mark Steben wrote:
> Good morning,
>
> Thank you all for your responses to my questions about streaming/cascading
> replication. I have them ironed out and running on three postgres 9.4
> sandbox servers.
> My question is not 'how to ' but more 'best practices'. We currently have
> hot standby non-streaming replication with one master serving two standbys
> in parallel. We wish to convert
> to streaming replication. My concern is that the logshipping load can be
> quite high - up to 40 16MB logs per minute (during off-hours maintenance).
We use streaming replication ("plain" TCP over a 10G Ethernet link) and have
double-digit WAL segments per second at times - it's no problem for moderately
powerful, contemporary hardware. Using TLS would be fine, too, if you use a
fast stream cipher.
> In our current hot standby
> environment the logs can queue up on the master side during this period -
> eventually, during times of lower load they do catch up. So, a couple
> questions:
> 1. Can streaming replication work in this high-load situation?
Sure (depending on your hardware/environment and your current load, of
course).
However, even if it doesn't, it won't make things worse in case you still
archive WAL and/or set up a replication slot for your standby(s) - a secondary
will transparently switch back to applying WAL from the archive whenever
streaming replication is interrupted (e.g. due to too much lag having
accumulated, and too little WAL kept around on the master - in the absence of
replication slots, this can happen), and switch back to streaming once it has
done so (and streaming is available).
> 2. What, if anything, can I do to make it work better? Perhaps convert
> to cascade? Master -->standby1 ---> standby2
I guess it depends on what your (perceived?) bottleneck lies - is it your
network's bandwith, your nodes' I/O capabilities, or maybe even mere CPU
saturation that makes your setup work "not good enough" (you should qantify
that, and think hard about how, and by how much, you want to improve)?
The latter would be the hardest to solve (i.e. "get more powerful CPUs", or
maybe "review your current CPUs' max. turbo frequency/power envelope"), whilst
you could - if you were to upgrade to 9.5 or better - use WAL compression to
potentially alleviate the former with a trivial config change.
--
with best regards:
- Johannes Truschnigg ( johannes@xxxxxxxxxxxxxxx )
www: https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp: johannes@xxxxxxxxxxxxxxx
Please do not bother me with HTML-email or attachments. Thank you.
Mark Steben
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
Database Administrator
@utoRevenue | Autobase
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com
