compiling, performance of PostGreSQL 8.3 on 64-bit processors

1. I have heard of problems arising from compiling PostGreSQL (8.3) on
64-bit
processors. What sort of problems am I likely to encounter and how
should I fix
them? We are will run Linux Redhat 5 on a Dell PE2950 III Quad Core
Xeon E54
2.33 GHz, and a Dell PE2950 III Quad Core Xeon L5335 2.0 GHz.

2. Are there performance problems running PostGreSQL 8.3 on a 64-bit
processor?

I have a few more questions on the 64-bit topic. Is there any benefit
to running a 32-bit OS (rhel 5 in this case) on a server with more than
4 GB of memory? In other words, can the OS-level cache take advantage
of more than 4 GB of memory? Can a process (such as PG backend) use
more than 4 GB of shared memory on a 32-bit OS? Or is the 4 GB memory
point the place where you normally transition to a 64-bit OS?

For people with experience running postgresql on systems with 16+ GB
of memory, what parameter settings have you found to be effective?
(This would be a large database that's mostly read-only that we'd
like to fit completely in memory)

Is it possible to backup (pg_dump) from a 32-bit OS to a 64-bit OS,
or is a plain SQL dump necessary?

On Thu, 26 Jun 2008, Benjamin Weaver wrote:

I have heard of problems arising from compiling PostGreSQL (8.3) on
64-bit processors.

From who?

We are will run Linux Redhat 5

If there were any problems compiling and running PostgreSQL on 64-bit
RHEL5, I wouldn't be writing this message right now because I'd be hiding
from angry customers circling with torches and pitchforks. And Tom Lane
would have already committed ritual suicide in shame.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Thu, Jun 26, 2008 at 7:12 PM, Adam Rich <adam.r@sbcglobal.net> wrote:

1. I have heard of problems arising from compiling PostGreSQL (8.3) on
64-bit
processors. What sort of problems am I likely to encounter and how
should I fix
them? We are will run Linux Redhat 5 on a Dell PE2950 III Quad Core
Xeon E54
2.33 GHz, and a Dell PE2950 III Quad Core Xeon L5335 2.0 GHz.

2. Are there performance problems running PostGreSQL 8.3 on a 64-bit
processor?

Should compile and run fine--I haven't heard of any problems with it,
and PG has run on 64-bit systems (SGI, Sun, Alpha) long before there
was x86_64.

I have a few more questions on the 64-bit topic. Is there any benefit
to running a 32-bit OS (rhel 5 in this case) on a server with more than
4 GB of memory? In other words, can the OS-level cache take advantage
of more than 4 GB of memory?

With a 32-bit kernel and >4GB of memory, you will indeed get more
available to cache from the OS. It's generally not recommended to use
more than 8GB with a 32-bit kernel.

Can a process (such as PG backend) use
more than 4 GB of shared memory on a 32-bit OS?

No, and the practical limit is more like 2GB (or less).

Or is the 4 GB memory
point the place where you normally transition to a 64-bit OS?

In this day and age there's not too much of a reason to run in 32 bits
on a server capable of 64. If necessary, you can run 32-bit legacy
binaries under a 64-bit kernel, if you set up the libraries properly.

For people with experience running postgresql on systems with 16+ GB
of memory, what parameter settings have you found to be effective?
(This would be a large database that's mostly read-only that we'd
like to fit completely in memory)

Can't help with this one, sorry.

Is it possible to backup (pg_dump) from a 32-bit OS to a 64-bit OS,
or is a plain SQL dump necessary?

pg_dump output (which by default *is* plain SQL, though there are two
other formats) is compatible across architectures. Backups of the
on-disk database files are not.

-Doug

On Thu, 26 Jun 2008, Benjamin Weaver wrote:

Dear All,

1. I have heard of problems arising from compiling PostGreSQL (8.3) on 64-bit
processors. What sort of problems am I likely to encounter and how should I fix
them? We are will run Linux Redhat 5 on a Dell PE2950 III Quad Core Xeon E54
2.33 GHz, and a Dell PE2950 III Quad Core Xeon L5335 2.0 GHz.

What problems have you heard? It's working quite well for us on our 64-bit
Xeons, but maybe there are some snake zygotes hidden under rocks that
we're unaware of.

Greg Smith <gsmith@gregsmith.com> writes:

On Thu, 26 Jun 2008, Benjamin Weaver wrote:

I have heard of problems arising from compiling PostGreSQL (8.3) on
64-bit processors.

From who?

Perhaps someone who remembers PG 6.4 or thereabouts?

Certainly any version released in the last couple of years has been
tested about as heavily on 64-bit platforms as 32-bit.

regards, tom lane

Sorry, guys, for wasting bandwidth on this! You guys gave just the answer I
wanted to hear. Sounds like there aren't any problems.

Not knowing about such things, I was scared by the following quote. Perhaps
binaries do not need to be compiled as 64 bit binaries on a 64 bit machine? Or
perhaps it's way out of date (2004) or simply wrong.

from

http://www.osnews.com/story/5768/Are_64-bit_Binaries_Really_Slower_than_32-bit_Binaries_/page3/

"
...

The Compile Factor

Getting applications to compile as 64-bit binaries can be tricky. The build
process for some applications, such as OpenSSL, have 64-bit specifically in
mind, and require nothing fancy. Others, like MySQL and especially PostgreSQL (I
was originally going to include PostgreSQL benchmarks) took quite a bit of
tweaking. There are compiler flags, linker flags, and you'll likely end up in a
position where you need to know your way around a Makefile..."

In message <23234.1214523814@sss.pgh.pa.us> Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Smith <gsmith@gregsmith.com> writes:

On Thu, 26 Jun 2008, Benjamin Weaver wrote:

I have heard of problems arising from compiling PostGreSQL (8.3) on
64-bit processors.

From who?

Perhaps someone who remembers PG 6.4 or thereabouts?

Certainly any version released in the last couple of years has been
tested about as heavily on 64-bit platforms as 32-bit.

regards, tom lane

--
Benjamin Weaver
Faculty Research Associate, Imaging Papyri, Greek Fragments Projects, Oxford
email: benjamin.weaver@classics.ox.ac.uk
phone: (0)1865 610236

On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
<benjamin.weaver@classics.ox.ac.uk> wrote:

Sorry, guys, for wasting bandwidth on this! You guys gave just the answer I
wanted to hear. Sounds like there aren't any problems.

Not knowing about such things, I was scared by the following quote. Perhaps
binaries do not need to be compiled as 64 bit binaries on a 64 bit machine? Or
perhaps it's way out of date (2004) or simply wrong.

Distro support for 64-bit x86 in 2004 was light-years behind where it
is now. A lot of stuff was hard to get working back then. Now almost
everything basically Just Works.

-Doug

"Douglas McNaught" <doug@mcnaught.org> writes:

On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
<benjamin.weaver@classics.ox.ac.uk> wrote:

Not knowing about such things, I was scared by the following quote.

Distro support for 64-bit x86 in 2004 was light-years behind where it
is now. A lot of stuff was hard to get working back then. Now almost
everything basically Just Works.

Even in 2004, the guy would have had to be working on a rather old or
broken distro to justify such a complaint. Getting 64-bit to work was
a live issue maybe around 2001 or so...

regards, tom lane

On Fri, Jun 27, 2008 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Douglas McNaught" <doug@mcnaught.org> writes:

On Fri, Jun 27, 2008 at 11:52 AM, Benjamin Weaver
<benjamin.weaver@classics.ox.ac.uk> wrote:

Not knowing about such things, I was scared by the following quote.

Distro support for 64-bit x86 in 2004 was light-years behind where it
is now. A lot of stuff was hard to get working back then. Now almost
everything basically Just Works.

Even in 2004, the guy would have had to be working on a rather old or
broken distro to justify such a complaint. Getting 64-bit to work was
a live issue maybe around 2001 or so...

Yeah, I thought his complaint about pg 64 bit compiling was a bit
wacky. I was building 8.0 and 8.1 64 bit on our servers back then and
/ or installing x86_64 rpms with no problems at all in 2004/5 or so.

MySQL compiling on the other hand, has always been a frakking nightmare.

On Thu, 26 Jun 2008, Adam Rich wrote:

Is there any benefit to running a 32-bit OS (rhel 5 in this case) on a
server with more than 4 GB of memory?

If you have more than 3GB of memory, you should be using a 64-bit OS.
While theoretically the 32-bit code might be smaller which has some
advantages, in practice the 64-bit versions will be faster.

For people with experience running postgresql on systems with 16+ GB of
memory, what parameter settings have you found to be effective? (This
would be a large database that's mostly read-only that we'd like to fit
completely in memory)

Much larger values for shared_buffers and work_mem seem to be the most
effective way to use larger amounts of memory. For example, if you've got
1GB of RAM, it can be hard to allocate >15% of it to shared_buffers while
leaving enough enough RAM for OS-level operations, applications, etc.
But if you've got 16GB, a large read-only database might usefully set that
to 50% of RAM instead.

Is it possible to backup (pg_dump) from a 32-bit OS to a 64-bit OS,
or is a plain SQL dump necessary?

pg_dump is a plain SQL dump, it's just a program to make it easier to
generate them. You need to do this sort of dump/reload in order to
convert from a 32-bit to a 64-bit platform.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD