Thread: Throttling Streamming Replication
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. Thanks in advance, -- 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 -- 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, 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
On 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da SilvaNo, there is no such parameter. You might be able to send it through
<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.
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
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:
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 argumentsOn 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net> wrote:On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da SilvaNo, there is no such parameter. You might be able to send it through
<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.
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
-- 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> 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
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
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
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
On 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da SilvaNo, there is no such parameter. You might be able to send it through
<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.
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
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:
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 argumentsOn 25 January 2013 12:59, Magnus Hagander <magnus@hagander.net> wrote:On Fri, Jan 25, 2013 at 1:59 PM, Rodrigo Pereira da SilvaNo, there is no such parameter. You might be able to send it through
<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.
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
-- 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 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> 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