pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUsed Memory
19+ messages / 8 participants
[nested] [flat]
* Used Memory
@ 2005-10-21 03:40 Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 07:23 ` Re: Used Memory Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
2005-10-21 15:29 ` Re: Used Memory Jon Brisbin <jon.brisbin@npcinternational.com>
0 siblings, 2 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-21 03:40 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-21 07:23 ` Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
2005-10-21 08:02 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 08:08 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 2 replies; 19+ messages in thread
From: Jens-Wolfhard Schicke @ 2005-10-21 07:23 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; pgsql-performance
--On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. Cosinas"
<cpc@cybees.com> 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
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
access
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't :)
Mit freundlichem Gruß,
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 07:23 ` Re: Used Memory Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
@ 2005-10-21 08:02 ` Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 0 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-21 08:02 UTC (permalink / raw)
To: pgsql-performance
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"
<cpc@cybees.com> 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
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
access
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't :)
Mit freundlichem Gruß,
Jens Schicke
--
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 07:23 ` Re: Used Memory Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
@ 2005-10-21 08:08 ` Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 0 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-21 08:08 UTC (permalink / raw)
To: pgsql-performance
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"
<cpc@cybees.com> 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
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
access
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't :)
Mit freundlichem Gruß,
Jens Schicke
--
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-21 15:29 ` Jon Brisbin <jon.brisbin@npcinternational.com>
2005-10-21 15:41 ` Re: Used Memory Alex Turner <armtuk@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Jon Brisbin @ 2005-10-21 15:29 UTC (permalink / raw)
To: pgsql-performance
On Fri, 21 Oct 2005 03:40:47 -0000
"Christian Paul B. Cosinas" <cpc@cybees.com> 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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 15:29 ` Re: Used Memory Jon Brisbin <jon.brisbin@npcinternational.com>
@ 2005-10-21 15:41 ` Alex Turner <armtuk@gmail.com>
2005-10-24 01:42 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-24 01:47 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 2 replies; 19+ messages in thread
From: Alex Turner @ 2005-10-21 15:41 UTC (permalink / raw)
To: Jon Brisbin <jon.brisbin@npcinternational.com>; +Cc: pgsql-performance
[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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 15:29 ` Re: Used Memory Jon Brisbin <jon.brisbin@npcinternational.com>
2005-10-21 15:41 ` Re: Used Memory Alex Turner <armtuk@gmail.com>
@ 2005-10-24 01:42 ` Christian Paul B. Cosinas <cpc@cybees.com>
1 sibling, 0 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-24 01:42 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 15:29 ` Re: Used Memory Jon Brisbin <jon.brisbin@npcinternational.com>
2005-10-21 15:41 ` Re: Used Memory Alex Turner <armtuk@gmail.com>
@ 2005-10-24 01:47 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-24 03:14 ` Re: Used Memory Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-24 01:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 15:29 ` Re: Used Memory Jon Brisbin <jon.brisbin@npcinternational.com>
2005-10-21 15:41 ` Re: Used Memory Alex Turner <armtuk@gmail.com>
2005-10-24 01:47 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-24 03:14 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-10-24 03:14 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
@ 2005-10-24 15:50 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 19+ messages in thread
From: Kevin Grittner @ 2005-10-24 15:50 UTC (permalink / raw)
To: cpc@cybees.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2005-10-24 17:00 ` Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 19+ messages in thread
From: Craig A. James @ 2005-10-24 17:00 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
@ 2005-10-24 20:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 19+ messages in thread
From: Scott Marlowe @ 2005-10-24 20:24 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-10-24 21:47 ` Craig A. James <cjames@modgraph-usa.com>
2005-10-25 02:39 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 1 reply; 19+ messages in thread
From: Craig A. James @ 2005-10-24 21:47 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
@ 2005-10-25 02:39 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 03:06 ` Re: Used Memory Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-25 02:39 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-25 02:39 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-25 03:06 ` Mark Kirkwood <markir@paradise.net.nz>
2005-10-25 03:20 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 1 reply; 19+ messages in thread
From: Mark Kirkwood @ 2005-10-25 03:06 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-25 02:39 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 03:06 ` Re: Used Memory Mark Kirkwood <markir@paradise.net.nz>
@ 2005-10-25 03:20 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 04:14 ` Re: Used Memory Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-25 03:20 UTC (permalink / raw)
To: 'Mark Kirkwood' <markir@paradise.net.nz>; pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Used Memory
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Re: Used Memory Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Re: Used Memory Craig A. James <cjames@modgraph-usa.com>
2005-10-25 02:39 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 03:06 ` Re: Used Memory Mark Kirkwood <markir@paradise.net.nz>
2005-10-25 03:20 ` Re: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-25 04:14 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-10-25 04:14 UTC (permalink / raw)
To: Christian Paul B. Cosinas <cpc@cybees.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* FW: Used Memory
@ 2005-10-25 07:28 Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-10-25 07:28 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* FW: Used Memory
@ 2005-11-07 15:34 Christian Paul B. Cosinas <cpc@cybees.com>
0 siblings, 0 replies; 19+ messages in thread
From: Christian Paul B. Cosinas @ 2005-11-07 15:34 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2005-11-07 15:34 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-21 03:40 Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 07:23 ` Jens-Wolfhard Schicke <ml+pgsql-performance@asco.de>
2005-10-21 08:02 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 08:08 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-21 15:29 ` Jon Brisbin <jon.brisbin@npcinternational.com>
2005-10-21 15:41 ` Alex Turner <armtuk@gmail.com>
2005-10-24 01:42 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-24 01:47 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-24 03:14 ` Mark Kirkwood <markir@paradise.net.nz>
2005-10-24 15:50 Re: Used Memory Kevin Grittner <Kevin.Grittner@wicourts.gov>
2005-10-24 17:00 ` Craig A. James <cjames@modgraph-usa.com>
2005-10-24 20:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-24 21:47 ` Craig A. James <cjames@modgraph-usa.com>
2005-10-25 02:39 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 03:06 ` Mark Kirkwood <markir@paradise.net.nz>
2005-10-25 03:20 ` Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-25 04:14 ` Mark Kirkwood <markir@paradise.net.nz>
2005-10-25 07:28 FW: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
2005-11-07 15:34 FW: Used Memory Christian Paul B. Cosinas <cpc@cybees.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox