Thread: shared_buffers > 284263 on OS X
I'm trying to optimize a PostgreSQL 8.1.5 database running on an Apple G5 Xserve (dual G5 2.3 GHz w/ 8GB of RAM), running Mac OS X 10.4.8 Server. The queries on the database are mostly reads, and I know a larger shared memory allocation will help performance (also by comparing it to the performance of the same database running on a SUSE Linux box, which has a higher shared_buffers setting). When I set shared_buffers above 284263 (~ 2.17 GB) in the postgresql.conf file, I get the standard error message when trying to start the db: FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=3289776128, 03600). shmmax and shmall are set to 4GB, as can be seen by the output from sysctl: hw.physmem = 2147483648 hw.usermem = 1885794304 hw.memsize = 8589934592 kern.sysv.shmmax: 4294967296 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1048576 Has anyone else noticed this limitation on OS X? Any ideas on how I might get shared_buffers higher than 284263? Brian Wipf <brian@clickspace.com>
Brian, On 16-Nov-06, at 7:03 PM, Brian Wipf wrote: > I'm trying to optimize a PostgreSQL 8.1.5 database running on an > Apple G5 Xserve (dual G5 2.3 GHz w/ 8GB of RAM), running Mac OS X > 10.4.8 Server. > > The queries on the database are mostly reads, and I know a larger > shared memory allocation will help performance (also by comparing > it to the performance of the same database running on a SUSE Linux > box, which has a higher shared_buffers setting). > > When I set shared_buffers above 284263 (~ 2.17 GB) in the > postgresql.conf file, I get the standard error message when trying > to start the db: > > FATAL: could not create shared memory segment: Cannot allocate memory > DETAIL: Failed system call was shmget(key=5432001, > size=3289776128, 03600). > > shmmax and shmall are set to 4GB, as can be seen by the output from > sysctl: > hw.physmem = 2147483648 > hw.usermem = 1885794304 > hw.memsize = 8589934592 > kern.sysv.shmmax: 4294967296 > kern.sysv.shmmin: 1 > kern.sysv.shmmni: 32 > kern.sysv.shmseg: 8 > kern.sysv.shmall: 1048576 > > Has anyone else noticed this limitation on OS X? Any ideas on how I > might get shared_buffers higher than 284263? My guess is something else has taken shared memory ahead of you. OS X seems to be somewhat strange in how it deals with shared memory. Try allocating more to shmmax ? Dave > > Brian Wipf > <brian@clickspace.com> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Dave Cramer <pg@fastcrypt.com> writes:
> On 16-Nov-06, at 7:03 PM, Brian Wipf wrote:
>> Has anyone else noticed this limitation on OS X? Any ideas on how I
>> might get shared_buffers higher than 284263?
> My guess is something else has taken shared memory ahead of you. OS X
> seems to be somewhat strange in how it deals with shared memory. Try
> allocating more to shmmax ?
Look in "ipcs -m -a" output to check this theory. (I am glad to see
that ipcs and ipcrm are finally there in recent OS X releases --- awhile
back they were not, leaving people to fly entirely blind while dealing
with issues like this :-()
regards, tom lane
Brian Wipf <brian@shoptoit.ca> wrote: > I'm trying to optimize a PostgreSQL 8.1.5 database running on an > Apple G5 Xserve (dual G5 2.3 GHz w/ 8GB of RAM), running Mac OS X > 10.4.8 Server. > > The queries on the database are mostly reads, and I know a larger > shared memory allocation will help performance (also by comparing it > to the performance of the same database running on a SUSE Linux box, > which has a higher shared_buffers setting). > > When I set shared_buffers above 284263 (~ 2.17 GB) in the > postgresql.conf file, I get the standard error message when trying to > start the db: It might be, that you hit an upper limit in Mac OS X: [galadriel: memtext ] cug $ ./test test(291) malloc: *** vm_allocate(size=2363490304) failed (error code=3) test(291) malloc: *** error: can't allocate region test(291) malloc: *** set a breakpoint in szone_error to debug max alloc = 2253 M That seems near the size you found to work. I don't really know much about that, but it seems you just can't alloc more memory than a bit over 2GB. So, be careful with my non-existing knowledge about that ... ;-) cug
Hi. I've sent this out once, but I think it didn't make it through the mail server ... don't know why. If it is a double post - sorry for it. Brian Wipf <brian@shoptoit.ca> wrote: > I'm trying to optimize a PostgreSQL 8.1.5 database running on an > Apple G5 Xserve (dual G5 2.3 GHz w/ 8GB of RAM), running Mac OS X > 10.4.8 Server. > > The queries on the database are mostly reads, and I know a larger > shared memory allocation will help performance (also by comparing it > to the performance of the same database running on a SUSE Linux box, > which has a higher shared_buffers setting). > > When I set shared_buffers above 284263 (~ 2.17 GB) in the > postgresql.conf file, I get the standard error message when trying to > start the db: It might be, that you hit an upper limit in Mac OS X: [galadriel: memtext ] cug $ ./test test(291) malloc: *** vm_allocate(size=2363490304) failed (error code=3) test(291) malloc: *** error: can't allocate region test(291) malloc: *** set a breakpoint in szone_error to debug max alloc = 2253 M That seems near the size you found to work. I don't really know much about that, but it seems you just can't alloc more memory than a bit over 2GB. So, be careful with my non-existing knowledge about that ... ;-) cug
On 18-Nov-06, at 11:30 AM, Tom Lane wrote: > Dave Cramer <pg@fastcrypt.com> writes: >> On 16-Nov-06, at 7:03 PM, Brian Wipf wrote: >>> Has anyone else noticed this limitation on OS X? Any ideas on how I >>> might get shared_buffers higher than 284263? > >> My guess is something else has taken shared memory ahead of you. OS X >> seems to be somewhat strange in how it deals with shared memory. Try >> allocating more to shmmax ? > > Look in "ipcs -m -a" output to check this theory. (I am glad to see > that ipcs and ipcrm are finally there in recent OS X releases --- > awhile > back they were not, leaving people to fly entirely blind while dealing > with issues like this :-() ipcs -m -a Shared Memory: T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME m 196607 5432001 --rw------- postgres postgres postgres postgres 8 -2100436992 223 223 23:00:07 2:49:44 23:00:07 (I also bumped shmmax and shmall to 6GB with the same shared_buffers limit.) It certainly is unfortunate if Guido's right and this is an upper limit for OS X. The performance benefit of having high shared_buffers on our mostly read database is remarkable.
Am 19.11.2006 um 04:13 schrieb Brian Wipf: > It certainly is unfortunate if Guido's right and this is an upper > limit for OS X. The performance benefit of having high > shared_buffers on our mostly read database is remarkable. I hate to say that, but if you want best performance out of PostgreSQL, Mac OS X (Server) isn't the best OS to achieve this. This might change in the future (who knows), but currently you get more out of Linux. Brendan might have some of my old benchmarks. We wrote a couple of mails about that a couple of months ago. If you're interested, I can run a pgbench benchmark on my desktop machine in the company comparing Mac OS X Tiger to Yellow Dog Linux with 8.1.5 and 8.2beta3. If I remember correctly I have YDL installed on a second hard drive and should be about a couple of minutes to install the latest PostgreSQL release. So, there is no need for you to do the testing of YDL on your Xserves without knowing pretty much for sure, that it will bring you some benefit. As far as I remember I got around 50% to 80% better performance with Linux on the same machine with same settings but that was in times when I hardly new anything about optimizing the OS and PostgreSQL for OLTP performance. Some hints from what I have learned in the past about PostgreSQL on Mac OS X / Apple machines: - Turn off Spotlight on all harddrives on the server (in /etc/ hostconfig) - Use the latest compilers (gcc) and PostgreSQL versions (I'm sure, you do ... ;-)). - If you need the highest possible performance, use Linux instead of Mac OS X for the DB server. :-/ I know that some of the tips don't help with your current setup. Perhaps the switch to Linux on the DB machines might help. But I don't know whether they work good with the XserveRAID you have. Might bring you some headache - I don't know, perhaps you can find opinions on the net. Regarding the memory test I also tried it on Leopard and it seems that the problem persists. Perhaps someone from Apple can say something about that. We might ask on the Darwin list. I'll post some results tomorrow. cug
Am 18.11.2006 um 19:44 schrieb Guido Neitzer: > It might be, that you hit an upper limit in Mac OS X: > > [galadriel: memtext ] cug $ ./test > test(291) malloc: *** vm_allocate(size=2363490304) failed (error > code=3) > test(291) malloc: *** error: can't allocate region > test(291) malloc: *** set a breakpoint in szone_error to debug > max alloc = 2253 M Compiled with 64 Bit support the test program doesn't bring an error. I have now tried to compile PostgreSQL as a 64 Bit binary on Mac OS X but wasn't able to do so. I'm running against the wall with my attempts but I must admit that I'm not an expert on that low level C stuff. I tried with setting the CFLAGS env variable to '-mpowerpc64 - mcpu=970 -m64' but with that, I'm not able to compile PostgreSQL on my G5. Has someone hints for that? cug
On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: > It certainly is unfortunate if Guido's right and this is an upper > limit for OS X. The performance benefit of having high shared_buffers > on our mostly read database is remarkable. Got any data about that you can share? People have been wondering about cases where drastically increasing shared_buffers makes a difference. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
I think the main issue is that we can't seem to get PostgreSQL compiled for 64 bit on OS X on an Xserve G5. Has anyone done that? We have 8 GB of RAM on that server, but we can't seem to utilize it all. At least not for the shared_buffers setting. Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Nov 26, 2006, at 4:25 PM, Jim C. Nasby wrote: > On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: >> It certainly is unfortunate if Guido's right and this is an upper >> limit for OS X. The performance benefit of having high shared_buffers >> on our mostly read database is remarkable. > > Got any data about that you can share? People have been wondering > about > cases where drastically increasing shared_buffers makes a difference. > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Brendan Duddridge <brendan@clickspace.com> writes:
> I think the main issue is that we can't seem to get PostgreSQL
> compiled for 64 bit on OS X on an Xserve G5. Has anyone done that?
There is no obvious reason why it would not work, and if anyone has
tried and failed, they've not bothered to provide details on any PG list
I read ...
regards, tom lane
On 27-Nov-06, at 4:04 AM, Tom Lane wrote: > Brendan Duddridge <brendan@clickspace.com> writes: >> I think the main issue is that we can't seem to get PostgreSQL >> compiled for 64 bit on OS X on an Xserve G5. Has anyone done that? > > There is no obvious reason why it would not work, and if anyone has > tried and failed, they've not bothered to provide details on any PG > list > I read ... I'll post details of the problems I've had compiling for 64-bit on OS X Tiger to the pgsql-ports when I get a chance. Brian Wipf
Am 27.11.2006 um 04:20 schrieb Brendan Duddridge: > I think the main issue is that we can't seem to get PostgreSQL > compiled for 64 bit on OS X on an Xserve G5. Has anyone done that? > > We have 8 GB of RAM on that server, but we can't seem to utilize it > all. At least not for the shared_buffers setting. One VERY ugly idea is: if you have your stuff in more than one db, let two PostgreSQL installations run on the same machine and put some databases on one and others on the second installation (on different ports and different data directories of course) and give either one the 2GB shared mem you like. So you can use the 50% of the available RAM. I don't know whether Mac OS X itself is able to handle a larger amount of shared memory but I believe it can. But nevertheless this is only a very ugly workaround on a problem that shouldn't exist. The correct way would be to get a 64 Bit binary of PostgreSQL - which I wasn't able to create. But, be aware of another thing here: As far as I have read about 64 Bit applications on G5, these apps are definitely slower than their 32 bit counterparts (I'm currently on the train so I can't be more precise here without Google ...). Was it something with not enough registers in the CPU? Something like that ... So it might be, that the 64 bit version is able to use more shared memory but is slower than the 32 bit version and you come out with the same performance. Nobody knows ... cug
Am 27.11.2006 um 00:25 schrieb Jim C. Nasby: > Got any data about that you can share? People have been wondering > about > cases where drastically increasing shared_buffers makes a difference. I have tried to compile PostgreSQL as a 64Bit application on my G5 but wasn't successful. But I must admit, that I'm not a C programmer at all. I know enough to work with source packages and configure / make but not enough to work with the errors I got from the compile. And as I'm extremely busy right now, I can't follow the trail and learn more about it. Perhaps someone with more knowledge can take a look at it. cug
On 26-Nov-06, at 11:25 PM, Jim C. Nasby wrote: > On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: >> It certainly is unfortunate if Guido's right and this is an upper >> limit for OS X. The performance benefit of having high shared_buffers >> on our mostly read database is remarkable. > > Got any data about that you can share? People have been wondering > about > cases where drastically increasing shared_buffers makes a difference. Unfortunately, there are more differences than just the shared_buffers setting in production right now; it's a completely different set up, so the numbers I have to compare against aren't particularly useful. When I get the chance, I will try to post data that shows the benefit of having a higher value of shared_buffers for our usage pattern (with all other settings being constant -- well, except maybe effective_cache_size). Basically, in our current configuration, we can cache all of the data we care about 99% of the time in about 3GB of shared_buffers. Having shared_buffers set to 512MB as it was originally, we were needlessly going to disk all of the time. Brian Wipf
Am 27.11.2006 um 08:04 schrieb Guido Neitzer: > But, be aware of another thing here: As far as I have read about 64 > Bit applications on G5, these apps are definitely slower than their > 32 bit counterparts (I'm currently on the train so I can't be more > precise here without Google ...). Was it something with not enough > registers in the CPU? Something like that ... So it might be, that > the 64 bit version is able to use more shared memory but is slower > than the 32 bit version and you come out with the same performance. > Nobody knows ... Some information about that: <http://www.geekpatrol.ca/2006/09/32-bit-vs-64-bit-performance/> So, the impact doesn't seem to high. So it seems to depend on the usage pattern whether the 32 bit with less RAM and slightly higher performance might be faster than 64 bit with more shared memory and slightly lower performance. cug
On Mon, Nov 27, 2006 at 07:23:47AM +0000, Brian Wipf wrote: > On 26-Nov-06, at 11:25 PM, Jim C. Nasby wrote: > >On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: > >>It certainly is unfortunate if Guido's right and this is an upper > >>limit for OS X. The performance benefit of having high shared_buffers > >>on our mostly read database is remarkable. > > > >Got any data about that you can share? People have been wondering > >about > >cases where drastically increasing shared_buffers makes a difference. > > Unfortunately, there are more differences than just the > shared_buffers setting in production right now; it's a completely > different set up, so the numbers I have to compare against aren't > particularly useful. > > When I get the chance, I will try to post data that shows the benefit > of having a higher value of shared_buffers for our usage pattern > (with all other settings being constant -- well, except maybe > effective_cache_size). Basically, in our current configuration, we > can cache all of the data we care about 99% of the time in about 3GB > of shared_buffers. Having shared_buffers set to 512MB as it was > originally, we were needlessly going to disk all of the time. Disk or to the kernel cache? -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Nov 27, 2006, at 2:23 , Brian Wipf wrote: > On 26-Nov-06, at 11:25 PM, Jim C. Nasby wrote: >> On Sat, Nov 18, 2006 at 08:13:26PM -0700, Brian Wipf wrote: >>> It certainly is unfortunate if Guido's right and this is an upper >>> limit for OS X. The performance benefit of having high >>> shared_buffers >>> on our mostly read database is remarkable. >> >> Got any data about that you can share? People have been wondering >> about >> cases where drastically increasing shared_buffers makes a difference. > > Unfortunately, there are more differences than just the > shared_buffers setting in production right now; it's a completely > different set up, so the numbers I have to compare against aren't > particularly useful. > > When I get the chance, I will try to post data that shows the > benefit of having a higher value of shared_buffers for our usage > pattern (with all other settings being constant -- well, except > maybe effective_cache_size). Basically, in our current > configuration, we can cache all of the data we care about 99% of > the time in about 3GB of shared_buffers. Having shared_buffers set > to 512MB as it was originally, we were needlessly going to disk all > of the time. There is a known unfortunate limitation on Darwin for SysV shared memory which, incidentally, does not afflict POSIX or mmap'd shared memory. http://archives.postgresql.org/pgsql-patches/2006-02/msg00176.php
Am 27.11.2006 um 17:05 schrieb AgentM: > There is a known unfortunate limitation on Darwin for SysV shared > memory which, incidentally, does not afflict POSIX or mmap'd shared > memory. Hmmm. The article from Chris you have linked does not mention the size of the mem segment you can allocate. Nevertheless - if you compile a 32 Bit binary, there is the limitation Brian mentioned. You can easily simulate this with a small C program that allocates memory - if you compile it as 64 Bit binary - not problem, if you compile as 32 Bit - crash. cug
I wanted to post a follow up to the list regarding a high shared_buffers value on OS X 10.4.8. Thanks to Tom's help we successfully compiled PostgreSQL 8.1.5 using 64-bit on OS X Server 10.4.8 (You can find info. for this on pgports) shared_buffers can now be set as high as shmmax without getting the error message "could not create shared memory segment...". Now, however, when shared_buffers are set greater than 279212 a segmentation fault occurs on startup of PostgreSQL. While trying to quantify the performance difference with higher shared_buffers versus relying more on the kernel cache, the difference does not appear as significant as I thought. We currently have shared_buffers set to about 25% of system memory on our box where we are free to set it within the bounds of shmmax (not OS X, of course). Brian Wipf <brian@clickspace.com>
Brian Wipf <brian@clickspace.com> writes:
> shared_buffers can now be set as high as shmmax without getting the
> error message "could not create shared memory segment...". Now,
> however, when shared_buffers are set greater than 279212 a
> segmentation fault occurs on startup of PostgreSQL.
Stack trace please?
regards, tom lane
On 5-Dec-06, at 4:10 PM, Tom Lane wrote:
> Brian Wipf <brian@clickspace.com> writes:
>> shared_buffers can now be set as high as shmmax without getting the
>> error message "could not create shared memory segment...". Now,
>> however, when shared_buffers are set greater than 279212 a
>> segmentation fault occurs on startup of PostgreSQL.
>
> Stack trace please?
Normally, when something crashes in OS X, I get a stack trace in the /
Library/Logs/CrashReporter/crashdump.crash.log. Unfortunately, the
only stack trace in crashdump.crash.log appears to be for
CrashReporter itself crashing. A guess is that crashreporterd is only
32-bit and can't handle generating a report for the crash of a 64-bit
program.
# file /usr/libexec/crashreporterd
/usr/libexec/crashreporterd: Mach-O executable ppc
Running gdb on the only generated core file shows:
/cores root# gdb /usr/local/pgsql/bin/postgres core.708
GNU gdb 6.3.50-20050815 (Apple version gdb-573) (Fri Oct 20 15:54:33
GMT 2006)
Core was generated by `/usr/libexec/crashdump'.
#0 0x00000000fffeff20 in ?? ()
(gdb) bt
#0 0x00000000fffeff20 in ?? ()
Cannot access memory at address 0xbffffa8000000000
#1 0x000000009293d968 in ?? ()
Cannot access memory at address 0xbffffa8000000000
Cannot access memory at address 0xbffffa8000000010
which appears to be for Crash Reporter crashing. Any idea how I might
be able to get a useful stack trace?
All crashdump.crash.log shows is:
Host Name: Hulk1
Date/Time: 2006-12-05 23:36:51.928 +0000
OS Version: 10.4.8 (Build 8L127)
Report Version: 4
Command: crashdump
Path: /usr/libexec/crashdump
Parent: crashreporterd [112]
Version: ??? (???)
PID: 708
Thread: 0
Exception: EXC_BAD_ACCESS (0x0001)
Codes: KERN_INVALID_ADDRESS (0x0001) at 0x789421ff
Thread 0 Crashed:
0 <<00000000>> 0xfffeff20 objc_msgSend_rtp + 32
1 com.apple.Foundation 0x9293d968 NSPopAutoreleasePool + 536
2 crashdump 0x00005c58 0x1000 + 19544
3 crashdump 0x00005cec 0x1000 + 19692
4 crashdump 0x00002954 0x1000 + 6484
5 crashdump 0x000027f8 0x1000 + 6136
Thread 0 crashed with PPC Thread State 64:
srr0: 0x00000000fffeff20 srr1:
0x100000000000f030 vrsave: 0x0000000000000000
cr: 0x44000234 xer: 0x0000000000000000 lr:
0x000000009293d968 ctr: 0x0000000000000001
r0: 0x0000000000000001 r1: 0x00000000bffff620 r2:
0x00000000789421ff r3: 0x0000000000308bc0
r4: 0x0000000090aa8904 r5: 0x0000000001800000 r6:
0xffffffffffffffff r7: 0x0000000001807400
r8: 0x0000000000000001 r9: 0x0000000000030000 r10:
0x0000000000000005 r11: 0x000000006f548904
r12: 0x000000000000357b r13: 0x0000000000000000 r14:
0x0000000000000000 r15: 0x0000000000000000
r16: 0x0000000000000000 r17: 0x0000000000000000 r18:
0x0000000000000000 r19: 0x0000000000000000
r20: 0x0000000000000000 r21: 0x0000000000000000 r22:
0x0000000000000000 r23: 0x0000000000000000
r24: 0x0000000000000000 r25: 0x0000000000306990 r26:
0x0000000000010000 r27: 0x0000000000000001
r28: 0x0000000000010000 r29: 0x0000000000308bc0 r30:
0x000000000000d02c r31: 0x000000009293d764
Binary Images Description:
0x1000 - 0xbfff crashdump /usr/libexec/crashdump
0x8fe00000 - 0x8fe51fff dyld 45.3 /usr/lib/dyld
0x90000000 - 0x901bcfff libSystem.B.dylib /usr/lib/
libSystem.B.dylib
0x90214000 - 0x90219fff libmathCommon.A.dylib /usr/lib/system/
libmathCommon.A.dylib
0x9021b000 - 0x90268fff com.apple.CoreText 1.0.2 (???) /System/
Library/Frameworks/ApplicationServices.framework/Versions/A/
Frameworks/CoreText.framework/Versions/A/CoreText
...
...
0x94281000 - 0x94281fff com.apple.audio.units.AudioUnit 1.4 /
System/Library/Frameworks/AudioUnit.framework/Versions/A/AudioUnit
0x94283000 - 0x94456fff com.apple.QuartzCore 1.4.9 /System/
Library/Frameworks/QuartzCore.framework/Versions/A/QuartzCore
0x944ac000 - 0x944e9fff libsqlite3.0.dylib /usr/lib/
libsqlite3.0.dylib
0x944f1000 - 0x94541fff libGLImage.dylib /System/Library/
Frameworks/OpenGL.framework/Versions/A/Libraries/libGLImage.dylib
0x945d2000 - 0x94614fff com.apple.vmutils 4.0.2 (93.1) /System/
Library/PrivateFrameworks/vmutils.framework/Versions/A/vmutils