Skip to content

Tracking issue: support Amazon RDS #163

Closed

Description

@shlomi-noach

EDIT: Documentation for RDS is available

This is a tracking issue to discuss all things RDS and what it takes to be able to run gh-ost on RDS.

Activity

  1. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    ContributorAuthor

    First limitation is the SUPER privilege. This is defined in #132 and addressed in #156

    When you know you are using RBR (Row Based Replication), you can specify --assume-rbr and this will skip the step of STOP SLAVE; START SLAVE which requires SUPER privilege.

  2. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    ContributorAuthor

    I should note that I am not using RDS at the moment; will need community feedback for production issues.

  3. pbitty commented on Aug 18, 2016

    @pbitty
    Contributor

    I am using RDS and have been trying out gh-ost with success so far. I am happy to give feedback and contribute where possible.

    I've succeeded in running a test with --test-on-replica (using the workaround described in #162) and table checksums matched. And I've done a migration with --migrate-on-replica and did not have any significant issues.

  4. pbitty commented on Aug 18, 2016

    @pbitty
    Contributor

    As far as I can tell, the only other blocker is the STOP SLAVE command that is issued in --test-on-replica mode (to be solved via #162).

    In RDS, it is possible to start/stop replication with stored procedures they provide, ie: CALL mysql.rds_stop_replication. So we could support automatic replication stop/start.

    The only other thing that is not supported (that I can tell) is setting global variables, so we wouldn't be able to support auto-configuring RBR from mysql itself. It would be possible to do it via the RDS API, but there are non-trivial concerns since multiple RDS instances can share the same configuration, so setting binlog_format=ROW on a shared configuration could change it on multiple RDS instances at the same time. This could be left as a manual step and included in a doc, or a plugin from #62.

  5. shlomi-noach commented on Aug 18, 2016

    @shlomi-noach
    ContributorAuthor

    Thank you! You are now assigned the role of Chief RDS Officer at Gh-Ost Corp.

    Changing a statement replica to row can certainly be delegated to the user, no need to trouble gh-ost on this if this is an issue.

  6. pbitty commented on Aug 19, 2016

    @pbitty
    Contributor

    Ha, thanks!

    I am working on documenting the steps required for using gh-ost in our RDS environment. I should be able to make it generic enough to help others. When I do, I can submit a PR if you are interested. Hopefully we can get other RDS users to provide some feedback.

  7. shlomi-noach commented on Aug 31, 2016

    @shlomi-noach
    ContributorAuthor

    Hooks have been merged, though not yet released.

  8. pbitty commented on Aug 31, 2016

    @pbitty
    Contributor

    Great! I will try them out with our next --test-on-replica run either today or tomorrow.

  9. pbitty commented on Sep 1, 2016

    @pbitty
    Contributor

    The gh-ost-on-stop-replication worked like a charm. I was able to stop replication the RDS way before continuing with cut-over, using the hook along with --test-on-replica and --test-on-replica-skip-replica-stop.

  10. shlomi-noach commented on Sep 5, 2016

    @shlomi-noach
    ContributorAuthor

    At this time (v.1.0.17), the way to run gh-ost on RDS is as follows:

    Real migration

    • You need to make sure replication is RBR. gh-ost has no privilege to set binlog_format=ROW for you.
    • RDS will not honor the parameter group config for the binlog unless you enable automated backups on the read replica (for at least 1 day).
    • Execute gh-ost --assume-rbr ...
      This will make gh-ost skip the stop slave; start slave step, which it does not have privileges for.
    • 🍕

    Test on replica

    In addition to the above:

    • gh-ost --assume-rbr --test-on-replica --test-on-replica-skip-replica-stop
      The --test-on-replica-skip-replica-stop avoids issuing a stop slave at the end of migration
    • However replication must be stopped. Using hooks you need to supply gh-ost-on-stop-replication hook -- where you will invoke your own RDS script to stop replication
    • You must make read_only=0 on the replica's Parameter Group. By default read_only={TrueIfReplica} and therefore enabled
    • 🍕

    Once the above is tested more by the community, I will put this in documentation

  11. pbitty commented on Sep 20, 2016

    @pbitty
    Contributor

    This is the hook we use.

    At /hooks/gh-ost-on-stop-replication-rds-command.sh:

    #!/usr/bin/env bash
    
    statement="call mysql.rds_stop_replication"
    echo "Issuing statement to stop replication: ${statement}" 1>&2
    exec mysql -h $DB_HOST -u $DB_USER -p$DB_PASSWORD -e "${statement}"

    where --hooks-path=/hooks.

    The DB_... env vars are set when running the gh-ost process, and then passed by gh-ost onto the hook process.

  12. pbitty commented on Sep 20, 2016

    @pbitty
    Contributor

    This is the minimum privilege required for that:

    GRANT EXECUTE ON PROCEDURE mysql.rds_stop_replication to '<user>'@'<host_or_wildcard>';
  13. shlomi-noach commented on Sep 21, 2016

    @shlomi-noach
    ContributorAuthor

    @pbitty thank you for this info!

    To clarify, in: GRANT EXECUTE ON PROCEDURE mysql.rds_stop_replication to '<your_ghost_user>'@... you don't actually need the user to be the gh-ost user you use for running the migration. This was your personal choice.

  14. pbitty commented on Sep 22, 2016

    @pbitty
    Contributor

    Good point. I've updated the comment above.

  15. 25 remaining items

  16. dgtized commented on Jan 24, 2017

    @dgtized

    I've run several migrations on RDS MySQL using the directions above and had no problems.

  17. kamaljoshi commented on Feb 16, 2017

    @kamaljoshi

    I ran around 7-8 migrations on MySQL in December using this. Everything seemed to have worked well.
    Some minor changes I had to do with gh-ost were related to making --assume-master-host work without Tungsten. As RDS replica was using internal IPs(172.17.X.X) to communicate with master and the machine we were running gh-ost on couldn't access that, I had to manually pass the master hostname.

  18. jacobbednarz commented on Mar 10, 2017

    @jacobbednarz
    Contributor

    I've been testing this for the last couple of months using RDS and aurora as the engine. The steps here related to RDS are spot on 👍 , the only differing pain point I've found is that the aurora parameter group doesn't allow modifying read_only. It still uses the same value of {TrueIfReplica} however the aurora parameters are marked as a non-modifiable field which prevents connecting to replica but running on the master.

    We're looking into a couple of work arounds for this and I'll report back if we get anything worth while but for now, best to just be aware you'll need to run it on the master.

  19. pbitty commented on Mar 10, 2017

    @pbitty
    Contributor

    @jacobbednarz That's great to hear.

    From what I understand of Aurora, all nodes share the same underlying storage layer, so only one node is allowed to write to it. You may be able to get around this by replicating to another Aurora cluster. From gh-ost's point of view, it would still be connecting to a "master", but this master would be on a 2nd Aurora cluster (that is receiving binlog updates from the 1st one).

    I haven't tried this, just theorizing. :)

  20. jacobbednarz commented on Mar 13, 2017

    @jacobbednarz
    Contributor

    You may be able to get around this by replicating to another Aurora cluster.

    This is a possibility however I don't believe it solves the issue of running on a replica but migrating the master (which is the intention here). Your comment further down explains why this is the case and right now I can't see a viable solution.

    The best you can expect from RDS on Aurora at the moment is that you can run it on the cluster but only directly on the master. If you want to test, you need to stand up additional clusters - probably based on a recent snapshot.

  21. HasMikeW commented on Mar 20, 2017

    @HasMikeW

    I migrated a 1.5 billion record table on a highly concurrent system in Aurora. It took a couple attempts, because the inserts would slow even with no constraints added to the base command. We had to scale back some of the load on the server to be able to complete the task in a timely manner.

  22. shlomi-noach commented on Mar 20, 2017

    @shlomi-noach
    ContributorAuthor

    @HasMikeW which version of gh-ost were you using? More specifically, does your version support --dml-batch-size? Introduction of this functionality (indicated by the existence of this flag) significantly improved write throughput. Generally speaking I prefer to just have some sane configuration and not try to tune too much, but if you suffer from low throughput you may try a higher value for that flag (default: 10, try perhaps 50 or 100).

  23. HasMikeW commented on Mar 20, 2017

    @HasMikeW

    @shlomi-noach The version is 1.0.32 I had looked into using the --dml-batch-size but was unable to get it to work with this version. I didn't want to go back and have to run more tests that a new version of gh-ost would have dictated at that point. That was the next step if reducing the load did not work :-)

  24. jacobbednarz commented on Mar 23, 2017

    @jacobbednarz
    Contributor

    Just an update to my comment above - I've managed to find a nice workaround for testing/migrating on a replica in AWS RDS using aurora as the engine.

    @pbitty was pretty close to the money however you don't use Aurora replication, you need to use standard MySQL-style replication. The AWS documentation page does an OK-ish job at explaining this but it's probably better to follow non-AWS docs and just setup replication as if you would elsewhere using a separate aurora cluster in the same region.

  25. shlomi-noach commented on Mar 23, 2017

    @shlomi-noach
    ContributorAuthor

    @jacobbednarz do you think it would be good to write some documentation for Aurora users, and if so, would you be able and willing to write it?

  26. jacobbednarz commented on Mar 23, 2017

    @jacobbednarz
    Contributor

    It would definitely be worth while! I'm happy to write it (since I've already got a bunch of notes from our internal documentation that would help here too).

  27. jacobbednarz commented on Mar 24, 2017

    @jacobbednarz
    Contributor

    I've opened a PR to add in AWS RDS docs at #385 for anyone following along at home.

  28. jacobbednarz commented on Apr 10, 2017

    @jacobbednarz
    Contributor

    @shlomi-noach I think everything in this issue has been addressed either via docs or changes. We've also managed to run ~20 successful test + production migrations on RDS so I'm happy most of the annoyances are out of the way. Would it be time to close this off and expect new issues to be in a thread of their own?

  29. shlomi-noach commented on May 24, 2017

    @shlomi-noach
    ContributorAuthor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions