TimesTen In-Memory Database
for Extreme Performance
-
Performance,
TimesTen Talk
April 15, 2020
Optimizing TimesTen Synchronous Replication on Oracle Database Appliance
Review
Review these blogs before optimizing TimesTen synchronous replication on Oracle Database Appliance:
- Oracle Database Appliance for TimesTen
- Installing Oracle TimesTen on an Oracle Database Appliance
- Creating a TimesTen Database on the Oracle Database Appliance
- Configuring Oracle Database Appliance X8-2-HA for TimesTen Replication with Clusterware and ACFS
Introduction
This blog will show you:
- How to make the best use of the Oracle Database Appliance network hardware
- How to create explicit, prioritized, TCP socket routes for TimesTen replication
- How to use multiple TCP sockets to automatically replicate data in parallel
- How to tune TimesTen parallel replication and transaction log buffers
Hardware
The Oracle Database Appliance X8-2-HA has two compute nodes, each of which has both external and internal network interface cards [NICs]. The internal/private network interface cards are designed to be used as a dedicated cluster interconnect for Oracle Real Application Clusters and Oracle Clusterware.
When the Oracle Database Appliance X8-2-HA is used with Oracle TimesTen for a highly available Active Standby Pair replication configuration and read/write caching for an Oracle database, the private network interface cards should be used for TimesTen replication.
Software
The following TimesTen replication configuration for Oracle Clusterware will automatically use the private network addresses:
Even though only the public hostnames [eg odoemoda0 and odoemoda1] are specified, the TimesTen software will use the Oracle Clusterware C API to determine what the interconnect network addresses are and use those addresses. This will result in the following TimesTen replication scheme:
This replication scheme uses default values for the store attributes. If you want to explicitly define the replication routes and/or store attributes, then you need to use the RepDDL syntax in the cluster.oracle.ini config file:
This RepDDL will create the following TimesTen repscheme:
The above RepDDL syntax does the following:
- It uses the cluster interconnect IP addresses to define the routes (from Master to Subscriber)
- The interconnect private hostnames can also be used [eg odoemoda1-priv and odoemoda2-priv]
- It defines the reverse route for cluster interconnect IP addresses in case there is a database failover
- If defines the forward and reverse routes based on the public hostnames [in case the interconnect addresses are not available]
- It defines the behavior of each TimesTen database via the store attributes
The RepDDL syntax give the full power of the TimesTen create active standby pair syntax within the cluster.oracle.ini config file.
TimesTen Two Safe Replication
TimesTen Replication enables many different configurations and behaviors.
If you cannot afford to lose any transactional data [eg financial transactions] then your should use two safe synchronous replication.
TimesTen Replication for an Active Standby Pair is logically transmitting the committed SQL Inserts, Updates and Deletes from the active database to the standby database. With TimesTen two safe synchronous replication, the SQL commits actually occur first on the standby database and then on the active database.
TimesTen replication uses several components:
- A Transaction Log Buffer is a FIFO queue for the redo/undo records
- Transaction Log Files are binary files which persist the redo/undo records
- A Transmitter thread writes to a TCP socket to send the redo/undo records to the standby database
- A Receiver thread reads the TCP sockets to apply the redo/undo records on the standby database
If the transmitter thread can always read from the transaction log buffer and the receiver thread can always read from the transaction log buffer, then replication can have both great throughput and latency as it is not doing any disk reads or writes.
The goal of TimesTen Replication tuning is to configure the transaction log buffer and transaction log files so that no (or minimal) disk reads and writes occur. By default the transaction log files are written to asynchronously from the transaction log buffers.
TimesTen Parallel Replication
It turns out that sequentially writing to a single socket for replication is a bottleneck for the TimesTen In Memory Database. Instead, TimesTen enables parallel replication:
- Multiple transmitter threads can be used, each with their own log buffer and TCP socket
- Multiple receiver treads can be used, each with their own log buffer and TCP socket
TimesTen Parallel Replication does the hard stuff for you:
- On the Active Database
- It takes a stream of transactions [redo/undo records] and figures out the dependencies between them
- For independent transactions, it replicates them in parallel using independent transmitter threads
- For dependent transactions, it processes them sequentially in a transmitter thread
- On the Standby Database
- It receives a stream of transactions and applies them in the same commit order as on the active database
SYS.ODBC.INI Attributes
TimesTen Parallel Replication is configured via setting attributes in the sys.odbc.ini file. Attributes of interest are:
These values are all interrelated.
The LogBufMB attribute defines how large the Transaction Log Buffer should be in Megabytes:
- The minimum size should be 1 GB [1024 MB]
- The maximum size should be 8 GB [8192 MB]
The LogBufParallism attribute defines how many logical log buffers there should be:
- A single large log buffer can exhibit enqueue/dequeue latch contention under high concurrent writes
- You can divide the log buffer into a set of smaller [logical] log buffers, where each has its own enqueue/dequeue latches
- This enables higher concurrent log buffer writes as each logical buffer has less latch contention
- A 1 GB log buffer with four logical log buffers would mean that each logical log buffer would have 256 MB of buffer space
- An 8 GB log buffer with four logical log buffers would mean that each logical log buffer would have 2 GB of buffer space
The LogFileSize attribute should always be the same value as LogBufMB!
The TempSize attribute defines an area of memory used for things like SQL ORDER BY and GROUP BY operations. It also is used as a work area to determine the commit dependencies.
The CacheAWTParallism attributes defines how many Oracle Net Services connections are used to write committed SQL Insert, Update and Delete statements to an Oracle Database [single instance, RAC or Exadata]. Depending on the workload, TimesTen uses either OCI batching or PLSQL blocks to send the writes to the Oracle Database.
The ReplicationParallism attribute defines how many Replication transmitter and receiver threads can be used concurrently:
- The value for ReplicationParallism should be half the value of LogBufParallism as you want each replication transmitter and receiver thread to have its own logical log buffer
- TimesTen replication will tend to scale with the value of ReplicationParallism, but it is constrained by Amdhal's Law
Amdahl's Law
Using more transmitter threads [each with their own log buffer and TCP socket] should provide linear scalablity for TimesTen Replication right? Unfortunately there is a pesky thing called Amdahl's Law.
It basically says that a parallel algorithm cannot go faster than its sequential tasks:
- Determining the commit dependencies in a redo/undo stream needs to be a sequential task
- Applying transactions in commit order requires some sequential processing
- Commit dependencies must be processed sequentially
Your workload's commit dependencies will determine the effective parallelism for TimesTen Replication.
Complex business logic will tend to reduce the effective Replication Parallelism. Simple workloads tend to enable greater replication Parallelism.
Sample sys.odbc.ini for Parallel Replication
The following example sys.odbc.ini file is used for two safe synchronous parallel replication. It uses:
- Five transmitter threads for replication
- Five transmitter threads for Asynchronous Write Though [AWT] cache replication
- 4 GB of RAM for the transaction log buffer
- Ten logical log buffers each of about 409 MB
- 512 MB of RAM for the temporary heap
Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.