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 drew a lovely little bit of art in Windows Paint in order to show you what I am after. http://i.imgur.com/TVzSLhW.png

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.

  • What is the status of this abandoned page? Did Rick solve your issue? If not, what can be done to progress this page to a system-recognized resolution? – mickmackusa Feb 5 '19 at 11:26

That topology will help scale reads. Is that what you need?

Writes must go to the Master, from which they will be 'replicated' to the Slaves. The Slaves will be readonly.

So, send any write, together with associated reads, to the Master. Send all other reads to a Slave in a round-robin way.

High CPU usually means you have a query that needs optimizing. Let's see such a query, plus SHOW CREATE TABLE.

Depending on what you say to those comments, we may move into talk of Galera Cluster.

  • Thanks my slow query log also shows that there are no UPDATE queries that could be slow but only 2 SELECT queries that are slow (1 second+) They also use DISTINCT and JOIN's i notice. – C0nw0nk Jul 10 '16 at 16:50
  • Start a new question with those SELECTs, plus the SHOW CREATE TABLE (for each table in the join). We may be able to recommend a way to speed up the query. (Often it involves a 'composite' index.) – Rick James Jul 10 '16 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.