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>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.


---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings