NoSQL到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