Thread: Postgress memory leak with JBoss3.2.6 and large DB

Postgress memory leak with JBoss3.2.6 and large DB

From
"Indika Maligaspe"
Date:

Hey guys,

  We are running a Linux 2.4 enterprise edition box with 6GB of RAM, Postgres 8.0.3. Our applications are running on JBoss 3.2.6. We are having a Database of over 22GB in size.

The problem is when we are querying a specific set of table (which all tables having over 100K  of rows), the Postgres user process takes over or close 700MB of memory. This is just to return 3000 odd rows. Even though we have lot of data we still do not have that much to eat up this much of memory.

 

What I would like to know is, is there any setting in the Postgres or in Linux that we can tune this with?

 

Our Postgres.conf file has the following settings, we have been playing around wit this but still no success.

 

shared_buffers = 5000   

effective_cache_size = 10000    

work_mem =  2048                               

random_page_cost = 2  

 

A sample of the top command is given below.

 

12:38:05  up 136 days,  7:06, 10 users,  load average: 7.69, 4.83, 3.78

459 processes: 458 sleeping, 1 running, 0 zombie, 0 stopped

CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle

           total    9.6%    0.0%    1.8%   0.0%     0.0%   88.3%    0.0%

           cpu00   11.3%    0.0%    0.3%   0.0%     0.1%   88.0%    0.0%

           cpu01    8.9%    0.0%    2.5%   0.0%     0.0%   88.4%    0.0%

           cpu02   14.1%    0.0%    2.9%   0.0%     0.0%   82.9%    0.0%

           cpu03    4.1%    0.0%    1.5%   0.1%     0.1%   93.8%    0.0%

Mem:  6153976k av, 6092084k used,   61892k free,       0k shrd,    6232k buff

                   4769364k actv,  916224k in_d,  111336k in_c

Swap: 1052216k av,  761912k used,  290304k free                 3036700k cached

 

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND

19736 postgres  15   0  508M 448M 42840 D     0.9  7.4   0:22   0 postmaster

19740 postgres  15   0  507M 441M 41428 D     0.8  7.3   0:21   0 postmaster

19779 postgres  15   0  508M 472M 42828 D     0.8  7.8   0:21   0 postmaster

19789 postgres  15   0  508M 477M 42412 D     0.6  7.9   0:21   0 postmaster

19738 postgres  15   0  507M 438M 41852 D     0.4  7.3   0:21   0 postmaster

14647 postgres  15   0 63948  56M 44236 D     0.1  0.9   0:41   3 postmaster

 

As you can see the postmaster users are taking way over the memory that should be taken.

 

If any of you can give us some pointers we would really appreciate that and thanks in advance.

 

Regards

Indika.

Re: Postgress memory leak with JBoss3.2.6 and large DB

From
Tom Lane
Date:
"Indika Maligaspe" <indika@rezgateway.com> writes:
> The problem is when we are querying a specific set of table (which all
> tables having over 100K  of rows), the Postgres user process takes over or
> close 700MB of memory. This is just to return 3000 odd rows. Even though we
> have lot of data we still do not have that much to eat up this much of
> memory.

Playing with server-side settings won't have the slightest effect on a
client-side problem.  I'd suggest asking about this on the pgsql-jdbc
list; they are more likely to have useful suggestions than backend
hackers will.

            regards, tom lane

Re: Postgress memory leak with JBoss3.2.6 and large DB

From
Dave Cramer
Date:
On 31-Aug-06, at 1:54 PM, Tom Lane wrote:

> "Indika Maligaspe" <indika@rezgateway.com> writes:
>> The problem is when we are querying a specific set of table (which
>> all
>> tables having over 100K  of rows), the Postgres user process takes
>> over or
>> close 700MB of memory. This is just to return 3000 odd rows. Even
>> though we
>> have lot of data we still do not have that much to eat up this
>> much of
>> memory.
>
> Playing with server-side settings won't have the slightest effect on a
> client-side problem.  I'd suggest asking about this on the pgsql-jdbc
> list; they are more likely to have useful suggestions than backend
> hackers will.

What is the query here. I doubt this is a client side problem, as we
are still looking at the server side processes, not the java
processes here.

Also your memory settings are *way* too low
Dave
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly
>


Re: Postgress memory leak with JBoss3.2.6 and large DB

From
Mark Kirkwood
Date:
Indika Maligaspe wrote:
> Hey guys,
>
>   We are running a Linux 2.4 enterprise edition box with 6GB of RAM,
> **Postgres 8.0.3**. (snippage)
>

You might want to consider upgrading to 8.0.8 (see below), and seeing if
the problem still persists.


> As you can see the postmaster users are taking way over the memory that
> should be taken.
>
>
> If any of you can give us some pointers we would really appreciate that
> and thanks in advance.
>

I notice that there are a number of fixes for memory leaks since 8.0.3 -
8.0.4 and 8.0.8 is where I see 'em specifically (reading release notes
for 8.0.8). So you may be experiencing an issue that is fixed in the
current 8.0 releases! I recommend upgrading to 8.0.8.

You didn't say what your HW was, but if you are on a 32-bit platform,
then a 2.4 kernel when you have >2G ram may leak noticeable amounts of
memory itself...

Cheers

Mark


Re: Postgress memory leak with JBoss3.2.6 and large DB

From
"Indika Maligaspe"
Date:
Hi Guys,
    We found the issue regarding our memory leak. It was the query. It
seams were using functions with fetch cursors on large data sets and the
cursors were not getting closed properly. Hence the memory was building up.
So I guess this was an application error. In fact we bought the Query memory
from 1.4 GB to 2 MB.........

Thanks for all the help guys. Because by reading all your comments I was
able to understand a lot about Postgres memory settings.


K.Indika Maligaspe

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mark Kirkwood
Sent: Monday, September 04, 2006 9:23 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgress memory leak with JBoss3.2.6 and large DB

Indika Maligaspe wrote:
> Hey guys,
>
>   We are running a Linux 2.4 enterprise edition box with 6GB of RAM,
> **Postgres 8.0.3**. (snippage)
>

You might want to consider upgrading to 8.0.8 (see below), and seeing if
the problem still persists.


> As you can see the postmaster users are taking way over the memory that
> should be taken.
>
>
> If any of you can give us some pointers we would really appreciate that
> and thanks in advance.
>

I notice that there are a number of fixes for memory leaks since 8.0.3 -
8.0.4 and 8.0.8 is where I see 'em specifically (reading release notes
for 8.0.8). So you may be experiencing an issue that is fixed in the
current 8.0 releases! I recommend upgrading to 8.0.8.

You didn't say what your HW was, but if you are on a 32-bit platform,
then a 2.4 kernel when you have >2G ram may leak noticeable amounts of
memory itself...

Cheers

Mark


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org