Обсуждение: Question about maintenance_work_mem and shared_buffer

Поиск
Список
Период
Сортировка

Question about maintenance_work_mem and shared_buffer

От
Rodrigo Barboza
Дата:
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?

Re: Question about maintenance_work_mem and shared_buffer

От
Amit Langote
Дата:
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


Re: Question about maintenance_work_mem and shared_buffer

От
Rodrigo Barboza
Дата:



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 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


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.

Re: Question about maintenance_work_mem and shared_buffer

От
Albe Laurenz
Дата:
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


Re: Question about maintenance_work_mem and shared_buffer

От
Rodrigo Barboza
Дата:



On Wed, May 22, 2013 at 6:14 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
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


Perfect.
Thanks, guys!