Oracle to Azure PostgreSQL Lessons learnt

Transcript

1. Oracle to Azure PostgreSQL Lessons learnt Alicja Kucharczyk EMEA Global Black Belt OSS Data Tech Specialist alicja.kucharczyk@microsoft.com
2. Migration Tooling Schema and Data migration
3. Installation • Use prebuild ora2pg (containers) wherever possible Simple workshop Fancy one • Use a separate VM for ora2pg
4. Ora2pg config TRANSACTION committed PREFIX_PARTITION 1 WHERE ROWNUM < 100 -> to validate the schema with data
5. Partitioning SELECT table_name, partitioning_type, partition_count FROM all_part_tables WHERE OWNER = ‚OWNER’ ORDER BY 1, 2;
6. Partitioning You do NOT need bunch of custom procedures, use stable and proven one – pg_partman
7. CREATE EXTENSION pg_partman WITH SCHEMA partman; SELECT partman.create_parent('github.events', 'created_at', 'native', 'hourly'); UPDATE partman.part_config SET retention_keep_table = false, retention = '1 month' WHERE parent_table = 'github.events';
8. Basic fixes ✓ Convert varchar2(255), varchar2(x) to TEXT ✓ NUMBER(1) or CHAR(1) to BOOLEAN ✓ JSONB?
9. Not only plpgsql CREATE FUNCTION foo(out f2 text) AS $$ SELECT 'BAR' $$ LANGUAGE SQL; https://www.postgresql.org/docs/current/sql-createfunction.html
10. Data migration # PERFORMANCES SECTION (Control export/import performances) JOBS 1 ORACLE_COPIES1 PARALLEL_TABLES 1 DEFAULT_PARALLELISM_DEGREE 0 PARALLEL_MIN_ROWS 100000 # DATA SECTION (Control data export behaviors) DATA_LIMIT 10000
11. After data migration NEVER FORGET VACUUM ANALYZE!
12. Performance Tuning  Schema and Data migration
13. Tuning cycle Gathering text logs Generate pgBadger report Tune Top queries App test run
14. Configuration log_min_duration_statement = 0 log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ' logging_collector = on log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 log_error_verbosity = default Rotate the log: SELECT pg_rotate_logfile();
15. Server Logs
16. Easier than clicking az postgres server-logs list -g [resource_group] -s [server-name] --query '[].{Name:name}' -o tsv |xargs -I {} az postgres server-logs download -g [resource_group] -s [server-name] -n {}
17. pgBadger
19. Database Only tuning • Storage tuning (indexes, fillfactor, statistics etc.) • GUC (Grand Unified Configuration) Tuning using GUC-es is NOT the best way of tuning and should not be the chosen method. It was used because of time constraints and no-code changes. Allows to showcase that expected performance level can be achieved (PoC). *https://www.postgresql.eu/events/pgconfeu2017/sessions/sessio n/1617/slides/9/FromMinutesToMilliseconds.pdf
20. Example Query 01 Changing a GUC join_collapse_limit to 1 with the following command: SET JOIN_COLLAPSE_LIMIT TO 1; Reduced the time of query execution from 682715.083 ms to 1217.904 ms (43% of initial value).
21. Example Query 02 Adding the following index: CREATE INDEX ON table(column1, column2 DESC); Reduced the time of query execution from 2 373 ms to 905 ms (38% of initial value).
22. Conection Pooling?
23. © Microsoft Corporation • https://github.com/Hrashid789/ora2pgWorkshop • https://github.com/AwdotiaRomanowna/ora2pgWorkshop • https://github.com/microsoft/MCW-Migrating-Oracle-to-Azure-SQL-and-PostgreSQL DIY
24. Thank you!