How to decrease the number of partitions for an existing database in SingleStore?
Once a database has been created, you cannot decrease the number of partitions in SingleStore. As of SingleStore Version 7.1, you can double the existing database partitions with the BACKUP WITH SPLIT PARTITION command.
As an example, for a specified database, any change to the leaf nodes (E.g., addition or removal) will result in the number of partitions remaining the same. To setup partitions, you can modify the variable default_partitions_per_leaf and the Number of Leaf nodes during the time of database creation.
The formula to calculate the total number of partitions requires inputs from the number of leaf nodes and the value of default_partitions_per_leaf as detailed below:
Total partitions = default_partitions_per_leaf * Number of leaf nodes
Another use case calls for inputting the Partition count with the CREATE DATABASE statement, so that you may choose the number of partitions during the new database creation. As a best practice, SingleStore DB recommends an equally consistent number of partitions per Leaf. For further documentation, Click here.
Alternatively, you can decrease or increase partition count with the following method across SingleStore Database for versions 7.0 and prior:
How to Decrease Partitions in SingleStore DB 7.0 and prior:
1.Take a mysqldump of an existing database(s).
2.Take a backup of existing user grants, sdb-admin dump.
3.Create a NEW DATABASE with the required number of partitions.
4.Import mysqldump to the new database.
5.Import user grants.
Note: The above process requires downtime.
If you are searching for Cluster Resizing, Please Click Here.