The commit time of a transaction varies based on the transaction type. We recommend that you reduce the proportion of cross-server distributed transactions in performance tuning. You can optimize a transaction model from the following perspectives:
Overall business logic.
Specific transactions.
Proportions of multi-table and single-table transactions, as well as the execution frequency of various SQL statements.
Transaction type statistics
SQL execution plans are divided into the local, remote, distributed, and uncertain types. A local execution plan indicates that the partition leader involved in the current statement is on the same server as the session. A remote execution plan indicates that the partition leader involved in the current statement is located on a server different from that of the session. A distributed or uncertain execution plan indicates that the relationship between the partition leader and the session is uncertain. They may be on the same server or multiple servers.
Single-server transactions demonstrate higher performance than distributed transactions. Based on the statistics on execution plan types, you can roughly estimate the proportion of distributed transactions and make the corresponding adjustments. Here is a sample SQL statement:
MySQL [oceanbase]> select plan_type, count(1) from gv$ob_sql_audit where
request_time > time_to_usec('2021-08-24 18:00:00') group by plan_type;
+-----------+----------+
| plan_type | count(1) |
+-----------+----------+
| 1 | 17119 |
| 0 | 9614 |
| 3 | 4400 |
| 2 | 23429 |
+-----------+----------+
4 rows in set
The value of plan_type can be 1, 2, or 3, indicating a local plan, remote plan, or distributed plan, respectively. Generally, 0 indicates an SQL statement without a plan, such as set autocommit=0/1 and commit.
Analysis of non-local plans
Compared with single-server transactions, transactions for requests that use non-local plans (except for those whose plan_type is 0) may involve cross-server operations and affect query performance. You can check for the following scenarios:
Single zone & single unit for primary zone.
Single zone & multiple units for primary zone.
RANDOM for primary zone.
Single zone & single unit for primary zone
Remote and uncertain plans are not expected in a single-unit deployment scenario for the following reasons:
The application directly connects to a follower of the tenant. You can verify this based on the statistics on execution plan types.
The application is connected to OBProxy, but the first SQL statement of the transaction cannot be correctly forwarded by OBProxy. As a result, the session and the partition leader involved in the transaction are on different servers. In this case, check the logs of all OBProxy instances in the cluster. Here is an example of the key log information:
fail to caculate partition id, just use tenant serverThe leaders of some partitions have just been switched, and the location cache maintained by the OBServer node or OBProxy has not yet been refreshed to the latest state.
For reason 1, connect the application to OBProxy. For reason 2, the current SQL statement is complex, and OBProxy cannot calculate the partitions to be accessed during parsing. Therefore, OBProxy sends the statement randomly. In this case, you must adjust the SQL statement by adding the partitioning key. For reason 3, you do not need to take any action.
Single zone & multiple units for primary zone
In this scenario, OBServer nodes in the same zone automatically balance the partition load. Therefore, cross-server transactions are highly probable. To avoid cross-server transactions, manage table groups based on the execution of statements in a transaction to ensure single-server execution whenever possible.
Here are some examples of table group management:
-- Non-partitioning scenario
create tablegroup tg1;
create table t1 (id1 int, id2 int) tablegroup tg1;
create table t2 (id1 int, id2 int) tablegroup tg1;
-- HASH partitioning (MySQL-compatible mode)
create tablegroup tg2 partition by hash partitions 2;
create table pg_trans_test2_1(id1 int, id2 int)
tablegroup tg2 partition by hash(id1 % 2) partitions 2;
create table pg_trans_test2_2(id1 int, id2 int)
tablegroup tg2 partition by hash(id1 % 2) partitions 2;
-- RANGE partitioning (MySQL-compatible mode)
create tablegroup tg3
partition by range columns 1 (
partition p0 values less than (10),
partition p1 values less than(20));
create table pg_trans_test3_1(id1 int, id2 int) tablegroup tg3
partition by range columns(id1)
(partition p0 values less than (10),
partition p1 values less than(20));
create table pg_trans_test3_2(id1 int, id2 int) tablegroup tg3
partition by range columns(id1)
(partition p0 values less than (10),
partition p1 values less than(20));
-- LIST partitioning (MySQL-compatible mode)
create tablegroup tg4 partition by list columns 1 (
partition p0 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
partition p1 values in (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
);
create table pg_trans_test4_1(id1 int, id2 int) tablegroup tg4 partition by list columns(id1) (
partition p0 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
partition p1 values in (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
);
create table pg_trans_test4_2(id1 int, id2 int) tablegroup tg4 partition by list columns(id1) (
partition p0 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
partition p1 values in (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
);
RANDOM for primary zone
In RANDOM deployment mode, the table leaders of the tenant are randomly distributed across servers, leading to a high probability of cross-server transactions.
Transaction commit optimization solution
Single-table and multi-table single-server transactions
Due to the adjustment to a single-log stream architecture in OceanBase AI Database, as long as the log stream leaders involved in a transaction are on the same server, the transaction is handled as a single-log stream transaction by default. This transaction model delivers the highest performance and therefore requires no parameter adjustments.
Cross-server transactions
You may use one of the following solutions:
Utilize the multi-server capabilities as much as possible.
Implement traffic load balancing on OBServer nodes.
You may take the following measures:
To avoid cross-server transactions, manage table groups based on the execution of statements in a transaction to ensure single-server execution whenever possible. For more information, see the Single zone & multiple units for primary zone section.
For batch import scenarios, make full use of the parallel execution capability of PDML, which is available in OceanBase AI Database V3.2.x and later versions.
Adjust the number of network threads (
net_thread_count) based on the load. By default, this parameter is set to0. After the process starts, the number of network threads required for the server is calculated automatically based on the number of CPU cores on the current server. The calculation formula ismin(6, cpu_core/8). You may manually adjust the value based on the actual situation. The parameter takes effect after the process restarts.