Re: Postgres filling up hard drive with swap files

Joe Lester <joe_lester@sweetwater.com> wrote:
> > How many Postgres processes are running?
>
> 146 right now. Most of them are idle at any given point in time.

That's a lot for a machine with only 512M of RAM.

> > I'm wondering, however, if you have a connection leak instead.  i.e.
> > is it possible that your client application is opening a whole bunch
> > of connections and never closing them?
>
> No. The clients open only one connection (and hang onto it for dear
> life  :-).

If these clients aren't utilizing the database, it might be worthwhile
to have them disconnect after a period of inactivity, and reconnect when
things get busy again.

> > You did show that you have
> > a max # of connection of 200.  That's pretty high, unless you've got
> > a lot of RAM in that machine.
>
> I have 512 MB of RAM in the machine.

That's not a lot of RAM.  I have 512M in a machine that's only designed
to handle 20 connections (although that's MS Windows + MSSQL ... but
you get the idea ... we're talking a factor of 10 here)

> The server is performing
> wonderfully. It's just that the swap files keep sprouting like weeds.

I would expect that if you ignore it for a while, eventually it will
reach an equalibrium.  (where it's not increasing the amount of swap in
use) but it will always hurt performance any time is has to page in or
out.

> > How much memory is actually in use by Postgres processes?  (The amount
> > of
> > swap in use is unimportant to the Postgres folks, it's an OS thing)
>
> This is where I could use some pointers. The following line is a top
> entry for a single postgres process. Hope that helps.
>
> PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  VSIZE
> 14235 postgres     0.0%  0:01.36   1     9    33   880K  16.9M  9.62M  60.0M

Please don't wrap machine-generated output ... it makes it VERY difficult
to understand.

I'll defer this answer to Jeff, as he seems to know quite a bit more about
how Darwin manages memory than I do.

His recommendation to try pgpool was also good.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com