oracle database cloud performance doag 2016
Post on 09-Jan-2017
333 Views
Preview:
TRANSCRIPT
ORACLEDATABASE CLOUD
PERFORMANCERandolf Geist
www.oracle-performance.dehttp://oracle-randolf.blogspot.com
randolf.geist@oracle-performance.de
WHO AM I Independent consultant
Performance Troubleshooting In-house workshops
Cost-Based Optimizer Performance By Design
Oracle ACE Director
Member of OakTable Network
MY CHANNELS - BLOGhttp://oracle-randolf.blogspot.com
MY CHANNELS - YOUTUBEhttps://www.youtube.com/c/RandolfGeist
MY CHANNELS – OTN FORUMhttps://community.oracle.com/welcome
INTRODUCTION Oracle Cloud extended trial license as part
of Oracle ACE Director program
Trial license comes with limitations / resource quotas:- 1TB max storage- max 6 OCPUs (more details later)- max 5 IP Endpoints
Performance tests conducted within these limits
ORACLE DBAAS – WHAT DO YOU GET?
ORACLE DBAAS – WHAT DO YOU GET?
ORACLE DBAAS – CREATE NEW SERVICE - TYPE
ORACLE DBAAS – CREATE NEW SERVICE - RELEASE
ORACLE DBAAS – CREATE NEW SERVICE - EDITION
ORACLE DBAAS – CREATE NEW SERVICE - EDITION
No “hardware” performance difference between different editions measurable – all created services show similar performance characteristics in terms of CPU and I/O capabilities
Different performance still possible on software level – Diagnostic + Tuning Pack features (e.g. SQL Tuning advisor), InMemory Column Store etc.
ORACLE DBAAS – CREATE NEW SERVICE - DETAILS
ORACLE DBAAS – CREATE NEW SERVICE - CONFIRMATION
ORACLE DBAAS – SERVICES
ORACLE DBAAS – SERVICES
ORACLE DBAAS – SCALABILITY Services can be scaled by adding more
CPUs, RAM and storage
Currently no online operation, other Cloud providers can do some of these scaling activities online
ORACLE DBAAS – SECURITY AND ACCESS
Tablespace encryption mandatory
Network access rules, by default public access disabled, only access via SSH possible
ORACLE DBAAS – SSH ACCESS
ORACLE DBAAS – NETWORK ACCESS RULES
ORACLE DBAAS – DBAAS MONITOR
ORACLE DBAAS – CPU PERFORMANCE
ORACLE DBAAS – CPU PERFORMANCE – SURPRISE!
4 OCPUs configuration
Architecture: x86_64CPU op-mode(s): 32-bit, 64-bitByte Order: Little EndianCPU(s): 8On-line CPU(s) list: 0-7Thread(s) per core: 2Core(s) per socket: 4Socket(s): 1NUMA node(s): 1Vendor ID: GenuineIntelCPU family: 6Model: 79Model name: Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHzStepping: 1CPU MHz: 2300.062BogoMIPS: 4600.12Hypervisor vendor: XenVirtualization type: fullL1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 46080KNUMA node0 CPU(s): 0-7
AMAZON RDS – 8 VCPUS CONFIGURATION
MEASURABLE DIFFERENCE? OH YES!
PLSQL8
PLSQL4
SQLLIO8
SQLLIO4
0 5 10 15 20 25 30 35 40
35.8
34
31.1
29.9
AVG_DURATION
PLSQL8
PLSQL4
SQLLIO8
SQLLIO4
0 10 20 30 40 50 60 70
60.5
36
47.4
22.7
AVG_DURATION
ORACLE DBAAS AMAZON RDS
Average duration of a CPU bound database operation, running 4 and 8 threadsSQLLIO: Burns CPU via SQL logical I/Os (SQL engine)PLSQL: Burns CPU via PL/SQL computation (PL/SQL engine)
Disks exposed via XEN disk storage devices (paravirtualized XEN disk drivers /dev/xvd*) and then via device mapper and LVM
Single Instance: ext4 Filesystem, asynchronous and direct I/O enabled by default
RAC: ASM plus ACFS on top
ORACLE DBAAS – STORAGE OVERVIEW
ORACLE DBAAS – I/O PERFORMANCE
Average read IOPS figures over several days4 / 8 threads, 4KB/8KB/16KB blocksize, maximum physical, asynchronous I/O
TEST AVG_IOPS
4THREADS_4K 43900
8THREADS_4K 55760
4THREADS_8K 37748
8THREADS_8K 38986
4THREADS_16K 28631
8THREADS_16K 30264
TEST AVG_IOPS
4THREADS_4K 58968
8THREADS_4K 60943
4THREADS_8K 50945
8THREADS_8K 58740
4THREADS_16K 30670
8THREADS_16K 40330
Single Instance 4 OCPUs / 8 Cores/Threads
RAC Two Instances 2 + 2 OCPUs / 4 + 4 Cores/Threads
General Purpose SSD:
Less than 1TB allocated:Peak: 3.000 IOPS for max. 30 minutesThen throttled to something between 100 IOPS and less than 3.000 IOPS depending on amount of storage allocated
I/O credits re-load over time, determine the possible IOPS peak between baseline and 3.000 IOPS
Provisioned IOPS: Maximum 30.000 IOPS
AMAZON RDS – I/O PERFORMANCE
Premium SSD:
Maximum 5.000 IOPS per storage container
MICROSOFT AZURE – I/O PERFORMANCE
IOPS / throughput independent from allocated storage size (tested with 25GB to
1TB) !!
Compare this to Amazon RDS where you need at least 3TB of storage to get
maximum 30.000 IOPS for “Provisioned IOPS” premium storage
ORACLE DBAAS – I/O PERFORMANCE
Maximum throughput
Single instance:
640MB / sec read 120MB / sec write
Two instances RAC:
1100MB / sec read80MB / sec write
ORACLE DBAAS – I/O PERFORMANCE
I/O Latency -Synchronous I/O 8KB blocksize / 8 threads
(“db file sequential read”)
ORACLE DBAAS – I/O PERFORMANCE
WAIT_TIME COUNT PERCENT PERCENT_GRAPH<= 256 microseconds 302062 3,1<= 512 microseconds
7013211 71,1
#################
<= 1 millisecond248993
8 25,2######<= 2 milliseconds 50799 0,5<= 4 milliseconds 4149 0<= 8 milliseconds 1102 0<= 16 milliseconds 224 0<= 32 milliseconds 195 0<= 65 milliseconds 73 0<= 131 milliseconds 20 0<= 262 milliseconds 16 0
I/O Latency “General Purpose SSD” -Synchronous I/O 8KB blocksize / 8 threads
(“db file sequential read”)
AMAZON RDS – I/O PERFORMANCE
WAIT_TIME COUNT PERCENT PERCENT_GRAPH<= 256 microseconds 152 0<= 512 microseconds 7150 0,4<= 1 millisecond 1911 0,1<= 2 milliseconds 6553 0,4
<= 4 milliseconds181736
2 99################
<= 8 milliseconds 1492 0,1<= 16 milliseconds 1006 0,1
Four different tests performed over several days- PL/SQL CPU burning loop (PL/SQL engine)- SQL logical I/O CPU burning- Maximum physical asynchronous I/O - read- Maximum physical asynchronous I/O - write
Comparison to dedicated physical host, four cores, Samsung SSD vs. Oracle DBaaS eight cores
ORACLE DBAAS – PERFORMANCE CONSISTENCY
Test 1: PL/SQL CPU burning loop, executed as many times as CPUs available
ORACLE DBAAS – PERFORMANCE CONSISTENCY
declare n number;begin loop n := 0; for i in 1..1000000000 loop n := n + 1; end loop; insert into timings(testtype, thread_id, ts) values ('PLSQL', &thread_id, systimestamp); commit; end loop;end;/
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS overall:
Physical host overall:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS per day:
Physical host per day:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS threads:
Physical host threads:
Test 2: SQL logical I/O CPU burning, executed as many times as CPUs available
ORACLE DBAAS – PERFORMANCE CONSISTENCY
alter session set "_old_connect_by_enabled" = true;
declare n number;begin loop select count(*) X into n from kill_cpu&tabname connect by n > prior n start with n = 1; insert into timings(testtype, thread_id, ts) values ('SQLLIO', &thread_id, systimestamp); commit; end loop;end;/
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS overall:
Physical hostoverall:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS per day:
Physical host per day:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS threads:
Physical host threads:
Test 3: Maximum physical asynchronous I/O – read only, minimum buffer cache, CPU
times
ORACLE DBAAS – PERFORMANCE CONSISTENCY
declare n number;begin loop select /*+ leading(t_o) use_nl(t_i) index(t_o) index(t_i) */ sum(t_i.n) into n from t_o , t_i&tabname t_i where t_o.id_fk = t_i.id; insert into timings(testtype, thread_id, ts) values ('&testtype', &thread_id, systimestamp); commit; end loop;end;/
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS overall:
Physical hostoverall:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS per day:
Physical host per day:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS threads:
Physical host threads:
Test 4: Maximum physical asynchronous I/O – read-write, minimum buffer cache, CPU
times
ORACLE DBAAS – PERFORMANCE CONSISTENCY
begin loop for rec in ( select /*+ index(t_o) */ id_fk from t_o ) loop update t_i&tabname t_i set n = rec.id_fk where id = rec.id_fk; end loop; insert into timings(testtype, thread_id, ts) values ('&testtype', &thread_id, systimestamp); commit; end loop;end;/
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS overall:
Physical hostoverall:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS per day:
Physical host per day:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS threads:
Physical host threads:
ORACLE DBAAS – PERFORMANCE CONSISTENCY
DBaaS events:
Physical host events:
MORE ON THIS TOPIC?http://oracle-randolf.blogspot.com/search/label/DBaaS
THANK YOU!
Q & A
I/O Latency -Synchronous I/O 16KB blocksize / 8 threads
(“db file sequential read”)
ORACLE DBAAS – I/O PERFORMANCE
WAIT_TIME COUNT PERCENT PERCENT_GRAPH<= 256 microseconds 20289 0,3<= 512 microseconds 3333341 45,6
##############
<= 1 millisecond 3607797 49,3###############
<= 2 milliseconds 289636 4#<= 4 milliseconds 64954 0,9<= 8 milliseconds 1412 0<= 16 milliseconds 179 0<= 32 milliseconds 179 0<= 65 milliseconds 72 0<= 131 milliseconds 31 0<= 262 milliseconds 5 0<= 1 second 2 0
top related