KlustronDB Connection Snapshot Sharing Introduction

Background

During the process of executing queries such as multi-table joins, a Kluscomp instance will access multiple tables simultaneously. When two of these tables are both distributed on the same Klustore instance, a join contention problem may occur.

The cause of connection contention is that, in order to meet data consistency requirements, a Kluscomp instance restricts there to being only one MySQL connection between the Kluscomp instance and each Klustore instance within the same user session. This ensures that when accessing two tables, the snapshot of the transaction used on the Klustore instance is the same. However, a MySQL connection cannot handle multiple SQL requests simultaneously—it must completely process the current SQL before handling the next one. Therefore, when a session needs to access the same Klustore instance simultaneously, connection contention occurs.

Common solutions

In order to handle connection contention issues, the Kluscomp instance needs to materialize the results of the SQL currently being executed on the connection into a local temporary file, freeing up the connection for the most urgent operator to use, so that the entire execution process can continue.

The above figure is an example of a join contention, where the user executed "select * from t1, t2 where t1.a=t2.a" and used the MergeJoin algorithm (assuming the execution plan cost of MergeJoin is optimal).

During execution, one of the RemoteScan(t1) operators executes first, reading the data of t1 from Klustore instance A and returning the received data to the upper-level MergeJoin operator; then, the MergeJoin operator reads data from RemoteScan(t2) and joins it with t1, thereby triggering RemoteScan(t2) to also request the data of t2 from Klustore instance A.

However, the connection to Klustore instance A is still occupied by the RemoteScan(t1) operator. In order to free up a connection for RemoteScan(2) to use, the Kluscomp instance creates a temporary file "sql1_tmp.dat" for the RemoteScan(t1) operator, temporarily storing the results of the still-executing sql1 in that temporary file. When RemoteScan(t1) needs to read the next row of t1 later, it can directly read the content from the temporary file.

After sql1 has completely executed, the connection with Klustore instance A is handed over for use by RemoteScan(t2).

Obviously, if the amount of materialized data is large, although this design meets the requirements of transaction consistency, it also brings a series of significant issues, such as the IO of writing temporary files, the occupation of disk space, and the CPU consumption generated by encoding data, and so on.

Therefore, we are in urgent need of a more lightweight solution.

A lighter solution

The previous design was implemented based on the characteristics of the Klustore instance (i.e., MySQL), and it was entirely a design that compromised with MySQL.

Imagine if MySQL provided an interface that allowed multiple connections to share a snapshot, then the Kluscomp instances wouldn't have to worry about connections contending with each other; they would only need to provide each operator accessing the Klustore instance with an independent connection. Unfortunately, MySQL does not provide such an interface, since after all, it is just a standalone database.

Fortunately, we have experienced MySQL kernel development experts who can be completely self-sufficient.

After transforming MySQL, the following interfaces are provided:

start
transaction read only from session $(other session thread id)；

This interface is used to start a read-only transaction and create a snapshot identical to the specified other connection, and this operation is very lightweight.

Using this interface, the Kluscomp instances no longer have to materialize SQL results due to connection contention.

It's still the previous example. RemoteScan(t1) executes first and occupies the connection with Klustore instance A. When RemoteScan(t2) executes, since 'connection 1' is already occupied, a new connection to Klustore instance A is established. By calling the newly provided interface, a snapshot copy is obtained from 'connection 1', so it now has a transaction snapshot exactly the same as 'connection 1'. Compared to the previous design, it is really much more lightweight.

Result

In addition to resolving connection contention issues, this interface also unlocks the capability for Kluscomp instances to execute multiple processes in parallel, a powerful feature that had previously been disabled due to concerns over transaction consistency.

abc=# explain select count(1) From t;
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate  (cost=7961.55..7961.56 rows=1 width=8)
->  Gather (cost=7961.14..7961.54 rows=4 width=8)
 Workers Planned: 2
 ->  Parallel RemotePlan  (cost=7951.14..7951.14 rows=1 width=8)
 Shard :1 Remote SQL:
SELECT count(1) FROM `t` WHERE ($PARTIAL-QUAL)

When the amount of scanned table data is very large, the optimizer will partition the table into multiple non-overlapping ranges based on statistical information, generating parallel scan operators on a single table; then, based on the parallel scan operators on a single table, it further generates higher-level parallel aggregation operators.

The final execution plan is shown in the figure above. The session process on the Kluscomp instance forks multiple worker processes, and each worker process scans different ranges of the table in parallel, thus accelerating the execution of the SQL. Before the session process forks the worker processes, it first ensures that a connection (assume 'Connection 1') is established with Klustore instance A, and a transaction is started on that connection; when forking the worker processes, the flag of that connection is also passed to each worker process, so that when the worker processes establish a new connection with Klustore instance A, they can obtain the same snapshot from 'Connection 1'.

Summary

This article introduces how the KlustronDB eliminates data materialization operations performed by Kluscomp instances during connection contention through a snapshot-sharing mechanism between connections, and how it ensures that the data read during the execution of parallel plans meets transactional consistency requirements.

END