1

I've asked the same question on the MySQL forum but have also seen lots of great responses here as well so thought I would try my luck here too.

We have a relatively large MySQL environment where we are currently using MySQL Master-Slave replication to achieve our performance requirements. We typically have multiple clusters of 10 hosts, where one is read-write and the others are read-only. We are also very performance sensitive so are currently using FusionIO cards in each host.

We are finding a few challenges with this architecture however, and are looking for other ideas - specifically though we would like to come back to a very high speed shared storage solution that would allow us to have a single copy of the DB, where the master can mount it as read-write and the slaves as read-only.

I understand that by using Clusterware this type of design is possible, however we would prefer to not to use that product as we are okay to use some manual failover processes if need be.

Has anyone ever done anything like this before? I know that MySQL supports mounting as read only, "--innodb-read-only=1", however what other implications are there if using this method compared to their existing Master-Slave configuration? Is it possible to have one as read-write and the others read-only if using this parameter?

Thank you in advance for any thoughts on this.

Improve this question

1 Answer 1

Reset to default
0

NO. Do not attempt to have a single copy of the data with multiple copies of mysqld accessing it. I don't know if that ever worked; if it did, I suspect it has atrophied over the years as changes have been made.

Think about it... Each copy of the server has lots of stuff cached in RAM. To coordinate that with other servers would require writing things to disk; this defeats the performance goal. When a write occurs; all traces of it would need to be immediately flushed to disk, and all the other servers would need to know to read it and replace whatever is cached in their RAMs.

With FusionIO, check to see that you can safely turn off innodb_doublewrite.

How many queries/second? Do you have any 'slow' queries? (That is, there are other ways to beef up performance, than just hardware.)

Improve this answer
1
  • Thank you Rick, this combined with a few other of yours and others excellent posts I think we can finally put this to bed. Our team has had one other idea to use a stale copy of the database for those read-only nodes, but we're not sure if this will work so are checking with the application owners.
    – Matt Hobbs
    Commented Apr 12, 2016 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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