PL asynchronous commit optimization

In OceanBase Database, stored procedures are an important carrier of user business logic. In the current implementation, transaction commits within stored procedures are synchronous. This means that after a worker thread calls the transaction commit interface, it must block and wait until the transaction commit is complete before returning. Under non-extreme circumstances, local transaction commits typically take only a few microseconds, while distributed transactions take several milliseconds (primarily due to network communication latency). Therefore, when distributed transactions are involved, the overall PL delay increases significantly, and throughput drops markedly. To address these issues, V4.4.2 introduces asynchronous PL commit optimization to improve PL performance.

Enable PL asynchronous commit optimization

You can use the ob_enable_pl_async_commit system variable to control whether to enable this optimization. When the system's throughput or TPS does not meet business requirements, you can try enabling ob_enable_pl_async_commit to activate PL asynchronous commit optimization. This will provide performance improvements in most commit scenarios and significantly increase system throughput under high-concurrency conditions.

Usage examples

1. Create a test table.

   obclient> CREATE TABLE test1(a int);
   

2. Create a stored procedure named test.

   obclient> DELIMITER $$
   CREATE PROCEDURE test()
   BEGIN
   DECLARE counter INT DEFAULT 1;
   WHILE counter <= 1000 DO
       INSERT INTO t0 (a) VALUES (counter);
       commit;
       SET counter = counter + 1;
   END WHILE;
   END $$
   
   DELIMITER ;
   

3. Performance comparison test execution.

   • Enable PL asynchronous commit optimization.

     obclient> SET ob_enable_pl_async_commit=ON;
     obclient> call test();
     Query OK, 0 rows affected (5.376 sec)
     obclient> call test();
     Query OK, 0 rows affected (5.122 sec)
     obclient> call test();
     Query OK, 0 rows affected (5.131 sec)
     obclient> call test();
     Query OK, 0 rows affected (5.117 sec)
     obclient> call test();
     Query OK, 0 rows affected (4.946 sec)
     

   • Disable PL asynchronous commit optimization.

     obclient> SET ob_enable_pl_async_commit=OFF;
     obclient> call test();
      Query OK, 0 rows affected (5.489 sec)
      obclient> call test();
      Query OK, 0 rows affected (5.484 sec)
      obclient> call test();
      Query OK, 0 rows affected (5.481 sec)
      obclient> call test();
      Query OK, 0 rows affected (5.440 sec)
      obclient> call test();
      Query OK, 0 rows affected (5.501 sec)
     

   Performs a loop operation to insert 1,000 rows into a database table, committing each row immediately after insertion. With optimization enabled, the average time taken is 5.1384s. Without optimization, the average time taken is 5.479s, representing an approximate performance improvement of 6%. The more data processed in a single transaction—the larger the transaction—the longer the commit time, and consequently, the better the parallelization effect with SQL, leading to greater performance improvements.

References

ob_enable_pl_async_commit