[PERFORM] 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 StosbergPrincipal Developer m...@summersault.com Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . . -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fw: Help me put 2 Gigs of RAM to use
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fw: Help me put 2 Gigs of RAM to use
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 Smith2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fw: Help me put 2 Gigs of RAM to use
On Thu, Dec 10, 2009 at 11:45 AM, Greg Smith g...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
