Performance Tuning Tips for GoldenGate

There are a number of factors that we need to keep in mind to be able to successfully tune a GoldenGate Setup. If you carefully design your system keeping in mind the end goal and apply some proven performance tuning measures you should be able to get real time replication from your GoldenGate setup.

Below are some of the key points that we found helpful when doing Performance Tuning for GoldenGate.

1) Have a very clear definition of Baseline

Before we can start tuning, we must record our baseline. See when load on your application peaks and take multiple readings of key database performance parameters like CPU/Memory/cursor usage /Transactions count/ etc. Generate and save AWR reports also for that time.

Involve your Application Stress testing team to simulate data and transactions in an exact Production replica.

2) Think “Parallel”

If you have a lot of tables to replicate, do not just put all of them in single extract/replicate process. Divide those large number of tables among parallel processes and trail files. This will certainly help the system in performing better.

We have also seen that increasing parallelism on the Replicat (target) side give better results than on the Extract (source) side.

You increase parallelism on the 12c GoldenGate by adding below kind of parameter in the parameter file:

DBOPTIONS INTEGRATEDPARAMS (parallelism 6)

And always keep in mind parallelism will increase the CPU/Memory consumption so keep monitoring those parameters closely.

3) Study and Optimize Online Redo Logs

It is very important that Redo logs should be arranged in such a way that GoldenGate performance can be optimized. Redo logs are constantly read and write making them first candidate of applying Tuning methods.

• Keep redo logs on fast disk (example SSD, Flash Disk) wherever possible
• Ensure adequate number of redo log groups are configured
• Read your AWR carefully, see if any redo log related events(example ‘log file sync’) are showing up.
• Incorrect RAID settings of Disk for Redo logs is another issue. Recommended is RAID1+0 (mirrored striped sets) for these files and RAID 5 is least recommended.

4) Understand the data in tables carefully

Does your table have any LOB (BLOB, CLOB, NLOB). LOB data types store large blocks of unstructured data in the binary or character format and it is a good idea that such tables having LOB type data should have separate Extract, data pump, and Replicat process group so that we can increase throughput.

Does your tables have primary key. Without a key on a table with multiple columns can definitely increase the redo log generation.

5) Avoid Contention at Trail file level

According to Oracle’s best practice for every remote trail file there should not be more than three Replicat processes that read the same remote trail file. So it is better to pair each Replicat with its own trail file and Extract process.

6) Verify if Network is your culprit

If network is performing poorly, you will get high latency, possible disconnections and poor performance. All this will lead to data pump process unable to write to the remote trail quickly enough.

Work with your network team, see if OS TCP socket buffers can be adjusted to higher value, TCP packet size can be changed etc. Usually OS default parameters are defined towards lower value and definitely not optimized for applications like GoldenGate.

7) Check if your applications are hosted on over-utilized hardware

If you have a very busy system which is already utilizing the hardware resources to the max, then it may prove helpful if some of those hardware parameters value can be increased to accommodate the GoldenGate resource demands. Closely monitor your CPU Consumption/ Memory usage / I/O contentions.

8) Plan to install latest GoldenGate Release

At the time of writing this post 12cR2 is latest GoldenGate release. This release has some new features like integrated Replicat which speed up the data apply feature and is a good performance improvement technique.

9) Use new Integrated Replicat features that are present in 12c version

You  can configure the Replicat processes to commit asynchronously at the session level by using commit_wait = ‘NOWAIT’ parameter setting. This will ensure that Oracle will not wait for a commit to succeed before allowing the session to continue

That can be setup by using SQLEXEC statement in each parameter file:

SQLEXEC "alter session set commit_wait = 'NOWAIT'";

you can also explore some other parameters like BATCHTRANSOPS, BATCHSQL_MODE which also help boost the replicat speed.

10) Calculate transaction size on the Source side and tweak parameters to increase throughput

Use GoldenGate’s LOGDUMP utility to identify the transaction size on source database

If required, Increase EAGER_SIZE  inbound server parameter for the integrated Replicat to value slightly higher than the transaction size.
Example:

DBOPTIONS INTEGRATEDPARAMS (EAGER_SIZE 18000)

11) If you get stuck somewhere, don’t forget to trace

Tracing enables additional information to be gathered from a given process and do help sometimes when you are simply not able to figure out what process is slow or what table is bottleneck.

Oracle GoldenGate 12c offers a similar tracing mechanism through its trace and trace2 options. “trace” provides processing information, whereas “trace2” identifies the processes with wait events.

Trace will help you collect some key parameters like: table name, operation name/type/number, average/maximum waits.

References / Read More

Note 1557031.1 Recommended patches for Integrated Capture/Replicat
Note 1448324.1 OGG Integrated Healthcheck Script
Note 1488668.1 GoldenGate Performance Data Gathering
Note 1596493.1 GoldenGate Integration with AWR
Note 1485620.1 Best Practices Downstream Capture
MAA white paper Oracle GoldenGate Performance Best Practices
http://www.oracle.com/technetwork/database/availability/maa-gg-performance-1969630.pdf