oaktable world 2014 kevin closson: slob – for more than i/o!

1 © Copyright 2014 EMC Corporation. All rights reserved. © Copyright 2014 EMC Corporation. All rights reserved.

SLOB Topics for Oaktable World 2014

Kevin Closson Chief Performance Architect

1 © Copyright 2014 EMC Corporation. All rights reserved.


xtremio.com/slob


>$100,000,000 <6 months >40PB sold

#1 All-Flash Array in the Market XtremIO

5 © Copyright 2014 EMC Corporation. All rights reserved.


Agenda �  The obvious things you can do with SLOB �  SLOB without physical I/O?

�  SLOB for Oracle Database 12c In-Memory Option?

�  SLOB for Oracle Multitenant?

�  SLOB to test Oracle Database contention?

�  A peek: Testing 11g on Windows 2012 with SLOB


Typical Platform Testing With SLOB


..but first, quick SLOB fundamentals...


SLOB Fundamentals �  Single design center

–  Maximum SQL execution with minimum CPU utilization –  Maximum physical I/O per unit of DB CPU

�  No application contention –  Schema per session –  Single row per block –  100% random data access


SLOB Fundamentals �  Predictable and Repeatable

–  Results vary less than 3% in the worst case. Generally 1-2% variation

–  Semaphore trigger. Timed-completion or fixed SQL execution count

–  SELECT/UPDATE workload. Tables never need reloaded, single index never needs maintenance


SLOB Fundamentals

�  Predictable and Repeatable

–  Synchronous reads

▪  Key Performance Metrics (Executions/sec, PIOPS, etc) directly tied to I/O service times


SLOB Fundamentals �  Simple

–  All you need ▪  A tablespace to store the SLOB schemas

▪  Enough storage to store ~4:1 ratio of SLOB data to db_cache_size —  Example: 1GB db_cache_size, 32 Sessions, for 4GB use: slob.conf->SCALE=16384 —  16384 * 8192 * 32 == 4GB

▪  Connection by bequeath or SQL*Net


SLOB Fundamentals �  SLOB is a Linux kit, not ported

–  Use Oracle client on Linux

▪  Small physical or virtual as the driver to test Windows, AIX, Solaris, etc

▪  The client does not require CPU during test execution. It’s 100% foreground PL/SQL


Testing Physical IOPS With SLOB

Examples based on EMC XtremIO


Single Host 2s16c32t Redo Payload vs LGWR Write Latency - Tip: Tweak SLOB/procedure.sql


Single Host 2s16c32t Redo Payload vs Random Read Latency


Cached SLOB


SLOB: slob.sql


Cached SLOB – Study CPU Scalability 2s16c32t E5-2600

3020 5215

24397

34150

41657

49059

55820

0

10000

20000

30000

40000

50000

60000

0 5 10 15 20 25 30 35 40 45

SQL EXECUTIONS/Second

EXECUTES



776967 1341608

6280211

8791012

10723697

12629035

14369615

0

2000000

4000000

6000000

8000000

10000000

12000000

14000000

16000000

0 5 10 15 20 25 30 35 40 45

LIOPS

LIOPS


Cached SLOB Focus on Intel SMT


Understand Intel SMT


Perf(1) top - skinny.sql


Perf(1) top – fat.sql


2-socket x86 server performance in the last decade

•  With CPU threading skinny.sql and fat.sql are oil and water as skinny forces fat to run on a thread == starvation since fat stalls less (see SQL)

•  Same number of “noise” sessions

•  Top case N fat.sql •  Bottom case N skinny.sql

+ 1 fat.sql.


In-Memory Column Store


SLOB – A Focus on In-Memory Database �  Study OLTP / In-Memory Database Option co-

hosting

–  In-memory cache population (ingest) rates

–  Think “data flow”. Who is a producer and who is a consumer ▪  inmemory_max_populate_servers and PQO slaves

–  Co-hosted with OLTP


Top(1) – In-Memory Database Option


SLOB – A Focus on In-Memory Database


Multitenant Oracle


SLOB – A Focus on Oracle Multitenant

�  Use SLOB to study Oracle Multitenant

�  What better way to understand a simple premise than to rely on a simple, predictable workload and test kit?


SLOB – A Focus on Oracle Multitenant �  http://www.oracle.com/technetwork/database/multitenant/

learn-more/oraclemultitenantt5-8-final-2185108.pdf, pg 23:

�  Hold it. To achieve great savings in CPU consumption, doesn’t that mean we must all accept the notion that background processing consumes a significant amount of CPU bandwidth?

“In Oracle Multitenant, great savings in CPU consumption arise from the sharing of background processes among databases, which can significantly reduce the amount of cores needed to support a given number of databases. “


SLOB – A Focus on Oracle Multitenant �  Background CPU

–  Scrutinize with SLOB. Know your platform. Now your costs and rates. –  We are looking at BG behavior so this is UPDATE intensive SLOB –  2s20c40t E5-2690v2 host. –  This is a single database!


SLOB – A Focus on Oracle Multitenant �  Testing to further understand a simple assertion.

�  The assertion: –  Consolidating from multiple databases/instances into a

single Oracle Multitenant deployment significantly reduces CPU overhead due to shared background processing


Multitenant – Huge CPU savings? Simple assertion, simple baseline?


Multitenant – Huge CPU savings? �  Testing to further understand a simple assertion. �  Should be simple enough, right?

–  SLOB – Simple to use, simple to understand. ▪  40 simple schema users with zero data sharing – simple workload ▪  40 databases each with

—  A single SLOB session —  slob.conf->SCALE=668734 (5GB 100% randomly visited active data set) —  slob.conf->UPDATE_PCT=25 (SQL mix 75% SELECT, 25% UPDATE) —  Simple init.ora (next slide) —  slob.conf->WORK_LOOP=100000 —  Test on a 2s20c40t E5-2690v2 server

�  The metric is job completion – fixed workload.


Multitenant – Huge CPU savings? �  Testing to further understand a simple assertion. �  The metric is job completion – fixed workload.

�  Which model pushes through 4,000,000 SQL executions faster?


Testing Database Contention With SLOB?


SLOB – Testing Database Contention �  slob.conf->SHARED_DATA_MODULUS

oaktable world 2014 kevin closson: slob – for more than i/o!

Software