Re: Another streaming replication question

Thank you Johannes for your feedback. It will be very useful.

On Tue, Nov 6, 2018 at 4:08 PM Johannes Truschnigg <johannes(at)truschnigg(dot)info>
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(at)truschnigg(dot)info )
>
> www: https://johannes.truschnigg.info/
> phone: +43 650 2 133337
> xmpp: johannes(at)truschnigg(dot)info
>
> Please do not bother me with HTML-email or attachments. Thank you.
>

--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
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 <http://www.autorevenue.com/>

<http://autobasedigital.net/marketing/DD12_sig.jpg>

--
<https://www.drivedominion.com/transform-your-vision/>