pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmemory allocation
13+ messages / 11 participants
[nested] [flat]
* memory allocation
@ 2004-06-18 13:34 Michael Ryan S. Puncia <mpuncia@census.gov.ph>
0 siblings, 2 replies; 13+ messages in thread
From: Michael Ryan S. Puncia @ 2004-06-18 13:34 UTC (permalink / raw)
To: pgsql-performance
Hi everyone .
How much memory should I give to the kernel and postgresql
I have 1G of memory and 120G of HD
Shared Buffers = ?
Vacuum Mem = ?
SHMAX = ?
Sorry I have so many question .I am a newbie :-(
I have 30G of data
At least 30 simultaneus users
But I will use it only for query with lot of sorting
thanks
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: memory allocation
@ 2004-06-18 13:39 Devrim GUNDUZ <devrim@gunduz.org>
parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
1 sibling, 0 replies; 13+ messages in thread
From: Devrim GUNDUZ @ 2004-06-18 13:39 UTC (permalink / raw)
To: pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On Fri, 18 Jun 2004, Michael Ryan S. Puncia wrote:
> How much memory should I give to the kernel and postgresql
>
> I have 1G of memory and 120G of HD
>
> Shared Buffers = ?
>
> Vacuum Mem = ?
Maybe you should read
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.sxw
OR
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> SHMAX = ?
SHMMAX is not that relevant with PostgreSQL, it's rather relevant with
your operating system.
Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFA0vCUtl86P3SPfQ4RAvITAJ48FV24aBN+nc2+lkRwXc79HlHV6QCfSvRA
YuGjn8hs1jvOJ2Ah9oamIJQ=
=96+i
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: memory allocation
@ 2004-06-18 13:58 Richard Huxton <dev@archonet.com>
parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
1 sibling, 0 replies; 13+ messages in thread
From: Richard Huxton @ 2004-06-18 13:58 UTC (permalink / raw)
To: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; +Cc: pgsql-performance
Michael Ryan S. Puncia wrote:
> Hi everyone .
>
>
>
> How much memory should I give to the kernel and postgresql
>
> I have 1G of memory and 120G of HD
Devrim's pointed you to a guide to the configuration file. There's also
an introduction to performance tuning on the same site.
An important thing to remember is that the sort_mem is the amount of
memory available *per sort* and some queries can use several sorts.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 13+ messages in thread
* Memory Allocation
@ 2008-11-26 22:09 Ryan Hansen <ryan.hansen@brightbuilders.com>
0 siblings, 4 replies; 13+ messages in thread
From: Ryan Hansen @ 2008-11-26 22:09 UTC (permalink / raw)
To: pgsql-performance
Hey all,
This may be more of a Linux question than a PG question, but I'm wondering
if any of you have successfully allocated more than 8 GB of memory to PG
before.
I have a fairly robust server running Ubuntu Hardy Heron, 24 GB of memory,
and I've tried to commit half the memory to PG's shared buffer, but it seems
to fail. I'm setting the kernel shared memory accordingly using sysctl,
which seems to work fine, but when I set the shared buffer in PG and restart
the service, it fails if it's above about 8 GB. I actually have it
currently set at 6 GB.
I don't have the exact failure message handy, but I can certainly get it if
that helps. Mostly I'm just looking to know if there's any general reason
why it would fail, some inherent kernel or db limitation that I'm unaware
of.
If it matters, this DB is going to be hosting and processing hundreds of GB
and eventually TB of data, it's a heavy read-write system, not transactional
processing, just a lot of data file parsing (python/bash) and bulk loading.
Obviously the disks get hit pretty hard already, so I want to make the most
of the large amount of available memory wherever possible. So I'm trying to
tune in that direction.
Any info is appreciated.
Thanks!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 22:18 Alan Hodgson <ahodgson@simkin.ca>
parent: Ryan Hansen <ryan.hansen@brightbuilders.com>
3 siblings, 0 replies; 13+ messages in thread
From: Alan Hodgson @ 2008-11-26 22:18 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 26 November 2008, "Ryan Hansen"
<ryan.hansen@brightbuilders.com> wrote:
> This may be more of a Linux question than a PG question, but I'm
> wondering if any of you have successfully allocated more than 8 GB of
> memory to PG before.
>
CentOS 5, 24GB shared_buffers on one server here. No problems.
--
Alan
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 22:47 Carlos Moreno <morenopg@mochima.com>
parent: Ryan Hansen <ryan.hansen@brightbuilders.com>
3 siblings, 0 replies; 13+ messages in thread
From: Carlos Moreno @ 2008-11-26 22:47 UTC (permalink / raw)
To: Ryan Hansen <ryan.hansen@brightbuilders.com>; +Cc: pgsql-performance
Ryan Hansen wrote:
>
> Hey all,
>
> This may be more of a Linux question than a PG question, but I’m
> wondering if any of you have successfully allocated more than 8 GB of
> memory to PG before.
>
> I have a fairly robust server running Ubuntu Hardy Heron, 24 GB of
> memory, and I’ve tried to commit half the memory to PG’s shared
> buffer, but it seems to fail.
>
Though not sure why this is happening or whether it is normal, I would
suggest that such setting is maybe too high. From the Annotated
postgresql.conf document at
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html,
the suggested range is 8 to 400MB. They specifically say that it
should never be set to more than 1/3 of the available memory, which
in your case is precisely the 8GB figure (I guess that's just a
coincidence --- I doubt that the server would be written so that it
fails to start if shared_buffers is more than 1/3 of available RAM)
Another important parameter that you don't mention is the
effective_cache_size, which that same document suggests should
be about 2/3 of available memory. (this tells the planner the amount
of data that it can "probabilistically" expect to reside in memory due
to caching, and as such, the planner is likely to produce more
accurate estimates and thus better query optimizations).
Maybe you could set shared_buffers to, say, 1 or 2GB (that's already
beyond the recommended figure, but given that you have 24GB, it
may not hurt), and then effective_cache_size to 16GB or so?
HTH,
Carlos
--
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 22:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ryan Hansen <ryan.hansen@brightbuilders.com>
3 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2008-11-26 22:58 UTC (permalink / raw)
To: Ryan Hansen <ryan.hansen@brightbuilders.com>; +Cc: pgsql-performance
"Ryan Hansen" <ryan.hansen@brightbuilders.com> writes:
> I have a fairly robust server running Ubuntu Hardy Heron, 24 GB of memory,
> and I've tried to commit half the memory to PG's shared buffer, but it seems
> to fail. I'm setting the kernel shared memory accordingly using sysctl,
> which seems to work fine, but when I set the shared buffer in PG and restart
> the service, it fails if it's above about 8 GB.
Fails how? And what PG version is that?
FWIW, while there are various schools of thought on how large to make
shared_buffers, pretty much everybody agrees that half of physical RAM
is not the sweet spot. What you're likely to get is maximal
inefficiency with every active disk page cached twice --- once in kernel
space and once in shared_buffers.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 23:00 Scott Carey <scott@richrelevance.com>
parent: Ryan Hansen <ryan.hansen@brightbuilders.com>
3 siblings, 1 reply; 13+ messages in thread
From: Scott Carey @ 2008-11-26 23:00 UTC (permalink / raw)
To: Ryan Hansen <ryan.hansen@brightbuilders.com>; pgsql-performance
Tuning for bulk loading:
Make sure the Linux kernel paramters in /proc/sys/vm related to the page cache are set well.
Set swappiness to 0 or 1.
Make sure you understand and configure /proc/sys/vm/dirty_background_ratio
and /proc/sys/vm/dirty_ratio well.
With enough RAM the default on some kernel versions is way, way off (40% of RAM with dirty pages! yuck).
http://www.westnet.com/~gsmith/content/linux-pdflush.htm
If postgres is doing a lot of caching for you you probably want dirty_ratio at 10% or less, and you'll want the OS to start flushing to disk sooner rather than later. A dirty_background_ratio of 3% with 24GB of RAM is 720MB -- a pretty big buffer. I would not personally want this buffer to be larger than 5 seconds of max write speed of the disk I/O.
You'll need to tune your background writer to be aggressive enough to actually write data fast enough so that checkpoints don't suck, and tune your checkpoint size and settings as well. Turn on checkpoint logging on the database and run tests while looking at the output of those. Ideally, most of your batch writes have made it to the OS before the checkpoint, and the OS has actually started moving most of it to disk. If your settings are wrong, you'll have the data buffered twice, and most or nearly all of it will be in memory when the checkpoint happens, and the checkpoint will take a LONG time. The default Linux settings + default postgres settings + large shared_buffers will almost guarantee this situation for bulk loads. Both have to be configured with complementary settings. If you have a large postgres buffer, the OS buffer should be small and write more aggressively. If you have a small postgres buffer, the OS can be more lazy and cache much more.
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ryan Hansen
Sent: Wednesday, November 26, 2008 2:10 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Memory Allocation
Hey all,
This may be more of a Linux question than a PG question, but I'm wondering if any of you have successfully allocated more than 8 GB of memory to PG before.
I have a fairly robust server running Ubuntu Hardy Heron, 24 GB of memory, and I've tried to commit half the memory to PG's shared buffer, but it seems to fail. I'm setting the kernel shared memory accordingly using sysctl, which seems to work fine, but when I set the shared buffer in PG and restart the service, it fails if it's above about 8 GB. I actually have it currently set at 6 GB.
I don't have the exact failure message handy, but I can certainly get it if that helps. Mostly I'm just looking to know if there's any general reason why it would fail, some inherent kernel or db limitation that I'm unaware of.
If it matters, this DB is going to be hosting and processing hundreds of GB and eventually TB of data, it's a heavy read-write system, not transactional processing, just a lot of data file parsing (python/bash) and bulk loading. Obviously the disks get hit pretty hard already, so I want to make the most of the large amount of available memory wherever possible. So I'm trying to tune in that direction.
Any info is appreciated.
Thanks!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 23:09 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 1 reply; 13+ messages in thread
From: Kevin Grittner @ 2008-11-26 23:09 UTC (permalink / raw)
To: Ryan Hansen <ryan.hansen@brightbuilders.com>; pgsql-performance; Scott Carey <scott@richrelevance.com>
>>> Scott Carey <scott@richrelevance.com> wrote:
> Set swappiness to 0 or 1.
We recently converted all 72 remote county databases from 8.2.5 to
8.3.4. In preparation we ran a test conversion of a large county over
and over with different settings to see what got us the best
performance. Setting swappiness below the default degraded
performance for us in those tests for identical data, same hardware,
no other changes.
Our best guess is that code which really wasn't getting called got
swapped out leaving more space in the OS cache, but that's just a
guess. Of course, I'm sure people would not be recommending it if
they hadn't done their own benchmarks to confirm that this setting
actually improved things in their environments, so the lesson here is
to test for your environment when possible.
-Kevin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-26 23:52 Scott Carey <scott@richrelevance.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 13+ messages in thread
From: Scott Carey @ 2008-11-26 23:52 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Ryan Hansen <ryan.hansen@brightbuilders.com>; pgsql-performance
Swappiness optimization is going to vary. Definitely test on your own.
For a bulk load database, with large page cache, swappines = 60 (default) is _GUARANTEED_ to force the OS to swap out some of Postgres while in heavy use. This is heavily dependent on the page cache size, work_mem size, and concurrency.
I've had significantly increased performance setting this value low (1000x ! -- if your DB starts swapping postgres, you're performance-DEAD). The default has the OS targeting close to 60% of the memory for page cache. On a 32GB server, with 7GB postgres buffer cache, several concurrent queries reading GB's of data and using 500MB + work_mem (huge aggregates), the default swappiness will choose to page out postgres with about 19GB of disk page cache left to evict, with disastrous results. And that is a read-only test. Tests with writes can trigger it earlier if combined with bad dirty_buffers settings.
The root of the problem is that the Linux paging algorithm estimates that I/O for file read access is as costly as I/O for paging. A reasonable assumption for a desktop, a ridiculously false assumption for a large database with high capacity DB file I/O and a much lower capability swap file. Not only that -- page in is almost always near pure random reads, but DB I/O is often sequential. So losing 100M of cached db file takes a lot less time to scan back in than 100MB of the application.
If you do have enough other applications that are idle that take up RAM that should be pushed out to disk from time to time (perhaps your programs that are doing the bulk loading?) a higher value is useful. Although it is not exact, think of the swappiness value as the percentage of RAM that the OS would prefer page cache to applications (very roughly).
The more RAM you have and the larger your postgres memory usage, the lower the swappiness value should be. 60% of 24GB is ~14.5GB, If you have that much stuff that is in RAM that should be paged out to save space, try it.
I currently use a value of 1, on a 32GB machine, and about 600MB of 'stuff' gets paged out normally, 1400MB under heavy load. This is a dedicated machine. Higher values page out more stuff that increases the cache size and helps performance a little, but under the heavy load, it hits the paging wall and falls over. The small improvement in performance when the system is not completely stressed is not worth risking hitting the wall for me.
***For a bulk load database, one is optimizing for _writes_ and extra page cache doesn't help writes like it does reads.***
When I use a machine with misc. other lower priority apps and less RAM, I have found larger values to be helpful.
If your DB is configured with a low shared_buffers and small work_mem, you probably want the OS to use that much memory for disk pages, and again a higher swappiness may be more optimal.
Like all of these settings, tune to your application and test. Many of these settings are things that go hand in hand with others, but alone don't make as much sense. Tuning Postgres to do most of the caching and making the OS get out of the way is far different than tuning the OS to do as much caching work as possible and minimizing postgres. Which of those two strategies is best is highly application dependent, somewhat O/S dependent, and also hardware dependent.
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Wednesday, November 26, 2008 3:09 PM
To: Ryan Hansen; pgsql-performance@postgresql.org; Scott Carey
Subject: Re: [PERFORM] Memory Allocation
>>> Scott Carey <scott@richrelevance.com> wrote:
> Set swappiness to 0 or 1.
We recently converted all 72 remote county databases from 8.2.5 to
8.3.4. In preparation we ran a test conversion of a large county over
and over with different settings to see what got us the best
performance. Setting swappiness below the default degraded
performance for us in those tests for identical data, same hardware,
no other changes.
Our best guess is that code which really wasn't getting called got
swapped out leaving more space in the OS cache, but that's just a
guess. Of course, I'm sure people would not be recommending it if
they hadn't done their own benchmarks to confirm that this setting
actually improved things in their environments, so the lesson here is
to test for your environment when possible.
-Kevin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Memory Allocation
@ 2008-11-28 22:28 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 13+ messages in thread
From: Kevin Grittner @ 2008-11-28 22:28 UTC (permalink / raw)
To: Ryan Hansen <ryan.hansen@brightbuilders.com>; pgsql-performance; Scott Carey <scott@richrelevance.com>
I'm hoping that through compare/contrast we might help someone start
closer to their own best values....
>>> Scott Carey <scott@richrelevance.com> wrote:
> Tests with writes can trigger it earlier if combined with bad
dirty_buffers
> settings.
We've never, ever modified dirty_buffers settings from defaults.
> The root of the problem is that the Linux paging algorithm estimates
that
> I/O for file read access is as costly as I/O for paging. A
reasonable
> assumption for a desktop, a ridiculously false assumption for a large
> database with high capacity DB file I/O and a much lower capability
swap
> file.
Our swap file is not on lower speed drives.
> If you do have enough other applications that are idle that take up
RAM that
> should be pushed out to disk from time to time (perhaps your programs
that
> are doing the bulk loading?) a higher value is useful.
Bulk loading was ssh cat | psql.
> The more RAM you have and the larger your postgres memory usage, the
lower
> the swappiness value should be.
I think the test environment had 8 GB RAM with 256 MB in
shared_buffers. For the conversion we had high work_mem and
maintenance_work_mem settings, and we turned fsync off, along with a
few other settings we would never using during production.
> I currently use a value of 1, on a 32GB machine, and about 600MB of
'stuff'
> gets paged out normally, 1400MB under heavy load.
Outside of bulk load, we've rarely seen anything swap, even under
load.
> ***For a bulk load database, one is optimizing for _writes_ and extra
page
> cache doesn't help writes like it does reads.***
I'm thinking that it likely helps when indexing tables for which data
has recently been loaded. It also might help minimize head movement
and/or avoid the initial disk hit for a page which subsequently get
hint bits set .
> Like all of these settings, tune to your application and test.
We sure seem to agree on that.
-Kevin
^ permalink raw reply [nested|flat] 13+ messages in thread
* memory allocation
@ 2017-10-17 09:28 nijam J <nijamutheenblogs1@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: nijam J @ 2017-10-17 09:28 UTC (permalink / raw)
To: pgsql-performance
we are using cloud server
*this are memory info*
free -h
total used free shared buffers cached
Mem: 15G 15G 197M 194M 121M 14G
-/+ buffers/cache: 926M 14G
Swap: 15G 32M 15G
*this are disk info:*
df -h
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 20G 1.7G 17G 10% /
devtmpfs 7.9G 0 7.9G 0% /dev
tmpfs 7.9G 4.0K 7.9G 1% /dev/shm
tmpfs 7.9G 17M 7.9G 1% /run
tmpfs 7.9G 0 7.9G 0% /sys/fs/cgroup
/dev/mapper/vgzero-lvhome 99G 189M 94G 1% /home
/dev/mapper/vgzero-lvdata 1.2T 75G 1.1T 7% /data
/dev/mapper/vgzero-lvbackup 296G 6.2G 274G 3% /backup
/dev/mapper/vgzero-lvxlog 197G 61M 187G 1% /pg_xlog
/dev/mapper/vgzero-lvarchive 197G 67G 121G 36% /archive
i allocated memory as per following list:
shared_buffers = 2GB (10-30 %)
effective_cache_size =7GB (70-75 %) ---->>(shared_buffers+page cache) for
dedicated server only
work_mem = 128MB (0.3-1 %)
maintenance_work_mem = 512MB (0.5-4 % )
temp_Buffer = 8MB ---->>default is better( setting can
be changed within individual sessions)
checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 3.5
cpu_tuple_cost = 0.05
wal_buffers = 32MB leave this default 3% of shared buffer is better
is it better or do i want to modify any thing
our server is getting too slow again and again
please give me a suggestion
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: memory allocation
@ 2017-10-19 12:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: nijam J <nijamutheenblogs1@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Laurenz Albe @ 2017-10-19 12:51 UTC (permalink / raw)
To: nijam J <nijamutheenblogs1@gmail.com>; pgsql-performance
nijam J wrote:
> our server is getting too slow again and again
Use "vmstat 1" and "iostat -mNx 1" to see if you are
running out of memory, CPU capacity or I/O bandwith.
Figure out if the slowness is due to slow queries or
an overloaded system.
Yours,
Laurenz Albe
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2017-10-19 12:51 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-18 13:34 memory allocation Michael Ryan S. Puncia <mpuncia@census.gov.ph>
2004-06-18 13:39 ` Devrim GUNDUZ <devrim@gunduz.org>
2004-06-18 13:58 ` Richard Huxton <dev@archonet.com>
2008-11-26 22:09 Memory Allocation Ryan Hansen <ryan.hansen@brightbuilders.com>
2008-11-26 22:18 ` Re: Memory Allocation Alan Hodgson <ahodgson@simkin.ca>
2008-11-26 22:47 ` Re: Memory Allocation Carlos Moreno <morenopg@mochima.com>
2008-11-26 22:58 ` Re: Memory Allocation Tom Lane <tgl@sss.pgh.pa.us>
2008-11-26 23:00 ` Re: Memory Allocation Scott Carey <scott@richrelevance.com>
2008-11-26 23:09 ` Re: Memory Allocation Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-11-26 23:52 ` Re: Memory Allocation Scott Carey <scott@richrelevance.com>
2008-11-28 22:28 ` Re: Memory Allocation Kevin Grittner <Kevin.Grittner@wicourts.gov>
2017-10-17 09:28 memory allocation nijam J <nijamutheenblogs1@gmail.com>
2017-10-19 12:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
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