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) 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 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. For more information, see Overview of storage architecture.

However, the LSM-tree architecture stores all incremental data in memory and triggers a minor compaction only when the memory usage reaches the specified threshold. This makes it impossible for a small-sized tenant to receive new requests after its memory is fully occupied in write-intensive scenarios, such as data import or batch processing of a large amount of data. To address this, OceanBase Database offers the following solutions:

• Enable write throttling: OceanBase Database can limit the client's data import speed once memory usage reaches a certain threshold.

• Expand the tenant memory: If the total memory in the environment is sufficient, you can allocate more memory to the tenant.

• Adjust the percentage of tenant memory available for MemTables: When total memory is limited and cannot be expanded, you can increase the percentage of tenant memory allocated to MemTables to expand writable memory. Additionally, you can lower the threshold to trigger 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. The value range is [1,100]. The default value is 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 tenant memory

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

The procedure is as follows:

1. Log in 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 the unit config of the sys tenant and generally does not need to be modified.
   • In this example, the name of the unit config of the test tenant is test_unit.

2. Copy the name of the 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; 
   

Adjust the percentage of tenant memory available for the MemStore

You can specify the following parameters to adjust the percentage of tenant memory available for the MemStore:

• freeze_trigger_percentage: the percentage of the MemStore memory usage of a tenant. When the value specified by this parameter is reached, the system automatically triggers a minor compaction to release 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 memory usage reaches 20%.
• memstore_limit_percentage: the percentage of the writable MemStore memory out of the total tenant memory. The value range of this parameter is [0, 100), and the default value is 0, which indicates that the percentage of tenant memory available for the MemStore is adaptively adjusted by the system.

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 release the occupied memory space.

Log in to the sys tenant of the OceanBase cluster as the root user. Increase the value of memstore_limit_percentage and decrease the value of freeze_trigger_percentage. Here is an example:

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. The default value is 10000000.

• ob_trx_timeout: the transaction timeout period, in μs. The default value is 86400000000.

• ob_trx_idle_timeout: the idle timeout period for transactions, in μs. The default value is 86400000000.

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:mysql://10.1.0.0:1001/unittests?user=**u**@sys&password=******&sessionVariables = ob_query_timeout = 60000000000,ob_trx_timeout = 60000000000&xxxx
  

• Add hints at the SQL statement level. For example:

  Note

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

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

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