Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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.0 +0200 +++ postgresql-8.1.4/src/include/storage/s_lock.h 2006-05-31 09:19:04.0 +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) : ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
On Tue, 13 Jun 2006 04:26:05 +0200, Alex Turner [EMAIL PROTECTED] 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 lrwxrwxrwx1 root root5 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... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Sven, On 6/13/06 2:04 AM, Sven Geisler [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Luke Luke Lonergan schrieb: On 6/13/06 2:04 AM, Sven Geisler [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Sven, On 6/13/06 5:03 AM, Sven Geisler [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Luke Luke Lonergan schrieb: Sven, On 6/13/06 5:03 AM, Sven Geisler [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Sven, On 6/13/06 5:46 AM, Sven Geisler [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Luke, Luke Lonergan schrieb: On 6/13/06 5:46 AM, Sven Geisler [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Anthony Presley [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
* Anthony Presley ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Anthony Presley [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Title: Re: [PERFORM] 64-bit vs 32-bit performance ... backwards? Opteron is ~20% faster at executing code in 64-bit mode than 32-bit because of the extra registers made available with their 64-bit mode: http://www.tomshardware.com/2003/04/22/duel_of_the_titans/page7.html Doubling the GPRs from 8 to 16 has generally made a 20%-30% difference in CPU-bound work: http://www.tomshardware.com/2003/04/22/duel_of_the_titans/page18.html If the task is memory bandwidth bound, there should be an advantage to using less memory for the same task, but if the database is using types that are the same width for either execution mode, you wouldn't expect a significant difference just from wider pointer arithmetic. - Luke
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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.On 6/12/06, Steve Atkins [EMAIL PROTECTED] 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 bitcode on it.Mine're mostly reasonably conservative users, with hundreds of machinesto support. Using 64 bit capable hardware, such as Opterons, is one thing,but using an entirely different linux installation and userspacecode, say, isa much bigger change in support terms. In the extreme case it makes nosense to double your OS support overheads to get a single digit percentageperformance improvement on one database system.That's not to say that linux/x86-64 isn't production ready for someusers, justthat it's not necessarily a good operational decision for my customers. Givenmy internal workloads aren't really stressing the hardware they're onI don'thave much incentive to benchmark x86-64 yet - by the time the numbersmight be useful to me we'll be on a different postgresql, likely a differentgcc/icc and so on.Cheers, Steve---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
[EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Alex Turner [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
Mark, On 6/12/06 7:16 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
