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

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 recovery source is sufficient, you can modify the tenant-level log_restore_concurrency parameter to adjust the concurrency of log synchronization of the standby tenant, to improve the log synchronization performance of the standby tenant.

For more information, see View the log synchronization progress.

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

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 synchronization. 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 specifies to use the adaptive degree of parallelism (DOP) for log recovery in OceanBase Database. We recommend that you use the default value.

   Notice

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

   If you have configured sufficient bandwidth between the standby tenant and the recovery source, you can increase the concurrency. If the recovery source uses 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 are described as follows:

   • SET: This keyword is optional in MySQL mode.
   • value: the modified value of the parameter.
   • 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.

   For example:

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

     ALTER SYSTEM SET log_restore_concurrency = 5;
     

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

     ALTER SYSTEM SET log_restore_concurrency = 5 TENANT = standby_tenant;
     

   For more information about the log_restore_concurrency parameter, see log_restore_concurrency.

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 on as the administrator to the primary tenant or the sys tenant of the cluster where the primary tenant resides.

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 specifies to use the adaptive degree of parallelism (DOP) for log archiving in OceanBase Database. We recommend that you use the default value.

   Notice

   If the tenant is configured with 2 or 4 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 are described as follows:

   • SET: This keyword is optional in MySQL mode.
   • value: the modified value of the parameter.
   • 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.

   For example:

   • Execute the following statement in the primary tenant to adjust the log archiving speed of the primary tenant:

     ALTER SYSTEM SET log_archive_concurrency = 10;
     

   • Execute the following statement in the sys tenant to adjust the log archiving speed of the primary tenant:

     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