connecticut cmg - demystifying oracle database capacity management with workload characterization

CCMG 2014

Cromwell (CT) – April 25th, 2014

Renato Bonomini – [email protected] Andrea Vasco – [email protected] Stefano Doni – [email protected]

Demystifying Oracle database capacity management with workload characterization

2 CCMG - Connecticut Computer Measurement Group 2014

§  Business-aware Capacity Management in a nutshell

§  Database modeling: common pitfalls

§  The workload characterization methodology

§  How to do it for Oracle databases

§  Results and Conclusions

Agenda


The Capacity Modeling a generic application can be approached focusing on different yet incremental scopes; ITIL defines a set of sub-processes such as:

§  Component Capacity Management: manage, control and predict the performance, utilization and capacity of IT resources and individual IT components

§  Business Capacity Management: translate business needs and plans into capacity and performance requirements for services and IT infrastructure, to ensure that future capacity and performance needs can be fulfilled

Capacity Management Approaches

HTTP requests

Web resources

Application Components

(servlet)

Business Logic

(java beans)

Database instances

Web transaction

Silo reporting

BA Approach


Component Capacity Management cannot:

§  Define and explain the capacity of a service in business terms (e.g. number of users)

§  Identify which component is the current bottleneck in the application

§  Optimize capacity at the application level (e.g. moving capacity between different layers)

Component capacity management sometimes is not enough

OS & Hardware

Middleware

Applications

Services

?

Will batch processes end timely? Is there capacity to manage +10% users?


Why do we need Business-Aware Capacity Management?

The goal is to express capacity in “business terms” which are

1.  easy to understand and to share with stakeholder (Business Service Owners, Marketing, etc.)

2.  Independent from the underlying technology and its changes

Q: Is this correlation always easy to find?


§  Front-end layers can be simply modelled using single business KPIs −  they are specialized for some well-defined business functions (e.g. application servers) −  the resource utilization is usually not workload dependant (e.g. HTTP request)

−  the workload composition is stationary

Front-end layers? Yes, it’s generally easy

R² = 0.97831 0%

20%

40%

60%

80%

100%

0 500 1000 1500 2000 2500 3000 3500 4000

Phys

ical C

PU co

nsum

ed

Purchase Orders Volume / Hour

Application Server Physical CPU consumed vs Purchase Orders Volume


§  Databases are inherently shared and their resources pulled in several directions − By multiple functions within the same application: the same database is typically used for both

online transactions, background batches, business reporting, etc.

− By multiple applications accessing the same database: quite often a single database is accessed by multiple applications at the same time, for different purposes

− By infrastructure consolidation: a single server might run multiple database instances

Database layers? The previous “simple” modeling would not fit…

R² = 0.51894 0

2

4

6

8

10

12

0 500 1000 1500 2000 2500 3000 3500

Phys

ical C

PU co

nsum

ed

Purchase Orders Volume / hour

Database Server Physical CPU consumed vs Purchase Orders Volume


Agenda







How the workload correlation usually degrades while traveling the different layers

0

0.5

CPU

core

s #

Application Server Physical CPU consumed

0

10

20

CPU

core

s #

Database Server Physical CPU consumed

0

1000

2000

3000

4000

# R

eque

st

Frontend Server Number of Request

HTTP requests

Front-end

Database layer

…


§  Single workload models for databases typically do not explain resource utilization

§  Statistically poor models can not be used for prediction purposes

What does it mean? “Simple” models do not work for databases

R² = 0.51894 0

2

4

6

8

10

12

0 500 1000 1500 2000 2500 3000 3500

Phys

ical C

PU co

nsum

ed

Purchase Orders Volume / hour

Database Server Physical CPU consumed vs Purchase Orders Volume

R² = 0.97831 0%

20%

40%

60%

80%

100%

0 500 1000 1500 2000 2500 3000 3500 4000

Phys

ical C

PU co

nsum

ed

Purchase Orders Volume / Hour

Application Server Physical CPU consumed vs Purchase Orders Volume


Agenda







§  The Utilization Law (Little’s Law) describes the relationship between workload intensity and resource utilization

Leveraging the Utilization Law

U = XnS

nn

∑ +U0

U resource utilization

U0

residual utilization

Xn

processed work (throughput)

Sn

service demand

§  The Utilization Law can be extended to include any number of workload classes; in the database modeling context, a workload class can represent a specific source of work we need to model:

U =X1S

1+X

2S

2+ ....+X

nS

n+U

0

Q: how can we derive the service demand of each workload class?


Estimate Si using U and Xi

§ Multivariate regression models to estimate service demands

§  Required when only the total resource utilization is known

§  Drawbacks − Not always feasible due to statistical

constraints (e.g. multicollinearity) − Lack of visual interpretation − Difficult to understand

Direct Measurement of Workload Demand

§  The single workload resource consumption can be measured − E.g. we know how much CPU is used for

business reporting, online transactions, receipt batches etc.

§  It is possible to manage each workload independently

Two service demand modeling approaches

“Divide et Impera” This is the approach we can use

U =X1S

1+X

2S

2+ ....+X

nS

n+U

0 then S

i = ?


Agenda







0

2

4

6

8

10

12

CPU

core

s #

Database Server Physical CPU consumed

Estimating resource utilization for each workload class

The selected approach relies on the ability to identify all of the different components contributing to the measured resource utilization

Oracle itself provides this information!


§  Oracle automatically tracks detailed resource consumption metrics, which are stored in circular buffers held in database memory and later flushed to disk for historical auditing

§ Metrics can be accessed with standard queries estimating resource consumption: − Per session

− Per SQL Statement

How to estimate the contribution on resource utilization for each component

It’s possible to slice resource consumptions at different levels: it depends on how the application works!

§  The identification of the impact of a workload can be derived for instance by considering: −  the specific user the application is using −  the machine generating the request


§ Metrics are stored into: − V$ACTIVE_SESSION_HISTORY view for about 1 hr − DBA_HIST_ACTIVE_SESS_HISTORY table for 7 days

§  Statistical analysis of sessions: − Session State (ON CPU, WAITING) − Wait Class, Event (User I/O, file scattered read, …)

− Application (Program, Module, Action, …) − Number of IO operations / bytes transferred

during the interval

§  How to aggregate the application sessions to get the total number of CPU time consumed? − count the concurrent number of sessions

(the ones that are ‘ON CPU’) − sum all the CPU times within the desired time

window (i.e. one hour), getting the total CPU busy time in seconds

Resource consumption components separation: an estimation method

SELECT TO_CHAR(sample_time, 'yyyy/mm/dd HH24') as sample_time, username, round(sum(DECODE(session_state,'ON CPU', DECODE(session_type,'BACKGROUND',0,1),0))/60/60*10,2) AS cpu_secs FROM DBA_HIST_ACTIVE_SESS_HISTORY a, dba_users b WHERE sample_time > sysdate – 7 and a.user_id=b.user_id GROUP BY TO_CHAR(sample_time, 'yyyy/mm/dd HH24'), username


Agenda







Workload characterization is an essential step in capacity management: if properly conducted, it can provide remarkably good models for complex contexts.

Single workload models work for Oracle Databases

R² = 0.96666 0.0

0.2

0.4

0.6

0.8

1.0

1.2

1.4

1.6

1.8

2.0

0 500 1000 1500 2000 2500 3000 3500

Estim

ated

Phy

sical

CPU

cons

umed

Purchase Order Volume / hour

Database Server Estimated Physical CPU consumed vs. Purchase Orders Volume


Questions?


Thank you!

US Office One Boston Place, Suite 2600 Boston, MA 02108 T: +1-617-936-0212

Headquarters Via Schiaffino, 11 20158, Milan Italy T: +39 02 4951 7001

@moviri

moviri

moviri

connecticut cmg - demystifying oracle database capacity management with workload characterization

Data & Analytics

future capacity

single database

business reporting

business needs

business terms

nutshell database modeling

single server

single business kpis