pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwork-mem
3+ messages / 3 participants
[nested] [flat]
* work-mem
@ 2007-01-28 20:21 Campbell, Lance <lance@uiuc.edu>
2007-01-28 20:26 ` Re: work-mem imad <immaad@gmail.com>
2007-01-28 22:11 ` Re: work_mem Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 2 replies; 3+ messages in thread
From: Campbell, Lance @ 2007-01-28 20:21 UTC (permalink / raw)
To: pgsql-performance
I have been researching how to improve my overall performance of
postgres. I am a little confused on the reasoning for how work-mem is
used in the postgresql.conf file. The way I understand the
documentation is you define with work-mem how much memory you want to
allocate per search. Couldn't you run out of memory? This approach
seems kind of odd to me. How do you tell the system not to allocate too
much memory if you all of the sudden got hit with a heavier number of
queries?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: work-mem
2007-01-28 20:21 work-mem Campbell, Lance <lance@uiuc.edu>
@ 2007-01-28 20:26 ` imad <immaad@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: imad @ 2007-01-28 20:26 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-performance
work-mem tells the size of physical memory only, virtual memory is
always there off course in case you run out of available memory.
I recommend you reading PostgreSQL internals for all this stuff:
http://www.postgresql.org/docs/8.0/static/internals.html
--Imad
www.EnterpriseDB.com
On 1/29/07, Campbell, Lance <lance@uiuc.edu> wrote:
>
>
>
>
> I have been researching how to improve my overall performance of postgres.
> I am a little confused on the reasoning for how work-mem is used in the
> postgresql.conf file. The way I understand the documentation is you define
> with work-mem how much memory you want to allocate per search. Couldn't you
> run out of memory? This approach seems kind of odd to me. How do you tell
> the system not to allocate too much memory if you all of the sudden got hit
> with a heavier number of queries?
>
>
>
>
>
> Thanks,
>
>
>
> Lance Campbell
>
> Project Manager/Software Architect
>
> Web Services at Public Affairs
>
> University of Illinois
>
> 217.333.0382
>
> http://webservices.uiuc.edu
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: work_mem
2007-01-28 20:21 work-mem Campbell, Lance <lance@uiuc.edu>
@ 2007-01-28 22:11 ` Bill Moran <wmoran@collaborativefusion.com>
1 sibling, 0 replies; 3+ messages in thread
From: Bill Moran @ 2007-01-28 22:11 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-performance
"Campbell, Lance" <lance@uiuc.edu> wrote:
>
> I have been researching how to improve my overall performance of
> postgres. I am a little confused on the reasoning for how work_mem is
> used in the postgresql.conf file. The way I understand the
> documentation is you define with work_mem how much memory you want to
> allocate per search. Couldn't you run out of memory? This approach
> seems kind of odd to me. How do you tell the system not to allocate too
> much memory if you all of the sudden got hit with a heavier number of
> queries?
work_mem tells PostgreSQL how much memory to use for each sort/join.
If a sort/join exceeds that amount, PostgreSQL uses temp files on the
disk instead of memory to do the work.
If you want a query to complete, you've got to allow Postgres to finish
it. The work_mem setting gives Postgres information on how to go about
doing that in the best way.
If you want to guarantee that individual processes can't suck up tons of
memory, use your operating system's ulimit or equivalent functionality.
That's one of the advantages of the forking model, it allows the operating
system to enforce a certain amount of policy an a per-connection basis.
HTH,
Bill
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-01-28 22:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-28 20:21 work-mem Campbell, Lance <lance@uiuc.edu>
2007-01-28 20:26 ` imad <immaad@gmail.com>
2007-01-28 22:11 ` Re: work_mem Bill Moran <wmoran@collaborativefusion.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox