Before You Start

If this is your first contact with OceanBase, here are two tips to help you better experience and get started with this distributed database before you start trying to use it.

Although OceanBase supports nearly all the syntax of MySQL, some differences may cause confusion to new users due to differences in underlying architecture and design. The two most common feature differences, memory and timeout, are described below.

About Memory

A traditional database flushes dirty pages in real time, whereas OceanBase uses a storage engine based on the Log-Structured Merge-Tree (LSM Tree). In OceanBase, data is divided into Memory Table (MemTable) data and Sorted String Table (SSTable) data. All data update or write operations are completed in the MemTable. When the memory usage reaches a specified threshold, a compaction is triggered, the MemTable data is dumped to the SSTable, and the active memory is released. The advantage of this architecture is that random IO can be converted into sequential IO, providing greater write throughput.

However, with LSM-Tree, incremental data is stored in the memory, and a dump is triggered only when the memory usage reaches a specified threshold. As a result, when the write load exceeds the capacity of a small-sized tenant instance, the MemTable cannot accept new requests because its upper limit is reached. This usually happens in scenarios where a huge amount of data is being processed, such as data import or batch processing.
At OceanBase, you can deal with this issue in several ways:

1. Enable the write throttling function, so that OceanBase actively limits the client's import speed when the write load to the memory reaches a specified threshold.

   OceanBase has write overload protection. When the resources are limited and the memory cannot be expanded, you can set the server write speed limit to protect the memory from write overload.
   writing_throttling_trigger_percentage is a tenant-level parameter that controls the write speed. That is, when the memory consumed by the memstore reaches this threshold (percentage), the write throttling function is triggered. The default value of this parameter is 100, which means that the write throttling function is disabled. The value range of this parameter is [0, 100]. Modifying this parameter takes effect immediately without restarting the OBServer.
   writing_throttling_maximum_duration specifies how long the remaining memory can provide for writing after write throttling is triggered. The default time is 1 hour, which is generally not modified.
   If you want to set a tenant's memory write speed limit of 80% and ensure that the remaining memory is sufficient to provide 1 hour of writing, set the following in the tenant's administrator account:

   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's memory configuration.

   If the memory resources in your environment are relatively sufficient, then the best solution is to increase the memory. To achieve this purpose, perform the following steps:
   a. Log on to the sys tenant's administrator account of the OceanBase cluster and run the following SQL statement to confirm the unit_config name used by the tenant.

   obclient -h127.0.0.1 -P2881 -u***@sys  -Doceanbase -p
   
   select name from __all_unit_config;   ##where the sys_unit_config is the configuration of the tenant and is generally not modified. In this example, the tenant's name is test and the unig_config name is test_unit.
   

   b. Copy the tenant's unit_config name, such as 'test_unit', to the following command:
   Note: As of OceanBase V3.1.3, modifying the resource unit configuration only takes effect on the CPU and Memory configurations, and does not take effect on other IO parameters such as disk and IOPS. Just keep the default.

   ALTER resource unit test_unit min_cpu=2,max_cpu=2, max_memory='10G',min_memory='10G', max_disk_size='500G',max_iops=10000,min_iops=10000,max_session_num=10000; 
   

3. If the total memory of the node is limited and cannot be expanded, you can also adjust the proportion of the memstore in the tenant's memory, increase the writeable memory, and decrease the dump threshold to make the dump happen faster.

   When the usage of the MemTable memory of a tenant in OceanBase reaches the percentage limited by the configuration item freeze_trigger_percentage, a dump is automatically triggered, and the occupied memory is released after the dump. The default value of this parameter is 70, which means that a dump is triggered when the memstore usage exceeds 70%.

   Another related parameter is memstore_limit_percentage, that is, the proportion of the tenant's memory available for memstore writing. The default value is 50%, which means that tenant's memory using the memstore accounts for 50% of its total available memory.

   For example, a tenant has a memory configuration of 10 GB, and the writeable memory for the memstore is 5 GB. The value range of this parameter is [1, 99]. Modifying this parameter takes effect without restarting the OBServer.

   If the memory is insufficient, you can increase the memstore_limit_percentage and decrease the freeze_trigger_percentage to achieve temporary expansion and dump as soon as possible.

   To achieve this purpose, use the root user to log on to the sys tenant of the database and run the following SQL statement:

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

About Timeout

In OceanBase, you may encounter errors of 'timeout' or 'Transaction is timeout' when querying or performing DML operations. This is because, unlike the default mechanism of MySQL, the query and transaction timeout are configured by default in OceanBase, which makes it easier for users to adjust for different business scenarios.
For the timeout setting, three variables can be adjusted in OceanBase. You can view them by using the command show variables like '%timeout%':

• ob_query_timeout: query timeout. Unit: μs. Default value: 10s
• ob_trx_timeout: transaction timeout. Unit: μs. Default value: 100s
• ob_trx_idle_timeout: transaction idle timeout. Unit: μs. Default value: 120s

There are generally three methods to set the timeout:

1. Set variables at the global and session levels, for example:

   set global ob_query_timeout=xxxxx;   --global level
   set ob_query_timeout=xxxxx;  --session level
   

2. Set variables in the JDBC connection string, for example:

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

3. Add a hint at the SQL level. This method is valid only in the current SQL statement.

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

These are the two points you need to pay attention to when you use OceanBase for the first time. For more details, see the developer guide and the operation and maintenance guide.