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 snapshot, it cannot be purged. To proceed with the binlog purge, any associated snapshots must be removed first.

Example usage:

select binlog_persistent_purge('binlog.000014');

snapshot_archive_persistent_purge

This function is used to purge all persistent 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 snapshots, leaving only the most recent one.

It's important to note that snapshot_archive_persistent_purge will delete the snapshots of storage engines (such as SE or InnoDB) contained within the 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 snapshot_archive_persistent_purge('2024-05-23 02:32:55');
select snapshot_archive_persistent_purge('ALL');

snapshot_archive_persistent_force

This function is used to force the generation of a snapshot and persist it to object storage when the snapshot task is within the snapshot_archive_period cycle.

select snapshot_archive_persistent_force();

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 raft protocol 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);