Thread: Fw: Help me put 2 Gigs of RAM to use
Hello, PostgreSQL has served us very well powering a busy national pet adoption website. Now I'd like to tune our setup further get more out of hardware. What I'm noticing is that the while the FreeBSD server has 4 Gigs of memory, there are rarely every more than 2 in use-- the memory use graphs as being rather constant. My goal is to make good use of those 2 Gigs of memory to improve performance and reduce the CPU usage. The server has 4 2.33 Ghz processors in it, and RAIDed 15k RPM SCSI disks. Here are some current memory-related settings from our postgresql.conf file. (We currently run 8.2, but are planning an upgrade to 8.4 "soon"). Do you see an obvious suggestions for improvement? I find the file a bit hard to read because of the lack of units in the examples, but perhaps that's already been addressed in future versions. max_connections = 400 # Seems to be enough us shared_buffers = 8192 effective_cache_size = 1000 work_mem = 4096 maintenance_work_mem = 160MB Thanks for your suggestions! Mark [I tried to post this yesterday but didn't see it come through. This message is a second attempt.) -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark Stosberg Principal Developer mark@summersault.com Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . .
On Thu, 10 Dec 2009, Mark Stosberg wrote: > What I'm noticing is that the while the FreeBSD server has 4 Gigs of > memory, there are rarely every more than 2 in use-- the memory use > graphs as being rather constant. My goal is to make good use of those 2 > Gigs of memory to improve performance and reduce the CPU usage. I think you'll find that the RAM is already being used quite effectively as disc cache by the OS. It sounds like the server is actually set up pretty well. You may get slightly better performance by tweaking a thing here or there, but the server needs some OS disc cache to perform well. > (We currently run 8.2, but are planning an upgrade to 8.4 "soon"). Highly recommended. > [I tried to post this yesterday but didn't see it come through. This > message is a second attempt.) The mailing list server will silently chuck any message whose subject starts with the word "help", just in case you're asking for help about managing the mailing list. The default behaviour is not to inform you that it has done so. It is highly annoying - could a list admin please consider changing this? Matthew -- I would like to think that in this day and age people would know better than to open executables in an e-mail. I'd also like to be able to flap my arms and fly to the moon. -- Tim Mullen
Thanks for the response, Matthew.
> On Thu, 10 Dec 2009, Mark Stosberg wrote:
> > What I'm noticing is that the while the FreeBSD server has 4 Gigs of
> > memory, there are rarely every more than 2 in use-- the memory use
> > graphs as being rather constant. My goal is to make good use of those 2
> > Gigs of memory to improve performance and reduce the CPU usage.
>
> I think you'll find that the RAM is already being used quite effectively
> as disc cache by the OS. It sounds like the server is actually set up
> pretty well. You may get slightly better performance by tweaking a thing
> here or there, but the server needs some OS disc cache to perform well.
As part of reviewing this status, I it appears that the OS is only
addresses 3 of the 4 Gigs of memory. We'll work on our FreeBSD setup to
cure that.
Here's how "top" reports the memory breakdown:
Mem: 513M Active, 2246M Inact, 249M Wired, 163M Cache, 112M Buf, 7176K
Free Swap: 9216M Total, 1052K Used, 9215M Free
So perhaps the OS disc cache is represented in the "Inactive" memory
statistic? I suppose once we have the 4th Gig of memory actually
available, that would all be doing to the disk cache.
> > (We currently run 8.2, but are planning an upgrade to 8.4 "soon").
>
> Highly recommended.
For performance improvements in particular?
Mark
--
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC
765-939-9301 ext 202 database driven websites
. . . . . http://www.summersault.com/ . . . . . . . .
Mark Stosberg wrote:
> I find the file a bit hard to read because of the lack of units in
> the examples, but perhaps that's already been addressed in future
> versions.
>
> max_connections = 400 # Seems to be enough us
> shared_buffers = 8192
> effective_cache_size = 1000
> work_mem = 4096
> maintenance_work_mem = 160MB
>
It's already addressed in 8.2, as you can note by the fact that
"maintenance_work_mem" is in there with an easy to read format.
Guessing that someone either pulled in settings from an older version,
or used some outdated web guide to get starter settings.
To convert the rest of them, you need to know what the units for each
parameter is. You can find that out like this:
gsmith=# select name,setting,unit from pg_settings where name in
('shared_buffers','effective_cache_size','work_mem');
name | setting | unit
----------------------+---------+------
effective_cache_size | 16384 | 8kB
shared_buffers | 4096 | 8kB
work_mem | 1024 | kB
So your shared buffers setting is 8192 * 8K = 64MB
effective_cache_size is 8MB
work_mem is 4MB.
The first and last of those are reasonable but on the small side, the
last is...not. Increasing it won't actually use more memory on your
server though, it will just change query plans--so you want to be
careful about increasing it too much in one shot.
The next set of stuff you need to know about general guidelines for
server sizing is at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
You'd probably want to put shared_buffers at a higher level based on the
amount of RAM on your server, but I'd suggest you tune the checkpoint
parameters along with that--just increasing the buffer space along can
cause problems rather than solve them if you're having checkpoints all
the time.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
On Thu, Dec 10, 2009 at 11:45 AM, Greg Smith <greg@2ndquadrant.com> wrote: > So your shared buffers setting is 8192 * 8K = 64MB > effective_cache_size is 8MB > work_mem is 4MB. > > The first and last of those are reasonable but on the small side, the last > is...not. I believe that the second instance of the word "last" in that sentence should have been "middle", referring to effective_cache_size. Small values discourage the planner from using indices in certain situations. ...Robert