consolidate with oracle exadata...pros and cons! • multi-tenancy-multiple pluggable databases...

Consolidate with Oracle Exadata Manage Resources and Availability - CON7770

Sue K. Lee, Director of Development René Kundersma, Consulting Member of Tech. Staff Oracle Server Technologies Oct 1, 2014

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |


Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


Program Agenda

Introduction to Consolidation

Oracle Exadata - Optimized for Consolidation and DBaaS

HA Tiers and Consolidation Planning Best Practices

Operational Best Practices in a consolidated environment

Managing Resources in Consolidated Environments

1

2

3

4

5


Why Consolidate?

• Efficient server and storage utilization – Each generation of servers and storage is more powerful – Typical database workload may not fully utilize hardware

• Database workloads are often bursty, with long periods of low utilization – Lots of test, development, and (non-)critical databases – reduce hw footprint

• Fewer systems to administer

– Reduce maintenance efforts


Consolidation Challenges

Bridge not redundant = Single Point Of Failure More tenants than resources


Consolidation Challenges • HA challenges

– Conflicting HA requirements – Planned maintenance ‘difficult’

• Database users apprehensive about consolidation – Users want security, isolation and performance guarantees

• Workload surges from one application can affect others – Excessive CPU, PGA, or I/O usage – Surges can originate from heavy application usage or a runaway query

• DBAs want to control resource usage – ‘Get what you pay for’ and get consistent performance


Consolidation Methodologies

• Schema Consolidation – Multiple applications share a database

• Server Consolidation – Multiple databases share a server

No “right” approach!

Each approach has its pros and cons!

• Multi-Tenancy -Multiple Pluggable Databases share a Container

Single O/S, No VMs Needed

Multitenant Database Billing

PDB

Parts

PDB Sales

PDB

Assets

PDB

Single Physical Container Database


Program Agenda






2

1

3

4

5


No Performance Bottlenecks for Consolidation and DBaaS Query offload in storage

– Data intensive query operations offloaded to storage CPUs

– 100 GB/sec SQL data throughput – Storage Index data skipping

Database storage compression – Hybrid Columnar for 10x DB size

reduction and faster analytics

Database optimized PCI Flash – Smart caching of database data – 2.66 Million Database IOs/sec – Smart Flash log speeds transactions

Database optimized and comprehensive resource management – End-to-end prioritization from application to DB to network to storage

–Prioritization of CPU and IO by multitenant pluggable database (12c)

Database optimized messaging – SQL optimized InfiniBand protocol for high throughput low latency SQL

–End-to-End prioritization of critical database messages (11.2.0.4), including log writes and RAC

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | 12

Application Servers OLTP/DW Load Drivers X4-2 / 250Gb memory

Exadata X4-2 Full Rack

End User

Consolidation Test: Exadata vs. Non-Exadata

Application Servers OLTP/DW Load Drivers X4-2 / 250Gb memory

Database Servers

End User


Exadata for OLTP: 4X Consolidation Density

Challenge: How many more databases can be consolidated on Exadata, compared to a fast x86 system? Approach: Compare the same Exadata system, with and without Exadata features* enabled, increasing databases until saturation. Results: 160 databases (CPU-bound) on Exadata, compared to 40 databases (I/O-bound) on equivalent x86 hardware. Exadata consolidates 4X as many databases with faster transaction response times.

*Includes Exadata Smart Flash Logging, Smart Flash Cache, Smart Scan, Smart Flash Cache compression, Storage Indexes, Network RM, IORM


Exadata for Mixed Workloads: 15X Faster

Challenge: How much faster can Exadata execute mixed workloads, compared to a fast x86 system? Approach: Compare the same Exadata system, with and without Exadata features* enabled, when Exadata is undersubscribed. The workload is OLTP plus a reporting Data Warehouse. Results: Exadata responds 15X faster with over 6X more transactions while still consolidating twice as many databases.

*Includes Exadata Smart Flash Logging, Smart Flash Cache, Smart Scan, Smart Flash Cache compression, Storage Indexes, Network RM, IORM


Program Agenda






2

1

3

4

5


Consolidation Principles

Setup hardware pools.

Migrate one database at a time.

Start Smart

Manage Resources from the start

Consolidate Standardize Monitor and Manage

Set up HA reference

architectures and map databases to tiers


Exadata MAA Portfolio

Products & Features • Real Application Clusters

• Automatic Storage Mgmt

• Data Guard

• Active Data Guard • GoldenGate • Application Continuity

• Flashback

• Global Data Services • Oracle Secure Backup

Redundant database servers, storage

servers, network, power

Active standby systems, local HA and/or remote DR

Log-based data replication with data

consistency validation

Online patching, re-configuration

and rolling upgrades

Active-active DB clusters. Mirrored storage and flash.

Local Standby

Remote Standby

LAN WAN

http://oracle.com/maa


What do I really need ? Where to Start? – Step1

What solutions to use ? Define key HA requirements: RTO, RPO and DR time first !

RPO

RTO

Main-tenance window

DR Time

Require-ment


Planning for Exadata Consolidation – Step 2 High Availability Tiers for Unplanned and Planned Outages

Unplanned Outages (local site)

Planned Maintenance Data Protection Unrecoverable local

outages and DR

Bronze Single instance with auto

restart for recoverable instance and server failures

Some online, most offline

Basic runtime validation combined with manual

checks

Restore from backup, potential to lose data

generated since last backup



outages and DR

Silver HA with automatic failover Some rolling,

some online, and some offline


checks







checks





outages and DR

Gold Comprehensive HA/DR All rolling or online Comprehensive runtime

validation combined with manual checks

Real-time failover, zero or near-zero data loss




checks







checks





outages and DR

Platinum Zero outage for platinum ready applications

Zero application outage

Comprehensive runtime validation combined with manual checks

Zero application outage for platinum-ready

applications, in-flight transactions are preserved,

zero data loss

Gold Comprehensive HA/DR All rolling or online Comprehensive runtime

validation combined with manual checks

Real-time failover, zero or near-zero data loss




checks







checks




Oracle MAA Availability Tiers Reference Architectures

BRONZE

SILVER

GOLD

PLATINUM

Single Instance

Replication (GG/DG)

Backups

App Cont. / GDS

Clusters

Backups

Clusters

Clusters and Replication


Planning for Exadata Consolidation – Step 3 Assign Databases to Availability Tier

Apps IT

I need a database for the new HR

system

I require HA with automatic

failover

Apps QA

I need to copy a production

database for testing

The cheapest configuration is

fine. This is just for

testing

BRONZE

SILVER

GOLD

PLATINUM

Single Instance

Replication

Backups

Clusters

Backups

Clusters

Clusters and Replication

Appl. Cont.


• Hardware Pool:

– A machine or group of machines used as target consolidation platform

• Guidelines – Do not assign different tiers to the same hardware pool – Minimum: Exadata X4-2 Half Rack

• Critical smaller Hardware Pools should use standby – When more capacity is required than provided by a single

hardware pool, divide the target databases in that category into two separate groups

Planning for Exadata Consolidation – Step 4 Assign tiers to their hardware pool


Program Agenda






2

1

3

4

5


Consolidation and Large Environments Best practice for consolidated environments

Consolidation GI version >= DB version

Required to 4th digit, recommended to 5th digit

Advance diskgroup compatible.rdbms with care

Once advanced cannot be reset

Share database homes whenever possible

Partially extend /u01 size (leave snapshot room)

Patch Out-of-Place

Move databases individually to new home

Large Environments Mixed Exadata version supported

Recommended only during rolling upgrade

Split updates across multiple maintenance window

In general – upgrade down the stack

1. Grid Infrastructure / Database

2. Exadata Database Servers

3. Exadata Storage Servers

4. InfiniBand switches


Role Separation and ASM/DB scoped security Segregating databases from each other in a platform consolidation environment

To separate Grid/ASM administration from database administration, request a “job role-separated environment” for the initial Exadata installation

Especially effective to manage multiple administrators

Privileges on griddisk level

Restrict griddisks to certain clusters and/or certain database(s). Exadata ASM v.s. database-scoped security

CellCLI> create key 4bb27f70c17f88232c936ed1fc437049 CellCLI> ASSIGN KEY FOR +ASM= 4bb27f70c17f88232c936ed1fc437049’ CellCLI> ALTER GRIDDISK ALL availableTo=\'+ASM\’ Place key on the database server.


Maintain Stability of the environment Apply consolidation best practices when adding databases:

Huge-pages

Minimize process count

Set ASM process count

Adjust SEMMSL and SEMMNS

Instance Cage the DWH workload

Use iorm objective=AUTO

Manage resources (CPU, Disk and Flash)

1. Exachk

4. Change

Operations 2. Monitor /

Calculate 4. Add / Validate

3. Test / Validate

Settings to review:


• Reduce Planned Maintenance Risk and

Downtime with Data Guard 1. Update standby system then test 2. Switchover then update new standby

Reduce Risk and Downtime with Data Guard Best data protection for consolidated environments ‘Gold and Platinum’

Standby First Apply to BP, PSU, CPU and PSE Max 1 year in between

Example: 11203BP5=>11203BP9

Transient Logical Standby / GoldenGate Major Database Patch sets

Example: 11.2.0.4 => 12.1.0.2 (DBMS_ROLLING starting 12.1.0.2)


Program Agenda






2

1

3

4

5


Performance Tiers

BRONZE

SILVER

GOLD Low consolidation High performance isolation

High consolidation Medium performance isolation

Highest consolidation Lowest performance isolation

PLATINUM Lowest consolidation density Highest performance isolation

Private Jet

1st Class

Economy Class


Resource Contention Databases consolidated on common hardware contend for • CPU • Memory • Flash Space and Bandwidth • Disk Bandwidth

How can we guarantee resources for each database and achieve good consolidation density?


Managing CPU


The Need for Instance Caging

0

10

20

30

40

50

60

70

80

90

100

Marketing Support

Your database starts with enough CPU, resulting in

good performance!

And then another database hogs the CPU, leaving you with

not enough CPU and bad performance…


Manage CPU with Instance Caging

0

10

20

30

40

50

60

70

80

90

100

MARKETING SUPPORT

“Cage” a database by limiting the amount of CPU it can use.

Step 1: Set “cpu_count” to the max CPU threads the instance can use at any time alter system set cpu_count = 8;

Step 2: Set “resource_manager_plan” to enable CPU Resource Manager alter system set resource_manager_plan = ‘default_plan’;


Partition Approach for Gold Databases

0

4

8

12

16

20

24

28

32 • Partition CPUs among the database instances – sum(cpu_counts) <= # cpu threads

• Partitioning provides maximum isolation – No CPU contention between instances

• Best for performance-critical databases

Instance A: 8 CPUs

Instance B: 8 CPUs

Instance C: 4 CPUs

Instance D: 4 CPUs

But if Instance A is idle, its CPU allocation is unused…

This server has a total of 24 CPU

threads

Instance B’s cpu_count


0

4

8

12

16

20

24

28

32

• Over-subscribe the CPUs among the

database instances – sum(cpu_counts) <= 3 x # cpu threads

• Over-subscribing provides efficient CPU utilization – Some contention for CPU if databases are

sufficiently loaded

• Best for non-critical databases

Instance A: 8 CPUs

Instance B: 8 CPUs

Instance C: 8 CPUs

Instance D: 8 CPUs

Over-Subscribe Approach for Bronze Databases This server has a total of 24 CPU

threads

Instance B’s cpu_count


0

4

8

12

16

20

24

28

32

Instance B: 8 CPUs

Instance C: 8 CPUs

Instance D: 8 CPUs

Over-Subscribe Approach for Bronze Databases

If Instance A is idle, there is no CPU contention!

The databases can fully utilize the server.


threads


0

4

8

12

16

20

24

28

32

Instance A: 8 CPUs

Instance B: 8 CPUs

Instance C: 8 CPUs

Instance D: 8 CPUs

Over-Subscribe Approach for Bronze Databases If all databases are active, there is

some contention. This is your maximum excess load.

Without Instance Caging, there is no limit on your maximum load!


threads


Instance Caging

• Keep the strategy simple – Initial cpu_count settings can be a guess

• Monitor actual CPU usage and then tweak – cpu_count can be adjusted dynamically – no database bounce needed! – If over-subscribing, monitor the server to make sure it’s not over-loaded – Avoid large changes to cpu_count

• cpu_count corresponds to CPU threads, not cores!

• Beware of over-subscribing on SPARC T-Series processors • Don’t set cpu_count to 1

– Makes database instance susceptible to hangs or poor performance

Best Practices


Managing Memory


512 GB of RAM

How Is Physical Memory Used?

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

OS Page Tables

Kernel Memory

Your physical memory should look like this!


512 GB of RAM

How Is Physical Memory Used?

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

OS Page Tables

Kernel Memory

Excessive memory usage causes paging, leading to performance problems,

leading to RAC evictions.

If you see “WARNING: Heavy swapping observed on system” in the alert log, take action! (11.2.0.4+)


512 GB of RAM

Memory Configuration – Huge Pages

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

Kernel Memory

OS Page Tables

Use Huge Pages! See MOS notes #361468.1 and #401749.1.

With huge pages, shrink OS page tables from 8 GB to 16 MB!

And boost performance!


512 GB of RAM

Memory Configuration – SGA

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

OS Page Tables

Kernel Memory

Configure SGA with SGA_TARGET and huge pages.

(MEMORY_TARGET doesn’t support huge pages.)

Coordinate SGA_TARGET and huge page configuration. PGA cannot use huge pages, so excessive huge pages waste memory.


512 GB of RAM

Memory Configuration – PGA

• Who obeys PGA_AGGREGATE_TARGET? – Good citizens: hash joins, sorts (“tunable” operations that can spill to temp space) – Bad citizens: parallel queries with high DOPs, badly behaved PL/SQL

• Monitor v$pgastat – “maximum PGA allocated” – historical max – “total PGA allocated” – current usage

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

OS Page Tables

Kernel Memory

PGA_AGGREGATE_TARGET.

Actual PGA usage. Can be 3x bigger than

PGA_AGGREGATE_TARGET!


512 GB of RAM

Memory Configuration – PGA

SGA DB #1

SGA DB #2

SGA DB #3

PGA DB #1

PGA DB #2

PGA DB #3

OS Page Tables

Kernel Memory

PGA_AGGREGATE_TARGET.

PGA_AGGREGATE_LIMIT is a hard limit

• PGA_AGGREGATE_LIMIT New in 12c

• PGA usage will not exceed this hard limit!

• Biggest untunable memory users are throttled and eventually killed

• Default value based on available memory and pga_aggregate_target


Managing Exadata Flash


Exadata Smart Flash Cache • Exadata Flash provides impressive capacity and bandwidth*

– 45 TB space – 100 GBps of scan throughput – ~2M IOPS

• Use Exadata Flash to – Cache frequently-used OLTP data – Accelerate scans, using excess flash resources – Accelerate log writes * For a full X4-2/8 rack


Exadata Smart Flash Cache

• Other market offerings – All-flash storage: expensive – Disks with flash to accelerate writes and cache recently-used data: doesn’t support

mixed workloads

• Only Exadata guarantees that flash stores frequently-accessed OLTP data – Scans must not evict OLTP data from the cache – Only Exadata can identify OLTP from scan IOs! – All-flash storage guarantees this too, but at many times the price!

Why Exadata?



How can you capitalize on 100 GBps of flash bandwidth?

• Use up to 20% for OLTP workloads – Maximum sustainable rate by OLTP

• Use the rest for scans! – Pre 11.2.2.3, mark tables with KEEP – 11.2.2.3+, scans automatically use flash if there’s space

• Scans use both flash and disks for maximum throughput – Using flash increases scan throughput by up to 5x!

Flash Bandwidth

OLTP

Scans

OLTP’s peak load only consumes 20% of flash

throughput

Use excess throughput for

scans!

Flash Bandwidth



For most workloads, Exadata already uses flash space optimally. To customize, use the Inter-Database IORM Plan

Space Contention

Inter-Database IORM Plan Use Flash Log? Use Flash Cache?

DB #1 DB #2 DB #3

Flash Logs use just 0.02% of total space. Don’t disable!

Disabling flash cache may cause a

heavier disk I/O load


Managing Disk


Use Case for IORM: Scan Workloads

Your disk utilization is around 50% with 1 database.

Should you consolidate and

add another database?

Disk Utilization by Database 0

10

20

30

40

50

60

70

80

90

100

Sales

Disk Utilization by Database


Non-Exadata Storage Use Case for IORM: Scan Workloads

0

10

20

30

40

50

60

70

80

90

100

Marketing

Sales

Disk Utilization by Database

The new database hogs the disk bandwidth.

Without Exadata IORM, databases issuing the most load get the most

bandwidth.

The original database sees a sudden drop in throughput.


Exadata Storage Use Case for IORM: Scan Workloads

0

10

20

30

40

50

60

70

80

90

100

Marketing

Sales

Disk Utilization by Database IORM ensures

predictable throughput for each database.

One database can use excess disk bandwidth

without affecting the other!


Exadata IORM To enable IORM

• Set IORM objective from “basic” to “auto”, using CellCLI or EM Cloud Control 12c CellCLI> alter iormplan objective = auto

• Configure a resource plan

By default, IORM runs in “basic” mode, which ensures reasonable latencies for

OLTP I/Os.

Inter-Database IORM Plan

Database Shares Utilization Limit

Guaranteed Disk Utilization

Maximum Disk Utilization

Sales 2 50% 100%

Support 1 25% 100%

Marketing 1 50% 25% 50%

Default 1 25%

Shares specify the relative importance of

each database.

A database with 2x shares can consume 2x

the disk bandwidth.


Exadata IORM To enable IORM

• Set IORM objective from “basic” to “auto”, using CellCLI or EM Cloud Control 12c CellCLI> alter iormplan objective = auto

• Configure a resource plan

By default, IORM runs in “basic” mode, which ensures reasonable latencies for

OLTP I/Os.

Inter-Database IORM Plan

Database Shares Utilization Limit

Guaranteed Disk Utilization

Maximum Disk Utilization

Sales 2 50% 100%

Support 1 25% 100%

Marketing 1 50% 25% 50%

Default 1 25%

You can also place a hard limit on the disk I/O

utilization for a database.

Primarily for cloud deployments for “pay for

performance”.


Use Case for IORM: High Disk Latencies for OLTP % of Waits ----------------------------------------------- Total Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- cell single block physical 213.7 90.1 3.3 .8 .1 .1 2.0 3.6 log file parallel write 951.5 93.9 2.6 1.3 1.1 1.0 .0 .0

• On Exadata, most OLTP I/Os are serviced from flash, except – Flash cache misses – Log writes when flash is busy

• During a smart scan, OLTP disk I/Os can be very slow

• Enable IORM to avoid outliers. Use the “objective” knob to enforce low disk latencies.


Non-Exadata Storage Use Case for IORM: High Disk Latencies for OLTP

OLTP

Parallel Execution

Table Scan

O/S Disk I/O Queue 200 ms wait time

A smart scan can issue a flood of IOs, dominating the front of the queue.

Long waits for OLTP IOs mean long latencies.

I/Os are processed in the order received. The O/S usually lets small OLTP I/Os cut towards the front of the queue.

But waiting behind the I/Os in front can still take 100s of ms.


Exadata Storage Use Case for IORM: High Disk Latencies for OLTP

Exadata Disk Exadata IORM IO Queue

Exadata IORM can put a new IO anywhere in the

queue!

IORM tags every IO: • Database, PDB, Consumer Group id • Background or foreground? • Buffer cache or smart scan?

Exadata IORM controls the order of the queue, based on the Resource Plan.

OLTP

Parallel Execution

Smart Scan


Managing Standby Databases


Resource Manager for Standbys Primary

Database Standby

Database Database X

2) Redo and Apply

3) Read-Only Queries

4) DB X’s Workload

1) Primary DB Workload

Issue #1: If synchronous redo transport is enabled,

the Primary DB will suffer from slow redo

writes on the Standby

can slowdown

Issue #2: Heavy read-only queries and other database

workloads can slow the Standby’s redo and apply

can slowdown


Resource Manager for Standbys

• Enable CPU and I/O resource management – Set a database resource plan on Standby – Use the primary’s resource plan or DEFAULT_PLAN (best practice - use the same resource plan in case

a switch-over occurs!) – Enable I/O Resource Manager (alter iormplan set objective = auto)

• On physical standbys, all resource manager objects are replicated on the standby – New resource plans cannot be configured on the standby – The primary and standby can use different resource plans – See MOS note 1930540.1 for known issues for consumer group mappings

• On logical standbys, all resource manager objects must be recreated

Prioritize Redo and Apply over Read-Only Queries


Resource Manager for Standbys

• Enable Instance Caging for all database instances on the Standby’s server – CPU_COUNT must be sufficiently high to handle the load in the event of a switch-over

• Enable inter-database IORM for all databases on the Standby’s storage cells – Create the allocation using standby’s DB_UNIQUE_NAME – See MOS note 1930540.1

Guarantee Resources for the Standby


References / Additional Resources

Oracle.com/maa

64

• MAA Best Practices for Database Consolidation and Oracle Multitenant with Oracle 12c • Best Practices For Database Consolidation On Oracle Exadata Database Machine • Oracle MAA Reference Architectures - The Foundation for Database as a Service • Oracle Exadata Database Machine Consolidation: Segregating Databases and Roles • Application Continuity with Oracle Database 12c

• http://tinyurl.com/kruf7pe • http://tinyurl.com/meh3wku • http://tinyurl.com/lwm9994 • http://tinyurl.com/oxlonht • http://tinyurl.com/o46hvuq

• Use Oplan and out-of-place patching • Utilize standby-first patching • Best Practices for Corr. Detection, Prevention and Autom. Repair in a Data Guard Config • Exadata Database Machine Software and Hardware Maintenance Planning Guide • Master Note for Oracle Database Resource Manager

• MOS 1306814.1 • MOS 1265700.1 • MOS 1302539.1 • MOS 1461240.1 • MOS 1339769.1

Support.oracle.com

consolidate with oracle exadata...pros and cons! • multi-tenancy-multiple pluggable databases...

Documents