Transcript EXADATA: deployment story

Сергей Щукин
[email protected]
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