Experience the hot row update capability of OceanBase Database

With the development of online transactions and the e-commerce industry, the high-concurrency pressure on hotspot data in business systems has gradually become a challenge. Frequent balance updates on hot accounts within a short period, or flash sales of hot products during marketing campaigns, are direct manifestations of this scenario. The essence of hotspot updates is the highly concurrent modification of certain field values (such as balance or inventory) in the same row of a database within a brief timeframe. The bottleneck lies in the fact that, to maintain transaction consistency, a relational database must follow a process of "lock acquisition -> update -> log writing and commit -> lock release" for each row update, which is essentially serial. Therefore, to improve hot row update capability, one must shorten the lock hold time as much as possible and reduce disk I/O overhead when a large number of transactions are committed concurrently.

Although the "Early Lock Release" (ELR) technique was proposed in academia long ago, its complex exception-handling scenarios have led to few mature industrial implementations. OceanBase Database has addressed this issue through continuous exploration, proposing a distributed architecture-based implementation to enhance concurrent single-row update capability in similar business scenarios. This is one of the key capabilities of OceanBase Database's "Scalable OLTP."

In addition to optimization at the lock level, OceanBase Database also provides another hot row optimization solution—group commit. In high-concurrency hot row update scenarios, a large number of transactions individually commit redo logs within a short time, with each commit corresponding to at least one disk write, making disk I/O a potential throughput bottleneck. Group commit merges the redo logs of multiple concurrent transactions into a single disk write, reducing multiple flushes to just one, thereby significantly improving the write throughput for hot rows.

This topic demonstrates the usage and performance comparison between OceanBase Database's ELR and group commit solutions for hot row optimization by constructing a scenario of highly concurrent single-row updates. Since verification occurs under high-concurrency stress, it is recommended to use node specifications at least equivalent to those in this example for a better experience and results. The design and implementation principles of ELR and group commit are complex and will not be discussed in detail here.

Hot row ELR

This example uses a node with 16 cores and 128 GB. Follow these steps to experience OceanBase Database's ELR feature.

Step 1: Create a test table and insert test data

First, create a table in the test database and insert test data.

CREATE TABLE `sbtest1` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `k` int(11) NOT NULL DEFAULT '0',
   `c` char(120) NOT NULL DEFAULT '',
   `pad` char(60) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
);

INSERT INTO sbtest1 VALUES(1,0,'aa','aa');

In this example, concurrent updates are performed on the k column using a primary key query, such as the statement UPDATE sbtest1 SET k=k+1 WHERE id=1. You can insert more data for testing, but since this is a stress test targeting a single row, it has little impact on the overall result.

Step 2: Construct a concurrent update scenario

In this example, Python multithreading is used to simulate concurrent updates. Start 50 threads simultaneously, each concurrently incrementing the value of the k field by 1 for the row with id=1. You can also directly use the following script ob_elr.py for testing in your own environment by modifying the database connection information within the script.

#!/usr/bin/env python3

from concurrent.futures import ThreadPoolExecutor
import pymysql
import time
import threading

# database connection info
config = {
    'user': 'root@test',
    'password': '****',
    'host': 'xxx.xxx.xxx.xxx',
    'port': 2881,
    'database': 'test'
}

# parallel thread and updates in each thread
parallel = 50
batch_num = 2000

# update query
def update_elr():
    update_hot_row = ("update sbtest1 set k=k+1 where id=1")
    cnx = pymysql.connect(**config)
    cursor = cnx.cursor()

    for i in range(0,batch_num):
        cursor.execute(update_hot_row)
    cursor.close()
    cnx.close()

start=time.time()

with ThreadPoolExecutor(max_workers=parallel) as pool:
    for i in range(parallel):
        pool.submit(update_elr)

end = time.time()
elapse_time = round((end-start),2)

print('Parallel Degree:',parallel)
print('Total Updates:',parallel*batch_num)
print('Elapse Time:',elapse_time,'s')
print('TPS on Hot Row:' ,round(parallel*batch_num/elapse_time,2),'/s')

Step 3: Execute the test with default configurations

For comparison, first test with ELR disabled by default. Execute the ob_elr.py script directly on the test machine.

This example uses 50 concurrent threads, updating a total of 100,000 times.

./ob_elr.py

After execution, the test script outputs the execution time and TPS:

[root@obce00 ~]# ./ob_elr.py
Parallel Degree: 50
Total Updates: 100000
Elapse Time: 54.5 s
TPS on Hot Row: 1834.86 /s

The test result is as follows:

With ELR disabled under the default configuration, the TPS for concurrent single-row updates in this test environment is 1834.86/s.

Step 4: Enable OceanBase Database's ELR configuration

Next, enable OceanBase Database's hot row feature. First, log in to the cluster's sys tenant as the root user.

[root@obce00 ~]# obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A -p -c

Then set the following two parameters. The effective scope of the enable_early_lock_release parameter can be specified as a specific tenant or all tenants, i.e., tenant=all.

ALTER SYSTEM SET _max_elr_dependent_trx_count = 1000;
ALTER SYSTEM SET enable_early_lock_release = true tenant = test;

Step 5: Enable OceanBase Database ELR for testing

After enabling the hot row feature, run the test again. Before execution, check the record with id=1 in table sbtest1. The value of the k field is 100000 because 100,000 updates were just performed under the default configuration.

SELECT * FROM sbtest1 WHERE id=1;
+----+--------+----+-----+
| id | k      | c  | pad |
+----+--------+----+-----+
|  1 | 100000 | aa | aa  |
+----+--------+----+-----+
1 row in set 

Next, execute ob_elr.py again on the test machine. Still use 50 concurrent requests and update a total of 100,000 times.

./ob_elr.py

After execution, the test script outputs the execution time and TPS:

[root@obce00 ~]# ./ob_elr.py
Parallel Degree: 50
Total Updates: 100000
Elapse Time: 12.16 s
TPS on Hot Row: 8223.68 /s

The test result is as follows:

After enabling OceanBase Database's ELR capability, the TPS for single-row updates reaches 8223.68/s, which is about 4.5 times higher than under the default configuration.

You can also see that the k value in table sbtest1 is now 200000, indicating that 100,000 updates were performed this time as well.

SELECT * FROM sbtest1 WHERE id=1;
+----+--------+----+-----+
| id | k      | c  | pad |
+----+--------+----+-----+
|  1 | 200000 | aa | aa  |
+----+--------+----+-----+
1 row in set 

This example only covers the scenario of concurrent single-row updates. OceanBase Database's ELR capability also supports concurrent updates for multi-statement transactions. Depending on the number of statements and the scenario, significant performance improvements can still be achieved.

Furthermore, OceanBase Database's ELR can be applied in multi-region deployment scenarios with high network latency. For example, a single transaction might take 30 ms under normal conditions. With ELR enabled in a concurrent environment, TPS throughput can increase by nearly a hundredfold.

Since OceanBase Database's log protocol is built on Multi-Paxos and optimizes the 2PC commit process, transaction consistency is still guaranteed after enabling ELR, even if a node fails and restarts or a leader switchover occurs.

Hot row group commit

In high-concurrency hot row update scenarios, a large number of transactions need to flush their redo logs to disk individually upon commit, resulting in at least one disk write per commit. The disk I/O overhead cannot be ignored. OceanBase Database provides the group commit solution to address this issue: it merges the redo logs of multiple concurrent transactions into a single group, consolidating them into one disk write, turning multiple disk flushes into one. This significantly reduces disk I/O pressure and improves write throughput for hot rows.

In this example, a node with a 16 cores and128 GB is used. The following steps guide you through experiencing OceanBase Database's hot row group commit feature.

Step 1: Create a test table and insert test data

First, create a table in the test database and insert test data.

CREATE TABLE `sbtest2` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `k` int(11) NOT NULL DEFAULT '0',
   `c` char(120) NOT NULL DEFAULT '',
   `pad` char(60) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
);

INSERT INTO sbtest2 VALUES(1,0,'aa','aa');

In this example, concurrent updates are performed on the k column using a primary key query, similar to the statement UPDATE sbtest2 SET k=k+1 WHERE id=1.

Step 2: Construct a concurrent update scenario

In this example, Java multithreading is used to simulate concurrent updates. Fifty threads are started simultaneously. Each thread executes the UPDATE statement with the GROUP_COMMIT hint 80,000 times in auto-commit mode using a PreparedStatement. You can directly use the following code GroupCommitTest.java for testing in your own environment by modifying the database connection information.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class GroupCommitTest {

    static final String URL = "jdbc:oceanbase://xxx.xxx.xxx.xxx:2881/test?useServerPrepStmts=true&cachePrepStmts=true";
    static final String USER = "root@test";
    static final String PASSWORD = "****";

    static final int PARALLEL = 50;
    static final int BATCH_NUM = 80000;

    public static void main(String[] args) throws Exception {
        ExecutorService pool = Executors.newFixedThreadPool(PARALLEL);
        CountDownLatch latch = new CountDownLatch(PARALLEL);

        long start = System.currentTimeMillis();

        for (int i = 0; i < PARALLEL; i++) {
            pool.submit(() -> {
                try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
                    conn.setAutoCommit(true);

                    PreparedStatement ps = conn.prepareStatement(
                        "UPDATE /*+ GROUP_COMMIT(TRUE) */ sbtest2 SET k = k + 1 WHERE id = ?"
                    );

                    for (int j = 0; j < BATCH_NUM; j++) {
                        ps.setInt(1, 1);
                        ps.executeUpdate();
                    }
                    ps.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                latch.countDown();
            });
        }

        latch.await();
        pool.shutdown();

        long end = System.currentTimeMillis();
        double elapse = (end - start) / 1000.0;

        System.out.println("Parallel Degree: " + PARALLEL);
        System.out.println("Total Updates: " + PARALLEL * BATCH_NUM);
        System.out.println("Elapse Time: " + elapse + " s");
        System.out.println("TPS on Hot Row: " + Math.round(PARALLEL * BATCH_NUM / elapse) + " /s");
    }
}

Compile and execute:

javac -cp oceanbase-client.jar GroupCommitTest.java
java -cp .:oceanbase-client.jar GroupCommitTest

Step 3: Execute the test under default configuration

For comparison, first test with group commit disabled by default, and ensure group commit is off using the following statement:

ALTER SYSTEM SET _enable_hotspot_group_commit = false tenant = test;

Execute GroupCommitTest on the test machine. In this example, 50 concurrent threads are used to perform a total of 4,000,000 updates.

java -cp .:oceanbase-client.jar GroupCommitTest

After execution, the test program outputs the execution time and TPS:

[root@obce00 ~]# java -cp .:oceanbase-client.jar GroupCommitTest
Parallel Degree: 50
Total Updates: 4000000
Elapse Time: 486.40 s
TPS on Hot Row: 8224 /s

Under the default configuration with group commit disabled, the TPS for single-row concurrent updates in this test environment is 8,224/s.

Step 4: Enable group commit for OceanBase Database

Next, enable the hot row group commit feature for OceanBase Database. First, log in to the sys tenant of the cluster as the root user.

[root@obce00 ~]# obclient -h127.0.0.1 -P2881 -uroot@sys -Doceanbase -A -p -c

Then set the group commit parameters. The _enable_hotspot_group_commit parameter can specify a specific tenant or all tenants, i.e., tenant=all.

ALTER SYSTEM SET _enable_hotspot_group_commit = true tenant = test;

Step 5: Test with group commit enabled for OceanBase Database

After enabling the group commit feature, run the test again.

java -cp .:oceanbase-client.jar GroupCommitTest

After execution, the test script outputs the execution time and TPS:

[root@obce00 ~]# java -cp .:oceanbase-client.jar GroupCommitTest
Parallel Degree: 50
Total Updates: 4000000
Elapse Time: 42.19 s
TPS on Hot Row: 94822 /s

The test results are as follows:

After enabling the hot row group commit capability, OceanBase Database achieves a TPS of 94,822/s for single-row updates, which is approximately 11.5 times higher than when group commit is disabled.

You can also see that the value of k for table sbtest2 is 8,000,000, indicating that 4,000,000 updates were performed this time as well.

SELECT * FROM sbtest2 WHERE id=1;
+----+---------+----+-----+
| id | k       | c  | pad |
+----+---------+----+-----+
|  1 | 8000000 | aa | aa  |
+----+---------+----+-----+
1 row in set 

This example only demonstrates the use of group commit in a single-row concurrent update scenario. OceanBase Database's group commit capability is also applicable to concurrent update scenarios involving multi-statement transactions. Depending on the number of statements and the specifics of the scenario, varying degrees of performance improvement can be achieved.

Since OceanBase Database's log protocol is based on Multi-Paxos, after enabling group commit, transaction consistency is still guaranteed even if a node fails and restarts or a leader switchover occurs.