16

(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.)

I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and namely, under what conditions that it starts to make sense to use 64-bit installations.

The database systems that I am interested in are: SQL Server 2008, MySQL, and PostgreSQL 9.0.

I have read that pre-9.0 versions of PostgreSQL only comes in 32-bit for Windows, and this article about running 32-bit PostgreSQL on 64-bit Windows clears up some of my confusion, but I'm looking for more info.

When would I benefit from using 64-bit databases (i.e. database size/disk space, available system memory, types of data sernarios that are known to benefit from it, which database engine being used, etc.)?

JohnB
  • 497
  • 2
  • 6
  • 12

5 Answers5

21

under what conditions that it starts to make sense to use 64-bit installations. Sorry - what good would be installing a 32 bit database on a 64 bit server? And - imagine - SERVER 2008R2 is ONLY available in 64 bit.

There is - today - no scenario where it makes sense to install a 32 bit SQL Server version if one has a chance.

Databases are specific in this - as they want to use a lot of memory as cache if necessary. A lot more than the meager 2gb / 3gb a 32 bit process can give them. PAE is not the same. Even ignoring limits, PAE memory is not equal to real memory for a SQL Server´(it is only used for ONE thing - caching db pages).

32 bit OS - is on the same level. it makes no sense on modern hardware at all to install a 32 bit OS.

Community
  • 1
TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 6
    Well as far as 32-bit OS and Apps, there are some minor advantages, such as smaller memory footprint. – JohnB Jul 13 '10 at 22:03
  • @TomTom: SQL Server 2008 R2 is available in 32-bit (scroll down slightly) http://msdn.microsoft.com/en-us/library/ms143506.aspx – JohnB Jul 14 '10 at 03:02
  • 1
    Yes, smaller footprint. SADLY especially for databases that is irrelevant. A database is not word. It deals with - under most installs - LOTS of data, so accessing LOTS of RAM makes sense. – TomTom Jul 14 '10 at 04:51
  • 1
    ... Lots of ram being more than 4GB in a process? You know how many SQL servers don't provide that for the whole multi-processed server? I'm not arguing that it is a bad idea to have a 64bit OS, and SQL server, but I will say there are plenty of configurations where it doesn't matter /at all/. – Evan Carroll Jul 14 '10 at 06:09
  • Yeah, Plenty of people also use ms access ;) Still: once installed 64bit, you can easily upgrade the RAM. Install 32 bit - reinstall. Still stupid. – TomTom Jul 14 '10 at 07:18
  • Many people run mysql on systems with much less than 3GB of RAM. That's the only scenario where I'd look at 32 bit, but even so I'd like to see an answer backed by data rather than attitude. – mc0e Nov 07 '16 at 14:45
6

PostgreSQL benefits from having a 64-bit build in two main ways. First, data types that can fit into 64-bits (larger integers and timestamp types mainly) can be more efficiently passed around directly in registers rather than using pointers. Second, it's possible to allocate more memory for the database's dedicated buffer cache. The point of diminishing returns on that tunable (shared_buffers) is usually around 8GB, but it will be limited to <2GB on a 32-bit system.

However, if you are on Windows, PostgreSQL doesn't handle shared memory as efficiently as on UNIX-ish platforms. The point of diminishing return generally ends up being <=512MB of dedicated memory for the database whether you have a 32-bit or 64-bit build of PostgreSQL. You'll do better to leave the rest for the operating system cache rather than dedicate it to the database. Accordingly, there really isn't that much of a performance gain going from 32 to 64 bits with PostgreSQL on Windows; the main tunable that would normally benefit from having more RAM available doesn't actually utilize it very well.

Greg Smith
  • 959
  • 5
  • 7
  • 1
    Note that some people have reported seeing a benefit from 64-bit Windows PostgreSQL by setting very large values for work_mem, which lets the server sort larger amounts of data in memory. – Greg Smith Mar 12 '11 at 16:59
4

I run MySQL on 64-bit architecture because I want them to most efficiently utilize more than 4GB of memory per thread. Generally speaking, this should apply to all databases.

One of the primary differences between the architectures is increased addressing allows greater memory handling. While Intel's Physical Address Extension allows addressing of more than 4GB, it is still limited to 4GB per thread. PAE allows up to a 64GB maximum.

Wikipedia has a comparison of 64-bit versus 32-bit, which includes more low level details.

Warner
  • 23,440
  • 2
  • 57
  • 69
3

Note that if you only have 64-bit MySQL client libraries, you will get "wrong architecture" errors when trying to link them together with 32-bit code. This happened to me when I tried to install python bindings ("pip install MySQL-python").

One can use 64-bit MySQL server with a 32-bit MySQL client and it's a shame that the MySQL Community Server doesn't include both 32-bit and 64-bit client library versions. The correct solution is to install additional 32-bit MySQL client libraries. However, since the easiest way to install MySQL seems to be the MySQL Community Server binary download, and given that the 64-bit installer comes with 64-bit client libraries only, the path of least resistance is to just download the 32-bit installer.

(all this, assuming that you will always use very small data sets)

Amit Moscovich
  • 131
  • 3
2

For so many things, 32 bit is a win (as long as you can live with the address space), but DB are one thing where even small databases can get a real boost running in 64 bit. Granted, I don't know a thing about MS SQL server, but I have seen benchmarks (for example, on a Sun 5 (older 64 bit Sun desktop), 32 bit was generally a little faster, except for mysql, which was 30% faster in 64 bit.

Ronald Pottol
  • 1,683
  • 1
  • 11
  • 19
  • 2
    "For so many things, 32 bit is a win" - Examples?? – Chris S Jul 14 '10 at 02:40
  • 1
    The only case I can imagine where a 32 bit database would be faster than 64 bit is if the code hasn't been compiled with appropriate optimisations. As far as business software goes, databases are probably the thing than can best take advantage of what 64 bits offers. – John Gardeniers Jul 14 '10 at 03:20
  • 1
    Well, large numbers of benchmarks in the RISC 32>64 transition era, and at least the early x86 showed little if any benefit to 64 bit, unless you need the address space, and db do. I didn't want to believe it either, and I bet for x86 today, 64 bit is always faster (just because you escape that i32 instruction set). Anandtech.com has benchmarks until your eyeballs bleed. Remember: SHOW ME THE DATA. – Ronald Pottol Jul 14 '10 at 07:12
  • 1
    All else being equal, the main disadvantage that i can think of for 64-bit is with larger pointers (64-bits), which will take up more cache memory space than an equivalent 32-bit dynamic data structure (i.e. linked lists, trees...). This reduces the amount of cache memory available. This symptom also persists in main memory. My 64-bit Debian server with only 64Mb of RAM uses more swap and runs less apps than a similarly setup 32-bit server. – sybreon Jul 14 '10 at 07:59
  • 3
    @sybreon: I think I have an old 128mb laying around if you need it! – JohnB Jul 14 '10 at 14:42
  • Here we go http://www.osnews.com/story/5768 The interesting thing is that a small DB on a small ram machine still was faster in 64 bit, even on a good 32 bit ISA (SPARC).
    Though now days we mostly only care about modernish x86 family cpu, and the 32 bit sucks so bad that 64 is usually a win, sometimes by a huge margin.     – Ronald Pottol Jul 14 '10 at 18:25
  • @sybreon, 64 bit OS with just 64MB (I'll assume you means bytes, not bits) of RAM? You're really not even giving it a chance. It brings to mind an image of a whale trying to get into a sardine can. – John Gardeniers Jul 16 '10 at 03:10
  • Haha. Thanks guys. It's a small hosted VPS. I am able to run mysql, lighttpd and php on it. On the 64-bit one, I can only run one instance of the php-cgi but with the 32-bit one, I can run 2-3 instances without breaking a sweat. – sybreon Jul 16 '10 at 13:22