PostgreSQL on 64 bit Linux
I have a PostgreSQL installation on a Debian box that had the 64bit SMP
kernel installed before PostgreSQL was compiled and installed on it.
Does PostgreSQL take any advantage of the 64 bit environment or have we
not done anything to move into the 64 bit world yet?
Regards,
- Naz
Naz Gassiep <naz@mira.net> writes:
I have a PostgreSQL installation on a Debian box that had the 64bit
SMP kernel installed before PostgreSQL was compiled and installed on
it. Does PostgreSQL take any advantage of the 64 bit environment or
have we not done anything to move into the 64 bit world yet?
Depends on whether PG was compiled as 64-bit or 32-bit--is your
toolchain 64-bit all the way, or is it just the kernel?
-Doug
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Douglas McNaught wrote:
<blockquote cite="mid87mz9zkue1.fsf@suzuka.mcnaught.org" type="cite">
<pre wrap="">Naz Gassiep <a class="moz-txt-link-rfc2396E" href="mailto:naz@mira.net"><naz@mira.net></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I have a PostgreSQL installation on a Debian box that had the 64bit
SMP kernel installed before PostgreSQL was compiled and installed on
it. Does PostgreSQL take any advantage of the 64 bit environment or
have we not done anything to move into the 64 bit world yet?
</pre>
</blockquote>
<pre wrap=""><!---->
Depends on whether PG was compiled as 64-bit or 32-bit--is your
toolchain 64-bit all the way, or is it just the kernel?
-Doug</pre>
</blockquote>
I just compiled as the manual says. I guess I must have compiled it in
32. I'll recompile in 64 when I upgrade to 8.2 when it's out.<br>
Thanks,<br>
- Naz.<br>
</body>
</html>
Naz Gassiep <naz@mira.net> writes:
I just compiled as the manual says. I guess I must have compiled it
in 32. I'll recompile in 64 when I upgrade to 8.2 when it's out.
The 'file' command will tell you whether a binary is 32- or 64-bit.
If you have a full 64-bit install, you'll get a 64-bit compile by
default, but it sounds like you just added a 64-bit kernel to a 32-bit
Debian system?
-Doug
On Sun, Aug 20, 2006 at 04:46:30PM -0400, Douglas McNaught wrote:
Naz Gassiep <naz@mira.net> writes:
I have a PostgreSQL installation on a Debian box that had the 64bit
SMP kernel installed before PostgreSQL was compiled and installed on
it. Does PostgreSQL take any advantage of the 64 bit environment or
have we not done anything to move into the 64 bit world yet?Depends on whether PG was compiled as 64-bit or 32-bit--is your
toolchain 64-bit all the way, or is it just the kernel?
I think he means - have benchmarks, or profiling been done with the
goal of specifically improving performance on 64-bit platforms.
For most applications available today, the answer is no. Compiling an
application designed for 32-bit, on a 64-bit architecture, does not
automatically improve performance. Too frequently, it can actually
reduce performance. Pointers are large, which means that any
application that is heavily pointer based can be forced to deal with
twice as many copies of memory, which reduces the effectiveness of
the various cache levels, and RAM itself.
Hopefully GLIBC counts here, in that it should contain 64-bit specific
code where it might count, so libc calls should be able to take
advantage of the 64-bit machine instructions.
Is there an interest, or any active project to examine PostgreSQL in
the area of 64-bit processors? Has it already been done? I don't recall
seeing a reference to it in my travels. I'm also not sure on what to
expect for results, as the territory is still new. 64-bit processors
have existed for a while, but 32-bit processors have been the popular
choice, making 64-bit support an after thought?
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...
On 8/21/06, mark@mark.mielke.cc <mark@mark.mielke.cc> wrote:
Is there an interest, or any active project to examine PostgreSQL in
the area of 64-bit processors? Has it already been done? I don't recall
seeing a reference to it in my travels. I'm also not sure on what to
expect for results, as the territory is still new. 64-bit processors
have existed for a while, but 32-bit processors have been the popular
choice, making 64-bit support an after thought?
That's certainly just a reference to the wintel world? AIX, HP-UX
and Solaris-Sparc have been 64-bit for a while now...
Cheers,
mark
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
mark@mark.mielke.cc writes:
Is there an interest, or any active project to examine PostgreSQL in
the area of 64-bit processors? Has it already been done? I don't recall
seeing a reference to it in my travels. I'm also not sure on what to
expect for results, as the territory is still new. 64-bit processors
have existed for a while, but 32-bit processors have been the popular
choice, making 64-bit support an after thought?
I find this question a bit amusing, since PG has run on 64-bit
architectures such as MIPS, Sparc, Alpha and PA-RISC for quite a while
now. :)
As I said in a private email to Naz, the main advantage I think you'd
see from 64-bit is the ability to run with more than 2GB or so of
shared buffers on a system with lots of RAM. Whether you'd want to do
that, or let the OS do most of the buffering, is an open question...
-Doug
Naz,
On 8/20/06 12:59 PM, "Naz Gassiep" <naz@mira.net> wrote:
I have a PostgreSQL installation on a Debian box that had the 64bit SMP
kernel installed before PostgreSQL was compiled and installed on it.
Does PostgreSQL take any advantage of the 64 bit environment or have we
not done anything to move into the 64 bit world yet?
Very likely the default gcc compiles for 64-bit, if not you need to specify
"-m64". As another respondent said - do a "file `which initdb`" to find out
whether you have compiled for 64-bit or not.
WRT 64-bit and Postgres, it depends on the CPU as to whether you see a
simple performance benefit. On the Opteron you will see a benefit when
doing CPU bound work. When doing the CPU portion, the additional registers
of the Opteron running in 64-bit mode are used by the compiler to produce a
20-30% boost in performance. On the Xeon in 64-bit mode, the same regions
of execution will slow down by about 5%.
Postgres benefits automatically from the larger memory addressing of the
64-bit kernel by using the larger I/O cache of Linux.
- Luke
WRT 64-bit and Postgres, it depends on the CPU as to whether you see a
simple performance benefit. On the Opteron you will see a benefit when
doing CPU bound work. When doing the CPU portion, the additional registers
of the Opteron running in 64-bit mode are used by the compiler to produce a
20-30% boost in performance. On the Xeon in 64-bit mode, the same regions
of execution will slow down by about 5%.
Is that true of even Woodcrest?
Joshua D. Drake
Postgres benefits automatically from the larger memory addressing of the
64-bit kernel by using the larger I/O cache of Linux.- Luke
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
=== 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/
One person who commented on the The business of Postbrsql made this comment:
Posted Aug 3, 2006 8:45 UTC (Thu) by subscriber *jgarzik* [Link
<http://lwn.net/Articles/193946/>]Cluster immaturity. MySQL has been
shipping a workable single-master replication+failover for quite a while
now in most Linux distros. MySQL's multi-master solution, while
requiring RAM (not disk) for storage, is also well-integrated and
deployed in production.
In contrast, the PostgreSQL team has chosen to provide hooks for
replication and failover. This has led to a situation where there are
multiple projects supporting replications/failover, none of which are
production-ready nor shipped in a modern Linux distro.
Modern systems *must* scale beyond a single computer, and the PostgreSQL
support shipped in modern Linux distros is completely incapable of this.
I really would appreciate a response. Thanks~ Michael
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.3/423 - Release Date: 8/18/2006
In contrast, the PostgreSQL team has chosen to provide hooks for
replication and failover. This has led to a situation where there are
multiple projects supporting replications/failover, none of which are
production-ready nor shipped in a modern Linux distro.
And no, we don't really provide hooks :). However there are several
projects trying to solve different problems with PostgreSQL.
Modern systems *must* scale beyond a single computer, and the PostgreSQL
support shipped in modern Linux distros is completely incapable of this.
Slony-I is quite capable as a production class FOSS replication system
and is in use widely.
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/
Josh,
On 8/20/06 8:52 PM, "Joshua D. Drake" <jd@commandprompt.com> wrote:
Is that true of even Woodcrest?
Joshua D. Drake
Not sure - haven't read anything about the register set on the Core 2 to
make me think it benefits from 64 bit.
The point may be academic from now on though - the comparisons between
Opteron and Core 2 will all likely be in 64-bit mode from now on.
- Luke
WRT 64-bit and Postgres, it depends on the CPU as to whether you
see a simple performance benefit. On the Opteron you will see a
benefit when doing CPU bound work. When doing the CPU portion, the
additional registers of the Opteron running in 64-bit mode are used
by the compiler to produce a 20-30% boost in performance. On the
Xeon in 64-bit mode, the same regions of execution will slow down
by about 5%.
Postgres benefits automatically from the larger memory addressing
of the 64-bit kernel by using the larger I/O cache of Linux.
Main benefit Postgres in 64-bit mode possible only in case dedicated
DB server on system with RAM > 3GB and use most part of RAM for
shared buffers and avoid persistent moving buffers between OS cache
and shared memory. On system with RAM below 2-3GB to difficult found
serious gain of performance.
Best regards,
Alexander Kirpa
Import Notes
Resolved by subject fallback
Joshua D. Drake wrote:
Modern systems *must* scale beyond a single computer, and the PostgreSQL
support shipped in modern Linux distros is completely incapable of this.Slony-I is quite capable as a production class FOSS replication system
and is in use widely.
Slony-I is not enough because it can cause the inconsistency of data between servers.
IMO, log-based replication is needed also for PostgreSQL just like MySQL.
Regards;
Fujii Masao wrote:
Joshua D. Drake wrote:
Modern systems *must* scale beyond a single computer, and the
PostgreSQL support shipped in modern Linux distros is completely
incapable of this.Slony-I is quite capable as a production class FOSS replication system
and is in use widely.Slony-I is not enough because it can cause the inconsistency of data
between servers.
hmm what are you refering to here ? slony1 does row-level replication
(something that MySQL cannot do until 5.1 which is still beta) - so it
should not be possible to cause data-inconsistency.
It is however async replication so you can loose data commited on the
master but not yet replicated to the slaves in case you loose the master
completely.
Stefan
Stefan Kaltenbrunner wrote:
It is however async replication so you can loose data commited on the
master but not yet replicated to the slaves in case you loose the master
completely.
Yes, here is an insufficient point of Slony-I, i think.
Most systems will not permit the committed data to be lost, so use is limited.
IMO, log-based replication is needed also for PostgreSQL just like MySQL.
Well, I had misunderstood MySQL. Its replication is also asynchronous.
regards;
On Mon, Aug 21, 2006 at 02:56:10PM +1200, Andrej Ricnik-Bay wrote:
On 8/21/06, mark@mark.mielke.cc <mark@mark.mielke.cc> wrote:
Is there an interest, or any active project to examine PostgreSQL in
the area of 64-bit processors? Has it already been done? I don't recall
seeing a reference to it in my travels. I'm also not sure on what to
expect for results, as the territory is still new. 64-bit processors
have existed for a while, but 32-bit processors have been the popular
choice, making 64-bit support an after thought?That's certainly just a reference to the wintel world? AIX, HP-UX
and Solaris-Sparc have been 64-bit for a while now...
I don't think so. In the Open Source world, most projects are still 32-bit
centric, regardless of how many years the products have been supported on
64-bit platforms.
What application were you thinking of that takes full advantage of 64-bit,
making the 64-bit application much significantly faster than the 32-bit
application? The only area I am aware of, is video processing.
It's often a surprise to people that an upgrade to 64-bit, regardless of
CPU architecture, too often ends up slower, rather than faster.
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...
On Sun, Aug 20, 2006 at 11:00:26PM -0400, Douglas McNaught wrote:
mark@mark.mielke.cc writes:
Is there an interest, or any active project to examine PostgreSQL in
the area of 64-bit processors? Has it already been done? I don't recall
seeing a reference to it in my travels. I'm also not sure on what to
expect for results, as the territory is still new. 64-bit processors
have existed for a while, but 32-bit processors have been the popular
choice, making 64-bit support an after thought?I find this question a bit amusing, since PG has run on 64-bit
architectures such as MIPS, Sparc, Alpha and PA-RISC for quite a while
now. :)
I don't think so. Software can be designed to take best advantage of
hardware. Recompiling it for a different architecture, running test
cases, and declaring support, is not the same as optimizing for.
As I said in a private email to Naz, the main advantage I think you'd
see from 64-bit is the ability to run with more than 2GB or so of
shared buffers on a system with lots of RAM. Whether you'd want to do
that, or let the OS do most of the buffering, is an open question...
This is what I mean by after thought. PostgreSQL is designed for
32-bit processors. Which is fine. I'm not complaining. The question
was whether there is an interest in pursuing 64-bit specific
optimizations. In the PostgreSQL code, a quick check points me only to
"has long int 64" as a 64-bit source code #ifdef. Of the six places
that reference this, five of them actually slow down the code, as they
check for overflow of the 'long int' result beyond 4 bytes of
data. The sixth place is used to define the 64-bit type in use by
PostgreSQL, which I suspect is infrequently used.
I believe the answer is no. No or few 64-bit optimization possibilities
have been chased down, probably because some or many of these would:
1) require significant re-architecture
2) reduce the performance in a 32-bit world
It's a question that only half interests me. As with most projects, I
don't think the projects are ready to re-architect for this
purpose. Perhaps once 50%+ of people are running PostgreSQL in 64-bit
mode, the question will be more serious to more people.
As a half interesting question, I'm defending it as a valid question.
Please don't write it off, but it is fine to say "not yet, we have more
important things to work on".
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...
On Mon, Aug 21, 2006 at 09:16:46AM -0400, mark@mark.mielke.cc wrote:
This is what I mean by after thought. PostgreSQL is designed for
32-bit processors. Which is fine. I'm not complaining. The question
was whether there is an interest in pursuing 64-bit specific
optimizations. In the PostgreSQL code, a quick check points me only to
"has long int 64" as a 64-bit source code #ifdef. Of the six places
that reference this, five of them actually slow down the code, as they
check for overflow of the 'long int' result beyond 4 bytes of
data. The sixth place is used to define the 64-bit type in use by
PostgreSQL, which I suspect is infrequently used.
There are two defines, the end result being to declare an int64 type
which is used a fair bit around the place. biginteger and bigserial
being the obvious ones.
The checks I see relate to strtol, where the code only wants an int4.
There's no strtoi so on 32 bit the range check is built-in, but if long
is 64 bit you have to do the check seperatly.
That's just an interface problem, there's not a lot we can do about
that really.
I believe the answer is no. No or few 64-bit optimization possibilities
have been chased down, probably because some or many of these would:1) require significant re-architecture
2) reduce the performance in a 32-bit world
Can you think of any places at all where 64-bit would make a difference
to processing? 64-bit gives you more memory, and on some x86 chips, more
registers, but that's it.
Have anice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
mark@mark.mielke.cc wrote:
This is what I mean by after thought. PostgreSQL is designed for
32-bit processors. Which is fine. I'm not complaining. The question
was whether there is an interest in pursuing 64-bit specific
optimizations. In the PostgreSQL code, a quick check points me only to
"has long int 64" as a 64-bit source code #ifdef. Of the six places
that reference this, five of them actually slow down the code, as they
check for overflow of the 'long int' result beyond 4 bytes of
data. The sixth place is used to define the 64-bit type in use by
PostgreSQL, which I suspect is infrequently used.I believe the answer is no. No or few 64-bit optimization possibilities
have been chased down, probably because some or many of these would:1) require significant re-architecture
2) reduce the performance in a 32-bit world
Just out of intereset - what areas in postgres do you think could be
improved (performance wise) on 64-bit machines? The only area that
I can see is the int64 datatype - it's stored in palloc()'ed memory
on 32-bit machines AFAIK - I'm not sure if it uses the "long long"
datatype on 64-bit archs.. But I can't imagine any other area that
could be tuned by making use of (native) 64-bit ints.
greetings, Florian Pflug
