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 business requirements, you can try enabling ob_enable_pl_async_commit to activate the PL asynchronous commit optimization. This optimization typically improves performance in most commit scenarios and significantly enhances throughput in high-concurrency situations.

Example

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. Perform a performance comparison test.

   • 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)
     

   Insert 1,000 records into the database table and commit each transaction immediately after inserting each record. With the optimization enabled, the average time is 5.1384s. Without the optimization, the average time is 5.479s. The performance improves by approximately 6%. The more data processed in a single transaction, the longer the commit time, and the better the performance improvement when combined with SQL parallelism.

Reference

ob_enable_pl_async_commit