1

I have replication environment running in a master-slave configuration. The replicant is doing all writes and doing selects only very rarely - and none in which high performance is a requirement.

Aside from the replication options themselves which have to be set, do the db administrators here have any recommendations for configuration of a mysql replicant that would be different from the default my.cnf values on a master server which is doing both selects and writes?

I can provide my own mysql-tuner results and my.cnf configuration, but I guess I was hoping for a more general overview on the configuration best practices for a replicant that is doing mostly writes.

| improve this question | | | | |
1

There are no hard-and-fast rules for such Slaves. Let's look at two scenarios

SCENARIO #1

Some standardize all DB Servers to have the same configuration. The rationale behind this is promote a Slave to a Master should have to fail over to it.

SCENARIO #2

A scaled down Slave being used as a data backup. No reads from that Slave.

COMPARISON OF SLAVES

For a Slave whose sole job was backup and simple data redundancy, using less optimization is fine but can come at a premium. How so ?

If you have Master that has high write volume, this can result in MySQL Replication having lots of Replication Lag (Seconds_Behind_Master > 0 and increasing). This can occur when a Master has 20 INSERTs, UPDATEs, and DELETEs happening in parallel. Those 20 commands are serialized in the Master's binary logs and are serially processed by the SQL thread on the Slave. If you scale down the hardware and MySQL config settings on the Slave too much, that Replication Log can get worse than the aforementioned serialization of updates.

CONCLUSION

The volume of writes on the Master should be inversely proportional to scale down of the Slave:

  • the higher the volume of writes on the Master
    • the less the scale down of the Slave
    • the lower the tolerance for Replication Lag (preferably none)
  • the lower the volume of writes on the Master
    • the more the scale down of the Slave
    • the higher the tolerance for Replication Lag (expected to be infrequent)

CAVEAT

In this context, scale down means

UPDATE 2015-02-02 12:53 EST

Based on the question in your comment about writes, you can increase the following on the Slave

| improve this answer | | | | |
  • Thanks Rolando. You are always very helpful and knowledgable. The server we have is very powerful - we sort of upgraded and the old master became the slave. So your answer makes sense - we can reduce buffer sizes - but is there something we can do with that saved buffer space that would somehow make it more performant for writes? Probably, not, I assume that writes are entirely dependent on the CPU power and disk speed of the machine and little else. Thanks again. – dgig Feb 2 '15 at 17:44
  • Updated my answer to answer the comment's question. – RolandoMySQLDBA Feb 2 '15 at 17:55
  • Ah! that's great about the write threads. Exactly what I was looking for. Thanks Rolando. – dgig Feb 2 '15 at 20:25

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.