1

I have a relatively small and happy MySQL 5.1 database living on a FreeBSD 7.1 64bit install. Because of incompatibility with our backup software and FreeBSD, I have to move the databases to a Linux server.

Since we run Debian on everything else here I'm going to settle for that. The big question is if I should run 32bit or 64bit. It's a virtualized enviroment, so hardware doesnt play a role in this.

Will I get any performance gain (or loss) by using 64bit linux+64bit MySQL with little memory assigned (lets say 512M to be extreme)? Should I just use 32bit until the day I need to assign more than 2GB RAM to MySQL (PAE does not help, 32bit MySQL can not use more than 2GB)?

pauska
  • 19,620
  • 5
  • 57
  • 75
  • side-note: I've considered using 32bit because of the VMWare Paravirtualization support, but it seems like performance gains are minimal unless you are running a extremely busy and complex database. – pauska Jun 15 '09 at 11:06
  • How big do you expect your dataset to grow to? Disk space and/or number of rows is what I am asking for. – Bruno Bronosky Jun 15 '09 at 15:46
  • I have no idea at all, but disk space is not a problem (I have a 42TB SAN, and the system is virtualized so disk growth/swap is a few clicks away). Thanks everyone for insightful answers, I went for 64bit so I don't have any worries in the future about scaling. – pauska Jun 15 '09 at 17:47

4 Answers4

8

You already answered the question yourself.

If you are 100% percent positive not to grow beyond 2GB of RAM usage in the current hardware lifecycle you can stay with 32bit, when the time comes to replace your hardware you will have to reconsider anyway.

Given the current hardware market I don't see a real reason not to use 64 bit it will be the safe side in case of unforseen growth, that is true for cache as well as real data size. After all a full migration will take somewhat longer than just taking the box down and adding some RAM.

Drawbacks could be that pointers on a 64bit system use about 30% more memory but that won't really hurt you if you really stay on the small side of memory usage, but on the other hand if you grow you will be able to grow more quickly (at least as long as you can stay on the same hardware box)

serverhorror
  • 6,478
  • 2
  • 25
  • 42
  • As a side note, if you are not bound to mysql I've had much better success scaling in both directions with PostgreSQL than with MySQL. Thou if you really want to go small sqlite might be worth a try – serverhorror Jun 15 '09 at 10:46
  • Thanks for the answer, but my hardware is already ready for 64bit (it's VMware ESX running on HP DL380 G5's) – pauska Jun 15 '09 at 10:47
  • That was my argument why to specifically choose 32bit over 64bit, maybe that was badly worded. I meant: There's no reason to specifically limit yourself to 32bit when just about any server hardware these days is 64bit capable... – serverhorror Jun 15 '09 at 10:57
  • Ok, I understand you better now. – pauska Jun 15 '09 at 11:00
4

64bit. If for some reason you get a request that blows the database needs beyond the 512, you can just add some cheap ram and be done with it. No reinstall, etc.

Dayton Brown
  • 1,539
  • 2
  • 13
  • 23
  • 1
    Yes, that was my initial thoughts aswell - except that RAM is not very cheap when I need to expand from 16GB to 32GB (it's a virtualized enviroment like I said) – pauska Jun 15 '09 at 10:49
  • Ahhhh, there is the rub. I still stand by the 64 bit answer though. You could move to more expensive hardware, etc. without any reinstall or time on your part. Your time is still worth more than the cost of hardware. – Dayton Brown Jun 15 '09 at 13:43
1

Running Mysql 5 or later on a 64bit OS is usually worth it regardless of the memory you're addressing because of the internal use of 64 integers in the database. Unless you're trying to fit your install into a tiny amount of RAM there is almost no reason to choose a 32bit install of Mysql.

kashani
  • 3,922
  • 19
  • 18
0

I think that what is more important than choosing 32bit or 64bit is:

  1. Make sure you are using LVM or some storage technique that will allow you to make backups of your data via snapshot. (Yes, even with MyISAM you can flush with lock, snap, release)
    1. I can move an active DB from on a 32bit system to a beefier one on a 64bit system with 0-5 minutes downtime.
    2. With this flexibility, I would choose to start out 32bit. If I didn't have it, I would likely go 64bit to be safe.
  2. Consider what else may need to run on the system. You may find that some other program you are wanting to run is 32bit only.

That is how would make my decision. I wouldn't "choose" an architecture. I would compare my case against a logic tree like that.

Bruno Bronosky
  • 4,529
  • 3
  • 26
  • 34