Customer is reporting a high traffic on his MySQL Server and ready to add two more servers on his private network to share the load. How to Setup a Load balance clustering to off-load the MySQL load on the primary server ?
First off, I would exhaust all efforts in optimizing the current server before even considering moving to a clustered setup. The administrative requirements on a cluster or even just several replicated instances is substantially more than a single server or even a bunch of single servers, not even counting the cost to physically setup multiple servers. Also, you say high traffic, but do you actually know what the bottlenecks are with respect to the database? Most of the time, high traffic, means a database gets read constrained which could simply be a matter of optimizing the database for the hardware, optimizing the software with caching, and adding as much RAM as humanly possible. Need to make sure your front end web servers are connected to the database via direct gig ethernet or fiber at the very least or running 10Gb fiber or ethernet cards if possible. After that, I would be running 128Gb - 256Gb of RAM in the database server, using well tuned Innodb table types, and either enterprise SSD cards or RAID 5 or 10, 15K SCSI drives on their own RAID controller card. Secondly, I don't think a question can be answered appropriately on a forum as setting up any cluster or multi node database is complicated and is often very dependent on the hardware that the servers are running on. The scope and complexity is something that almost needs to be handled hands on. I would definitely lean towards a mariadb or percona mysql instead of a vanilla installation of mysql, but again wouldn't even consider pursuing it until you are absolutely certain that there is nothing else you can do with your existing setup.
And if you read the documentation, you'll see that you probably need to upgrade: http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html
typical. i moved all of my databases from InnoDB to MyISAM when 5.4 was the latest version so I could do FULLTEXT search... Is there really any benefit of moving back? My databases are almost a TB
Depends on usage and optimization, I guess. Usually, innodb is recommended, albeit I know some instances where myisam performes better
The lack of MyISAM foreign key support is usually a deal breaker by itself for me. But the biggest difference is tunability. MyISAM is virtually untunable, whereas Innodb can be very customized to specific usage. MyISAM is often better out of the box for people that aren't going to mess with their database parameters. I've only seen a handful of cases where MyISAM was better than Innodb once it was reasonably tuned. If you're not having problems and the current performance is sufficient, there probably isn't a lot of reason to migrate back. If you start running into performance issues or want more control, or think you will in the future, then I would definitely migrate back or at least look into any benefits of changing for your current usage. The larger the database, the harder and longer it takes to change engines or make structural changes of any type, and where you're at right now, it's probably close to a point where it becomes prohibitive to make indiscriminate structural changes.