Comprehensive Guide to Integrating PolarDB-X

An approach for semantic integration of heterogeneous data sources [PeerJ]

Introduction

PolarDB-X is a cutting-edge, cloud-native distributed SQL database system developed by Alibaba Cloud. Designed to handle high concurrency, massive data storage, and complex query scenarios, PolarDB-X offers features such as horizontal scalability, hybrid transaction/analytical processing (HTAP), and financial-grade reliability. Its compatibility with MySQL syntax and protocols makes it an ideal choice for applications transitioning from traditional relational databases to more scalable, distributed architectures.

Understanding PolarDB-X

Architecture

PolarDB-X is built on a distributed architecture that separates compute and storage, allowing independent scaling of resources. This architecture ensures high availability, fault tolerance, and efficient resource utilization. The core components of PolarDB-X include:

Compute Nodes (CN): Handle SQL parsing, optimization, and execution. They manage query distribution and coordination via two-phase commit (2PC) protocols.
Data Nodes (DN): Responsible for data storage and persistence. They utilize Multi-Paxos protocols to ensure strong consistency and employ Multi-Version Concurrency Control (MVCC) for transaction visibility.
Global Meta Service (GMS): Maintains global metadata, including schema information, configuration settings, and global transaction states. It also provides a global time service (TSO).
Change Data Capture (CDC): Captures and records data changes in real-time, facilitating synchronization and replication with other systems.

Key Features

Horizontal Scaling: Supports seamless horizontal scaling through various data partitioning algorithms, enabling the handling of large datasets and high traffic loads.
Distributed Transactions: Ensures ACID compliance using MVCC combined with TSO and 2PC protocols, providing high transaction performance.
Hybrid Transaction/Analytical Processing (HTAP): Capable of handling both OLTP and OLAP workloads with strong isolation, facilitating real-time analytics alongside transactional operations.
MySQL Compatibility: Fully compatible with MySQL protocols, syntax, collation, transaction isolation levels, and Binlog, simplifying migration and integration.
High Availability: Achieves data consistency and reliability through Multi-Majority Paxos protocols and supports multiple disaster recovery modes.
Enterprise-Level Features: Includes SQL limiting, SQL Advisor, Transparent Data Encryption (TDE), three-authority separation, and Flashback Query for enhanced security and management.
Cloud-Native Deployment: Offers flexible deployment options, including Kubernetes Operator and various cloud environments, ensuring adaptability to different infrastructure setups.

Deployment Options

1. Quick Deployment Using PXD

PolarDB-X provides the pxd tool for rapid deployment, allowing users to set up a PolarDB-X cluster with minimal configuration.

pxd tryout -cn_replica 1 -cn_version latest -dn_replica 1 -dn_version latest -cdc_replica 1 -cdc_version latest

After executing the above command, the connection information for the PolarDB-X database will be displayed, enabling immediate access.

2. Kubernetes (K8S) Deployment

For scalable and customizable deployments, Kubernetes provides an efficient platform to manage PolarDB-X clusters using the Kubernetes Operator.

Install Kubernetes: Ensure Kubernetes is installed on your infrastructure. Refer to the official Kubernetes documentation for installation guidelines.

Deploy PolarDB-X Operator:

helm repo add polardbx https://polardbx-opensource.oss-cn-hangzhou.aliyuncs.com/charts
helm install polardbx-operator polardbx/polardbx-operator

Create PolarDB-X Cluster:

apiVersion: polardbx.aliyun.com/v1
kind: PolarDBXCluster
metadata:
  name: polarx-cluster
spec:
  topology:
    nodes:
      cn:
        replicas: 3
        resources:
          limits:
            cpu: "16"
            memory: "64Gi"
      dn:
        replicas: 3
        resources:
          limits:
            cpu: "16"
            memory: "50Gi"

Deploy the Cluster:
```
kubectl apply -f polarx_k8s.yaml
```
Verify Cluster Status:
```
kubectl get polardbxcluster
```

3. Source Code Compilation and Installation

For users requiring deep customization, compiling PolarDB-X from source is an option. This approach provides the highest level of control over the deployment.

Download Source Code: Clone the necessary repositories.

git clone https://github.com/alibaba/polardbx-engine.git
git clone https://github.com/alibaba/polardbx-sql.git
git clone https://github.com/alibaba/polardbx-glue.git
git clone https://github.com/alibaba/polardbx-cdc.git

Compile and Install Components:

cd polardbx-sql
mvn install -D maven.test.skip=true -D env=release

cd polardbx-engine
cmake -DCMAKE_BUILD_TYPE="Debug" -DSYSCONFDIR="/u01/mysql" -DCMAKE_INSTALL_PREFIX="/u01/mysql" -DMYSQL_DATADIR="/u01/mysql/data"
make -j8
make install

cd polardbx-cdc
mvn install -D maven.test.skip=true -D env=release
tar zxvf polardbx-binlog.tar.gz

Start PolarDB-X Components:

# Start Data Nodes
cd polardbx-engine
./bin/mysqld --defaults-file=./my.cnf &

# Start Compute Nodes
cd polardbx-sql
./bin/polardbx-sql --defaults-file=./conf/server.properties &

# Start CDC Nodes
cd polardbx-cdc
./bin/polardbx-cdc --defaults-file=./conf/config.properties &

Verify Cluster Status:
```
MySQL [(none)]> SHOW STORAGE;
```
The output will display information about storage instances, their health status, and other relevant metrics.

Integrating PolarDB-X with Applications

Connection Pool Configuration

Efficient management of database connections is crucial for application performance. Utilizing a connection pool, such as Druid, helps in reusing existing connections and optimizing resource usage.

DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://:/");
dataSource.setUsername("polardbx_root");
dataSource.setPassword("123456");
dataSource.setInitialSize(10);
dataSource.setMaxActive(100);
dataSource.setMinIdle(5);

Benefits of Connection Pools:

Improved Response Efficiency: Reuses existing connections, reducing the overhead of establishing new connections.
Resource Reuse: Prevents frequent connection initialization and release.
Connection Leak Prevention: Automatically deallocates unused connections.

For more details, visit the PolarDB-X Connection Pool Best Practices.

Calculating Required Connections

Optimizing the number of database connections based on Queries Per Second (QPS) and Response Time (RT) ensures that the application can handle the expected load without overloading the database.

Formulas:

Maximum QPS for a Single Connection: Max QPS = 1,000 / RT (ms)
Required Connections: Required Connections = Total QPS / Max QPS per Connection

Example: If the average RT is 5ms and the total QPS is 400,000:

Maximum QPS per Connection = 1,000 / 5 = 200 Required Connections = 400,000 / 200 = 2,000

For more examples and detailed explanations, refer to the PolarDB-X Connection Pool Documentation.

Special Integrations

Integrating PolarDB-X with IoT Data Using EMQX

Integrating PolarDB-X with Internet of Things (IoT) data can be achieved using EMQX, an open-source MQTT broker, to collect, transmit, and store IoT data.

Steps to Integrate EMQX and PolarDB-X

Deploy PolarDB-X: Choose your deployment method (PXD, Kubernetes, or source code compilation) and ensure the database is up and running.
Set Up EMQX: Deploy EMQX and configure it to connect to your IoT devices. Access the EMQX dashboard at http://localhost:18083 to manage settings.
Create Data Integration Rules: In the EMQX dashboard, set up rules to process and distribute data to an MQTT topic, such as t/#.

Use Third-Party Scripts: Since the open-source version of EMQX doesn't support direct writing to PolarDB-X, implement a script (e.g., using Node.js) to subscribe to the MQTT topic and insert data into PolarDB-X.

const mqtt = require('mqtt');
const mysql = require('mysql2');

const config = {
  host: 'localhost',
  port: 10743,
  username: 'polardbx_root',
  password: '****',
  database: 'emqx_iot'
};

const connection = mysql.createConnection({
  host: config.host,
  user: config.username,
  password: config.password,
  database: config.database,
  port: config.port,
});

function createClient() {
  return new Promise((resolve, reject) => {
    const client = mqtt.connect('mqtt://localhost:1883');
    client.on('connect', () => {
      resolve(client);
    });
  });
}

async function main() {
  for (let i = 0; i < 10; i++) {
    const client = await createClient();
    client.subscribe('$share/1/emqx_polardb');
    client.on('message', handleMessage);
    console.log(`client ${i} ready...`);
  }
}

function handleMessage(topic, message) {
  const data = JSON.parse(message.toString());
  connection.execute('INSERT INTO messages (clientid, topic, payload) VALUES (?, ?, ?)', [data.clientid, data.topic, data.payload]);
  console.log('Message saved ok');
}

main();

Database Table Structure: Create necessary tables in PolarDB-X to store IoT data.

CREATE TABLE messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  clientid VARCHAR(255),
  topic VARCHAR(255),
  payload BLOB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY HASH(id) PARTITIONS 2;

This table structure includes fields for the client ID, topic, payload, and a timestamp for when the message was created.

Integrating PolarDB-X with Apache Flink (CDC)

Apache Flink can be integrated with PolarDB-X using Change Data Capture (CDC) to enable real-time data processing and synchronization with systems like Elasticsearch.

Steps to Integrate PolarDB-X with Apache Flink

Deploy PolarDB-X and Other Components: Use Docker Compose to deploy PolarDB-X, Elasticsearch, and Kibana.

version: '2.1'
services:
  polardbx:
    image: polardbx/polardb-x:2.0.1
    container_name: polardbx
    ports:
      - "8527:8527"
  elasticsearch:
    image: 'elastic/elasticsearch:7.6.0'
    container_name: elasticsearch
    environment:
      - cluster.name=docker-cluster
      - bootstrap.memory_lock=true
      - ES_JAVA_OPTS=-Xms512m -Xmx512m
      - discovery.type=single-node
    ports:
      - '9200:9200'
      - '9300:9300'
    ulimits:
      memlock:
        soft: -1
        hard: -1
      nofile:
        soft: 65536
        hard: 65536
  kibana:
    image: 'elastic/kibana:7.6.0'
    container_name: kibana
    ports:
      - '5601:5601'
    volumes:
      - '/var/run/docker.sock:/var/run/docker.sock'

Start the containers using:

docker-compose up -d

Prepare Data in PolarDB-X: Connect to PolarDB-X and create necessary tables.

CREATE TABLE orders (
  order_id INT,
  order_date TIMESTAMP,
  customer_name STRING,
  price DECIMAL(10, 5),
  product_id INT,
  order_status BOOLEAN,
  PRIMARY KEY (order_id) NOT ENFORCED
);

CREATE TABLE products (
  id INT,
  name STRING,
  description STRING,
  PRIMARY KEY (id) NOT ENFORCED
);

Insert sample data into these tables to facilitate testing.

Configure Flink CDC: Set up Flink SQL to mirror PolarDB-X tables and establish CDC connectors.

Flink SQL> CREATE TABLE orders (
  order_id INT,
  order_date TIMESTAMP,
  customer_name STRING,
  price DECIMAL(10, 5),
  product_id INT,
  order_status BOOLEAN,
  PRIMARY KEY (order_id) NOT ENFORCED
) WITH (
  'connector' = 'mysql-cdc',
  'hostname' = '127.0.0.1',
  'port' = '8527',
  'username' = 'polardbx_root',
  'password' = '123456',
  'database-name' = 'mydb',
  'table-name' = 'orders'
);

Flink SQL> CREATE TABLE products (
  id INT,
  name STRING,
  description STRING,
  PRIMARY KEY (id) NOT ENFORCED
) WITH (
  'connector' = 'mysql-cdc',
  'hostname' = '127.0.0.1',
  'port' = '8527',
  'username' = 'polardbx_root',
  'password' = '123456',
  'database-name' = 'mydb',
  'table-name' = 'products'
);

Create Sink Table in Elasticsearch:

Flink SQL> CREATE TABLE enriched_orders (
  order_id INT,
  order_date TIMESTAMP,
  customer_name STRING,
  price DECIMAL(10, 5),
  product_id INT,
  order_status BOOLEAN,
  product_name STRING,
  product_description STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
) WITH (
  'connector' = 'elasticsearch-7',
  'hosts' = 'http://localhost:9200',
  'index' = 'enriched_orders'
);

Execute the Join Query: Combine data from the orders and products tables and write the results to Elasticsearch.

Flink SQL> INSERT INTO enriched_orders
  SELECT o.order_id,
         o.order_date,
         o.customer_name,
         o.price,
         o.product_id,
         o.order_status,
         p.name,
         p.description
  FROM orders AS o
  LEFT JOIN products AS p ON o.product_id = p.id;

Monitor Data in Kibana: Access Kibana at http://localhost:5601, create an index pattern for enriched_orders, and use the Discover section to view and analyze the data.

Best Practices for Integration

High Availability and Disaster Recovery

Ensuring high availability and robust disaster recovery mechanisms is essential for maintaining data integrity and minimizing downtime. PolarDB-X employs the Paxos protocol to achieve financial-grade reliability. Implement the following practices:

Multi-Majority Paxos: Use this protocol to ensure data consistency across distributed nodes.
Disaster Recovery Modes: Configure multiple disaster recovery modes to handle various failure scenarios gracefully.
Failover Handling: Design your application to detect and handle failover events without disrupting service.

Performance Optimization

Optimizing performance involves several strategies to ensure efficient utilization of resources and rapid query processing:

Enable HTAP: Utilize PolarDB-X's hybrid transaction/analytical processing capabilities to perform real-time analytics alongside transactional operations.
Use Global Secondary Indexes: Implement these indexes to enhance query performance, especially in distributed environments.
Monitor QPS and RT: Continuously monitor Queries Per Second (QPS) and Response Time (RT) to identify and address performance bottlenecks.
Optimize Connection Pools: Adjust connection pool settings based on application load and performance metrics to maintain optimal performance.

Conclusion

Integrating PolarDB-X into your system involves a series of well-defined steps, from understanding its architecture and deploying it using the appropriate method to configuring connection pools and implementing best practices for high availability and performance optimization. Whether it's handling IoT data with EMQX or enabling real-time data processing with Apache Flink CDC, PolarDB-X provides the flexibility and robustness needed for modern, data-intensive applications.

References

emqx.com

EMQX + PolarDB-X Integration Guide

nightlies.apache.org

Apache Flink CDC Tutorial with PolarDB-X

help.aliyun.com

PolarDB-X First Use Guide

doc.polardbx.com

PolarDB-X Product Documentation

alibabacloud.com

PolarDB-X on Alibaba Cloud

alibabacloud.com

PolarDB-X Deployment Guide

Last updated January 6, 2025