Thread: Question about maintenance_work_mem and shared_buffer
Hi, everyone.
I have a doubt.
I have a 32-bit postrgesql running with 2.5gb of shared_buffer.
And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3.
How maintenance_work_mem is related to shared_buffer?
If the 3 workers uses 1gb, will the database crash?
Or their memory usage are separated from each other?
On Wed, May 22, 2013 at 12:07 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote: > Hi, everyone. > I have a doubt. > I have a 32-bit postrgesql running with 2.5gb of shared_buffer. > And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3. > How maintenance_work_mem is related to shared_buffer? They are independent settings. > If the 3 workers uses 1gb, will the database crash? Documentation does warn about not setting it (maintenance_work_mem) too high: "Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high." http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html -- Amit Langote
On Tue, May 21, 2013 at 1:03 PM, Amit Langote <amitlangote09@gmail.com> wrote:
On Wed, May 22, 2013 at 12:07 AM, Rodrigo BarbozaThey are independent settings.
<rodrigombufrj@gmail.com> wrote:
> Hi, everyone.
> I have a doubt.
> I have a 32-bit postrgesql running with 2.5gb of shared_buffer.
> And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3.
> How maintenance_work_mem is related to shared_buffer?Documentation does warn about not setting it (maintenance_work_mem) too high:
> If the 3 workers uses 1gb, will the database crash?
"Note that when autovacuum runs, up to autovacuum_max_workers times
this memory may be allocated, so be careful not to set the default
value too high."
http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html
--
Amit Langote
So what would it be a reasonable value?
Because I saw some people suggesting to set it to total_mem / 8.
In my case I am running a 32 bit postgres with 64gb of total memory. My kernel is 64 bits (I know that is not ideal, but that's what I have now) to recognize and use the to total memory.
Rodrigo Barboza wrote: > I have a doubt. > I have a 32-bit postrgesql running with 2.5gb of shared_buffer. > And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3. > How maintenance_work_mem is related to shared_buffer? > If the 3 workers uses 1gb, will the database crash? > Or their memory usage are separated from each other? Your doubt is quite in place, as a process on a 32-bit architecture cannot address more than 4GB of memory. See http://rhaas.blogspot.jp/2011/05/sharedbuffers-on-32-bit-systems.html shared_buffers plus the private memory of a backend shouldn't exceed 3GB. So I'd go for shared_buffers = 2GB and maintenance_work_mem much smaller than 1GB. Yours, Laurenz Albe
On Wed, May 22, 2013 at 6:14 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Your doubt is quite in place, as a process on a 32-bit architectureRodrigo Barboza wrote:
> I have a doubt.
> I have a 32-bit postrgesql running with 2.5gb of shared_buffer.
> And I have maintenance_work_mem = 1gb and autovacuum_max_workers = 3.
> How maintenance_work_mem is related to shared_buffer?
> If the 3 workers uses 1gb, will the database crash?
> Or their memory usage are separated from each other?
cannot address more than 4GB of memory.
See http://rhaas.blogspot.jp/2011/05/sharedbuffers-on-32-bit-systems.html
shared_buffers plus the private memory of a backend shouldn't
exceed 3GB.
So I'd go for shared_buffers = 2GB and maintenance_work_mem much
smaller than 1GB.
Yours,
Laurenz Albe
Perfect.
Thanks, guys!