oaktableworld 2013: ultimate exadata io monitoring – flash, harddisk , & write back cache...
DESCRIPTION
TRANSCRIPT
Ultimate Exadata IO monitoringFlash, HardDisk , & Write back cache
overhead
Presented by: Karl Arao
1
whoami
Karl Arao• Senior Technical Consultant @ Enkitec• Performance and Capacity Planning Enthusiast
7 years DBA experienceOracle ACE, OCP-DBA, RHCE, OakTableBlog: karlarao.wordpress.comWiki: karlarao.tiddlyspot.comTwitter: @karlarao
www.enkitec.com 2
www.enkitec.com 3
200+
3
Booth #501 Moscone
South
Agenda
• Performance scenario
• WBFC
• Different ways to Monitoring IO• AWR IO data• Correlate up to the workload and sqlstats data – feedback loop• Cell data - cell_iops.sh
• Scaling it!
www.enkitec.com 4
www.enkitec.com 5
The workload
www.enkitec.com 6
Performance page
www.enkitec.com 7
OEM12c storage grid perf
www.enkitec.com 8
WBFC
www.enkitec.com 9
http://goo.gl/2WCmw
www.enkitec.com 10
Write-Through Write-Back
www.enkitec.com 11
www.enkitec.com 12
www.enkitec.com 13
www.enkitec.com 14
Tableau demo on AWR IOPS and Workload data
www.enkitec.com 15
Different views of IO performance with AWR datahttp://goo.gl/i660CZ
SECTION 1: USER IO wait class and cell single block reads latency with curve fitting
SECTION 2: Small IOPS vs Large IOPS
SECTION 3: Flash vs HD IOPS
SECTION 4: Flash vs HD IOPS with read/write breakdown
SECTION 5: IO throughput read/write MB/s
SECTION 6: Drill down on smart scans affecting cell single block latency on 24hour period
www.enkitec.com 16
www.enkitec.com 17
Measuring the effect of IORM on latency
www.enkitec.com 18
www.enkitec.com 19
IORM BASIC IORM AUTO IORM LOW
LATENCY
Tableau demo on Cell data - cell_iops.sh
www.enkitec.com 20
www.enkitec.com 21
How do you scale?
www.enkitec.com 22
Metric Extensions + BIP
www.enkitec.com 23
www.enkitec.com 24
DB and CELL IOPS/MBs comparison:
1)Create metric extensions for DB and CELL
2)Create the model
3)Graph it in Tableau and BIP
4)Schedule the email of final reports/visualization
www.enkitec.com 25
www.enkitec.com 26
Reference band for HardDisk IOPS capacityCheck the _OTHER_DATABASE_ IOPS and compare with AWR and CELL numbers for WBFC destage & flash cache metadata operations overhead
www.enkitec.com 27
Workload AAS by day per node
www.enkitec.com 28
Summary• WBFC
• Different ways to Monitoring IO• AWR IO data• Correlate up to the workload and sqlstats data – feedback loop• Cell data - cell_iops.sh
• Scaling it with Metric Extensions and BIP
www.enkitec.com 29
References• WBFC patent http://goo.gl/2WCmw• AWR scripts and cell_iops.sh
http://karlarao.wordpress.com/scripts-resources/• SQL Developer ready AWR scripts http://goo.gl/ocv5b• Different views of IO performance with AWR data http://goo.gl/i660CZ
www.enkitec.com 30
Questions?
karlarao.wordpress.comkarlarao.tiddlyspot.com
@karlarao
www.enkitec.com 31