Re: A memory leak. Postgres 7.4. Mac OS. X Server

Hi,
we have a postgresql 7.4 database server on an X Server with Mac OS
10.3.3, 7F45
The server possess two CPU's, 4 Gb in memory, and an adequate space on
disk.
The service run fine in the majority of time. But, we see the memory go
down, bit by bit. (A memory leak).
No errors are produced, but the memory is missing, and then we need to
restart the service (pg_ctl restart) to clean the busy memory. Before the
reload, the postgres run slow (by the lack of memory, I think!).
Postgres was compiled with gcc 3.3, with fink on stable (read line, open
ssl),
and with CFLAGS= -03-mtune=970 -funroll-loops
The parameters used was: /configure --with-includes=/sw/include
--with-libraries=/sw/lib --prefix=/sw --with-rendevouz

Regards

Pablo Avilés Cisneros
Area Manager
Software Research and Development Area
Development and Research, ITS
San José,Costa Rica
email: paviles(at)its(dot)co(dot)cr
phone: (506) 2110319
cel: (506) 8444312

Hi,

this is just a me too message. We have 7.4.2 running which
was build from fink (I guess same compiler settings).

My investigation so far is that:
- the system will eat more and more memory and will start swapping
to hard disk
- no performance hits so far (I haven't noticed any)
- the system will crash when the partition where the swap files reside
is full
- stopping the connections will free up the virtual memory reported in
top
- stopping postmaster will free up the disk space used for swapping and
will bring back everything to "normal"
- top will report nothing unusual (no large VSIZE, RSIZE, RSHRD values
per process), just a large total of virtual memory

Settings:
shared_buffers = 12500 # 12500*8.2kB + 1 MB = shmem ~=100MB

/etc/rc
sysctl -w kern.sysv.shmmax=268435456
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=32
sysctl -w kern.sysv.shmseg=8
sysctl -w kern.sysv.shmall=65536

We've got about 50 concurrent connections (WebApp, via JDBC...)
OSX Server 10.3.4, on XServe G5 2x2Ghz

Another thing is that we have a C trigger that will do replication on
update/insert/delete.

The whole setup was fine when running on 10.2.x and 7.3.4.

Anyway, I don't know if this is a postgres problem or an OSX 10.3.4
problem.

If you have any hints or places or tools that I could use to analyze the
problem more throughly, I'd be very glad (right now we're down to
rebooting
the system every week).

The DB has one large table with around 1.6 million rows.

Thanks

Christian

On May 21, 2004, at 10:15 PM, PAviles(at)its(dot)co(dot)cr wrote:

>
> Hi,
> we have a postgresql 7.4 database server on an  X Server with Mac OS
> 10.3.3, 7F45
> The server  possess two CPU's, 4 Gb in memory, and an adequate space
> on disk.
> The service run fine in the majority of time. But, we see the memory
> go down, bit by bit. (A memory leak).
> No errors are produced, but the memory is missing, and then we need to
> restart the service (pg_ctl restart) to clean the busy memory. Before
> the reload, the postgres run slow (by the lack of memory, I think!).
> Postgres was compiled with gcc 3.3, with fink on stable (read line,
> open ssl),
> and with CFLAGS= -03-mtune=970 -funroll-loops
> The parameters used was: /configure --with-includes=/sw/include
> --with-libraries=/sw/lib --prefix=/sw --with-rendevouz
>
> Regards
>
>
> Pablo Avilés Cisneros
> Area Manager
> Software Research and Development Area
> Development and Research, ITS
> San José,Costa Rica
> email: paviles(at)its(dot)co(dot)cr
> phone: (506) 2110319
> cel: (506) 8444312

Christian van der leeden <lists(at)logicunited(dot)com> writes:
> this is just a me too message.

Neither one of you has provided any information that would let anyone
else even guess where to look for the problem :-(

For starters, which process or processes is eating memory? (see top(1))

regards, tom lane

Hi Tom,

I'll try to be more specific, but top doesn't report anything
unusual. Each connection (28 of them) has around 1 MB RPRVT,
101 MB real shared, between 10 and 50 MB Real Size, and
231 MB Virtual Size. These values haven't changed. The postmaster
has 60k Real private, 101MB Real shared, 756 k real size and 230 MB
in Virtual Size.

shared buffers is at shared_buffers = 12500 (100 MB)
sort_mem = 5000
vacuum_mem = 5000

My problem is that I can't pinpoint a process that is constantly paging
out. So any pointers to tools to investigate the problem more would
be very helpful. Any unusual things to look for when running
lsof? Nothing special in the postgres server log.
Settings for shared memory in /etc/rc:
sysctl -w kern.sysv.shmmax=268435456
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=32
sysctl -w kern.sysv.shmseg=8
sysctl -w kern.sysv.shmall=65536

The only thing that strikes me as odd, is that each connection has such
a large
virtual size, our backup system is running with shared_buffers of 10 MB
and shows only around 30 MB Virtual size.

Basic problem that the system is paging out and allocating more and
more place
on the disc persists (9 GB, right now and has been up for 3 days).

Maybe I'll try to lower shared buffers...

Christian

P.S.: Running 7.4.2 on OSX 10.3.4 with 4.5 GB RAM.

On Jun 8, 2004, at 6:53 AM, Tom Lane wrote:

> Christian van der leeden <lists(at)logicunited(dot)com> writes:
>> this is just a me too message.
>
> Neither one of you has provided any information that would let anyone
> else even guess where to look for the problem :-(
>
> For starters, which process or processes is eating memory? (see top(1))
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>
-----------------------------------------------
Christian van der Leeden
Software Engineering