PL asynchronous commit optimization

In OceanBase Database, stored procedures are crucial for implementing user business logic. In the current implementation, transactions within stored procedures are committed synchronously. This means that the worker thread must block and wait for the transaction to complete before it can return. Under normal circumstances, local transactions take only microseconds to commit, while distributed transactions take milliseconds (primarily due to network communication latency). As a result, when distributed transactions are involved, the overall latency of PL increases significantly, and the throughput decreases. To address this issue, OceanBase Database V4.4.2 introduces PL asynchronous commit optimization, which improves the performance of PL by reducing the latency and increasing the throughput.

Enable PL asynchronous commit optimization

You can use the ob_enable_pl_async_commit system variable to enable or disable this optimization. If the system's throughput or TPS does not meet the business requirements, you can enable 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 each insertion. With the optimization enabled, the average time is 5.1384s. Without the optimization, the average time is 5.479s, resulting in a performance improvement of about 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.

References

ob_enable_pl_async_commit