pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMemory and/or cache issues?
27+ messages / 10 participants
[nested] [flat]
* Memory and/or cache issues?
@ 2006-05-05 11:49 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 13:25 ` Re: Memory and/or cache issues? Tom Lane <tgl@sss.pgh.pa.us>
2006-05-06 04:33 ` Re: Memory and/or cache issues? David Boreham <david_list@boreham.org>
0 siblings, 2 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 11:49 UTC (permalink / raw)
To: pgsql-performance
Good morning,
First the stats: I'm using PostgreSQL 8.0.1 (I know I should upgrade,
cannot due to vendor app. restrictions...), RedHat 9 on a SUN V40Z with 8GB
of memory. I'm using the "out-of-the-box" settings in postgresql.conf.
I've been testing various changes but cannot increase anything to improve
performance till I get this memory leak and/or cache issue resolved.
Scenario: Last night the backup of my largest DB failed (4.4GB in size with
44Million+ tuples) with a memory alloc error. I'll attach it at the end of
this email. Once we rebooted the box and freed memory all was well, the
backup completed fine but as the backup ran and I did a few minor queries
all of a sudden 3+GB of memory was used up! I then performed my nightly
vacuumdb with analyze and just about the remaining 4GB of memory was gone!
This was the only application running in the machine at the time.
Questions:
1. I thought using such "smallish" setting as provided would cause postgres
to go to swap instead of eating up all the memory?
2. If PostgreSQL is the culprit (which I hope it is not) does postgres
release any memory it assumes during processing when that processing is
complete? Such as the backup and vacuumdb I mentioned?
3. Does anyone know of a way to determine if it actually is postgres hogging
this memory? Using TOP I only see my postgres processes using 1% or 2% of
memory. It would be nice to have a tool that showed exactly what is eating
up that 7+GB?
4. IS this due to my low setting in postgresql.conf?
Any and all help is welcomed. For you PostgreSQL purists out there of whom
I am fast becoming, your help is needed as my company is considering dumping
postgresql in favor of Oracle.....I would much rather figure out the issue
then switch DBs. Here is the error received from the failed backup and the
second was noted in my pg_log file:
pg_dump: ERROR: invalid memory alloc request size 18446744073709551613
pg_dump: SQL command to dump the contents of table "msgstate" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR: invalid memory alloc request
size 18446744073709551613
pg_dump: The command was: COPY public.msgstate (id, connectormsgid,
parentid, orderidfk, clordid, orgclordid, msg, rawmsg, msgtype, "action",
sendstate, statechain, fromdest, todest, inserted, op_id, released, reason,
outgoing, symbol, qty, price, stopprice, side, data1, data2, data3, data4,
data5) TO stdout;
2006-05-04 18:04:58 EDT USER=postgres DB=FIX1 [12427] PORT = [local] ERROR:
invalid memory alloc request size 18446744073709551613
Thank you,
Tim McElroy
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 11:49 Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-05 13:31 ` Re: Memory and/or cache issues? Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 1 reply; 27+ messages in thread
From: Tom Lane @ 2006-05-05 13:25 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> pg_dump: ERROR: invalid memory alloc request size 18446744073709551613
That looks more like a corrupt-data problem than anything directly to do
with having or not having enough memory.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 11:49 Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 13:25 ` Re: Memory and/or cache issues? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-05-05 13:31 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 27+ messages in thread
From: Alvaro Herrera @ 2006-05-05 13:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mcelroy, tim <tim.mcelroy@bostonstock.com>; pgsql-performance
Tom Lane wrote:
> "mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> > pg_dump: ERROR: invalid memory alloc request size 18446744073709551613
>
> That looks more like a corrupt-data problem than anything directly to do
> with having or not having enough memory.
The bit pattern is certainly suspicious, though I'll grant that it
doesn't mean anything.
$ dc
2 o
18446744073709551613 p
1111111111111111111111111111111111111111111111111111111111111101
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 11:49 Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-06 04:33 ` David Boreham <david_list@boreham.org>
1 sibling, 0 replies; 27+ messages in thread
From: David Boreham @ 2006-05-06 04:33 UTC (permalink / raw)
To: pgsql-performance
> 2006-05-04 18:04:58 EDT USER=postgres DB=FIX1 [12427] PORT = [local]
> ERROR: invalid memory alloc request size 18446744073709551613
Perhaps I'm off beam here, but any time I've seen an app try to allocate
a gazillion bytes, it's
due to some code incorrectly calculating the size of something (or more
commonly, using an
initialized variable as the basis for said calculation).
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 13:26 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 13:43 ` Re: Memory and/or cache issues? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 13:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks Tom. I thought the same thing and waded through the archives trying
various fixes such as vacuum, vacuum full (both with analyze), reindex and
still the same issue. However, once the box was rebooted the backup went
smooth and the data was fine. We have two (2) machines (PROD001 & PROD002)
that are "in-sync" and the data matched exactly. PROD002 was where I had
the problem. I see this on all the postgres installations, no matter what I
set the postgresql.conf settings to regarding memory allocation, once
postgres starts up 95% of the memory on the box is used. Is there a way
within Linux to 'see' what or who is actually using this memory? I would
love to say it's a hardware thing and that postgres is fine :)
Regards,
Tim
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 05, 2006 9:25 AM
To: mcelroy, tim
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> pg_dump: ERROR: invalid memory alloc request size 18446744073709551613
That looks more like a corrupt-data problem than anything directly to do
with having or not having enough memory.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 13:26 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 13:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 27+ messages in thread
From: Tom Lane @ 2006-05-05 13:43 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> I see this on all the postgres installations, no matter what I
> set the postgresql.conf settings to regarding memory allocation, once
> postgres starts up 95% of the memory on the box is used. Is there a way
> within Linux to 'see' what or who is actually using this memory?
Probably kernel disk cache. Are you under the misimpression that unused
memory is a good thing? If a Unix-ish system *isn't* showing near zero
free memory under load, the kernel is wasting valuable resources.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 13:57 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:23 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
2006-05-05 14:31 ` Re: Memory and/or cache issues? Dave Dutcher <dave@tridecap.com>
0 siblings, 2 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 13:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Are you saying the kernel's disc cache may be getting whacked? No, I
understand that PG should use as much memory as it can and the system as
well. The main problem here is that with almost all the 8GB of RAM 'in use'
when I try to do a pg_dump or vacuumdb I run out of memory and the system
crashes....
I well understand that unused memory is not a good thing, just that when you
have none and can't do the maint work....bad stuff happens. For example, I
just created a benchdb on my DEV box with 1,000,000 tuples. As this ran the
mem in use jumped up 1G and it hasn't gone down? Once the PG process has
finished its task shouldn't it release the memory it used?
Thanks,
Tim
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 05, 2006 9:44 AM
To: mcelroy, tim
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> I see this on all the postgres installations, no matter what I
> set the postgresql.conf settings to regarding memory allocation, once
> postgres starts up 95% of the memory on the box is used. Is there a way
> within Linux to 'see' what or who is actually using this memory?
Probably kernel disk cache. Are you under the misimpression that unused
memory is a good thing? If a Unix-ish system *isn't* showing near zero
free memory under load, the kernel is wasting valuable resources.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 13:57 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 14:23 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 27+ messages in thread
From: Michael Stone @ 2006-05-05 14:23 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 09:57:58AM -0400, mcelroy, tim wrote:
>Are you saying the kernel's disc cache may be getting whacked? No, I
>understand that PG should use as much memory as it can and the system as
>well. The main problem here is that with almost all the 8GB of RAM 'in use'
>when I try to do a pg_dump or vacuumdb I run out of memory and the system
>crashes....
You need to be way more specific about what "in use" means. Try pasting
the output of actual commands like "free". The main problem here
according to the output you sent is that your process is trying to
allocate 10billion terabytes of RAM (which ain't gonna work) and dies.
That is not a memory issue.
Mike Stone
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 13:57 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 14:31 ` Dave Dutcher <dave@tridecap.com>
1 sibling, 0 replies; 27+ messages in thread
From: Dave Dutcher @ 2006-05-05 14:31 UTC (permalink / raw)
To: 'mcelroy, tim' <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
For a standard config most of the memory used by Postgres is the shared
buffers. The shared buffers are a cache to store blocks read from the
disk, so if you do a query, Postgres will allocate and fill the shared
buffers up to the max amount you set in your postgresql.conf file.
Postgres doesn't release that memory between queries because the point
is to be able to pull data from ram instead of the disk on the next
query.
Are you sure your settings in postgresql.conf are standard? What are
your settings for shared_buffers and work_mem?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of mcelroy,
tim
Sent: Friday, May 05, 2006 8:58 AM
To: 'Tom Lane'
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
Are you saying the kernel's disc cache may be getting whacked? No, I
understand that PG should use as much memory as it can and the system as
well. The main problem here is that with almost all the 8GB of RAM 'in
use' when I try to do a pg_dump or vacuumdb I run out of memory and the
system crashes....
I well understand that unused memory is not a good thing, just that when
you have none and can't do the maint work....bad stuff happens. For
example, I just created a benchdb on my DEV box with 1,000,000 tuples.
As this ran the mem in use jumped up 1G and it hasn't gone down? Once
the PG process has finished its task shouldn't it release the memory it
used?
Thanks,
Tim
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 05, 2006 9:44 AM
To: mcelroy, tim
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> I see this on all the postgres installations, no matter what I
> set the postgresql.conf settings to regarding memory allocation, once
> postgres starts up 95% of the memory on the box is used. Is there a
way
> within Linux to 'see' what or who is actually using this memory?
Probably kernel disk cache. Are you under the misimpression that unused
memory is a good thing? If a Unix-ish system *isn't* showing near zero
free memory under load, the kernel is wasting valuable resources.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 14:27 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:40 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
2006-05-05 14:51 ` Re: Memory and/or cache issues? Craig A. James <cjames@modgraph-usa.com>
2006-05-06 00:35 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 3 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 14:27 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
Sorry, been up all night and maybe provided too much information or not the
right information and only confused folks, tired I guess. When I say 'in
use' I am referring to the 'used' column. Thanks all who have responded to
this inquiry, I appreciate it.
Here's free from PROD001:
[root@wbibsngwyprod001 kernel]# free -k -t
total used free shared buffers cached
Mem: 7643536 6975772 667764 0 165496 5393396
-/+ buffers/cache: 1416880 6226656
Swap: 8185108 5208 8179900
Total: 15828644 6980980 8847664
Here's free from PROD002:
[root@wbibsngwyprod002 root]# free -k -t
total used free shared buffers cached
Mem: 7643536 6694220 949316 0 161008 4916420
-/+ buffers/cache: 1616792 6026744
Swap: 8185108 11584 8173524
Total: 15828644 6705804 9122840
Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Michael Stone
Sent: Friday, May 05, 2006 10:24 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
On Fri, May 05, 2006 at 09:57:58AM -0400, mcelroy, tim wrote:
>Are you saying the kernel's disc cache may be getting whacked? No, I
>understand that PG should use as much memory as it can and the system as
>well. The main problem here is that with almost all the 8GB of RAM 'in
use'
>when I try to do a pg_dump or vacuumdb I run out of memory and the system
>crashes....
You need to be way more specific about what "in use" means. Try pasting
the output of actual commands like "free". The main problem here
according to the output you sent is that your process is trying to
allocate 10billion terabytes of RAM (which ain't gonna work) and dies.
That is not a memory issue.
Mike Stone
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:27 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 14:40 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-06 00:31 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
2 siblings, 1 reply; 27+ messages in thread
From: Michael Stone @ 2006-05-05 14:40 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote:
>Sorry, been up all night and maybe provided too much information or not the
>right information and only confused folks, tired I guess. When I say 'in
>use' I am referring to the 'used' column.
Which is a mostly irrelevant number.
>Here's free from PROD001:
>[root@wbibsngwyprod001 kernel]# free -k -t
> total used free shared buffers cached
>Mem: 7643536 6975772 667764 0 165496 5393396
>-/+ buffers/cache: 1416880 6226656
>Swap: 8185108 5208 8179900
>Total: 15828644 6980980 8847664
You've got 1.4G in use, 5.3G of disk cache, 165M of buffers and 667M
free. That doesn't seem unreasonable. If an application needs more
memory the amount of disk cache will decrease. As I said in an earlier
email, the problem is that the application is trying to allocate a bogus
amount of memory, not that you have a memory problem.
Mike Stone
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:27 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:40 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
@ 2006-05-06 00:31 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 27+ messages in thread
From: Jim C. Nasby @ 2006-05-06 00:31 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 10:40:33AM -0400, Michael Stone wrote:
> You've got 1.4G in use, 5.3G of disk cache, 165M of buffers and 667M
> free. That doesn't seem unreasonable. If an application needs more
Actually, it indiciates a bunch of memory not being used, but IIRC Tim's
database is approximately 4G in size, so the 5.3G of disk cache makes
sense if the system was recently rebooted.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:27 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 14:51 ` Craig A. James <cjames@modgraph-usa.com>
2 siblings, 0 replies; 27+ messages in thread
From: Craig A. James @ 2006-05-05 14:51 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
mcelroy, tim wrote:
> Sorry, been up all night and maybe provided too much information or not
> the right information and only confused folks, tired I guess. When I
> say 'in use' I am referring to the 'used' column. Thanks all who have
> responded to this inquiry, I appreciate it.
>
> Here's free from PROD001:
> [root@wbibsngwyprod001 kernel]# free -k -t
> total used free shared buffers cached
> Mem: 7643536 6975772 667764 0 165496 5393396
> -/+ buffers/cache: 1416880 6226656
> Swap: 8185108 5208 8179900
> Total: 15828644 6980980 8847664
On Linux (unlike most Unix systems), "used" includes both processes AND the kernel's file-system buffers, which means "used" will almost always be close to 100%. Starting with a freshly-booted system, you can issue almost any command that scans files, and "used" will go up and STAY at nearly 100% of memory. For example, reboot and try "tar cf - / >/dev/null" and you'll see the same sort of "used" numbers.
In My Humble Opinion, this is a mistake in Linux. This confuses just about everyone the first time they see it (including me), because the file-system buffers are dynamic and will be relenquished by the kernel if another process needs memory. On Unix systems, "used" means, "someone else is using it and you can't have it", which is what most of us really want to know.
Craig
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:27 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-06 00:35 ` Jim C. Nasby <jnasby@pervasive.com>
2 siblings, 0 replies; 27+ messages in thread
From: Jim C. Nasby @ 2006-05-06 00:35 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote:
> Sorry, been up all night and maybe provided too much information or not the
Do you have any budget for support or training, either from the company
selling you the app or a company that provides PostgreSQL support? I
suspect some money invested there would result in a lot less
frustration. It'd also certainly be cheaper than switching to Oracle.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 14:36 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 14:36 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
On the boxes in question the settings are:
shared_buffers = 1000
work_mem = 1024
I have revised these on my DEV box and see some improvement (a quick thank
you to Jim Nasby for his assistance with that):
shared_buffers = 20000
work_mem = 8024
Regards,
Tim
-----Original Message-----
From: Dave Dutcher [mailto:dave@tridecap.com]
Sent: Friday, May 05, 2006 10:32 AM
To: 'mcelroy, tim'
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Memory and/or cache issues?
For a standard config most of the memory used by Postgres is the shared
buffers. The shared buffers are a cache to store blocks read from the disk,
so if you do a query, Postgres will allocate and fill the shared buffers up
to the max amount you set in your postgresql.conf file. Postgres doesn't
release that memory between queries because the point is to be able to pull
data from ram instead of the disk on the next query.
Are you sure your settings in postgresql.conf are standard? What are your
settings for shared_buffers and work_mem?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of mcelroy, tim
Sent: Friday, May 05, 2006 8:58 AM
To: 'Tom Lane'
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
Are you saying the kernel's disc cache may be getting whacked? No, I
understand that PG should use as much memory as it can and the system as
well. The main problem here is that with almost all the 8GB of RAM 'in use'
when I try to do a pg_dump or vacuumdb I run out of memory and the system
crashes....
I well understand that unused memory is not a good thing, just that when you
have none and can't do the maint work....bad stuff happens. For example, I
just created a benchdb on my DEV box with 1,000,000 tuples. As this ran the
mem in use jumped up 1G and it hasn't gone down? Once the PG process has
finished its task shouldn't it release the memory it used?
Thanks,
Tim
-----Original Message-----
From: Tom Lane [ mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> ]
Sent: Friday, May 05, 2006 9:44 AM
To: mcelroy, tim
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
"mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> I see this on all the postgres installations, no matter what I
> set the postgresql.conf settings to regarding memory allocation, once
> postgres starts up 95% of the memory on the box is used. Is there a way
> within Linux to 'see' what or who is actually using this memory?
Probably kernel disk cache. Are you under the misimpression that unused
memory is a good thing? If a Unix-ish system *isn't* showing near zero
free memory under load, the kernel is wasting valuable resources.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 14:45 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:53 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 14:45 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; pgsql-performance
Thanks Michael. Are you saying the 'used' column is the irrelevant number?
Is the number that is more pertinent is 1416880? Is that the actual amount
of memory in use? I agree about the allocation of a bogus amount of memory
but the issue occurred after-hours when the application(s) were not running.
Or are you saying the app whacked the DB during the day and never recovered?
Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Michael Stone
Sent: Friday, May 05, 2006 10:41 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote:
>Sorry, been up all night and maybe provided too much information or not the
>right information and only confused folks, tired I guess. When I say 'in
>use' I am referring to the 'used' column.
Which is a mostly irrelevant number.
>Here's free from PROD001:
>[root@wbibsngwyprod001 kernel]# free -k -t
> total used free shared buffers cached
>Mem: 7643536 6975772 667764 0 165496 5393396
>-/+ buffers/cache: 1416880 6226656
>Swap: 8185108 5208 8179900
>Total: 15828644 6980980 8847664
You've got 1.4G in use, 5.3G of disk cache, 165M of buffers and 667M
free. That doesn't seem unreasonable. If an application needs more
memory the amount of disk cache will decrease. As I said in an earlier
email, the problem is that the application is trying to allocate a bogus
amount of memory, not that you have a memory problem.
Mike Stone
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-05 14:53 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-05 16:33 ` Re: Memory and/or cache issues? Gábriel Ákos <akos.gabriel@i-logic.hu>
0 siblings, 1 reply; 27+ messages in thread
From: Michael Stone @ 2006-05-05 14:53 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 10:45:21AM -0400, mcelroy, tim wrote:
>Thanks Michael. Are you saying the 'used' column is the irrelevant number?
>Is the number that is more pertinent is 1416880? Is that the actual amount
>of memory in use?
Yes.
>I agree about the allocation of a bogus amount of memory
>but the issue occurred after-hours when the application(s) were not running.
>Or are you saying the app whacked the DB during the day and never recovered?
I have no idea why the bogus memory allocation happened. If it continues
to happen you might have data corruption on disk. If it never happens
again, it could have been cosmic rays.
Mike Stone
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:53 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
@ 2006-05-05 16:33 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-05-05 17:09 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 27+ messages in thread
From: Gábriel Ákos @ 2006-05-05 16:33 UTC (permalink / raw)
To: pgsql-performance
Michael Stone wrote:
> On Fri, May 05, 2006 at 10:45:21AM -0400, mcelroy, tim wrote:
>> Thanks Michael. Are you saying the 'used' column is the irrelevant
>> number?
>> Is the number that is more pertinent is 1416880? Is that the actual
>> amount
>> of memory in use?
>
> Yes.
>
>> I agree about the allocation of a bogus amount of memory
>> but the issue occurred after-hours when the application(s) were not
>> running.
>> Or are you saying the app whacked the DB during the day and never
>> recovered?
>
> I have no idea why the bogus memory allocation happened. If it continues
> to happen you might have data corruption on disk. If it never happens
> again, it could have been cosmic rays.
>
> Mike Stone
have you configured your shared memory settings right?
if postgres tries to allocate more memory (because of settings enable
it) than the kernel itself is configured for, then you will see similar
error messages.
--
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu=-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:53 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
2006-05-05 16:33 ` Re: Memory and/or cache issues? Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-05-05 17:09 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-06 00:33 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 27+ messages in thread
From: Michael Stone @ 2006-05-05 17:09 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 06:33:33PM +0200, Gábriel Ákos wrote:
>if postgres tries to allocate more memory (because of settings enable
>it) than the kernel itself is configured for, then you will see similar
>error messages.
If you're talking about the shared memory limits, postgres will bomb out
fairly quickly in that case, IIRC.
Mike Stone
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-05 14:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:53 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
2006-05-05 16:33 ` Re: Memory and/or cache issues? Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-05-05 17:09 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
@ 2006-05-06 00:33 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 27+ messages in thread
From: Jim C. Nasby @ 2006-05-06 00:33 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 05, 2006 at 01:09:53PM -0400, Michael Stone wrote:
> On Fri, May 05, 2006 at 06:33:33PM +0200, G?briel ?kos wrote:
> >if postgres tries to allocate more memory (because of settings enable
> >it) than the kernel itself is configured for, then you will see similar
> >error messages.
>
> If you're talking about the shared memory limits, postgres will bomb out
> fairly quickly in that case, IIRC.
More importantly I don't think it would result in trying to allocate 10
TB or whatever that huge number was.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-05 14:48 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-05 14:48 UTC (permalink / raw)
To: 'Craig A. James' <cjames@modgraph-usa.com>; +Cc: pgsql-performance
Thanks for a great explanation Craig, makes more sense now.
Tim
-----Original Message-----
From: Craig A. James [mailto:cjames@modgraph-usa.com]
Sent: Friday, May 05, 2006 10:51 AM
To: mcelroy, tim
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
mcelroy, tim wrote:
> Sorry, been up all night and maybe provided too much information or not
> the right information and only confused folks, tired I guess. When I
> say 'in use' I am referring to the 'used' column. Thanks all who have
> responded to this inquiry, I appreciate it.
>
> Here's free from PROD001:
> [root@wbibsngwyprod001 kernel]# free -k -t
> total used free shared buffers cached
> Mem: 7643536 6975772 667764 0 165496 5393396
> -/+ buffers/cache: 1416880 6226656
> Swap: 8185108 5208 8179900
> Total: 15828644 6980980 8847664
On Linux (unlike most Unix systems), "used" includes both processes AND the
kernel's file-system buffers, which means "used" will almost always be close
to 100%. Starting with a freshly-booted system, you can issue almost any
command that scans files, and "used" will go up and STAY at nearly 100% of
memory. For example, reboot and try "tar cf - / >/dev/null" and you'll see
the same sort of "used" numbers.
In My Humble Opinion, this is a mistake in Linux. This confuses just about
everyone the first time they see it (including me), because the file-system
buffers are dynamic and will be relenquished by the kernel if another
process needs memory. On Unix systems, "used" means, "someone else is using
it and you can't have it", which is what most of us really want to know.
Craig
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-06 14:53 mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-08 15:06 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
0 siblings, 1 reply; 27+ messages in thread
From: mcelroy, tim @ 2006-05-06 14:53 UTC (permalink / raw)
To: pgsql-performance; +Cc: Michael Stone <mstone+postgres@mathom.us>; 'Jim C. Nasby' <jnasby@pervasive.com>
Thank you again to all who have offered advice, suggestions, tips and offers
of support/training. From the gist of some of the latter posts I must come
off as a rank rookie, lol. Deservedly so as I've only been working with
postgres for 7 months and in the linux/unix world a year or so. My
background is Stratus SysAdmin (which I still do in addition to DBA) so the
transition is an on-going process.
That said, at this time I'll put the thread to rest as my company just
doubled the memory to 16GB, isn't that how it always works out anyway ;)
I'll also be moving the new postgresql.conf settings that were worked out
with the patient help of Jim Nasby, thanks again Jim. The DEV box I put
those on has shown some improvement. As far as outside support and
training, thank you but no. Probably doesn't show but I did attend a week
long PostgreSQL boot camp in January (which I found aimed more to the
development side than DBA by the way), but there is no better way to learn
and understand better than actual day-to-day working experience.
Thank you,
Tim McElroy
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Friday, May 05, 2006 8:35 PM
To: mcelroy, tim
Cc: 'Michael Stone'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
On Fri, May 05, 2006 at 10:27:10AM -0400, mcelroy, tim wrote:
> Sorry, been up all night and maybe provided too much information or not
the
Do you have any budget for support or training, either from the company
selling you the app or a company that provides PostgreSQL support? I
suspect some money invested there would result in a lot less
frustration. It'd also certainly be cheaper than switching to Oracle.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-06 14:53 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
@ 2006-05-08 15:06 ` Vivek Khera <vivek@khera.org>
2006-05-08 17:30 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 27+ messages in thread
From: Vivek Khera @ 2006-05-08 15:06 UTC (permalink / raw)
To: pgsql-performance
On May 6, 2006, at 10:53 AM, mcelroy, tim wrote:
> development side than DBA by the way), but there is no better way
> to learn
> and understand better than actual day-to-day working experience.
Yeah, I prefer my surgeons to work this way too. training is for the
birds.
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-06 14:53 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-08 15:06 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
@ 2006-05-08 17:30 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:38 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
0 siblings, 1 reply; 27+ messages in thread
From: Jim C. Nasby @ 2006-05-08 17:30 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-performance
On Mon, May 08, 2006 at 11:06:42AM -0400, Vivek Khera wrote:
>
> On May 6, 2006, at 10:53 AM, mcelroy, tim wrote:
>
> >development side than DBA by the way), but there is no better way
> >to learn
> >and understand better than actual day-to-day working experience.
>
> Yeah, I prefer my surgeons to work this way too. training is for the
> birds.
I think you read too quickly past the part where Tim said he'd taking a
week-long training class.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-06 14:53 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-08 15:06 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
2006-05-08 17:30 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
@ 2006-05-08 19:38 ` Vivek Khera <vivek@khera.org>
2006-05-08 21:17 ` Re: Memory and/or cache issues? Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 27+ messages in thread
From: Vivek Khera @ 2006-05-08 19:38 UTC (permalink / raw)
To: pgsql-performance
On May 8, 2006, at 1:30 PM, Jim C. Nasby wrote:
>> Yeah, I prefer my surgeons to work this way too. training is for the
>> birds.
>
> I think you read too quickly past the part where Tim said he'd
> taking a
> week-long training class.
s/training/apprenticeship/g;
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
2006-05-06 14:53 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-08 15:06 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
2006-05-08 17:30 ` Re: Memory and/or cache issues? Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:38 ` Re: Memory and/or cache issues? Vivek Khera <vivek@khera.org>
@ 2006-05-08 21:17 ` Michael Stone <mstone+postgres@mathom.us>
0 siblings, 0 replies; 27+ messages in thread
From: Michael Stone @ 2006-05-08 21:17 UTC (permalink / raw)
To: pgsql-performance
On Mon, May 08, 2006 at 03:38:23PM -0400, Vivek Khera wrote:
>On May 8, 2006, at 1:30 PM, Jim C. Nasby wrote:
>>>Yeah, I prefer my surgeons to work this way too. training is for the
>>>birds.
>>
>>I think you read too quickly past the part where Tim said he'd
>>taking a
>>week-long training class.
>
>s/training/apprenticeship/g;
Of course, the original poster did say that hands-on was the best way to
learn. What is apprenticeship but a combination of training and
experience. Are you just sniping for fun?
Mike Stone
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Memory and/or cache issues?
@ 2006-05-09 12:45 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 27+ messages in thread
From: mcelroy, tim @ 2006-05-09 12:45 UTC (permalink / raw)
To: pgsql-performance
Ok, thank you all again for your help in this matter. Yes, Michael I (the
original poster) did say or imply I guess is a better word for it that a
combo of training and hands-on is the best way for one to learn PostgreSQL
or just about anything for that matter. Thank you for recognizing the true
intention of my statements.
One does need some sort of basis from which to grow. I will say that
nothing can replace the hands-on real-world training one can get in this
business as it is the best way to learn and remember. Just my opinion. For
example, I stated I was a SysAdmin for 20 years. I was then thrust into the
Oracle world as a DBA about 2 years ago while still maintaining my SysAdmin
responsibilities. I have yet to receive any formal Oracle training and have
had to learn that on my own via, manuals, Google searches and begging the
Oracle Database Architect here for assistance. However, with PostgreSQL I
initially started down the very same track but was fortunate enough to
receive the ok for that week long PG boot camp. Although I didn't take all
that much away from the boot camp it did provide an excellent base from
which I continue to grow as a PG DBA and it has helped me to understand
postgres a lot easier and quicker than Oracle.
So please, lets just not throw emails back-n-forth amongst the group. Since
joining I have found the group as a whole to be a great resource of
information and PG knowledge and do not want us to get a testy with each
other over something I said or someone's interpretation of what I said.
Case closed.
BTW - I am still working towards getting the knowledge out here about what I
learned form the posts, mainly that the buffers/cache row of information
from the free command is the one we need most be concerned with.
Thank you,
Tim McElroy
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Michael Stone
Sent: Monday, May 08, 2006 5:17 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Memory and/or cache issues?
On Mon, May 08, 2006 at 03:38:23PM -0400, Vivek Khera wrote:
>On May 8, 2006, at 1:30 PM, Jim C. Nasby wrote:
>>>Yeah, I prefer my surgeons to work this way too. training is for the
>>>birds.
>>
>>I think you read too quickly past the part where Tim said he'd
>>taking a
>>week-long training class.
>
>s/training/apprenticeship/g;
Of course, the original poster did say that hands-on was the best way to
learn. What is apprenticeship but a combination of training and
experience. Are you just sniping for fun?
Mike Stone
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2006-05-09 12:45 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-05 11:49 Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-05 13:31 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-05-06 04:33 ` David Boreham <david_list@boreham.org>
2006-05-05 13:26 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 13:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-05 13:57 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:23 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-05 14:31 ` Dave Dutcher <dave@tridecap.com>
2006-05-05 14:27 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:40 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-06 00:31 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-05 14:51 ` Craig A. James <cjames@modgraph-usa.com>
2006-05-06 00:35 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-05 14:36 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-05 14:53 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-05 16:33 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-05-05 17:09 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-06 00:33 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-05 14:48 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-06 14:53 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-05-08 15:06 ` Vivek Khera <vivek@khera.org>
2006-05-08 17:30 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-08 19:38 ` Vivek Khera <vivek@khera.org>
2006-05-08 21:17 ` Michael Stone <mstone+postgres@mathom.us>
2006-05-09 12:45 Re: Memory and/or cache issues? mcelroy, tim <tim.mcelroy@bostonstock.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