One idea we're exploring at the moment is converting from our current MyISAM storage engine to InnoDB, with the idea that row level locking as opposed to table level locking will eliminate potential collisions which may be causing the increasing number of table crashes we're seeing.
The downside to this, from what I can see, is the lack of FULLTEXT INDEX support in InnoDB meaning we would require an alternative search facility to be brought online. As mentioned in the previous post, I can see
Sphinx in this role, and in fact I like the
idea of functionally seperating search from the user,post,thread model. Also, I would imagine that the lack of fulltext searching would significantly reduce the load on the primary server. Paired with InnoDB's
Hot Backup facility, it should eliminate the need for a secondary MySQL server altogether at this juncture, thus freeing up the other database server for Sphinx, improving the speed of the site and the search while eliminating downtime almost entirely.
...of course this is all just theory right now :) I will need to talk to people who know more than me, and hopefully those people exist within my sphere of contact.