Usage notes

To help you better experience and get familiar with OceanBase Database, learn of the differences between memory and timeout period.

Memory

OceanBase Database uses an LSM Tree-based storage engine, which is different from the dirty page flushing mechanism used in conventional database systems. OceanBase Database stores data in MemTables of the memory and SSTables of the disks. The update operations of all data are completed in the MemTables of the memory. When the memory usage reaches the specified threshold, a minor compaction is triggered to migrate the data to SSTables to release the memory space. The benefit of this architecture is that random I/Os are converted into sequential I/Os to improve the write throughput. For more information, see Overview of storage architecture.

LSM Tree stores all incremental data in the memory and triggers a minor compaction when the memory usage reaches the specified threshold. As a result, when a small-sized tenant instance runs in an intensive write scenario that exceeds its capacity, such as a data import or batch processing of massive data, new requests cannot be accepted because the MemTables reach the upper limit. In this case, OceanBase Database supports the following handling methods:

• Enable write throttling: When the data written into the memory reaches the specified threshold, OceanBase Database will proactively limit the import speed of the client.

• Scale up the memory: If the total memory in the environment is sufficient, you can scale up the memory of the tenant.

• Adjust the percentage of MemTables in the memory: When the total memory is limited and cannot be scaled up, you can adjust the percentage of MemTables in the memory for the tenant to expand the writable memory and set a lower threshold that triggers a minor compaction.

Enable write throttling

OceanBase Database supports write throttling. When resources are limited and the memory cannot be scaled up, you can enable write throttling on the server to protect the memory and avoid write overloading. You can specify the following parameters to enable write throttling on the server:

• writing_throttling_trigger_percentage: the threshold on the write speed. When the MemStore usage reaches this threshold (percentage), write throttling is triggered. Value range: [1,100]. Default value: 60. The value 100 indicates that write throttling is disabled.

• writing_throttling_maximum_duration: the available time of the remaining MemStores after write throttling is triggered. The default value is 2 h. Generally, you do not need to modify this parameter.

Use the administrator account of the tenant to specify that write throttling is triggered when the memory usage reaches 80% and ensure that the remaining memory is sufficient for writing data for 2 hours. Sample code:

obclient> ALTER SYSTEM SET writing_throttling_trigger_percentage = 80;
Query OK, 0 rows affected

obclient> ALTER SYSTEM SET writing_throttling_maximum_duration = '2h';
Query OK, 0 rows affected

Scale up the memory of the tenant

When the total memory in the environment is sufficient, you can expand the memory for the tenant.

Perform the following steps:

1. Log on to the OceanBase cluster with the administrator account of the sys tenant and execute the following statement to query UNIT_CONFIG NAME used by the current tenant.

obclient> SELECT NAME FROM DBA_OB_UNIT_CONFIGS;
+-----------------+
| NAME            |
+-----------------+
| sys_unit_config |
| test_unit       |
+-----------------+
2 rows in set

Note

• sys_unit_config is a tenant management parameter and does not need to be modified.
• In this example, unig_config of the test tenant is test_unit.

2. Copy the value of unit_config and execute the following statement to scale up the memory.

obclient> ALTER RESOURCE UNIT test_unit MIN_CPU = 2, MAX_CPU = 2, MEMORY_SIZE = '10G', MAX_IOPS = 10000, MIN_IOPS = 10000;

Notice

In the current version, only the CPU- and memory-related parameters take effect, and other I/O parameters such as IOPS do not take effect.

Adjust the percentage of MemTables in the memory for the tenant

Specify the following parameters to adjust the percentage of MemTables in the memory for the tenant:

• freeze_trigger_percentage: When the MemStore usage of the tenant reaches the value of the freeze_trigger_percentage parameter, a minor compaction is automatically triggered to release the memory space. The default value of this parameter is 20, which indicates that a minor compaction is triggered when the MemStore usage exceeds 20%.
• memstore_limit_percentage: the percentage of memory available for MemStore writes. The default value is 50%, indicating that the MemStore memory accounts for 50% of the total memory of the tenant.

When the memory is insufficient, you can increase the value of the memstore_limit_percentage parameter and lower the value of the freeze_trigger_percentage parameter to temporarily scale up the memory and promptly release the memory space.

Log on to the OceanBase cluster with the administrator account of the sys tenant. Increase the value of memstore_limit_percentage and lower the value of freeze_trigger_percentage. Sample code:

obclient> ALTER SYSTEM SET freeze_trigger_percentage = 20;
Query OK, 0 rows affected

obclient> ALTER SYSTEM SET memstore_limit_percentage = 70;
Query OK, 0 rows affected

Timeout period

In OceanBase Database, you may encounter the 'timeout' or 'Transaction is timeout' error in queries or DML operations. This is because OceanBase Database applies the default timeout settings for queries and transactions. You can adjust the settings based on business scenarios. You can execute the SHOW VARIABLES LIKE '%timeout%'; statement to view the timeout-related variables in OceanBase Database.

• ob_query_timeout: the timeout period for queries, in us. Default value: 10s.

• ob_trx_timeout: the timeout period for transactions, in us. Default value: 86400s.

• ob_trx_idle_timeout: the timeout period for idle transactions, in us. Default value: 86400s.

Set the timeout-related variables

You can set the timeout-related variables in the following ways:

• Set the variables in the current session or globally. Sample code:

obclient> SET ob_query_timeout = 10000000;
Query OK, 0 rows affected

obclient> SET GLOBAL ob_query_timeout = 10000000;
Query OK, 0 rows affected

• Set the variables in the JDBC connection string. Sample code:

jdbc:oceanbase://10.1.0.0:1001/unittests?user=**u**@sys&password=***1**&sessionVariables = ob_query_timeout = 60000000000,ob_trx_timeout = 60000000000&xxxx

• Add hints at the SQL statement level. Sample code:

Note

The added hints take effect only for the current SQL statement.

SELECT /*+query_timeout(100000000)  */  c1 FROM t1;