The value of using KlustronDB in single-shard mode

Preface

For most companies' application systems, the amount of data is usually only tens of GB, and a MySQL master-slave replication cluster can easily handle it. So in this case, what value does using KlustronDB have? In fact, there is still tremendous value, and this article will comprehensively answer this question.

Advantages of Using a Single Shard in KlustronDB

High availability and automatic failover

KlustronDB_storage Fullsync's high-performance strong synchronization feature ensures the high availability of each KlustronDB_storage shard.

At the same time, the cluster_mgr module of KlustronDB automatically monitors the primary node, and when a failure of the primary node is detected, it automatically elects a new primary node. Both kunlun-proxysql and Kunlun_server can automatically switch to work with the new primary node, making KlustronDB equipped with complete automatic fault recovery and high availability capabilities, with the entire fault handling process requiring no user intervention.

Compared to MySQL Group Replication or semisync replication, KlustronDB has higher performance and lower latency, and consumes fewer computing resources.

The reasons are as follows:

MGR requires that before flushing a transaction's binlog, all transactions that are being committed must hold their transaction locks and wait for the standby to acknowledge (ACK) the receipt of the transaction's binlog. This can cause long-term blocking of all active transactions that conflict with the transaction locks of those waiting for ACKs, significantly reducing the system's concurrent processing capability, and prolonging the execution time of those blocked transactions, affecting the responsiveness of the business system. In contrast, KlustronDB_storage Fullsync performs after-commit waiting; before it starts waiting for the standby's binlog ACK, the transactions being committed have already released their transaction locks, so waiting for the standby's response does not block those conflicting transactions, ensuring high overall cluster throughput and low latency.
At the same time, fullsync does not need to occupy worker threads while waiting for the standby ACK, whereas semisync and MGR both need to occupy worker threads. This results in MySQL needing to start more worker threads to handle the business load. Each worker thread consumes CPU time slices, memory, and requires the OS to manage their state, all of which are consumption of computing resources.
Due to these technical advantages, KlustronDB_storage fullsync has up to 5 times the performance advantage over MGR in performance tests such as sysbench.

High performance

Not only does KlustronDB_storage have better replication performance than MySQL, but KlustronDB_server (Kluscomp instances) also possesses a series of high-performance and scalable capabilities in data analysis, query processing, and other aspects, as detailed below.

Read-write separation

When Kluscomp instances execute read-only query statements (i.e., select statements), they can automatically send select queries to the standby nodes of the storage shard to obtain data in order to execute the select query statements.

This behavior does not require user intervention, so the user's program can use this feature without modification. Of course, users can also disable this feature at the connection level or globally. Executing read-only queries on the standby node can reduce the load on the primary node.

However, it should be noted that some read-only queries cannot be sent to the standby server. For example, after updating a table and then querying that table's data in the same transaction, if you need to ensure that you get the latest data and avoid inconsistent query results, this select statement by default cannot be sent to the standby server for execution, unless the user lowers the consistency level and forces reading potentially outdated data from the standby server.

In other words, KlustronDB's read-write separation technology not only needs to distribute the load on the primary node, but also ensure the consistency of query results, and provide users with ample choices. This is the advantage of KlustronDB over some open-source middleware projects.

Parallel select query

KlustronDB supports multi-level parallel execution of select queries, including parallel query processing within Kluscomp instances, parallel queries between Kluscomp instances and Klustore instances, and parallel queries within Klustore instances.

The parallel processing capabilities of these three levels greatly enhance KlustronDB's query performance, especially significantly improving the performance of complex queries for data analysis.

Parallelism within a Kluscomp instance refers to multiple threads executing a specific node of a query plan. Some query processing functions in KlustronDB_server have parallel query capabilities, including nodes such as NestedLoopJoin, HashJoin, MergeJoin, Append, Aggregate, etc.; this parallel capability is inherited from PostgreSQL.

Parallelism between Kluscomp instances and Klustore instances: it refers to the ability of Kluscomp instances to asynchronously send SQL statements to read and write target data on Klustore instances, so that all Klustore instances containing the target data will execute the received SQL statements in parallel; this capability has been present in KlustronDB since the initial public release of version 0.6, and it is also technology developed by us independently.

Parallelism within Klustore instances: When a Kluscomp instance sends multiple read-only select statements to the same storage shard, the Kluscomp instance will open multiple connections to this target Klustore instance, executing one select statement in each connection. These select statements are executed in parallel within this Klustore instance and use the same snapshot, ensuring the consistency of the query result data.

In this way, the Kluscomp instances can utilize more computing resources of the Klustore instances while serving the same query request. This feature was released in KlustronDB version 1.0 and is also our self-developed technology.

Data analysis functionality

Regardless of how many storage shards a KlustronDB cluster has, users can deploy several Kluscomp instances specifically for handling data analysis queries. These Kluscomp instances use read-write separation technology to obtain data from the standby nodes of these storage shards to execute analysis statements, and therefore do not affect the performance of OLTP workloads.

The Kluscomp instances of KlustronDB support comprehensive data analysis capabilities and have passed all TPC-H and TPC-DS performance tests with performance many times higher than MySQL. At the same time, the data analysis functions supported by KlustronDB_server are very complete. Specifically, KlustronDB_server supports all window functions, grouping sets, cube, rollup, and other functions, whereas MySQL only supports rollup and some window function features.

KlustronDB's full cluster multi-level parallel query processing capability can, in the optimal case, use all CPU/memory resources of the entire cluster to execute the same SELECT query, bringing a performance leap to data analysis queries.

It should be noted that in order to use Kluscomp instances in a single-shard mode, every DDL statement must be executed through the Kluscomp instances of this single-shard cluster. Additionally, doing so has another advantage: in the future, the single-shard cluster can be upgraded to a normal mode cluster (i.e., a cluster that allows any number of shards) when needed.

Make full use of hardware resources

Using the simplest MySQL cluster with one master and two replicas requires three machines, but among these three machines, only the master node can handle write loads, and its CPU and IO load are usually higher than those of the replicas.

After using KlustronDB, a KlustronDB cluster can be deployed on 3 machines using a peer-to-peer deployment mode. This cluster has 3 storage shards, with each machine hosting the primary node of one shard and one replica node of the other two shards (as shown in the figure). This way, the computing and write loads, as well as the total number of connections initiated by applications, are evenly distributed across the 3 machines, making full use of each machine's computing resources and achieving higher overall throughput and performance for the cluster.

Comprehensive and flexible cluster management capabilities

Programmable basic cluster management API

KlustronDB supports comprehensive cluster management features, including automatic cluster backups and globally consistent recovery, horizontal elastic scaling, adding, deleting, starting, and stopping Kluscomp instances, adding and deleting storage shards and Klustore instances, redo of standby machines, automatic or manual primary-standby switching, and business-transparent Online DDL, among others.

And all of these features have corresponding API interfaces, so external software systems can programmatically operate and use KlustronDB's cluster management functions. For example, it can be very easily integrated into the user's database cluster management interface.

Reserve horizontal scaling capacity

As the user's business scale grows, a KlustronDB cluster, even if it currently uses a single storage shard, can later automatically and seamlessly scale out horizontally to multiple storage shards, as long as the user allocates more server nodes to the cluster, without any additional intervention from a DBA. This way, users do not need to worry about how to cope with continuously increasing data volumes in the future.

During automatic horizontal elastic scaling, the KlustronDB cluster does not lock tables and does not affect the operation of applications.

As mentioned earlier, the prerequisite for horizontal scaling in a single-shard mode cluster is that every DDL statement must be executed through the Kluscomp instances of this single-shard cluster.

Graphical operation monitoring, fault diagnosis, and alarms

The running logs, slow query logs, and other log files of each module of KlustronDB contain rich runtime information of the cluster's modules. These logs are collected by KlustronDB for graphical semi-automated fault diagnosis, helping DBAs quickly and accurately locate problems.

At the same time, with the help of Grafana, a popular monitoring system integrated into XPanel, cluster node monitoring has been implemented. Alerts can also be sent in various forms such as SMS and phone calls to notify DBAs to handle issues promptly.

Database migration workload

When migrating from other database systems to KlustronDB, the data import part can be completed using third-party tools.

The aspect that is usually more difficult and labor-intensive is the transformation of application systems. In this regard, we have done a lot of work to help users easily migrate from MySQL and Oracle Server to KlustronDB.

MySQL Compatibility

KlustronDB supports both PostgreSQL and MySQL connection protocols, and any SQL statement supported by KlustronDB can be sent in either protocol.

In this way, you can take advantage of more extensive data storage management and utilization capabilities than MySQL, such as KlustronDB's OLAP analysis capabilities, which perform far better than MySQL.

At the same time, KlustronDB supports MySQL's private DML syntax. For details, see this article (KlustronDB's Support for MySQL Private DML Syntax).

For MySQL-specific SQL functions (that is, those not defined in the SQL standard), as of Klustron-2.0, we have added almost all MySQL-specific functions except for JSON functions. If you want to call MySQL GIS functions, you need to enable the PostGIS extension in KlustronDB. For JSON data management, KlustronDB supports complete and efficient JSON data management, but you need to use PostgreSQL's functions and operators.

In this way, applications originally using MySQL do not need any code modifications or recompilation, and can connect directly to KlustronDB.

Oracle Compatibility

KlustronDB inherits PostgreSQL's compatibility with Oracle databases, including support for PL/SQL and most features of SQL-2003.

For other Oracle-specific features, users need to complete application-side code modifications (usually requiring changes to a small number of stored procedures and SQL queries), and they can also use some third-party tools to simplify and speed up these tasks.

If the application originally uses ODBC or JDBC to connect to an Oracle database, then it can connect to KlustronDB without any code modifications; otherwise, the application code needs to be modified to use the client library for PostgreSQL in the respective programming language to connect to KlustronDB.