How to distribute MySQL over various machines (or otherwise up its performance)?

[Olaf Greve]

Hi,

I have been asked to assist in a most interesting challenge: getting rid
of a Win2K server (running MySQL) on which MySQL takes up around 100% of
the CPU. :) 
I have near total freedom in suggesting a replacement architecture
(within some reasonable finacial limits, of course), and I am
considering suggesting a solution that involves one or more FreeBSD
MySQL DB servers, in order to speed up the database performance.

Now, there are various strategies that spring to mind, and I was hoping
someone could perhaps tell me some more about this from personal
experience or hearsay.

Regardless of what the eventual suggestion will be, first I'll tune the
current DB by assigning a proper DB scheme and by properly using
indexes. I've got a gut feeling that these guys set up the DB without
paying proper attention to that (in this case probably due to a lack of
experience with this), so hopefully a lot can already be gained by doing
so.

Nonetheless: for setting up a more robust and fast DB server (or server
cluster?) I'd like to take matters a step further, by using a fast
hardware set-up as well (note: in any and all proposed architecture,
I'll propose to use plenty of memory).

Now, here comes the bit with which I do not have any experience, so I'm
hoping perhaps someone can help me getting started on the proper path.
The following is what I'm considering as potential steps:

-The guys for whom this will be done mentioned having acquired 64-bit
motherboards (I do not yet know the exact type), they do not mind
installing multiple processors on it.
Question: which FreeBSD version can best be used in order to optimally
make use of a 64-bit and/or multi processor architecture?

-RAID: for performance and security matters, I _think_ a RAID 10
architecture would be a very good choice. By using a proper U320 SCSI
hardware set-up, running in RAID 10 mode, I think much can be gained.
Cost is somewhat of an issue, but not all too much. I'm considering the
Adaptec 2200S RAID controller, with 15K U320 drives (like the Maxtor 15K
36 GB drives or so).
Questions: does it really make sense to use U320 (and 15K instead of
10K) instead of U160? I'm not certain what the speed is of the PCI slots
that are present on the motherboards that are to be used, but am I right
that if it's the normal speed (being 133MHz), that virtually nothing is
gained by using U320 over U160 (as U160 would then already be faster
than the bus speed anyway)?
Also: does anyone have an insight in actual DB performance gain by using
striping? RAID 0 is not an option, it'll have to be fault tolerant. I'm
somewhat suspicious of RAID 50 and the likes, hence the idea of using
RAID 10...

-Using multiple machines.
Questions: is there something like a 'MySQL load balancer'? Is this a
good idea at all, or will a fast machine (e.g. dual processor) + enough
memory (1GB? 2GB?) + a fast hardware RAID 10 set-up already be more than
sufficient to do the job?

I realise this is a long mail, so sorry for that. :)
I hope someone can give me some good pointers and/or other general
information for how to best handle this...

Thanks in advance, and cheerz!
Olafo