DBA!ert
Independant Oracle consultancy
Copyright notice • The content of this document is copyrighted by DBA!ert, Waddinxveen, The Netherlands. © 2005 DBA!ert. All rights reserved. • No portion of the content may be directly or indirectly copied, published, reproduced, modified, performed, displayed, sold, transmitted, published, broadcast, rewritten for broadcast or publication or redistributed in any medium. Nor may any portion of the content be stored in a computer or distributed over any network except that you may print one copy strictly for personal and non-commercial use
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert
Data Guard Performance Issues (Cover your Assets with Data Guard) Carel-Jan Engel
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Introduction – who am I ? Carel-Jan Engel Company: DBA!ert In IT since 1982 Using Oracle since May, 1985 Member of the Oaktable Network email: cjpengel.dbalert@ xs4all.nl Hotsos Symposium 2005
Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Agenda Data Guard architecture HA Alternatives Data Guard performance Test environment Results Q &A
Testhardware: courtesy of Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Architecture Redundant Hardware Redo Archiving Recovery
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Architecture Primary
LGWR
Standby
RFS Oracle Net
redo
ARCH
standby redo
ARCH
MRP archives archives Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Architecture Primary
LGWR
Standby
RFS Oracle Net
ARCH
redo
standby redo
ARCH
MRP archives archives
FAL
Oracle Net
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Architecture Primary
LGWR
Standby
RFS
redo
ARCH
standby redo
ARCH
MRP archives archives Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard benefits Disaster Recovery Natural or humanoid
High Availability Switchover allows HW maintenance
Guaranteed data protection Synchronous transfer, shutdown if no standby available
Ease of administration GUI, CLI Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard benefits Data Protection Flexibility 3 protection modes to tailor to your needs
Light footprint Not too heavy transaction penalty
Extra functionality Read-only standby for reporting
Resource Maximization Move backup, reporting to standby
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Requirements Enterprise Edition license On both ends
Any Oracle supported platform But: same OS/OS level, same Oracle version
Archive Log Mode Identical directory structure recommended FORCE_LOGGING recommended
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard / HA alternatives
RAC Redundant instances Single database - SPOF Complementary
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard / HA alternatives
Mirroring (storage) Mostly asynchronous Far from fine granularity block/track level mirroring much superfluous changes transferred redo (2x), arch (2x), control (3x), database
Large bandwidth required
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard / HA alternatives
Quest’s Shareplex 3rd party supports cross-platform, cross version expensive
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Data Guard Performance Performance affected by redo log volume log transport network latency network throughput LOG_ARCHIVE_DEST_n settings
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert LGWR SYNC PARALLEL Primary
LGWR
Standby
RFS Oracle Net
redo
ARCH
standby redo
ARCH
MRP archives archives
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert LGWR ASYNC Primary
LGWR
Standby
RFS
redo
ARCH
standby redo
ARCH
MRP archives archives
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert LGWR SYNC PARALLEL AFFIRM Primary
LGWR
Standby
RFS Oracle Net
redo
ARCH
standby redo
ARCH
MRP archives archives
1
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert LOG_ARCHIVE_DEST_n settings ARCH LOCATION / SERVICE MANDATORY / OPTIONAL
LGWR
X X
X X
SYNC / ASYNC
X
(NO)AFFIRM
X
(NO)PARALLEL
SYNC
NET_TIMEOUT
ASYNC > 0 SYNC=PARALLEL
1
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Test harness reproducible testruns statistics gathering logfile gathering long running jobs parameterized scenarios unattended
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Test harness
rc.d concept execute init, run and exit jobs ordered by name extended with default settings
init cleanup, create schema, tablespaces, set parameters, start
databases in correct mode
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Test harness run kick off the load generation jobs
exit export perfstat data, schema data backup alert and trace files backup load-logging
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Test harness load-generation with dbaman - James Morle
whitepaper: ‘brewing benchmarks’ - www.oaktable.org tcl-based extended shell oci-calls available controlcenter randomizing ‘think time’ for parallel sessions
2
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing OLTP order-entry: 5 processes order creation order extension order delivery invoicing payment
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing order creation create an order create 1-10 order lines 64 processes
order extension extend 25% of the orders extend to 18 order lines 10 processes
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing order delivery update delivery date 16 processes
invoicing invoice delivered orders 6 processes
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing payment create payments
amount 25 - 75 % (random), until full payment 4 processes
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing OLTP many small, scattered transactions 100 processes delays to ‘age-out’ buffers from cache
2
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing OLTP 0.5 hour runs
42824 orders (23 / second) (correct paper, p7!) 258335 orderlines (143 / second) 4329 invoices 1301 payments 25 MB redo / min ==> 1.5 GB/hr:
2
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing
No performance degradation measurable.
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing
OLTP - No performance degradation System: 1 x Pentium 3.0 GHz/HT 2 disks: 1x 10,000 RP/M, 1x 15,000 RPM
100 Mb Network can keep up easily with the disks
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Performance testing Batchruns 4 parallel sessions:
create table t as select * from all_objects; delete from t; rollback; delete from t; drop table t;
run 100 times measure time
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Scenarios Per run 70 logswitches (+/- 1) 1762 MB redolog detailed statistics
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Scenarios Per run 12 scenarios One single-server, base-line 10 LGWR, various options 1 ARCH
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Ramdisk redo Avoiding disk-contention For re-runnable batches only Can speed things up quite well, especially in NOARCHIVELOG mode
Take care, data lost after crash
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Results Per run
redo size 1762 MB (7 GB / hour)
70 - 71 logswitches running appr. 15 minutes ~ 1.9 MB redo / second
3
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Run1, Redo on Ramdisk, 100Mb 0 NONE 1 PERF
1 LGWR
SYNC
NOAFFIRM
NOPARALLEL
1.23
2 AVAIL LGWR
SYNC
AFFIRM
NOPARALLEL
1.17
3 PROT
LGWR
SYNC
AFFIRM
NOPARALLEL
1.22
4 PERF
LGWR
SYNC
NOAFFIRM
PARALLEL
1.21
5 AVAIL LGWR
SYNC
AFFIRM
PARALLEL
1.21
6 PROT
LGWR
SYNC
AFFIRM
PARALLEL
1.23
7 PERF
LGWR
ASYNC 50
1.06
8 PERF
LGWR
ASYNC 100
1.21
9 PERF
LGWR
ASYNC 200
1.03
10 PERF
LGWR
ASYNC 400
1.04
11 PERF
ARCH
1.05
3
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Run2, Redo on Harddisk, 100Mb 0 NONE 1 PERF
1 LGWR
SYNC
NOAFFIRM
NOPARALLEL
0.98
2 AVAIL LGWR
SYNC
AFFIRM
NOPARALLEL
1.27
3 PROT
LGWR
SYNC
AFFIRM
NOPARALLEL
1.23
4 PERF
LGWR
SYNC
NOAFFIRM
PARALLEL
1.21
5 AVAIL LGWR
SYNC
AFFIRM
PARALLEL
1.19
6 PROT
LGWR
SYNC
AFFIRM
PARALLEL
1.22
7 PERF
LGWR
ASYNC 50
1.21
8 PERF
LGWR
ASYNC 100
1.07
9 PERF
LGWR
ASYNC 200
1.12
10 PERF
LGWR
ASYNC 400
1.09
11 PERF
ARCH
1.18
3
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Run3, Redo on Harddisk, 1Gb 0 NONE 1 PERF
1 LGWR
SYNC
NOAFFIRM
NOPARALLEL
1.10
2 AVAIL LGWR
SYNC
AFFIRM
NOPARALLEL
1.10
3 PROT
LGWR
SYNC
AFFIRM
NOPARALLEL
1.11
4 PERF
LGWR
SYNC
NOAFFIRM
PARALLEL
1.07
5 AVAIL LGWR
SYNC
AFFIRM
PARALLEL
1.05
6 PROT
LGWR
SYNC
AFFIRM
PARALLEL
1.00
7 PERF
LGWR
ASYNC 50
0.98
8 PERF
LGWR
ASYNC 100
1.01
9 PERF
LGWR
ASYNC 200
1.03
10 PERF
LGWR
ASYNC 400
1.04
11 PERF
ARCH
1.07
3
Hotsos Symposium 2005 © DBA!ert 2005
Independant Oracle consultancy
DBA!ert Conclusions
Data Guard slows down a system saturated with batchload Maximum performance loss in heavy batchload: ~ 27 % (Relatively) small system with OLTP No effect from Data Guard measurable: Speeds vary, 2% slower - 2% faster, results unreliable
4
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert Conclusions
Experience in day-to-day implementations No significant performance problems during normal work Large data loads: trade off between either slow run or detached standby run followed by Instantiation.
4
Hotsos Symposium 2005 Š DBA!ert 2005
Independant Oracle consultancy
DBA!ert
Q &A cjpengel.dbalert@ xs4all.nl
4
Hotsos Symposium 2005 Š DBA!ert 2005