Before you begin

To better get started with OceanBase Database, learn about the following two key concepts first: memory and timeout period.

Memory

The storage engine of OceanBase Database adopts a log-structured merge-tree (LSM-tree) based architecture. Unlike conventional databases that flush dirty pages in real time, OceanBase Database stores baseline data in disks by using SSTables and incremental data in the memory by using MemTables. All data updates and writes are performed in MemTables. When the memory usage reaches the specified threshold, a minor compaction is triggered to dump in-memory data to SSTables and free up the occupied memory space. This architecture enhances write throughput by converting random I/Os into sequential I/Os.

The LSM-tree-based architecture stores all incremental data in the memory and initiates a minor compaction when memory usage reaches the specified threshold. However, if a small-sized tenant instance runs in a write-intensive scenario, such as data import or batch processing of massive data, the instance might become overloaded. In such scenarios, the instance cannot accept new requests when the memory usage of MemTables reaches the upper limit. To address this, OceanBase Database offers the following solutions:

• 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.

• Expand memory: If the total memory in the environment is sufficient, you can expand the memory of the tenant. This solution is recommended.

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

Enable write throttling

In situations where resources are limited and memory cannot be expanded, OceanBase Database provides write throttling as a solution. This feature safeguards memory usage and helps prevent write overloading. To enable write throttling, specify the following parameters:

• writing_throttling_trigger_percentage: the memory usage threshold for triggering write throttling. When the MemStore memory usage reaches the threshold, 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 time required for allocating the remaining MemStore memory after write throttling is triggered. The default value is 2h. Generally, you do not need to modify this parameter.

To trigger write throttling on OceanBase Database when memory usage reaches 80%, and ensure enough memory is available for writing data for 2 hours, you can use the administrator account of the tenant to set the parameters as follows:

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

Expand the memory of the tenant

When the total memory in the environment is sufficient, you can expand the memory for the tenant by following these steps:

1. Log on to the sys tenant of the OceanBase cluster as the root user 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 parameter of the sys tenant and generally does not need to be modified.
   • In this example, UNIT_CONFIG NAME of the tenant is test_unit.

2. Copy the value of UNIT_CONFIG NAME and execute the following statement to expand the memory:

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

Adjust the percentage of MemTables in the memory for the tenant

You can specify the following parameters to adjust the percentage of MemTables in the memory for the tenant:

• freeze_trigger_percentage: When the memory space of the tenant occupied by MemTables reaches the specified threshold, the system automatically triggers a minor compaction and then frees up the occupied memory space. The value range of this parameter is [1,99]. The default value is 20, indicating that a minor compaction is triggered when the MemStore usage exceeds 20%.
• memstore_limit_percentage: the percentage of memory available for MemStore writes. The value range is [1,99]. 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 expand the memory and promptly free up the memory space.

To increase the value of memstore_limit_percentage and decrease the value of freeze_trigger_percentage, log on to the sys tenant of the OceanBase cluster as the root user and modify the parameters as follows:

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

You may encounter the timeout or Transaction is timeout error when you perform a query or execute DML operations in OceanBase Database. This is because default timeout periods are set for queries and transactions in OceanBase Database.

You can execute the SHOW VARIABLES LIKE '%timeout%'; statement to view the timeout-related variables in OceanBase Database.

• ob_query_timeout: the query timeout period, in μs. Default value: 10s.

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

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

Set the timeout period

You can set the timeout period in the following ways:

• Set the timeout period in the current session or globally. For example:

  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 timeout period in the JDBC connection string. For example:

  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. For example:

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

  Note

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

For more information about database development and management, see the Develop and Manage chapters.