Optimize the log synchronization performance

The synchronization performance of a physical standby database is affected by many factors, such as database load, storage medium, and network bandwidth. Without considering these factors, the log archiving-based physical standby database provides two parameters, log_archive_concurrency and log_restore_concurrency, for controlling the concurrency of log archiving and log restore.

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, if you find that the standby tenant's synchronization progress differs significantly from that of the primary tenant, the standby tenant's log synchronization may be lagging behind. If the bandwidth between the standby tenant and the restore source is sufficient, you can try to adjust the concurrency of log restore for the standby tenant by modifying the tenant-level parameter log_restore_concurrency, thereby improving the standby tenant's log synchronization performance.

For more information about how to view the standby tenant's log synchronization status, 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

   For small-scale tenants (tenants with two or four CPU cores CPU), we recommend that you use the default value and do not adjust this parameter.

   If you have confirmed that the bandwidth between the standby tenant and the restore source is sufficient, you can try to increase the concurrency. When the restore source medium is NFS, setting log_restore_concurrency to 5 can meet most scenarios.

   The SQL syntax is as follows:

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

   Statement usage:

   • SET: This keyword is optional in MySQL-compatible mode.
   • value: the value of the parameter after modification.
   • TENANT [=] tenant_name: only the system tenant can set tenant-level parameters by specifying the tenant.

   Examples:

   • The standby tenant adjusts its own log synchronization performance

     obclient> ALTER SYSTEM SET log_restore_concurrency = 5;
     

   • The sys tenant adjusts the log synchronization performance of a specified 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 protocol on network-based standby database

Under the parallel log transport framework, the standby tenant (standby database) sends RPC requests more frequently. For performance or stability considerations, you can disable the parallel log transport protocol on the network-based standby database to reduce performance overhead.

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

2. Query the current value of the _ob_enable_standby_db_parallel_log_transport parameter.

   The tenant-level hidden parameter _ob_enable_standby_db_parallel_log_transport controls whether to enable the parallel log transport protocol on the network-based standby database. The default value is True, which indicates that the parallel log transport protocol is enabled. The modification takes effect dynamically without restarting the OBServer node.

   MySQL-compatible mode

   Oracle-compatible mode

   Query the value of the hidden parameter in MySQL-compatible mode:

   obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME LIKE '_ob_enable_standby_db_parallel_log_transport';
   

   Query the value of the hidden parameter in Oracle-compatible mode:

   obclient [SYS]> SELECT * FROM SYS.GV$OB_PARAMETERS WHERE NAME LIKE '_ob_enable_standby_db_parallel_log_transport';
   

   The query result is as follows:

   +----------------+----------+-------+--------+-----------+----------------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------+---------------+-----------+
   | SVR_IP         | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                                         | DATA_TYPE | VALUE | INFO                                                                                                                                                                                                                                                     | SECTION    | EDIT_LEVEL        | DEFAULT_VALUE | ISDEFAULT |
   +----------------+----------+-------+--------+-----------+----------------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------+---------------+-----------+
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1004 | _ob_enable_standby_db_parallel_log_transport | NULL      | True  | Specifies whether the parallel log transport protocol is enabled on the standby database. The parallel log transport protocol is enabled only if this parameter is true and the primary database is compatible with the parallel log transport protocol. | LOGSERVICE | DYNAMIC_EFFECTIVE | True          | YES       |
   +----------------+----------+-------+--------+-----------+----------------------------------------------+-----------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+-------------------+---------------+-----------+
   1 row in set
   

3. Choose the appropriate syntax to set the _ob_enable_standby_db_parallel_log_transport parameter.

   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-compatible 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:

   • The standby tenant disables the parallel log transport protocol for itself

     MySQL-compatible mode

     Oracle-compatible mode

     MySQL-compatible tenant disables the parallel log transport protocol for itself:

     obclient> ALTER SYSTEM SET _ob_enable_standby_db_parallel_log_transport = False;
     

     Oracle-compatible tenant disables the parallel log transport protocol for itself:

     obclient> ALTER SYSTEM SET "_ob_enable_standby_db_parallel_log_transport" = False;
     

   • The sys tenant disables the parallel log transport protocol for a specified 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, if you find that the primary tenant's archive position differs significantly from the current time, the primary tenant's log archiving speed may have a bottleneck. You can try to adjust the primary tenant's log archiving concurrency by modifying the tenant-level parameter log_archive_concurrency, thereby improving the primary tenant's log archiving speed.

For more information about how to view the primary tenant's log archiving progress, 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 parameter log_archive_concurrency configures the total number of worker threads for log archiving. The modification takes effect dynamically without restarting the OBServer node. The value range is [0, 100]. The default value is 0, which indicates that OceanBase Database uses adaptive log archiving parallelism. We recommend that you use the default value.

   Notice

   For small-scale tenants (tenants with 2C or 4C CPU), we recommend that you use the default value and do not adjust this parameter.

   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-compatible 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:

   • The primary tenant adjusts its own log archiving speed

     obclient> ALTER SYSTEM SET log_archive_concurrency = 10;
     

   • The sys tenant adjusts the log archiving speed of a specified primary 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