Thread: Postgresql on an AMD64 machine
I'm not sure if this is the appropriate list to post this question to but i'm starting with this one because it is related to the performance of Postgresql server. I have a Penguin Computing dual AMD 64 bit opteron machine with 8 Gigs of memory. In my attempt to increase the number of shared_buffers from the default to 65000 i was running into a semget error when trying to start Postgresql. After reading the documentation I adjusted the semaphore settings in the kernel to allow Postgresql to start successfully. With this configuration running if I do a ipcs -u i get the following. ------ Shared Memory Status -------- segments allocated 1 pages allocated 30728 pages resident 30626 pages swapped 0 Swap performance: 0 attempts 0 successes ------ Semaphore Status -------- used arrays = 1880 allocated semaphores = 31928 ------ Messages: Status -------- allocated queues = 0 used headers = 0 used space = 0 bytes I'm questioning the number of semaphores being used. In order for postgresql to start I had to set the maximum number of semaphores system wide to 6000000. This seems to be an abnormal amount of semaphores. I'm curious if this is a bug in the amd64 postgresql port. Is anyone else using postgresql on an AMD64 machine without similar issues? TIA Mark
On Jun 6, 2005, at 1:53 PM, Mark Rinaudo wrote: > I'm questioning the number of semaphores being used. In order for > postgresql to start I had to set the maximum number of semaphores > system > wide to 6000000. This seems to be an abnormal amount of > semaphores. I'm > curious if this is a bug in the amd64 postgresql port. Is anyone else > using postgresql on an AMD64 machine without similar issues? > No such nonsense required for me under FreeBSD 5.4/amd64. I used the same settings I had under i386 OS. Postgres uses very few semaphores, from what I recall. My system shows 13 active semaphores. Vivek Khera, Ph.D. +1-301-869-4449 x806
Attachment
On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote: > I'm not sure if this is the appropriate list to post this question to > but i'm starting with this one because it is related to the performance > of Postgresql server. I have a Penguin Computing dual AMD 64 bit > opteron machine with 8 Gigs of memory. In my attempt to increase the > number of shared_buffers from the default to 65000 i was running into a > semget error when trying to start Postgresql. After reading the > documentation I adjusted the semaphore settings in the kernel to allow > Postgresql to start successfully. With this configuration running if I > do a ipcs -u i get the following. On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9): $ ipcs -u i ------ Shared Memory Status -------- segments allocated 1 pages allocated 34866 pages resident 31642 pages swapped 128 Swap performance: 0 attempts 0 successes ------ Semaphore Status -------- used arrays = 7 allocated semaphores = 119 ------ Messages: Status -------- allocated queues = 0 used headers = 0 used space = 0 bytes Did you perhaps disable spinlocks when compiling PG? -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
Mike Rylander <mrylander@gmail.com> writes:
> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote:
>> I'm not sure if this is the appropriate list to post this question to
>> but i'm starting with this one because it is related to the performance
>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>> opteron machine with 8 Gigs of memory. In my attempt to increase the
>> number of shared_buffers from the default to 65000 i was running into a
>> semget error when trying to start Postgresql.
> Did you perhaps disable spinlocks when compiling PG?
That sure looks like it must be the issue --- in a normal build the
number of semaphores needed does not vary with shared_buffers, but
it will if Postgres is falling back to semaphore-based spinlocks.
Which is a really bad idea from a performance standpoint, so you
want to fix the build.
Which PG version is this exactly, and what configure options did
you use? What compiler was used?
regards, tom lane
I'm running the Redhat Version of Postgresql which came pre-installed with Redhat ES. It's version number is 7.3.10-1. I'm not sure what options it was compiled with. Is there a way for me to tell? Should i just compile my own postgresql for this platform? Thanks Mark On Mon, 2005-06-06 at 16:15, Tom Lane wrote: > Mike Rylander <mrylander@gmail.com> writes: > > On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote: > >> I'm not sure if this is the appropriate list to post this question to > >> but i'm starting with this one because it is related to the performance > >> of Postgresql server. I have a Penguin Computing dual AMD 64 bit > >> opteron machine with 8 Gigs of memory. In my attempt to increase the > >> number of shared_buffers from the default to 65000 i was running into a > >> semget error when trying to start Postgresql. > > > Did you perhaps disable spinlocks when compiling PG? > > That sure looks like it must be the issue --- in a normal build the > number of semaphores needed does not vary with shared_buffers, but > it will if Postgres is falling back to semaphore-based spinlocks. > Which is a really bad idea from a performance standpoint, so you > want to fix the build. > > Which PG version is this exactly, and what configure options did > you use? What compiler was used? > > regards, tom lane > -- Mark Rinaudo 318-213-8780 ext 111 Bowman Systems
Mark Rinaudo wrote: > I'm running the Redhat Version of Postgresql which came pre-installed > with Redhat ES. It's version number is 7.3.10-1. I'm not sure what > options it was compiled with. Is there a way for me to tell? `pg_config --configure` in recent releases. > Should i just compile my own postgresql for this platform? Yes, I would. 7.4 was the first release to include support for proper spinlocks on AMD64. (From a Redhat POV, it would probably be a good idea to patch 7.3 to include the relatively trivial changes needed for decent AMD64 performance, assuming that shipping a more recent version of PG with ES isn't an option.) -Neil
Neil Conway <neilc@samurai.com> writes:
> (From a Redhat POV, it would probably be a good idea to patch 7.3 to
> include the relatively trivial changes needed for decent AMD64
> performance,
How embarrassing :-( Will see about fixing it. However, this certainly
won't ship before the next RHEL3 quarterly update, so in the meantime if
Mark feels like building locally, it wouldn't be a bad idea.
regards, tom lane
Get FATAL when starting up (64 bit) with large shared_buffers setting I built a 64 bit for Sparc/Solaris easily but I found that the startup of postmaster generates a FATAL diagnostic due to going over the 2GB limit (3.7 GB). When building for 64 bit is there some other things that must change in order to size UP the shared_buffers? Thanks. Don C. P.S. A severe checkpoint problem I was having was fixed with "checkpoint_segments=200". Message: FATAL: 460000 is outside the valid range for parameter "shared_buffers" (16 .. 262143) LOG: database system was shut down at 2005-06-07 15:20:28 EDT Mike Rylander wrote: >On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote: > > >>I'm not sure if this is the appropriate list to post this question to >>but i'm starting with this one because it is related to the performance >>of Postgresql server. I have a Penguin Computing dual AMD 64 bit >>opteron machine with 8 Gigs of memory. In my attempt to increase the >>number of shared_buffers from the default to 65000 i was running into a >>semget error when trying to start Postgresql. After reading the >>documentation I adjusted the semaphore settings in the kernel to allow >>Postgresql to start successfully. With this configuration running if I >>do a ipcs -u i get the following. >> >> > > >On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9): > >$ ipcs -u i > >------ Shared Memory Status -------- >segments allocated 1 >pages allocated 34866 >pages resident 31642 >pages swapped 128 >Swap performance: 0 attempts 0 successes > >------ Semaphore Status -------- >used arrays = 7 >allocated semaphores = 119 > >------ Messages: Status -------- >allocated queues = 0 >used headers = 0 >used space = 0 bytes > > >Did you perhaps disable spinlocks when compiling PG? > > >
According to my research, you only need a 64 bit image if you are going to be doing intensive floating point operations (which most db servers don't do). Some benchmarking results I've found on the internet indicate that 64 bit executables can be slower than 32 bit versions. I've been running 32 bit compiles on solaris for several years. How much memory do you have on that sparc box? Allocating more than about 7-12% to shared buffers has proven counter productive for us (it slows down). Kernel buffers are another animal. :) Donald Courtney wrote: > Get FATAL when starting up (64 bit) with large shared_buffers setting > > I built a 64 bit for Sparc/Solaris easily but I found that the > startup of postmaster generates a FATAL diagnostic due to going > over the 2GB limit (3.7 GB). > > When building for 64 bit is there some other > things that must change in order to size UP the shared_buffers? > > Thanks. > > Don C. > > P.S. A severe checkpoint problem I was having was fixed with > "checkpoint_segments=200". > > > Message: > > FATAL: 460000 is outside the valid range for parameter "shared_buffers" > (16 .. 262143) > LOG: database system was shut down at 2005-06-07 15:20:28 EDT > > Mike Rylander wrote: > >> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> >> wrote: >> >> >>> I'm not sure if this is the appropriate list to post this question to >>> but i'm starting with this one because it is related to the performance >>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit >>> opteron machine with 8 Gigs of memory. In my attempt to increase the >>> number of shared_buffers from the default to 65000 i was running into a >>> semget error when trying to start Postgresql. After reading the >>> documentation I adjusted the semaphore settings in the kernel to allow >>> Postgresql to start successfully. With this configuration running if I >>> do a ipcs -u i get the following. >>> >> >> >> >> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9): >> >> $ ipcs -u i >> >> ------ Shared Memory Status -------- >> segments allocated 1 >> pages allocated 34866 >> pages resident 31642 >> pages swapped 128 >> Swap performance: 0 attempts 0 successes >> >> ------ Semaphore Status -------- >> used arrays = 7 >> allocated semaphores = 119 >> >> ------ Messages: Status -------- >> allocated queues = 0 >> used headers = 0 >> used space = 0 bytes >> >> >> Did you perhaps disable spinlocks when compiling PG? >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > >
Tom Arthurs wrote: > According to my research, you only need a 64 bit image if you are > going to be doing intensive floating point operations (which most db > servers don't do). Some benchmarking results I've found on the > internet indicate that 64 bit executables can be slower than 32 bit > versions. I've been running 32 bit compiles on solaris for several years. > > How much memory do you have on that sparc box? Allocating more than > about 7-12% to shared buffers has proven counter productive for us (it > slows down). > The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large caches - Am I missing something key with postgreSQL? Yes - we have seen with oracle 64 bit that there can be as much as a 10% hit moving from 32 - but we make it up big time with large db-buffer sizes that drastically reduce I/O and allow for other things (like more connections). Maybe the expectation of less I/O is not correct? Don P.S. built with the Snapshot from two weeks ago. > Kernel buffers are another animal. :) > > Donald Courtney wrote: > >> Get FATAL when starting up (64 bit) with large shared_buffers setting >> >> I built a 64 bit for Sparc/Solaris easily but I found that the >> startup of postmaster generates a FATAL diagnostic due to going >> over the 2GB limit (3.7 GB). >> >> When building for 64 bit is there some other >> things that must change in order to size UP the shared_buffers? >> >> Thanks. >> >> Don C. >> >> P.S. A severe checkpoint problem I was having was fixed with >> "checkpoint_segments=200". >> >> >> Message: >> >> FATAL: 460000 is outside the valid range for parameter >> "shared_buffers" (16 .. 262143) >> LOG: database system was shut down at 2005-06-07 15:20:28 EDT >> >> Mike Rylander wrote: >> >>> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> >>> wrote: >>> >>> >>>> I'm not sure if this is the appropriate list to post this question to >>>> but i'm starting with this one because it is related to the >>>> performance >>>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit >>>> opteron machine with 8 Gigs of memory. In my attempt to increase the >>>> number of shared_buffers from the default to 65000 i was running >>>> into a >>>> semget error when trying to start Postgresql. After reading the >>>> documentation I adjusted the semaphore settings in the kernel to allow >>>> Postgresql to start successfully. With this configuration running >>>> if I >>>> do a ipcs -u i get the following. >>>> >>> >>> >>> >>> >>> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9): >>> >>> $ ipcs -u i >>> >>> ------ Shared Memory Status -------- >>> segments allocated 1 >>> pages allocated 34866 >>> pages resident 31642 >>> pages swapped 128 >>> Swap performance: 0 attempts 0 successes >>> >>> ------ Semaphore Status -------- >>> used arrays = 7 >>> allocated semaphores = 119 >>> >>> ------ Messages: Status -------- >>> allocated queues = 0 >>> used headers = 0 >>> used space = 0 bytes >>> >>> >>> Did you perhaps disable spinlocks when compiling PG? >>> >>> >>> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> >>
>> > The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large > caches - > Am I missing something key with postgreSQL? > Yes - we have seen with oracle 64 bit that there can be as much as a 10% > hit moving > from 32 - but we make it up big time with large db-buffer sizes that > drastically Well for Opteron you should also gain from the very high memory bandwidth and the fact that it has I believe "3" FP units per CPU. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Yes, shared buffers in postgres are not used for caching -- unlike Oracle. Every time we hire an Oracle dba, I have to break them of the notion (which I shared when I started with postgres -- Josh Berkus and Josh Drake helped burst that bubble for me) :) You should gain i/o reduction through increasing kernel buffers -- Postgresql counts on read/write caching through that, so increasing that should get your performance improvemnets -- though I haven't found the sweet spot there yet, for solaris. My biggest challenge with solaris/sparc is trying to reduce context switching. Donald Courtney wrote: > Tom Arthurs wrote: > >> According to my research, you only need a 64 bit image if you are >> going to be doing intensive floating point operations (which most db >> servers don't do). Some benchmarking results I've found on the >> internet indicate that 64 bit executables can be slower than 32 bit >> versions. I've been running 32 bit compiles on solaris for several years. >> >> How much memory do you have on that sparc box? Allocating more than >> about 7-12% to shared buffers has proven counter productive for us (it >> slows down). >> > The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large > caches - > Am I missing something key with postgreSQL? > Yes - we have seen with oracle 64 bit that there can be as much as a 10% > hit moving > from 32 - but we make it up big time with large db-buffer sizes that > drastically > reduce I/O and allow for other things (like more connections). Maybe > the expectation of less I/O is not correct? > > Don > > P.S. built with the Snapshot from two weeks ago. > >> Kernel buffers are another animal. :) >> >> Donald Courtney wrote: >> >>> Get FATAL when starting up (64 bit) with large shared_buffers setting >>> >>> I built a 64 bit for Sparc/Solaris easily but I found that the >>> startup of postmaster generates a FATAL diagnostic due to going >>> over the 2GB limit (3.7 GB). >>> >>> When building for 64 bit is there some other >>> things that must change in order to size UP the shared_buffers? >>> >>> Thanks. >>> >>> Don C. >>> >>> P.S. A severe checkpoint problem I was having was fixed with >>> "checkpoint_segments=200". >>> >>> >>> Message: >>> >>> FATAL: 460000 is outside the valid range for parameter >>> "shared_buffers" (16 .. 262143) >>> LOG: database system was shut down at 2005-06-07 15:20:28 EDT >>> >>> Mike Rylander wrote: >>> >>>> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> >>>> wrote: >>>> >>>> >>>>> I'm not sure if this is the appropriate list to post this question to >>>>> but i'm starting with this one because it is related to the >>>>> performance >>>>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit >>>>> opteron machine with 8 Gigs of memory. In my attempt to increase the >>>>> number of shared_buffers from the default to 65000 i was running >>>>> into a >>>>> semget error when trying to start Postgresql. After reading the >>>>> documentation I adjusted the semaphore settings in the kernel to allow >>>>> Postgresql to start successfully. With this configuration running >>>>> if I >>>>> do a ipcs -u i get the following. >>>>> >>>> >>>> >>>> >>>> >>>> >>>> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9): >>>> >>>> $ ipcs -u i >>>> >>>> ------ Shared Memory Status -------- >>>> segments allocated 1 >>>> pages allocated 34866 >>>> pages resident 31642 >>>> pages swapped 128 >>>> Swap performance: 0 attempts 0 successes >>>> >>>> ------ Semaphore Status -------- >>>> used arrays = 7 >>>> allocated semaphores = 119 >>>> >>>> ------ Messages: Status -------- >>>> allocated queues = 0 >>>> used headers = 0 >>>> used space = 0 bytes >>>> >>>> >>>> Did you perhaps disable spinlocks when compiling PG? >>>> >>>> >>>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 5: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/docs/faq >>> >>> >>> > > > >
On Tue, Jun 07, 2005 at 04:19:24PM -0400, Donald Courtney wrote: > The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large > caches - > Am I missing something key with postgreSQL? Yeah. Postgres makes extensive use of the kernel's cache (or, more precisely, assumes that the kernel is doing some caching on its own). So the bulk of the memory should be left to the kernel to handle, and shared_buffers be set relatively slow. This was the standard wisdom with releases previous to 8.0; I'm not sure if anyone confirmed to still hold after the buffer manager changes in 8.0 and later in 8.1 -- we saw extensive redesign of the bufmgr on both, so the behavior may have changed. If you wanna test, I'm sure lots of people here will be interested in the results. -- Alvaro Herrera (<alvherre[a]surnet.cl>) "This is a foot just waiting to be shot" (Andrew Dunstan)
On Tue, Jun 07, 2005 at 01:39:04PM -0700, Tom Arthurs wrote: >Yes, shared buffers in postgres are not used for caching That begs the question of what they are used for. :) Mike Stone
Alvaro Herrera <alvherre@surnet.cl> writes:
> This was the standard wisdom with releases previous to 8.0; I'm not sure
> if anyone confirmed to still hold after the buffer manager changes in
> 8.0 and later in 8.1 -- we saw extensive redesign of the bufmgr on both,
> so the behavior may have changed. If you wanna test, I'm sure lots of
> people here will be interested in the results.
Quite. The story at the moment is that we haven't bothered to create
support for shared memory exceeding 2Gb, because there's never been any
evidence that pushing shared_buffers up even close to that, much less
above it, was a good idea. Most people have found the "sweet spot" to
be in the range of 10K to 50K shared buffers, with performance dropping
off above that.
Obviously we'd be willing to do this work if there were convincing
evidence it'd be worth the time. A benchmark showing performance
continuing to climb with increasing shared_buffers right up to the 2Gb
limit would be reasonably convincing. I think there is 0 chance of
drawing such a graph with a pre-8.1 server, because of internal
inefficiencies in the buffer manager ... but with CVS tip the story
might be different.
regards, tom lane
Tom Arthurs wrote: > Yes, shared buffers in postgres are not used for caching Shared buffers in Postgres _are_ used for caching, they just form a secondary cache on top of the kernel's IO cache. Postgres does IO through the filesystem, which is then cached by the kernel. Increasing shared_buffers means that less memory is available for the kernel to cache IO -- increasing shared_buffers has been shown to be a net performance loss beyond a certain point. Still, there is value in shared_buffers as it means we can avoid a read() system call for hot pages. We can also do better buffer replacement in the PG shared buffer than the kernel can do (e.g. treating IO caused by VACUUM specially). > My biggest challenge with solaris/sparc is trying to reduce context > switching. It would be interesting to see if this is improved with current sources, as Tom's bufmgr rewrite should have hopefully have reduced this problem. -Neil
Neil Conway wrote: > Tom Arthurs wrote: > >> Yes, shared buffers in postgres are not used for caching > > > Shared buffers in Postgres _are_ used for caching, they just form a > secondary cache on top of the kernel's IO cache. Postgres does IO > through the filesystem, which is then cached by the kernel. Increasing > shared_buffers means that less memory is available for the kernel to > cache IO -- increasing shared_buffers has been shown to be a net > performance loss beyond a certain point. Still, there is value in > shared_buffers as it means we can avoid a read() system call for hot > pages. We can also do better buffer replacement in the PG shared buffer > than the kernel can do (e.g. treating IO caused by VACUUM specially). > As I recall, one of the performance problems with a large shared_buffers is that there are some commands which require looking at *all* of the shared buffer space. So the larger it gets, the longer those functions take. >> My biggest challenge with solaris/sparc is trying to reduce context >> switching. > > > It would be interesting to see if this is improved with current sources, > as Tom's bufmgr rewrite should have hopefully have reduced this problem. > These might be what was fixed with Tom's rewrite. I don't really know. John =:-> > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Attachment
Tom, > Obviously we'd be willing to do this work if there were convincing > evidence it'd be worth the time. A benchmark showing performance > continuing to climb with increasing shared_buffers right up to the 2Gb > limit would be reasonably convincing. I think there is 0 chance of > drawing such a graph with a pre-8.1 server, because of internal > inefficiencies in the buffer manager ... but with CVS tip the story > might be different. Not that I've seen in testing so far. Your improvements have, fortunately, eliminated the penalty for allocating too much shared buffers as far as I can tell (at least, allocating 70,000 when gains stopped at 15,000 doesn't seem to carry a penalty), but I don't see any progressive gain with increased buffers above the initial ideal. In fact, with clock-sweep the shared_buffer curve is refreshingly flat once it reaches the required level, which will take a lot of the guesswork out of allocating buffers. Regarding 2GB memory allocation, though, we *could* really use support for work_mem and maintenance_mem of > 2GB. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Not that I've seen in testing so far. Your improvements have, fortunately,
> eliminated the penalty for allocating too much shared buffers as far as I can
> tell (at least, allocating 70,000 when gains stopped at 15,000 doesn't seem
> to carry a penalty),
Cool, that's definitely a step forward ;-)
> Regarding 2GB memory allocation, though, we *could* really use support for
> work_mem and maintenance_mem of > 2GB.
Again, let's see some evidence that it's worth putting effort into that.
(Offhand it seems this is probably an easier fix than changing the
shared-memory allocation code; but conventional wisdom is that really
large values of work_mem are a bad idea, and I'm not sure I see the case
for maintenance_work_mem above 2Gb either.)
regards, tom lane
On Tue, 2005-06-07 at 23:50 -0400, Tom Lane wrote: > > Regarding 2GB memory allocation, though, we *could* really use support for > > work_mem and maintenance_mem of > 2GB. > > Again, let's see some evidence that it's worth putting effort into that. > (Offhand it seems this is probably an easier fix than changing the > shared-memory allocation code; but conventional wisdom is that really > large values of work_mem are a bad idea, and I'm not sure I see the case > for maintenance_work_mem above 2Gb either.) We have strong evidence that an in-memory sort is better than an external sort. And strong evidence that a hash-join/aggregate is faster than a sort-merge or sort-aggregate. What other evidence do you need? The idea that work_mem is bad is a workload dependent thing. It assumes that using the memory for other things is useful. That isn't the case for apps with large tables, which just churn through memory with zero gain. In 8.2, I imagine a workload management feature that would limit the allocation of work_mem and maintenance_work_mem, so that they can be more safely allocated to very high values in production. That would open the door to the use of very high work_mem values. Best Regards, Simon Riggs
On Tue, Jun 07, 2005 at 11:50:33PM -0400, Tom Lane wrote: >Again, let's see some evidence that it's worth putting effort into that. >(Offhand it seems this is probably an easier fix than changing the >shared-memory allocation code; but conventional wisdom is that really >large values of work_mem are a bad idea, and I'm not sure I see the case >for maintenance_work_mem above 2Gb either.) Hmm. That would be a fairly hard thing to test, no? I wouldn't expect to see a smooth curve as the value is increased--I'd expect it to remain fairly flat until you hit the sweet spot where you can fit the whole working set into RAM. When you say "2Gb", does that imply that the memory allocation limit in 8.1 has been increased from 1G-1? Mike Stone
I just puhsd 8.0.3 to production on Sunday, and haven't had a time to really monitor it under load, so I can't tell if it's helped the context switch problem yet or not. Neil Conway wrote: > Tom Arthurs wrote: > >> Yes, shared buffers in postgres are not used for caching > > > Shared buffers in Postgres _are_ used for caching, they just form a > secondary cache on top of the kernel's IO cache. Postgres does IO > through the filesystem, which is then cached by the kernel. Increasing > shared_buffers means that less memory is available for the kernel to > cache IO -- increasing shared_buffers has been shown to be a net > performance loss beyond a certain point. Still, there is value in > shared_buffers as it means we can avoid a read() system call for hot > pages. We can also do better buffer replacement in the PG shared buffer > than the kernel can do (e.g. treating IO caused by VACUUM specially). > >> My biggest challenge with solaris/sparc is trying to reduce context >> switching. > > > It would be interesting to see if this is improved with current sources, > as Tom's bufmgr rewrite should have hopefully have reduced this problem. > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > >
Hi, > I just puhsd 8.0.3 to production on Sunday, and haven't had a time to > really monitor it under load, so I can't tell if it's helped the context > switch problem yet or not. Attached is a "vmstat 5" output from one of our machines. This is a dual Xeon 3,2 Ghz with EM64T and 8 GB RAM, running postgresql 8.0.3 on Debian Sarge 64bit. Connection count is about 350. Largest amount of cs per second is nearly 10000 which is high, yes, but not too high. Regards, Bjoern # vmstat 5 procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 1 0 0 332004 52 6508160 0 0 28 55 5 9 5 1 94 0 0 0 0 335268 52 6509452 0 0 162 2195 2210 13633 34 7 57 2 2 0 0 303996 52 6510200 0 0 89 151 1909 26634 39 8 52 1 3 0 0 305772 52 6510676 0 0 41 40 1934 45525 54 12 34 0 4 0 0 283700 52 6511900 0 0 122 115 2175 36937 59 13 28 0 2 0 0 283132 52 6512444 0 0 88 137 1965 41128 57 12 31 0 1 0 0 277940 52 6513056 0 0 24 88 1899 47906 47 10 43 0 2 0 0 282404 52 6513668 0 0 54 51 1901 37858 47 9 44 0 3 0 0 283996 52 6514212 0 0 59 1675 2028 33609 49 10 40 1 4 0 0 282372 52 6514892 0 0 86 81 2046 31513 57 9 33 1 3 0 0 279228 52 6515300 0 0 18 88 1876 14465 41 5 54 0 3 0 0 288156 52 6516048 0 0 130 632 1944 25456 45 7 47 1 3 0 0 284884 52 6516592 0 0 66 60 1907 27620 56 8 35 0 3 0 0 279356 52 6516932 0 0 38 97 1950 45386 57 10 33 0 3 0 0 294764 52 6517476 0 0 45 52 1823 27900 40 7 53 0 4 0 0 295348 52 6518020 0 0 78 1352 1938 6048 16 4 79 1 2 0 0 282260 52 6518496 0 0 45 100 1954 14304 47 10 42 0 1 0 0 282708 52 6520196 0 0 288 62 2007 8705 29 6 64 1 4 0 0 292868 52 6520468 0 0 29 983 1829 6634 28 4 68 0 0 1 0 284380 52 6521148 0 0 114 163 2035 7017 23 4 72 1 1 0 0 281572 52 6522032 0 0 102 180 1861 7577 19 4 76 1 2 0 0 286668 52 6522440 0 0 75 150 1870 11185 30 5 65 1 0 0 0 293964 52 6523188 0 0 58 1533 2122 8174 23 5 71 1 1 0 0 287940 52 6523732 0 0 93 127 2001 11732 28 5 66 1 1 0 0 283428 52 6523936 0 0 40 89 1941 6360 20 4 75 0 1 0 0 280492 52 6524752 0 0 110 78 1912 5732 19 3 76 1 0 0 0 275684 52 6525160 0 0 67 55 2025 15541 25 6 69 1
Joshua D. Drake wrote: >> Yes - we have seen with oracle 64 bit that there can be as much as a >> 10% hit moving >> from 32 - but we make it up big time with large db-buffer sizes that >> drastically > Well for Opteron you should also gain from the very high memory > bandwidth and the fact that it has I believe "3" FP units per CPU. Sure. But you get those benefits in 32 or 64-bit mode. Sam.
Tom Arthurs wrote: > I just puhsd 8.0.3 to production on Sunday, and haven't had a time to > really monitor it under load, so I can't tell if it's helped the context > switch problem yet or not. 8.0 is unlikely to make a significant difference -- by "current sources" I meant the current CVS HEAD sources (i.e. 8.1devel). -Neil