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:
- Log on to the OceanBase cluster with the administrator account of the sys tenant and execute the following statement to query
UNIT_CONFIG NAMEused 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_configis a tenant management parameter and does not need to be modified.- In this example,
unig_configof thetesttenant istest_unit.
- Copy the value of
unit_configand 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 thefreeze_trigger_percentageparameter, 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;
