In-Memory Database Choices @ Oracle
One size fits All approach has seldom worked; neither in human lives nor in technology world. Right choices have to be made to thrive and succeed.
Coming to the main point — databases have always been primarily used as persistent stores for data with appropriate data models to insert and retrieve data.
In-Memory databases (IMDB) have been the sophisticated weapon for catering to the demand of superfast response times to user interactions and transaction processing.
Need for Speed is the motto but there are practical scenarios to take cognizance of:
·Robustness v/s Speed: Databases and their hosting servers are built with the purpose of providing robustness, guaranteed persistence (via replication, redundancy etc.) whereas memory based processing tiers have their top objective in form of faster response (reads and/or writes). Hence, their purpose, processing capabilities are understandably different and just one of it cannot do it all.
· Memory (RAM/DRAM) is extremely fast as compared to disk/SSD but it is ephemeral. Of course, there are good alternatives to handle the volatility of RAM. It could be battery supported RAM, writing the transaction and checkpoint logs to disk, by periodically taking snapshots to disk or by replicating the in-memory state to other machines. Are there good in-memory products which already offer this?
· Limited Caching due to Cost Factor: Memory is still quite expensive as compared to disks and hence cannot be the only choice for hosting the complete dataset especially when it’s a really large one running into multiple terabytes. In such situation, do we have a choice of using most response time sensitive interactions catered to from memory like a cache but for persistence and all other purposes have a database at backend?
· In-Memory Only at Scale: What If the nature of application is such that it needs all user functionalities to be served at very low latency and at the same time the data store size is huge?
· Nature of application: Whether it is Transaction Processing (OLTP) or it’s Data Warehouse/ Analytics (DSS) or it’s a mixed workload catering to both OLTP and Reporting within the same datastore? The best way to handle data in OLTP (row-based) is different than how it would be when it’s an analytical workload (column-based offers the optimal processing speed).
We, by now realize that having appropriate options at our disposal and thereafter making a right choice is the key here.
How does Oracle’s IMDB Story helps to make a right choice?
While Oracle has multiple offerings that act as pure IMDB and/or Cache and/or performance boosters, I am going to limit this article to-
[1] Oracle TimesTen (OLTP Focused) and
[2] Oracle Database In-Memory (Analytics Focused)
Objective of this article is not to do a deep-dive but to understand the main features and applicability of these 2 options. It’s more of Oracle In-Memory 101. Advanced technical details can be referred to from the links provided in Reference section at the bottom of this article.
NOTE: Oracle has other performance boosters in form of offerings like Oracle Coherence as an in-memory data-grid solution, Oracle Exadata Engineered Systems with Flash, PMEM etc. to provide the best platform to run an Oracle database. You can read more about those on Oracle’s official site.
Oracle TimesTen -In-Memory for OLTP providing microsecond response and millions of TPS
TimesTen as a product traces back its origin to year 1996 and it was the first commercial in-memory RDBMS.
Some of the major features that it has had since beginning and also enhanced over years are- Replication for High Availability, Online Upgrades and the Capability to run this In-memory Database as an Application-tier Cache for Oracle database.
The most significant offering from Oracle TimesTen in recent times has been the introduction of TimesTen Scaleout.
Oracle TimesTen In-Memory database gives the choice of multiple deployment options:
TimesTen Classic:
1. Standalone / Replicated Relational IMDB
Relation Database — Pure in-memory, ACID compliant, Standard SQL, Entire database in RAM
Persistent & Recoverable — Database and Transaction logs persisted on local disk or flash storage, Automatic recovery after failure
Extremely Fast — Microseconds response time, Very high throughput
Highly Available — Active-Standby and multi-master replication, High performance parallel replication, HA and Disaster Recovery
2. Cache for Oracle Database
Accelerates existing Oracle Database Applications
Caching from Oracle Database with automatic change synchronization
Multiple configuration options — mix and match
Read-only cache groups
Write-through cache groups
Native TimesTen tables
Same architecture and features as TimesTen Classic
Low, consistent response time
High availability and online upgrades
Standard SQL, relational model and standard APIs
Compatible with Oracle Enterprise Manager, SQL Developer, GoldenGate & Clusterware
TimesTen Scaleout:
3. Distributed Relational IMDB
Extreme performance that scales with increase in number of nodes and IMDB size
Single database image, data location transparency
Full SQL, ACID transactions
Automatic high availability via K-safety
Elastic scale-out and scale-in
On-premises or Cloud deployment
+++++++++++++
Oracle Database In-Memory Option — In-Memory for Analytics within Oracle Enterprise database
Call it Columnar Store within Oracle Database if you wish to…
Database In-Memory enables the DUAL format database.
BOTH row and column formats for same table
Simultaneously active and transactionally consistent
Analytics & reporting use new in-memory Column format
OLTP uses proven row format
No application changes required
Pure in-memory columnar format; Not persistent, so no undo or redo is generated
Can be enabled for table, partition, sub-partition or materialized view
Available on all hardware platforms
Does not require the whole database to be in-memory
Can be enabled for just active data (table, partition, sub-partition, materialized view)
Use Cases:
Oracle TimesTen
— — — — — — —
With primary intention of boosting the performance of an OLTP application, it can be used as a Scaleout or Classic (single node) IMDB or as a Read-Write / Read Only Application-Tier cache for critical applications across various industries like Banking & Financial Services, Telecom, Stock Exchanges, Transportation, Aviation, Pharmaceuticals, Manufacturing.
· Order Bookings
· Status Enquiries
· Fraud Detection in financial transactions
· Balance Enquiries in Banks, Stocks/Shares applications
· Improved speed of User Logins to the applications
· Customer Support applications
· Customer Relationship Management applications
and anything under the Sun that can run on an Oracle database can be contemplated.
Oracle Database In-Memory
— — — — — — — — — — — —
This columnar form of in-memory database within larger Oracle Enterprise Edition database finds its applicability for scenarios with non-trivial quantum of rows and execution time wherein a significant amount of time goes into — accessing data, joining data and/or aggregating data.
- Enterprise Mixed workload systems for reporting purpose
- In-memory transactional data can be used for ETL towards Enterprise Data Warehouse
In Datawarehouse,
- Star schema and pre-calculated KPIs
- All or sub-set of foundation layer
References:
https://www.oracle.com/in/database/technologies/related/timesten.html
https://www.oracle.com/technical-resources/articles/database/timesten/scaleout.html
https://blogs.oracle.com/timesten/howto-2
https://www.oracle.com/in/database/technologies/in-memory.html
https://www.oracle.com/technetwork/database/in-memory/learnmore/dbim-quickstart-guide-v5-6525689.pdf
Disclaimer: I am an employee of Oracle Corporation. All views expressed in this article are in my personal capacity only and do not necessarily reflect my employer’s views.
