Обсуждение: maintenance memory vs autovac
Would it make sense to be able to configure maintenance_work_mem specifically for the autovacuum processes? Given that there can be a number of them, it might be good to be able to have one default for all *other* processes, and a separate one from the ones kicked off by autovac? //Magnus
Seems it would make more sense to just divide maintenance_work_mem by the number of workers for autovacuum. This sounds familiar. Didn't we already decide to do this once? One concern I have about this is people asking "how come when I runvacuum manually it takes x minutes but when autovacuum runs it it tale 5x minutes?" greg On 2 Dec 2008, at 01:38 PM, Magnus Hagander <magnus@hagander.net> wrote: > Would it make sense to be able to configure maintenance_work_mem > specifically for the autovacuum processes? Given that there can be a > number of them, it might be good to be able to have one default for > all > *other* processes, and a separate one from the ones kicked off by > autovac? > > //Magnus > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
Greg Stark wrote: > Seems it would make more sense to just divide maintenance_work_mem by > the number of workers for autovacuum. While that would be a solution for some cases, it is far from certain that's what you'd actually want. > This sounds familiar. Didn't we already decide to do this once? Could be my google-fu is off today... > One concern I have about this is people asking "how come when I > runvacuum manually it takes x minutes but when autovacuum runs it it > tale 5x minutes?" As long as the default is the same, people would get at least an initial clue that it might have something to do with them changing a configuration parameter... //Magnus > On 2 Dec 2008, at 01:38 PM, Magnus Hagander <magnus@hagander.net> wrote: > >> Would it make sense to be able to configure maintenance_work_mem >> specifically for the autovacuum processes? Given that there can be a >> number of them, it might be good to be able to have one default for all >> *other* processes, and a separate one from the ones kicked off by >> autovac? >> >> //Magnus >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers
Magnus Hagander <magnus@hagander.net> writes:
> Greg Stark wrote:
>> One concern I have about this is people asking "how come when I
>> runvacuum manually it takes x minutes but when autovacuum runs it it
>> tale 5x minutes?"
> As long as the default is the same, people would get at least an initial
> clue that it might have something to do with them changing a
> configuration parameter...
It seems like mostly a confusion-generator to me. Is there any actual
evidence that autovac should use a different maintenance_work_mem than
other processes?
regards, tom lane
On Wed, Dec 3, 2008 at 2:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > It seems like mostly a confusion-generator to me. Is there any actual > evidence that autovac should use a different maintenance_work_mem than > other processes? IMHO, the point is that we were used to consider the maintenance_work_mem as a "one process at a time" thing. Even if it's not really true, we usually didn't do maintenance task on a concurrent basis. The autovacuum workers change that and make it a default behaviour (as we can have 3*maintenance_work_mem by default). From my point of view, the best solution would be to share the maintenance_work_mem amongst all the workers but I suppose it's not technically possible. -- Guillaume
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Greg Stark wrote: >>> One concern I have about this is people asking "how come when I >>> runvacuum manually it takes x minutes but when autovacuum runs it it >>> tale 5x minutes?" > >> As long as the default is the same, people would get at least an initial >> clue that it might have something to do with them changing a >> configuration parameter... > > It seems like mostly a confusion-generator to me. Is there any actual > evidence that autovac should use a different maintenance_work_mem than > other processes? The use-case that made me think of that is one with lots of autovac workers in a system with lots of small tables in different databases. Turns out I read the documentation for autovac wrong. I understood that if I wanted it to look at 1000 databases at once, I needed autovac_workers at 1000. Talked a bit offlist with Alvaro and realized that's not what it is, but that the documentation is a bit unclear on that - will work on fixing that. Which means there's probably no real use-case for "lots of autovac workers that each needs only a little maint_work_mem", in which case having such an extra parameter would become unnecessary. //Magnus
Guillaume Smet wrote: > On Wed, Dec 3, 2008 at 2:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> It seems like mostly a confusion-generator to me. Is there any actual >> evidence that autovac should use a different maintenance_work_mem than >> other processes? > > IMHO, the point is that we were used to consider the > maintenance_work_mem as a "one process at a time" thing. Even if it's > not really true, we usually didn't do maintenance task on a concurrent > basis. > The autovacuum workers change that and make it a default behaviour (as > we can have 3*maintenance_work_mem by default). It's still one per process, it's just that autovac uses more than one process. It's probably worthwhile to add a note about the effects of autovacuum around the documentation of maintenance_work_mem, though. //Magnus
On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <magnus@hagander.net> wrote: >> The autovacuum workers change that and make it a default behaviour (as >> we can have 3*maintenance_work_mem by default). > > It's still one per process, it's just that autovac uses more than one > process. I agree. What I implied is that by default you have 3 autovacuum workers so the behaviour has changed, even if it didn't change in a technical way. > It's probably worthwhile to add a note about the effects of > autovacuum around the documentation of maintenance_work_mem, though. +1 A lot of people set maintenance_work_mem quite high because of the old behaviour. -- Guillaume
"Guillaume Smet" <guillaume.smet@gmail.com> writes: > On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <magnus@hagander.net> wrote: >> It's probably worthwhile to add a note about the effects of >> autovacuum around the documentation of maintenance_work_mem, though. > > +1 > A lot of people set maintenance_work_mem quite high because of the old > behaviour. The high level view is that an admin will expect to be able to allocate all the memory on his machine (at least all the memory he expects Postgres to use) as something like: shared_buffers and sundry shared mem+ max_connections*work_mem+ maintenance_work_mem+ filesystem cache (Yes, "max_connections" isn't quite right there but that's the general idea) If you have 1G of ram and allocate 200M of shared buffers, 1M of work_mem of which you don't expect more than a hundred concurrent allocations, and want about half your ram set aside for filesystem cache you would be quite reasonable to expect to have about 256M to play with for maintenance_work_me -- which in my experience is a nice value (lower than that is noticeably slower and greater has little effect on sorting data sets I've seen). But if you set things up that way you could end up with three autovacuum daemons running with 256M allocated each on a 1G machine. That's pretty frightening, especially with a 200M shared buffers. We definitely need at the very least a prominent warning in the maintenance_work_mem documentation. Users can always raise it for manually run commands if they're sure they're only running one at a time. But all of this isn't a new issue is it? I thought we've had multiple autovacuum workers since 8.3. Have there been any complaints? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
Gregory Stark wrote: > "Guillaume Smet" <guillaume.smet@gmail.com> writes: > >> On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <magnus@hagander.net> wrote: >>> It's probably worthwhile to add a note about the effects of >>> autovacuum around the documentation of maintenance_work_mem, though. >> +1 >> A lot of people set maintenance_work_mem quite high because of the old >> behaviour. <snip> > We definitely need at the very least a prominent warning in the > maintenance_work_mem documentation. Users can always raise it for manually run > commands if they're sure they're only running one at a time. Yeah. > But all of this isn't a new issue is it? I thought we've had multiple > autovacuum workers since 8.3. Have there been any complaints? Yes, that's why I brought it up. Haven't seen complaints on-list, but have heard a couple from customers off-list. Not necessarily so much complaints as "what does this mean", but questions nevertheless. //Magnus
Guillaume Smet wrote:
> On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <magnus@hagander.net> wrote:
>>> The autovacuum workers change that and make it a default behaviour (as
>>> we can have 3*maintenance_work_mem by default).
>> It's still one per process, it's just that autovac uses more than one
>> process.
>
> I agree. What I implied is that by default you have 3 autovacuum
> workers so the behaviour has changed, even if it didn't change in a
> technical way.
>
>> It's probably worthwhile to add a note about the effects of
>> autovacuum around the documentation of maintenance_work_mem, though.
>
> +1
> A lot of people set maintenance_work_mem quite high because of the old
> behaviour.
How about something as simple as this?
//Magnus
*** doc/src/sgml/config.sgml
--- doc/src/sgml/config.sgml
***************
*** 881,886 **** SET ENABLE_SEQSCAN TO OFF;
--- 881,891 ----
than <varname>work_mem</varname>. Larger settings might improve
performance for vacuuming and for restoring database dumps.
</para>
+ <para>
+ Note that when autovacuum runs, up to
+ <xref linkend="guc-autovacuum-max-workers"> times this memory may be
+ allocated, so be careful not to set the default value too high.
+ </para>
</listitem>
</varlistentry>
Magnus Hagander wrote: > Tom Lane wrote: > > It seems like mostly a confusion-generator to me. Is there any actual > > evidence that autovac should use a different maintenance_work_mem than > > other processes? > > The use-case that made me think of that is one with lots of autovac > workers in a system with lots of small tables in different databases. Another thing to consider here is that lazy vacuum will scale down its memory usage depending on table size. > Turns out I read the documentation for autovac wrong. I understood that > if I wanted it to look at 1000 databases at once, I needed > autovac_workers at 1000. Talked a bit offlist with Alvaro and realized > that's not what it is, but that the documentation is a bit unclear on > that - will work on fixing that. Yeah, Rob Treat has also asked me twice about this, so it's probably worth rewriting. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.