Thread: compiling, performance of PostGreSQL 8.3 on 64-bit processors

compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Benjamin Weaver
Date:
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.

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

Thanks in advance.

Yours Ben Weaver



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


Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
"Adam Rich"
Date:
> 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?





Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Greg Smith
Date:
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

Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
"Douglas McNaught"
Date:
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.


Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Tom Lane
Date:
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

Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Benjamin Weaver
Date:
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


Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Tom Lane
Date:
"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

Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
"Douglas McNaught"
Date:
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

Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
"Scott Marlowe"
Date:
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.

Re: compiling, performance of PostGreSQL 8.3 on 64-bit processors

From
Greg Smith
Date:
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