1 / 40

Сергей Щукин shchukin@devexperts.com

Сергей Щукин shchukin@devexperts.com. EXADATA: deployment story. Сергей Щукин shchukin@devexperts.com. Agenda Introduction Our Exadata First Issues Preparation for the Migration Migration Performance Comparison Issues Afterwards. Сергей Щукин shchukin@devexperts.com. Introduction

alijah
Télécharger la présentation

Сергей Щукин shchukin@devexperts.com

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Сергей Щукин shchukin@devexperts.com EXADATA: deployment story

  2. Сергей Щукин shchukin@devexperts.com • Agenda • Introduction • Our Exadata • First Issues • Preparation for the Migration • Migration • Performance Comparison • Issues Afterwards

  3. Сергей Щукин shchukin@devexperts.com • Introduction • Early history • Started as a storage subsystem – former name SAGE • Originally designed for very large databases • Fast data access: symbiosis of HW and SW • Increase the channel or decrease the data flow?

  4. Сергей Щукин shchukin@devexperts.com • Introduction • Main Exadata’s secret • Data Processing on Storage layer • Less data is transferred over the Network • Less resources used on DB servers

  5. Сергей Щукин shchukin@devexperts.com • Introduction • Versions’s history • V1 • Released in 2008 • HP h/w + Oracle s/w (Database Machine) • No Flash Cache • Data Warehouse oriented • V2 • Announced at OOW 2009 • Partnership between Sun and Oracle • Added 384GB of SSD + s/w changes • More than Data Warehouse • X2-2 • Announced at OOW 2010 • Same as V2 except CPU (2x6 Cores, V2 used 4 Cores) • X2-8 used per Unit: 8 CPU x 8Cores + 1TB memory, • OLTP or mixed workload oriented

  6. Сергей Щукин shchukin@devexperts.com • Introduction • Existing configurations

  7. Сергей Щукин shchukin@devexperts.com • Introduction • Existing configurations 3-D model links http://oracle.com.edgesuite.net/producttours/3d/exadata22/index.html http://oracle.com.edgesuite.net/producttours/3d/exadata28/index.html Physical parameters Connectivity

  8. Сергей Щукин shchukin@devexperts.com • Introduction • Existing configurations • DB server: Sun Fire X4170 M2

  9. Сергей Щукин shchukin@devexperts.com • Introduction: • Existing configurations • Storage server: Sun Fire X4270 M2

  10. Сергей Щукин shchukin@devexperts.com • Introduction • Exadata’s features • Offloading • Smart Scan • Column projection • Predicate filtering • Storage Indexes • Hybrid Columnar Compression (decompression) • Encryption/Decription • Datafile initialization • RMAN (db block change tracking) offload • Smart Flash Cache • Parallel Operations • Resource management

  11. Сергей Щукин shchukin@devexperts.com • Our Exadata • System’s description • Order processing system, Forex market • OLTP database + Reporting database • DB version: 10gR2 EE RAC, OS: OEL 5.5 x86_64 • Tpm - 1000 • DB Size – OLTP(1TB, logs per day: up to 70GB), Reporting (2TB) • Users online (up to 10 000) + orders issued 1000 per minute • Streams environment

  12. Сергей Щукин shchukin@devexperts.com • Our Exadata • Shipment details

  13. Сергей Щукин shchukin@devexperts.com • Our Exadata • Primary setup • We got pre-deploy network setup document from Oracle • System engineers prepared network according the document (address space, dns, etc) • Particular ports were dedicated on switches for Exadata • Exadata arrived and was plugged in • Checks

  14. Сергей Щукин shchukin@devexperts.com • Our Exadata • What we got • Software versions • 11.2.0.2 BP8 DB servers • 11.2.2.3.2 Cells • ASM layout • Local disks (LVM) • Precreated database with DBFS • OFA

  15. Сергей Щукин shchukin@devexperts.com • Our Exadata • What we got • ASM layout

  16. Сергей Щукин shchukin@devexperts.com • First issues • No place for copying backup • DBFS • Local LVM • NFS • Attach external storage

  17. Сергей Щукин shchukin@devexperts.com • First issues • Slow NFS • Was: 1 MB/sec • Fixed after putting Exadata and Storage server into the same vlan

  18. Сергей Щукин shchukin@devexperts.com • First issues • Restrictions in customization • Any changes in SW and HW(except switches) are prohibited

  19. Сергей Щукин shchukin@devexperts.com • Preparation for the Migration • Real Application Testing (RAT) • Restrictions • Streams are skipped • Direct path load of data from • external files • Flashback queries • Distributed transactions • Non SQL-based object access • Description • Capture & Replay • EE option • Playback from 11.1.0.6 • 9i & 10g Capture only. • Patch is required • Cost:11500 $ per processor • + 2530 support

  20. Сергей Щукин shchukin@devexperts.com • Preparation for the Migration • Real Application Testing (RAT) • Streams workload is not captured

  21. Сергей Щукин shchukin@devexperts.com • MOS 560977.1

  22. Сергей Щукин shchukin@devexperts.com • Preparation for the Migration • Capture (10.2.0.4.4 -> 11.2.0.2) • Get one-off patch 10239989 compatible with 10.2.0.4.4. Apply it • Activate capture feature on a source system (wrrenbl.sql) • Create filter (every time): • exec DBMS_WORKLOAD_CAPTURE.ADD_FILTER ( fname => 'filter_name', fattribute => 'USER', fvalue => user) • Start Capture: • exec DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'test_capture', dir => 'rat', duration => 1200, capture_sts => TRUE, sts_cap_interval => 300) • Export AWR: • exec dbms_workload_capture.export_awr (capture_id => ) • Useful views: DBA_WORKLOAD_CAPTURES DBA_WORKLOAD_FILTERS • Replay

  23. Сергей Щукин shchukin@devexperts.com • Preparation for the Migration • Replay • Copy files, Process them once • exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(capture_dir=> • 'RAT') • Restore consistent db copy, reset time • Initialize Replay • exec DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_replay', replay_dir => 'RAT'); • exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(!!!) • exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE); • Calibrate WRC clients • wrc mode=calibrate replaydir=/opt/oracle11g/rat • Connect WRC clients • wrc system/... mode=replay replaydir=/opt/oracle11g/rat • Start Replay • exec DBMS_WORKLOAD_REPLAY.start_replay; • Analyze diffs in reports • dbms_workload_capture.IMPORT_AWR • dbms_workload_repository.awr_diff_report_text

  24. Сергей Щукин shchukin@devexperts.com • Preparation for the Migration • Bug in Playback • Worked fine for Reporting DB (3 hours) • Better DB Time (lower: 855.3vs138.7) • Better CPU (faster, less cpu time: 11000sec vs 3600sec) • Better IO reads (sinlge block read 13ms vs 0.6ms, 6,5ms vs 1ms) • Worse IO write, especially db file parallel write (4,5ms vs 20ms) • Better interconnect • For Trading DB gave ora-00600 after 1 hour of working • ORA-00600: internal error code, arguments: [1433], [60], [], [], [], [], [], [], [], [], [], [] CKPT (ospid: 4327): terminating the instance due to error 469 • System state dump requested by (instance=1, osid=4327 (CKPT)), summary=[abnormal instance termination]. • Oracle made a patch after 2,5 months

  25. Сергей Щукин shchukin@devexperts.com • Migration • Official methods • Physical • Pros • Simple • Less downtime • Cons • Data structure can’t be changed (extent’s size, partitioning, HCC) • Unnecessary data migrates as well • Logical • Pros • One can change data structure • Old data can be skipped • More flexible in terms of changing the platform • Cons • More complicated • Involve more downtime

  26. Сергей Щукин shchukin@devexperts.com • Migration • Official methods • Physical • http://www.oracle.com/technetwork/database/features/ • availability/xmigration-11-133466.pdf

  27. Сергей Щукин shchukin@devexperts.com • Migration • Official methods • Logical

  28. Сергей Щукин shchukin@devexperts.com • Migration • Our method (10gR2 -> 11gR2) • Backup & Restore database • Manual applying of the archivelogs • Open in new DB with RESETLOGS and UPGRADE

  29. Сергей Щукин shchukin@devexperts.com • Migration • Difficulties • Streams • Test runs with rollback • 10gR2 standby in Amazon • Need to run utlu112i.sql on the Prod DB with the following rollback

  30. Сергей Щукин shchukin@devexperts.com Migration Changes made by utlu112i.sql Unsupported Unsupported set transaction read write; Unsupported Unsupported commit; Unsupported ALTER TABLE registry$database ADD (tz_version NUMBER); set transaction read write; Unsupported commit; update "SYS"."OBJ$" set "OBJ#" = '898', "DATAOBJ#" = '898', "TYPE#" "FLAGS" = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '2' where " set transaction read write; ALTER PUBLIC SYNONYM DBA_REGISTRY_DATABASE COMPILE; update "SYS"."SYN$" set "NODE" = NULL, "OWNER" = 'SYS', "NAME" = 'DB delete from "SYS"."DEPENDENCY$" where "D_OBJ#" = '7533' and "D_TIMES update "SYS"."OBJ$" set "OBJ#" = '7533', "DATAOBJ#" = NULL, "TYPE#" and "CTIME" = TO_DATE('09-AUG-11', 'DD-MON-RR') and "MTIME" = TO_DAT insert into "SYS"."DEPENDENCY$"("D_OBJ#","D_TIMESTAMP","ORDER#","P_O commit;

  31. Сергей Щукин shchukin@devexperts.com • Migration • Step-by-step for Rep database • Backup both databases (Trad & Rep)  • Restore Rep copy on Exadata (over NFS). Keep it synchronized • Stop applications • Disable synchronization with Standby • Build Streams catalog (to be able to rewind changes applied on new REP copy) • Create guaranteed restore point on Rep database • Execute utlu112i.sql on Rep db • Apply all logs from Rep db to Exadata’s copy • Open with RESETLOGS and UPGRADE Rep copy on Exadata. Run upgrade scripts • Direct Streams to the new Rep copy on Exadata • Direct application to the new Rep db • Rollback • Flashback Rep db to the guaranteed restore point • Rewind Streams on Trading db and re-apply changes on Reporting db • Go Live • Upgrade Standby to the 11gR2. Synchronize it

  32. Сергей Щукин shchukin@devexperts.com • Performance comparison • AWR diffs (RAT)

  33. Сергей Щукин shchukin@devexperts.com • Performance comparison • AWR diffs (RAT) • CPU Time: 11000secvs3600sec • Sinlge block read 13msvs0.6ms • Multiblock read 6,5msvs1ms • DB file parallel write 4,5msvs20ms - WORSE • Log file sync 33msvs17ms • CF parallel read 15msvs4ms • CF sequential read 7.4msvs1.5ms

  34. Сергей Щукин shchukin@devexperts.com • Performance comparison • Improved/degraded events • 1 week comparison

  35. Сергей Щукин shchukin@devexperts.com • Performance comparison • Our own observations • DB time/CPU time slightly better • Amount of User IO increased (buffer cache 115GB->15G), but avg time decreased • Half SQL performs better, another half performs worse • Hard to analyze • DB Caches decreased • HW changed • SW changed (plans) • Exadata features hard to use (Smart Scan requires full scans) • Not all the databases were migrated

  36. Сергей Щукин shchukin@devexperts.com • Performance comparison • Redo writes to SSD • Exadata Smart Flash Log • Version 11.2.2.4 at the cell level • Version 11.2.0.3 at the DB level • (when it comes out) or 11.2.0.2 BP11 • Enabled by default

  37. Сергей Щукин shchukin@devexperts.com • Performance comparison • HCC for historical DB • Good for Historical data, that doesn’t change • Requires direct inserts • Doesn’t require additional license on Exadata • Decompression on Storage server (Offloading) • Our example gave 8.7 times compression (compress for archive high) • Can be estimated by DBMS_COMPRESSION.GET_COMPRESSION_RATIO

  38. Сергей Щукин shchukin@devexperts.com • Issues afterwards • Data corruption on Standby • Tue Nov 01 16:33:41 2011 • Errors in file /opt/orabase/diag/rdbms/reports/nlrcs/trace/nlrcs_mrp0_31928.trc (incident=44233): • ORA-00600: internal error code, arguments: [3020], [8], [1938230], [35492662], [], [], [], [], [], [], [], [] • ORA-10567: Redo is inconsistent with data block (file# 8, block# 1938230, file offset is 3644022784 bytes) • ORA-10564: tablespace DATA_TS • ORA-01110: data file 8: '+DATA/reports/datafile/data_ts.270.740684989' • ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 113755 • SR with Severity 1 status! • No root causes within 3 months!!

  39. Сергей Щукин shchukin@devexperts.com • Issues afterwards • Bug in statistic’s gathering • Bug 12865902  NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention") in RAC • Fixed in 11.2.0.2 BP13

  40. Сергей Щукин shchukin@devexperts.com • Q/A

More Related