pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql on an AMD64 machine
25+ messages / 15 participants
[nested] [flat]
* Postgresql on an AMD64 machine
@ 2005-06-06 17:53 Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 18:32 ` Re: Postgresql on an AMD64 machine Vivek Khera <vivek@khera.org>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
0 siblings, 2 replies; 25+ messages in thread
From: Mark Rinaudo @ 2005-06-06 17:53 UTC (permalink / raw)
To: pgsql-performance
I'm not sure if this is the appropriate list to post this question to
but i'm starting with this one because it is related to the performance
of Postgresql server. I have a Penguin Computing dual AMD 64 bit
opteron machine with 8 Gigs of memory. In my attempt to increase the
number of shared_buffers from the default to 65000 i was running into a
semget error when trying to start Postgresql. After reading the
documentation I adjusted the semaphore settings in the kernel to allow
Postgresql to start successfully. With this configuration running if I
do a ipcs -u i get the following.
------ Shared Memory Status --------
segments allocated 1
pages allocated 30728
pages resident 30626
pages swapped 0
Swap performance: 0 attempts 0 successes
------ Semaphore Status --------
used arrays = 1880
allocated semaphores = 31928
------ Messages: Status --------
allocated queues = 0
used headers = 0
used space = 0 bytes
I'm questioning the number of semaphores being used. In order for
postgresql to start I had to set the maximum number of semaphores system
wide to 6000000. This seems to be an abnormal amount of semaphores. I'm
curious if this is a bug in the amd64 postgresql port. Is anyone else
using postgresql on an AMD64 machine without similar issues?
TIA
Mark
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
@ 2005-06-06 18:32 ` Vivek Khera <vivek@khera.org>
1 sibling, 0 replies; 25+ messages in thread
From: Vivek Khera @ 2005-06-06 18:32 UTC (permalink / raw)
To: pgsql-performance
On Jun 6, 2005, at 1:53 PM, Mark Rinaudo wrote:
> I'm questioning the number of semaphores being used. In order for
> postgresql to start I had to set the maximum number of semaphores
> system
> wide to 6000000. This seems to be an abnormal amount of
> semaphores. I'm
> curious if this is a bug in the amd64 postgresql port. Is anyone else
> using postgresql on an AMD64 machine without similar issues?
>
No such nonsense required for me under FreeBSD 5.4/amd64. I used the
same settings I had under i386 OS. Postgres uses very few
semaphores, from what I recall. My system shows 13 active semaphores.
Vivek Khera, Ph.D.
+1-301-869-4449 x806
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
@ 2005-06-06 20:59 ` Mike Rylander <mrylander@gmail.com>
2005-06-06 21:15 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
1 sibling, 2 replies; 25+ messages in thread
From: Mike Rylander @ 2005-06-06 20:59 UTC (permalink / raw)
To: Mark Rinaudo <mark@bowmansystems.com>; +Cc: pgsql-performance
On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote:
> I'm not sure if this is the appropriate list to post this question to
> but i'm starting with this one because it is related to the performance
> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
> opteron machine with 8 Gigs of memory. In my attempt to increase the
> number of shared_buffers from the default to 65000 i was running into a
> semget error when trying to start Postgresql. After reading the
> documentation I adjusted the semaphore settings in the kernel to allow
> Postgresql to start successfully. With this configuration running if I
> do a ipcs -u i get the following.
On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9):
$ ipcs -u i
------ Shared Memory Status --------
segments allocated 1
pages allocated 34866
pages resident 31642
pages swapped 128
Swap performance: 0 attempts 0 successes
------ Semaphore Status --------
used arrays = 7
allocated semaphores = 119
------ Messages: Status --------
allocated queues = 0
used headers = 0
used space = 0 bytes
Did you perhaps disable spinlocks when compiling PG?
--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
@ 2005-06-06 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 22:28 ` Re: Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
1 sibling, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-06-06 21:15 UTC (permalink / raw)
To: Mike Rylander <mrylander@gmail.com>; +Cc: Mark Rinaudo <mark@bowmansystems.com>; pgsql-performance
Mike Rylander <mrylander@gmail.com> writes:
> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote:
>> I'm not sure if this is the appropriate list to post this question to
>> but i'm starting with this one because it is related to the performance
>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>> opteron machine with 8 Gigs of memory. In my attempt to increase the
>> number of shared_buffers from the default to 65000 i was running into a
>> semget error when trying to start Postgresql.
> Did you perhaps disable spinlocks when compiling PG?
That sure looks like it must be the issue --- in a normal build the
number of semaphores needed does not vary with shared_buffers, but
it will if Postgres is falling back to semaphore-based spinlocks.
Which is a really bad idea from a performance standpoint, so you
want to fix the build.
Which PG version is this exactly, and what configure options did
you use? What compiler was used?
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-06 21:15 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-06 22:28 ` Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 23:48 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 25+ messages in thread
From: Mark Rinaudo @ 2005-06-06 22:28 UTC (permalink / raw)
To: pgsql-performance; +Cc: tgl@sss.pgh.pa.us
I'm running the Redhat Version of Postgresql which came pre-installed
with Redhat ES. It's version number is 7.3.10-1. I'm not sure what
options it was compiled with. Is there a way for me to tell? Should i
just compile my own postgresql for this platform?
Thanks
Mark
On Mon, 2005-06-06 at 16:15, Tom Lane wrote:
> Mike Rylander <mrylander@gmail.com> writes:
> > On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote:
> >> I'm not sure if this is the appropriate list to post this question to
> >> but i'm starting with this one because it is related to the performance
> >> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
> >> opteron machine with 8 Gigs of memory. In my attempt to increase the
> >> number of shared_buffers from the default to 65000 i was running into a
> >> semget error when trying to start Postgresql.
>
> > Did you perhaps disable spinlocks when compiling PG?
>
> That sure looks like it must be the issue --- in a normal build the
> number of semaphores needed does not vary with shared_buffers, but
> it will if Postgres is falling back to semaphore-based spinlocks.
> Which is a really bad idea from a performance standpoint, so you
> want to fix the build.
>
> Which PG version is this exactly, and what configure options did
> you use? What compiler was used?
>
> regards, tom lane
>
--
Mark Rinaudo
318-213-8780 ext 111
Bowman Systems
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-06 21:15 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 22:28 ` Re: Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
@ 2005-06-06 23:48 ` Neil Conway <neilc@samurai.com>
2005-06-07 04:28 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Neil Conway @ 2005-06-06 23:48 UTC (permalink / raw)
To: Mark Rinaudo <mark@bowmansystems.com>; +Cc: pgsql-performance; tgl@sss.pgh.pa.us
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-06 21:15 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 22:28 ` Re: Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 23:48 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
@ 2005-06-07 04:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Tom Lane @ 2005-06-07 04:28 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Mark Rinaudo <mark@bowmansystems.com>; pgsql-performance
Neil Conway <neilc@samurai.com> writes:
> (From a Redhat POV, it would probably be a good idea to patch 7.3 to
> include the relatively trivial changes needed for decent AMD64
> performance,
How embarrassing :-( Will see about fixing it. However, this certainly
won't ship before the next RHEL3 quarterly update, so in the meantime if
Mark feels like building locally, it wouldn't be a bad idea.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
@ 2005-06-07 19:38 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
1 sibling, 1 reply; 25+ messages in thread
From: Donald Courtney @ 2005-06-07 19:38 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Get FATAL when starting up (64 bit) with large shared_buffers setting
I built a 64 bit for Sparc/Solaris easily but I found that the
startup of postmaster generates a FATAL diagnostic due to going
over the 2GB limit (3.7 GB).
When building for 64 bit is there some other
things that must change in order to size UP the shared_buffers?
Thanks.
Don C.
P.S. A severe checkpoint problem I was having was fixed with
"checkpoint_segments=200".
Message:
FATAL: 460000 is outside the valid range for parameter "shared_buffers"
(16 .. 262143)
LOG: database system was shut down at 2005-06-07 15:20:28 EDT
Mike Rylander wrote:
>On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com> wrote:
>
>
>>I'm not sure if this is the appropriate list to post this question to
>>but i'm starting with this one because it is related to the performance
>>of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>>opteron machine with 8 Gigs of memory. In my attempt to increase the
>>number of shared_buffers from the default to 65000 i was running into a
>>semget error when trying to start Postgresql. After reading the
>>documentation I adjusted the semaphore settings in the kernel to allow
>>Postgresql to start successfully. With this configuration running if I
>>do a ipcs -u i get the following.
>>
>>
>
>
>On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9):
>
>$ ipcs -u i
>
>------ Shared Memory Status --------
>segments allocated 1
>pages allocated 34866
>pages resident 31642
>pages swapped 128
>Swap performance: 0 attempts 0 successes
>
>------ Semaphore Status --------
>used arrays = 7
>allocated semaphores = 119
>
>------ Messages: Status --------
>allocated queues = 0
>used headers = 0
>used space = 0 bytes
>
>
>Did you perhaps disable spinlocks when compiling PG?
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
@ 2005-06-07 19:54 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Arthurs @ 2005-06-07 19:54 UTC (permalink / raw)
To: Donald Courtney <Donald.Courtney@sun.com>; +Cc: pgsql-performance
According to my research, you only need a 64 bit image if you are going
to be doing intensive floating point operations (which most db servers
don't do). Some benchmarking results I've found on the internet
indicate that 64 bit executables can be slower than 32 bit versions.
I've been running 32 bit compiles on solaris for several years.
How much memory do you have on that sparc box? Allocating more than
about 7-12% to shared buffers has proven counter productive for us (it
slows down).
Kernel buffers are another animal. :)
Donald Courtney wrote:
> Get FATAL when starting up (64 bit) with large shared_buffers setting
>
> I built a 64 bit for Sparc/Solaris easily but I found that the
> startup of postmaster generates a FATAL diagnostic due to going
> over the 2GB limit (3.7 GB).
>
> When building for 64 bit is there some other
> things that must change in order to size UP the shared_buffers?
>
> Thanks.
>
> Don C.
>
> P.S. A severe checkpoint problem I was having was fixed with
> "checkpoint_segments=200".
>
>
> Message:
>
> FATAL: 460000 is outside the valid range for parameter "shared_buffers"
> (16 .. 262143)
> LOG: database system was shut down at 2005-06-07 15:20:28 EDT
>
> Mike Rylander wrote:
>
>> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com>
>> wrote:
>>
>>
>>> I'm not sure if this is the appropriate list to post this question to
>>> but i'm starting with this one because it is related to the performance
>>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>>> opteron machine with 8 Gigs of memory. In my attempt to increase the
>>> number of shared_buffers from the default to 65000 i was running into a
>>> semget error when trying to start Postgresql. After reading the
>>> documentation I adjusted the semaphore settings in the kernel to allow
>>> Postgresql to start successfully. With this configuration running if I
>>> do a ipcs -u i get the following.
>>>
>>
>>
>>
>> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9):
>>
>> $ ipcs -u i
>>
>> ------ Shared Memory Status --------
>> segments allocated 1
>> pages allocated 34866
>> pages resident 31642
>> pages swapped 128
>> Swap performance: 0 attempts 0 successes
>>
>> ------ Semaphore Status --------
>> used arrays = 7
>> allocated semaphores = 119
>>
>> ------ Messages: Status --------
>> allocated queues = 0
>> used headers = 0
>> used space = 0 bytes
>>
>>
>> Did you perhaps disable spinlocks when compiling PG?
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
@ 2005-06-07 20:19 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Joshua D. Drake <jd@commandprompt.com>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
0 siblings, 3 replies; 25+ messages in thread
From: Donald Courtney @ 2005-06-07 20:19 UTC (permalink / raw)
To: Tom Arthurs <tarthurs@jobflash.com>; +Cc: pgsql-performance
Tom Arthurs wrote:
> According to my research, you only need a 64 bit image if you are
> going to be doing intensive floating point operations (which most db
> servers don't do). Some benchmarking results I've found on the
> internet indicate that 64 bit executables can be slower than 32 bit
> versions. I've been running 32 bit compiles on solaris for several years.
>
> How much memory do you have on that sparc box? Allocating more than
> about 7-12% to shared buffers has proven counter productive for us (it
> slows down).
>
The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large
caches -
Am I missing something key with postgreSQL?
Yes - we have seen with oracle 64 bit that there can be as much as a 10%
hit moving
from 32 - but we make it up big time with large db-buffer sizes that
drastically
reduce I/O and allow for other things (like more connections). Maybe
the expectation of less I/O is not correct?
Don
P.S. built with the Snapshot from two weeks ago.
> Kernel buffers are another animal. :)
>
> Donald Courtney wrote:
>
>> Get FATAL when starting up (64 bit) with large shared_buffers setting
>>
>> I built a 64 bit for Sparc/Solaris easily but I found that the
>> startup of postmaster generates a FATAL diagnostic due to going
>> over the 2GB limit (3.7 GB).
>>
>> When building for 64 bit is there some other
>> things that must change in order to size UP the shared_buffers?
>>
>> Thanks.
>>
>> Don C.
>>
>> P.S. A severe checkpoint problem I was having was fixed with
>> "checkpoint_segments=200".
>>
>>
>> Message:
>>
>> FATAL: 460000 is outside the valid range for parameter
>> "shared_buffers" (16 .. 262143)
>> LOG: database system was shut down at 2005-06-07 15:20:28 EDT
>>
>> Mike Rylander wrote:
>>
>>> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com>
>>> wrote:
>>>
>>>
>>>> I'm not sure if this is the appropriate list to post this question to
>>>> but i'm starting with this one because it is related to the
>>>> performance
>>>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>>>> opteron machine with 8 Gigs of memory. In my attempt to increase the
>>>> number of shared_buffers from the default to 65000 i was running
>>>> into a
>>>> semget error when trying to start Postgresql. After reading the
>>>> documentation I adjusted the semaphore settings in the kernel to allow
>>>> Postgresql to start successfully. With this configuration running
>>>> if I
>>>> do a ipcs -u i get the following.
>>>>
>>>
>>>
>>>
>>>
>>> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9):
>>>
>>> $ ipcs -u i
>>>
>>> ------ Shared Memory Status --------
>>> segments allocated 1
>>> pages allocated 34866
>>> pages resident 31642
>>> pages swapped 128
>>> Swap performance: 0 attempts 0 successes
>>>
>>> ------ Semaphore Status --------
>>> used arrays = 7
>>> allocated semaphores = 119
>>>
>>> ------ Messages: Status --------
>>> allocated queues = 0
>>> used headers = 0
>>> used space = 0 bytes
>>>
>>>
>>> Did you perhaps disable spinlocks when compiling PG?
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
@ 2005-06-07 20:27 ` Joshua D. Drake <jd@commandprompt.com>
2005-06-08 21:14 ` Re: Postgresql on an AMD64 machine Sam Vilain <sam@vilain.net>
2 siblings, 1 reply; 25+ messages in thread
From: Joshua D. Drake @ 2005-06-07 20:27 UTC (permalink / raw)
To: Donald Courtney <Donald.Courtney@Sun.COM>; +Cc: Tom Arthurs <tarthurs@jobflash.com>; pgsql-performance
>>
> 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/
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Joshua D. Drake <jd@commandprompt.com>
@ 2005-06-08 21:14 ` Sam Vilain <sam@vilain.net>
0 siblings, 0 replies; 25+ messages in thread
From: Sam Vilain @ 2005-06-08 21:14 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
@ 2005-06-07 20:27 ` Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 25+ messages in thread
From: Alvaro Herrera @ 2005-06-07 20:27 UTC (permalink / raw)
To: Donald Courtney <Donald.Courtney@Sun.COM>; +Cc: Tom Arthurs <tarthurs@jobflash.com>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
@ 2005-06-07 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 03:05 ` Re: Postgresql on an AMD64 machine Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-06-07 21:11 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@surnet.cl>; +Cc: Donald Courtney <Donald.Courtney@Sun.COM>; Tom Arthurs <tarthurs@jobflash.com>; pgsql-performance
Alvaro Herrera <alvherre@surnet.cl> writes:
> This was the standard wisdom with releases previous to 8.0; I'm not sure
> if anyone confirmed to still hold after the buffer manager changes in
> 8.0 and later in 8.1 -- we saw extensive redesign of the bufmgr on both,
> so the behavior may have changed. If you wanna test, I'm sure lots of
> people here will be interested in the results.
Quite. The story at the moment is that we haven't bothered to create
support for shared memory exceeding 2Gb, because there's never been any
evidence that pushing shared_buffers up even close to that, much less
above it, was a good idea. Most people have found the "sweet spot" to
be in the range of 10K to 50K shared buffers, with performance dropping
off above that.
Obviously we'd be willing to do this work if there were convincing
evidence it'd be worth the time. A benchmark showing performance
continuing to climb with increasing shared_buffers right up to the 2Gb
limit would be reasonably convincing. I think there is 0 chance of
drawing such a graph with a pre-8.1 server, because of internal
inefficiencies in the buffer manager ... but with CVS tip the story
might be different.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-08 03:05 ` Josh Berkus <josh@agliodbs.com>
2005-06-08 03:50 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Josh Berkus @ 2005-06-08 03:05 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 03:05 ` Re: Postgresql on an AMD64 machine Josh Berkus <josh@agliodbs.com>
@ 2005-06-08 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 06:16 ` Re: Postgresql on an AMD64 machine Simon Riggs <simon@2ndquadrant.com>
2005-06-08 09:51 ` Re: Postgresql on an AMD64 machine Michael Stone <mstone+postgres@mathom.us>
0 siblings, 2 replies; 25+ messages in thread
From: Tom Lane @ 2005-06-08 03:50 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 03:05 ` Re: Postgresql on an AMD64 machine Josh Berkus <josh@agliodbs.com>
2005-06-08 03:50 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-08 06:16 ` Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 25+ messages in thread
From: Simon Riggs @ 2005-06-08 06:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Re: Postgresql on an AMD64 machine Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 03:05 ` Re: Postgresql on an AMD64 machine Josh Berkus <josh@agliodbs.com>
2005-06-08 03:50 ` Re: Postgresql on an AMD64 machine Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-08 09:51 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 25+ messages in thread
From: Michael Stone @ 2005-06-08 09:51 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
@ 2005-06-07 20:39 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 21:04 ` Re: Postgresql on an AMD64 machine Michael Stone <mstone+postgres@mathom.us>
2005-06-08 00:20 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
2 siblings, 2 replies; 25+ messages in thread
From: Tom Arthurs @ 2005-06-07 20:39 UTC (permalink / raw)
To: Donald Courtney <Donald.Courtney@Sun.COM>; +Cc: pgsql-performance
Yes, shared buffers in postgres are not used for caching -- unlike
Oracle. Every time we hire an Oracle dba, I have to break them of the
notion (which I shared when I started with postgres -- Josh Berkus and
Josh Drake helped burst that bubble for me) :)
You should gain i/o reduction through increasing kernel buffers --
Postgresql counts on read/write caching through that, so increasing that
should get your performance improvemnets -- though I haven't found the
sweet spot there yet, for solaris. My biggest challenge with
solaris/sparc is trying to reduce context switching.
Donald Courtney wrote:
> Tom Arthurs wrote:
>
>> According to my research, you only need a 64 bit image if you are
>> going to be doing intensive floating point operations (which most db
>> servers don't do). Some benchmarking results I've found on the
>> internet indicate that 64 bit executables can be slower than 32 bit
>> versions. I've been running 32 bit compiles on solaris for several years.
>>
>> How much memory do you have on that sparc box? Allocating more than
>> about 7-12% to shared buffers has proven counter productive for us (it
>> slows down).
>>
> The system has 8 CPUs w/ 32 GB - I'm hoping to see some benefit to large
> caches -
> Am I missing something key with postgreSQL?
> Yes - we have seen with oracle 64 bit that there can be as much as a 10%
> hit moving
> from 32 - but we make it up big time with large db-buffer sizes that
> drastically
> reduce I/O and allow for other things (like more connections). Maybe
> the expectation of less I/O is not correct?
>
> Don
>
> P.S. built with the Snapshot from two weeks ago.
>
>> Kernel buffers are another animal. :)
>>
>> Donald Courtney wrote:
>>
>>> Get FATAL when starting up (64 bit) with large shared_buffers setting
>>>
>>> I built a 64 bit for Sparc/Solaris easily but I found that the
>>> startup of postmaster generates a FATAL diagnostic due to going
>>> over the 2GB limit (3.7 GB).
>>>
>>> When building for 64 bit is there some other
>>> things that must change in order to size UP the shared_buffers?
>>>
>>> Thanks.
>>>
>>> Don C.
>>>
>>> P.S. A severe checkpoint problem I was having was fixed with
>>> "checkpoint_segments=200".
>>>
>>>
>>> Message:
>>>
>>> FATAL: 460000 is outside the valid range for parameter
>>> "shared_buffers" (16 .. 262143)
>>> LOG: database system was shut down at 2005-06-07 15:20:28 EDT
>>>
>>> Mike Rylander wrote:
>>>
>>>> On 06 Jun 2005 12:53:40 -0500, Mark Rinaudo <mark@bowmansystems.com>
>>>> wrote:
>>>>
>>>>
>>>>> I'm not sure if this is the appropriate list to post this question to
>>>>> but i'm starting with this one because it is related to the
>>>>> performance
>>>>> of Postgresql server. I have a Penguin Computing dual AMD 64 bit
>>>>> opteron machine with 8 Gigs of memory. In my attempt to increase the
>>>>> number of shared_buffers from the default to 65000 i was running
>>>>> into a
>>>>> semget error when trying to start Postgresql. After reading the
>>>>> documentation I adjusted the semaphore settings in the kernel to allow
>>>>> Postgresql to start successfully. With this configuration running
>>>>> if I
>>>>> do a ipcs -u i get the following.
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On my HP-585, 4xOpteron, 16G RAM, Gentoo Linux (2.6.9):
>>>>
>>>> $ ipcs -u i
>>>>
>>>> ------ Shared Memory Status --------
>>>> segments allocated 1
>>>> pages allocated 34866
>>>> pages resident 31642
>>>> pages swapped 128
>>>> Swap performance: 0 attempts 0 successes
>>>>
>>>> ------ Semaphore Status --------
>>>> used arrays = 7
>>>> allocated semaphores = 119
>>>>
>>>> ------ Messages: Status --------
>>>> allocated queues = 0
>>>> used headers = 0
>>>> used space = 0 bytes
>>>>
>>>>
>>>> Did you perhaps disable spinlocks when compiling PG?
>>>>
>>>>
>>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/docs/faq
>>>
>>>
>>>
>
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
@ 2005-06-07 21:04 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 25+ messages in thread
From: Michael Stone @ 2005-06-07 21:04 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
@ 2005-06-08 00:20 ` Neil Conway <neilc@samurai.com>
2005-06-08 00:44 ` Re: Postgresql on an AMD64 machine John A Meinel <john@arbash-meinel.com>
2005-06-08 16:43 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
1 sibling, 2 replies; 25+ messages in thread
From: Neil Conway @ 2005-06-08 00:20 UTC (permalink / raw)
To: Tom Arthurs <tarthurs@jobflash.com>; +Cc: Donald Courtney <Donald.Courtney@Sun.COM>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 00:20 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
@ 2005-06-08 00:44 ` John A Meinel <john@arbash-meinel.com>
1 sibling, 0 replies; 25+ messages in thread
From: John A Meinel @ 2005-06-08 00:44 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Tom Arthurs <tarthurs@jobflash.com>; Donald Courtney <Donald.Courtney@Sun.COM>; pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 250B) download
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 00:20 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
@ 2005-06-08 16:43 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 16:53 ` Re: Postgresql on an AMD64 machine Bjoern Metzdorf <bm@turtle-entertainment.de>
2005-06-09 00:19 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
1 sibling, 2 replies; 25+ messages in thread
From: Tom Arthurs @ 2005-06-08 16:43 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Donald Courtney <Donald.Courtney@Sun.COM>; pgsql-performance
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
>
>
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 00:20 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
2005-06-08 16:43 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
@ 2005-06-08 16:53 ` Bjoern Metzdorf <bm@turtle-entertainment.de>
1 sibling, 0 replies; 25+ messages in thread
From: Bjoern Metzdorf @ 2005-06-08 16:53 UTC (permalink / raw)
To: Tom Arthurs <tarthurs@jobflash.com>; +Cc: Neil Conway <neilc@samurai.com>; Donald Courtney <Donald.Courtney@Sun.COM>; pgsql-performance
Hi,
> I just puhsd 8.0.3 to production on Sunday, and haven't had a time to
> really monitor it under load, so I can't tell if it's helped the context
> switch problem yet or not.
Attached is a "vmstat 5" output from one of our machines. This is a dual
Xeon 3,2 Ghz with EM64T and 8 GB RAM, running postgresql 8.0.3 on Debian
Sarge 64bit. Connection count is about 350.
Largest amount of cs per second is nearly 10000 which is high, yes, but
not too high.
Regards,
Bjoern
Attachments:
vmstat.txt (text/plain, 2.3K) download
# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 0 332004 52 6508160 0 0 28 55 5 9 5 1 94 0
0 0 0 335268 52 6509452 0 0 162 2195 2210 13633 34 7 57 2
2 0 0 303996 52 6510200 0 0 89 151 1909 26634 39 8 52 1
3 0 0 305772 52 6510676 0 0 41 40 1934 45525 54 12 34 0
4 0 0 283700 52 6511900 0 0 122 115 2175 36937 59 13 28 0
2 0 0 283132 52 6512444 0 0 88 137 1965 41128 57 12 31 0
1 0 0 277940 52 6513056 0 0 24 88 1899 47906 47 10 43 0
2 0 0 282404 52 6513668 0 0 54 51 1901 37858 47 9 44 0
3 0 0 283996 52 6514212 0 0 59 1675 2028 33609 49 10 40 1
4 0 0 282372 52 6514892 0 0 86 81 2046 31513 57 9 33 1
3 0 0 279228 52 6515300 0 0 18 88 1876 14465 41 5 54 0
3 0 0 288156 52 6516048 0 0 130 632 1944 25456 45 7 47 1
3 0 0 284884 52 6516592 0 0 66 60 1907 27620 56 8 35 0
3 0 0 279356 52 6516932 0 0 38 97 1950 45386 57 10 33 0
3 0 0 294764 52 6517476 0 0 45 52 1823 27900 40 7 53 0
4 0 0 295348 52 6518020 0 0 78 1352 1938 6048 16 4 79 1
2 0 0 282260 52 6518496 0 0 45 100 1954 14304 47 10 42 0
1 0 0 282708 52 6520196 0 0 288 62 2007 8705 29 6 64 1
4 0 0 292868 52 6520468 0 0 29 983 1829 6634 28 4 68 0
0 1 0 284380 52 6521148 0 0 114 163 2035 7017 23 4 72 1
1 0 0 281572 52 6522032 0 0 102 180 1861 7577 19 4 76 1
2 0 0 286668 52 6522440 0 0 75 150 1870 11185 30 5 65 1
0 0 0 293964 52 6523188 0 0 58 1533 2122 8174 23 5 71 1
1 0 0 287940 52 6523732 0 0 93 127 2001 11732 28 5 66 1
1 0 0 283428 52 6523936 0 0 40 89 1941 6360 20 4 75 0
1 0 0 280492 52 6524752 0 0 110 78 1912 5732 19 3 76 1
0 0 0 275684 52 6525160 0 0 67 55 2025 15541 25 6 69 1
* Re: Postgresql on an AMD64 machine
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 20:59 ` Re: Postgresql on an AMD64 machine Mike Rylander <mrylander@gmail.com>
2005-06-07 19:38 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Re: Postgresql on an AMD64 machine Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:39 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 00:20 ` Re: Postgresql on an AMD64 machine Neil Conway <neilc@samurai.com>
2005-06-08 16:43 ` Re: Postgresql on an AMD64 machine Tom Arthurs <tarthurs@jobflash.com>
@ 2005-06-09 00:19 ` Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 25+ messages in thread
From: Neil Conway @ 2005-06-09 00:19 UTC (permalink / raw)
To: Tom Arthurs <tarthurs@jobflash.com>; +Cc: Donald Courtney <Donald.Courtney@Sun.COM>; pgsql-performance
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
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2005-06-09 00:19 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-06 17:53 Postgresql on an AMD64 machine Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 18:32 ` Vivek Khera <vivek@khera.org>
2005-06-06 20:59 ` Mike Rylander <mrylander@gmail.com>
2005-06-06 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 22:28 ` Mark Rinaudo <mark@bowmansystems.com>
2005-06-06 23:48 ` Neil Conway <neilc@samurai.com>
2005-06-07 04:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-07 19:38 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 19:54 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 20:19 ` Donald Courtney <Donald.Courtney@Sun.COM>
2005-06-07 20:27 ` Joshua D. Drake <jd@commandprompt.com>
2005-06-08 21:14 ` Sam Vilain <sam@vilain.net>
2005-06-07 20:27 ` Alvaro Herrera <alvherre@surnet.cl>
2005-06-07 21:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 03:05 ` Josh Berkus <josh@agliodbs.com>
2005-06-08 03:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 06:16 ` Simon Riggs <simon@2ndquadrant.com>
2005-06-08 09:51 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-07 20:39 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-07 21:04 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-08 00:20 ` Neil Conway <neilc@samurai.com>
2005-06-08 00:44 ` John A Meinel <john@arbash-meinel.com>
2005-06-08 16:43 ` Tom Arthurs <tarthurs@jobflash.com>
2005-06-08 16:53 ` Bjoern Metzdorf <bm@turtle-entertainment.de>
2005-06-09 00:19 ` Neil Conway <neilc@samurai.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