{question}
How to increase database partitions in a bottomless DB?
{question}
{answer}
Unlimited storage databases (also known as remote storage or "bottomless" databases) are kept on remote object storage. Remote storage separates data (in an object store external to the SingleStore cluster) from processing the data (in a SingleStore cluster). To learn more about the concepts of Bottomless Database, click here.
In this article, we are going to discuss increasing database partitions in a Bottomless Database.
BACKUP WITH SPLIT PARTITIONS is supported with Bottomless from SingleStore 7.8. So, from SingleStore 7.8, we can use the BACKUP DATABASE WITH SPLIT PARTITIONS command to double the number of partitions in the bottomless database backup so that when the bottomless database backup is restored, we will have twice the number of partitions as we had before the split backup. Click here to learn about upgrading SingleStore DB. If you are a SingleStore Cloud DB customer, click here to reach out to SingleStore support to upgrade your cluster.
Prior to SingleStore 7.8 version (7.5 and 7.6), Trying to restore Non-Bottomless database into Bottomless Database which has already been split results in below error,
ERROR 1706 (HY000): Feature 'Restore into bottomless with partition split' is not supported by SingleStore.
The only way to Increase Partitions in bottomless database on SingleStore 7.5/7.6 is to perform INSERT...SELECT or CREATE...SELECT from NON-BOTTOMLESS DB into the BOTTOMLESS DB.
Let's take a cluster with a Non-Bottomless database called memsql_demo and has 4 partitions,
mysql> use memsql_demo;
Database changed
mysql> show database status;
+--------------------------+-------------+
| Key | Value |
+--------------------------+-------------+
| database | memsql_demo |
| role | master |
| state | online |
| commit_count | 0 |
| provision_file | 50 |
| oldest_living_snapshot | 50 |
| newest_snapshot | 55 |
| log_type | paged |
| commit_lsn | 0:57 |
| hardened_lsn | 0:57 |
| replay_lsn | 0:52 |
| tail_lsn | 0:57 |
| replicated_committed_lsn | 0:56 |
| term | 100000003 |
| workload_throttled | no |
| partitions | 4 |
+--------------------------+-------------+
16 rows in set (0.00 sec)
The requirement is to move it to bottomless DB and increase the number of partitions,
STEP 1: Create a bottomless database with the number of desired partitions,
In this example, I am creating a bottomless database with 8 partitions,
CREATE DATABASE memsql_demo_bottomless PARTITIONS 8 ON S3 "bottomless/bottomless_db_folder" CONFIG '{"region":"us-east-1"}' CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';
STEP 2: Create and populate the target tables into a bottomless database
mysql> create table memsql_demo_bottomless.customer as select * from memsql_demo.customer;
Query OK, 150000 rows affected (1.22 sec)
mysql> use memsql_demo_bottomless;select count(*) from customer;
Database changed
+----------+
| count(*) |
+----------+
| 150000 |
+----------+
1 row in set (0.07 sec)
Same steps needed to be followed to transfer all the tables to the bottomless DB to increase the number of partitions.
Note: Bottomless needs all uncommitted data (any new rows written by a big write query) to fix on the local disk. If you do a single big INSERT..SELECT you can run out of local disk. It is recommended that you confirm that you have enough local disk space or use a series of small INSERT..SELECT's on a range of the primary key to copy the table.
Click here to learn more about migrating the local databases to bottomless databases.
{answer}