pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about memory allocations
11+ messages / 7 participants
[nested] [flat]
* Question about memory allocations
@ 2007-04-10 19:28 Steve <cheetah@tanabi.org>
2007-04-12 04:31 ` Re: Question about memory allocations Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 02:50 ` Re: Question about memory allocations Greg Smith <gsmith@gregsmith.com>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 3 replies; 11+ messages in thread
From: Steve @ 2007-04-10 19:28 UTC (permalink / raw)
To: pgsql-performance
Hey there;
I'm trying to tune the memory usage of a new machine that has a -lot- of
memory in it (32 gigs). We're upgrading from a machine that had 16 gigs
of RAM and using a database that's around 130-some gigs on disc. Our
largest tables have in the order of close to 10 million rows.
Problem is, the postgres documentation isn't always clear about what
different memory things are used for and it's definitely not clear about
what 'useful values' would be for various things. Further, looking
online, gets a lot of random stuff and most of the configuration
information out there is for pre-8.1 versions that don't have all these
new and strange values :)
This machine exists only for the database. With that in mind, a few
questions.
- I've set up a configuration (I'll show important values below), and
I"m wondering if there's any way I can actually see the distribution of
memory in the DB and how the memory is being used.
- What is temp_buffers used for exactly? Does this matter for, say,
nested queries or anything in specific? Is there any case where having
this as a large number actually -helps-?
- Do full_page_writes and wal_buffers settings matter AT ALL for a machine
where fysnc = off ?
- What does wal_buffers mean and does increasing this value actually help
anything?
- Any idea if this is a smart configuration for this machine? It's a
Redhat Enterprise Linux machine (kernel 2.6.18), 8 dual-core AMD 64bit
processors, 32 gigs of RAM, 4x 176 (or whatever the exact number is) gig
SCSI hard drives in a stripe. Only values I have modified are mentioned,
everything else left at default:
shared_buffers = 16GB
temp_buffers = 128MB
max_prepared_transactions = 0
# This value is going to probably set off cries of using this as a set
# command instead of a big global value; however there's more big queries
# than small ones and the number of simultaneous users is very small so
# 'for now' this can be set globally big and if it shows improvement
# I'll implement it as set commands later.
#
# Question; does this mean 2 gigs will be immediately allocated to
# every query, or is this just how big the work memory is allowed to
# grow per transaction?
work_mem=2G
maintenance_work_mem = 4GB
max_stack_depth = 16MB
# Vacuum suggested I make this 'over 3600000' on the old machine, so
# I use this value; if it's too big, this is a symptom of another problem,
# I'd be interested to know :)
max_fsm_pages = 5000000
# For a lot of reasons, it doesn't make any sense to use fsync for this
# DB. Read-only during the day, backed up daily, UPS'd, etc.
fsync = off
full_page_writes = off
wal_buffers = 512MB
# Leaving this low makes the DB complain, but I'm not sure what's
# reasonable.
checkpoint_segments = 128
random_page_cost = 1.5
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.00025
effective_cache_size = 8GB
default_statistics_target = 100
Thanks for all your help!
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
@ 2007-04-12 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 16:46 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
2 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-04-12 04:31 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
Steve <cheetah@tanabi.org> writes:
> - What is temp_buffers used for exactly?
Temporary tables. Pages of temp tables belonging to your own backend
don't ever get loaded into the main shared-buffers arena, they are read
into backend-local memory. temp_buffers is the max amount (per backend)
of local memory to use for this purpose.
> - Do full_page_writes and wal_buffers settings matter AT ALL for a machine
> where fysnc = off ?
Yes.
> - What does wal_buffers mean and does increasing this value actually help
> anything?
It's the amount of space available to buffer WAL log data that's not
been written to disk. If you have a lot of short transactions then
there's not much benefit to increasing it (because the WAL will be
getting forced to disk frequently anyway) but I've heard reports that
for workloads involving long single transactions bumping it up to 64
or 100 or so helps.
> - Any idea if this is a smart configuration for this machine?
Um ... you didn't mention which PG version?
> # This value is going to probably set off cries of using this as a set
> # command instead of a big global value;
No kidding. You do NOT want work_mem that high, at least not without an
extremely predictable, simple workload.
> wal_buffers = 512MB
I haven't heard any reports that there's a point in values even as high
as 1 meg for this.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-12 04:31 ` Re: Question about memory allocations Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 16:46 ` Steve <cheetah@tanabi.org>
0 siblings, 0 replies; 11+ messages in thread
From: Steve @ 2007-04-12 16:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> Steve <cheetah@tanabi.org> writes:
>> - What is temp_buffers used for exactly?
>
> Temporary tables. Pages of temp tables belonging to your own backend
> don't ever get loaded into the main shared-buffers arena, they are read
> into backend-local memory. temp_buffers is the max amount (per backend)
> of local memory to use for this purpose.
Are these only tables explicitly stated as 'temporary' (which as I
recall is a create table option) or are temporary tables used for other
things to like, say, nested queries or other lil in the background things?
>> - Any idea if this is a smart configuration for this machine?
>
> Um ... you didn't mention which PG version?
>
The latest and greatest stable as downloaded a couple days ago.
8.2.3. :)
Thanks for the info!
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
@ 2007-04-13 02:50 ` Greg Smith <gsmith@gregsmith.com>
2007-04-13 03:57 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
2 siblings, 1 reply; 11+ messages in thread
From: Greg Smith @ 2007-04-13 02:50 UTC (permalink / raw)
To: pgsql-performance
On Tue, 10 Apr 2007, Steve wrote:
> - I've set up a configuration (I'll show important values below), and I"m
> wondering if there's any way I can actually see the distribution of memory in
> the DB and how the memory is being used.
I didn't notice anyone address this for you yet. There is a tool in
contrib/pg_buffercache whose purpose in life is to show you what the
shared buffer cache has inside it. The documentation in that directory
leads through installing it. The additional variable you'll likely never
know is what additional information is inside the operating system's
buffer cache.
> # Leaving this low makes the DB complain, but I'm not sure what's #
> reasonable.
> checkpoint_segments = 128
That's a reasonable setting for a large server. The main downside to
setting it that high is longer recovery periods after a crash, but I doubt
that's a problem for you if you're so brazen as to turn off fsync.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 02:50 ` Re: Question about memory allocations Greg Smith <gsmith@gregsmith.com>
@ 2007-04-13 03:57 ` Steve <cheetah@tanabi.org>
0 siblings, 0 replies; 11+ messages in thread
From: Steve @ 2007-04-13 03:57 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
> I didn't notice anyone address this for you yet. There is a tool in
> contrib/pg_buffercache whose purpose in life is to show you what the shared
> buffer cache has inside it. The documentation in that directory leads
> through installing it. The additional variable you'll likely never know is
> what additional information is inside the operating system's buffer cache.
Okay -- thanks! I'll take a look at this.
>> # Leaving this low makes the DB complain, but I'm not sure what's #
>> reasonable.
>> checkpoint_segments = 128
>
> That's a reasonable setting for a large server. The main downside to setting
> it that high is longer recovery periods after a crash, but I doubt that's a
> problem for you if you're so brazen as to turn off fsync.
Hahaha yeah. It's 100% assumed that if something goes bad we're
restoring from the previous day's backup. However because the DB is read
only for -most- of the day and only read/write at night it's acceptable
risk for us anyway. But good to know that's a reasonable value.
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
@ 2007-04-13 08:35 ` Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
2 siblings, 1 reply; 11+ messages in thread
From: Andrew McMillan @ 2007-04-13 08:35 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
On Tue, 2007-04-10 at 15:28 -0400, Steve wrote:
>
> I'm trying to tune the memory usage of a new machine that has a -lot- of
> memory in it (32 gigs).
...
>
> shared_buffers = 16GB
Really?
Wow!
Common wisdom in the past has been that values above a couple of hundred
MB will degrade performance. Have you done any benchmarks on 8.2.x that
show that you get an improvement from this, or did you just take the
"too much of a good thing is wonderful" approach?
Cheers,
Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
You have an unusual equipment for success. Be sure to use it properly.
-------------------------------------------------------------------------
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
@ 2007-04-13 16:38 ` Steve <cheetah@tanabi.org>
2007-04-13 18:23 ` Re: Question about memory allocations Ron <rjpeace@earthlink.net>
2007-04-13 18:53 ` Re: Question about memory allocations Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 2 replies; 11+ messages in thread
From: Steve @ 2007-04-13 16:38 UTC (permalink / raw)
To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: pgsql-performance
> Really?
>
> Wow!
>
> Common wisdom in the past has been that values above a couple of hundred
> MB will degrade performance. Have you done any benchmarks on 8.2.x that
> show that you get an improvement from this, or did you just take the
> "too much of a good thing is wonderful" approach?
>
Not to be rude, but there's more common wisdom on this particular
subject than anything else in postgres I'd say ;) I think I recently read
someone else on this list who's laundry-listed the recommended memory
values that are out there these days and pretty much it ranges from
what you've just said to "half of system memory".
I've tried many memory layouts, and in my own experience with
this huge DB, more -does- appear to be better but marginally so; more
memory alone won't fix a speed problem. It may be a function of how much
reading/writing is done to the DB and if fsync is used or not if that
makes any sense :) Seems there's no "silver bullet" to the shared_memory
question. Or if there is, nobody can agree on it ;)
Anyway, talk to you later!
Steve
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
@ 2007-04-13 18:23 ` Ron <rjpeace@earthlink.net>
2007-04-14 16:37 ` Re: Question about memory allocations Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Ron @ 2007-04-13 18:23 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
At 12:38 PM 4/13/2007, Steve wrote:
>>Really?
>>
>>Wow!
>>
>>Common wisdom in the past has been that values above a couple of hundred
>>MB will degrade performance. Have you done any benchmarks on 8.2.x that
>>show that you get an improvement from this, or did you just take the
>>"too much of a good thing is wonderful" approach?
>
> Not to be rude, but there's more common wisdom on this
> particular subject than anything else in postgres I'd say ;) I
> think I recently read someone else on this list who's
> laundry-listed the recommended memory values that are out there
> these days and pretty much it ranges from what you've just said to
> "half of system memory".
>
> I've tried many memory layouts, and in my own experience
> with this huge DB, more -does- appear to be better but marginally
> so; more memory alone won't fix a speed problem. It may be a
> function of how much reading/writing is done to the DB and if fsync
> is used or not if that makes any sense :) Seems there's no "silver
> bullet" to the shared_memory question. Or if there is, nobody can
> agree on it ;)
One of the reasons for the wide variance in suggested values for pg
memory use is that pg 7.x and pg 8.x are =very= different beasts.
If you break the advice into pg 7.x and pg 8.x categories, you find
that there is far less variation in the suggestions.
Bottom line: pg 7.x could not take advantage of larger sums of memory
anywhere near as well as pg 8.x can.
Cheers,
Ron
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 18:23 ` Re: Question about memory allocations Ron <rjpeace@earthlink.net>
@ 2007-04-14 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2007-04-14 16:37 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: Steve <cheetah@tanabi.org>; pgsql-performance
Ron <rjpeace@earthlink.net> writes:
> One of the reasons for the wide variance in suggested values for pg
> memory use is that pg 7.x and pg 8.x are =very= different beasts.
> If you break the advice into pg 7.x and pg 8.x categories, you find
> that there is far less variation in the suggestions.
> Bottom line: pg 7.x could not take advantage of larger sums of memory
> anywhere near as well as pg 8.x can.
Actually I think it was 8.1 that really broke the barrier in terms of
scalability of shared_buffers. Pre-8.1, the buffer manager just didn't
scale well enough to make it useful to use more than a few hundred meg.
(In fact, we never even bothered to fix the shared-memory-sizing
calculations to be able to deal with >2GB shared memory until 8.1;
if you try it in 8.0 it'll probably just crash.)
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
@ 2007-04-13 18:53 ` Carlos Moreno <moreno_pg@mochima.com>
2007-04-13 20:24 ` Re: Question about memory allocations Jan de Visser <jdevisser@digitalfairway.com>
1 sibling, 1 reply; 11+ messages in thread
From: Carlos Moreno @ 2007-04-13 18:53 UTC (permalink / raw)
To: pgsql-performance
Steve wrote:
>>
>> Common wisdom in the past has been that values above a couple of hundred
>> MB will degrade performance.
The annotated config file talks about setting shared_buffers to a third
of the
available memory --- well, it says "it should be no more than 1/3 of the
total
amount of memory" (quoting off the top of my head). Don't recall seeing
any warning about not exceeding a few hundred megabytes.
My eternal curiosity when it comes to this memory and shared_buffers thing:
How does PG take advantage of the available memory? I mean, if I have a
machine with, say, 4 or 8GB of memory, how will those GBs would end
up being used? They just do?? (I mean, I would find that a vaild
answer;
but I ask, because this configuration parameters stuff makes me think that
perhaps PG does not simply use whatever memory is in there, but it has
to go through the parameters in the config file to allocate whatever it has
to use).
So, is it just like that? We put more memory and PG will automatically
make use of it?
Carlos
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Question about memory allocations
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Re: Question about memory allocations Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Re: Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-13 18:53 ` Re: Question about memory allocations Carlos Moreno <moreno_pg@mochima.com>
@ 2007-04-13 20:24 ` Jan de Visser <jdevisser@digitalfairway.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jan de Visser @ 2007-04-13 20:24 UTC (permalink / raw)
To: pgsql-performance
On Friday 13 April 2007 14:53:53 Carlos Moreno wrote:
> How does PG take advantage of the available memory? I mean, if I have a
> machine with, say, 4 or 8GB of memory, how will those GBs would end
> up being used? They just do?? (I mean, I would find that a vaild
> answer;
On linux the filesystem cache will gobble them up, which means indirectly
pgsql profits as well (assuming no other apps poison the fs cache).
jan
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-04-14 16:37 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-10 19:28 Question about memory allocations Steve <cheetah@tanabi.org>
2007-04-12 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 16:46 ` Steve <cheetah@tanabi.org>
2007-04-13 02:50 ` Greg Smith <gsmith@gregsmith.com>
2007-04-13 03:57 ` Steve <cheetah@tanabi.org>
2007-04-13 08:35 ` Andrew McMillan <andrew@catalyst.net.nz>
2007-04-13 16:38 ` Steve <cheetah@tanabi.org>
2007-04-13 18:23 ` Ron <rjpeace@earthlink.net>
2007-04-14 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 18:53 ` Carlos Moreno <moreno_pg@mochima.com>
2007-04-13 20:24 ` Jan de Visser <jdevisser@digitalfairway.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