Thread: shared_buffers Question

shared_buffers Question

From
Joe Lester
Date:
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!



Re: shared_buffers Question

From
"Scott Marlowe"
Date:
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.


Re: shared_buffers Question

From
Scott Ribe
Date:
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


Re: shared_buffers Question

From
Scott Ribe
Date:
> 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


Re: shared_buffers Question

From
Joe Lester
Date:
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.

Re: shared_buffers Question

From
Joe Lester
Date:
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.


Re: shared_buffers Question

From
"Scott Marlowe"
Date:
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.