Compare FlyData and DMS as options

[Tuuleh]

Thinking about affordable, reliable data migrations

I'm currently working with two tools to try and perform full load and ongoing replication onto Redshift.

AWS DMS

• Much less expensive than FlyData as an option - I dropped and recreated all the tables several times last month and that + running the replication cost $64.74 (charges would be lower if dropping and reloading the tables hadn't been needed). There is an additional cost from the replication instance, but it's still nothing compared to FlyData.
• Allows some operations on the data to be imported (e.g. truncating long text fields), which allows us to customize the tables so they might work better for queries
• I'm still doing back and forth with AWS customer support to figure out the issues that are going on. I'd also like to spend a bit more time debugging this with customer support, because
  • DMS enables some more transformations on the incoming data -> we can optimize the long text columns
  • using DMS doesn't bind us to a new third party service provider (we're already on Amazon)
  • we can use DMS for more sources than just the AK database - for example, if we'll want to write data into a different schema from the 'email a target' tool Omar is working on right now, we could just set up a DMS task.

FlyData

• A third party service that replicates using binary logs similarly to DMS
• Seems to just work without any issues with a quick setup
• Would get very expensive due to all the writes we do to core_usermailing - in December, we wrote 75 million rows and in January, 54 million. The plan for 75 million writes per costs $1,163 per month if payed monthly and $874 on an annual plan.
  • If we instead used a different tool for migrating core_usermailing (e.g. periodic script / DMS), we'd only have a fraction of the writes as the next most active table, core_open, gets only about 0.15 of the writes that core_usermailing does. Imagining that we would then be at up to 15 million writes per month, we would still be at $398 per month if payed monthly and $200 on an annual plan.
• Doesn't really support transformations on the data to be imported - so we can't really optimize the data types on Redshift to work better for queries (e.g. value fields in core_actionfield and core_userfield will be max length varchar fields)
  • This might actually be a deal breaker! Redshift does not perform well with queries over long text columns. We might want to consider whether it makes sense to have an expensive Redshift instance that might not work any better than a well indexed MySQL / Postgres db.

[paulaferris]

This might actually be a deal breaker! Redshift does not perform well with queries over long text columns. We might want to consider whether it makes sense to have an expensive Redshift instance that might not work any better than a well indexed MySQL / Postgres db.

How do MoveOn and/or other organizations handle this?

[Tuuleh]

Good question, Paul. I think it might be that there are just not that many queries that need the big text columns, so it might not be an issue. For core_mailing, I know that MoveOn has actually dropped them altogether and doesn't import them (I've opted to do the same after consulting with Tara and Simona). They also have some custom ETL scripts and don't run everything through FlyData so they might be truncating some columns. They haven't been super responsive in replying to my emails lately, but I might try and bug them a bit to see if someone would be willing to hop on a call with me.

[tara-m-harwood]

Thanks for all this work Tuuli!

Just a note re: the long text fields in core_actionfield ... the long ones are all URLs for the 'referrer' value, where we store the URL that the action-taker came from. While we do peek at that occasionally, we use this data so rarely that I'd be fine dropping it altogether. AFAIK we could actually scrub all of that data from AK and stop Champaign from writing more of it without tripping up any data analysis. If truncating the long fields is the sticking point, we can prob just get rid of them. Sounds like cost is a big factor too though.

What is the max character you are truncating to in DMS? I can run some queries on AK to see how much data we have that runs over that limit. Thanks again!

[rodrei]

If we instead used a different tool for migrating core_usermailing (e.g. periodic script / DMS), we'd > only have a fraction of the writes as the next most active table, core_open, gets only about 0.15 of > the writes that core_usermailing does. Imagining that we would then be at up to 15 million writes > per month, we would still be at $398 per month if payed monthly and $200 on an annual plan.

If DMS doesn't work, this sounds very reasonable ☝️

[Tuuleh]

I'm copy pasting from the email I just sent to keep this conversation documented under the right issue:

During the weekend, I experimented a bit with a setup where I'm migrating all other tables with FlyData, and core_usermailing with DMS. I've verified today that the setup works fine and is running right now - you can look at the Redshift instance called flydata-cluster. The difference is that DMS replicates on the go, as changes happen, and FlyData replicates every five minutes - so you might see a small delay in data coming in to tables other than core_usermailing.

Again to re-iterate - the reason we're using FlyData for the other tables and DMS for core_usermailing is that this significantly reduces the costs we'd incur using FlyData alone. Writing all our data through FlyData, we'd be at up to 75 million rows, monthly, which costs $1,163/mo. Without the writes we enter core_usermailing, we should just be able to fit into the 15 million rows monthly plan (with an overage fee up to 30 million rows), which costs $398/month. We have three days left on the free trial, after which we can try to negotiate a non profit discount - MoveOn.org uses FlyData with a 50% non profit discount.

The downside of using FlyData is that there is less customizability on the Redshift tables and so query performance might not be as great for Simona and Tara. I will be in touch with a customer rep, but so far I haven't found a way of specifying which columns we want to move over, and there are no major transformations I can perform on the data I'm moving, other than some very simple value replacement (basically replacing values that match x with y, which isn't useful for us right now). I will need to add sort and distkeys manually by dropping the tables again but that won't be such a big hurdle now that I have the create table statements ready (possibly a day or so of work if there are no issues - I'll just need to change all the type declarations and add all the columns in my script... sigh).

I'm still having a call with AWS customer support today to see if we can fix the issue where I cannot run multiple tasks off of the same source simultaneously. But so far this current setup looks to be working all right on first glance.

I promised to do some budgeting, so here goes:

• FlyData replication for our currently most wanted tables: $398/month (with a possibility of an up to 50% non profit discount)
• AWS t2.medium replication instance for core_usermailing: $52.26/month
• All data transfer into AWS Database Migration Service is free, and data transferred between AWS Database Migration Service and databases in Amazon RDS and Amazon EC2 Instances in the same Availability Zone also is free.

Therefore, the current setup would cost max. $450.26 monthly, and if we got the 50% FlyData discount, it'd cost around $250.