1

I have the following topology

City 1                                                City 2
Master <----> Distribution Master (EC2 Instance) <---> Slave
  • Master : Write heavy
  • Distribution Master: Read only
  • Slave: Read only

There won't be any application in front of Distribution Master.

Distribution Master is slave of Master and Master of SlaveServer . Master and Slave are in different cities and the replication needs to be Asynchronous.

The EC2 instance just gets the data from Master and replicates to the Slave when a connection is established.

  • Do you think I need SSD volumes for the Replication Master?
  • Or will a cold HDD (sc1) will be enough?

With a cold storage solution I can get more space for a much lower price.

closed as primarily opinion-based by LowlyDBA, mustaccio, jcolebrand Jan 4 '17 at 0:17

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Hi Renko, welcome to the site. Unfortunately this question boils down to "what performance do you need?" and we really can't help with that. My advice is to profile and determine what your needs are, then use that to base your decision off it. If you're moving GBs of data or less, then you probably are fine with cold storage. – jcolebrand Jan 4 '17 at 0:17
  • Sorry, I know this is off-topic, but before my thread get closed. Where can i make questions of this kind? I think Quora is too general purpose. – RenkoSmith Jan 4 '17 at 13:47
  • Yeah, in general it is too general purpose for this sort of thing. Honestly my experience in this case says you probably want ServerFault, because you're mostly asking about how to setup/configure a VM for best perf at lowest price, but they would probably scoff at that too. In your case the ideal thing to do is to gain a little more rep and join us in the chat rooms for this sort of question. Gain rep by asking great questions and providing great answers! – jcolebrand Jan 4 '17 at 15:59
2

Your "distribution master" -- assuming it is running MySQL Server, not a specialized program specifically for directly replicating binlogs -- cannot "just get data from Master and replicate to the Slave when gets connection" and relay the incoming events to downstream replicas. It will also be keeping a copy of the entire data set and processing the replication events, keeping its local copy in sync before sending the events downstream.

It will, therefore, need to have as much I/O capacity as needed in order to keep up with the master.

The question of whether sc1 Cold Storage (mechanical) HDD EBS volumes offer sufficient capacity in both write throughput and IOPS is not a question that can be answered in general.

The answer depends entirely on your workload.

There is nothing in sc1 that is intrinsically incompatible with MySQL, but a 500 GiB sc1 volume has a baseline throughput of only 6 MiB/s and a burst throughput of 40 MiB/s. There is a reason it is marketed as "cold storage," and a reason it is inexpensive.

An st1 Optimized Throughput (also mechanical) HDD volume has > 3x the performance for < 2x the price of sc1 so is a somewhat safer bet. I have recently begun testing st1 volumes for binary log and relay log storage on some non-critical replicas -- with the datadir still on gp2 SSD volumes -- and have not been seeing any replication lag nor taxing of these volumes according to the CloudWatch metrics nor significant iowait... but binary logs and relay logs are, of course, largely sequential write and read, which puts them within the design parameters of both st1 and sc1.

The bottom line, though, is that your experience will be highly correlated to your workload.

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html


Remember also that if you create a volume of any storage class from an EBS snapshot (such as when you are testing different storage classes -- a snapshot made from one type of volume can be used to create a volume of a different type) that you will see degraded performance until the volume is warmed up.

Assuming the volume is /dev/xvdf, this might look like dd if=/dev/xvdf obs=1M | pv -pterab > /dev/null ... followed by a few hours of waiting. Don't try to optimize a warmup with fio or a large block sizes on smaller instances -- you'll DoS your own instance if you hit a stretch of already warmed-up blocks and the volume's read throughput spikes significantly above the available network bandwidth on the instance's Ethernet interface. If this happens, the instance will fail its health checks.

  • sqlbolt Thank you very much for your answer. I'm newbie on DBA world, so my questions are very basic. There wont be any application in front of Replication master, so it works is get and send data to one or two slaves servers. All the work will made in Master (Write heavy) and Slave (Read only), the distribution master will be read only also. – RenkoSmith Jan 4 '17 at 13:45

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