From cpc@cybees.com Fri Oct 21 03:36:44 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1EC2BDA69D for ; Fri, 21 Oct 2005 00:36:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38033-09 for ; Fri, 21 Oct 2005 03:36:41 +0000 (GMT) Received: from mailer.eglobalreach.net (mail.padalacentral.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 9D607D9E93 for ; Fri, 21 Oct 2005 00:36:40 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9L3acN29123 for ; Fri, 21 Oct 2005 11:36:38 +0800 From: "Christian Paul B. Cosinas" To: Subject: Used Memory Date: Fri, 21 Oct 2005 03:40:47 -0000 Message-ID: <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00A5_01C5D5F1.39F7E870" X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <3cf983d0510181107sb060171je702da5fe0c29252@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXUD9MHSbjkud3OSMqeu5cWHK1SMQB4GQiQ X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=-0.001, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/288 X-Sequence-Number: 15056 This is a multi-part message in MIME format. ------=_NextPart_000_00A5_01C5D5F1.39F7E870 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit HI! I am having a confusion to the memory handling of postgreSQL. Here is the Scenario. I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which is a Dual Xeon Server and 6 gig of memory. Of course there is not much memory still used since it is just restarted. But after a number of access to the tables the memory is being used and it is not being free up. Actually after this access to the database and the server is just idle The memory is still used up. I am monitoring this using the "free" command which gives me about 5.5 gig of used memory and the rest free. Is there something that I should do to minimize and free up the used memory? Thanks You. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ------=_NextPart_000_00A5_01C5D5F1.39F7E870 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

HI!

I am having a confusion to the = memory handling of postgreSQL.

Here is the = Scenario.

I rebooted my Server which is a = PostgreSQL 8.0 Running on Redhat 9, which is a Dual Xeon Server and 6 gig of = memory.

Of course there is not much memory = still used since it is just restarted.

But after a number of access to the = tables the memory is being used and it is not being free up. Actually after = this access to the database and the server is just = idle

The memory is still used up. I am monitoring this using the “free” command which gives me = about 5.5 gig of used memory and the rest free.

Is there something that I should do = to minimize and free up the used memory?

Thanks = You.

I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html ------=_NextPart_000_00A5_01C5D5F1.39F7E870-- From ml+pgsql-performance@asco.de Fri Oct 21 07:23:40 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CDC35DA9AD for ; Fri, 21 Oct 2005 04:23:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23173-06 for ; Fri, 21 Oct 2005 07:23:36 +0000 (GMT) Received: from smtp1.asco.de (smtp1.asco.de [217.13.70.154]) by svr1.postgresql.org (Postfix) with ESMTP id 9F03EDA999 for ; Fri, 21 Oct 2005 04:23:33 -0300 (ADT) Received: from [192.168.1.72] (pitr.asco.de [192.168.1.72]) (envelope-sender: ) (authenticated j_schicke CRAM-MD5 bits=0) by smtp1.asco.de (8.13.4/8.13.4/Debian-3) with ESMTP id j9L7NSdP009314 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Fri, 21 Oct 2005 09:23:28 +0200 Date: Fri, 21 Oct 2005 09:23:28 +0200 From: Jens-Wolfhard Schicke Reply-To: Jens-Wolfhard Schicke To: "Christian Paul B. Cosinas" , pgsql-performance@postgresql.org Subject: Re: Used Memory Message-ID: <28EE02F5BBCA481B51C4E44E@[192.168.1.72]> In-Reply-To: <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> References: <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> X-Mailer: Mulberry/3.1.6 (Linux/x86) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.429 required=5 tests=[AWL=-0.200, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/289 X-Sequence-Number: 15057 --On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. Cosinas"=20 wrote: > I am having a confusion to the memory handling of postgreSQL. > I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which > is a Dual Xeon Server and 6 gig of memory. > > Of course there is not much memory still used since it is just restarted. > > But after a number of access to the tables the memory is being used and > it is not being free up. Actually after this access to the database and > the server is just idle > > The memory is still used up. I am monitoring this using the "free" > command which gives me about 5.5 gig of used memory and the rest free. I suppose you looked at the top row of the free output? Because there the disk-cache is counted as "used"... Have a look at the=20 second row where buffers are counted as free, which they more or less are. > Is there something that I should do to minimize and free up the used > memory? No, the buffers make your database faster because they reduce direct disk=20 access > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html I don't :) Mit freundlichem Gru=DF, Jens Schicke --=20 Jens Schicke j.schicke@asco.de asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 Braunschweig Fax 0531/3906-400 From cpc@cybees.com Fri Oct 21 07:58:31 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B3AEDD6E78 for ; Fri, 21 Oct 2005 04:58:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30720-10 for ; Fri, 21 Oct 2005 07:58:28 +0000 (GMT) Received: from mailer.eglobalreach.net (mail.padalacentral.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 52F59DA9BB for ; Fri, 21 Oct 2005 04:58:28 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9L7wTs26784 for ; Fri, 21 Oct 2005 15:58:29 +0800 From: "Christian Paul B. Cosinas" To: Subject: Re: Used Memory Date: Fri, 21 Oct 2005 08:02:40 -0000 Message-ID: <011101c5d615$cf56b2b0$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <28EE02F5BBCA481B51C4E44E@[192.168.1.72]> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXWEFcoAHG1cVOITHaHyPh019MTKAABS1XQ X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/292 X-Sequence-Number: 15060 But as long as the memory is in the cache my database became much = slower. What could probably be the cause of this? But When I restarted the = database is back to normal processing. -----Original Message----- From: Jens-Wolfhard Schicke [mailto:ml+pgsql-performance@asco.de] Sent: Friday, October 21, 2005 7:23 AM To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory --On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. = Cosinas"=20 wrote: > I am having a confusion to the memory handling of postgreSQL. > I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9,=20 > which is a Dual Xeon Server and 6 gig of memory. > > Of course there is not much memory still used since it is just = restarted. > > But after a number of access to the tables the memory is being used=20 > and it is not being free up. Actually after this access to the=20 > database and the server is just idle > > The memory is still used up. I am monitoring this using the "free" > command which gives me about 5.5 gig of used memory and the rest free. I suppose you looked at the top row of the free output? Because there the disk-cache is counted as "used"... Have a look at the second row where buffers are counted as free, which they more or less = are. > Is there something that I should do to minimize and free up the used=20 > memory? No, the buffers make your database faster because they reduce direct = disk access > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html I don't :) Mit freundlichem Gru=DF, Jens Schicke --=20 Jens Schicke j.schicke@asco.de asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 Braunschweig Fax 0531/3906-400 I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html =20 From cpc@cybees.com Fri Oct 21 08:04:06 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2C884DA983 for ; Fri, 21 Oct 2005 05:04:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36259-02 for ; Fri, 21 Oct 2005 08:04:03 +0000 (GMT) Received: from mailer.eglobalreach.net (mail.padalacentral.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id CD03FDA9C4 for ; Fri, 21 Oct 2005 05:04:03 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9L845X25506 for ; Fri, 21 Oct 2005 16:04:05 +0800 From: "Christian Paul B. Cosinas" To: Subject: Re: Used Memory Date: Fri, 21 Oct 2005 08:08:16 -0000 Message-ID: <011501c5d616$97766330$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <28EE02F5BBCA481B51C4E44E@[192.168.1.72]> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXWEFcoAHG1cVOITHaHyPh019MTKAABiD4A X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/293 X-Sequence-Number: 15061 Also Does Creating Temporary table in a function and not dropping them affects the performance of the database? -----Original Message----- From: Jens-Wolfhard Schicke [mailto:ml+pgsql-performance@asco.de] Sent: Friday, October 21, 2005 7:23 AM To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory --On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. = Cosinas"=20 wrote: > I am having a confusion to the memory handling of postgreSQL. > I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9,=20 > which is a Dual Xeon Server and 6 gig of memory. > > Of course there is not much memory still used since it is just = restarted. > > But after a number of access to the tables the memory is being used=20 > and it is not being free up. Actually after this access to the=20 > database and the server is just idle > > The memory is still used up. I am monitoring this using the "free" > command which gives me about 5.5 gig of used memory and the rest free. I suppose you looked at the top row of the free output? Because there the disk-cache is counted as "used"... Have a look at the second row where buffers are counted as free, which they more or less = are. > Is there something that I should do to minimize and free up the used=20 > memory? No, the buffers make your database faster because they reduce direct = disk access > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html I don't :) Mit freundlichem Gru=DF, Jens Schicke --=20 Jens Schicke j.schicke@asco.de asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 Braunschweig Fax 0531/3906-400 I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html =20 From jon.brisbin@npcinternational.com Fri Oct 21 15:29:40 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9A0EDDA8A3 for ; Fri, 21 Oct 2005 12:29:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28320-01 for ; Fri, 21 Oct 2005 15:29:37 +0000 (GMT) Received: from mail1.npci.com (mail.npcinternational.com [63.76.154.140]) by svr1.postgresql.org (Postfix) with ESMTP id 5E5D2DA79E for ; Fri, 21 Oct 2005 12:29:37 -0300 (ADT) Received: from localhost ([172.16.10.91]) by mail1.npci.com (MOS 3.5.9-GR) with ESMTP id BVU02724 (AUTH via LOGINBEFORESMTP); Fri, 21 Oct 2005 10:24:59 -0500 (CDT) Date: Fri, 21 Oct 2005 10:29:35 -0500 From: Jon Brisbin To: pgsql-performance@postgresql.org Subject: Re: Used Memory Message-ID: <20051021102935.5e4b7113@localhost> In-Reply-To: <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> References: <3cf983d0510181107sb060171je702da5fe0c29252@mail.gmail.com> <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> Organization: NPC International X-Mailer: Sylpheed-Claws 1.9.13 (GTK+ 2.6.7; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.293 required=5 tests=[AWL=0.243, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/298 X-Sequence-Number: 15066 On Fri, 21 Oct 2005 03:40:47 -0000 "Christian Paul B. Cosinas" wrote: > > But after a number of access to the tables the memory is being used > and it is not being free up. Actually after this access to the > database and the server is just idle I noticed this behavior on my SUSE linux box as well. I thought it was a memory leak in something (I think there was an actual memory leak in the kernel shared memory stuff, which I fixed by upgrading my kernel to 2.6.13-ck8). It turns out that some file systems are better than others when it comes to increasing the performance of I/O on Linux. ReiserFS was what I put on originally and by the end of the day, the box would be using all of it's available memory in caching inodes. I kept rebooting and trying to get the memory usage to go down, but it never did. All but 500MB of it was disk cache. I let my apps just run and when the application server needed more memory, it reclaimed it from the disk cache, so there weren't side effects to the fact that top and free always reported full memory usage. They tell me that this is a good thing, as it reduces disk I/O and increases performance. That's all well and good, but it's entirely unnecessary in our situation. Despite that, I can't turn it off because my research into the issue has shown that kernel developers don't want users to be able to turn off disk caching. There is a value in /proc/sys/vm/vfs_cache_pressure that can be changed, which will affect the propensity of the kernel to cache files in RAM (google it to find the suggestions on what value to set it to), but there isn't a setting to turn that off on purpose. After rolling my own CK-based kernel, switching to XFS, and tweaking the nice and CPU affinity of my database process (I use schedtool in my CK kernel to run it at SCHED_FIFO, nice -15, and CPU affinity confined to the second processor in my dual Xeon eServer) has got me to the point that the perpetually high memory usage doesn't affect my application server. Hope any of this helps. Jon Brisbin Webmaster NPC International, Inc. From armtuk@gmail.com Fri Oct 21 15:41:38 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 48103D9D1F for ; Fri, 21 Oct 2005 12:41:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71612-01 for ; Fri, 21 Oct 2005 15:41:35 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by svr1.postgresql.org (Postfix) with ESMTP id 009B1D8307 for ; Fri, 21 Oct 2005 12:41:34 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 71so271027wra for ; Fri, 21 Oct 2005 08:41:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=RJIwPBccX+8uMwh2biDcW6B0T+2VD1w20DX6unkeLRuYLnJrkS5x5yYNlLHax9LKAMPDDGl/iAnBD0r+/fBx7itPlfIcyB+Pon3IMxqegV3EL42CLcoKlGsm9XHqKK4NHyjYWvseRkBfCaP0OiM+4qMG+tWV3lyoChsnnPj1INw= Received: by 10.54.99.10 with SMTP id w10mr1580331wrb; Fri, 21 Oct 2005 08:41:34 -0700 (PDT) Received: by 10.54.86.17 with HTTP; Fri, 21 Oct 2005 08:41:34 -0700 (PDT) Message-ID: <33c6269f0510210841h568f173ar2fa9ea4a7d105905@mail.gmail.com> Date: Fri, 21 Oct 2005 11:41:34 -0400 From: Alex Turner To: Jon Brisbin Subject: Re: Used Memory Cc: pgsql-performance@postgresql.org In-Reply-To: <20051021102935.5e4b7113@localhost> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19692_24632811.1129909294744" References: <3cf983d0510181107sb060171je702da5fe0c29252@mail.gmail.com> <00a401c5d5f1$39f661d0$1e21100a@ghwk02002147> <20051021102935.5e4b7113@localhost> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.152 required=5 tests=[AWL=0.041, HTML_40_50=0.086, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/299 X-Sequence-Number: 15067 ------=_Part_19692_24632811.1129909294744 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline [snip] > > to the second processor in my dual Xeon eServer) has got me to the > point that the perpetually high memory usage doesn't affect my > application server. I'm curious - how does the high memory usage affect your application server= ? Alex ------=_Part_19692_24632811.1129909294744 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline [snip]

to the= second processor in my dual Xeon eServer) has got me to the
point that = the perpetually high memory usage doesn't affect my
application server.

I'm curious - how does the high memory usage affect your application server= ?

Alex

------=_Part_19692_24632811.1129909294744-- From cpc@cybees.com Mon Nov 7 05:58:46 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6D7E4D682C for ; Mon, 7 Nov 2005 01:58:46 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00654-05 for ; Mon, 7 Nov 2005 05:58:43 +0000 (GMT) X-Greylist: delayed 00:20:23.699001 by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 1BD8DD6805 for ; Mon, 7 Nov 2005 01:58:42 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9O1cNK03726 for ; Mon, 24 Oct 2005 09:38:23 +0800 From: "Christian Paul B. Cosinas" To: Subject: Re: Used Memory Date: Mon, 24 Oct 2005 01:42:33 -0000 Message-ID: <02b001c5d83c$34acbba0$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02B1_01C5D83C.34AFC8E0" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 In-Reply-To: <33c6269f0510210841h568f173ar2fa9ea4a7d105905@mail.gmail.com> Thread-Index: AcXWVwx9TKFORE3pS6yM9N43/8G7SwB5OJ9w X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.958 required=5 tests=[AWL=-0.137, HTML_MESSAGE=0.001, URIBL_SBL=1.094] X-Spam-Score: 0.958 X-Spam-Level: X-Archive-Number: 200511/110 X-Sequence-Number: 15367 This is a multi-part message in MIME format. ------=_NextPart_000_02B1_01C5D83C.34AFC8E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit It affect my application since the database server starts to slow down. Hence a very slow in return of functions. Any more ideas about this everyone? Please.. _____ From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Alex Turner Sent: Friday, October 21, 2005 3:42 PM To: Jon Brisbin Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory [snip] to the second processor in my dual Xeon eServer) has got me to the point that the perpetually high memory usage doesn't affect my application server. I'm curious - how does the high memory usage affect your application server? Alex I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ------=_NextPart_000_02B1_01C5D83C.34AFC8E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

It affect my application since the database server starts to slow down. Hence a very slow in return of = functions.

Any more ideas about this = everyone?

Please….

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Alex Turner
Sent: Friday, October 21, = 2005 3:42 PM
To: Jon Brisbin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] = Used Memory

[snip]

to the second processor in my dual Xeon eServer) has got me to = the
point that the perpetually high memory usage doesn't affect my
application server.

I'm curious - how does the high memory usage affect your application = server?

Alex

I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html ------=_NextPart_000_02B1_01C5D83C.34AFC8E0-- From cpc@cybees.com Mon Oct 24 01:42:58 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 241FADAB85 for ; Sun, 23 Oct 2005 22:42:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53402-01 for ; Mon, 24 Oct 2005 01:42:50 +0000 (GMT) Received: from mailer.eglobalreach.net (cybees.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 5B453DAB4B for ; Sun, 23 Oct 2005 22:42:50 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9O1gt603929 for ; Mon, 24 Oct 2005 09:42:55 +0800 From: "Christian Paul B. Cosinas" To: Subject: Re: Used Memory Date: Mon, 24 Oct 2005 01:47:05 -0000 Message-ID: <02b501c5d83c$d6bf0f10$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02B6_01C5D83C.D6BF0F10" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 In-Reply-To: <33c6269f0510210841h568f173ar2fa9ea4a7d105905@mail.gmail.com> Thread-Index: AcXWVwx9TKFORE3pS6yM9N43/8G7SwB5T9QA X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.68 required=5 tests=[AWL=-0.000, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/325 X-Sequence-Number: 15093 This is a multi-part message in MIME format. ------=_NextPart_000_02B6_01C5D83C.D6BF0F10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit total used free shared buffers cached Mem: 6192460 6137424 55036 0 85952 5828844 -/+ buffers/cache: 222628 5969832 Swap: 2096472 0 2096472 Here is the result of "free" command" I am talking about. What does this result mean? I just noticed that as long as the free memory in the first row (which is 55036 as of now) became low, the slower is the response of the database server. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ------=_NextPart_000_02B6_01C5D83C.D6BF0F10 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

total = = used = = free = = shared = buffers = = cached

Mem: &n= bsp; = 6192460 = 6137424 = 55036 = 0 = &= nbsp; 85952 = = 5828844

-/+ = buffers/cache: = &= nbsp; 222628 = 5969832

Swap: = = 2096472 = 0 &= nbsp; 2096472

Here is the result of = “free” command” I am talking about.

What does this result = mean?

I just noticed that as long as the = free memory in the first row (which is 55036 as of now) became low, the = slower is the response of the database server.

I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html ------=_NextPart_000_02B6_01C5D83C.D6BF0F10-- From markir@paradise.net.nz Mon Oct 24 04:15:51 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BB924DABF6 for ; Mon, 24 Oct 2005 01:15:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00373-03 for ; Mon, 24 Oct 2005 04:15:48 +0000 (GMT) Received: from linda-4.paradise.net.nz (bm-4a.paradise.net.nz [203.96.152.183]) by svr1.postgresql.org (Postfix) with ESMTP id 25555DABB6 for ; Mon, 24 Oct 2005 01:15:44 -0300 (ADT) Received: from smtp-1.paradise.net.nz (tclsnelb2-src-1.paradise.net.nz [203.96.152.177]) by linda-4.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IOU006A9GZYS1@linda-4.paradise.net.nz> for pgsql-performance@postgresql.org; Mon, 24 Oct 2005 16:14:22 +1300 (NZDT) Received: from [192.168.1.11] (218-101-13-33.paradise.net.nz [218.101.13.33]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id 7D6B9155F718; Mon, 24 Oct 2005 16:14:22 +1300 (NZDT) Date: Mon, 24 Oct 2005 16:14:20 +1300 From: Mark Kirkwood Subject: Re: Used Memory In-reply-to: <02b501c5d83c$d6bf0f10$1e21100a@ghwk02002147> To: "Christian Paul B. Cosinas" Cc: pgsql-performance@postgresql.org Message-id: <435C518C.8070505@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=windows-1252; format=flowed Content-transfer-encoding: 8BIT X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726) References: <02b501c5d83c$d6bf0f10$1e21100a@ghwk02002147> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.365 required=5 tests=[AWL=-1.096, RCVD_IN_BL_SPAMCOP_NET=1.832, URIBL_SBL=0.629] X-Spam-Level: * X-Archive-Number: 200510/327 X-Sequence-Number: 15095 Christian Paul B. Cosinas wrote: > > > Here is the result of �free� command� I am talking about. > > What does this result mean? > I seem to recall the Linux man page for 'free' being most unenlightening, so have a look at: http://gentoo-wiki.com/FAQ_Linux_Memory_Management (For Gentoo, but should be applicable to RHEL). The basic idea is that modern operating systems try to make as much use of the memory as possible. Postgresql depends on this behavior - e.g. a page that has previously been fetched from disk, will be cached, so it can be read from memory next time, as this is faster(!) > > > I just noticed that as long as the free memory in the first row (which > is 55036 as of now) became low, the slower is the response of the > database server. > Well, you could be swapping - what does the swap line of 'free' show then? Also, how about posting your postgresql.conf (or just the non-default parameters) to this list? Some other stuff that could be relevant: - Is the machine just a database server, or does it run (say) Apache + Php? - When the slowdown is noticed, does this coincide with certain activities - e.g, backup , daily maintenance, data load(!) etc. regards Mark > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html Nope, not me either. From Kevin.Grittner@wicourts.gov Mon Oct 24 15:51:19 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D847DACAA for ; Mon, 24 Oct 2005 12:51:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67530-04 for ; Mon, 24 Oct 2005 15:51:16 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by svr1.postgresql.org (Postfix) with ESMTP id B1AFBDACA3 for ; Mon, 24 Oct 2005 12:51:15 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 24 Oct 2005 10:51:07 -0500 Message-Id: <435CBC9202000025000001AB@gwmta.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0 Date: Mon, 24 Oct 2005 10:50:57 -0500 From: "Kevin Grittner" To: Cc: Subject: Re: Used Memory Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 required=5 tests=[AWL=0.003] X-Spam-Level: X-Archive-Number: 200510/334 X-Sequence-Number: 15102 In addition to what Mark pointed out, there is the possibility that a query is running which is scanning a large table or otherwise bringing in a large number of pages from disk. That would first use up all available unused cache space, and then may start replacing some of your frequently used data. This can cause slowness for some time after the process which flushed the cache, as pages are reread and recached. Keep in mind that the cache could be flushed by some external process, such as copying disk files. The use of free memory for caching is not slowing you down; but if it coincides with slowness, it could be a useful clue. -Kevin From cjames@modgraph-usa.com Mon Oct 24 17:01:58 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5BAACDACB5 for ; Mon, 24 Oct 2005 14:01:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80990-10 for ; Mon, 24 Oct 2005 17:01:56 +0000 (GMT) Received: from moonunit2.moonview.localnet (wsip-68-15-5-150.sd.sd.cox.net [68.15.5.150]) by svr1.postgresql.org (Postfix) with ESMTP id 67243DACB7 for ; Mon, 24 Oct 2005 14:01:55 -0300 (ADT) Received: from [192.168.0.3] (moonunit3.moonview.localnet [192.168.0.3]) by moonunit2.moonview.localnet (8.13.1/8.13.1) with ESMTP id j9OH7Pc9011174 for ; Mon, 24 Oct 2005 10:07:25 -0700 Message-ID: <435D1314.5040807@modgraph-usa.com> Date: Mon, 24 Oct 2005 10:00:04 -0700 From: "Craig A. James" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Used Memory References: <435CBC9202000025000001AB@gwmta.wicourts.gov> In-Reply-To: <435CBC9202000025000001AB@gwmta.wicourts.gov> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 required=5 tests=[AWL=-0.000, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/335 X-Sequence-Number: 15103 Kevin Grittner wrote: > In addition to what Mark pointed out, there is the possibility that a > query > is running which is scanning a large table or otherwise bringing in a > large number of pages from disk. That would first use up all available > unused cache space, and then may start replacing some of your > frequently used data. An LRU cache is often a bad strategy for database applications. There are two illustrations that show why. 1. You have an index that's used for EVERY search, but each search returns a large and unique set of rows. If it happens that the rows returned exceed the systems cache size, the part or all of your index will be flushed with EVERY query. 2. You do a sequential scan of a table that's one block bigger than the file system cache, then you do it again. At the beginning of the second scan, the first block of the table will have just been swapped out because it was the oldest, so the file system brings it back in, replacing the second block, which is now the oldest. As you scan the table, each block of the table is swapped out JUST BEFORE you get to it. At the start of your query, the file system might have had 99.9% of the relevant data in memory, but it swaps out 100% of it as your query progresses. Scenario 2 above is interesting because a system that is performing very well can suddenly experience a catastrophic performance decline when the size of the data exceeds a critical limit - the file system's avaliable cache. LRU works well if your frequently-used data is used often enough to keep it in memory. But many applications don't have that luxury. It's often the case that a single query will exceed the file system's cache size. The file system cache is "dumb" -- it's strategy is too simple for a relational database. What's needed is a way for the application developer to explicitely say, "This object is frequenly used, and I want it kept in memory." Craig From smarlowe@g2switchworks.com Mon Oct 24 20:25:03 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4CE5D850D for ; Mon, 24 Oct 2005 17:25:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45423-06 for ; Mon, 24 Oct 2005 20:24:59 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 47C33D6EE1 for ; Mon, 24 Oct 2005 17:24:58 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 24 Oct 2005 15:24:59 -0500 Subject: Re: Used Memory From: Scott Marlowe To: "Craig A. James" Cc: pgsql-performance@postgresql.org In-Reply-To: <435D1314.5040807@modgraph-usa.com> References: <435CBC9202000025000001AB@gwmta.wicourts.gov> <435D1314.5040807@modgraph-usa.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1130185499.15546.120.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 24 Oct 2005 15:24:59 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 required=5 tests=[AWL=0.007] X-Spam-Level: X-Archive-Number: 200510/337 X-Sequence-Number: 15105 On Mon, 2005-10-24 at 12:00, Craig A. James wrote: > Kevin Grittner wrote: > > In addition to what Mark pointed out, there is the possibility that a > > query > > is running which is scanning a large table or otherwise bringing in a > > large number of pages from disk. That would first use up all available > > unused cache space, and then may start replacing some of your > > frequently used data. > > An LRU cache is often a bad strategy for database applications. There are two illustrations that show why. > > 1. You have an index that's used for EVERY search, but each search returns a large and unique set of rows. If it happens that the rows returned exceed the systems cache size, the part or all of your index will be flushed with EVERY query. > > 2. You do a sequential scan of a table that's one block bigger than the file system cache, then you do it again. At the beginning of the second scan, the first block of the table will have just been swapped out because it was the oldest, so the file system brings it back in, replacing the second block, which is now the oldest. As you scan the table, each block of the table is swapped out JUST BEFORE you get to it. At the start of your query, the file system might have had 99.9% of the relevant data in memory, but it swaps out 100% of it as your query progresses. > > Scenario 2 above is interesting because a system that is performing very well can suddenly experience a catastrophic performance decline when the size of the data exceeds a critical limit - the file system's avaliable cache. > > LRU works well if your frequently-used data is used often enough to keep it in memory. But many applications don't have that luxury. It's often the case that a single query will exceed the file system's cache size. The file system cache is "dumb" -- it's strategy is too simple for a relational database. > > What's needed is a way for the application developer to explicitely say, "This object is frequenly used, and I want it kept in memory." There's an interesting conversation happening on the linux kernel hackers mailing list right about now that applies: http://www.gossamer-threads.com/lists/linux/kernel/580789 From cjames@modgraph-usa.com Mon Oct 24 21:48:57 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 998ACDAD29 for ; Mon, 24 Oct 2005 18:48:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67100-03 for ; Mon, 24 Oct 2005 21:48:52 +0000 (GMT) Received: from moonunit2.moonview.localnet (wsip-68-15-5-150.sd.sd.cox.net [68.15.5.150]) by svr1.postgresql.org (Postfix) with ESMTP id B1169DAD63 for ; Mon, 24 Oct 2005 18:48:53 -0300 (ADT) Received: from [192.168.0.3] (moonunit3.moonview.localnet [192.168.0.3]) by moonunit2.moonview.localnet (8.13.1/8.13.1) with ESMTP id j9OLsNG2011306; Mon, 24 Oct 2005 14:54:23 -0700 Message-ID: <435D5654.2080209@modgraph-usa.com> Date: Mon, 24 Oct 2005 14:47:00 -0700 From: "Craig A. James" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Marlowe Cc: pgsql-performance@postgresql.org Subject: Re: Used Memory References: <435CBC9202000025000001AB@gwmta.wicourts.gov> <435D1314.5040807@modgraph-usa.com> <1130185499.15546.120.camel@state.g2switchworks.com> In-Reply-To: <1130185499.15546.120.camel@state.g2switchworks.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 required=5 tests=[AWL=-0.000, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/338 X-Sequence-Number: 15106 Scott Marlowe wrote: >>What's needed is a way for the application developer to explicitely say, >> "This object is frequenly used, and I want it kept in memory." > > There's an interesting conversation happening on the linux kernel > hackers mailing list right about now that applies: > > http://www.gossamer-threads.com/lists/linux/kernel/580789 Thanks for the pointer. If you're a participant in that mailing list, maybe you could forward this comment... A fundamental flaw in the kernel, which goes WAY back to early UNIX implementations, is that the nice(1) setting of a program only applies to CPU usage, not to other resources. In this case, the file-system cache has no priority, so even if I set postmaster's nice(1) value to a very high priority, any pissant process with the lowest priority possible can come along with a "cat some-big-file >/dev/null" and trash my cached file-system pages. It's essentially a denial-of-service mechanism that's built in to the kernel. The kernel group's discussion on the heuristics of how and when to toss stale cache pages should have a strong nice(1) component to it. A process with a low priority should not be allowed to toss memory from a higher-priority process unless there is no other source of memory. Getting back to Postgres, the same points that the linux kernel group are discussing apply to Postgres. There is simply no way to devise a heuristic that comes even close to what the app developer can tell you. A mechanism that allowed an application to say, "Keep this table in memory" is the only way. App developers should be advised to use it sparingly, because most of the time the system is pretty good at memory management, and such a mechanism hobbles the system's ability to manage. But when it's needed, there is no substitute. Craig From cpc@cybees.com Tue Oct 25 02:39:44 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 86EDCDAE83 for ; Mon, 24 Oct 2005 23:39:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 45476-09 for ; Tue, 25 Oct 2005 02:39:33 +0000 (GMT) Received: from mailer.eglobalreach.net (cybees.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 3CC83DAF62 for ; Mon, 24 Oct 2005 23:35:36 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9P2ZeL27307 for ; Tue, 25 Oct 2005 10:35:40 +0800 From: "Christian Paul B. Cosinas" To: Subject: Re: Used Memory Date: Tue, 25 Oct 2005 02:39:51 -0000 Message-ID: <004e01c5d90d$5ffdadb0$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 In-Reply-To: <435D5654.2080209@modgraph-usa.com> thread-index: AcXY5Zw4tiI7/6KVSPu3LTb7x7fgKAAJsZ5w X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/342 X-Sequence-Number: 15110 Hi To all those who replied. Thank You. I monitor my database server a while ago and found out that memory is used extensively when I am fetching records from the database. I use the command "fetch all" in my VB Code and put it in a recordset.Also in this command the CPU utilization is used extensively. Is there something wrong with my code or is it just the way postgresql is behaving which I cannot do something about it? I just monitor one workstation connecting to the database server and it is already eating up about 20 % of the CPU of database server. Which I think will not be applicable to our system since we have a target of 25 PC connecting to the database server most of the time. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Craig A. James Sent: Monday, October 24, 2005 9:47 PM To: Scott Marlowe Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory Scott Marlowe wrote: >>What's needed is a way for the application developer to explicitely >>say, "This object is frequenly used, and I want it kept in memory." > > There's an interesting conversation happening on the linux kernel > hackers mailing list right about now that applies: > > http://www.gossamer-threads.com/lists/linux/kernel/580789 Thanks for the pointer. If you're a participant in that mailing list, maybe you could forward this comment... A fundamental flaw in the kernel, which goes WAY back to early UNIX implementations, is that the nice(1) setting of a program only applies to CPU usage, not to other resources. In this case, the file-system cache has no priority, so even if I set postmaster's nice(1) value to a very high priority, any pissant process with the lowest priority possible can come along with a "cat some-big-file >/dev/null" and trash my cached file-system pages. It's essentially a denial-of-service mechanism that's built in to the kernel. The kernel group's discussion on the heuristics of how and when to toss stale cache pages should have a strong nice(1) component to it. A process with a low priority should not be allowed to toss memory from a higher-priority process unless there is no other source of memory. Getting back to Postgres, the same points that the linux kernel group are discussing apply to Postgres. There is simply no way to devise a heuristic that comes even close to what the app developer can tell you. A mechanism that allowed an application to say, "Keep this table in memory" is the only way. App developers should be advised to use it sparingly, because most of the time the system is pretty good at memory management, and such a mechanism hobbles the system's ability to manage. But when it's needed, there is no substitute. Craig ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From markir@paradise.net.nz Tue Oct 25 03:06:50 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F38FDADBD for ; Tue, 25 Oct 2005 00:06:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64553-08 for ; Tue, 25 Oct 2005 03:06:48 +0000 (GMT) Received: from linda-4.paradise.net.nz (bm-4a.paradise.net.nz [203.96.152.183]) by svr1.postgresql.org (Postfix) with ESMTP id 01E65DAD9D for ; Tue, 25 Oct 2005 00:06:48 -0300 (ADT) Received: from smtp-3.paradise.net.nz (tclsnelb2-src-1.paradise.net.nz [203.96.152.177]) by linda-4.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IOW0064JBBADJ@linda-4.paradise.net.nz> for pgsql-performance@postgresql.org; Tue, 25 Oct 2005 16:06:46 +1300 (NZDT) Received: from [192.168.1.11] (218-101-13-43.paradise.net.nz [218.101.13.43]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id C49DE7F39F1; Tue, 25 Oct 2005 16:06:45 +1300 (NZDT) Date: Tue, 25 Oct 2005 16:06:43 +1300 From: Mark Kirkwood Subject: Re: Used Memory In-reply-to: <004e01c5d90d$5ffdadb0$1e21100a@ghwk02002147> To: "Christian Paul B. Cosinas" Cc: pgsql-performance@postgresql.org Message-id: <435DA143.3050100@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726) References: <004e01c5d90d$5ffdadb0$1e21100a@ghwk02002147> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.146 required=5 tests=[AWL=0.146] X-Spam-Level: X-Archive-Number: 200510/347 X-Sequence-Number: 15115 Christian Paul B. Cosinas wrote: > Hi To all those who replied. Thank You. > > I monitor my database server a while ago and found out that memory is used > extensively when I am fetching records from the database. I use the command > "fetch all" in my VB Code and put it in a recordset.Also in this command the > CPU utilization is used extensively. > > Is there something wrong with my code or is it just the way postgresql is > behaving which I cannot do something about it? > > I just monitor one workstation connecting to the database server and it is > already eating up about 20 % of the CPU of database server. > > Which I think will not be applicable to our system since we have a target of > 25 PC connecting to the database server most of the time. > Could you post the query and the output of EXPLAIN ANALYZE? In addition, have you run ANALYZE on all the tables in that database ? (sorry, have to ask :-) ....). cheers Mark From cpc@cybees.com Tue Oct 25 03:16:10 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D00E7DA78F for ; Tue, 25 Oct 2005 00:16:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66818-08 for ; Tue, 25 Oct 2005 03:16:05 +0000 (GMT) Received: from mailer.eglobalreach.net (cybees.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 5BA85DADE6 for ; Tue, 25 Oct 2005 00:16:04 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9P3FwP29153; Tue, 25 Oct 2005 11:15:58 +0800 From: "Christian Paul B. Cosinas" To: "'Mark Kirkwood'" , Subject: Re: Used Memory Date: Tue, 25 Oct 2005 03:20:09 -0000 Message-ID: <007801c5d913$018d2340$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 In-Reply-To: <435DA143.3050100@paradise.net.nz> thread-index: AcXZESPnDPtb0QZ8QoylE3m0wHj/cQAAWFXA X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/348 X-Sequence-Number: 15116 Hi mark I have so many functions, more than 100 functions in the database :) And I am dealing about 3 million of records in one database. And about 100 databases :) -----Original Message----- From: Mark Kirkwood [mailto:markir@paradise.net.nz] Sent: Tuesday, October 25, 2005 3:07 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory Christian Paul B. Cosinas wrote: > Hi To all those who replied. Thank You. > > I monitor my database server a while ago and found out that memory is > used extensively when I am fetching records from the database. I use > the command "fetch all" in my VB Code and put it in a recordset.Also > in this command the CPU utilization is used extensively. > > Is there something wrong with my code or is it just the way postgresql > is behaving which I cannot do something about it? > > I just monitor one workstation connecting to the database server and > it is already eating up about 20 % of the CPU of database server. > > Which I think will not be applicable to our system since we have a > target of > 25 PC connecting to the database server most of the time. > Could you post the query and the output of EXPLAIN ANALYZE? In addition, have you run ANALYZE on all the tables in that database ? (sorry, have to ask :-) ....). cheers Mark I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From markir@paradise.net.nz Tue Oct 25 04:14:42 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C6FABDAD10 for ; Tue, 25 Oct 2005 01:14:42 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83128-02 for ; Tue, 25 Oct 2005 04:14:40 +0000 (GMT) Received: from linda-5.paradise.net.nz (bm-5a.paradise.net.nz [203.96.152.184]) by svr1.postgresql.org (Postfix) with ESMTP id 3CC05DA8B4 for ; Tue, 25 Oct 2005 01:14:39 -0300 (ADT) Received: from smtp-3.paradise.net.nz (tclsnelb2-src-1.paradise.net.nz [203.96.152.177]) by linda-5.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IOW00CRUEGE34@linda-5.paradise.net.nz> for pgsql-performance@postgresql.org; Tue, 25 Oct 2005 17:14:38 +1300 (NZDT) Received: from [192.168.1.11] (218-101-13-43.paradise.net.nz [218.101.13.43]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id 122FFFA50AE; Tue, 25 Oct 2005 17:14:38 +1300 (NZDT) Date: Tue, 25 Oct 2005 17:14:35 +1300 From: Mark Kirkwood Subject: Re: Used Memory In-reply-to: <007801c5d913$018d2340$1e21100a@ghwk02002147> To: "Christian Paul B. Cosinas" Cc: pgsql-performance@postgresql.org Message-id: <435DB12B.5030901@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726) References: <007801c5d913$018d2340$1e21100a@ghwk02002147> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.144 required=5 tests=[AWL=0.144] X-Spam-Level: X-Archive-Number: 200510/349 X-Sequence-Number: 15117 Christian Paul B. Cosinas wrote: > Hi mark > > I have so many functions, more than 100 functions in the database :) And I > am dealing about 3 million of records in one database. > And about 100 databases :) > LOL - sorry, mis-understood your previous message to mean you had identified *one* query where 'fetch all' was causing the problem! Having said that, to make much more progress, you probably want to identify those queries that are consuming your resource, pick one of two of the particularly bad ones and post 'em. There are a number of ways to perform said identification, enabling stats collection might be worth a try. regards Mark From cpc@cybees.com Tue Oct 25 07:24:41 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4D008DADA7 for ; Tue, 25 Oct 2005 04:24:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27693-01 for ; Tue, 25 Oct 2005 07:24:38 +0000 (GMT) Received: from mailer.eglobalreach.net (cybees.com [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id CD3C4DADC8 for ; Tue, 25 Oct 2005 04:24:38 -0300 (ADT) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id j9P7OdO25014 for ; Tue, 25 Oct 2005 15:24:39 +0800 From: "Christian Paul B. Cosinas" To: Subject: FW: Used Memory Date: Tue, 25 Oct 2005 07:28:51 -0000 Message-ID: <001701c5d935$bfc21970$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXYSRQEEzZVhwc+RZSn0P4xxglPcQAA3w8wADpH21A= X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[AWL=0.000, FORGED_RCVD_HELO=0.05, URIBL_SBL=0.629] X-Spam-Level: X-Archive-Number: 200510/351 X-Sequence-Number: 15119 Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html From cpc@cybees.com Mon Nov 7 07:31:10 2005 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DED92D682F for ; Mon, 7 Nov 2005 03:31:10 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32618-01 for ; Mon, 7 Nov 2005 07:31:07 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mailer.eglobalreach.net (mail.heliustech.net [202.124.128.22]) by svr1.postgresql.org (Postfix) with ESMTP id 2FDE0D680C for ; Mon, 7 Nov 2005 03:31:06 -0400 (AST) Received: from ghwk02002147 (localhost [127.0.0.1] (may be forged)) by mailer.eglobalreach.net (8.11.6p2/) with ESMTP id jA77UN304246 for ; Mon, 7 Nov 2005 15:30:23 +0800 From: "Christian Paul B. Cosinas" To: Subject: FW: Used Memory Date: Mon, 7 Nov 2005 15:34:41 -0000 Message-ID: <004001c5e3b0$c5f368a0$1e21100a@ghwk02002147> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 Thread-Index: AcXYSRQEEzZVhwc+RZSn0P4xxglPcQAA3w8wAtkKxYA= X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.717 required=5 tests=[AWL=-0.875, DATE_IN_FUTURE_06_12=1.498, URIBL_SBL=1.094] X-Spam-Score: 1.717 X-Spam-Level: * X-Archive-Number: 200511/112 X-Sequence-Number: 15369 Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. -----Original Message----- From: Mark Kirkwood [mailto:markir@paradise.net.nz] Sent: Monday, October 24, 2005 3:14 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory > > > I just noticed that as long as the free memory in the first row (which > is 55036 as of now) became low, the slower is the response of the > database server. > Also, how about posting your postgresql.conf (or just the non-default parameters) to this list? Some other stuff that could be relevant: - Is the machine just a database server, or does it run (say) Apache + Php? - When the slowdown is noticed, does this coincide with certain activities - e.g, backup , daily maintenance, data load(!) etc. regards Mark > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html Nope, not me either. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html