Real Application Testing with Database Replay

1
Real Application Testing with Database Replay

• Yujun Wang, Supiti Buranawatanachoke, Romain
  Colle, Karl Dias,
• Leonidas Galanis, Stratos Papadomanolakis, Uri
  Shaft

2
Motivation Database Upgrade
Clients
?
Middle Tier
RDBMS

• Database upgrade for Siebel Financial Services
• Discover problems before actual upgrade

3
Upgrade Risks

• Changes are inevitable
• Extensive testing can be expensive and time
  consuming
• Success rate is low
• Current methods are inadequate
• Simulate unrealistic synthetic workload
• Cover very small percentage of all workflows

• Database Replay makes realistic testing possible
• Capture production workload
• Recreate it in test system
• Fix potential problems before going alive

4
Upgrade Test Plan
Capture
Upgrade
Replay
Problem Resolution
Analyze

no
yes
Problem?
Upgrade
5
Workload Capture
Backup

• Minimal overhead ? Record everything
• Platform and protocol independent
• Workload filters

Middle Tier
OS Directory
File 1
Server 2
File 2
Server 1
Server N

File N
6
Workload Capture

• Oracle Siebel Financial Services Application
• Database Server
• Database Oracle DB 11.1.0.7.0
• OS Red Hat Enterprise Linux 4
• 8 CPUs
• 32G memory
• Workload
• 1300 users
• Capture duration 110 min
• 4,685,220 database calls
• Total capture size 4.1G

7
Workload Replay

• Before Replay
• Upgrade Database
• Process capture
• Create restore point
• for future replays
• Replay Clients
• Multithreaded
• One replay thread per file
• Scalable architecture
• Provide calibrate for of replay clients

8
Replay Properties

• Same User Calls
• Reverse engineer the captured calls and Replay
  with OCI calls
• Remap machine-dependent data such as ROWIDs,
  Cursor numbers, LOB locators and sequences
• Preserve timing characteristics
• Same Connection Pattern
• Preserve concurrency
• Same Transactional Semantics
• Each transaction tries to start with the same
  database state and make the same change
• It is supported by commit ordering

9
Commit Ordering
Session 1 1000 UPDATE EMP 1010 COMMIT
Session 2 1020 SELECT EMP
Session 3 1030 UPDATE EMP 1040 COMMIT
Replay session 1 1500 UPDATE EMP 1540 COMMIT
Replay session 2 1520 1541 SELECT EMP
Replay session 3 1530 1541 UPDATE EMP 1550
COMMIT
delay
wait for clock
wait for clock
10
Initial Replay
11
Replay Analysis

• How much has been replayed as expected
• Replay finished normally?
• Percentage of diverged calls?
• Find performance regression
• Use Oracle tools such as AWR, ADDM etc.
• Analyze call divergence
• Error divergence
• Data divergence

Yes
Very low 0.02
12
Performance Analysis

• Problem
• Performance regression from SQL (44u0kwtwmd7h5)
• Plan change
• Remedy
• Tune SQL (44u0kwtwmd7h5)
• Export the SQL profile
• Flashback the database
• Import the SQL profile

Capture 90 secs Replay 10409 secs
13
Replay after SQL Tuning
14
Analysis Tool

• It takes time to manually compare two workloads
• AWR report might have 2300 lines
• Compare period report makes it easy
• New analysis tool at Database Replay
• First effort at Oracle to compare two workload
• Capture and replay make it meaningful to compare

15
Compare Period Report

• Main sections
• General information about two environments
• Replay divergence level (NONE / LOW / MEDIUM /
  HIGH)
• MAIN performance statistics
• Top SQL by Change in database time
• Hardware Usage Comparison
• ADDM Comparison
• Two types of compare period reports
• Compare capture with replay
• Compare replay with replay

16

17

18
Conclusion

• DB Replay helped with a smooth upgrade
• Very helpful in finding and fixing problems
• Reliable to reproduce problems
• No other software! Easier to use!
• Better testing with better workload
• Record and replay the real production workload
• Full coverage of applications
• No need to develop test scripts

19
Questions?