Обсуждение: shared_buffers Question
I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 clients connecting for about 2 months without a crash. However just yesterday the database and all the clients hung. When I looked at the Mac I'm using as the postgres server it had a window up that said that there was no more disk space available to write memory too. I ended up having to restart the whole machine. I would like to configure postgres so that is does not rely so heavily on disk-based memory but, rather, tries to stay within the scope of the 512MB of physical memory in the Mac. Am I correct in thinking that lowering that value of shared_buffers in postgresql.conf will reduce the amount of disk space that is swapped for memory? I lowered the value from 2000 down to 500. Was that the right thing to do or should I have gone the other way? Any other settings I should look at? Thanks!
On Sat, 2004-07-31 at 10:25, Joe Lester wrote: > I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 > clients connecting for about 2 months without a crash. However just > yesterday the database and all the clients hung. When I looked at the > Mac I'm using as the postgres server it had a window up that said that > there was no more disk space available to write memory too. I ended up > having to restart the whole machine. I would like to configure postgres > so that is does not rely so heavily on disk-based memory but, rather, > tries to stay within the scope of the 512MB of physical memory in the > Mac. > > Am I correct in thinking that lowering that value of shared_buffers in > postgresql.conf will reduce the amount of disk space that is swapped > for memory? > > I lowered the value from 2000 down to 500. Was that the right thing to > do or should I have gone the other way? Any other settings I should > look at? Thanks! Your shared buffers are almost certainly not the problem here. 2000 shared buffers is only 16 Megs of ram, max. More than likely, the database filled up the data directory / partition because it wasn't being vacuumed.
BTW Joe, I sent my earlier suggestion to you directly. You might want to talk to your email admin to find out why your server bounced a perfectly innocuous message thusly: <joe_lester@sweetwater.com>: 12.47.0.10 failed after I sent the message. Remote host said: 550 Message Returned: For some reason, your e-mail was unable to be delivered to Sweetwater. This may be due to a content filter on our server. If your message is legitimate and contains questionable content, please remove it and try re-sending. If you feel this is an error, please contact your Sweetwater Representative or our E-mail Administrator at 1-800-222-4700 x1198 to resolve this issue. We apologize for any inconvenience this may have caused. #D702 -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
> Your shared buffers are almost certainly not the problem here. 2000 > shared buffers is only 16 Megs of ram, max. More than likely, the > database filled up the data directory / partition because it wasn't > being vacuumed. Yes. Also check to make sure that some rogue process somewhere isn't filling your hard disk with some huge log file. I don't remember the UNIX commands offhand, but you should sudo a search starting in / for all large files, say > 1GB for instance. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
Thanks for the suggestion Scott. I did a... find / -type f -size +100000 -print The results contained 9 Gig! of swap files: /private/var/vm/swapfile0 /private/var/vm/swapfile1 /private/var/vm/swapfile10 .... [plus many more entries] That seems to indicate to me a memory "leak" of some sort. My symptoms mirror almost exactly those of this fellow, who's thread was never resolved as far as I can see: http://archives.postgresql.org/pgsql-bugs/2004-06/msg00013.php Anyone have any other suggestions on what to look for? At this rate I'm leaking about 2 to 4 Gigs of memory (swap) per week. I'm running postgres 7.4.1 on an 700MHz eMac, 512MB RAM, OS 10.3.2. Thanks. <fontfamily><param>Courier</param>> Scott Ribe: > Also check to make sure that some rogue process somewhere isn't filling your > hard disk with some huge log file. I don't remember the UNIX commands > offhand, but you should sudo a search starting in / for all large files, say Joe's Original Message: </fontfamily>I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 clients connecting for about 2 months without a crash. However just yesterday the database and all the clients hung. When I looked at the Mac I'm using as the postgres server it had a window up that said that there was no more disk space available to write memory too. I ended up having to restart the whole machine. I would like to configure postgres so that is does not rely so heavily on disk-based memory but, rather, tries to stay within the scope of the 512MB of physical memory in the Mac. Thanks for the suggestion Scott. I did a... find / -type f -size +100000 -print The results contained 9 Gig! of swap files: /private/var/vm/swapfile0 /private/var/vm/swapfile1 /private/var/vm/swapfile10 .... [plus many more entries] That seems to indicate to me a memory "leak" of some sort. My symptoms mirror almost exactly those of this fellow, who's thread was never resolved as far as I can see: http://archives.postgresql.org/pgsql-bugs/2004-06/msg00013.php Anyone have any other suggestions on what to look for? At this rate I'm leaking about 2 to 4 Gigs of memory (swap) per week. I'm running postgres 7.4.1 on an 700MHz eMac, 512MB RAM, OS 10.3.2. Thanks. > Scott Ribe: > Also check to make sure that some rogue process somewhere isn't filling your > hard disk with some huge log file. I don't remember the UNIX commands > offhand, but you should sudo a search starting in / for all large files, say Joe's Original Message: I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 clients connecting for about 2 months without a crash. However just yesterday the database and all the clients hung. When I looked at the Mac I'm using as the postgres server it had a window up that said that there was no more disk space available to write memory too. I ended up having to restart the whole machine. I would like to configure postgres so that is does not rely so heavily on disk-based memory but, rather, tries to stay within the scope of the 512MB of physical memory in the Mac.
I'm doing a nightly vacuum... so I don't think that's it, although should I be doing a FULL vacuum instead? The size of my data directory is only about 389 MB. I'll take a closer look at file sizes going forward. echo "VACUUM ANALYZE VERBOSE;" | /Library/PostgreSQL/bin/psql -U postgres officelink 2>> vacuum.log Thanks. <bold><fontfamily><param>Lucida Grande</param><x-tad-bigger>From</x-tad-bigger></fontfamily></bold><fontfamily><param>Lucida Grande</param><x-tad-bigger>: </x-tad-bigger><bold><x-tad-bigger>"Scott Marlowe"</x-tad-bigger></bold></fontfamily><fontfamily><param>Courier</param> Your shared buffers are almost certainly not the problem here. 2000 shared buffers is only 16 Megs of ram, max. More than likely, the database filled up the data directory / partition because it wasn't being vacuumed. </fontfamily> <fontfamily><param>Courier</param>On Sat, 2004-07-31 at 10:25, Joe Lester wrote: > I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 > clients connecting for about 2 months without a crash. However just > yesterday the database and all the clients hung. When I looked at the > Mac I'm using as the postgres server it had a window up that said that > there was no more disk space available to write memory too. I ended up > having to restart the whole machine. I would like to configure postgres > so that is does not rely so heavily on disk-based memory but, rather, > tries to stay within the scope of the 512MB of physical memory in the > Mac. </fontfamily> I'm doing a nightly vacuum... so I don't think that's it, although should I be doing a FULL vacuum instead? The size of my data directory is only about 389 MB. I'll take a closer look at file sizes going forward. echo "VACUUM ANALYZE VERBOSE;" | /Library/PostgreSQL/bin/psql -U postgres officelink 2>> vacuum.log Thanks. From: "Scott Marlowe" Your shared buffers are almost certainly not the problem here. 2000 shared buffers is only 16 Megs of ram, max. More than likely, the database filled up the data directory / partition because it wasn't being vacuumed. On Sat, 2004-07-31 at 10:25, Joe Lester wrote: > I've been running a postgres server on a Mac (10.3, 512MB RAM) with 200 > clients connecting for about 2 months without a crash. However just > yesterday the database and all the clients hung. When I looked at the > Mac I'm using as the postgres server it had a window up that said that > there was no more disk space available to write memory too. I ended up > having to restart the whole machine. I would like to configure postgres > so that is does not rely so heavily on disk-based memory but, rather, > tries to stay within the scope of the 512MB of physical memory in the > Mac.
Is the memory freed up if you shut down and restart PostgreSQL? If not, then it might not be PostgreSQL that's directly causing the issue, but something like logging. What OS is this by the way? On Tue, 2004-08-17 at 15:10, Joe Lester wrote: > Thanks for the suggestion Scott. I did a... > > find / -type f -size +100000 -print > > The results contained 9 Gig! of swap files: > /private/var/vm/swapfile0 > /private/var/vm/swapfile1 > /private/var/vm/swapfile10 > .... [plus many more entries] > > That seems to indicate to me a memory "leak" of some sort. My > symptomsmirror almost exactly those of this fellow, who's thread was > neverresolved as far as I can see: > > http://archives.postgresql.org/pgsql-bugs/2004-06/msg00013.php > > Anyone have any other suggestions on what to look for? At this rateI'm > leaking about 2 to 4 Gigs of memory (swap) per week. I'm > runningpostgres 7.4.1 on an 700MHz eMac, 512MB RAM, OS 10.3.2. Thanks. > > > Scott Ribe: > > Also check to make sure that some rogue process somewhere > isn'tfilling your > > hard disk with some huge log file. I don't remember the UNIX > commands > > offhand, but you should sudo a search starting in / for all > largefiles, say > > Joe's Original Message: > I've been running a postgres server on a Mac (10.3, 512MBRAM) with 200 > clients connecting for about 2 months without a crash.However just > yesterday the database and all the clients hung. When Ilooked at the > Mac I'm using as the postgres server it had a window upthat said that > there was no more disk space available to write memorytoo. I ended up > having to restart the whole machine. I would like toconfigure postgres > so that is does not rely so heavily on disk-basedmemory but, rather, > tries to stay within the scope of the 512MB ofphysical memory in the > Mac.