Thread: 64-bit vs 32-bit performance ... backwards?

Hi all!

I had an interesting discussion today w/ an Enterprise DB developer and
sales person, and was told, twice, that the 64-bit linux version of
Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
32-bit version?

I was told that the added 32-bits puts a "strain" and extra "overhead"
on the processor / etc.... which actually slows down the pointers and
necessary back-end "stuff" on the database.

I'm curious if anyone can back this up .... or debunk it.  It's about
the polar opposite of everything I've heard from every other database
vendor for the past several years, and would be quite an eye-opener for
me.

Anyone?

Thanks.

--
Anthony

Anthony,

> I'm curious if anyone can back this up .... or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

I generally see a 20% "free" gain in performance on 64-bit (Opteron,
actually).  Possibly EDB is still using ICC to compile, and ICC is bad at
64-bit?

I have seen some applications which failed to gain any performance from
64-bit, but have never personally dealt with one which was slower.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Anthony Presley wrote:

>I had an interesting discussion today w/ an Enterprise DB developer and
>sales person, and was told, twice, that the 64-bit linux version of
>Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
>is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
>are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
>32-bit version?
>
>I was told that the added 32-bits puts a "strain" and extra "overhead"
>on the processor / etc.... which actually slows down the pointers and
>necessary back-end "stuff" on the database.
>
>I'm curious if anyone can back this up .... or debunk it.  It's about
>the polar opposite of everything I've heard from every other database
>vendor for the past several years, and would be quite an eye-opener for
>me.
>
>
What they are saying is strictly true : 64-bit pointers tend to increase
the working set size
of an application vs. 32-bit pointers. This means that any caches will
have somewhat lower
hit ratio. Also the bytes/s between the CPU and memory will be higher
due to moving those larger pointers.
In the case of a 32-bit OS this also applies to the kernel so the effect
will be system-wide.

However, an application that needs to work on > around 2G of data will
in the end be
much faster 64-bit due to reduced I/O (it can keep more of the data in
memory).

I worked on porting a large database application from 32-bit to 64-bit. One
of our customers required us to retain the 32-bit version because of
this phenomenon.

In measurements I conducted on that application, the performance
difference wasn't
great (10% or so), but it was measurable. This was with Sun Sparc hardware.
It is possible that more modern CPU designs have more efficient 64-bit
implementation than 32-bit, so the opposite might be seen too.

Whether or not PG would show the same thing I can't say for sure.
Probably it would though.

On Jun 12, 2006, at 3:28 PM, Anthony Presley wrote:

> Hi all!
>
> I had an interesting discussion today w/ an Enterprise DB developer
> and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of
> EDB
> are PostgreSQL ..... has anyone seen that the 64-bit is slower than
> the
> 32-bit version?
>
> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc.... which actually slows down the pointers and
> necessary back-end "stuff" on the database.
>
> I'm curious if anyone can back this up .... or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener
> for
> me.
>
> Anyone?

It's unsurprising for code written with 64 bit pointers ("64 bit
code") to be a little
slower than 32 bit code. The code and data structures are bigger,
more has to
be copied from main memory, fewer cache hits, all those bad things.

On CPUs with a uniform instructions set in both 32 and 64 bit modes
you're
only likely to see improved performance in 64 bit mode if your code can
take advantage of the larger address space (postgresql doesn't).

Some x86-esque architectures provide a somewhat different instruction
set in their 64 bit mode, with more programmer visible registers. The
increase in performance they can offer (with the right compiler) can
offset
the reduction due to pointer bloat, in some cases.

Empirically... postgresql built for 64 bits is marginally slower than
that built
for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested on
any
of those architectures.

Cheers,
   Steve

Anthony Presley <anthony@resolution.com> writes:
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
> 32-bit version?

That is a content-free statement, since they didn't mention what
architectures they are comparing, what compilers (and compiler options)
they are using, or what test cases they are measuring on.

Theoretically speaking, 64-bit *should* be slower than 32-bit (because
more data to transfer between memory and CPU to accomplish the same
work), except when considering workloads that can profit from having
direct access to more than 4Gb of memory.  However the theoretical
advantage is probably completely swamped by implementation details,
ie, how tensely did the designers of your 64-bit chip optimize its
32-bit behavior.

I believe that Red Hat generally recommends using 32-bit mode for
small-memory applications on PPC machines, because PPC32 is indeed
measurably faster than PPC64, but finds no such advantage on x86_64,
ia64 or s390x.  I don't know what applications they tested to come
to that conclusion, though.

            regards, tom lane

* Anthony Presley (anthony@resolution.com) wrote:
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
> 32-bit version?

Alot of it depends on what you're doing in the database, exactly, and
just which 32/64-bit platform is under discussion..  They're not all the
same (not even just amoung the ones Linux runs on :).

> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc.... which actually slows down the pointers and
> necessary back-end "stuff" on the database.

That's so hand-wavy that I'd be disinclined to believe the speaker, so
I'll assume you're (poorly) paraphrasing...  It's true that running
64bit means that you've got 64bit pointers, which are physically
larger than 32bit pointers.  Larger pointers means more effort to keep
track of them, copy them around, etc.  This is mitigated on some
platforms (ie: amd64) where there are extra registers available in
'64bit' mode (which is really more than just a 64bit mode of a 32bit
platform, unlike a platform like PPC or sparc).

> I'm curious if anyone can back this up .... or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

PostgreSQL doesn't generally operate on >2G resident memory.  I'm not
sure if it's possible for it to (I havn't really tried to find out,
though I have systems where it'd be possible to want to sort a >2G table
or similar, I don't have the work_mem set high enough for it to try, I
don't think).  This is because Postgres lets the OS handle most of the
cacheing, so as long as your OS can see all the memory you have in the
box, that benefit of running 64bit isn't going to be seen on Postgres.
On many other database systems (notably the 800-pound gorillas...) the
database handle the cacheing and so wants to basically have control over
all the memory in the box, which means running 64bit if you have more
than 2G in your system.

Just my 2c.

    Enjoy,

        Stephen

Anthony Presley <anthony@resolution.com> wrote:

> Hi all!
>
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
> 32-bit version?
>
> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc.... which actually slows down the pointers and
> necessary back-end "stuff" on the database.
>
> I'm curious if anyone can back this up .... or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

We did some tests on with identical hardware in both EMT64 and ia32 mode.
(Dell 2850, if you're curious)  This was PostgreSQL 8.1 running on
FreeBSD 6.

We found 64 bit to be ~5% slower than 32 bit mode in the (very) limited
tests that we did.  We pulled the plug before doing any extensive
testing, because it just didn't seem as if it was going to be worth it.

--
Bill Moran

I already know the ending it's the part that makes your face implode.
I don't know what makes your face implode, but that's the way the movie ends.

    TMBG

> Empirically... postgresql built for 64 bits is marginally slower than
> that built
> for a 32 bit api on sparc. None of my customers have found 64 bit x86
> systems to be suitable for production use, yet, so I've not tested on any
> of those architectures.

Really? All of our customers are migrating to Opteron and I have many
that have been using Opteron for over 12 months happily.

Joshua D. Drake

>
> Cheers,
>   Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/

On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:

>
>> Empirically... postgresql built for 64 bits is marginally slower
>> than that built
>> for a 32 bit api on sparc. None of my customers have found 64 bit x86
>> systems to be suitable for production use, yet, so I've not tested
>> on any
>> of those architectures.
>
> Really? All of our customers are migrating to Opteron and I have
> many that have been using Opteron for over 12 months happily.

An Opteron is 64 bit capable; that doesn't mean you have to run 64 bit
code on it.

Mine're mostly reasonably conservative users, with hundreds of machines
to support. Using 64 bit capable hardware, such as Opterons, is one
thing,
but using an entirely different linux installation and userspace
code, say, is
a much bigger change in support terms. In the extreme case it makes no
sense to double your OS support overheads to get a single digit
percentage
performance improvement on one database system.

That's not to say that linux/x86-64 isn't production ready for some
users, just
that it's not necessarily a good operational decision for my
customers. Given
my internal workloads aren't really stressing the hardware they're on
I don't
have much incentive to benchmark x86-64 yet - by the time the numbers
might be useful to me we'll be on a different postgresql, likely a
different
gcc/icc and so on.

Cheers,
   Steve

I've been trying to track this stuff - in fact, I'll likely be
switching from AMD32 to AMD64 in the next few weeks.

I believe I have a handle on the + vs - of 64-bit. It makes sense that
full 64-bit would be slower. At an extreme it halfs the amount of
available memory or doubles the required memory bandwidth, depending
on the work load.

Has anybody taken a look at PostgreSQL to ensure that it uses 32-bit
integers instead of 64-bit integers where only 32-bit is necessary?
32-bit offsets instead of 64-bit pointers? This sort of thing?

I haven't. I'm meaning to take a look. Within registers, 64-bit should
be equal speed to 32-bit. Outside the registers, it would make sense
to only deal with the lower 32-bits where 32-bits is all that is
required.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/

anthony@resolution.com (Anthony Presley) wrote:
> Hi all!
>
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL ..... has anyone seen that the 64-bit is slower than the
> 32-bit version?
>
> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc.... which actually slows down the pointers and
> necessary back-end "stuff" on the database.
>
> I'm curious if anyone can back this up .... or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.
>
> Anyone?

Traditionally, there has been *some* truth to such assertions.

Consider:

1.  64 bit versions of things need to manipulate 64 bit address values
and such; this will bloat up the code somewhat as compared to 32 bit
versions that will be somewhat more compact.

2.  If you only have 2GB of memory, you get no particular advantage
out of 64 bittedness.

In the days when people had 64 bit Alphas with 256MB of memory, there
was considerable debate about the actual merits of running in 64 bit
mode, and the answers were unobvious.

On the other hand...

3.  Opterons tend to address memory quite a bit quicker than Intels of
pretty much any variety.

4.  64 bit CPUs offer additional registers that can be expected to
make register-bound code quicker.

5.  If you have >>2GB of memory, a 64 bit system is needful to harness
that, and that will make a *big* difference to performance.

The overall claim is somewhat content-free, in the absence of
information about the architecture of the database server.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/
"A program invented (sic) by a Finnish computer hacker and handed out free
in 1991 cost investors in Microsoft $11 billion (#6.75 billion) this week."
-- Andrew Butcher in the UK's Sunday Times, Feb 20th, 1999

"Alex Turner" <armtuk@gmail.com> writes:
> Anyone who has tried x86-64 linux knows what a royal pain in the ass it
> is.   They didn't do anything sensible, like just make the whole OS 64 bit,
> no, they had to split it up, and put 64-bit libs in a new directory /lib64.

Actually, there's nothing wrong with that.  As this thread already made
clear, there are good reasons why you might want to run 32-bit apps as
well as 64-bit apps on your 64-bit hardware.  So the 32-bit libraries
live in /usr/lib and the 64-bit ones in /usr/lib64.  If you ask me, the
really serious mistake in this design is they didn't decree separate bin
directories /usr/bin and /usr/bin64 too.  This makes it impossible to
install 32-bit and 64-bit versions of the same package at the same time,
something that curiously enough people are now demanding support for.

(Personally, if I'd designed it, the libraries would actually live in
/usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
you needed it to be at the moment.  Likewise for /usr/bin.)

            regards, tom lane

Martha Stewart called it a Good Thing when armtuk@gmail.com ("Alex Turner") wrote:
> Anyone who has tried x86-64 linux knows what a royal pain in the ass
> it is.   They didn't do anything sensible, like just make the whole
> OS 64 bit, no, they had to split it up, and put 64-bit libs in a new
> directory /lib64.  This means that a great many applications don't
> know to check in there for libs, and don't compile pleasantly, php
> is one among them.  I forget what others, it's been awhile now.  Of
> course if you actualy want to use more than 4gig RAM in a pleasant
> way, it's pretty much essential.  Alex.

That's absolute nonsense.

I have been running the Debian AMD64 port since I can't recall when.
I have experienced NO such issues.

Packages simply install, in most cases.

When I do need to compile things, they *do* compile pleasantly.

I seem to recall hearing there being "significant issues" as to how
Red Hat's distributions of Linux coped with AMD64.  That's not a
problem with Linux, of course...
--
"cbbrowne","@","gmail.com"
http://linuxdatabases.info/info/spreadsheets.html
"Imagine a law so stupid that civil obedience becomes an efficient way
to fighting it" --Per Abrahamsen on the DMCA

Mark,

On 6/12/06 7:16 PM, "mark@mark.mielke.cc" <mark@mark.mielke.cc> wrote:

> I haven't. I'm meaning to take a look. Within registers, 64-bit should
> be equal speed to 32-bit. Outside the registers, it would make sense
> to only deal with the lower 32-bits where 32-bits is all that is
> required.

The short answer to all of this as shown in many lab tests by us and
elsewhere (see prior post):

- 64-bit pgsql on Opteron is generally faster than 32-bit, often by a large
amount (20%-30%) on queries that perform sorting, aggregation, etc.  It's
generally not slower.

- 64-bit pgsql on Xeon is generally slower than 32-bit by about 5%

So if you have Opterons and you want the best performance, run in 64-bit.
If you have Xeons, you would only run in 64-bit if you use more than 4GB of
memory.

- Luke

On Jun 12, 2006, at 19:44, Tom Lane wrote:

> (Personally, if I'd designed it, the libraries would actually live in
> /usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to
> whichever
> you needed it to be at the moment.  Likewise for /usr/bin.)

/me nominates Tom to create a Linux distribution.

:-)

David

Alex Turner wrote:
> Anyone who has tried x86-64 linux knows what a royal pain in the ass it
> is.   They didn't do anything sensible, like just make the whole OS 64
> bit, no, they had to split it up, and put 64-bit libs in a new directory
> /lib64.  This means that a great many applications don't know to check
> in there for libs, and don't compile pleasantly, php is one among them.
> I forget what others, it's been awhile now.  Of course if you actualy
> want to use more than 4gig RAM in a pleasant way, it's pretty much
> essential.
>
That depends entirely on what AMD64 distribution you use -- on a Debian
or Ubuntu 64-bit system, the main system is pre 64-bit, with some
(optional) add-on libraries in separate directories to provide some
32-bit compatibility.

On the performance stuff, my own testing of AMD64 on AMD's chips (not
with PostgreSQL, but with various other things) has shown it to be about
10% faster on average. As Luke mentioned, this isn't because of any
inherent advantage in 64-bit -- it's because AMD did some tweaking while
they had the hood open, adding extra registers among other things.

I remember reading an article some time back comparing AMD's
implementation to Intel's that showed that EM64T Xeons ran 64-bit code
about 5-10% more slowly than they ran 32-bit code. I can't find the link
now, but it may explain why some people are getting better performance
with 64-bit (on Opterons), while others are finding it slower (on Xeons).

Thanks
Leigh

> Alex.
>
> On 6/12/06, *Steve Atkins* <steve@blighty.com
> <mailto:steve@blighty.com>> wrote:
>
>
>     On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:
>
>      >
>      >> Empirically... postgresql built for 64 bits is marginally slower
>      >> than that built
>      >> for a 32 bit api on sparc. None of my customers have found 64
>     bit x86
>      >> systems to be suitable for production use, yet, so I've not tested
>      >> on any
>      >> of those architectures.
>      >
>      > Really? All of our customers are migrating to Opteron and I have
>      > many that have been using Opteron for over 12 months happily.
>
>     An Opteron is 64 bit capable; that doesn't mean you have to run 64 bit
>     code on it.
>
>     Mine're mostly reasonably conservative users, with hundreds of machines
>     to support. Using 64 bit capable hardware, such as Opterons, is one
>     thing,
>     but using an entirely different linux installation and userspace
>     code, say, is
>     a much bigger change in support terms. In the extreme case it makes no
>     sense to double your OS support overheads to get a single digit
>     percentage
>     performance improvement on one database system.
>
>     That's not to say that linux/x86-64 isn't production ready for some
>     users, just
>     that it's not necessarily a good operational decision for my
>     customers. Given
>     my internal workloads aren't really stressing the hardware they're on
>     I don't
>     have much incentive to benchmark x86-64 yet - by the time the numbers
>     might be useful to me we'll be on a different postgresql, likely a
>     different
>     gcc/icc and so on.
>
>     Cheers,
>        Steve
>
>
>     ---------------------------(end of broadcast)---------------------------
>     TIP 2: Don't 'kill -9' the postmaster
>
>

Folks,

FWIW, the applications where I did direct 32 / 64 comparison were
a) several data warehouse tests, with databases > 100GB
b) computation-heavy applications (such as a complex calendaring app)

And, as others have pointed out, I wasn't comparing generics; I was comparing
Athalon/Xeon to Opteron.  So it's quite possible that the improvements had
nothing to do with going 64-bit and were because of other architecture
improvements.

In which case, why was 64-bit such a big deal?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Mon, Jun 12, 2006 at 10:44:01PM -0400, Tom Lane wrote:
> (Personally, if I'd designed it, the libraries would actually live in
> /usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
> you needed it to be at the moment.  Likewise for /usr/bin.)

Actually, there have been plans for doing something like this in Debian for a
while: Let stuff live in /lib/i686-linux-gnu and /lib/x86_64-linux-gnu
(lib32 and lib64 doesn't really scale, once you start considering stuff like
"ia64 can emulate hppa"), and adjust paths and symlinks as fit. It's still a
long way to go, though.

/* Steinar */
--
Homepage: http://www.sesse.net/

On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:
>> Empirically... postgresql built for 64 bits is marginally slower
>> than that built
>> for a 32 bit api on sparc. None of my customers have found 64 bit x86
>> systems to be suitable for production use, yet, so I've not tested
>> on any
>> of those architectures.
>
> Really? All of our customers are migrating to Opteron and I have
> many that have been using Opteron for over 12 months happily.



We have been using PostgreSQL on Opteron servers almost since the
Opteron was first released, running both 32-bit and 64-bit versions
of Linux.  Both 32-bit and 64-bit versions have been bulletproof for
us, with the usual stability I've become accustomed to with both
PostgreSQL and Linux.  We have been running nothing but 64-bit
versions on mission-critical systems for the last year with zero
problems.

The short story is that for us 64-bit PostgreSQL on Opterons is
typically something like 20% faster than 32-bit on the same, and
*much* faster than P4 Xeon systems they nominally compete with.
AMD64 is a more efficient architecture than x86 in a number of ways,
and the Opteron has enviable memory latency and bandwidth that make
it an extremely efficient database workhorse.  x86->AMD64 is not a
word-width migration, it is a different architecture cleverly
designed to be efficiently compatible with x86.  In addition to
things like a more RISC-like register set, AMD64 uses a different
floating point architecture that is more efficient than the old x87.

In terms of bang for the buck in a bulletproof database server, it is
really hard to argue with 64-bit Opterons.  They are damn fast, and
in my experience problem free.  We run databases on other
architectures, but they are all getting replaced with 64-bit Linux on
Opterons because the AMD64 systems tend to be both faster and
cheaper.  Architectures like Sparc have never given us problems, but
they have not exactly thrilled us with their performance either.


Cheers,

J. Andrew Rogers

J. Andrew Rogers wrote:

> We have been using PostgreSQL on Opteron servers almost since the
> Opteron was first released, running both 32-bit and 64-bit versions of
> Linux.  Both 32-bit and 64-bit versions have been bulletproof for us,
> with the usual stability I've become accustomed to with both PostgreSQL
> and Linux.  We have been running nothing but 64-bit versions on
> mission-critical systems for the last year with zero problems.
>
> The short story is that for us 64-bit PostgreSQL on Opterons is
> typically something like 20% faster than 32-bit on the same, and *much*
> faster than P4 Xeon systems they nominally compete with.

Since you sound like you have done extensive testing:

Do you have any data regarding whether to enable hyperthreading or not?
I realize that this may be highly dependant on the OS, application and
number of CPUs, but I would be interested in hearing your
recommendations (or others').

/Nis

Installation of a 32-bit PostgreSQL on a 64-bit Linux like RHEL 4 is
very easy. Make sure that you have installed all needed 32-bit libs and
devel packages.

Here is an example to call configure to get a 32-bit PostgreSQL:

CXX="/usr/bin/g++ -m32" \
CPP="/usr/bin/gcc -m32 -E" \
LD="/usr/bin/ld -m elf_i386" \
AS="/usr/bin/gcc -m32 -Wa,--32 -D__ASSEMBLY__ -traditional" \
CC="/usr/bin/gcc -m32" \
CFLAGS="-O3 -funroll-loops -fno-strict-aliasing -pipe -mcpu=opteron
-march=opteron -mfpmath=sse -msse2" \
./configure --prefix=<pgsql-path>


J. Andrew Rogers schrieb:
> The short story is that for us 64-bit PostgreSQL on Opterons is
> typically something like 20% faster than 32-bit on the same, and *much*
> faster than P4 Xeon systems they nominally compete with.  AMD64 is a
> more efficient architecture than x86 in a number of ways, and the
> Opteron has enviable memory latency and bandwidth that make it an
> extremely efficient database workhorse.  x86->AMD64 is not a word-width
> migration, it is a different architecture cleverly designed to be
> efficiently compatible with x86.  In addition to things like a more
> RISC-like register set, AMD64 uses a different floating point
> architecture that is more efficient than the old x87.
>

I did a few test in the past with 64-bit PostgreSQL and 32-bit
PostgreSQL and the 32-bit version was always faster.
Please find attached a small patch with does apply a change to the
x86_64 part also to the i386 part of src/include/storage/s_lock.h.
Without this change the performance of PostgreSQL 8.0 was horrible on a
Opteron. The effect is smaller with PostgreSQL 8.1.

Cheers
Sven.




diff -Naur postgresql-8.1.4.orig/src/include/storage/s_lock.h postgresql-8.1.4/src/include/storage/s_lock.h
--- postgresql-8.1.4.orig/src/include/storage/s_lock.h    2005-10-11 22:41:32.000000000 +0200
+++ postgresql-8.1.4/src/include/storage/s_lock.h    2006-05-31 09:19:04.000000000 +0200
@@ -125,12 +125,9 @@
      * extra test appears to be a small loss on some x86 platforms and a small
      * win on others; it's by no means clear that we should keep it.
      */
+    /* xchg implies a LOCK prefix, so no need to say LOCK explicitly */
     __asm__ __volatile__(
-        "    cmpb    $0,%1    \n"
-        "    jne        1f        \n"
-        "    lock            \n"
         "    xchgb    %0,%1    \n"
-        "1: \n"
 :        "+q"(_res), "+m"(*lock)
 :
 :        "memory", "cc");
@@ -189,8 +186,8 @@
      * is a huge loss.  On EM64T, it appears to be a wash or small loss,
      * so we needn't bother to try to distinguish the sub-architectures.
      */
+    /* xchg implies a LOCK prefix, so no need to say LOCK explicitly */
     __asm__ __volatile__(
-        "    lock            \n"
         "    xchgb    %0,%1    \n"
 :        "+q"(_res), "+m"(*lock)
 :

On Tue, 13 Jun 2006 04:26:05 +0200, Alex Turner <armtuk@gmail.com> wrote:

> Anyone who has tried x86-64 linux knows what a royal pain in the ass it
> is.   They didn't do anything sensible, like just make the whole OS 64
> bit,
> no, they had to split it up, and put 64-bit libs in a new directory
> /lib64.
> This means that a great many applications don't know to check in there
> for
> libs, and don't compile pleasantly, php is one among them.  I forget what
> others, it's been awhile now.  Of course if you actualy want to use more
> than 4gig RAM in a pleasant way, it's pretty much essential.
>
> Alex.

    Decent distros do this for you :

$ ll /usr | grep lib
lrwxrwxrwx    1 root root    5 jan 20 09:55 lib -> lib64
drwxr-xr-x   10 root root 1,8K avr 19 16:16 lib32
drwxr-xr-x   92 root root  77K jun 10 15:48 lib64

    Also, on gentoo, everything "just works" in 64-bit mode and the packages
compile normally... I don't see a problem...

Sven,

On 6/13/06 2:04 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:

> Please find attached a small patch with does apply a change to the
> x86_64 part also to the i386 part of src/include/storage/s_lock.h.
> Without this change the performance of PostgreSQL 8.0 was horrible on a
> Opteron. The effect is smaller with PostgreSQL 8.1.

Can you describe what kinds of tests you ran to check your speed?

Since it's the TAS lock that you are patching, the potential impact is
diffuse and large: xlog.c, shmem.c, lwlock.c, proc.c, all do significant
work.

- Luke

Luke

Luke Lonergan schrieb:
> On 6/13/06 2:04 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:
>> Please find attached a small patch with does apply a change to the
>> x86_64 part also to the i386 part of src/include/storage/s_lock.h.
>> Without this change the performance of PostgreSQL 8.0 was horrible on a
>> Opteron. The effect is smaller with PostgreSQL 8.1.
>
> Can you describe what kinds of tests you ran to check your speed?

I has create a test scenario with parallel client which running mostly
SELECTs on the same tables. I used a sequence of 25 queries using 10
tables. We use the total throughput (queries per second) as result.

>
> Since it's the TAS lock that you are patching, the potential impact is
> diffuse and large: xlog.c, shmem.c, lwlock.c, proc.c, all do significant
> work.

Yes, I know. We had a problem last year with the performance of the
Opteron. We have started the futex patch to resolve the issue. The futex
patch itself did have no effect, but there was a side effect because the
futex patch did use also another assembler sequence. This make a hole
difference on a Opteron. It turned out that removing the lines

cmpb
jne
lock

was the reason why the Opteron runs faster.
I have created a sequence of larger query with following result on
Opteron 875 and PostgreSQL 8.0.3
orignal 8.0.3 => 289 query/time and 10% cpu usage
patched 8.0.3 => 1022 query/time and 45% cpu usage

I has a smaller effect on a XEON MP with EM64T. But this effect wasn't
that huge. There was no effect on classic XEON's.

Cheers
Sven.

Martha Stewart called it a Good Thing when nis@superlativ.dk (Nis Jorgensen) wrote:
> J. Andrew Rogers wrote:
>
>> We have been using PostgreSQL on Opteron servers almost since the
>> Opteron was first released, running both 32-bit and 64-bit versions of
>> Linux.  Both 32-bit and 64-bit versions have been bulletproof for us,
>> with the usual stability I've become accustomed to with both PostgreSQL
>> and Linux.  We have been running nothing but 64-bit versions on
>> mission-critical systems for the last year with zero problems.
>>
>> The short story is that for us 64-bit PostgreSQL on Opterons is
>> typically something like 20% faster than 32-bit on the same, and *much*
>> faster than P4 Xeon systems they nominally compete with.
>
> Since you sound like you have done extensive testing:
>
> Do you have any data regarding whether to enable hyperthreading or not?
> I realize that this may be highly dependant on the OS, application and
> number of CPUs, but I would be interested in hearing your
> recommendations (or others').

Um, Hyper-Threading?  On AMD?

Hyper-Threading is a feature only offered by Intel, on some Pentium 4
chips.

It is not offered by AMD.  For our purposes, this is no loss; database
benchmarks have widely shown it to be a performance loser across
various database systems.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://linuxdatabases.info/info/languages.html
Yes, for sparkling white chip prints, use low SUDSing DRAW....

Sven,

On 6/13/06 5:03 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:

> Yes, I know. We had a problem last year with the performance of the
> Opteron. We have started the futex patch to resolve the issue. The futex
> patch itself did have no effect, but there was a side effect because the
> futex patch did use also another assembler sequence. This make a hole
> difference on a Opteron. It turned out that removing the lines
>
> cmpb
> jne
> lock
>
> was the reason why the Opteron runs faster.
> I have created a sequence of larger query with following result on
> Opteron 875 and PostgreSQL 8.0.3
> orignal 8.0.3 => 289 query/time and 10% cpu usage
> patched 8.0.3 => 1022 query/time and 45% cpu usage

This was in 64-bit mode on the Opteron?

- Luke

Luke

Luke Lonergan schrieb:
> Sven,
> On 6/13/06 5:03 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:
>> Yes, I know. We had a problem last year with the performance of the
>> Opteron. We have started the futex patch to resolve the issue. The futex
>> patch itself did have no effect, but there was a side effect because the
>> futex patch did use also another assembler sequence. This make a hole
>> difference on a Opteron. It turned out that removing the lines
>>
>> cmpb
>> jne
>> lock
>>
>> was the reason why the Opteron runs faster.
>> I have created a sequence of larger query with following result on
>> Opteron 875 and PostgreSQL 8.0.3
>> orignal 8.0.3 => 289 query/time and 10% cpu usage
>> patched 8.0.3 => 1022 query/time and 45% cpu usage
>
> This was in 64-bit mode on the Opteron?
This was in 32-bit mode on the Opteron. But the effect was the same in
64-bit mode with PostgreSQL 8.0.3.

You already get this change if you compile PostgreSQL 8.1.x in x86_64
(64-bit mode).

Cheers
Sven.

Sven,

On 6/13/06 5:46 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:

> You already get this change if you compile PostgreSQL 8.1.x in x86_64
> (64-bit mode).

I see, so I think your point with the patch is to make the 32-bit compiled
version benefit as well.

- Luke

Luke,

Luke Lonergan schrieb:
> On 6/13/06 5:46 AM, "Sven Geisler" <sgeisler@aeccom.com> wrote:
>> You already get this change if you compile PostgreSQL 8.1.x in x86_64
>> (64-bit mode).
>
> I see, so I think your point with the patch is to make the 32-bit compiled
> version benefit as well.
>

Yup. I think you have to change this in the 32-bit compiled version too
if you want to compare 32-bit and 64-bit on a Opteron.

Sven.

Josh Berkus wrote:
> Folks,
>
>
> In which case, why was 64-bit such a big deal?
>

We had this discussion with 16/32 bit too, back in those 286/386 times...
Not too many 16bit apps left now :-)

Regards,
Andreas

On Jun 13, 2006, at 1:40 AM, Nis Jorgensen wrote:
> Since you sound like you have done extensive testing:
>
> Do you have any data regarding whether to enable hyperthreading or
> not?
> I realize that this may be highly dependant on the OS, application and
> number of CPUs, but I would be interested in hearing your
> recommendations (or others').


Hyperthreading never made much of a difference for our database
loads.  Since we've retired all non-dev P4 database servers, I am not
too worried about it.  We will probably re-test the new "Core 2" CPUs
that are coming out, since those differ significantly from the P4 in
capability.


J. Andrew Rogers

* Steinar H. Gunderson (sgunderson@bigfoot.com) wrote:
> On Mon, Jun 12, 2006 at 10:44:01PM -0400, Tom Lane wrote:
> > (Personally, if I'd designed it, the libraries would actually live in
> > /usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
> > you needed it to be at the moment.  Likewise for /usr/bin.)
>
> Actually, there have been plans for doing something like this in Debian for a
> while: Let stuff live in /lib/i686-linux-gnu and /lib/x86_64-linux-gnu
> (lib32 and lib64 doesn't really scale, once you start considering stuff like
> "ia64 can emulate hppa"), and adjust paths and symlinks as fit. It's still a
> long way to go, though.

The general feeling is that there won't be support for multiple versions
of a given binary being installed at once though.  The proposal Steinar
mentioned is called 'multiarch' and is being discussed with LSB and
other distros too, though I think it did mostly originated with Debian
folks.

Just my 2c.

    Thanks,

        Stephen

On Mon, Jun 12, 2006 at 05:19:46PM -0600, David Boreham wrote:
> What they are saying is strictly true : 64-bit pointers tend to increase
> the working set size
> of an application vs. 32-bit pointers. This means that any caches will
> have somewhat lower
> hit ratio. Also the bytes/s between the CPU and memory will be higher
> due to moving those larger pointers.

While bytes/s will go up what really matters is words/s, where a word is
the size of a memory transfer to the CPU. Taking a simplistic view, 8
bit CPUs move data into the CPU one byte at a time; 16 bit CPUs, 2
bytes; 32 bit, 4 bytes, and 64 bit, 8 bytes. The reality is a bit more
complicated, but I'm 99.9% certain that you won't see a modern 64 bit CPU
tranfering data in less than 64 bit increments.

> However, an application that needs to work on > around 2G of data will
> in the end be
> much faster 64-bit due to reduced I/O (it can keep more of the data in
> memory).

There's not an automatic correlation between word size and address
space, just look at the 8088, so this depends entirely on the CPU.

> I worked on porting a large database application from 32-bit to 64-bit. One
> of our customers required us to retain the 32-bit version because of
> this phenomenon.
>
> In measurements I conducted on that application, the performance
> difference wasn't
> great (10% or so), but it was measurable. This was with Sun Sparc hardware.
> It is possible that more modern CPU designs have more efficient 64-bit
> implementation than 32-bit, so the opposite might be seen too.
>
> Whether or not PG would show the same thing I can't say for sure.
> Probably it would though.

It's going to depend entirely on the CPU and the compiler. I can say
that in the 32 vs 64 bit benchmarking I've done using dbt2, I wasn't
able to find a difference at all on Sunfire Opteron machines.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, Jun 12, 2006 at 08:04:41PM -0400, Stephen Frost wrote:
> don't think).  This is because Postgres lets the OS handle most of the
> cacheing, so as long as your OS can see all the memory you have in the

Actually, in 8.1.x I've seen some big wins from greatly increasing the
amount of shared_buffers, even as high as 50% of memory, thanks to the
changes made to the buffer management code. I'd strongly advice users to
benchmark their applications with higher shared_buffers and see what
impact it has, especially if your application can't make use of really
big work_mem settings. If there's additional changes to the shared
buffer code in 8.2 (I know Tom's been looking at doing multiple buffer
pools to reduce contention on the BufMgr lock), it'd be worth
re-benchmarking when it comes out.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
...
> Actually, in 8.1.x I've seen some big wins from greatly increasing the
> amount of shared_buffers, even as high as 50% of memory, thanks to the
> changes made to the buffer management code. ...

Anyone else run into a gotcha that one of our customers ran into?
PG 7.4.8 running on Solaris 2.6, USparc w 4GB RAM.
Usually about 50 active backends.
(No reason to believe this wouldn't apply to 8.x).

Initially shared_buffers were set to 1000 (8MB).
Then, we moved all apps but the database server off the box.

Raised shared_buffers to 2000 (16MB).
Modest improvement in some frequent repeated queries.

Raised shared_buffers to 16000 (128MB).
DB server dropped to a CRAWL.

vmstat showed that it was swapping like crazy.
Dropped shared_buffers back down again.
Swapping stopped.

Stared at "ps u" a lot, and realized that the shm seg appeared to
be counted as part of the resident set (RSS).
Theory was that the kernel was reading the numbers the same way,
and swapping out resident sets, since they obviously wouldn't
all fit in RAM :-)

Anyone from Sun reading this list, willing to offer an opinion?

Mischa Sandberg <mischa@ca.sophos.com> writes:
> vmstat showed that it was swapping like crazy.
> Dropped shared_buffers back down again.
> Swapping stopped.

Does Solaris have any call that allows locking a shmem segment in RAM?

            regards, tom lane

On Tue, Jun 13, 2006 at 06:22:07PM -0400, Tom Lane wrote:
> Mischa Sandberg <mischa@ca.sophos.com> writes:
> > vmstat showed that it was swapping like crazy.
> > Dropped shared_buffers back down again.
> > Swapping stopped.
>
> Does Solaris have any call that allows locking a shmem segment in RAM?

The Solaris 9 shmctl manpage mentions this token:

  SHM_LOCK
        Lock the shared memory segment specified by shmid in
        memory. This command can be executed only by a process
        that has an effective user ID equal to super-user.

--
Michael Fuhr

On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> ...
> >Actually, in 8.1.x I've seen some big wins from greatly increasing the
> >amount of shared_buffers, even as high as 50% of memory, thanks to the
> >changes made to the buffer management code. ...
>
> Anyone else run into a gotcha that one of our customers ran into?
> PG 7.4.8 running on Solaris 2.6, USparc w 4GB RAM.
> Usually about 50 active backends.
> (No reason to believe this wouldn't apply to 8.x).
>
> Initially shared_buffers were set to 1000 (8MB).
> Then, we moved all apps but the database server off the box.
>
> Raised shared_buffers to 2000 (16MB).
> Modest improvement in some frequent repeated queries.
>
> Raised shared_buffers to 16000 (128MB).
> DB server dropped to a CRAWL.
>
> vmstat showed that it was swapping like crazy.
> Dropped shared_buffers back down again.
> Swapping stopped.

What's sort_mem set to? I suspect you simply ran the machine out of
memory.

Also, Solaris by default will only use a portion of memory for
filesystem caching, which will kill PostgreSQL performance.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

> Initially shared_buffers were set to 1000 (8MB).
> Then, we moved all apps but the database server off the box.
>
> Raised shared_buffers to 2000 (16MB).
> Modest improvement in some frequent repeated queries.
>
> Raised shared_buffers to 16000 (128MB).
> DB server dropped to a CRAWL.

Versions below 8.1 normally don't do well with high shared_buffers. 8.1
would do much better. If you dropped that to more like 6k you would
probably continue to see increase over 2k.

Sincerely,

Joshua D. Drake


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Tom Lane wrote:
> Mischa Sandberg <mischa@ca.sophos.com> writes:
>> vmstat showed that it was swapping like crazy.
>> Dropped shared_buffers back down again.
>> Swapping stopped.
>
> Does Solaris have any call that allows locking a shmem segment in RAM?

Yes, mlock(). But want to understand what's going on before patching.
No reason to believe that the multiply-attached shm seg was being swapped out
(which is frankly insane). Swapping out (and in) just the true resident set of
every backend would be enough to explain the vmstat io we saw.

http://www.carumba.com/talk/random/swol-09-insidesolaris.html

For a dedicated DB server machine, Solaris has a feature:
create "intimate" shared memory with shmat(..., SHM_SHARE_MMU).
All backends share the same TLB entries (!).

Context switch rates on our in-house solaris boxes running PG
have been insane (4000/sec). Reloading the TLB map on every process
context switch might be one reason Solaris runs our db apps at less
than half the speed of our perftesters' Xeon beige-boxes.

That's guesswork. Sun is making PG part of their distro ...
perhaps they've some knowledgeable input.

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Jim C. Nasby wrote:
> On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
>> Raised shared_buffers to 16000 (128MB).
>> DB server dropped to a CRAWL.
>>
>> vmstat showed that it was swapping like crazy.
>> Dropped shared_buffers back down again.
>> Swapping stopped.
>
> What's sort_mem set to? I suspect you simply ran the machine out of
> memory.

8192 (8MB). No issue when shared_buffers was 2000; same apps always.

> Also, Solaris by default will only use a portion of memory for
> filesystem caching, which will kill PostgreSQL performance.

Yep, tested /etc/system segmap_percent at 20,40,60.
No significant difference between 20 and 60.
Default is 10%? 12%? Can't recall.

Was not changed from 20 during the shared_buffer test.
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
> >>Raised shared_buffers to 16000 (128MB).
> >>DB server dropped to a CRAWL.
> >>
> >>vmstat showed that it was swapping like crazy.
> >>Dropped shared_buffers back down again.
> >>Swapping stopped.
> >
> >What's sort_mem set to? I suspect you simply ran the machine out of
> >memory.
>
> 8192 (8MB). No issue when shared_buffers was 2000; same apps always.

So if all 50 backends were running a sort, you'd use 400MB. The box has
4G, right?

> >Also, Solaris by default will only use a portion of memory for
> >filesystem caching, which will kill PostgreSQL performance.
>
> Yep, tested /etc/system segmap_percent at 20,40,60.
> No significant difference between 20 and 60.

That's pretty disturbing... how large is your database?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
> On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
>> Jim C. Nasby wrote:
>>> What's sort_mem set to? I suspect you simply ran the machine out of
>>> memory.
>> 8192 (8MB). No issue when shared_buffers was 2000; same apps always.
>
> So if all 50 backends were running a sort, you'd use 400MB. The box has
> 4G, right?

Umm ... yes. "if". 35-40 of them are doing pure INSERTS.
Not following your train.

>> Yep, tested /etc/system segmap_percent at 20,40,60.
>> No significant difference between 20 and 60.
> That's pretty disturbing... how large is your database?

~10GB. Good locality. Where heading?

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> ...
>> Actually, in 8.1.x I've seen some big wins from greatly increasing the
>> amount of shared_buffers, even as high as 50% of memory, thanks to the
>> changes made to the buffer management code. ...
>
> Anyone else run into a gotcha that one of our customers ran into?
> PG 7.4.8 running on Solaris 2.6, USparc w 4GB RAM.
> Usually about 50 active backends.
> (No reason to believe this wouldn't apply to 8.x).
>
> Initially shared_buffers were set to 1000 (8MB).
> Then, we moved all apps but the database server off the box.
>
> Raised shared_buffers to 2000 (16MB).
> Modest improvement in some frequent repeated queries.
>
> Raised shared_buffers to 16000 (128MB).
> DB server dropped to a CRAWL.
>
> vmstat showed that it was swapping like crazy.
> Dropped shared_buffers back down again. Swapping stopped.
>
> Stared at "ps u" a lot, and realized that the shm seg appeared to
> be counted as part of the resident set (RSS).
> Theory was that the kernel was reading the numbers the same way,
> and swapping out resident sets, since they obviously wouldn't
> all fit in RAM :-)
>
> Anyone from Sun reading this list, willing to offer an opinion?
>

A while ago I ran 7.4.? on a Solaris 2.8 box (E280 or E220 can't recall)
with 2G of ram - 40 users or so with shared_buffers = approx 12000 -
with no swapping I recall (in fact I pretty sure there was free memory!).

I suspect something else is your culprit - what is work_mem (or
sort_mem) set to? I'm thinking that you have this high and didn't have
much memory headroom to begin with, so that upping shared_buffers from
16MB -> 128MB tipped things over the edge!

Cheers

Mark

Mischa Sandberg <mischa@ca.sophos.com> writes:
> Tom Lane wrote:
>> Does Solaris have any call that allows locking a shmem segment in RAM?

> Yes, mlock(). But want to understand what's going on before patching.

Sure, but testing it with mlock() might help you understand what's going
on, by eliminating one variable: we don't really know if the shmem is
getting swapped, or something else.

> For a dedicated DB server machine, Solaris has a feature:
> create "intimate" shared memory with shmat(..., SHM_SHARE_MMU).
> All backends share the same TLB entries (!).

We use that already.  (Hmm, might be interesting for you to turn it
*off* and see if anything changes.  See src/backend/port/sysv_shmem.c.)

            regards, tom lane

On Tue, Jun 13, 2006 at 05:01:34PM -0700, Mischa Sandberg wrote:
> Jim C. Nasby wrote:
> >On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
> >>Jim C. Nasby wrote:
> >>>What's sort_mem set to? I suspect you simply ran the machine out of
> >>>memory.
> >>8192 (8MB). No issue when shared_buffers was 2000; same apps always.
> >
> >So if all 50 backends were running a sort, you'd use 400MB. The box has
> >4G, right?
>
> Umm ... yes. "if". 35-40 of them are doing pure INSERTS.
> Not following your train.

If sort_mem is set too high and a bunch of sorts fire off at once,
you'll run the box out of memory and it'll start swapping. Won't really
matter much whether it's swapping shared buffers or not; performance
will just completely tank.

Actually, I think that Solaris can be pretty aggressive about swapping
stuff out to try and cache more data. Perhaps that's what's happening?

> >>Yep, tested /etc/system segmap_percent at 20,40,60.
> >>No significant difference between 20 and 60.
> >That's pretty disturbing... how large is your database?
>
> ~10GB. Good locality. Where heading?

I guess I should have asked what your working set size was... unless
that's very small, it doesn't make sense that changing the cache size
that much wouldn't help things.

BTW, on some versions of Solaris, segmap_percent doesn't actually work;
you have to change something else that's measured in bytes.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Tom Lane wrote:
> Mischa Sandberg <mischa@ca.sophos.com> writes:

>> Tom Lane wrote:
>>> Does Solaris have any call that allows locking a shmem segment in RAM?
>> Yes, mlock(). But want to understand what's going on before patching.
>
> Sure, but testing it with mlock() might help you understand what's going
> on, by eliminating one variable: we don't really know if the shmem is
> getting swapped, or something else.

>> For a dedicated DB server machine, Solaris has a feature:
>> create "intimate" shared memory with shmat(..., SHM_SHARE_MMU).
>> All backends share the same TLB entries (!).
>
> We use that already.  (Hmm, might be interesting for you to turn it
> *off* and see if anything changes.  See src/backend/port/sysv_shmem.c.)

Gah. Always must remember to RTFSource.
And reproduce the problem on a machine I control :-)

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Folks,

First off, you'll be glad to know that I've persuaded two of the Sun
performance engineers to join this list soon.   So you should be able to
get more difinitive answers to these questions.

Second, 7.4 still did linear scanning of shared_buffers as part of LRU and
for other activities.   I don't know how that would cause swapping, but it
certainly could cause dramatic slowdowns (like 2-5x) if you overallocated
shared_buffers.

Possibly this is also triggering a bug in Solaris 2.6.   2.6 is pretty
darned old (1997); maybe you should upgrade?   We're testing with s_b set
to 300,000 on Solaris 10 (Niagara) so this is obviously not a current
Solaris issue.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco