PL asynchronous commit optimization

In OceanBase Database, stored procedures are a key component for implementing user business logic. In the current implementation, transactions within stored procedures are committed synchronously. That is, after a worker thread calls the transaction commit interface, it must block and wait for the transaction to be committed before it can return. In non-extreme scenarios, local transaction commit times are typically in the microsecond range, while distributed transaction commit times are in the millisecond range, primarily due to network communication latency. Therefore, when distributed transactions are involved, PL overall latency significantly increases, and throughput decreases. To address this, V4.4.2 introduces PL asynchronous commit optimization, which alleviates these issues and further enhances PL performance.

Enable PL asynchronous commit optimization

Use the ob_enable_pl_async_commit system variable to control whether this optimization is enabled. If the system's throughput or TPS does not meet business requirements, consider enabling ob_enable_pl_async_commit to activate the PL asynchronous commit optimization. This optimization typically improves performance in most commit scenarios and significantly boosts throughput in high-concurrency situations.

Example

1. Create a test table.

   obclient> CREATE TABLE test1(a int);
   obclient> CREATE TABLE test2(a int);
   

2. Create a stored procedure named test.

   obclient> CREATE OR REPLACE PROCEDURE test(b int)
   AS
   a int; 
   BEGIN  
   FOR i IN 1..b LOOP
   FOR j IN 1..100 LOOP
   insert into test1 values(a);
   commit;
   if a = 0  then a := 1;
   else a := i + 1;
   end if;
   insert into test2 values(a);
   commit;
   END LOOP;
   END LOOP;
   delete from test1;
   delete from test2;
   end;
   /
   

3. Perform performance comparison tests.

   • Enable the PL asynchronous commit optimization.

     obclient> SET ob_enable_pl_async_commit=ON;
     obclient> call test(10);
     Query OK, 0 rows affected (9.487 sec)
     
     obclient> call test(10);
     Query OK, 0 rows affected (9.808 sec)
     
     obclient> call test(10);
     Query OK, 0 rows affected (10.013 sec)
     
     obclient> call test(10);
     Query OK, 0 rows affected (10.032 sec)
     obclient> call test(10);
     Query OK, 0 rows affected (9.280 sec)
     

   • Disable the PL asynchronous commit optimization.

     obclient> SET ob_enable_pl_async_commit=OFF;
     obclient> call test(100);
      Query OK, 0 rows affected (10.788 sec)
     
      obclient> call test(100);
      Query OK, 0 rows affected (11.616 sec)
     
      obclient> call test(100);
      Query OK, 0 rows affected (11.264 sec)
     
      obclient> call test(100);
      Query OK, 0 rows affected (10.378 sec)
     
      obclient> call test(100);
      Query OK, 0 rows affected (10.832 sec)
     

   The example above uses a loop to batch insert data into two test tables and commits after each operation. With the optimization enabled, the average time is 9.724s. Without the optimization, the average time is 10.976s, resulting in a performance improvement of approximately 11.41%. The more data processed in a single transaction, the longer the commit time, and the better the parallelism with SQL, leading to greater performance gains.

References

ob_enable_pl_async_commit