Thread: WAL bandwidth
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. Thanks, Torsten
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.
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
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?
This does not appear the domain of PostgreSQL as much as the domain of your OS and network layer.
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.
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.
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
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
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: http://www.postgresql.org/message-id/CA+U5nMLfxBgHQ1VLSeBHYEMjHXz_OHSkuFdU6_1quiGM0RNKEg@mail.gmail.com -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services