Throttling Streamming Replication

On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da Silva
<rodrigo@paripassu.com.br> wrote:

Hi Guys,

We are having a problem with our infrastructure provider because the network
traffic between master and slave server is reaching more than 30k packages
per second(SLA says 20k/second).
Is there any way to throttle the streamming replication? I meant, any
parameter that I set the max number of megabytes sent to standby server per
second?
I didn't have any luck looking at the postgresql streamming replication
documentation. There is the wal_sender_delay, but I suppose that if I set
more than 1 second, it could accumulate a bunch of wal files and send it at
once. So, it wouldn't work.

No, there is no such parameter. You might be able to send it through
some proxy that slows it down, but there is no builtin support to do
that.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

Can you not change your method of streaming replication. What is your
archive command, are you using rsync as you can control the bandwidth limit?

We use: archive_command = 'test ! -f
/opt/postgres/remote_pgsql/wal_archive/%f && rsync -az %p
/opt/postgres/remote_pgsql/wal_archive/%f'

According to the link below you can add --bwlimit=1000 to the rsync
arguments

http://scratching.psybermonkey.net/2009/03/rsync-transfer-rate-limit-aka-it-down.html

On 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net> wrote:

That's a good idea, but we are using windows copy command, not sure if
it has something similar. Anyway, we just talked to infrastructure
provider and they figured out it was a problem with their monitoring
system, so we are not reaching 30k packages actually.

Thanks a lot for the replies,

Em 25/01/2013 12:18, Cliff de Carteret escreveu:

Can you not change your method of streaming replication. What is your
archive command, are you using rsync as you can control the bandwidth
limit?

We use: archive_command = 'test ! -f
/opt/postgres/remote_pgsql/wal_archive/%f && rsync -az %p
/opt/postgres/remote_pgsql/wal_archive/%f'

According to the link below you can add --bwlimit=1000 to the rsync
arguments

http://scratching.psybermonkey.net/2009/03/rsync-transfer-rate-limit-aka-it-down.html

On 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net
<mailto:magnus@hagander.net>> wrote:

On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da Silva
<rodrigo@paripassu.com.br <mailto:rodrigo@paripassu.com.br>> wrote:

Hi Guys,

We are having a problem with our infrastructure provider because

the network

traffic between master and slave server is reaching more than

30k packages

per second(SLA says 20k/second).
Is there any way to throttle the streamming replication? I

meant, any

parameter that I set the max number of megabytes sent to standby

server per

second?
I didn't have any luck looking at the postgresql streamming

replication

documentation. There is the wal_sender_delay, but I suppose that

if I set

more than 1 second, it could accumulate a bunch of wal files and

send it at

once. So, it wouldn't work.

No, there is no such parameter. You might be able to send it through
some proxy that slows it down, but there is no builtin support to do
that.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Rodrigo Pereira da Silva
rodrigo@paripassu.com.br
http://www.paripassu.com.br
Rua Coronel Luis Caldeira, nº 67, Bloco 1, Sala 3A
88034-110 . Florianópolis . SC
Tel. (48) 3207-5755

On Fri, Jan 25, 2013 at 5:59 AM, Rodrigo Pereira da Silva
<rodrigo@paripassu.com.br> wrote:

Hi Guys,

We are having a problem with our infrastructure provider because the network
traffic between master and slave server is reaching more than 30k packages
per second(SLA says 20k/second).
Is there any way to throttle the streamming replication? I meant, any
parameter that I set the max number of megabytes sent to standby server per
second?
I didn't have any luck looking at the postgresql streamming replication
documentation. There is the wal_sender_delay, but I suppose that if I set
more than 1 second, it could accumulate a bunch of wal files and send it at
once. So, it wouldn't work.

Assuming these machines are near each other the answer is simple,
install a NIC in each one and give them their own private connection
to each other.

If they're distant from each other, stop using streaming replication
and use log archiving with mass transfer every x minutes or hours.

If they are on virtual machines then there's the problem right there.

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

On Friday, January 25, 2013 at 23:59, Rodrigo Pereira da Silva wrote:

We are having a problem with our infrastructure provider because the
network traffic between master and slave server is reaching more than
30k packages per second(SLA says 20k/second).

I note the later post where the infrastructure provider has
determined it is their problem, however you might well benefit
from this as a warning and trim your traffic anyway.

Is there any way to throttle the streamming replication? I meant, any
parameter that I set the max number of megabytes sent to standby
server per second?

I am guessing (i.e., no knowledge of your setup) you would
prefer to not have a lot of delay between the master and slave
servers. This is especially so if the slave has to become master
since people mostly want that transition to appear seamless to
the outside world. So increasing time settings may not be
suitable even if it did reduce bandwidth.

It is possible to get serious bandwidth savings without
increasing latency.

ref: <http://permalink.gmane.org/gmane.comp.db.postgresql.general/164874&gt;

If you put each WAL file through pg_clearxlogtail (it zeros out
the unused part of the fixed-length WAL file) then
compress-transmit-decompress the result you will get much better
use of the available bandwidth between master and slave servers.
Specifically you will only be sending information that is
needed, and smaller data chunks are faster data chunks.

Regards
Gavan Schneider

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