HowTo Get Max Peform with MySQL 5.0 and FreeBSD 5.4 ??

[Ashok Shrestha]

I'm using MySQL 5.0.15 with FreeBSD 5.4-RELEASE amd64.

The box specs:
CPU: Dual Core AMD Opteron(tm) Processor 275 (2193.76-MHz K8-class CPU)
Memory: 4 GB

My databases will be anywhere from 2GB-20GB.

I would like to get the maximum performance out of this box. What options
(MAKE, kernel, etc...) should I use to achieve this? What about loading the
INDEX file into memory?

Should I build WITH_LINUXTHREADS? Is it still necessary to do so on FreeBSD
5.4? I know it is better on a 4.X machine. Note: It is not possible to build
WITH_LINUXTHREADS on a FreeBSD 5.4 AMD64.

What are the advantages of building with (MAKE) BUILD_OPTIMIZED?
 Is there an advantage in using a 64bit FreeBSD?

How should I configure the following in /etc/loader.conf? I could not find
an authoritative definition any of these variables; do you know of any?

kern.maxdsiz="3221225472" # 3GB; should it be closer to 4GB?
kern.dfldsiz="3221225472" # 3GB; should it be closer to 4GB?
kern.maxssiz="134217728" # 128MB; what is this? stack size? how large should
I make it?


--
Ashok Shrestha