How to step up performance with Oracle in-memory database options
A comprehensive collection of articles, videos and more, hand-picked by our editors
This is the first part of a two-part series comparing the in-memory database technologies of Oracle TimesTen and Sybase Adaptive Server Enterprise (ASE). This part takes an in-depth look at TimesTen. The second part looks at how Sybase ASE compares to TimesTen.
More on Oracle TimesTen in-memory database
Oracle Exalytics includes TimesTen
Is in-memory technology becoming a commodity?
Read about Oracle’s acquisition of TimesTen
An in-memory database, or IMDB, can be a standalone database management system (DBMS) like Oracle’s TimesTen, or a specific database that is part of a DBMS, like Sybase Adaptive Server Enterprise (ASE).
The goal of an IMDB is maximizing throughput and minimizing latency by relying on computer memory for data storage. This is unlike traditional database management systems that rely on disk storage. In-memory databases are faster than disk-optimized databases since the internal optimization algorithms are simpler and execute fewer CPU instructions. Accessing data in memory provides faster response. In applications where response time is critical, such as certain trading systems, telecoms and defense, IMDBs are often used. Because of the nature of IMDBs, these databases tend to use more memory than their disk-resident database counterparts.
Oracle TimesTen and Sybase ASE-IMDB are examples out-of-process main-memory databases. They implement full SQL, possibly with some dialects, security and database administration. Both provide access to data through SQL. Both mimic the functionality of their disk-resident big brothers. That makes it easier to use these products for caching SQL requests to a SQL back-end that maintains persistent databases.
TimesTen, ASE-IMDB and almost all current commercial IMDBs are based on what I call a row-based storage implementation of the relational model. These products are effective for online transaction processing (OLTP) applications.
What is the Oracle TimesTen in-memory database?
Oracle TimesTen was designed from the ground up as an in-memory database. It stores data using the row-based relational model (tables, columns, datatypes, indexes etc.), and uses SQL as the access language. It provides many APIs and largely supports Oracle PL/SQL. Applications work with it in the same way they would work with any other relational database. The main difference is that the performance offered by TimesTen is much better compared to a classic database. Although TimesTen operates completely in memory, it does keep a copy of the database on disk purely for restart and recovery purposes. This copy is kept up to date via checkpointing and transaction logging, thus allowing for recovery after any failure. TimesTen also has a replication mechanism typically used for high-availability. Its Cache Connect functionality allows it to act as a high-performance cache for a subset of data in a backend Oracle database. When used in this way it is known as the In-Memory Database Cache option for Oracle Database.
In the client/server mode, the Client API library usually uses TCP/IP – but can also use a UNIX domain socket or a local shared memory connection – to exchange requests and responses with a TimesTen server process that performs the actual database access. This is the usual mode used when the application is running on a different host from the TimesTen database. As well as latency introduced by network round trips, the extra code, context switches etc. involved in each database access also have a performance impact.
Direct mode can only be used when the application and TimesTen run on the same host. In this mode the Data Manager API library is essentially also the TimesTen database engine. API calls are in-process function calls and the TimesTen database (shared memory segment) is mapped into the application address space. This eliminates contest switches from the database access path and delivers the very highest performance and lowest latency with consistent outcome.
It is common to have mixed access where a TimesTen database is concurrently being accessed in direct and client/server modes by different application processes/threads. A single database can handle up to 2,000 user connections. When TimesTen has an instance (managing multiple TimesTen databases) then there is typically a limit of 25,000 user connections.
There is little or no functional difference at the API level between the two modes, so in general application code does not need to know or care which mode is being used – it is primarily a build time or runtime configuration choice.
Direct mode is one of the unique features of TimesTen. The bulk of TimesTen production deployments have typically been exclusively or largely direct mode. However, there are scenarios where client/server is also used.
A TimesTen cache is transparent in that you can look at the cached data using any usual relational tool. If the application has a database-centric view of its data – that is, it accesses its data using SQL and JDBC – then TimesTen may be the best fit. However, TimesTen does not have any built-in interoperability to non-Oracle databases, so the application code would have to manage any data movement between TimesTen and Sybase or others.
TimesTen’s memory structure
TimesTen memory constructs are far simpler than Oracle Database’s. Unlike Oracle, TimesTen does not have the concepts of database buffer cache, keep pool or recycle pool. Conventional database systems are designed using a strategy of minimizing disk I/O. This design strategy stems from the recognition of I/O as a very important factor in database’s performance. In contrast, in-memory database systems eliminate disk I/O from the beginning. Their overriding optimization goal is reducing memory and CPU demands.
TimesTen currently supports two types of indexes: hash indexes and T-Tree indexes. Hash indexes are limited to full key-equality lookups only but are very fast for those, and perform the same regardless of the cardinality of the underlying table (as long as they are sized correctly and ignoring hardware L1/L2/L3 cache effects for small tables). These indexes scale well for reads and writes and offer good concurrency. T-Tree indexes are also good for reads but not as good as hash indexes. They are more flexible as range lookups are supported. One area where they are somewhat weak is in concurrency under heavy write workloads, as each index modification has to latch the entire index. However, write performance of T-Trees is very good under low concurrency conditions.
Application of TimesTen
TimesTen is primarily about response time with throughput and high availability as secondary value areas. TimesTen typically delivers high value for OLTP style workloads where there is a need for very low and consistent response times. Also important is the redundancy. Typically you have several application servers for a single database server. Using TimesTen on these application servers reduces the need to go to the database, thus eliminating network latency and disk I/O.
For example, some trading systems provide rates information on a web. Without use of TimesTen, the application server will need to check the disk resident database every time a query comes for rates. With up to 100,000 queries per second, that would just cripple the database. TimesTen is designed to work closely to the application layer, whereas a classic DBMS has a much wider purpose. For real time response and moderate volumes there is overwhelming advantage in using TimesTen as opposed to any classic DBMS. In areas like program trading and market data where the underlying data changes rapidly, TimesTen will come in very handy as the data is real-time and the calculations must be done almost real-time, with few complications from the execution engine. Other areas of interest are telecoms, defense and intelligence.
About the author:
Mich Talebzadeh is a consultant and technical architect who has worked with database management systems since his student days at Imperial College, University of London, where he obtained his PhD in Experimental Particle Physics. He specializes in the strategic use of Oracle and Sybase and is the author of several database books and articles.