Best practices for hot tables in OceanBase Database

A hot table in OceanBase Database refers to data rows or tables that are frequently read from or written to in high-concurrency scenarios, leading to system performance bottlenecks. Hot table issues are often associated with SQL workload hotspots and are particularly noticeable in read-intensive scenarios.

Hot table issues can have the following impacts on the database system:

• Performance bottlenecks: Excessive consumption of CPU, memory, or disk I/O.
• Lock contention: Concurrent updates or queries on the same row may trigger lock contention issues, increasing transaction wait time.
• Deteriorated user experience: Extended response times can significantly impact business performance, potentially leading to service unavailability.

Classification of hot tables

Table schema design: Avoid hot tables from the start

When designing the table schema, consider the following principles to avoid hotspot issues and reduce the complexity of future optimizations:

1. Appropriate selection of primary keys

   • Avoid using auto-increment IDs as primary keys (which can easily create hotspots)
   • Use composite primary keys or distributed IDs
   • Consider the distribution characteristics of your business logic

2. Partitioning strategy design

   • Time-based partitioning: suitable for log tables and transaction tables
   • User ID-based partitioning: suitable for user-related tables
   • Business dimension-based partitioning: suitable for multi-tenant scenarios

3. Index design

   • Avoid single-point hot indexes
   • Design composite indexes appropriately
   • Consider the dispersion of query patterns

Design example

-- Table design example to avoid hotspots
CREATE TABLE user_order (
    user_id BIGINT,           -- Partitioning key
    sequence_id BIGINT,       -- Sequence number
    order_amount DECIMAL(10,2),
    create_time TIMESTAMP,
    PRIMARY KEY (user_id, sequence_id)  -- Composite primary key
) PARTITION BY HASH(user_id) PARTITIONS 16;  -- Partitioned by user ID

-- Create a table group
CREATE TABLEGROUP tg_user_related SHARDING = 'PARTITION';
-- Add the user_order table to the table group
ALTER TABLEGROUP tg_user_related ADD user_order;

-- View tables in the table group
SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TG_USER_RELATED';

Identify hot tables

OceanBase Database does not provide a direct view for hot tables, but you can recognize them by combining and analyzing data from multiple performance views. The following are recommended best practices for developers and DBAs to use when tuning performance and planning resource isolation.

Method 1: Use the GV$OB_SQL_AUDIT view to identify hot SQL statements

OceanBase Database provides the audit view GV$OB_SQL_AUDIT, which can be used to perform aggregate analysis of SQL execution behavior and identify resource-intensive SQL statements, thereby helping to locate hot tables. The following query finds the top 10 SQL statements with the highest number of rows read per unit time, which are often the root cause of read hotspots.

SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/
  svr_ip, sql_id,
  tenant_id, tenant_name, user_name, db_name, plan_id,
  COUNT(*) executions,
  MAX(event) event,
  MAX(table_scan) table_scan,
  SUM(CASE WHEN ret_code = 0 THEN 0 ELSE 1 END) fail_times,
  SUM(rpc_count) rpc_count,
  SUM(retry_cnt) retry_cnt,
  SUM(CASE WHEN plan_type = 2 THEN 1 ELSE 0 END) remote_plans,
  SUM(CASE WHEN is_hit_plan = 1 THEN 0 ELSE 1 END) miss_plans,
  ROUND(AVG(elapsed_time)) elapsed_time,
  ROUND(MAX(elapsed_time)) max_elapsed_time,
  ROUND(AVG(execute_time)) execute_time,
  ROUND(AVG((execute_time - total_wait_time_micro + get_plan_time))) cpu_time,
  ROUND(AVG(queue_time)) queue_time,
  ROUND(AVG(net_wait_time)) netwait_time,
  ROUND(AVG(user_io_wait_time)) iowait_time,
  ROUND(AVG(return_rows)) return_rows,
  ROUND(AVG(affected_rows)) affected_rows,
  SUM(memstore_read_row_count + ssstore_read_row_count) AS total_row_count
FROM GV$OB_SQL_AUDIT
WHERE is_inner_sql = 0
GROUP BY svr_ip, sql_id 
ORDER BY total_row_count DESC 
LIMIT 10;

Key metrics:

• total_row_count: the total number of rows read (memstore_read_row_count + ssstore_read_row_count), which is used to identify read hotspots.
• retry_cnt: the number of retries. A high value may indicate lock conflicts (write hotspots).
• cpu_time: the actual CPU consumption time. If it is much higher than the average value, it indicates a computationally intensive SQL statement.
• event: lock wait events (such as row lock wait) can directly point to write hotspots.

Method 2: Use OCP to monitor hot tables

Although the preceding SQL statement can be executed manually, we recommend using OceanBase Cloud Platform (OCP) for continuous monitoring and trend analysis, as it is the most straightforward and code-free approach. OCP provides out-of-the-box hot table monitoring dashboards, automatic aggregation, and anomaly detection. It includes monitoring metrics such as SQL TopN, table access frequency, and wait events, which allow you to directly identify hot tables without the need to manually write complex SQL statements. For more information, see the OCP documentation.

Method 3: Use sql_diagnoser with custom rules and scripts

This method is suitable for batch automated analysis:

1. Start sql_diagnoser and connect to the target tenant.

2. Set the time window (such as the last 1 hour) and increase the sampling number (the default value is 10,000, which can be changed to 50,000).

3. Configure custom diagnostic rules (similar to SQL WHERE conditions), for example:

   -- SQL statements executed more than 100 times with each execution taking more than 100 ms will be marked as suspicious.
   count > 100 AND elapsed_time > 100000
   

4. Export the diagnostic results (CSV or JSON).

5. Use a script (Python/Shell) to extract table names from SQL statements, count the frequency of each table, and identify hot tables.

Typical scenarios and solutions

Scenario 1: Buffer table optimization

Scenario description: A buffer table is a physical table used to cache data. It temporarily stores a large amount of data during transactions and is deleted after the data is processed. Due to the append-only write feature of the LSM-tree in OceanBase Database, buffer tables may cause memory and storage space to expand, which affects the performance of queries and DML operations.

Solution:

1. Set the table_mode attribute

   -- Specify table_mode when creating a buffer table.
   CREATE TABLE buffer_table (
       id BIGINT,
       data VARCHAR(1000),
       create_time TIMESTAMP
   ) TABLE_MODE = 'queuing';
   
   -- Or modify the table_mode of an existing table.
   ALTER TABLE buffer_table TABLE_MODE = 'queuing';
   

   Except for the NORMAL mode, all other TABLE_MODE values (including QUEUING, MODERATE, SUPER, and EXTREME) are considered QUEUING tables.

   Mode	Probability of triggering a major compaction after minor compactions	Description
   NORMAL	Very low	The default mode, which rarely triggers major compactions after minor compactions. It is suitable for scenarios where the performance requirements for write-intensive and read-consistency are not high.
   QUEUING	Low	The basic QUEUING table, which slightly promotes the major compaction process.
   MODERATE	Moderate	More proactive in driving major compactions, balancing performance and resource consumption.
   SUPER	High	Frequent major compactions, suitable for workloads sensitive to query latency.
   EXTREME	Very high	The most aggressive major compaction strategy, aiming to complete major compactions as much as possible.

2. Design a reasonable partitioning strategy

   -- Example of partitioning by time.
   CREATE TABLE buffer_table (
         id BIGINT,
         data VARCHAR(1000),
         create_time TIMESTAMP
   ) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time)) (
         PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
         PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01 00:00:00'))
   ) TABLE_MODE = 'queuing';
   

Scenario 2: Replicated table optimization

Scenario description: Parameter tables and traffic routing tables with high read and low write operations require high availability and low-latency reads. These tables typically experience extremely high access volumes, and the business demands optimal query performance with queries being as localized as possible. Therefore, it is recommended to use the replicated table strategy.

Solution:

1. Create a replicated table

   -- Convert a regular table into a replicated table
   ALTER TABLE config_table DUPLICATE_SCOPE = 'cluster';
   

2. Characteristics of replicated tables

   • Each replica stores complete data, supporting localized queries.
   • You can read the latest data from any healthy replica, enhancing read concurrency.
   • Reduces cross-node network overhead, delivering exceptional read performance.
   • Sacrifices a small amount of write performance to achieve higher read concurrency.
   • Ideal for high-traffic scenarios with extreme query performance requirements.

Scenario 3: Optimization for hot row lock conflicts

Scenario description: When a business scenario involves high-frequency concurrent updates to hot rows (such as user points and order status transitions), directly using traditional single-row transactions or full-table DELETE operations can easily lead to severe lock conflicts and performance bottlenecks.

Solution:

1. V4.X implements row locking (automatically executed by the database, no user intervention required).

2. Use explicit transactions and commit them timely.

3. Delay update operations until the commit point to reduce the time locks are held.

4. Distribute hot rows by adding a partitioning key, turning the competition on a single row into competition across multiple different rows.

-- Example of explicit transaction processing
START TRANSACTION;
-- Check if the user exists
SELECT points INTO @points FROM user_points WHERE user_id = 123 FOR UPDATE;
-- Update the points
UPDATE user_points SET points = points + 100 WHERE user_id = 123;
-- Update the order status
UPDATE order_status SET status = 'processing' WHERE order_id = 456;
-- Commit the transaction
COMMIT;
-- If an error occurs, rollback
-- ROLLBACK;

Scenario 4: Optimization for batch data updates on hot tables

Scenario description: When you need to perform large-scale data updates or deletes on a hot table, traditional row-by-row operations can cause severe performance issues and lock conflicts.

Solution:

1. Use an appropriate partitioning strategy and process in batches by partition.

   -- Example of partitioning by user ID
   CREATE TABLE user_data (
       user_id BIGINT,
       data VARCHAR(1000)
   ) PARTITION BY RANGE(user_id) (
       PARTITION p0 VALUES LESS THAN (1000000),
       PARTITION p1 VALUES LESS THAN (2000000),
       PARTITION p2 VALUES LESS THAN (3000000),
       PARTITION p3 VALUES LESS THAN (4000000)
   );
   

2. Convert batch deletes into TRUNCATE PARTITION or DROP PARTITION operations.

3. Use an appropriate batch size to control the size of each transaction and avoid oversized transactions.

Scenario 5: Aggregate table optimization

Scenario description: For tables used in report statistics, BI analysis, and aggregation calculations, there is usually a massive amount of data and high query complexity.

Solution:

1. Columnstore tables

   -- Create a columnstore table
   CREATE TABLE sales_data (
       product_id INT,
       sale_date DATE,
       amount DECIMAL
   ) WITH COLUMN GROUP(each column);
   

   Advantages of columnstore tables:

   • Improve the performance of OLAP queries (such as SUM, AVG, and GROUP BY).
   • Reduce I/O operations by reading only the relevant columns.

2. Parallel queries

   -- Manually specify the degree of parallelism
   SELECT /*+ENABLE_PARALLEL_DML PARALLEL(4)*/
          SUM(amount)
   FROM sales_data;
   

3. Partitioning and table groups

   Partitioning strategy:

   • Perform primary or secondary partitioning based on dimensions such as time and region.
   • Reduce the amount of data scanned in a single query, thereby improving query efficiency.

   Table group design:

   • Place highly related tables on the same machine to utilize partition-wise join and reduce network transmission.

4. Materialized views and skip index

   • Materialized views: Precompute aggregation results to speed up complex queries.

     Example:

     -- Create the sales_data table
     CREATE TABLE sales_data (
         product_id INT,
         sale_date DATE,
         amount DECIMAL
     );
     
      -- Create a materialized view
     
      CREATE MATERIALIZED VIEW mv_sales_filtered
      ENABLE QUERY REWRITE
      AS
      SELECT s.product_id, s.amount, s.sale_date, p.product_name
      FROM sales_data s
      JOIN products p ON s.product_id = p.id
      WHERE s.sale_date >= '2024-01-01';
     
      -- Query using the materialized view
      SELECT product_id, amount, sale_date, product_name
      FROM mv_sales_filtered
      WHERE product_id = 101;
     
      -- Query without using the materialized view
      SELECT s.product_id, s.amount, s.sale_date, p.product_name
      FROM sales_data s
      JOIN products p ON s.product_id = p.id
      WHERE s.product_id = 101
      AND s.sale_date >= '2024-01-01';
     

   • Skip index: Skip scanning irrelevant data blocks, which is suitable for queries with high filtering rates.

     Example:

     -- 1. Create a table with skip index
     CREATE TABLE sales_data_skidx(
       product_id INT SKIP_INDEX(MIN_MAX),
       sale_date DATE SKIP_INDEX(MIN_MAX),
       amount DECIMAL(10,2) SKIP_INDEX(MIN_MAX, SUM),
       region VARCHAR(50) SKIP_INDEX(MIN_MAX)
     );
     
     -- 2. Insert test data
     INSERT INTO sales_data_skidx VALUES
       (1, '2024-01-01', 100.50, 'North'),
       (2, '2024-01-02', 200.75, 'South'),
       (3, '2024-01-01', 150.25, 'North'),
       (4, '2024-01-03', 300.00, 'East');
     
     -- 3. Range query using skip index
     SELECT /*+ TRACE_ID('skip_index_range') */
       COUNT(*) FROM sales_data_skidx
       WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-02';
     
     -- 4. Aggregation query using skip index
     SELECT /*+ TRACE_ID('skip_index_agg') */
       SUM(amount) FROM sales_data_skidx
       WHERE region = 'North';
     

Scenario 6: Keyword-based throttling optimization

Scenario description: When specific keywords or parameter values are frequently accessed, keyword-based throttling can help alleviate hotspot issues. This method is especially suitable for read-intensive scenarios and can quickly reduce database pressure.

Solution:

1. Create a throttling rule

   -- Limit the number of concurrent queries for a specific table.
   CREATE CONCURRENT_LIMITING_RULE IF NOT EXISTS `test_limit_rule`
   ON test.test_table
   TO 'root'@'%'
   FOR SELECT
   FILTER BY KEYWORD('test_table')
   WITH MAX_CONCURRENCY = 10;
   

References

• Typical scenarios and cases for SQL tuning
• Overview of data tables
• Replicated table
• Parallel execution optimization tips
• Try out the hot row update feature of OceanBase Database