Re: [PERFORM] Question about memory allocations
Ron [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Question about memory allocations
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. - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Question about memory allocations
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Question about memory allocations
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Question about memory allocations
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 -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Question about memory allocations
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 [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Question about memory allocations
Steve [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Question about memory allocations
On Tue, 10 Apr 2007, Steve wrote: - I've set up a configuration (I'll show important values below), and Im 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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Question about memory allocations
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Question about memory allocations
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 Im 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 360' 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 = 500 # 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Question about memory allocations
Steve [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
