Notice
PL asynchronous commit in OceanBase AI Database is currently an experimental feature and is not recommended for production environments.
In OceanBase AI 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 conditions, 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 latency increases significantly, and throughput drops markedly. To address these issues and further improve PL performance, OceanBase AI Database supports asynchronous PL commit optimization.
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
Create a test table.
obclient> CREATE TABLE test1(a int);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 ;Perform the 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)
Performs a loop operation to insert 1,000 rows into a database table. Each row is inserted as a transaction, which is immediately committed after insertion. With optimization enabled, the average time consumed is
5.1384s. Without optimization, the average time consumed is5.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 and the greater the performance improvement.