NoSQL到PostgreSQL-迁移经历

考虑迁移到PostgreSQL?这里有一个机会学习所有有趣的冒险,我们把我们的服务从rethinkdb迁移到postgresql。
从物化视图刷新,到系统视图查询,再到撕破的页面分析,在第一次迁移过程中从来没有一个沉闷的时刻!

展开查看详情

1.NoSQL to PostgreSQL Adventures in Migrations Phil Hildebrand Moz

2.Where we’ll be going 1. A little background of how we got here 2. Look at the few things we did right along the way in our adventure 3. Dive into a few of the fun things we learned (some the hard way) 4. Talk a little bit about what’s next and what we’ll do different

3.How’d we get here? A desire About for title the best thing for the job led to above ● Location and customer reviews in json documents ● NodeJS, RethinkDB, MySQL, SortDB, Elastic Search ...the ever expanding technology stack

4.How’d we get here? A need for simplicity drove About title above ● PostgreSQL for JSON, Transactions, and Materialized views ● Easier integration for business analytics ● Ease for onboarding new devs ...reduce technology stack

5.Eyes wide open? PostgreSQL is not Oracle, and it’s certainly not MySQL ● Command line client and the art of backslashology About title above

6.Eyes wide open? PostgreSQL is not Oracle, and it’s certainly not MySQL ● Command line client and the art of backslashology ● Reviewing the core architecture About title above

7.Eyes wide open? PostgreSQL is not Oracle, and it’s certainly not MySQL ● Command line client and the art of backslashology ● Reviewing the core architecture About title above ● Configuration Options (PGTune by Alexey Vasiliev)

8.Eyes wide open? PostgreSQL is not Oracle, and it’s certainly not MySQL ● Command line client and the art of backslashology ● Reviewing the core architecture About title above ● Configuration Options (PGTune by Alexey Vasiliev) ● PG_ <underscore> system view foo ● Inheritance? That’s a thing? https://gist.github.com/phil-hildebrand/c59f9739bbd745f70b6c1e513931873b

9.Eyes wide open? PostgreSQL is not Oracle, and it’s certainly not MySQL ● Reviewing the core architecture ● Command line client and the art of backslashology About title above ● PG_ <underscore> system view foo ● Inheritance? That’s a thing? But it’s still a relational database ● Memory allocations ● Fragmentation ● Archive logging ● Replication

10. Outage #1: Materialized View Refresh Historical sets of Reviews - Partitioned by year About title above - Accessed by materialized views - New reviews trickle in nightly - Key functionality depends on a single materialized view

11.Outage #2: Out of Space - 3X wal log space?

12.Outage #2: Out of Space - 3X wal log space? - It’s only 400 tps at peak - About 150ktitle above tps/day (over an 8 hour period) - 200GB / day - 1.5 k / transaction.

13.Outage #3: Torn Page Corruption? But it’s a database... - I Love active forums!!!! About title above

14.Outage #3: Torn Page Corruption? But it’s a database... - I Love active forums!!!! - Special call out to these four forum members About title aboveinto the muck with us: that jumped - Alvaro Herrera - Andrew Gierth - Michael Paquier - Stephen Frost

15.Outage #3: Torn Page Corruption? But it’s a database... - I Love active forums!!!! - Replication didn’t help About - title Finallyabove got to use those backups

16.Outage #3: Torn Page - Validation Walk Through About title above

17.If we did it all over again... What was helpful: - Read the Docs - Creating pg_* cheat sheets About - title aboveinstallation, configuration, replication Automating - Command client cheat sheets - Migrating smaller/simpler data sets first What was maybe not so helpful: - Skipping load tests - Waiting to get involved till migration time

18.Next Up... Migrating the next piece into postgres - Chose a MySQL based service to migrate - title About Much above better understanding of replication and materialized views - Looking into changing the wal segment size - Easier do to all the automation we now have in place First new service with GIS features: - Postgres 11 - More load testing up front

19. Helpful Resources PostgreSQL Docs: https://www.postgresql.org/docs/current/ Percona PostgreSQL Blog: https://www.percona.com/blog/category/postgresql/ Several Ninestitle About Become a PostgreSQL DBA Blog: https://severalnines.com/blog?series=690 above PG Tune: https://pgtune.leopard.in.ua/#/ PG Bench: https://www.postgresql.org/docs/current/pgbench.html DBLoader: https://github.com/phil-hildebrand/dbloader Gist Queries: https://gist.github.com/phil-hildebrand/c59f9739bbd745f70b6c1e513931873b Gist Page Inspection: https://gist.github.com/phil-hildebrand/27e2a5029f1bca725eea27f995edde20 Forum Issue: https://www.postgresql.org/message-id/flat/15570-d920421b445027cc%40postgresql.org

20.Questions ? phil.hildebrand@moz.com

21.Rate My Session 21