pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedshared_buffers > 284263 on OS X
21+ messages / 8 participants
[nested] [flat]
* shared_buffers > 284263 on OS X
@ 2006-11-17 00:03 Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 17:48 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
2006-11-18 18:44 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
0 siblings, 3 replies; 21+ messages in thread
From: Brian Wipf @ 2006-11-17 00:03 UTC (permalink / raw)
To: pgsql-performance
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>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
@ 2006-11-18 16:17 ` Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 21+ messages in thread
From: Dave Cramer @ 2006-11-18 16:17 UTC (permalink / raw)
To: Brian Wipf <brian@shoptoit.ca>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
@ 2006-11-18 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2006-11-18 18:30 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: Brian Wipf <brian@shoptoit.ca>; pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-11-19 03:13 ` Brian Wipf <brian@clickspace.com>
2006-11-19 15:20 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
0 siblings, 2 replies; 21+ messages in thread
From: Brian Wipf @ 2006-11-19 03:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
@ 2006-11-19 15:20 ` Guido Neitzer <lists@event-s.net>
1 sibling, 0 replies; 21+ messages in thread
From: Guido Neitzer @ 2006-11-19 15:20 UTC (permalink / raw)
To: Brian Wipf <brian@clickspace.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
@ 2006-11-26 23:25 ` Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Re: shared_buffers > 284263 on OS X Brendan Duddridge <brendan@clickspace.com>
2006-11-27 07:04 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
2006-11-27 07:23 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
1 sibling, 3 replies; 21+ messages in thread
From: Jim C. Nasby @ 2006-11-26 23:25 UTC (permalink / raw)
To: Brian Wipf <brian@clickspace.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
@ 2006-11-27 03:20 ` Brendan Duddridge <brendan@clickspace.com>
2006-11-27 04:04 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-27 07:04 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
2 siblings, 2 replies; 21+ messages in thread
From: Brendan Duddridge @ 2006-11-27 03:20 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Brian Wipf <brian@clickspace.com>; Tom Lane <tgl@sss.pgh.pa.us>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Re: shared_buffers > 284263 on OS X Brendan Duddridge <brendan@clickspace.com>
@ 2006-11-27 04:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-27 04:10 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
1 sibling, 1 reply; 21+ messages in thread
From: Tom Lane @ 2006-11-27 04:04 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Brian Wipf <brian@clickspace.com>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Re: shared_buffers > 284263 on OS X Brendan Duddridge <brendan@clickspace.com>
2006-11-27 04:04 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-11-27 04:10 ` Brian Wipf <brian@clickspace.com>
0 siblings, 0 replies; 21+ messages in thread
From: Brian Wipf @ 2006-11-27 04:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brendan Duddridge <brendan@clickspace.com>; Jim C. Nasby <jim@nasby.net>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Re: shared_buffers > 284263 on OS X Brendan Duddridge <brendan@clickspace.com>
@ 2006-11-27 07:04 ` Guido Neitzer <lists@event-s.net>
2006-11-27 07:35 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
1 sibling, 1 reply; 21+ messages in thread
From: Guido Neitzer @ 2006-11-27 07:04 UTC (permalink / raw)
To: Brendan Duddridge <brendan@clickspace.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Re: shared_buffers > 284263 on OS X Brendan Duddridge <brendan@clickspace.com>
2006-11-27 07:04 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
@ 2006-11-27 07:35 ` Guido Neitzer <lists@event-s.net>
0 siblings, 0 replies; 21+ messages in thread
From: Guido Neitzer @ 2006-11-27 07:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
@ 2006-11-27 07:04 ` Guido Neitzer <lists@event-s.net>
2 siblings, 0 replies; 21+ messages in thread
From: Guido Neitzer @ 2006-11-27 07:04 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
@ 2006-11-27 07:23 ` Brian Wipf <brian@clickspace.com>
2006-11-27 09:22 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 16:05 ` Re: shared_buffers > 284263 on OS X AgentM <agentm@themactionfaction.com>
2 siblings, 2 replies; 21+ messages in thread
From: Brian Wipf @ 2006-11-27 07:23 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 07:23 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
@ 2006-11-27 09:22 ` Jim C. Nasby <jim@nasby.net>
1 sibling, 0 replies; 21+ messages in thread
From: Jim C. Nasby @ 2006-11-27 09:22 UTC (permalink / raw)
To: Brian Wipf <brian@clickspace.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dave Cramer <pg@fastcrypt.com>; pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>
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)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 07:23 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
@ 2006-11-27 16:05 ` AgentM <agentm@themactionfaction.com>
2006-11-27 16:21 ` Re: shared_buffers > 284263 on OS X Guido Neitzer <lists@event-s.net>
1 sibling, 1 reply; 21+ messages in thread
From: AgentM @ 2006-11-27 16:05 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Re: shared_buffers > 284263 on OS X Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-26 23:25 ` Re: shared_buffers > 284263 on OS X Jim C. Nasby <jim@nasby.net>
2006-11-27 07:23 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-11-27 16:05 ` Re: shared_buffers > 284263 on OS X AgentM <agentm@themactionfaction.com>
@ 2006-11-27 16:21 ` Guido Neitzer <lists@event-s.net>
0 siblings, 0 replies; 21+ messages in thread
From: Guido Neitzer @ 2006-11-27 16:21 UTC (permalink / raw)
To: AgentM <agentm@themactionfaction.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
@ 2006-11-18 17:48 ` Guido Neitzer <lists@event-s.net>
2 siblings, 0 replies; 21+ messages in thread
From: lists@event-s.net @ 2006-11-18 17:48 UTC (permalink / raw)
To: brian@shoptoit.ca; pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
@ 2006-11-18 18:44 ` Guido Neitzer <lists@event-s.net>
2 siblings, 0 replies; 21+ messages in thread
From: Guido Neitzer @ 2006-11-18 18:44 UTC (permalink / raw)
To: Brian Wipf <brian@shoptoit.ca>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
@ 2006-12-05 23:06 Brian Wipf <brian@clickspace.com>
2006-12-05 23:10 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Brian Wipf @ 2006-12-05 23:06 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Guido Neitzer <lists@event-s.net>; Brendan Duddridge <brendan@clickspace.com>; Jim C. Nasby <jim@nasby.net>
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>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-12-05 23:06 Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
@ 2006-12-05 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-06 00:06 ` Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2006-12-05 23:10 UTC (permalink / raw)
To: Brian Wipf <brian@clickspace.com>; +Cc: pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>; Brendan Duddridge <brendan@clickspace.com>; Jim C. Nasby <jim@nasby.net>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: shared_buffers > 284263 on OS X
2006-12-05 23:06 Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-12-05 23:10 ` Re: shared_buffers > 284263 on OS X Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-12-06 00:06 ` Brian Wipf <brian@clickspace.com>
0 siblings, 0 replies; 21+ messages in thread
From: Brian Wipf @ 2006-12-06 00:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@postgresql.org, Guido Neitzer <lists@event-s.net>; Brendan Duddridge <brendan@clickspace.com>; Jim C. Nasby <jim@nasby.net>
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
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2006-12-06 00:06 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-17 00:03 shared_buffers > 284263 on OS X Brian Wipf <brian@shoptoit.ca>
2006-11-18 16:17 ` Dave Cramer <pg@fastcrypt.com>
2006-11-18 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-19 03:13 ` Brian Wipf <brian@clickspace.com>
2006-11-19 15:20 ` Guido Neitzer <lists@event-s.net>
2006-11-26 23:25 ` Jim C. Nasby <jim@nasby.net>
2006-11-27 03:20 ` Brendan Duddridge <brendan@clickspace.com>
2006-11-27 04:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-27 04:10 ` Brian Wipf <brian@clickspace.com>
2006-11-27 07:04 ` Guido Neitzer <lists@event-s.net>
2006-11-27 07:35 ` Guido Neitzer <lists@event-s.net>
2006-11-27 07:04 ` Guido Neitzer <lists@event-s.net>
2006-11-27 07:23 ` Brian Wipf <brian@clickspace.com>
2006-11-27 09:22 ` Jim C. Nasby <jim@nasby.net>
2006-11-27 16:05 ` AgentM <agentm@themactionfaction.com>
2006-11-27 16:21 ` Guido Neitzer <lists@event-s.net>
2006-11-18 17:48 ` Guido Neitzer <lists@event-s.net>
2006-11-18 18:44 ` Guido Neitzer <lists@event-s.net>
2006-12-05 23:06 Re: shared_buffers > 284263 on OS X Brian Wipf <brian@clickspace.com>
2006-12-05 23:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-06 00:06 ` Brian Wipf <brian@clickspace.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox