Oracle Times Ten InMemory Database Architecture Performance Tips

Oracle Times. Ten In-Memory Database Architecture, Performance Tips, Use Cases Chris Jenkins (chris. jenkins@oracle. com) Senior Director, In-Memory Technology Times. Ten Product Management Copyright © 2018, 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. Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 3

Best In-Memory Databases: For Both OLTP and Analytics In-Memory for OLTP Application Oracle Times. Ten In-Memory Database Application • Lightweight, highly-available IMDB Application • Primary use case: Extreme OLTP • Microsecond response time • Millions of TPS on commodity hardware In-Memory for Analytics Oracle Database In-Memory Option • Dual-Format In-Memory Database • Primary use case: Real Time Analytics • Billions of Rows/Sec scan rate • Faster mixed-workload enterprise OLTP Ø Fewer indexes needed to support analytics Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 4

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

Oracle Times. Ten In-Memory Database One product, two deployment modes Times. Ten Scaleout Times. Ten Classic Application read/writes Active Application Reads from Standby Application Reads from Subscribers Subscriber • Replicated In-Memory Relational Database • Highly Available • Extremely low latency reads and writes • Read scaling across multiple hosts • Cache Groups (cache for Oracle DB EE) Single System Image In-Memory Database • Scale-Out In-Memory Relational Database • Highly Available • Extremely high throughput reads and writes • Scales both reads and writes Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 7

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

Times. Ten Classic Persistent and Recoverable In-memory RDBMS – – Pure in-memory Entire database in RAM ACID compliant Standard SQL and APIs – Database persisted on local storage – Automatic recovery after failure Highly Available Extremely Fast – Low, consistent response time – Very high throughput – Excellent scalability – Active-standby and multi-master replication – Highly parallel, high throughput – Async and Sync – HA and DR Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 9

Architecture: Classic Instance • Installation Client-Server Applications and Tools Created using tt. Instance. Create A runnable copy of the software Linked to an installation Includes configuration files Identified by TIMESTEN_HOME Set of processes Supports one or more databases TT Client Oracle RDBMS Network • Instance – – – – Times. Ten Replica DB Application Server daemon Application Code Times. Ten Data Manager Library Direct mode Applications and Tools Times. Ten daemon Instance Server proxies Replication agent(s) In-Memory Database(s) Millionths of a Second Metadata, Tables, Data Tables, Indexes, Views, Indexes, System Tables Sequences, … Cache Millionths agent(s) of a Admin/Utility Second programs Data Store subdaemon(s) Checkpoint files Log Buffer Log files Locks, Cursors, Command cache, Temp Indexes, … – An unzipped copy of the Times. Ten software package – Immutable Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 10

Application Connectivity Two modes, functionally equivalent , supported for all APIs (ODBC, OCI, JDBC, ODP. NET, …) Host 2 Host 1 App 1 Database App 2 … App N Shared Memory TT Server 1 App 1 TT Server 2 App 2 … … TT Server X You can mix and match these modes as desired based on your requirements. App N Network Direct mode • • Apps run on same host as database Apps directly map database shared memory (via TT engine) No context switches, no IPC for database access Ultra low latency (in process direct memory access) Client/server mode • • TCP/IP connections between apps and TT server processes TT server process is a multi-threaded direct mode app Each interaction involves 1 or more n/w round trips More overhead, higher latency than direct mode Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 11

Memory Layout Database is a single shared memory segment (separate much smaller segment for PL/SQL) ~20 MB Perm. Size Perm Region D b H d r Temp. Size Temp Region Log Buffer Metadata Temp objects • System catalog tables • Tables • System views • Indexes • Sequences Sort space • System. Database data Shared Memory Segment Locks Tables • Logical Tuple Pages Connections • Physical Tuple Pages Commit • Page directories buffers Indexes • Hash buckets • B-Tree nodes … Persistent Log. Buf. MB … Strand 1 Times. Ten is a row store • At least for now… • Recommended unless database is very small Mandatory on Linux if segment size >= 256 GB Use of huge pages Strand 2 • … Strand N If not using huge pages • Lock segment in memory • Memory. Lock=4 Consider NUMA effects… Not to scale Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 12

Persistence - Checkpointing Dual checkpoint files, automatic fuzzy checkpoints Perm Region 01 01 10 Pg 0 • Variable size based on contents • Two bits to track ‘dirty’ state wrt to checkpoint files 01 Last checkpoint was to. ds 1 at some earlier time T 0 At time T 1 Pg 1 • Page 0 is dirty wrt. ds 0 and. ds 1 • Page 2 is dirty wrt. ds 0 Checkpoint occurs at time T 2 • . ds 0 is oldest file (previous checkpoint was to. ds 1) • Db. Hdr + pages 0 & 2 are written to. ds 0 (in place update) • Pages 0 & 2 dirty bits for. ds 0 are cleared … At time T 3 . ds 1 . ds 0 Pg. N • Each is a full image of Db. Hdr + Perm • Written to alternately by checkpoint operations Perm region is divided into pages D 0 10 b Pg 2 H d r 0 Two checkpoint files, dbname. ds 0 and dbname. ds 1 0 • Page 1 is modified (e. g. by a DML operation) • Both dirty bits are set in the page • . ds 0 dirty: Pg 1 • . ds 1 dirty: Pg 0, Pg 1 Checkpoint occurs at time T 4 • . ds 1 is oldest file • Db. Hdr + pages 0 and 1 are written to. ds 1 • Dirty bit for. ds 1 cleared in both pages Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 13

Persistence – Transaction Logging High throughput and concurrency Parallel log manager • Multi-strand buffer (multiple ring buffers) • Behaves as a single shared logical buffer Log files • Configurable maximum size • Sequence number (64 -bit) • Old files purged by checkpointing In-memory Database Highly concurrent Log Buffer Strand 1 … • Concurrent record post to each strand AND • Flush buffer to disk Strand N Asynchronous and synchronous operation • Asynchronous (Durable. Commits=0) is the default • Synchronous (Durable. Commits=1) is an option Sync Async • Many records written at once • Outside transaction path • Decoupled from commit Current Log File • Less records written at once • Part of commit • Write through to media • Commit blocks until I/O completion • Group commit optimisation Async/sync configurable at • Database level • Connection level • Transaction level (application API) Log usage • Undo and Redo • Replication, XLA, AWT caching & incremental backups Log file purge criteria • No records in file belong to an open transaction • Changes for all records written to both checkpoint files • Not required by replication, XLA, AWT or backups Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 14

Persistence – Recovery Occurs on every ram. Load operation Database Shared Segment Most recent checkpoint loaded into memory A p ps • Physical operation • Can use multiple reader threads (configurable) Log replay Rollback any open txns • From the corresponding checkpoint log record… • …to end of log • Mark any indexes that would be modified Rollback any open transactions Drop marked indexes Rebuild them Ckpt Files Log files • No commit marker seen Drop and re-build marked indexes • Index modifications are not redo logged • Done in parallel (configurable) Static checkpoint to oldest checkpoint file Allow application connections Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 15

Concurrency Single level versioning for READ COMMITTED isolation (default) READER MYTABLE UPDATE p. Key 0 This is row 0 1 This is row 1 2 This is row 2 3 This is row 3 44 This row 4 4 Newisrow 5 6 This is row 5 • Readers don’t block writers and vice versa • Readers don’t place any locks column. X This is row 6 INSERT/UPDATE/DELETE • Creates a new (uncommitted) version of affected rows • e. Xclusively locks the row(s) Reader 4 This is row 4 • Reads old (committed) version(s) Old version(s) deleted on COMMIT or ROLLBACK • In reclaim phase • New version(s) becomes the only version SERIALIZABLE isolation • Readers place Shared row locks • Writers place e. Xclusive row locks • Optimiser may choose table locks instead Ø If many rows would be locked • Can use a hint to prevent lock escalation Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 16

Indexing Hash Indexes, Lockless B-Trees Hash Indexes Memory optimized B-Tree Indexes • Best performance for • Default index type - Full key equality lookups - Full key equijoins • Can’t be used for - Range lookups - Key prefix lookups • Must be sized accurately - Too small: poor performance and concurrency - Too large: wastes memory • Good all-round performance • Self balancing • Lockless design for high concurrency – No locks or latches for reads – Fine grained latching for writes CREATE[UNIQUE]HASH INDEX My. Hash. Index ON My. Table(somecol)PAGES = CURRENT; CREATE[UNIQUE]INDEX My. Range. Index ON My. Table (somecol); Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 17

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

Distributed, Shared Nothing, In-Memory Database Single-Image Database with High Availability and Elasticity • Appears to applications as a single database - Not as a sharded database • Online scale-out and scale-in C - Data automatically redistributed - Workload automatically uses new elements B A’ • Built-in HA via multiple fully-active copies A – Copies automatically kept in sync – Automatic client failover - Parallel SQL execution D’ D - Same features as Times. Ten Classic C’ - Mostly… Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | B’ 19

Scaleout Table Data Distribution Options • DISTRIBUTE large tables by consistent hash ØDistribute CUSTOMER rows across all elements by hash of Customer ID • COLOCATE child table rows with parent table row to maximize locality ØPlace ORDERS rows in same element along with corresponding CUSTOMER row • DUPLICATE small read-mostly tables on all elements for maximum locality ØDuplicate the PRODUCT list on all elements Distribute CUST Colocate ORDERS PRODUCTS Element 1 Duplicate PRODUCTS … Element N Servers Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 20

SQL SSH External Network Times. Ten Scaleout Architecture Overview RS 1_DSG 1 RS 1_DSG 2 2 PC MGMT 1 SSH & SCP RS 2_DSG 1 RS 2_DSG 2 2 PC Internal Network RS 3_DSG 1 MGMT 2 RS 3_DSG 2 2 PC REPO 1 Management instances RS 4_DSG 1 … Zoo. Keeper Membership Management MOUNT / SCP Repository Storage Hosts Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | RS 4_DSG 2 2 PC Data instances 21

Architecture: Scaleout Instances – Stores grid model – Processes management functions – Very similar to a ‘classic’ instance – 1 or 2 per Scaleout grid • Data Instance – Hosts database elements – Processes SQL and Txns – As many as you want Management Instances(s) Other Data Instances Client-Server Applications TT Client and Tools Network Server daemon Application Code Times. Ten Data Manager Library Direct mode Applications and Tools Times. Ten daemon Data Instance Server proxies Replication Grid agent(s) Workers Database Element(s) Millionths of a Second Metadata, Tables, Data Tables, Indexes, Views, Indexes, System Tables Sequences, … Millionths of a Second Data Store subdaemon(s) Checkpoint files Log Buffer Locks, Cursors, Command cache, Temp Indexes, … • Management Instance Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Log files Epoch files 22

High Availability K-safety, Synchronous , All Replicas Active • Hosts assigned to Data Space Groups – DSG = rack, Cloud AD etc. Data Space Group 1 C Data Space Group 2 • Replica sets created automatically – One replica per DSG B Replica Set 1 • All replicas are active for reads and writes A – All replicas are ‘equal’ A’ Replica Set 2 • Always consistent Replica Set 3 D’ D – 2 phase commit (synchronous) – Reduced 2 PC durable writes when K > 1 • Queries and transactions can span all replica sets Replica Set 4 C’ B’ – Grid aware optimizer – Grid aware SQL engine – Sophisticated transaction manager Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 23

Elastic Scalability Expand shrink the database online, based on business needs Data Space • Adding replica sets Group 1 - Prepare - Deploy new hosts/installations/instances - Typically one command per host - Redistribute data when ready Replica Set 1 Replica Set 2 - One command - Workload uses the new elements - Connections start to use new elements • Removing replica sets - Redistribute data - Tear down old hosts Replica Set 3 Replica Set 4 Replica Set 5 Data Space Group 2 A A’ B B’ C C’ D D’ E E’ Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 24

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

Top Performance Tips for Times. Ten General • Optimise data types – Use native integer types where appropriate (TT_TINYINT, TT_SMALLINT, …) – Inline versus out of line for variable length columns (VARCHAR 2, NVARCHAR 2, VARBINARY) • Indexes and optimiser statistics – Hash versus range indexes – use the index advisor! – Keep optimizer stats up to date • Optimise OS configuration – Shared memory, semaphores – Huge pages, locked memory • Optimise database configuration – Configure database parameters depending on workload, hardware etc. – If using HDD storage, separate checkpoint files and transaction logs – avoid I/O contention – Use huge pages (or lock database in memory if can’t use huge pages) Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 26

Top Performance Tips for Times. Ten Scaleout • Use the fastest, lowest latency network you can get – 10 Gb. E is absolute minimum – Faster is better! • Choose hardware wisely – Fewer, larger hosts better than many small hosts • Less network hops • Leverages Times. Ten’s excellent vertical scalability • Optimise table distributions – Distribution type, distribution keys (hash) • Global versus local indexes – Faster access to data – Slower DML – Tradeoffs! Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 27

Top Performance Tips for Times. Ten Applications • Use parameterized SQL – Facilitates use of (shared) prepared statements • Prepare once, execute many times – Hard parse >> soft parse >>>> no parse • Bind once (ODBC and OCI) • Minimise type conversions and character set conversions • Leverage batch operations, especially for INSERT – Multiple of 256 rows => fast path insert • Prefer direct mode connectivity where you can • When using client/server connectivity – Use PL/SQL to reduce network round trips – Use OCI ‘commit on success’ option where appropriate • Keep transactions small/short Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 28

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

When to use Times. Ten? Classic or Scaleout? Primarily for OLTP, Scaleout useful for Analytics too Times. Ten Classic • Very low, consistent latency – Microsecond response times • High throughput – Millions of TPS on a commodity server – 10 s of millions of queries per second • Single server or replicated – Optional read-only subscribers – Vertical scalability for writes – Vertical and horizontal for reads • Highly available (99. 999% possible) • Cache functionality for Oracle DB EE Times. Ten Scaleout • Good latency – Low millisecond response times • Very high throughput – 100 s of millions of TPS – Billions of queries per second • Vertical and horizontal scalability – For both reads and writes • Easy HA (99. 999% possible) • Elastic scalability – Add or remove database elements online – Read-only and read-write caching Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 30

Agenda 1 What is Times. Ten? 2 Times. Ten Classic Architecture 3 Times. Ten Scaleout Architecture 4 Performance Tips & Tricks 5 When to use Times. Ten? 6 Summary Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

Summary • Times. Ten is a sophisticated relational In-Memory Database • Two deployment modes; Classic and Scaleout • Focus is primarily on OLTP type workloads – Ultra low latency – Classic – Massive throughput – Scaleout • Standard SQL, PL/SQL and APIs • Fully ACID • Highly available Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 32

More Info… • Times. Ten OTN Portal (http: //www. oracle. com/technetwork/databasetechnologies/timesten/overview/index. html) – Product Information • Presentations, use cases, whitepapers, FAQs, … – Software Downloads – Product Documentation – Scaleout Demo / Learning VM download • Times. Ten Git. Hub Quickstart and Samples (https: //github. com/oracle-timesten-samples) • Contact me! (chris. jenkins@oracle. com) Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 33