Re: Used Memory - Mailing list pgsql-performance

From Christian Paul B. Cosinas
Subject Re: Used Memory
Date
Msg-id 004e01c5d90d$5ffdadb0$1e21100a@ghwk02002147
Whole thread Raw
In response to Re: Used Memory  ("Craig A. James" <cjames@modgraph-usa.com>)
Responses Re: Used Memory  (Mark Kirkwood <markir@paradise.net.nz>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Alan Stange
Date:
Subject: Re: Is There Any Way ....
Next
From: Katherine Stoovs
Date:
Subject: tuning seqscan costs