Postgres filling up hard drive with swap files - Mailing list pgsql-general

This thread was renamed. It used to be: "shared_buffers Question". The
old thread kind of died out. I'm hoping to get some more direction by
rephrasing the problem, along with some extra observations I've
recently made.


The core of the problem is that Postgres is filling up my hard drive
with swap files at the rate of around 3 to 7 GB per week (that's
Gigabytes not Megabytes) . At this rate it takes roughly two months to
fill up my 40 GB hard drive with swap files. When the hard drive is
full, Postgres crashes and all the 200 connected clients hang. It's
not pretty. I can see the swap files and watch them grow over time.
They are located on my Mac OS 10.3.2 eMac at /var/vm/. I know the swap
files come from Postgres processes because, if I stop the Postgres
server, the swap files go away and the hard drive space is freed up.


When I look at each individual Postgres back-end process in the
Activity Monitor, each one seems to take up a lot of memory. Processes
usually jump to 60.04 MB of "virtual memory" soon after they are first
created. However, "real memory" use ranges 1 MB to 10 MB per process.


I'm looking for advice on what settings to look at in order to solve
the problem of my hard drive filling up with swap files. My guess is
that it must be something I'm doing, since I would find it hard to
imagine that Postgres has an *innate* memory leak that big on OS X.
Here are the only settings I have customized for my Postgres
installation, in case it helps:


<bold>In /etc/profile:</bold>

ulimit -u 512 # increases the max processes per user (from 100 to 512)
at the shell level.

ulimit -n 40000 # increases the max number of open files allowed by
the shell level (used to be 8000).


<bold>In postgresql.conf:</bold>

max_connections = 200

shared_buffers = 2000<bold>


In <fontfamily><param>Courier</param>/etc/rc:</fontfamily></bold><fontfamily><param>Courier</param>

</fontfamily>sysctl -w kern.sysv.shmmax=167772160

sysctl -w kern.sysv.shmall=65536


<bold>In /etc/sysctl.conf:</bold>

kern.maxproc=2048

kern.maxprocperuid=512   # Turn up the maxproc per user

kern.maxfiles=40000   # Turn up max files

kern.maxfilesperproc=30000   # Turn up max files per process



Thanks for any insight you can provide!


Joe
This thread was renamed. It used to be: "shared_buffers Question". The
old thread kind of died out. I'm hoping to get some more direction by
rephrasing the problem, along with some extra observations I've
recently made.

The core of the problem is that Postgres is filling up my hard drive
with swap files at the rate of around 3 to 7 GB per week (that's
Gigabytes not Megabytes) . At this rate it takes roughly two months to
fill up my 40 GB hard drive with swap files. When the hard drive is
full, Postgres crashes and all the 200 connected clients hang. It's not
pretty. I can see the swap files and watch them grow over time. They
are located on my Mac OS 10.3.2 eMac at /var/vm/. I know the swap files
come from Postgres processes because, if I stop the Postgres server,
the swap files go away and the hard drive space is freed up.

When I look at each individual Postgres back-end process in the
Activity Monitor, each one seems to take up a lot of memory. Processes
usually jump to 60.04 MB of "virtual memory" soon after they are first
created. However, "real memory" use ranges 1 MB to 10 MB per process.

I'm looking for advice on what settings to look at in order to solve
the problem of my hard drive filling up with swap files. My guess is
that it must be something I'm doing, since I would find it hard to
imagine that Postgres has an *innate* memory leak that big on OS X.
Here are the only settings I have customized for my Postgres
installation, in case it helps:

In /etc/profile:
ulimit -u 512 # increases the max processes per user (from 100 to 512)
at the shell level.
ulimit -n 40000 # increases the max number of open files allowed by the
shell level (used to be 8000).

In postgresql.conf:
max_connections = 200
shared_buffers = 2000

In /etc/rc:
sysctl -w kern.sysv.shmmax=167772160
sysctl -w kern.sysv.shmall=65536

In /etc/sysctl.conf:
kern.maxproc=2048
kern.maxprocperuid=512   # Turn up the maxproc per user
kern.maxfiles=40000   # Turn up max files
kern.maxfilesperproc=30000   # Turn up max files per process


Thanks for any insight you can provide!

Joe