pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMemory question
16+ messages / 12 participants
[nested] [flat]
* Memory question
@ 2003-06-27 19:09 Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:44 ` Re: Memory question scott.marlowe <scott.marlowe@ihs.com>
2003-06-27 19:54 ` Re: Memory question Josh Berkus <josh@agliodbs.com>
2003-06-27 20:17 ` Re: Memory question Jord Tanner <jord@indygecko.com>
0 siblings, 3 replies; 16+ messages in thread
From: Patrick Hatcher @ 2003-06-27 19:09 UTC (permalink / raw)
To: pgsql-performance
Sorry for posting an obvious Linux question, but have any of you
encountered this and how have you fixed it.
I have 6gig Ram box. I've set my shmmax to 3072000000. The database
starts up fine without any issues. As soon as a query is ran
or a FTP process to the server is done, the used memory shoots up and
appears to never be released.
My fear is that this may cause problems for my database if this number
continues to grow. Below is my TOP after running a query, and shutting
down PgAdmin. While not low now, the amount of free memory has dropped to
around 11mg. I'll admit I'm not that Linux savvy, but am I reading this
correct?
--TOP
45 processes: 44 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
buff
Swap: 2044056K av, 0K used, 2044056K free 6257620K
cached
Patrick Hatcher
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
@ 2003-06-27 19:44 ` scott.marlowe <scott.marlowe@ihs.com>
2003-06-27 19:59 ` Re: Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-30 16:02 ` Re: Memory question Jonathan Gardner <jgardner@jonathangardner.net>
2 siblings, 2 replies; 16+ messages in thread
From: scott.marlowe @ 2003-06-27 19:44 UTC (permalink / raw)
To: Patrick Hatcher <PHatcher@macys.com>; +Cc: pgsql-performance
This is actually normal. Look at the amount cached: 6257620K. That's
6.2Gig of cache. Linux is using only 6517776k - 6257620k of memory, the
rest is just acting as kernel cache. If anything tries to allocate a bit
of memory, linux will flush enough cache to give the memory to the
application that needs it.
Note that you're only showing linux and all its applications using about
256Meg.
On Fri, 27 Jun 2003, Patrick Hatcher wrote:
> Sorry for posting an obvious Linux question, but have any of you
> encountered this and how have you fixed it.
> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
> starts up fine without any issues. As soon as a query is ran
> or a FTP process to the server is done, the used memory shoots up and
> appears to never be released.
> My fear is that this may cause problems for my database if this number
> continues to grow. Below is my TOP after running a query, and shutting
> down PgAdmin. While not low now, the amount of free memory has dropped to
> around 11mg. I'll admit I'm not that Linux savvy, but am I reading this
> correct?
>
> --TOP
>
> 45 processes: 44 sleeping, 1 running, 0 zombie, 0 stopped
> CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
> buff
> Swap: 2044056K av, 0K used, 2044056K free 6257620K
> cached
>
> Patrick Hatcher
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:44 ` Re: Memory question scott.marlowe <scott.marlowe@ihs.com>
@ 2003-06-27 19:59 ` Patrick Hatcher <PHatcher@macys.com>
1 sibling, 0 replies; 16+ messages in thread
From: Patrick Hatcher @ 2003-06-27 19:59 UTC (permalink / raw)
To: scott.marlowe@ihs.com; +Cc: pgsql-performance
Thank you
Patrick Hatcher
"scott.marlowe
" To: Patrick Hatcher <PHatcher@macys.com>
<scott.marlowe cc: <pgsql-performance@postgresql.org>
@ihs.com> Subject: Re: [PERFORM] Memory question
06/27/2003
12:44 PM
This is actually normal. Look at the amount cached: 6257620K. That's
6.2Gig of cache. Linux is using only 6517776k - 6257620k of memory, the
rest is just acting as kernel cache. If anything tries to allocate a bit
of memory, linux will flush enough cache to give the memory to the
application that needs it.
Note that you're only showing linux and all its applications using about
256Meg.
On Fri, 27 Jun 2003, Patrick Hatcher wrote:
> Sorry for posting an obvious Linux question, but have any of you
> encountered this and how have you fixed it.
> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
> starts up fine without any issues. As soon as a query is ran
> or a FTP process to the server is done, the used memory shoots up and
> appears to never be released.
> My fear is that this may cause problems for my database if this number
> continues to grow. Below is my TOP after running a query, and shutting
> down PgAdmin. While not low now, the amount of free memory has dropped
to
> around 11mg. I'll admit I'm not that Linux savvy, but am I reading this
> correct?
>
> --TOP
>
> 45 processes: 44 sleeping, 1 running, 0 zombie, 0 stopped
> CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
> buff
> Swap: 2044056K av, 0K used, 2044056K free 6257620K
> cached
>
> Patrick Hatcher
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:44 ` Re: Memory question scott.marlowe <scott.marlowe@ihs.com>
@ 2003-06-30 16:02 ` Jonathan Gardner <jgardner@jonathangardner.net>
1 sibling, 0 replies; 16+ messages in thread
From: Jonathan Gardner @ 2003-06-30 16:02 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; Patrick Hatcher <PHatcher@macys.com>; +Cc: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Friday 27 June 2003 12:44, scott.marlowe wrote:
> This is actually normal. Look at the amount cached: 6257620K. That's
> 6.2Gig of cache. Linux is using only 6517776k - 6257620k of memory, the
> rest is just acting as kernel cache. If anything tries to allocate a bit
> of memory, linux will flush enough cache to give the memory to the
> application that needs it.
>
I think it is appropriate to add that the Linux kernel does this in an
extremely innovative and intelligent way. The more room you give your kernel
to cache, the more responsive it is going to be.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/AF8EWgwF3QvpWNwRArmVAJwK5C2ExmS8Rayrne33UJ0KZZM4UgCgq7b5
3J1LGtofgtnKq/bPtF75lNI=
=4Not
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
@ 2003-06-27 19:54 ` Josh Berkus <josh@agliodbs.com>
2003-06-27 19:58 ` Re: Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:49 ` Re: Memory question Arjen van der Meijden <acm@tweakers.net>
2 siblings, 2 replies; 16+ messages in thread
From: Josh Berkus @ 2003-06-27 19:54 UTC (permalink / raw)
To: Patrick Hatcher <PHatcher@macys.com>; pgsql-performance
Patrick,
> Sorry for posting an obvious Linux question, but have any of you
> encountered this and how have you fixed it.
> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
> starts up fine without any issues. As soon as a query is ran
> or a FTP process to the server is done, the used memory shoots up and
> appears to never be released.
What's you shared_buffers set to after our talk? Do you actually need 3gb of
shmmax?
> My fear is that this may cause problems for my database if this number
> continues to grow. Below is my TOP after running a query, and shutting
> down PgAdmin. While not low now, the amount of free memory has dropped to
> around 11mg. I'll admit I'm not that Linux savvy, but am I reading this
> correct?
No.
> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
The "used" figure in Top doesn't really tell you anything, since it includes
the kernel buffer which tries to take up all available memory. If you
actually look at the list of processes, I think you'll find that you're only
using 1-2% of memory for applications.
I'm not sure what app would show your "real" free memory.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:54 ` Re: Memory question Josh Berkus <josh@agliodbs.com>
@ 2003-06-27 19:58 ` Patrick Hatcher <PHatcher@macys.com>
1 sibling, 0 replies; 16+ messages in thread
From: Patrick Hatcher @ 2003-06-27 19:58 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
Shared buffer is now set to 20,000 as suggested. So far so good.
As far as shmmax, it really is my ignorance of Linux. We are going to play
around with this number. Is there a suggested amount since I have
my effective_cache_size = 625000 (or does one have nothing to do with the
other)
Thanks again
Patrick Hatcher
Josh Berkus
<josh@agliodbs To: "Patrick Hatcher" <PHatcher@macys.com>, pgsql-performance@postgresql.org
.com> cc:
Subject: Re: [PERFORM] Memory question
06/27/2003
12:54 PM
Please respond
to josh
Patrick,
> Sorry for posting an obvious Linux question, but have any of you
> encountered this and how have you fixed it.
> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
> starts up fine without any issues. As soon as a query is ran
> or a FTP process to the server is done, the used memory shoots up and
> appears to never be released.
What's you shared_buffers set to after our talk? Do you actually need 3gb
of
shmmax?
> My fear is that this may cause problems for my database if this number
> continues to grow. Below is my TOP after running a query, and shutting
> down PgAdmin. While not low now, the amount of free memory has dropped
to
> around 11mg. I'll admit I'm not that Linux savvy, but am I reading this
> correct?
No.
> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
The "used" figure in Top doesn't really tell you anything, since it
includes
the kernel buffer which tries to take up all available memory. If you
actually look at the list of processes, I think you'll find that you're
only
using 1-2% of memory for applications.
I'm not sure what app would show your "real" free memory.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:54 ` Re: Memory question Josh Berkus <josh@agliodbs.com>
@ 2003-06-27 20:49 ` Arjen van der Meijden <acm@tweakers.net>
1 sibling, 0 replies; 16+ messages in thread
From: Arjen van der Meijden @ 2003-06-27 20:49 UTC (permalink / raw)
To: josh@agliodbs.com; 'Patrick Hatcher' <PHatcher@macys.com>; pgsql-performance
> The "used" figure in Top doesn't really tell you anything,
> since it includes
> the kernel buffer which tries to take up all available
> memory. If you
> actually look at the list of processes, I think you'll find
> that you're only
> using 1-2% of memory for applications.
>
> I'm not sure what app would show your "real" free memory.
The command 'free' shows what you like to know:
$ free
total used free shared buffers
cached
Mem: 1551480 1505656 45824 0 101400
1015540
-/+ buffers/cache: 388716 1162764
Swap: 524264 23088 501176
The used/free amounts on the second line are the interesting ones in
this case.
Arjen
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
@ 2003-06-27 20:17 ` Jord Tanner <jord@indygecko.com>
2003-06-27 20:30 ` Re: Memory question Matthew Hixson <hixson@poindextrose.org>
2 siblings, 1 reply; 16+ messages in thread
From: Jord Tanner @ 2003-06-27 20:17 UTC (permalink / raw)
To: Patrick Hatcher <PHatcher@macys.com>; pgsql-performance
On Fri, 2003-06-27 at 12:09, Patrick Hatcher wrote:
> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
> starts up fine without any issues. As soon as a query is ran
> or a FTP process to the server is done, the used memory shoots up and
> appears to never be released.
In my experience Linux likes to allocate almost all available RAM. I've
never had any trouble with that. I'm looking at the memory meter on my
RH9 development workstation and it is at 95%. Performance is good, so I
just trust that the kernel knows what it is doing.
> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd, 25168K
> buff
> Swap: 2044056K av, 0K used, 2044056K free 6257620K
> cached
I've heard anecdotally that Linux has troubles if the swap space is less
than the RAM size. I note that you have 6G of RAM, but only 2G of swap.
I'm sure others on the list will have more definitive opinions.
--
Jord Tanner <jord@indygecko.com>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:17 ` Re: Memory question Jord Tanner <jord@indygecko.com>
@ 2003-06-27 20:30 ` Matthew Hixson <hixson@poindextrose.org>
2003-06-27 20:51 ` Re: Memory question scott.marlowe <scott.marlowe@ihs.com>
2003-06-27 20:55 ` Re: Memory question Arjen van der Meijden <acm@tweakers.net>
0 siblings, 2 replies; 16+ messages in thread
From: Matthew Hixson @ 2003-06-27 20:30 UTC (permalink / raw)
To: pgsql-performance
On Friday, June 27, 2003, at 01:17 PM, Jord Tanner wrote:
> On Fri, 2003-06-27 at 12:09, Patrick Hatcher wrote:
>
>
>
>> I have 6gig Ram box. I've set my shmmax to 3072000000. The database
>> starts up fine without any issues. As soon as a query is ran
>> or a FTP process to the server is done, the used memory shoots up and
>> appears to never be released.
>
> In my experience Linux likes to allocate almost all available RAM. I've
> never had any trouble with that. I'm looking at the memory meter on my
> RH9 development workstation and it is at 95%. Performance is good, so I
> just trust that the kernel knows what it is doing.
>
>
>
>> Mem: 6711564K av, 6517776K used, 193788K free, 0K shrd,
>> 25168K
>> buff
>> Swap: 2044056K av, 0K used, 2044056K free
>> 6257620K
>> cached
>
> I've heard anecdotally that Linux has troubles if the swap space is
> less
> than the RAM size. I note that you have 6G of RAM, but only 2G of swap.
I've heard that too, but it doesn't seem to make much sense to me. If
you get to the point where your machine is _needing_ 2GB of swap then
something has gone horribly wrong (or you just need more RAM in the
machine) and it will just crawl until the kernel kills off whatever
process causes the swap space to be exceeded. Seems to me that you
should only have that much swap if you can't afford more RAM or you've
tapped out your machine's capacity, and your application needs that
much memory.
-M@
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:17 ` Re: Memory question Jord Tanner <jord@indygecko.com>
2003-06-27 20:30 ` Re: Memory question Matthew Hixson <hixson@poindextrose.org>
@ 2003-06-27 20:51 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 16+ messages in thread
From: scott.marlowe @ 2003-06-27 20:51 UTC (permalink / raw)
To: Matthew Hixson <hixson@poindextrose.org>; +Cc: pgsql-performance
On Fri, 27 Jun 2003, Matthew Hixson wrote:
> On Friday, June 27, 2003, at 01:17 PM, Jord Tanner wrote:
> > I've heard anecdotally that Linux has troubles if the swap space is
> > less
> > than the RAM size. I note that you have 6G of RAM, but only 2G of swap.
>
> I've heard that too, but it doesn't seem to make much sense to me. If
> you get to the point where your machine is _needing_ 2GB of swap then
> something has gone horribly wrong (or you just need more RAM in the
> machine) and it will just crawl until the kernel kills off whatever
> process causes the swap space to be exceeded. Seems to me that you
> should only have that much swap if you can't afford more RAM or you've
> tapped out your machine's capacity, and your application needs that
> much memory.
This was an artifact in older kernels where the swap code didn't work
right unless it had as much swap as memory. I'm pretty sure that was
fixed long ago in the 2.4 series.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:17 ` Re: Memory question Jord Tanner <jord@indygecko.com>
2003-06-27 20:30 ` Re: Memory question Matthew Hixson <hixson@poindextrose.org>
@ 2003-06-27 20:55 ` Arjen van der Meijden <acm@tweakers.net>
2003-06-30 05:49 ` Re: Memory question Thomas Swan <tswan@idigx.com>
1 sibling, 1 reply; 16+ messages in thread
From: Arjen van der Meijden @ 2003-06-27 20:55 UTC (permalink / raw)
To: 'Matthew Hixson' <hixson@poindextrose.org>; pgsql-performance
> I've heard that too, but it doesn't seem to make much sense
> to me. If
> you get to the point where your machine is _needing_ 2GB of swap then
> something has gone horribly wrong (or you just need more RAM in the
> machine) and it will just crawl until the kernel kills off whatever
> process causes the swap space to be exceeded. Seems to me that you
> should only have that much swap if you can't afford more RAM
> or you've
> tapped out your machine's capacity, and your application needs that
> much memory.
> -M@
I've heard the same, the reason behind it was that there needs to be
one-to-one copy of the memory to be able to swap out everything and to
have a gain in the total "memory", you'd need twice as much swap as
memory to have a doubling of your memory.
But afaik this behaviour has been adjusted since the 2.4.5 kernel and
isn't a real issue anymore.
Please keep in mind that I'm no expert at all on linux, so if you want
to be sure, you'd better mail to the kernel-mailinglist orso :)
Anyway, I manage a few machines with 1GB++ memory and none of them has
more than 1G of swap and none of them uses that swap for more than a few
MB unless something was terribly wrong, so the actual 'risk' probably
doesn't have a high chance to occur.
Arjen
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Memory question
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:17 ` Re: Memory question Jord Tanner <jord@indygecko.com>
2003-06-27 20:30 ` Re: Memory question Matthew Hixson <hixson@poindextrose.org>
2003-06-27 20:55 ` Re: Memory question Arjen van der Meijden <acm@tweakers.net>
@ 2003-06-30 05:49 ` Thomas Swan <tswan@idigx.com>
0 siblings, 0 replies; 16+ messages in thread
From: Thomas Swan @ 2003-06-30 05:49 UTC (permalink / raw)
To: Arjen van der Meijden <acm@tweakers.net>; +Cc: 'Matthew Hixson' <hixson@poindextrose.org>; pgsql-performance
Arjen van der Meijden wrote:
>>I've heard that too, but it doesn't seem to make much sense
>>to me. If
>>you get to the point where your machine is _needing_ 2GB of swap then
>>something has gone horribly wrong (or you just need more RAM in the
>>machine) and it will just crawl until the kernel kills off whatever
>>process causes the swap space to be exceeded. Seems to me that you
>>should only have that much swap if you can't afford more RAM
>>or you've
>>tapped out your machine's capacity, and your application needs that
>>much memory.
>> -M@
>>
>>
>I've heard the same, the reason behind it was that there needs to be
>one-to-one copy of the memory to be able to swap out everything and to
>have a gain in the total "memory", you'd need twice as much swap as
>memory to have a doubling of your memory.
>
>But afaik this behaviour has been adjusted since the 2.4.5 kernel and
>isn't a real issue anymore.
>
It may be different in vendor released kernels as the default overcommit
behavior of the Linux kernel may vary. More detailed discussions can be
found on the LKML, or you can find some useful summaries by searching
through the last couple "Kernel Traffic" issues <http://kt.zork.net; ..
I had some unexpected problems on one system, an older RH distribution,
until I actually set the swap to be double the 2GB of ram on the system:
4GB.
>Please keep in mind that I'm no expert at all on linux, so if you want
>to be sure, you'd better mail to the kernel-mailinglist orso :)
>
>Anyway, I manage a few machines with 1GB++ memory and none of them has
>more than 1G of swap and none of them uses that swap for more than a few
>MB unless something was terribly wrong, so the actual 'risk' probably
>doesn't have a high chance to occur.
>
>Arjen
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* memory question
@ 2010-03-25 00:49 Campbell, Lance <lance@illinois.edu>
2010-03-25 02:06 ` Re: memory question Scott Marlowe <scott.marlowe@gmail.com>
2010-03-25 10:35 ` Re: memory question Matthew Wakeling <matthew@flymine.org>
0 siblings, 2 replies; 16+ messages in thread
From: Campbell, Lance @ 2010-03-25 00:49 UTC (permalink / raw)
To: pgsql-performance
PostgreSQL 8.4.3
Linux Redhat 5.0
Question: How much memory do I really need?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: memory question
2010-03-25 00:49 memory question Campbell, Lance <lance@illinois.edu>
@ 2010-03-25 02:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-25 06:28 ` Re: memory question Dave Crooke <dcrooke@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2010-03-25 02:06 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
On Wed, Mar 24, 2010 at 6:49 PM, Campbell, Lance <lance@illinois.edu> wrote:
> PostgreSQL 8.4.3
>
> Linux Redhat 5.0
>
> Question: How much memory do I really need?
The answer is "as much as needed to hold your entire database in
memory and a few gig left over for sorts and backends to play in."
> From my understanding there are two primary strategies for setting up
> PostgreSQL in relationship to memory:
>
>
>
> 1) Rely on Linux to cache the files. In this approach you set the
> shared_buffers to a relatively low number.
>
> 2) You can set shared_buffers to a very large percentage of your memory
> so that PostgreSQL reserves the memory for the database.
The kernel is better at caching large amounts of memory. Pg is better
at handling somewhat smaller amounts and not flushing out random
access data for sequential access data.
> I am currently using option #1. I have 24 Gig of memory on my server and
> the database takes up 17 Gig of disk space. When I do the Linux command
> “top” I notice that 19 Gig is allocated for cache. Is there a way for me to
> tell how much of that cache is associated with the caching of database
> files?
Probably nearly all of that 19G for cache is allocated for pg files.
Not sure how to tell off the top of my head though.
> I am basically asking how much memory do I really need? Maybe I have
> complete over kill. Maybe I am getting to a point where I might need more
> memory.
Actually, there are three levels of caching that are possible. 1:
Entire db, tables and indexes, can fit in RAM. This is the fastest
method. Worth the extra $ for RAM if you can afford it / db isn't too
huge. 2: Indexes can fit in RAM, some of tables can. Still pretty
fast. Definitely worth paying a little extra for. 3: Neither indexes
nor tables can wholly fit in RAM. At this point the speed of your
large disk array becomes important, and you want a fast cachine RAID
controller. Both of these items (disk array and RAID controller) are
considerably more costly than 16 or 32 Gigs of RAM.
> My thought was I could use option #2 and then set the number to a lower
> amount. If the performance is bad then slowly work the number up.
I'm not sure what you mean. Install less RAM and let PG do all the
caching? Usually a bad idea. Usually. I'm sure there are use cases
that it might be a good idea on. But keep in mind, a large amount of
shared_buffers doesn't JUST buffer your reads, it also results in a
much large memory space to keep track of in terms of things that need
to get written out etc. I'm actually about to reduce the
shared_buffers from 8G on one reporting server down to 1 or 2G cause
that's plenty, and it's having a hard time keeping up with the huge
checkpoints it's having to do.
> Our server manager seems to think that I have way to much memory. He thinks
> that we only need 5 Gig.
How much do you absolutely need to boot up, run postgresql, and not
run out of memory? That's what you "need" and it's probably around
1Gig. It's just no less arbitraty than 5G. Did he show you how he
arrived at this number? If your DB is 17Gig on disk, it's foolish to
be cheap on memory.
> I don’t really believe that. But I want to cover
> myself. With money tight I don’t want to be the person who is wasting
> resources. We need to replace our database servers so I want to do the
> right thing.
You can waste your time (valuable but sunk cost) other people's time
(more valuable, also sunk cost) or "waste" a few dollars on memory.
24Gig isn't that expensive really compared to say 10 seconds per
transaction for 100 users, 1000 times a day. Or 11 user days in a
single day. 10s of seconds start to add up.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: memory question
2010-03-25 00:49 memory question Campbell, Lance <lance@illinois.edu>
2010-03-25 02:06 ` Re: memory question Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-03-25 06:28 ` Dave Crooke <dcrooke@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Dave Crooke @ 2010-03-25 06:28 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Campbell, Lance <lance@illinois.edu>; pgsql-performance
What Scott said ... seconded, all of it.
I'm running one 500GB database on a 64-bit, 8GB VMware virtual machine, with
2 vcores, PG 8.3.9 with shared_buffers set to 2GB, and it works great.
However, it's a modest workload, most of the database is archival for data
mining, and the "working set" for routine OLTP is pretty modest and easily
fits in the 2GB, and it's back-ended on to a pretty decent EMC Clariion
FibreChannel array. Not the typical case.
For physical x86 servers, brand name (e.g. Kingston) ECC memory is down to
$25 per GB in 4GB DIMMs, and $36 per GB in 8GB DIMMs .... dollars to
doughnuts you have a server somewhere with 2GB or 4GB parts that can be
pulled and replaced with double the density, et voila, an extra 16GB of RAM
for about $500.
Lots and lots of RAM is absolutely, positively a no-brainer when trying to
make a DB go fast. If for no other reason than people get all starry eyed at
GHz numbers, almost all computers tend to be CPU heavy and RAM light in
their factory configs. I build a new little server for the house every 3-5
years, using desktop parts, and give it a mid-life upgrade with bigger
drives and doubling the RAM density.
Big banks running huge Oracle OLTP setups use the strategy of essentially
keeping the whole thing in RAM .... HP shifts a lot of Superdome's maxed out
with 2TB of RAM into this market - and that RAM costs a lot more than $25 a
gig ;-)
Cheers
Dave
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: memory question
2010-03-25 00:49 memory question Campbell, Lance <lance@illinois.edu>
@ 2010-03-25 10:35 ` Matthew Wakeling <matthew@flymine.org>
1 sibling, 0 replies; 16+ messages in thread
From: Matthew Wakeling @ 2010-03-25 10:35 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
On Wed, 24 Mar 2010, Campbell, Lance wrote:
> I have 24 Gig of memory on my server...
>
> Our server manager seems to think that I have way to much memory. He
> thinks that we only need 5 Gig.
You organisation probably spent more money getting your server manager to
investigate how much RAM you need and scaring you about wasting resources,
than it would cost to just slap 24GB in the machine.
24GB is the least amount of RAM I would consider putting in a new server
nowadays. It's so cheap.
Matthew
--
Lord grant me patience, and I want it NOW!
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-03-25 10:35 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-27 19:09 Memory question Patrick Hatcher <PHatcher@macys.com>
2003-06-27 19:44 ` scott.marlowe <scott.marlowe@ihs.com>
2003-06-27 19:59 ` Patrick Hatcher <PHatcher@macys.com>
2003-06-30 16:02 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-06-27 19:54 ` Josh Berkus <josh@agliodbs.com>
2003-06-27 19:58 ` Patrick Hatcher <PHatcher@macys.com>
2003-06-27 20:49 ` Arjen van der Meijden <acm@tweakers.net>
2003-06-27 20:17 ` Jord Tanner <jord@indygecko.com>
2003-06-27 20:30 ` Matthew Hixson <hixson@poindextrose.org>
2003-06-27 20:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-06-27 20:55 ` Arjen van der Meijden <acm@tweakers.net>
2003-06-30 05:49 ` Thomas Swan <tswan@idigx.com>
2010-03-25 00:49 memory question Campbell, Lance <lance@illinois.edu>
2010-03-25 02:06 ` Re: memory question Scott Marlowe <scott.marlowe@gmail.com>
2010-03-25 06:28 ` Re: memory question Dave Crooke <dcrooke@gmail.com>
2010-03-25 10:35 ` Re: memory question Matthew Wakeling <matthew@flymine.org>
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