X

TimesTen In-Memory Database
for Extreme Performance

Scaling SQL to millions of transactions per second with a single database

Doug Hood
TimesTen Cloud Product Manager

Introduction

Oracle TimesTen In-Memory Database has always had very low latency, now it also has extreme scalability.  TimesTen 18.1.1.2.0 has a new scale-out feature called TimesTen Scaleout that enables linear scalability via a shared nothing architecture. TimesTen Scaleout is a SQL RDBMS that supports ACID transactions and persists its data to disk.

TPTBM workload with 144 million TPS

The above chart shows a benchmark that achieved 144 million transactions per second using the TPTBM workload with 80% SQL selects and 20% SQL updates.

TPTBM with 100% read workload

The above chart shows a benchmark that achieved 1.2 billion transactions per second using the TPTBM workload with 100% SQL selects. The rest of this blog goes into the details of what this means and how it was achieved.

These benchmarks were run for several hours to obtain the 'steady state' behavior and avoid any short term 'caching effects'.

 

TPTBM Workload

The Telecom Provider Transaction Bench Mark [TPTBM] workload models a simple transactional workload used by some telecommunications providers. The TPTBM workload uses a single table:

CREATE TABLE vpn_users(
  vpn_id             TT_INT    NOT NULL,
  vpn_nb             TT_INT    NOT NULL,
  directory_nb       CHAR(10)  NOT NULL,
  last_calling_party CHAR(10)  NOT NULL,
  descr              CHAR(100) NOT NULL,
  PRIMARY KEY (vpn_id, vpn_nb) );

This table was populated with 640 million rows.

The SQL select statement used a primary key lookup with randomly generated values for the bind variables :

select
  directory_nb, 
  last_calling_party, 
  descr 
from vpn_users 
where vpn_id = ? 
and   vpn_nb = ?;

The update statement updates the 'last_calling_party' column via the primary key with randomly generated values for the bind variables :

update /*+ TT_CommitDMLOnSuccess(1) */ vpn_users 
  set last_calling_party = ?
where vpn_id = ? 
and   vpn_nb = ?;

The TPTBM workload is one of the Quickstart sample programs that TimesTen has made available for over ten years. The source code for TPTBM is available in C / ODBC and Java / JDBC.

 

Hardware Configuration

These two benchmarks were run on the same hardware in Oracle Cloud Infrastructure.

64 BM.HighIO1.36 bare metal compute instances were used:

  • The BM.HighIO1.36 compute instances were Oracle X5-2 servers with NVMe storage and a single 10G Ethernet network card using TCP/IP.

Oracle X5-2 server

TimesTen Scaleout is a distributed SQL RDBMS database with a shared nothing architecture.

Shared nothing architecture

A TimesTen Scaleout database has a single database image. That means that each user sees a single database schema with tables, indexes, sequences, views and materialized views.

As the benchmarks use 64 compute servers in the Oracle Cloud, logically the TimesTen Scaleout database looked like this:

A TimesTen Scaleout Database with 64 elements

The single VPN_USERS table was accessible on each of the 64 machines. The data was automatically distributed across the 64 machines using a consistent hash on the primary key (the VPN_ID and VPN_NB columns). Each machine contained about 10 million rows and there were 640 million unique rows in the VPN_USERS table. As the SQL Select and Update statements used random values for the primary key bind values, this means that each transaction could occur on any of the 64 machines.

 

Software

The BM.HighIO1.36 compute instances used the standard Oracle Linux 7.4 operating system image [Red Hat 7.4 compatible] that is available for Oracle Cloud Infrastructure instances.

The Oracle TimesTen Scaleout 18.1.1.2.0 software was used with the following database configuration:

[tptbm]
PermSize=40000
TempSize=8192
CkptLogVolume=4096
LogBufMB=4096
LogFileSize=4096
LogBufParallelism=16
Connections=10000
LockWait=3600
DatabaseCharacterSet=US7ASCII
ConnectionCharacterSet=US7ASCII
Datastore=/u10/instances/tptbm/tptbm
LogDir=/u10/instances/logs
PrivateCommands=1
PLSQL_MEMORY_SIZE=1024

 

 

How to make it Scale

The following techniques were used to make the TPTBM workload scale to millions of transactions per second:

  • Use the Oracle TimesTen Scaleout feature to scale horizontally using many machines
    • The database was spread over 64 elements using BM.HighIO1.36 servers in Oracle Cloud Infrastructure.
  • Use the TT_CommitDMLOnSuccess SQL directive
    • This SQL hint avoids an extra round trip for DML operations that you know will be followed by a commit operation.
  • Use the C Routing API :
    • The routing API is a way to enable simple transactions to determine which machine that a row exists on. The routing API imports the hash distribution map to the client, so that transactions can be executed on the element where the data resides avoiding a network hop.

 

Note

  • The BM.HighIO1.36 shape has been phased out in Oracle Cloud and has been replaced by the BM.DenseIO1.36 shape.
  • BM.DenseIO1.36 compute instances are also Oracle X5-2 servers with more NVMe storage and a single 10G Ethernet network card.
  • While testing these TPTBM workloads, we did not see a performance difference between the BM.HighIO1.36 and BM.DenseIO1.36 shapes.

 

Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

 

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.