Functions and Stored Procedures

WeSQL provides several functions and stored procedures designed for system management tasks, including:

• Cleaning up unused files and snapshots stored in object storage.
• Managing nodes within the Raft protocol, including tasks such as adding, removing, or maintaining nodes to ensure system consistency and fault tolerance.

Functions

binlog_persistent_purge

This function is used to purge all persistent binlog objects in object storage that were generated before, but not including, the specified binlog. It works similarly to MySQL's PURGE BINLOG LOGS TO 'binlog.000014'. Binlogs must be purged sequentially, in complete objects. If a binlog is currently being contained/referenced by a consistent snapshot, it cannot be purged. To proceed with the binlog purge, any associated consistent snapshots must be removed first.

Example usage:

select binlog_persistent_purge('binlog.000014');

consistent_persistent_snapshot_purge

This function is used to purge all persistent consistent snapshots stored in object storage that were created before the specified timestamp (but will always keep at least one snapshot and won't delete all snapshots). Similarly, if the parameter is 'ALL', it will purge all consistent snapshots, leaving only the most recent one.

It's important to note that consistent_persistent_snapshot_purge will delete the snapshots of storage engines (such as SE or InnoDB) contained within the consistent snapshot, but it will not delete any binlog objects. Since a snapshot only contains the target binlog position necessary to replay the binlog in order to achieve a consistent database state. Binlog cleanup is handled separately by the binlog_persistent_purge function, which is specifically designed for that purpose.

select consistent_persistent_snapshot_purge('2024-05-23 02:32:55');
select consistent_persistent_snapshot_purge('ALL');

Stored Procedures

Raft command

Adding a Node

This function allows adding a new node to a WeSQL cluster. A Follower node can either be a Data node or a Logger node.

For instance, if the current Leader node fails, you can use the add_follower command to introduce a new Data node into the Raft Group. This node will synchronize data from the existing Logger nodes, and it can eventually be promoted to a Leader to take over the service.

Example command:

CALL dbms_consensus.add_follower('192.168.0.3:13008');

Removing a Node

To remove a node from the WeSQL cluster, the following steps should be taken:

1. Demote the node to a learner role before removal:

   CALL dbms_consensus.downgrade_follower('192.168.0.3:13008');
   CALL dbms_consensus.drop_learner('192.168.0.3:13008');
2. Stop the instance and delete the associated data if necessary.

Switchover (Changing the Leader Node)

The switchover operation is used to transfer the leadership to another specified node. This operation must be executed on the current Leader node. You will need to specify the IP address and consensus communication port of the target new Leader node.

Ensure the cluster is in a stable state before performing the switchover, as this involves shifting leadership responsibilities within the cluster.

Example command:

CALL dbms_consensus.change_leader('192.168.0.3:13007');

Log Cleanup

Cleaning Logs from All Nodes

This operation can only be performed on the Leader node. The parameter $start_log_index represents the log position from which logs will be cleaned. The cleanup position cannot exceed the minMatchIndex across all nodes in the cluster:

CALL dbms_consensus.purge_log($start_log_index);

Cleaning Local Node Logs

For individual nodes, $start_log_index represents the log position from which logs will be cleaned. On a Leader node, the cleanup position cannot exceed the minMatchIndex across all nodes. On a non-leader node, the cleanup position cannot exceed the minMatchIndex of any node that depends on it as a learner source:

CALL dbms_consensus.local_purge_log($start_log_index);