0

I have an application for which I‌ want to set up a master/slave mysql database, because the current db struggles under high load.

I have two different servers for db replication. Lets call them A and B. A is the current db server.

Let's score the CPU power of A as 10 and B as 7. B has half the memory of A. Both servers run on SSD.

The read/write ratio is about 9 to 1.

Now, my question is which server should be master? and why?

1

In general (until very recent versions), it is better to make the Slave the beefier machine.

Originally, you could easily run queries in parallel on the Master, but they would be run purely sequentially on the Slave. If the system is busy enough, the Slave could get behind. Later versions have changed to RBR and added some parallelism on the Slave.

Also, if the Slave is getting 9 times as many queries (assuming of similar length), then it would be good to have a beefier Slave for that reason.

| improve this answer | |
  • What are the very recent versions in mariadb where the beefier should be the master? – zumba May 1 '17 at 23:21
  • 1
    MariaDB 10.0.5 describes the "parallel replication" in which the Slave is less likely to get behind. – Rick James May 1 '17 at 23:58
  • Ok I'm using 10.1. So do you suggest that the beefier server should be the master? – zumba May 2 '17 at 0:10
  • 1
    could be the Master. There are too many possible things going on to say (after 10.0.5) which should be the Master. Such as: Do you have lots of reads on the Slave (need beefy slave); lengthy transactions that end up writing very little (need beefy master); more connections to the Master (need beefy master); etc. – Rick James May 2 '17 at 1:36
  • Meanwhile, MySQL (Oracle's) parallel replication was splitting on database. This had the downside of being useless when you have only one database. That has be rectified only recently. – Rick James May 2 '17 at 13:24

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.