EXADATA: deployment story

EXADATA: deployment story Сергей Щукин [email protected] Agenda 1. Introduction 2. Our Exadata 3. First Issues 4. Preparation for the ...
Author: Merry Tate
25 downloads 0 Views 2MB Size
EXADATA: deployment story

Сергей Щукин [email protected]

Agenda 1.

Introduction

2.

Our Exadata

3.

First Issues

4.

Preparation for the Migration

5.

Migration

6.

Performance Comparison

7.

Issues Afterwards

Сергей Щукин [email protected]

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?

Сергей Щукин [email protected]

Introduction Main Exadata’s secret Data Processing on Storage layer 1. 2.

Less data is transferred over the Network Less resources used on DB servers

Сергей Щукин [email protected]

Introduction Versions’s history •

V1 • • • •



V2 • • • •



Released in 2008 HP h/w + Oracle s/w (Database Machine) No Flash Cache Data Warehouse oriented 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

Сергей Щукин [email protected]

Introduction Existing configurations V1

V2 Storage

DB

Server

8xProLiant DL 360 G5

14xProLiant DL180 G5

Sun ?

Sun ?

8xSun x4170 M2

14x Sun x4270 M2

2xSun x4800

14x Sun x4270 M2

CPU

2x 4Core Xeon E5430 2,66 GHz

2x 4Core Xeon E5430

4Core

?

2x 6Core Xeon X5670 2,93 GHz

2x 6Core Xeon L5640

8x 8Core Xeon X7560 2.26Ghz

2x 6Core Xeon L5640

RAM

32GB

8GB

96GB

24GB

1tb

24GB

Info

12xSAS 450GB Added 384GB SSD Flash Cache

DB

Storage

X2-8

DB

Disks

Storage

X2-2 DB

Storage

12xSAS 600GB

12xSAS 600GB

512MB Controller cache+ 384GB SSD

512MB Controller cache+ 384GB SSD

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Introduction Existing configurations DB server: Sun Fire X4170 M2

Сергей Щукин [email protected]

Introduction: Existing configurations Storage server: Sun Fire X4270 M2

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Our Exadata Shipment details Date

Event

Days passed

21-APR-2011

Purchase of Exadata is approved by Client

27-MAY-2011

Exadata arrived with problem power supply

36 days

07-JUL-2011

Power supply was changed

11 days

19-JUL-2011

Primary setup was completed

12 days

20-JUL-2011

Testing was started

1 day

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Our Exadata What we got



ASM layout

Сергей Щукин [email protected]

First issues No place for copying backup •

DBFS



Local LVM



NFS



Attach external storage

Сергей Щукин [email protected]

First issues Slow NFS 1.

Was: 1 MB/sec

2.

Fixed after putting Exadata and Storage server into the same vlan

Сергей Щукин [email protected]

First issues Restrictions in customization Any changes in SW and HW(except switches) are prohibited

Сергей Щукин [email protected]

Preparation for the Migration Real Application Testing (RAT) Description • • • • •

Capture & Replay EE option Playback from 11.1.0.6 9i & 10g Capture only. Patch is required Cost:11500 $ per processor + 2530 support

Restrictions •

Streams are skipped



Direct path load of data from external files Flashback queries Distributed transactions Non SQL-based object access

• • •

Сергей Щукин [email protected]

Preparation for the Migration Real Application Testing (RAT) Streams workload is not captured

Сергей Щукин [email protected]

Source DB Destination DB What patch you need to apply? Download Information Upgrade Upgrade to any from release release

Comments

9.2.0.8.0

>=11.1.0.7.0

9.2.0.8.0 + one-off patch 9373986 One-off patch can be downloaded + one-off patch or requested from MOS 8712466 on top of 11.1.0.7.0

8712466 is a merge patch on top of 11.1.0.7.0

9.2.0.8.0

>=11.2.0.2.0

9.2.0.8.0 + one-off patch 9373986 One-off patch can be downloaded and 11.2.0.2.0 + patch 11870615 from MOS

One-off patch can be downloaded from MOS

10.2.0.4.0

>=11.1.0.7.0

10.2.0.4.0 patchset+ one-off patch 10239989 + one-off patch 8712466 on top of 11.1.0.7.0

Functionality already exists in Patchset, download from Metalink

10.2.0.4.0

>=11.2.0.2.0

10.2.0.4.0 Patchset + one-off Patchset can be downloaded from patch 10239989 MOS and 11.2.0.2.0 + patch 11870615

10.2.0.5.0

>=11.2.0.2.0

11.1.0.7.0

>=11.2.0.2.0

10.2.0.5.0 Patchset + one-off patch 9373986 and 11.2.0.2.0 + one-off patch 11870615 11.1.0.7.0 + one-off patch 8712466 + 9373986 and 11.2.0.2.0 + one-off patch 11870615

MOS 560977.1

Patchset can be downloaded from MOS

Functionality already exists in Patchset, download from MOS

One-off patch can be downloaded or One-off patch can be downloaded or requested from MOS requested from MOS

One-off patch can be downloaded or One-off patch can be downloaded or requested from MOS requested from MOS

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Preparation for the Migration Bug in Playback •

Worked fine for Reporting DB (3 hours) • • • • •



Better DB Time (lower: 855.3 vs 138.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

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Migration Official methods Physical

http://www.oracle.com/technetwork/database/features/ availability/xmigration-11-133466.pdf

Сергей Щукин [email protected]

Migration Official methods Logical

Сергей Щукин [email protected]

Migration Our method (10gR2 -> 11gR2)



Backup & Restore database



Manual applying of the archivelogs



Open in new DB with RESETLOGS and UPGRADE

Сергей Щукин [email protected]

Migration Difficulties • • • •

Streams Test runs with rollback 10gR2 standby in Amazon Need to run utlu112i.sql on the Prod DB with the following rollback

Сергей Щукин [email protected]

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;

Сергей Щукин [email protected]

Migration Step-by-step for Rep database 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.

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 1. 2.

Flashback Rep db to the guaranteed restore point Rewind Streams on Trading db and re-apply changes on Reporting db

Go Live 1.

Upgrade Standby to the 11gR2. Synchronize it

Сергей Щукин [email protected]

Performance comparison AWR diffs (RAT)

Сергей Щукин [email protected]

Performance comparison AWR diffs (RAT) •

CPU Time: 11000sec vs 3600sec



Sinlge block read 13ms vs 0.6ms



Multiblock read 6,5ms vs 1ms



DB file parallel write 4,5ms vs 20ms - WORSE



Log file sync 33ms vs 17ms



CF parallel read 15ms vs 4ms



CF sequential read 7.4ms vs 1.5ms

Сергей Щукин [email protected]

Performance comparison Improved/degraded events 1 week comparison

Сергей Щукин [email protected]

Performance comparison Our own observations 1.

DB time/CPU time slightly better

2.

Amount of User IO increased (buffer cache 115GB->15G), but avg time decreased

3.

Half SQL performs better, another half performs worse

4.

Hard to analyze 1.

DB Caches decreased

2.

HW changed

3.

SW changed (plans)

4.

Exadata features hard to use (Smart Scan requires full scans)

5.

Not all the databases were migrated

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

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!!

Сергей Щукин [email protected]

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

Сергей Щукин [email protected]

Q/A