benchmark oracle database appliance - · pdf fileoracle database appliance benchmark report...

Oracle Performance on Oracle Database Appliance

Benchmark Report

August 2012

copyright © 2012 by benchware.ch slide 2

1 About Benchware

2 CPU Performance

3 Server Performance

4 Storage Performance

5 Database Performance

6 Conclusion

Contents


Benchware Ltd

Strong foundation in core technologies like Oracle database system, server and storage systems

• System Architecture, Component Evaluation, Reviews

• Performance Analysis & Optimization

• Benchmarking

• Database engineering

Services and Products


Benchware Ltd

• Vendor-independent company - Benchware is completely committed to customers’ interests

• Holistic approach in designing, tuning and benchmarking Oracle systems

• Long experience track record - Responsible for system architecture of largest DWH and OLTP

systems, mainly telecom and finance industry

- Oracle since 1984 (Oracle Version 3)

- Performance tuning and benchmarking since 1993 (Oracle Version 7)

Value proposition


Oracle Database

Different versions, patches and options, about hundred configuration parameters.

Server & Operating System

Different server Systems, processors and CPU architectures, (x86, IA-64, UltraSparc, SPARC64, Power), #cores, multithreading, main memory, bus architecture. Different operating Systems and patches, over hundred configuration parameters, virtualization of resources.

Volume & File Management

Different volume managers (VxVM, ASM) and file Systems (UFS, VxFS, ext3, JFS, ZFS, raw devices), different I/O methods (async, direct), a lot of config parameters (#LUNS, queue depth, max i/o unit), software striping and/or mirroring, multipathing.

Storage System

Different storage Systems, storage tiers and storage technology: spindle count and speed, RAID management, cache management, server interface technology, storage system options like remote copy, hardware striping and/or mirroring, virtualization of resources.

Storage Network (FC-, IB- or IP-based)

Bandwidth, latency during remote storage mirroring (sync, async) due to switches, hubs and distance.

Application Network (IP-based)

Bandwidth, latency during remote database mirroring (sync, async) due to switches and sql*net and tcp/ip stack (frame size, …).

Benchware Ltd


Database System

Storage System St

ora

ge N

etw

ork

Middleware (apps server, esb)

Application

Ap

plic

atio

n N

etw

ork

Syst

em

Man

agem

en

t, O

pe

rati

on

s, S

ecu

rity

,

Re

sso

urc

e M

anag

em

en

t

Server & Operating

System

Complex architecture of Oracle platforms needs benchmarking

Performance of complex technology stack is NOT predictable – unless running a benchmark


Benchware Ltd


Database System

Storage System St

ora

ge N

etw

ork

Middleware (apps server, esb)

Application

Ap

plic

atio

n N

etw

ork

Server & Operating

System

Benchware Performance Suite

Object of measurement

Syst

em

Man

agem

en

t, O

pe

rati

on

s, S

ecu

rity

,

Re

sso

urc

e M

anag

em

en

t

• Benchware Performance Suite

- Benchware Monitor

- Benchware Loader

• Performance measurement at the interface between application and technology stack

• Key Performance Metrics can be used for SLA between IT operation and business

• Benchware uses Oracle Database stack to generate all kind of loads for cpu, server, storage and database


Server Performance Server-bound Oracle operations All operations in RAM - no I/O operations

OLTP systems

DWH systems

Efficiency Metrics

Unit

• in-memory SQL scalability cc-numa

virtualization

speed throughput

[µs] [s] [bps] [tps] [rps]

• pl/sql algorithms

quicksort

Benchware Ltd

CPU Performance CPU-bound Oracle operations All operations in Level 1, 2, 3 CPU cache

OLTP systems

DWH systems

Efficiency Metrics

Unit

• pl/sql basic operations multithreading virtualization

speed throughput

[s] [ops]

• pl/sql algorithms

fibonacci, prime numbers

Library of Oracle benchmark tests - implemented in PL/SQL, Java and SQL

[s] seconds [ms] milli seconds (10-3) [µs] micro seconds (10-6) [ns] nano seconds (10-9)

less important important very important

[bps] buffers per second [rps] rows per second [tps] transactions per second [ops] operations per second

[MBps] mega bytes per second [GBps] giga bytes per second [iops] i/o operations per second [qpm] queries per minute


Database Performance Mixed resource usage: CPU, memory, storage

OLTP systems

DWH systems

Efficiency Metrics Unit

• data load uncompressed, compressed

scalability speed throughput service time

[ms] [s] [rps] [tps] [qpm]

• data scan

• data aggregation & reports

• OLTP transactions insert, select, update

Benchware Ltd

Storage Performance I/O-bound Oracle operations

OLTP systems

DWH systems

Efficiency Metrics Unit

• sequential I/O 1 MByte, read and write

RAID tiering striping

virtualization replication

service time throughput

[ms] [MBps] [GBps]

[iops] • random I/O 8 kByte, read and write

Library of Oracle benchmark tests - implemented in PL/SQL, Java and SQL

[s] seconds [ms] milli seconds (10-3) [µs] micro seconds (10-6) [ns] nano seconds (10-9)

less important important very important

[bps] buffers per second [rps] rows per second [tps] transactions per second [ops] operations per second

[MBps] mega bytes per second [GBps] giga bytes per second [iops] i/o operations per second [qpm] queries per minute


1 About Benchware

2 CPU Performance




6 Conclusion

Contents


Oracle Database Platform

CPU Customer System

Oracle Database Appliance (ODA)

Type Intel Xeon X5675

Frequency [GHz] 3.06 GHz

#Cores 6

Multithreading 2-fold

Server Customer System


#sockets 2

#cores 12

#threads 24

CPU

CPU has huge impact on performance of most database operations - but also on Oracle license cost!


CPU Performance

0

2'000

4'000

6'000

8'000

10'000

12'000

14'000

16'000

18'000

1 2 4 8 16 32 64

ODA (1 node)

Customer System

Degree of parallelism (dop)

Thro

ugh

pu

t in

[ko

ps]

PL/SQL string processing (data type VARCHAR2)

• one of the fastest available CPU (single thread speed)

• with 2 nodes and 4 servers the ODA provides double the performance


CPU Performance

PL/SQL string processing (data type VARCHAR2)

Physical Physical Physical Physical

Rows/sec Ops/sec CPU read write Total read write Total REDO Time

Run Tst Code #N #J #T [rps] [ops] [%] [iops] [iops] [iops] [MBps] [MBps] [MBps] [MBps] [sec]

--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 8 T141 1 1 1 0.000E+00 2.308E+06 5 6 2 8 0 0 0 0 13

9 T141 1 2 1 0.000E+00 4.615E+06 9 8 3 11 0 0 0 0 13

10 T141 1 4 1 0.000E+00 9.231E+06 18 8 3 11 0 0 0 0 13

11 T141 1 8 1 0.000E+00 1.263E+07 29 6 3 9 0 0 0 0 19

12 T141 1 16 1 0.000E+00 1.500E+07 46 5 3 8 0 0 0 0 32

13 T141 1 32 1 0.000E+00 1.548E+07 50 6 3 9 0 0 0 0 62

OD

A

• #N - describes the number of RAC nodes involved in this test • #J - describes the number of jobs running the workload, jobs are round-robin distributed over all configured node in #N • CPU [%] – total CPU utilization in cluster


CPU Performance

0

1'000

2'000

3'000

4'000

5'000

6'000

1 2 4 8 16 32 64

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[ko

ps]

PL/SQL integer processing (data type NUMBER)




CPU Performance

PL/SQL integer processing (data type NUMBER)

OD

A




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 22 T132 1 1 1 0.000E+00 6.579E+05 6 6 2 8 0 0 0 0 19

23 T132 1 2 1 0.000E+00 1.316E+06 10 6 3 9 0 0 0 0 19

24 T132 1 4 1 0.000E+00 2.500E+06 18 9 2 11 0 0 0 0 20

25 T132 1 8 1 0.000E+00 3.846E+06 31 6 3 9 0 0 0 0 26

26 T132 1 16 1 0.000E+00 4.762E+06 47 6 2 8 0 0 0 0 42

27 T132 1 32 1 0.000E+00 4.938E+06 50 6 3 9 0 0 0 0 81

28 T132 2 64 1 0.000E+00 9.877E+06 99 9 3 12 0 0 0 0 81


CPU Performance

0

10'000

20'000

30'000

40'000

50'000

60'000

70'000

1 2 4 8 16 32 64

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[ko

ps]

PL/SQL floating point processing (data type FLOAT)




CPU Performance

PL/SQL floating point processing (data type FLOAT)




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 29 T133 1 1 1 0.000E+00 8.929E+06 5 9 3 12 0 0 0 0 14

30 T133 1 2 1 0.000E+00 1.786E+07 10 7 2 9 0 0 0 0 14

31 T133 1 4 1 0.000E+00 3.333E+07 18 5 2 7 0 0 0 0 15

32 T133 1 8 1 0.000E+00 4.762E+07 31 6 3 9 0 0 0 0 21

33 T133 1 16 1 0.000E+00 5.714E+07 47 6 2 8 0 0 0 0 35

34 T133 1 32 1 0.000E+00 5.970E+07 50 7 3 10 0 0 0 0 67

35 T133 2 64 1 0.000E+00 1.194E+08 98 6 4 10 0 0 0 0 67

OD

A


CPU Performance

12

19

0

2

4

6

8

10

12

14

16

18

20

n=39 n=40

ODA

Customer System


Spee

d in

[se

c]

PL/SQL algorithm (fibonacci, recursive)

• fastest ever benchmarked CPU!


Benchmark Results

Metric Customer System


#cores 24

#threads 48

PL/SQL operations Metric Customer System


String processing • Speed (single thread) • Throughput

[kops] [kops]

2’308

30’960

NUMBER processing • Speed (singe thread) • Throughput

[kops] [kops]

657

9’877

Floating point processing • Speed(single thread) • Throughput

[kops] [kops]

8’929

119’400

Algorithms • Speed fibonacci recursive (n=39) • Speed fibonacci recursive (n=40)

[s] [s]

12 19

Summary CPU Performance


1 About Benchware

2 CPU Performance




6 Conclusion

Contents



Server

Server Customer System


ASM3


ASM2

#sockets 2 2

#cores 12 12

#threads (CPU_COUNT) 24 24

Main Memory [GByte] 96 96

Host-Bus-Adapter (type, quantity, throughput)

Dualported SAS-s Dualported SAS-s

Operating System Oracle Linux 5.5 Oracle Linux 5.5

Cluster

#server 2 1

Most OLTP applications avoid I/O operations as much as possible and work predominately in RAM – server performance is essential for these kind of OLTP applications!

For some tests with ASM normal redundancy (ASM2) we used a non-RAC Oracle database on 1 server.


Server Performance

0

1'000

2'000

3'000

4'000

5'000

6'000

7'000

1 2 4 8 16 32

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[kr

ps]

In-memory SQL, full table scan

• perfect scalability on one node • no contention within Oracle as

long as no RAC conflicts


Server Performance

In-memory SQL, full table scan




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 61 T421 1 1 1 7.619E+05 0.000E+00 6 16 312 328 0 3 3 0 42

62 T421 1 2 1 1.488E+06 0.000E+00 9 27 31 58 0 0 1 0 43

63 T421 1 4 1 2.909E+06 1.000E-01 17 26 29 55 0 0 1 0 44

64 T421 1 8 1 4.414E+06 1.000E-01 28 14 19 33 0 0 0 0 58

65 T421 1 16 1 6.024E+06 2.000E-01 45 11 14 25 0 0 0 0 85

66 T421 1 32 1 6.400E+06 2.000E-01 49 12 10 22 0 0 0 0 160

67 T421 2 64 1 7.585E+06 2.000E-01 57 10 8 18 0 0 0 0 270

OD

A


Server Performance

0

10'000

20'000

30'000

40'000

50'000

60'000

70'000

80'000

90'000

100'000

1 2 4 8 16 32

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[tp

s]

In-memory SQL, primary key access

SQL statement service time < 5 ms

• scalability up to 90’000 tps for one node

• avg service time of SQL statement < 5 ms


Server Performance

In-memory SQL, primary key access




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 68 T611 1 1 1 6.096E+03 6.096E+03 5 12 1588 1600 0 14 14 0 86

69 T611 1 2 1 2.185E+04 2.185E+04 10 16 35 51 0 0 1 0 48

70 T611 1 4 1 3.957E+04 3.957E+04 17 23 27 50 0 0 1 0 53

71 T611 1 8 1 6.876E+04 6.876E+04 32 21 22 43 0 0 0 0 61

72 T611 1 16 1 8.924E+04 8.924E+04 47 16 17 33 0 0 0 0 94

73 T611 1 32 1 9.269E+04 9.269E+04 50 8 8 16 0 0 0 0 181

74 T611 2 64 1 1.598E+05 1.598E+05 88 11 9 20 0 0 0 0 210

OD

A


Server Performance

0

500

1'000

1'500

2'000

2'500

3'000

3'500

1 2 4 8 16 32

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[kr

ps]

In-memory SQL, secondary key access

• scalability up to 3’250’00 processed rows per second [rps] via index access

• no Oracle contention • perfect scalability with 2 RAC

nodes (next slide)

SQL statement service time < 5 ms


Server Performance

In-memory SQL, secondary key access




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 75 T621 1 1 1 4.226E+05 1.321E+03 6 9 7 16 0 0 0 0 397

76 T621 1 2 1 8.224E+05 2.570E+03 10 9 7 16 0 0 0 0 408

77 T621 1 4 1 1.657E+06 5.178E+03 18 9 6 15 0 0 0 0 405

78 T621 1 8 1 2.591E+06 8.097E+03 33 8 5 13 0 0 0 0 518

79 T621 1 16 1 3.254E+06 1.017E+04 50 7 4 11 0 0 0 0 825

80 T621 1 32 1 3.223E+06 1.007E+04 50 7 4 11 0 0 0 0 1666

81 T621 2 64 1 6.437E+06 2.012E+04 99 7 4 11 0 0 0 0 1668

OD

A


Benchmark Results

Server Metric Customer System

Oracle Database Appliance

1 node


2 nodes

#cores 12 24

#threads 24 48

Main memory [GByte] 96 196

In-memory SQL operations Metric Oracle Database Appliance

1 node


2 nodes

Full table scan • throughput

[rps]

6’400’000

7’585’000

Primary key table access • Throughput

• Service time

[rps] [tps] [ms]

92’690 92’690

< 5

159’800 159’800

-

Secondary key table access • Throughput

• Service time

[rps] [tps] [ms]

3’223’000

10’070 < 5

6’437’000

20’120 -

Summary Server Performance


1 About Benchware

2 CPU Performance




6 Conclusion

Contents



Storage System Customer System


Type JBOD

Front-end Controller (type, quantity, throughput)

2 x dual ported SAS-2

Frontend Cache [GByte] -

Back-end Controller (type, quantity, throughput)

-

#HDD Disks 20 x 600 GByte, 15k rpm

#SSD Disks 4 x 73 GByte

Hardware RAID-Level -

Hardware stripe unit size [KByte] -

Thin provisoning, Pools -

Usable disk capacity [TByte] 4.00

Usable flash capacity [TByte] 0.30 (for redo)

Storage System



Volume Manager Customer System



ASM3 ASM2

#LUN’s - -

Concatenated oder Striped striped striped

Queue depth - -

Special parameters 3-fold mirroring 2-fold mirroring

Volume and File Management

File System Customer System



ASM3 ASM2

Block size [KByte] - -

Direct I/O Yes Yes

Concurrent I/O - -

Asynchronous I/O Yes Yes

Special parameters - -


Storage Performance Test

0

500

1'000

1'500

2'000

2'500

3'000

1 2 4 8 16 32 64

ODA ASM3 (1 node)

Customer System


Thro

ugh

pu

t in

[M

Bp

s]

Sequential read

• excellent throughput for 20 conventional HDD (hard disc drives)

• avg HDD throughput over 100 MBps

• avg service time per multi block read @ DOP64 = 24 ms!



Sequential read




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 106 T426 1 1 8 7.262E+06 0.000E+00 10 2166 12 2178 2150 0 2150 0 141

107 T426 1 1 16 7.938E+06 0.000E+00 11 2365 10 2375 2350 0 2350 0 129

108 T426 1 1 32 8.605E+06 0.000E+00 12 2560 10 2570 2548 0 2548 0 119

109 T426 1 1 64 8.904E+06 0.000E+00 12 2650 10 2660 2636 0 2636 0 115

110 T426 1 1 128 8.463E+06 0.000E+00 12 2522 12 2534 2506 0 2506 0 121

111 T426 2 1 256 7.817E+06 0.000E+00 12 2330 13 2343 2314 0 2314 0 131

• Avg I/O size about 1 MByte

OD

A



0

200

400

600

800

1'000

1'200

1'400

1 2 4 8 16 32 64

ODA ASM3 (1 node)

Customer System

ODA ASM2 (1 node)


Thro

ugh

pu

t in

[M

Bp

s]

Sequential write

• moderate throughput for ASM high redundancy – far less than sequential read

• well throughput for ASM with normal redundancy



Sequential write




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 45 T211 1 1 1 0.000E+00 0.000E+00 2 15 259 274 0 214 214 0 24

46 T211 1 1 1 0.000E+00 0.000E+00 2 9 283 292 0 233 233 0 22

47 T211 1 2 1 0.000E+00 1.000E-01 3 10 372 382 0 320 320 0 32

48 T211 1 4 1 0.000E+00 1.000E-01 4 12 538 550 0 466 466 0 44

49 T211 1 8 1 0.000E+00 1.000E-01 3 14 689 703 0 603 603 0 68

50 T211 1 16 1 0.000E+00 1.000E-01 4 16 818 834 0 719 719 0 114

51 T211 1 32 1 0.000E+00 2.000E-01 4 18 918 936 0 808 808 0 203

52 T211 1 64 1 0.000E+00 2.000E-01 4 19 953 972 0 837 837 0 392


OD

A –

ASM

3




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 2 T211 1 1 1 0.000E+00 1.000E-01 2 5 310 315 0 256 256 0 20

3 T211 1 1 1 0.000E+00 0.000E+00 2 4 295 299 0 244 244 0 21

4 T211 1 2 1 0.000E+00 1.000E-01 2 5 469 474 0 394 394 0 26

5 T211 1 4 1 0.000E+00 1.000E-01 2 7 682 689 0 586 586 0 35

6 T211 1 8 1 0.000E+00 2.000E-01 3 10 977 987 0 837 837 0 49

7 T211 1 16 1 0.000E+00 2.000E-01 3 14 1151 1165 0 988 988 0 83

8 T211 1 32 1 0.000E+00 2.000E-01 4 15 1373 1388 0 1180 1180 0 139

9 T211 1 64 1 0.000E+00 2.000E-01 4 28 1417 1445 0 1215 1215 0 270


OD

A –

ASM

2



0

2'000

4'000

6'000

8'000

10'000

12'000

1 2 4 8 16 32 64

ODA (1 node)

Customer System


Thro

ugh

pu

t in

[io

ps]

Random read

• scales well • high throughput for small system

with 20 HDD’s • well service times for HDD

technology

24 ms

10 ms

7 ms

15 ms

5 ms

4 ms

40 ms 2 nodes

Number at measuring point: Avg service time for single block 8 kByte random read



Random read




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 136 T626 1 1 1 1.486E+03 1.000E-01 5 1533 14 1547 66 0 67 0 217

137 T626 1 2 1 2.500E+03 2.000E-01 5 2509 15 2524 70 0 71 0 262

138 T626 1 4 1 3.780E+03 4.000E-01 5 3738 20 3758 75 0 76 0 346

139 T626 1 8 1 5.227E+03 5.000E-01 5 5141 22 5163 80 0 81 0 501

140 T626 1 16 1 6.697E+03 7.000E-01 5 6543 27 6570 84 1 84 0 784

141 T626 1 32 1 8.237E+03 8.000E-01 5 8040 30 8070 87 1 88 0 1272

142 T626 2 64 1 9.932E+03 1.000E+00 5 9770 37 9807 81 1 81 0 2109

OD

A



0

1'000

2'000

3'000

4'000

5'000

6'000

1 2 4 8 16 32

ODA ASM3 (1 node)

Customer System

ODA ASM2 (1 node)


Thro

ugh

pu

t in

[io

ps]

Random write

• high throughput for small system with 20 HDD’s

• performance penalty for ASM with high redundancy

DBWR: 6 ms LGWR: 1 ms

DBWR: 8 ms LGWR: 1 ms

Number at measuring point: Avg service time for DBWR and LGWR multiblock I/O



Random write




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 88 T721 1 1 1 2.188E+03 6.800E+00 4 11 1872 1883 0 17 17 1 150

89 T721 1 2 1 3.506E+03 1.100E+01 4 8 2657 2665 0 24 24 2 187

90 T721 1 4 1 5.431E+03 1.700E+01 5 9 3344 3353 0 31 31 3 241

91 T721 1 8 1 8.406E+03 2.630E+01 5 8 3642 3650 0 35 35 4 312

92 T721 1 16 1 1.429E+04 4.460E+01 6 8 3707 3715 0 38 39 7 367

93 T721 1 32 1 2.329E+04 7.280E+01 9 16 3583 3599 8 50 59 11 450

OD

A –

ASM

3




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

2 15 T721 1 1 1 5.271E+03 1.650E+01 2 8 4594 4602 0 40 40 2 62

16 T721 1 2 1 6.308E+03 1.970E+01 2 6 4890 4896 0 43 43 3 104

17 T721 1 4 1 8.188E+03 2.560E+01 3 4 5133 5137 0 46 46 4 160

18 T721 1 8 1 1.180E+04 3.690E+01 4 4 5199 5203 0 49 49 5 222

19 T721 1 16 1 1.859E+04 5.810E+01 6 3 5146 5149 0 51 51 9 282

20 T721 1 32 1 2.929E+04 9.150E+01 10 3 5027 5030 0 55 55 14 358

OD

A –

ASM

2


Benchmark Results



ASM3


ASM2

Sequential I/O • Read • Write

[MBps] [MBps]

2’636 837

1’215 (+ 45%)

Random I/O read • Throughput • Service time

[iops] [ms]

8’040

24

Random I/O write • Throughput • Service time, DBWR multiblock

[iops] [ms]

3’707

8

5’199 (+ 40%)

Summary Storage Performance


1 About Benchware

2 CPU Performance




6 Conclusion

Contents



Installation Customer System


Oracle Edition (EE, SE) EE

Oracle Release 11.2.0.3

Real Application Cluster Y

Diagnostic Pack Y

DataGuard N

Flashback N

Database System

Configuration Customer System


SGA capacity [GByte] 32

SGA keep cache [GByte] 16

PGA capacity [GByte] 8

Block size [KByte] 8

NUMA support disabled



Benchmark Suite Customer System


Release Benchware BPS 8.0

PL/SQL Code native

Benchmark Suite

Benchmark Database Customer System


Benchmark Database size V – 1 TByte

#rows small table 32’000’000

#rows large table 1’024’000’000


Database Performance

0

100

200

300

400

500

600

700

800

900

1'000

1 2 4 8 16

ODA ASM3 un-comp

ODA ASM3 comp

Customer System

ODA ASM2 comp


Thro

ugh

pu

t in

[kr

ps]

Bulk load compressed versus un-compressed

• standard Oracle compression takes around double CPU resources (details next slide)

• compression ratio and REDO rate depends on customer data



Bulk load compressed versus un-compressed




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 94 T332 1 1 1 1.240E+05 0.000E+00 6 38 226 264 27 89 116 30 258

95 T332 1 1 1 1.260E+05 0.000E+00 6 36 225 261 27 90 117 31 254

96 T332 1 2 1 2.261E+05 0.000E+00 9 62 407 469 51 163 215 55 283

97 T332 1 4 1 3.657E+05 0.000E+00 14 97 657 754 84 265 349 89 350

98 T332 1 8 1 5.333E+05 0.000E+00 22 150 949 1099 134 398 532 130 480

99 T332 1 16 1 7.161E+05 0.000E+00 33 196 1200 1396 178 533 711 175 715




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 100 T331 1 1 1 1.730E+05 0.000E+00 4 47 365 412 38 143 181 51 185

101 T331 1 1 1 1.758E+05 0.000E+00 4 48 372 420 39 145 183 52 182

102 T331 1 2 1 2.540E+05 0.000E+00 6 82 556 638 72 225 297 76 252

103 T331 1 4 1 3.776E+05 0.000E+00 8 123 818 941 109 337 445 112 339

104 T331 1 8 1 5.322E+05 0.000E+00 11 176 1107 1283 159 480 639 158 481

105 T331 1 16 1 6.615E+05 0.000E+00 13 218 1237 1455 200 600 800 197 774 OD

A –

ASM

3

un

-co

mp

ress

ed

OD

A –

ASM

3

com

pre

ssed




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

2 21 T332 1 1 1 1.373E+05 0.000E+00 5 4 221 225 0 68 68 33 233

22 T332 1 1 1 1.345E+05 0.000E+00 5 5 214 219 0 66 67 33 238

23 T332 1 2 1 2.406E+05 0.000E+00 8 60 429 489 56 175 230 59 266

24 T332 1 4 1 4.156E+05 0.000E+00 14 100 746 846 96 301 398 101 308

25 T332 1 8 1 6.598E+05 0.000E+00 23 162 1158 1320 156 482 638 161 388

26 T332 1 16 1 9.343E+05 0.000E+00 34 241 1602 1843 232 694 926 227 548

OD

A –

ASM

2

com

pre

ssed


Benchmark Results



ASM3


ASM2

Data load bulk, un-compressed • Throughput DOP = 1

• Throughput max.

[rps]

[MBps] [rps]

[MBps]

175’000

145 661’000

800

216’000 (+ 23%)

131

Data load bulk, compressed • Throughput DOP = 1

• Throughput max.

[rps]

[MBps] [rps]

[MBps]

126’000

90 716’000

711

137’000 (+ 8.7%)

68 934’000 (+ 30%)

926

Max REDO rate during bulk load [MBps] 197 227

Summary Database Performance: bulk load



0

10

20

30

40

50

60

1 2 4 8 16 32 64

ODA ASM3 (1 node)

Customer System

ODA ASM2


Thro

ugh

pu

t in

[tp

s]

OLTP select transaction, primary key access, 32 rows per tx

4 ms 4 ms

5 ms

6 ms

7 ms

11 ms

• moderate transaction rate for small system with 20 HDD’s

• avg SQL service time < 1 s at DOP = 64

Number at measuring point: avg service time for single block 8 kByte random read

2 nodes 16 ms



OLTP select transaction, primary key access, 32 rows per tx




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 122 T616 1 1 1 7.060E+01 2.200E+00 5 272 96 368 63 1 64 0 58

123 T616 1 2 1 1.489E+02 4.700E+00 5 413 175 588 63 2 65 0 55

124 T616 1 4 1 2.979E+02 9.300E+00 5 675 320 995 60 3 62 0 55

125 T616 1 8 1 5.120E+02 1.600E+01 5 1092 535 1627 56 5 61 0 64

126 T616 1 16 1 8.192E+02 2.560E+01 5 1662 833 2495 54 7 61 0 80

127 T616 1 32 1 1.111E+03 3.470E+01 6 2205 1121 3326 54 9 63 0 118

128 T616 2 64 1 1.515E+03 4.740E+01 7 2877 1511 4388 48 12 60 0 173

Due to block cleanout there is a mixed random I/O picture with both: read and write

OD

A –

ASM

3

2-n

od

e C

lust

er




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

3 18 T616 1 1 1 1.130E+02 3.500E+00 1 246 116 362 2 1 3 0 290

19 T616 1 2 1 2.291E+02 7.200E+00 1 457 238 695 4 2 6 0 286

20 T616 1 4 1 4.215E+02 1.320E+01 1 822 421 1243 6 3 10 0 311

21 T616 1 8 1 7.282E+02 2.280E+01 2 1374 719 2093 11 6 17 0 360

22 T616 1 16 1 1.165E+03 3.640E+01 2 2069 1131 3200 16 9 25 0 450

23 T616 1 32 1 1.631E+03 5.100E+01 2 2808 1537 4345 22 13 35 0 643 OD

A –

ASM

2

Sin

gle

Inst

ance



0

1'000

2'000

3'000

4'000

5'000

6'000

7'000

8'000

9'000

1 2 4 8 16

ODA ASM3 (1 node)

Customer System

ODA ASM2


Thro

ugh

pu

t in

[rp

s]

OLTP insert transaction, 2 rows per tx

DBWR: 16 ms LGWR: < 1 ms

DBWR: 7 ms LGWR: < 1 ms

Number at measuring point: avg service time for DBWR and LGWR multiblock I/O

DOP 16 • log file sync time < 1 ms (95%) • avg SQL service time < 5 ms



OLTP insert transaction, 2 rows per tx




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 54 T311 1 1 1 2.500E+03 1.250E+03 7 16 1529 1545 0 6 7 4 50

55 T311 1 1 1 2.359E+03 1.179E+03 7 23 1429 1452 0 6 6 3 53

56 T311 1 2 1 3.521E+03 1.761E+03 9 18 2371 2389 0 10 10 5 71

57 T311 1 4 1 5.102E+03 2.551E+03 12 27 3512 3539 0 17 17 8 98

58 T311 1 8 1 6.369E+03 3.185E+03 16 27 3639 3666 0 22 23 10 157

59 T311 1 16 1 7.169E+03 3.584E+03 18 25 3070 3095 0 26 26 11 279

OD

A –

ASM

3




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

2 1 T311 1 1 1 2.907E+03 1.454E+03 5 19 1758 1777 0 7 7 4 43

2 T311 1 1 1 3.049E+03 1.524E+03 5 15 1819 1834 0 7 7 4 41

3 T311 1 2 1 4.464E+03 2.232E+03 7 11 2892 2903 0 11 11 6 56

4 T311 1 4 1 6.410E+03 3.205E+03 9 13 4019 4032 0 16 17 9 78

5 T311 1 8 1 7.407E+03 3.704E+03 11 6 4210 4216 0 20 20 11 135

6 T311 1 16 1 7.968E+03 3.984E+03 14 8 4235 4243 0 22 22 12 251

OD

A –

ASM

2



0

10

20

30

40

50

60

1 2 4 8 16 32 64

ODA ASM3 (1 node)

Customer System

ODA ASM2


Thro

ugh

pu

t in

[tp

s]

OLTP update transaction, primary key access, 32 rows per tx

Number at measuring point: avg service time for REDO log file sync

• moderate transaction rate for small system with 20 HDD’s

• avg SQL service time 0.72 s at DOP = 64

2 nodes < 1 ms

< 1 ms

< 1 ms

< 1 ms

< 1 ms

< 1 ms

< 1 ms



OLTP update transaction, primary key access, 32 rows per tx




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 129 T716 1 1 1 9.100E+01 2.800E+00 5 253 120 373 63 1 64 0 45

130 T716 1 2 1 1.743E+02 5.400E+00 5 398 206 604 61 2 63 0 47

131 T716 1 4 1 3.486E+02 1.090E+01 5 713 391 1104 59 3 63 0 47

132 T716 1 8 1 5.650E+02 1.770E+01 5 1113 622 1735 57 5 62 0 58

133 T716 1 16 1 8.738E+02 2.730E+01 5 1660 940 2600 56 8 64 1 75

134 T716 1 32 1 1.225E+03 3.830E+01 6 2260 1307 3567 54 11 65 1 107

135 T716 2 64 1 1.533E+03 4.790E+01 6 2768 1625 4393 46 13 60 1 171

OD

A –

ASM

3




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

3 25 T716 1 1 1 1.388E+02 4.300E+00 1 239 147 386 2 1 3 0 236

26 T716 1 2 1 2.632E+02 8.200E+00 1 450 276 726 4 2 6 0 249

27 T716 1 4 1 4.784E+02 1.490E+01 1 812 502 1314 6 4 10 0 274

28 T716 1 8 1 7.944E+02 2.480E+01 2 1348 834 2182 11 7 17 0 330

29 T716 1 16 1 1.197E+03 3.740E+01 2 2030 1244 3274 16 10 26 1 438

30 T716 1 32 1 1.646E+03 5.140E+01 2 2746 1692 4438 21 14 35 1 637

OD

A –

ASM

2


Benchmark Results



ASM3


ASM2

OLTP select tx, 32 rows per tx • Throughput (1 node / 2 nodes)

• SQL service time

[tps] [rps] [s]

35 / 47

1’111 / 1’515 1.00

51 / -

1’631 / - (+ 46%)

OLTP insert tx, 2 rows per tx • Throughput

• SQL service time

[tps] [rps] [s]

3’584 7’619

< 0.005

3’984 (+ 11%)

7’968

-

OLTP update tx, 32 rows/tx • Throughput • SQL service time

[tps] [rps] [s]

47

1’533 0.72

51 (+ 8%)

1’646 -

Summary Database Performance: OLTP transactions



Configuration of database files

Customer System


Cluster ASM3

Temp HDD


Single Instance ASM2

Temp HDD



Temp SSD

Oracle Data Files Hard Disk Drive Hard Disk Drive Hard Disk Drive

Oracle Temp Files Hard Disk Drive Hard Disk Drive Solid State Disk

Oracle REDO Log Files Solid State Disk Solid State Disk Solid State Disk

Database Performance: Data Aggregation

ASM3: ASM with high redundancy ASM2: ASM with normal redundancy



0

200

400

600

800

1'000

1'200

8 16 32 64

ODA ASM3 (HDD)

Customer System

ODA ASM2 (HDD)

ODA ASM2 (SSD)


Elap

sed

tim

e in

[s]

Data Aggregation



Data Aggregation




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

1 112 T522 1 1 8 0.000E+00 0.000E+00 8 833 811 1644 322 74 395 17 1078

113 T522 1 1 16 0.000E+00 0.000E+00 11 1140 832 1972 376 73 449 17 1067

114 T522 1 1 32 0.000E+00 0.000E+00 8 460 549 1009 265 48 313 11 1651

115 T522 1 1 64 0.000E+00 0.000E+00 20 1345 2421 3766 1035 223 1258 53 338




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

3 10 T522 1 1 8 0.000E+00 0.000E+00 13 1028 1941 2969 802 177 979 42 426

11 T522 1 1 16 0.000E+00 0.000E+00 14 1086 2096 3182 847 186 1033 44 403

12 T522 1 1 32 0.000E+00 0.000E+00 14 1047 2057 3104 815 177 993 43 417

13 T522 1 1 64 0.000E+00 0.000E+00 17 1337 2520 3857 1044 232 1277 54 328




--- --- ---- --- ---- ---- ----------- ----------- ---- -------- -------- -------- -------- -------- ------- ------ ------

4 1 T522 1 1 8 0.000E+00 0.000E+00 15 1294 2460 3754 996 220 1217 52 343

2 T522 1 1 16 0.000E+00 0.000E+00 18 1510 2862 4372 1160 248 1408 61 292

3 T522 1 1 32 0.000E+00 0.000E+00 21 1701 3252 4953 1296 276 1572 68 261

4 T522 1 1 64 0.000E+00 0.000E+00 22 1690 3172 4862 1316 280 1596 69 257

Runaway value at DOP = 32; query optimizer changes strategy within RAC cluster; use parallel_force_local = true to avoid this situation

OD

A –

ASM

3

Tem

p o

n H

DD

O

DA

– A

SM2

Te

mp

on

HD

D

OD

A –

ASM

2

Tem

p o

n S

SD



Data Aggregation

Foreground Wait Events DB/Inst: BENCH/bench1 Snaps: 233-234

Avg

%Time Total Wait wait Waits % DB

Event Waits -outs Time (s) (ms) /txn time

-------------------------- ------------ ----- ---------- ------- -------- ------

direct path read 64,765 0 1,074 17 736.0 22.9

direct path write temp 17,203 0 736 43 195.5 15.7

direct path write 15,751 0 639 41 179.0 13.6

direct path read temp 13,864 0 416 30 157.5 8.9

OD

A –

ASM

3

Tem

p o

n H

DD

Foreground Wait Events DB/Inst: ODATEST1/ODATEST1 Snaps: 191-192

Avg



-------------------------- ------------ ----- ---------- ------- -------- ------

direct path read 284,092 0 2,888 10 2,840.9 36.8

direct path write temp 33,855 0 1,515 45 338.6 19.3

direct path write 31,274 0 1,073 34 312.7 13.7


OD

A –

ASM

2

Tem

p o

n H

DD

Foreground Wait Events DB/Inst: ODATEST1/ODATEST1 Snaps: 258-259

Avg



-------------------------- ------------ ----- ---------- ------- -------- ------

direct path read 263,401 0 3,341 13 3,465.8 55.0

direct path write 31,928 0 996 31 420.1 16.4


direct path write temp 1,286 0 5 4 16.9 .1

OD

A –

ASM

2

Tem

p o

n S

SD


Benchmark Results



Cluster ASM3

Temp HDD



Temp HDD



Temp SSD

Create Bitmap Index

• Elapsed time DOP = 8

• Elapsed time DOP = 64

[s]

[s]

1078

338

426 (- 60%)

328 (- 3%)

343 (- 24%)

257 (- 21%)

Summary Database Performance: Data Aggregation

Temp tablespace on SSD reduces elapsed time of large disk sort up to 24%


1 About Benchware

2 CPU Performance




6 Conclusion

Contents


Conclusion

0

200

400

600

800

1000

1200

ODA Server ODA Storage Database License fortypical Starter

Database License forcomplete System

2 Server • 2 x 2 sockets, 3.06 GHz • 2 x 12 cores • 2 x 24 threads • 2 x 96 GB RAM • Oracle Linux 5.5

50k USD

JBOD SAS Storage (integrated) • 20 disks • 600 GB • 15k rpm • 4 x 73 Gbyte SSD 0 USD (integrated)

Oracle License • Enterprise Edition • Diagnostic Pack • Tuning Pack • 12 cores ~ 345k USD

Oracle Database Appliance All prices are list prices (spring 2012)

Oracle License • Enterprise Edition • Real Application

Cluster • Partitioning • Diagnostic Pack • Tuning Pack • 24 cores ~ 1’100k USD


Conclusion

• Robust engineered system since release OAK 2.2 - Ready to run within hours

- Built-in fault tolerance with Oracle Real Application Cluster (optional)

• Very fast CPU and server - Most CPU performance per Oracle license dollar

- Very fast in-memory SQL operations

- Sufficient RAM capacity for 2 socket server

• Flexible Oracle licensing based on activated cores - Currently only with Enterprise Edition but not with Standard Edition



Conclusion

• Superior storage read performance

• Use spare capacity on SSD drives for temporary tablespaces

• 3-fold ASM mirroring - Limited storage capacity (only 30% of raw capacity) - storage

expansion via NFS possible

- Moderate storage write performance for 20 HDD up to 3’500 IOPS

• 2-fold ASM mirroring - Up to 46% performance increase for write intensive operations

- Excellent storage write performance for 20 HDD up to 5’200 IOPS



Conclusion

• Bottom line - Excellent database server for small and medium sized mission-critical

applications



Conclusion

• Benchware uses fair, reproducible and representative benchmark tests delivering understandable key performance metrics (KPM)

• Benchware uses a list of defined price performance ratios (PPR) to evaluate platform cost

• Benchware publishes price performance ratios (PPR) to its customers only


www.benchware.ch

[email protected]

swiss precision in performance measurement


Further Reading

[1] Oracle Database Appliance - Data Sheet; Oracle Corp. 2011

[2] Oracle Database Appliance - An Oracle Technical White Paper; Oracle Corp. October 2011

[3] Oracle Database Appliance - Getting Started Guide, Release 2.2 for Linux x86-64; Oracle Corp. April 2012

benchmark oracle database appliance - · pdf fileoracle database appliance benchmark report...

Documents