So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can take any more.
I have been seeking out my options and have come to the conclusion I should go about either setting up a Replication or Cluster.
But as with anything everyone's needs are different, So I will inform you about my current application. Joomla CMS, InnoDB database handler. (Pretty straight forward.)
Now the way I can think of it being the easiest setup for my needs is something like this. I know replication and clustering probably does not work like this but I ask this question to be corrected and to be pointed in the direction of the setup I should pursue.
I want to load balance all forms/types of queries across multiple machines and have them all somehow sharing the same database across a private network like Network Sharing mapped hard drives what ever.
That way instead of a single machine's CPU being utilized to select update and insert it will load balance between multiple utilizing multiple servers resources in order to return the query results.
Thanks in advanced for reading this much appreciated for any help that can be provided on what I should go with and type of setup I require.