4 ms·

If you are using MySQL I also recommend Percona's Online Schema Change [1] which is basically a robust implementation of this post's algorithm, handles more edg

If you are using MySQL I also recommend Percona's Online Schema Change [1] which is basically a robust implementation of this post's algorithm, handles more edge-cases, has been existing for at least 10 years now and works better out of the box with AWS RDS than Gh-ost. (if you have production experience on 100s of millions rows tables with Gh-ost on RDS please share your experience/tricks, I'd love to hear them!) [1] https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...

boudin 5y ago
I have been using gh-ost with RDS for years no with no issues. What problems did you have?
- darkwater 5y ago
  Actually I never tried but I was scared by the small print of GH not using RDS themselves [1] and Ghost relying on lower-level features that might be not easily available in RDS. Also I had the impression you have to setup a normal non-RDS replica attached to your RDS master? [1] https://github.com/github/gh-ost/blob/master/doc/rds.md https://github.com/github/gh-ost/blob/master/doc/rds.md
  - WJW 5y ago
    At my previous employer we've run it just fine from an EC2 instance talking to a MySQL RDS database. At some point the triggers from pt-osc were really hurting when trying to migrate big (several billion rows) table with heavy traffic and gh-ost really helped with that. That said, gh-ost is definitely a big boy tool and it took us a few tries to get the settings just right. No downtime or anything, we just had to delete the new table and start over. Still annoying when it wastes several hours :)
  - boudin 5y ago
    It works with a standard RDS (not aurora) replica. There are a few constraints when using gh-ost but it's all documented.
    - earless1 5y ago
      It also works fine with Aurora, as long as you have binary logging enabled.
      - evanelias 5y ago
        That's true. However, if you don't already have any "traditional" (binlog-based) replicas on Aurora, you might not even need gh-ost or pt-osc in the first place -- at least for many common ALTER operations. Aurora supports physical replication at the storage level, which means you can use MySQL online DDL (ALGORITHM=INPLACE, LOCK=NONE) without having to worry about replication lag. Recent blog post with findings from Percona: https://www.percona.com/blog/zero-impact-on-index-creation-with-amazon-aurora-3/ https://www.percona.com/blog/zero-impact-on-index-creation-w... Caveats: * This is safe on Aurora 3 due to MySQL 8's atomic DDL support. On previous versions, there may be crash-safety risks, which make external tools safer for altering very large tables. * External OSC tools can provide other advantages, such as throttling, safer to cancel, ability to time the final table switchover, ability to rollback (pt-osc supports "reverse" triggers), etc. * Some forms of ALTER TABLE don't support online DDL, and for those you'd still need an external OSC tool like gh-ost or pt-osc, but it's less common.
  - grepfru_it 5y ago
    GH no longer uses RDS. They had some services which relied on that maybe 3 years ago. Migrations were performed with gh-ost, but if not running replicas (RDS was not) it would downgrade to mysql binaries GH is migrating from gh-ost in general, as it’s not even used in the enterprise products (though GitHub.com relies on it, the next iteration will phase it out)
ransom1538 5y ago
^ Yes. Percona's Online Schema Change is the best tool on the market. Replication checking, setup with triggers, online, etc.

boudin 5y ago

I have been using gh-ost with RDS for years no with no issues. What problems did you have?

darkwater 5y ago
Actually I never tried but I was scared by the small print of GH not using RDS themselves [1] and Ghost relying on lower-level features that might be not easily available in RDS. Also I had the impression you have to setup a normal non-RDS replica attached to your RDS master? [1] https://github.com/github/gh-ost/blob/master/doc/rds.md https://github.com/github/gh-ost/blob/master/doc/rds.md
- WJW 5y ago
  At my previous employer we've run it just fine from an EC2 instance talking to a MySQL RDS database. At some point the triggers from pt-osc were really hurting when trying to migrate big (several billion rows) table with heavy traffic and gh-ost really helped with that. That said, gh-ost is definitely a big boy tool and it took us a few tries to get the settings just right. No downtime or anything, we just had to delete the new table and start over. Still annoying when it wastes several hours :)
- boudin 5y ago
  It works with a standard RDS (not aurora) replica. There are a few constraints when using gh-ost but it's all documented.
  - earless1 5y ago
    It also works fine with Aurora, as long as you have binary logging enabled.
    - evanelias 5y ago
      That's true. However, if you don't already have any "traditional" (binlog-based) replicas on Aurora, you might not even need gh-ost or pt-osc in the first place -- at least for many common ALTER operations. Aurora supports physical replication at the storage level, which means you can use MySQL online DDL (ALGORITHM=INPLACE, LOCK=NONE) without having to worry about replication lag. Recent blog post with findings from Percona: https://www.percona.com/blog/zero-impact-on-index-creation-with-amazon-aurora-3/ https://www.percona.com/blog/zero-impact-on-index-creation-w... Caveats: * This is safe on Aurora 3 due to MySQL 8's atomic DDL support. On previous versions, there may be crash-safety risks, which make external tools safer for altering very large tables. * External OSC tools can provide other advantages, such as throttling, safer to cancel, ability to time the final table switchover, ability to rollback (pt-osc supports "reverse" triggers), etc. * Some forms of ALTER TABLE don't support online DDL, and for those you'd still need an external OSC tool like gh-ost or pt-osc, but it's less common.
- grepfru_it 5y ago
  GH no longer uses RDS. They had some services which relied on that maybe 3 years ago. Migrations were performed with gh-ost, but if not running replicas (RDS was not) it would downgrade to mysql binaries GH is migrating from gh-ost in general, as it’s not even used in the enterprise products (though GitHub.com relies on it, the next iteration will phase it out)

ransom1538 5y ago

^ Yes. Percona's Online Schema Change is the best tool on the market. Replication checking, setup with triggers, online, etc.