X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 013AE9FB2A3 for ; Thu, 31 Aug 2006 14:22:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 21120-01 for ; Thu, 31 Aug 2006 14:22:40 -0300 (ADT) X-Greylist: delayed 00:31:00.54097 by SQLgrey- Received: from omta18.mta.everyone.net (sitemail2.everyone.net [216.200.145.36]) by postgresql.org (Postfix) with ESMTP id 6CA6A9FB3F4 for ; Thu, 31 Aug 2006 14:22:38 -0300 (ADT) Received: from dm17.mta.everyone.net (bigiplb-dsnat [172.16.0.19]) by omta18.mta.everyone.net (Postfix) with ESMTP id E823A424F6 for ; Thu, 31 Aug 2006 09:51:36 -0700 (PDT) X-Eon-Dm: dm17 Received: by dm17.mta.everyone.net (EON-AUTHRELAY2 - cbbd48ba) id dm17.44ee3978.10d462 for ; Thu, 31 Aug 2006 09:51:34 -0700 X-Eon-Sig: AQJKldRE9xOW7yxdNAIAAAAB,e7ce9f4ddbc3c7d0d1010dd09032c6b2 From: "Indika Maligaspe" To: Subject: Postgress memory leak with JBoss3.2.6 and large DB Date: Thu, 31 Aug 2006 22:22:48 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01C6CD4B.FF2EDB60" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 Thread-Index: AcbNHeN6SitDIZqRQiqu/iuIeYIAkQ== Message-Id: <20060831095135.4292E9D7@dm17.mta.everyone.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200608/490 X-Sequence-Number: 20675 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C6CD4B.FF2EDB60 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_0009_01C6CD4B.FF2EDB60 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D 5000    =

effective_cache_size =3D = 10000    

work_mem =3D  = 2048           &nb= sp;           &nbs= p;       =20

random_page_cost =3D 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           &nb= sp;     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.

------=_NextPart_000_0009_01C6CD4B.FF2EDB60--