Introduction to KlustronDB Mirror Table Function

Preface

Mirror tables are also called mirrored tables, broadcast tables, or replicated tables. KlustronDB supports the mirror table feature to achieve better query performance.

Mirror Function Description

In the business systems of various users, there are usually some data tables that have the following characteristics:

1. The data volume is relatively small, for example, less than 1GB. Most companies' personnel information tables, department information tables, organizational structure tables, and even customer information tables have this characteristic.

2. Data changes are infrequent. For example, a table experiences only a few thousand insertions, updates, and other operations per day. The examples mentioned above also basically conform to this characteristic.

3. These tables may be joined with some very large tables (such as the orders table). For these tables, in KlustronDB users can define them as Mirror tables to achieve better query performance.

Specifically, KlustronDB ensures that a Mirror table in a cluster has the same data in each storage shard through the following methods.

1. When performing insert, update, or delete operations on a Mirror table, the compute nodes of KlustronDB, KlustronDB_server, will automatically perform the same data insert, update, or delete operations on the copy of this Mirror table in each storage shard, and these operations run within the same global transaction, thus guaranteeing ACID.

For this reason, if a table is updated very frequently, then updating the copies of this mirror table on all shards in a distributed transaction would seriously affect performance, and therefore it may not be suitable as a mirror table. This is a fairly general rule of thumb, and users can make their own decisions based on these considerations.

2. When adding a new storage shard, KlustronDB will automatically copy all Mirror tables in the system to the new shard. During this copying process, these Mirror tables can still be read and written, but the operation of adding new Mirror tables will be blocked until the copying is fully completed.

Joins between Mirror tables and large sharded tables can always be pushed down to be executed at the storage nodes, which also ensures that the join of these two tables is performed in parallel by multiple storage nodes, thereby achieving better query execution performance.

In OLAP applications, dimension tables usually have the characteristics of mirror tables and are suitable to be defined as mirror tables. In this way, in a star join query of an OLAP, multiple mirror tables joining with a huge fact table actually run in parallel on multiple storage shards, because this fact table will be shard-stored across multiple (preferably all cluster) storage shards.

At the same time, the join of two or more Mirror tables can always be pushed down to a certain (less loaded) shard for execution, which can also improve query performance to some extent.

Usage example

1. Connect the compute nodes of the cluster, and write to the Mirror table through the compute nodes:

psql postgres://abc:abc@192.168.0.136:59701/postgres
create table test1(id int primary key, address char(50), number int) with (shard = all);
insert into test1(id,address,number) values(1, 'abc', 001);
insert into test1(id,address,number) values(2, '2de', 002);

Then test1 is the Mirror table.

2. Call the add_shards interface of cluster_mgr

{
        "version":"1.0",
        "job_id":"",
        "job_type":"add_shards",
        "user_name":"kunlun_test",
        "timestamp":"202205131532",
        "paras":{
              "cluster_id":"${cluster_id}",
              "shards":"1",
              "nodes":"3",
              "storage_iplists":[
                      ${storage_iplists}
                ]
              }
}

After the add shard is successfully completed, if test1 exists on the new shard and the data in the test1 table is consistent with that in pg, then the Mirror function is complete.

END