Optimize the log synchronization performance

The synchronization performance of a Physical Standby Database solution is affected by many factors, such as the database load, storage medium, and network bandwidth. Without considering these factors, the Physical Standby Database solution based on log archiving provides the log_archive_concurrency and log_restore_concurrency parameters for controlling the concurrency of log archiving and log restore tasks.

Considerations

Adjust the log synchronization performance of a standby tenant

Adjust the concurrency of log restore for a standby tenant

During log synchronization for a standby tenant, a large difference between the synchronization progress of the standby tenant and that of the primary tenant indicates that the synchronization progress of the standby tenant lags behind that of the primary tenant. If the bandwidth between the standby tenant and the restore source is sufficient, you can modify the tenant-level log_restore_concurrency parameter to adjust the concurrency of log restore for the standby tenant, thereby improving the log synchronization performance of the standby tenant.

For more information, see View the log synchronization progress.

1. Log in to the standby tenant or to the sys tenant of the cluster where the standby tenant resides as the administrator.

2. Select an appropriate statement and specify the log_restore_concurrency parameter.

   The tenant-level log_restore_concurrency parameter specifies the total number of worker threads for log restore. The value range of this parameter is [0, 100]. The default value is 0, which specifies to use the adaptive degree of parallelism (DOP) for log restore in OceanBase Database. The modification of this parameter takes effect immediately without restarting the OBServer node. We recommend that you use the default value.

   Notice

   If the tenant is configured with two or four CPU cores, we recommend that you use the default value.

   If you have configured sufficient bandwidth between the standby tenant and the restore source, you can increase the concurrency. If the restore source uses Network File System (NFS), you can set the log_restore_concurrency parameter to 5, which is sufficient for most business scenarios.

   The SQL syntax is as follows:

   ALTER SYSTEM SET log_restore_concurrency = value [TENANT [=] tenant_name];
   

   The parameters in the syntax are described as follows:

   • SET: This keyword is optional in MySQL mode.
   • value: the value of the parameter after modification.
   • TENANT [=] tenant_name: the name of the tenant. You use this parameter to specify the name of the tenant only when you execute the statement in the sys tenant.

   Here are some examples:

   • Adjust the log synchronization performance of a standby tenant in the standby tenant

     obclient> ALTER SYSTEM SET log_restore_concurrency = 5;
     

   • Adjust the log synchronization performance of a specified tenant in the sys tenant

     obclient> ALTER SYSTEM SET log_restore_concurrency = 5 TENANT = standby_tenant;
     

   For more information about the log_restore_concurrency parameter, see log_restore_concurrency.

Disable parallel log transport for the network-based Physical Standby Database solution

In the parallel log transport framework, a standby tenant in the network-based Physical Standby Database solution sends remote procedure call (RPC) packets frequently. To improve performance and stability, you can disable parallel log transport for the standby tenant.

1. Log in to the standby tenant or to the sys tenant of the cluster where the standby tenant resides as the administrator.

2. Select an appropriate statement and specify the _ob_enable_standby_db_parallel_log_transport parameter.

   The tenant-level hidden parameter _ob_enable_standby_db_parallel_log_transport specifies whether to enable parallel log transport for the standby tenant. The default value is True, which indicates to enable parallel log transport. The modification of this parameter takes effect immediately without restarting the OBServer node.

   The SQL syntax is as follows:

   ALTER SYSTEM [SET] _ob_enable_standby_db_parallel_log_transport = value [TENANT [=] tenant_name];
   

   The parameters in the syntax are described as follows:

   • SET: This keyword is optional in MySQL mode.
   • value: the value of the parameter after modification.
   • TENANT [=] tenant_name: the name of the tenant. You use this parameter to specify the name of the tenant only when you execute the statement in the sys tenant.

   Here are some examples:

   • Disable parallel log transport for a standby tenant in the standby tenant

     MySQL mode

     Oracle mode

     Execute the following statement in a MySQL tenant to disable parallel log transport for the tenant:

     obclient> ALTER SYSTEM SET _ob_enable_standby_db_parallel_log_transport = False;
     

     Execute the following statement in an Oracle tenant to disable parallel log transport for the tenant:

     obclient> ALTER SYSTEM SET "_ob_enable_standby_db_parallel_log_transport" = False;
     

   • Disable parallel log transport for a specified tenant in the sys tenant

     obclient> ALTER SYSTEM SET _ob_enable_standby_db_parallel_log_transport = False TENANT = standby_tenant;
     

Adjust the log archiving speed of the primary tenant

During log archiving for the primary tenant, a large difference between the archive timestamp of the primary tenant and the current time indicates that log archiving for the primary tenant is slow. You can modify the log_archive_concurrency parameter for the primary tenant to adjust the log archiving concurrency and increase the log archiving speed.

For more information about how to view the log archiving speed of the primary tenant, see View the archiving progress.

1. Log in to the primary tenant or the sys tenant of the cluster where the primary tenant resides as the administrator.

2. Select an appropriate statement and specify the log_archive_concurrency parameter.

   The tenant-level log_archive_concurrency parameter specifies the total number of worker threads for log archiving. The modification of this parameter takes effect immediately without restarting the OBServer node. The value range of this parameter is [0, 100]. The default value is 0, which means that OceanBase Database automatically adapts the log archiving concurrency. We recommend that you use the default value.

   Notice

   If the tenant is configured with two or four CPU cores, we recommend that you use the default value.

   The SQL syntax is as follows:

   ALTER SYSTEM [SET] log_archive_concurrency = value [TENANT [=] tenant_name];
   

   The parameters in the syntax are described as follows:

   • SET: This keyword is optional in MySQL mode.
   • value: the value of the parameter after modification.
   • TENANT [=] tenant_name: the name of the tenant. You use this parameter to specify the name of the tenant only when you execute the statement in the sys tenant.

   Here are some examples:

   • Adjust the log archiving speed of the primary tenant in the primary tenant

     obclient> ALTER SYSTEM SET log_archive_concurrency = 10;
     

   • Adjust the log archiving speed of the primary tenant in the sys tenant

     obclient> ALTER SYSTEM SET log_archive_concurrency = 10 TENANT = mysql;
     

   For more information about the log_archive_concurrency parameter, see log_archive_concurrency.

References

• View the log synchronization progress

• View the archiving progress