Before you start

This topic describes two key points about OceanBase Database to help first-time users better experience this distributed database management system.

OceanBase Database is compatible with most SQL syntaxes of MySQL. However, the differences in the architecture and design between the two systems can be tricky for beginners.

The two major differences are about:

• Memory management

• Timeout period

Memory management

The storage engine of OceanBase Database adopts a log-structured merge-tree (LSM-tree) architecture. Unlike a conventional database that flushes dirty pages in real time, OceanBase Database stores baseline data in disks by using Sorted Strings Tables (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 release the occupied memory space. The benefit of this architecture is that it converts random I/Os into sequential I/Os and provides greater data write throughput.

However, it stores 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.

OceanBase Database allows you to address this issue by using any of the following methods:

1. Enable write throttling

   You can specify a memory usage threshold. After the memory usage reaches the threshold, OceanBase Database throttles the data writes from the client.

   OceanBase Database provides write overload protection. If you have configured limited resources and cannot scale up the memory space, you can enable the write throttling on the server to prevent memory overload.

   The writing_throttling_trigger_percentage parameter is a tenant-level parameter that specifies the threshold percentage for write throttling. If the MemStore memory usage exceeds the threshold, write throttling is triggered. The default value of this parameter is 100, which indicates that write throttling is disabled. The value range is [0 to 100]. The modification to this parameter immediately takes effect without the need to restart the OBServer. Another related parameter is writing_throttling_maximum_duration. This parameter specifies the period of time for which the remaining MemStore memory will be sufficient for data writing after write throttling is triggered. The default value is 2 hours. In most cases, you do not need to modify this parameter.

   To enable write throttling when the tenant memory usage reaches 80% and ensure that the remaining memory is sufficient for data writing for 1 hour, log on to the database as the tenant administrator and execute the following statements:

   obclient -h127.0.0.1 -P2881 -uroot@test  -Doceanbase -p******
   
   ALTER SYSTEM SET writing_throttling_trigger_percentage = 80;
   ALTER SYSTEM SET writing_throttling_maximum_duration = '1h';
   

2. Increase the tenant memory

   If you have configured sufficient memory resources for the system, the best solution is to increase the tenant memory by performing the following steps:

   1. Log on to the sys tenant of the OceanBase cluster as the administrator and execute the following SQL statement to confirm the unit_config name used by the current tenant.

      obclient -h127.0.0.1 -P2881 -uroot@sys  -Doceanbase -p******
      
      SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS\G    ##The sys_unit_config parameter is a parameter of the sys tenant and you do not need to modify it. In this example, the test tenant is used and its unit_config name is test_unit.
      

   2. Copy the unit_config name of the tenant, such as test_unit in this example, and use it in the following command to scale up the memory:

      ALTER resource unit test_unit max_cpu=2,min_cpu=2, memory_size='10G';
      

3. If the total memory of a tenant cannot be scaled up, you can increase the percentage of tenant MemStores to expand the writable memory and set a lower threshold for triggering a minor compaction.

   If the MemStore memory usage of a tenant in OceanBase Database 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 70, which indicates that a minor compaction is triggered if the MemStore memory usage reaches 70%. You can also modify the memstore_limit_percentage parameter. This parameter specifies the percentage of the writable MemStore memory out of the total tenant memory. The default value is 50, which indicates that the MemStore memory that can be used by a tenant is at most 50% of the total tenant memory.

   For example, if the total memory of a tenant is 10 GB, at most 5 GB of data can be written to the MemStore. The value range of this parameter is [1 to 99]. The modification to this parameter immediately takes effect without the need to restart the OBServer.

   To temporarily scale up the memory and facilitate the release of memory space, you can increase the value of the memstore_limit_percentage parameter and lower the value of the freeze_trigger_percentage parameter. Perform the following operation:

   Log on to the sys tenant as the root user and execute the following statements:

   obclient -h127.0.0.1 -P2881 -uroot@sys  -Doceanbase -p******
   
   ALTER SYSTEM SET freeze_trigger_percentage=40;
   ALTER SYSTEM SET memstore_limit_percentage=70;
   

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 the default query and transaction timeout configurations of OceanBase Database are different from those of a MySQL database. OceanBase Database allows you to modify the following three timeout variables. You can run the show variables like '%timeout%' command to view the timeout variables.

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

• ob_trx_timeout: the transaction timeout period, in μs. Default value: 100s.

• ob_trx_idle_timeout: the idle transaction timeout period, in μs. Default value: 120s.

OceanBase Database supports the following three methods for setting the timeout period:

1. Set the session-level or global variable. Example:

   set global ob_query_timeout=xxxxx;   //Set the global variable.
   set ob_query_timeout=xxxxx;  //Set the session-level variable.
   

2. Set the timeout period in a JDBC connection string. Example:

   jdbc:mysql://xxxxxx.com:3306xxxxxxx&sessionVariables=ob_query_timeout=60000000000,ob_trx_timeout=60000000000&xxxx
   

3. Add a hint to an SQL statement. This method takes effect only on the current SQL statement. Example:

   select /*+query_timeout(100000000)  */  c1 from t1;
   

For more information about development and O&M of OceanBase Database, see Developer Guide and O&M management.