Backup throttling

On Wed, Jul 24, 2013 at 4:20 PM, Antonin Houska
<antonin.houska@gmail.com> wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Interesting. Please add this patch to the next commitfeat.
https://commitfest.postgresql.org/action/commitfest_view?id=19

Regards,

--
Fujii Masao

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

On Wed, 24 Jul 2013 09:20:52 +0200
Antonin Houska <antonin.houska@gmail.com> wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running
server. Feedback is appreciated.

// Antonin Houska (Tony)

Hi,

That is a really nice feature. I took a first look at your patch and
some empty lines you added (e.g. line 60 your patch). Can you remove
them?

Why did you move localGetCurrentTimestamp() into streamutil.c? Is
sys/time.h still needed in receivelog.c after the move?

I will try your patch later today to see, if it works.

regards,

Stefan Radomski

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

Gibheer escribi�:

Why did you move localGetCurrentTimestamp() into streamutil.c? Is
sys/time.h still needed in receivelog.c after the move?

I think we should have GetCurrentTimestamp() in src/common; there are
way too many copies of that functionality now. I looked into this
awhile back, but eviscerating the datetime.c/timestamp.c code out of the
backend proved much messier than I anticipated.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On 07/31/2013 07:13 AM, Gibheer wrote:

Hi,

That is a really nice feature.

I don't pretend it's my idea, I just coded it. My boss proposed the
feature as such :-)

I took a first look at your patch and some empty lines you added (e.g. line 60 your patch). Can you remove
them?

Sure, will do in the next version.

Why did you move localGetCurrentTimestamp() into streamutil.c? Is sys/time.h still needed in receivelog.c after the move?

Because both receivelog.c and pg_basebackup.c need it now. I thought I
could move localTimestampDifference() and
localTimestampDifferenceExceeds() as well for the sake of consistency
(these are actually utilities too) but I didn't get convinced enough
that the feature alone justifies such a change.

As mentioned in
/messages/by-id/20130731173624.GX14652@eldon.alvh.no-ip.org
these functions ideally shouldn't have separate implementation at all.
However the problem is that pg_basebackup is not linked to the backend.

You're right about sys/time.h, it's included via via streamutil.h. I'll
fix that too.

I will try your patch later today to see, if it works.

Whenever you have time. Thanks!

// Tony

On Wed, 31 Jul 2013 22:50:19 +0200
Antonin Houska <antonin.houska@gmail.com> wrote:

On 07/31/2013 07:13 AM, Gibheer wrote:

Hi,

That is a really nice feature.

I don't pretend it's my idea, I just coded it. My boss proposed the
feature as such :-)

I took a first look at your patch and some empty lines you added
(e.g. line 60 your patch). Can you remove them?

Sure, will do in the next version.

Why did you move localGetCurrentTimestamp() into streamutil.c? Is
sys/time.h still needed in receivelog.c after the move?

Because both receivelog.c and pg_basebackup.c need it now. I thought
I could move localTimestampDifference() and
localTimestampDifferenceExceeds() as well for the sake of consistency
(these are actually utilities too) but I didn't get convinced enough
that the feature alone justifies such a change.

As mentioned in
/messages/by-id/20130731173624.GX14652@eldon.alvh.no-ip.org
these functions ideally shouldn't have separate implementation at
all. However the problem is that pg_basebackup is not linked to the
backend.

You're right about sys/time.h, it's included via via streamutil.h.
I'll fix that too.

I will try your patch later today to see, if it works.

Whenever you have time. Thanks!

// Tony

Hi,

I got around to test your patch and it works. I've build a small script
for others to test it easily. You can tell it with ROOTDIR and BASEDIR
environment variables where to look for the binaries and where to put
the test directories.

There is only one small thing I hit, namely the error message when the
limit is too small. It was like

transfer rate out of range '10k'

but it does not mention what the actual range is.

Maybe a message like

transfer rate of 10k is too small. Lower limit is 32k.

or

transfer rate has to be between 32k and 1GB, was 10k.

would be better as they mention what the actual limit is. That avoids
that people have to look up the limits in the manual.
We should also add the current limits to the documentation.

regards,

Stefan Radomski

2013-07-31 22:50 keltez�ssel, Antonin Houska �rta:

On 07/31/2013 07:13 AM, Gibheer wrote:

Hi,

That is a really nice feature.

I don't pretend it's my idea, I just coded it. My boss proposed the feature as such :-)

I took a first look at your patch and some empty lines you added (e.g. line 60 your patch). Can you remove
them?

Sure, will do in the next version.

Why did you move localGetCurrentTimestamp() into streamutil.c? Is sys/time.h still needed in receivelog.c after the move?

Because both receivelog.c and pg_basebackup.c need it now. I thought I could move
localTimestampDifference() and localTimestampDifferenceExceeds() as well for the sake of
consistency (these are actually utilities too) but I didn't get convinced enough that
the feature alone justifies such a change.

As mentioned in
/messages/by-id/20130731173624.GX14652@eldon.alvh.no-ip.org these
functions ideally shouldn't have separate implementation at all. However the problem is
that pg_basebackup is not linked to the backend.

Have you considered the src/common directory?

You're right about sys/time.h, it's included via via streamutil.h. I'll fix that too.

I will try your patch later today to see, if it works.

Whenever you have time. Thanks!

// Tony

Best regards,
Zolt�n B�sz�rm�nyi

--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

2013-08-19 19:20 keltez�ssel, Andres Freund �rta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Greetings,

Andres Freund

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Maybe throttling the walsender is not a good idea, it can lead
to DoS via disk space shortage.

Best regards,
Zolt�n B�sz�rm�nyi

--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

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

On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltezéssel, Andres Freund írta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Seems fine to me. Under the premise that the idea is decided to be
worthwile to be integrated. Which I am not yet convinced of.

Maybe throttling the walsender is not a good idea, it can lead
to DoS via disk space shortage.

Not in a measurably different way than receiver side throttling?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

2013-08-19 21:11 keltez�ssel, Andres Freund �rta:

On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltez�ssel, Andres Freund �rta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Seems fine to me. Under the premise that the idea is decided to be
worthwile to be integrated. Which I am not yet convinced of.

Maybe throttling the walsender is not a good idea, it can lead
to DoS via disk space shortage.

Not in a measurably different way than receiver side throttling?

No, but that's not what I meant.

START_BACKUP has to deal with big data but it finishes after some time.
But pg_receivexlog may sit there indefinitely...

Best regards,
Zolt�n B�sz�rm�nyi

--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

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

On 19.08.2013 21:15, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltez�ssel, Andres Freund �rta:

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Throttling in the client seems much better to me. TCP is designed to
handle a slow client.

Maybe throttling the walsender is not a good idea, it can lead
to DoS via disk space shortage.

If a client can initiate a backup and/or streaming replication, he can
already do much more damage than a DoS via out of disk space. And a
nothing stops even a non-privileged user from causing an out of disk
space situation anyway. IOW that's a non-issue.

- Heikki

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

2013-08-20 08:37 keltez�ssel, Heikki Linnakangas �rta:

On 19.08.2013 21:15, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltez�ssel, Andres Freund �rta:

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Throttling in the client seems much better to me. TCP is designed to handle a slow client.

Maybe throttling the walsender is not a good idea, it can lead
to DoS via disk space shortage.

If a client can initiate a backup and/or streaming replication, he can already do much
more damage than a DoS via out of disk space. And a nothing stops even a non-privileged
user from causing an out of disk space situation anyway. IOW that's a non-issue.

I got to the same conclusion this morning, but because of wal_keep_segments.

Best regards,
Zolt�n B�sz�rm�nyi

- Heikki

--
----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
Gr�hrm�hlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

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

On Aug 19, 2013, at 9:11 PM, Andres Freund wrote:

On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltezéssel, Andres Freund írta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Seems fine to me. Under the premise that the idea is decided to be
worthwile to be integrated. Which I am not yet convinced of.

i think there is a lot of value for this one. the scenario we had a couple of times is pretty simple:
just assume a weak server - maybe just one disk or two - and a slave.
master and slave are connected via a 1 GB network.
pg_basebackup will fetch data full speed basically putting those lonely disks out of business.
we actually had a case where a client asked if "PostgreSQL is locked during base backup". of
course it was just disk wait caused by a full speed pg_basebackup.

regarding the client side implementation: we have chosen this way because it is less invasive.
i cannot see a reason to do this on the server side because we won't have 10
pg_basebackup-style tools making use of this feature anyway.

of course, if you got 20 disk and a 1 gbit network this is useless - but many people don't have that.

regards,

hans-jürgen schönig

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

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

On 2013-08-21 08:10:42 +0200, PostgreSQL - Hans-Jürgen Schönig wrote:

On Aug 19, 2013, at 9:11 PM, Andres Freund wrote:

On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltezéssel, Andres Freund írta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Seems fine to me. Under the premise that the idea is decided to be
worthwile to be integrated. Which I am not yet convinced of.

i think there is a lot of value for this one. the scenario we had a couple of times is pretty simple:
just assume a weak server - maybe just one disk or two - and a slave.
master and slave are connected via a 1 GB network.
pg_basebackup will fetch data full speed basically putting those lonely disks out of business.
we actually had a case where a client asked if "PostgreSQL is locked during base backup". of
course it was just disk wait caused by a full speed pg_basebackup.

regarding the client side implementation: we have chosen this way because it is less invasive.
i cannot see a reason to do this on the server side because we won't have 10
pg_basebackup-style tools making use of this feature anyway.

The problem is that receiver side throttling over TCP doesn't always
work all that nicely unless you have a low rate of transfer and/or very
low latency . Quite often you will have OS buffers/the TCP Window being
filled in bursts where the sender sends at max capacity and then a
period where nothing happens on the sender. That's often not what you
want when you need to throttle.

Besides, I can see some value in e.g. normal streaming replication also
being rate limited...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On Aug 21, 2013, at 10:57 AM, Andres Freund wrote:

On 2013-08-21 08:10:42 +0200, PostgreSQL - Hans-Jürgen Schönig wrote:

On Aug 19, 2013, at 9:11 PM, Andres Freund wrote:

On 2013-08-19 20:15:51 +0200, Boszormenyi Zoltan wrote:

2013-08-19 19:20 keltezéssel, Andres Freund írta:

Hi,

On 2013-07-24 09:20:52 +0200, Antonin Houska wrote:

Hello,
the purpose of this patch is to limit impact of pg_backup on running server.
Feedback is appreciated.

Based on a quick look it seems like you're throttling on the receiving
side. Is that a good idea? Especially over longer latency links, TCP
buffering will reduce the effect on the sender side considerably.

Throttling on the sender side requires extending the syntax of
BASE_BACKUP and maybe START_REPLICATION so both can be
throttled but throttling is still initiated by the receiver side.

Seems fine to me. Under the premise that the idea is decided to be
worthwile to be integrated. Which I am not yet convinced of.

i think there is a lot of value for this one. the scenario we had a couple of times is pretty simple:
just assume a weak server - maybe just one disk or two - and a slave.
master and slave are connected via a 1 GB network.
pg_basebackup will fetch data full speed basically putting those lonely disks out of business.
we actually had a case where a client asked if "PostgreSQL is locked during base backup". of
course it was just disk wait caused by a full speed pg_basebackup.

regarding the client side implementation: we have chosen this way because it is less invasive.
i cannot see a reason to do this on the server side because we won't have 10
pg_basebackup-style tools making use of this feature anyway.

The problem is that receiver side throttling over TCP doesn't always
work all that nicely unless you have a low rate of transfer and/or very
low latency . Quite often you will have OS buffers/the TCP Window being
filled in bursts where the sender sends at max capacity and then a
period where nothing happens on the sender. That's often not what you
want when you need to throttle.

Besides, I can see some value in e.g. normal streaming replication also
being rate limited...

what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest.

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

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

On 08/22/2013 01:39 PM, PostgreSQL - Hans-J�rgen Sch�nig wrote:

what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest.

I tend to agree. If anything we're likely to want the reverse - the
ability to throttle WAL *generation* on the master so streaming can keep up.

I see a lot of value in throttling base backup transfer rates. It's
something PgBarman does per-tablespace using rsync at the moment, but
it'd be nice if it available as an option possible over the streaming
replication protocol via pg_basebackup so it was easier for people to
use ad-hoc and without all the shell access wrangling.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On 2013-08-22 07:39:41 +0200, PostgreSQL - Hans-Jürgen Schönig wrote:

regarding the client side implementation: we have chosen this way because it is less invasive.
i cannot see a reason to do this on the server side because we won't have 10
pg_basebackup-style tools making use of this feature anyway.

The problem is that receiver side throttling over TCP doesn't always
work all that nicely unless you have a low rate of transfer and/or very
low latency . Quite often you will have OS buffers/the TCP Window being
filled in bursts where the sender sends at max capacity and then a
period where nothing happens on the sender. That's often not what you
want when you need to throttle.

Besides, I can see some value in e.g. normal streaming replication also
being rate limited...

what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest.

It's not an unreasonable goal if you have several streaming replicas
with only some of them being synchronous replicas. Also, analytics
replicas that need to catchup don't really need priority over local
operations.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

On Wed, 2013-07-24 at 09:20 +0200, Antonin Houska wrote:

the purpose of this patch is to limit impact of pg_backup on running
server. Feedback is appreciated.

Please replace the tabs in the SGML files with spaces.

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

On 08/22/2013 03:33 PM, Craig Ringer wrote:

On 08/22/2013 01:39 PM, PostgreSQL - Hans-J�rgen Sch�nig wrote:

what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest.

I tend to agree. If anything we're likely to want the reverse - the
ability to throttle WAL *generation* on the master so streaming can keep up.

(I assume you mean WAL *sending* rather than *generation*.)

I don't think we want to throttle WAL sending/receiving at all. The WAL
senders should always keep up with the amount of WAL generated. If the
rate of WAL sending is restricted, then the pg_basebackup (or another
client application) might never catch up.

Also, IMO, pg_basebackup starts at the current WAL segment. Thus the
unthrottled WAL transfer shouldn't cause significant additional load,
such as reading many old WAL segments from the master's disk.

I see a lot of value in throttling base backup transfer rates. It's
something PgBarman does per-tablespace using rsync at the moment, but
it'd be nice if it available as an option possible over the streaming
replication protocol via pg_basebackup so it was easier for people to
use ad-hoc and without all the shell access wrangling.

(Possibly huge) DATA directory (tablespaces, etc.) is the actual purpose
of this patch. This is the additional load that pg_basebackup imposes on
the master. As pointed out elsewhere in the thread, the client-side
throttling was chosen because it seemed to be less invasive. But the
discussion (including this your comment) keeps me no longer convinced
that it's the best way. I'll reconsider things.

// Antonin Houska (Tony)

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