Обсуждение: Re: [GENERAL] PostgreSQL and memory usage
Hi Dann, I took hackers out of the list as this isn't really a hacking issue, but I added in performance as this definitely applies there. There are generally two areas of a database server you have to reconfigure to use that extra memory. The first is the kernel's shared memory settings. On a linux box that has sysconf installed this is quite easy. If it isn't installed, install it, as it's much easier to manipulate your kernel's settings using sysctl than it is with editing rc.local. First, get root. Then, use 'sysctl -a|grep shm' to get a list of all the shared memory settings handled by sysctl. On a default redhat install, we'll get something like this: kernel.shmmni = 4096 kernel.shmall = 2097152 kernel.shmmax = 33554432 On my bigger box, it's been setup to have this: kernel.shmmni = 4096 kernel.shmall = 32000000 kernel.shmmax = 256000000 To make changes that stick around, edit the /etc/sysctl.conf file to have lines that look kinda like those above. To make the changes to the /etc/sysctl.conf file take effect, use 'sysctl -p'. Next, as the postgres user, edit $PGDATA/postgresql.conf and increase the number of shared buffers. On most postgresql installations this number is multiplied by 8k to get the amount of ram being allocated, since postgresql allocates share buffers in blocks the same size as what it uses on the dataset. To allocate 256 Megs of buffers (that's what I use, seems like a nice large chunk, but doesn't starve my other processes or system file cache) set it to 32768. Be careful how big you make your sort size. I haven't seen a great increase in speed on anything over 8 or 16 megs, while memory usage can skyrocket under heavy parallel load with lots of sorts, since sort memory is PER SORT maximum. Then do the old pg_ctl reload and you should be cooking with gas.
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Then do the old pg_ctl reload and you should be cooking with gas.
One correction: altering the number of shared buffers requires an actual
postmaster restart.
regards, tom lane
To put this usage of shared buffers in perspective would you mind kindly let us know your total amount of system ram? Without hearing what percentage of memory used as shared buffers (assuming is the primary application being using here) I have always taken the 'more is better' approach with shared buffers but would like to know what in terms of percentages other people are using. I have been using 50% of system ram (2 out of 4 gigs) for shared buffers (and corresponding shmmax values) and it has been working great. I haven't tweaked the kernel yet to get more than 2 gigs shmmax so I can't speak for a setup using over 50%. I've been using between 256 and 512 megs sort memory which sounds like a little much from what I'm hearing here. Thanks Fred > > Hi Dann, I took hackers out of the list as this isn't really a hacking > issue, but I added in performance as this definitely applies there. > > There are generally two areas of a database server you have to > reconfigure to use that extra memory. The first is the kernel's shared > memory settings. > > On a linux box that has sysconf installed this is quite easy. If it > isn't installed, install it, as it's much easier to manipulate your > kernel's settings using sysctl than it is with editing rc.local. > > First, get root. Then, use 'sysctl -a|grep shm' to get a list of all > the shared memory settings handled by sysctl. > > On a default redhat install, we'll get something like this: > > kernel.shmmni = 4096 > kernel.shmall = 2097152 > kernel.shmmax = 33554432 > > On my bigger box, it's been setup to have this: > > kernel.shmmni = 4096 > kernel.shmall = 32000000 > kernel.shmmax = 256000000 > > To make changes that stick around, edit the /etc/sysctl.conf file to > have lines that look kinda like those above. To make the changes to > the /etc/sysctl.conf file take effect, use 'sysctl -p'. > > Next, as the postgres user, edit $PGDATA/postgresql.conf and increase > the number of shared buffers. On most postgresql installations this > number is multiplied by 8k to get the amount of ram being allocated, > since > postgresql allocates share buffers in blocks the same size as what it > uses on the dataset. To allocate 256 Megs of buffers (that's what I > use, seems like a nice large chunk, but doesn't starve my other > processes or system file cache) set it to 32768. > > Be careful how big you make your sort size. I haven't seen a great > increase in speed on anything over 8 or 16 megs, while memory usage can > skyrocket under heavy parallel load with lots of sorts, since sort > memory is PER SORT maximum. > > Then do the old pg_ctl reload and you should be cooking with gas. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Oh yeah, sorry. My box has 1.5 gig ram, but it is an application server that runs things other than just postgresql. It also runs: Apache Real Server OpenLDAP Squid Samba with all those services fired up and running, as well as postgresql with 256 Megs of shared buffer, I have about 900 megs of cache and 100 megs free ram. Since a lot of data is flying off the hard drives at any given time, favoring one service (database) over the others makes little sense for me, and I've found that there was little or no performance gain from 256 Megs ram over say 128 meg or 64 meg. We run about 50 databases averaging about 25megs each or so (backed up, it's about 50 to 75 Megs on the machine's hard drives) so there's no way for ALL the data to fit into memory. On Tue, 7 Jan 2003, Fred Moyer wrote: > To put this usage of shared buffers in perspective would you mind kindly > let us know your total amount of system ram? Without hearing what > percentage of memory used as shared buffers (assuming is the primary > application being using here) > > I have always taken the 'more is better' approach with shared buffers but > would like to know what in terms of percentages other people are using. I > have been using 50% of system ram (2 out of 4 gigs) for shared buffers > (and corresponding shmmax values) and it has been working great. I > haven't tweaked the kernel yet to get more than 2 gigs shmmax so I can't > speak for a setup using over 50%. I've been using between 256 and 512 > megs sort memory which sounds like a little much from what I'm hearing > here.