Thread
-
Fw: Help me put 2 Gigs of RAM to use
Mark Stosberg <mark@summersault.com> — 2009-12-10T15:50:10Z
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/ . . . . . . . .
-
Re: Fw: Help me put 2 Gigs of RAM to use
Matthew Wakeling <matthew@flymine.org> — 2009-12-10T16:03:11Z
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
-
Re: Help me put 2 Gigs of RAM to use
Mark Stosberg <mark@summersault.com> — 2009-12-10T16:44:32Z
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/ . . . . . . . . -
Re: Fw: Help me put 2 Gigs of RAM to use
Greg Smith <greg@2ndquadrant.com> — 2009-12-10T16:45:30Z
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 -
Re: Fw: Help me put 2 Gigs of RAM to use
Robert Haas <robertmhaas@gmail.com> — 2009-12-10T17:19:44Z
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