Optimizing the network settings for a GoldenGate configuration

You can use GoldenGate to replicate data between two schemas within the same database; however, in most cases you will use GoldenGate to replicate the data between two databases that are runningon different systems. These systems can be quite far from each other in different locations. One of the key functions performed by the GoldenGate processes is transferring the files to the remote destination. Even though you have the best tuned database with a well-tuned GoldenGate Replicat process, it will not apply any changes if they are not transferred to the target system by the Extract/Datapump process. In order to transfer the data efficiently and make the best use of the network, there are certain things that you need to consider and tune at the GoldenGate and operating system level. In this recipe, we will look at the configuration required to ensure an efficient transfer of GoldenGate trail files between two systems.

Getting ready

Tuning or changing some of these parameters may require administrator-level privileges at theoperating system level. Depending upon your environment, you might need to involve the System Administrator team to do this.

How to do it...

The following are the key parameters that can be tuned for a GoldenGate configuration:

1. You should always configure at least one Datapump process for each extract to alleviate the job of data transfer from the Extract process.
2. Configure the TCPBUFSIZE and TCPFLUSHBYTES option for the RMTHOST parameter. The default value of TCPBUFSIZE is 30,000 bytes and it is not adequate for high-speed networks. In order to determine the optimal value of TCPBUFSIZE for your environment, determine the average Round-Trip Time (RTT) as follows:

   [ggate@prim1-ol6-112 ~]$ ping -c 3 stdby1-ol6-112
   PING stdby1-ol6-112.localdomain (192.168.0.11) 56(84) bytes of data.
   64 bytes from stdby1-ol6-112.localdomain (192.168.0.11): icmp_seq=1 ttl=64 time=1.22 ms
   64 bytes from stdby1-ol6-112.localdomain (192.168.0.11): icmp_seq=2 ttl=64 time=0.765 ms
   64 bytes from stdby1-ol6-112.localdomain (192.168.0.11): icmp_seq=3 ttl=64 time=0.520 ms
   --- stdby1-ol6-112.localdomain ping statistics ---
   3 packets transmitted, 3 received, 0% packet loss, time 2003ms
   rtt min/avg/max/mdev = 0.520/0.836/1.223/0.291 ms

3. Multiply the average RTT with the bandwidth of your network.
   In our example, the network bandwidth is 100 Mbps and the average RTT is 0.836 milliseconds as follows:

   TCPBUFSIZE =.0836 seconds * 100000000 Bits per seconds = 8360000 Bits =8360000/8 = 1045000 Bytes

4. Configure the parameters in the Datapump parameter file. The TCPFLUSHBYTES parameter should be at least equal to TCPBUFSIZE as shown in the following code:

   RMTHOST stdby1-ol6-112 , MGRPORT 8809, TCPBUFSIZE 1045000, TCPFLUSHBYTES 1045000

5. Configure the appropriate values for OS maximum socket buffer sizes. These should be at least equal to the values of TCPBUFSIZE. For a Linux environment, the following parameters in/etc/sysctl.conf will need tuning/adjusting:

   net.core.rmem_max
   net.core.wmem_max
   net.ipv4.tcp_rmem
   net.ipv4.tcp_wmem
   

6. If even after modifying the previous settings, there is a big lag in the transfer of files to the remote system, you can configure the Datapump process to compress the trail files for network transfer. This can be done using the COMPRESS option with the RMTHOST parameter as follows:

   RMTHOST stdby1-ol6-112 , MGRPORT 8809, COMPRESS

   GoldenGate compression compresses the trail files by a factor 1:4. Enabling the compression option adds extra processing load to the server, so you would see an increase in the CPU usage.

How it works...

Network speeds like any other part of IT infrastructure are improving with time. One of the features of GoldenGate is to replicate the data between systems that are physically located in different geographical locations on two different sides of the world. If the underlying system is a very busy OLTP system and the distance between the systems is massive, sometimes there can be a delay in the data transfer between the systems.

Tuning the network for an efficient transfer for the trail files is an important task. GoldenGate uses the TCP/IP protocol for transfer of the trail files. You should always create at least one Datapump process in the extract configuration. Without that, the extract will be doing both the data extraction and data transfer tasks. This can sometimes cause a bottleneck if there are too many changes occurring in the database.

The next step is to determine the appropriate value for the TCPBUFSIZE parameter. This parameter should be configured in all the Datapumps and the processes should be restarted.

Similarly, you will also need to ensure that the underlying operating system has been configured to handle the TCP data efficiently. For this, you will need to modify some parameters that can be different from one platform to another.

If the network speed in your infrastructure is not enough to handle the transfer load of the trail files and you are observing considerable lag, you should enable the compression of the trail files. This might not be desirable in some cases as it will add some overheads that will lead to higher CPU usage on the server.