Some quick -yet important- questions for AMD-64 as DB server OS

[ray at redshift.com]

At 08:40 AM 11/18/2005 -0500, Nathan Vidican wrote:
| Olaf Greve wrote:
| > Hi all,
| > 
| > I have just been asked for my opinion/experiences in setting up a 
| > high-end MySQL DB server. Now.... I know some of the main things to look 
| > at, but I'm not 100% certain about some of the other things, and based 
| > on what I'll say they'll make their hardware decisions as well (and I'll 
| > be the one who will then most likely have to tune the OS and DB).
| > 
| > Unfortunately they didn't ask me this any time sooner, and if possible 
| > they would like to make their hardware choices today or on Monday, so I 
| > have precious little time to do the typical Googling and reading on 
| > forums etc.
| > 
| > Instead, I have a few basic questions, and based on the input I hope I 
| > can at least give them a hardware suggestion, and then I'll figure out 
| > the details when the machine is in... :)
| > 
| > Their questions basically come down to the following, and I hope someone 
| > can answer them for me:
| > 
| > 1-What would be the most ideal selection of processor(s)? Would that be 
| > Dual AMD-64 (Athlon?), Dual Xenon, or Dual core AMD-64? Also, generally 
| > spoken what is better: two AMD-64s or one Dual core AMD-64?
| > 
| go with AMD Opteron, hands-down better choice. Single or dual cpu's with single 
| or dual cores, your choice... personally, considering the price I'd go with dual 
| (single core) cpus, like two AMD Opteron 246 cpu's. Also, I'd reccomend using 
| ECC registered memory if cost is not the ultimate issue.
| 
| > 2-Does anyone know if the MySQL versions that appear in the amd64 ports 
| > are specifically tuned for the AMD-64 architectures, or are they not a 
| > whole lot different from the normal -say- i386 ports versions? IOW, just 
| > "how 64-bits" is the amd64 version of MySQL?
| > 
| 
| Not entirely sure, built mysql/64 from the source myself - will say it runs 
| GREAT so far.
| 
| > 3-Which MySQL version (generally spoken) nowadays has the edge for 
| > performance (under the amd64 version of FBSD), would that be 4.0.x, 
| > 4.1.x or is 3.23.x better for speed?
| > 
| 
| Not have any definitive benchmarks, but will vouch for the stability. Hardware 
| wise, the AMD Opteron runs faster than most anything else in it's price-range 
| anyhow - on that alone one can assume better results.
| 
| > 4-Does MySQL have explicit tuning options (e.g. in my.cnf) in order to 
| > specify the amount of processors to use, or is that automatically 
| > handled by the OS?
| > 
| 
| Yes - check http://www.mysql.com/
| 
| > 5-The people who asked me this are very W*nd*ws minded, and I am not. I 
| > told them that IMO it would be far better and easier to tune a MySQL DB 
| > under Unix than under W*nd*ws. Can someone please give me a good edge, 
| > for this such that indeed they will NOT want to opt for W*nd*ws, or am I 
| > totally off-base here and can the W*nd*ws versions of MySQL be equally 
| > well tuned as the Unix versions for a high-end (!) DB system?
| > 
| Don't know much myself about tuning/performance on windows. Do know however, 
| that administation on windows for mysql is a pain. Lack of Perl, lack of a 
| shell, etc make the scripts that come with myself virtually useless. You can 
| always consider the downfall to the windows O/S wasting cpu/ram resources on 
| running a full GUI that you don't need too.
| 
| > 6-Any pitfalls you may be aware of when using the amd64 FBSD version for 
| > a high-end DB server?
| > 
| None I've encountered, been running for a year now almost with freebsd/amd64 and 
| mysql - using transactional innodb tables.
| 
| > Thanks heaps in advance, and cheers!
| > Olafo
| > _______________________________________________
| > freebsd-amd64 at freebsd.org mailing list
| > http://lists.freebsd.org/mailman/listinfo/freebsd-amd64
| > To unsubscribe, send any mail to "freebsd-amd64-unsubscribe at freebsd.org"
| > 
| > 
| 
| hope it helps...

For production database servers, I use Dual AMD 246 machines, running on Tyan
S2881 motherboards.  The AMD CPU's are much faster than the Xeon machines in my
benchmarking tests.  You can also gain considerable speed by using a properly
configured RAID 1+0 on the database server.  I personally use the 3Ware RAID
cards (specifically the 9500S line).  With 4 or 8 SATA 10K RPM drives on it, it
runs really well/fast under FreeBSD 5.4 AMD64.  Just make sure to compile the
SMP kernel  :)

If you are interested, I have some benchmarks I've done that I can send along.
I've always used MySQL 4.1.xx (built from source) on the machines with very good
luck - all with Innodb tables and then interfaced via the with_mysql lib in PHP.
 Works like a dream.

As far as trying to use Windows over FreeBSD for a high end database server,
that sounds about as fun as firing a nail gun through the top of your foot.

Ray