This topic compares the four SQL parameter templates available when creating a MySQL- or Oracle-compatible tenant.
Note
Currently, parameter template configuration is only supported for newly created OceanBase Database V4.3.3 and later.
Background information
OceanBase Cloud supports selecting different parameter templates when creating a tenant to quickly match the required scenario. For different business scenarios, users can choose the corresponding parameter template to achieve optimal performance in that scenario.
You can choose from the following SQL parameter templates:
OLTP Simple Transactions (default option): Suitable for core systems such as online order trading and payment, and internet businesses primarily involving high-concurrency point queries. Each executed SQL statement is relatively simple, requiring fast response. This template is recommended for applications using the Vector Search feature.
OLTP Complex Transactions: Suitable for online transaction systems with complex transactions, such as scenarios involving complex join calculations, subqueries, and batch processing jobs written in PL. These scenarios may also involve long or large transactions.
HTAP Mixed Workloads: Simultaneously runs transactional and online analytical workloads. Based on providing OLTP capabilities, it enhances computing power for online analytical scenarios through vectorization capabilities (here referring to the processing capability of the vectorized execution engine, not vector search).
OLAP Online Analysis: For real-time data warehouse scenarios involving online analytical processing (OLAP). By default, created tables use columnar storage and a more optimized vectorized execution engine to enhance computing power for complex analytical scenarios. If this cluster instance is dedicated for OLAP analysis, it is recommended to optimize system variables and cluster parameters accordingly, such as enabling adaptive parallel computing, optimizing SQL sort memory space, setting SQL and transaction timeouts, and configuring large query strategies. Additionally, it is recommended to use the
utf8mb4_bincollation for the tenant.
Parameter template comparison
Parameter type |
Parameter name |
OLTP Simple Transactions |
OLTP Complex Transactions |
HTAP Mixed Workloads |
OLAP |
|---|---|---|---|---|---|
| Cluster parameters | large_query_threshold | 5s | 600s | 600s | 0 ms (large query disabled) |
| Cluster parameters | trace_log_slow_query_watermark | 1s | 1s | 1s | 7d |
| Tenant parameters | _rowsets_max_rows | 1 | 4 | 32 | 256 |
| Tenant parameters | log_transport_compress_all | TRUE | TRUE | TRUE | FALSE |
| Tenant parameters | default_table_store_format | Row Store | Row Store | Row Store | Columnar storage |
| Tenant parameters | _io_read_batch_size | 0K | 0K | 0K | 128K |
| Tenant parameters | _io_read_redundant_limit_percentage | 0 | 0 | 0 | 50 |
| System Variables | ob_query_timeout | 10s | 10s | 10s | 604800000000 (7 days) |
| System Variables | ob_trx_timeout | 1 Day | 1 Day | 1 Day | 604800000000 (7 days) |
| System Variables | parallel_degree_policy | MANUAL | MANUAL | MANUAL | AUTO |
| System Variables | parallel_min_scan_time_threshold | 1s | 1s | 1s | 10ms |
| System Variables | ob_sql_work_area_percentage | 0.05 | 0.05 | 0.05 | 0.3 |
| System Variables | collation_server | utf8mb4_general_ci | utf8mb4_general_ci | utf8mb4_general_ci | utf8mb4_bin |
| System Variables | collation_connection | utf8mb4_general_ci | utf8mb4_general_ci | utf8mb4_general_ci | utf8mb4_bin |