PL asynchronous commit optimization

In OceanBase Database, stored procedures are key to implementing user business logic. In the current implementation, transactions within stored procedures are committed synchronously, meaning the worker thread blocks until the transaction commit completes. Under normal circumstances, local transactions typically take microseconds, while distributed transactions take milliseconds (mainly due to network latency). As a result, distributed transactions can significantly increase overall PL latency and decrease throughput. To address this, V4.4.2 introduces asynchronous commit optimization for PL, alleviating these issues and further improving performance.

Enable PL asynchronous commit optimization

Use the ob_enable_pl_async_commit system variable to control whether to enable this optimization. If the system's throughput or TPS does not meet the business requirements, you can try to enable ob_enable_pl_async_commit to enable PL asynchronous commit optimization. Most commit scenarios will see a performance improvement, and high-concurrency scenarios will significantly improve the system's throughput.

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 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 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 above example inserts data in batches into two test tables using a loop, and commits immediately after each step. With the optimization enabled, the average time is 9.724s. Without the optimization, the average time is 10.976s. The performance improvement is approximately 11.41%. The more data processed in a single transaction, the longer the commit time, and the better the effect of parallelism with SQL, leading to a greater performance improvement.

Reference

ob_enable_pl_async_commit