WAL bandwidth

On 05/22/2014 11:25 AM, Torsten F�rtsch wrote:

Hi,

time and again I need to build indexes. If they are big, that generates
a lot of WAL data that needs to be replicated to streaming replication
slaves. Usually these slaves don't lag behind noticeably. So, the
application often reads from them. Well, unless I build indexes and,
thus, create a huge amount of WAL in a short period of time.

What I'd like to have is something where I can set the max. bandwidth
with which the index generating backend may generate WAL data. I seem to
remember to have seen a discussion about something similar but can't
recall where.

Is there anything I can do about that problem in 9.3 or 9.4?

I already have a function that waits for the streaming slaves to catch
up. But that mitigates the problem only at a very crude level. I'd like
to be able to set that bandwidth to, say, 10mbit/sec. Then I can be sure
that all my replicas are fine. How long the index creation takes, does
not matter.

This does not appear the domain of PostgreSQL as much as the domain of
your OS and network layer.

Sincerely,

JD

Thanks,
Torsten

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

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

On 5/22/2014 11:46 AM, Joshua D. Drake wrote:

This does not appear the domain of PostgreSQL as much as the domain of
your OS and network layer.

I think he's asking for a throttle on create index, so OTHER WAL
activity would proceed at full speed, but the CREATE INDEX would somehow
constrain itself to not generating more than N blocks/second

the answer is, no, there's no such feature.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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

On Thu, May 22, 2014 at 11:46 AM, Joshua D. Drake <jd@commandprompt.com>wrote:

On 05/22/2014 11:25 AM, Torsten Förtsch wrote:

Hi,

time and again I need to build indexes. If they are big, that generates
a lot of WAL data that needs to be replicated to streaming replication
slaves. Usually these slaves don't lag behind noticeably. So, the
application often reads from them. Well, unless I build indexes and,
thus, create a huge amount of WAL in a short period of time.

Are these built CONCURRENTLY?

What I'd like to have is something where I can set the max. bandwidth
with which the index generating backend may generate WAL data. I seem to
remember to have seen a discussion about something similar but can't
recall where.

Is there anything I can do about that problem in 9.3 or 9.4?

I already have a function that waits for the streaming slaves to catch
up. But that mitigates the problem only at a very crude level. I'd like
to be able to set that bandwidth to, say, 10mbit/sec. Then I can be sure
that all my replicas are fine. How long the index creation takes, does
not matter.

This does not appear the domain of PostgreSQL as much as the domain of
your OS and network layer.

The OS and network have little choice but to process the WAL in the order
it is generated. If you want to throttle the generation of WAL by
background maintenance operations so they don't interfere with the
processing of WAL generated by bread-and-butter transaction processing,
that is something that only PostgreSQL can do.

Cheers,

Jeff

On 05/22/2014 12:04 PM, John R Pierce wrote:

On 5/22/2014 11:46 AM, Joshua D. Drake wrote:

This does not appear the domain of PostgreSQL as much as the domain of
your OS and network layer.

I think he's asking for a throttle on create index, so OTHER WAL
activity would proceed at full speed, but the CREATE INDEX would somehow
constrain itself to not generating more than N blocks/second

Yeah I was a bit obtuse in my answer. I didn't meant to be. IMO, the
problem is that he doesn't have enough Network IO. We don't want to
throttle PostgreSQL.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

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

On 22/05/14 21:05, Jeff Janes wrote:

time and again I need to build indexes. If they are big, that
generates
a lot of WAL data that needs to be replicated to streaming
replication
slaves. Usually these slaves don't lag behind noticeably. So, the
application often reads from them. Well, unless I build indexes and,
thus, create a huge amount of WAL in a short period of time.

Are these built CONCURRENTLY?

yes

What I'd like to have is something where I can set the max.
bandwidth
with which the index generating backend may generate WAL data. I
seem to
remember to have seen a discussion about something similar but can't
recall where.

Is there anything I can do about that problem in 9.3 or 9.4?

I already have a function that waits for the streaming slaves to
catch
up. But that mitigates the problem only at a very crude level.
I'd like
to be able to set that bandwidth to, say, 10mbit/sec. Then I can
be sure
that all my replicas are fine. How long the index creation
takes, does
not matter.

This does not appear the domain of PostgreSQL as much as the domain
of your OS and network layer.

The OS and network have little choice but to process the WAL in the
order it is generated. If you want to throttle the generation of WAL
by background maintenance operations so they don't interfere with the
processing of WAL generated by bread-and-butter transaction processing,
that is something that only PostgreSQL can do.

That's what I want, to throttle the rate at which WAL is generated by
maintenance operations.

I take it, there is no such thing by now. Would it be a useful addition?

I am not sure if I have the time to implement it. I have had a cursory
look at the code before, just to find out how things work, but never
changed something. What do you think, is it complicated to implement?

Torsten

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

On 5/22/2014 12:31 PM, Torsten Förtsch wrote:

That's what I want, to throttle the rate at which WAL is generated by
maintenance operations.

I take it, there is no such thing by now. Would it be a useful addition?

I am not sure if I have the time to implement it. I have had a cursory
look at the code before, just to find out how things work, but never
changed something. What do you think, is it complicated to implement?

I suspect you'd need to A) add some SET variable(s) to control this, and
B) have the CREATE INDEX code sleep() some number of milliseconds per N
generated blocks of index, this would be akin to what vacuum and
autovacuum do now.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

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

Torsten F�rtsch wrote:

That's what I want, to throttle the rate at which WAL is generated by
maintenance operations.

I take it, there is no such thing by now. Would it be a useful addition?

Uh. See this thread:
/messages/by-id/CA+U5nMLfxBgHQ1VLSeBHYEMjHXz_OHSkuFdU6_1quiGM0RNKEg@mail.gmail.com

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

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