Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 3: 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] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Postgresql on an AMD64 machine
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 1 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 sobibo incs us sy id wa 1 0 0 332004 52 65081600028555 9 5 1 94 0 0 0 0 335268 52 650945200 162 2195 2210 13633 34 7 57 2 2 0 0 303996 52 65102000089 151 1909 26634 39 8 52 1 3 0 0 305772 52 6510676004140 1934 45525 54 12 34 0 4 0 0 283700 52 651190000 122 115 2175 36937 59 13 28 0 2 0 0 283132 52 65124440088 137 1965 41128 57 12 31 0 1 0 0 277940 52 6513056002488 1899 47906 47 10 43 0 2 0 0 282404 52 6513668005451 1901 37858 47 9 44 0 3 0 0 283996 52 65142120059 1675 2028 33609 49 10 40 1 4 0 0 282372 52 6514892008681 2046 31513 57 9 33 1 3 0 0 279228 52 6515300001888 1876 14465 41 5 54 0 3 0 0 288156 52 651604800 130 632 1944 25456 45 7 47 1 3 0 0 284884 52 6516592006660 1907 27620 56 8 35 0 3 0 0 279356 52 6516932003897 1950 45386 57 10 33 0 3 0 0 294764 52 6517476004552 1823 27900 40 7 53 0 4 0 0 295348 52 65180200078 1352 1938 6048 16 4 79 1 2 0 0 282260 52 65184960045 100 1954 14304 47 10 42 0 1 0 0 282708 52 652019600 28862 2007 8705 29 6 64 1 4 0 0 292868 52 65204680029 983 1829 6634 28 4 68 0 0 1 0 284380 52 652114800 114 163 2035 7017 23 4 72 1 1 0 0 281572 52 652203200 102 180 1861 7577 19 4 76 1 2 0 0 286668 52 65224400075 150 1870 11185 30 5 65 1 0 0 0 293964 52 65231880058 1533 2122 8174 23 5 71 1 1 0 0 287940 52 65237320093 127 2001 11732 28 5 66 1 1 0 0 283428 52 6523936004089 1941 6360 20 4 75 0 1 0 0 280492 52 652475200 11078 1912 5732 19 3 76 1 0 0 0 275684 52 6525160006755 2025 15541 25 6 69 1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgresql on an AMD64 machine
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. ---(end of broadcast)--- TIP 3: 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] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Postgresql on an AMD64 machine
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: 46 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 [EMAIL PROTECTED] 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
Re: [PERFORM] Postgresql on an AMD64 machine
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: 46 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgresql on an AMD64 machine
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/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Postgresql on an AMD64 machine
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: 46 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Postgresql on an AMD64 machine
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: 46 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Postgresql on an AMD64 machine
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) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Postgresql on an AMD64 machine
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Postgresql on an AMD64 machine
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
Re: [PERFORM] Postgresql on an AMD64 machine
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 signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Postgresql on an AMD64 machine
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 600. 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 smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] Postgresql on an AMD64 machine
On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo [EMAIL PROTECTED] 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 [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql on an AMD64 machine
Mike Rylander [EMAIL PROTECTED] writes: On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Postgresql on an AMD64 machine
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 [EMAIL PROTECTED] writes: On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Postgresql on an AMD64 machine
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Postgresql on an AMD64 machine
Neil Conway [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
