How to delete empty partitions?

CrateDB
1

We have one table in which we are doing partition based on day.
we will take snapshot of tables based on that partition and after taking backup we delete the data of that day.

Due to multiple partition, shards count is more than 2000 and configured shard is 6

I have observed that old partitions have no data but still exist in database.
So it will take more time to become healthy and available to write data after restarting the crate.

shard
shard1124×738 64.3 KB

So Is there any way to delete those partition?

Is there any way to stop replication of data on startup the cluster? cause it takes too much time to become healthy cluster and due to that table is not writable until that process finished.

Any solution for this issue will be great help?

1 Like
2

It would be very helpful to know the CrateDB version you are running and observing these issues.

Is there any way to stop replication of data on startup the cluster? cause it takes too much time to become healthy cluster and due to that table is not writable until that process finished.

On CrateDB versions 2.0.0 up to 4.0.x, per default writing was only possible if all shards (primaries + replicas) were available. See https://crate.io/docs/crate/reference/en/3.3/sql/statements/create-table.html#sql-ref-write-wait-for-active-shards.

We have changed the default back to 1 in CrateDB >= 4.2. See

You can always change the default by:
ALTER TABLE my_table SET ("write.wait_for_active_shards" = 1);

Additionally you can disable allocation of replica shards before shutting down the cluster:

SET GLOBAL cluster.routing.allocation.enable = 'primaries';

Of course this setting must be adjusted (reset) after the restart at some point, e.g.

RESET GLOBAL cluster.routing.allocation.enable;

See Runtime configuration — CrateDB: Reference for further readings on runtime settings.

3

Thanks for your reply.
We are using 4.0.7 cratedb version

In my scenario, we are using days (current day midnight epoch) as a partition for table and we have configured 8 shards and table retention is 15-45 days for records.

So due to partition and retention, It will create around 1400-2500 shards for single table

Can I choose days column for partition?

Does this multiple shards create bad impact on read/write performance?

I have read https://crate.io/docs/crate/howtos/en/latest/performance/sharding.html
We have 2 core cpu, 8 gb ram around 3-4 nodes.
As per guide, shard count should be equal to cpu count.

Is it over allocation for table?
What should I need to do to avoid this over allocation?
What is the difference between Configured shards vs Started shards?



Regarding the more partition, How can I delete partitions from the table?

We have just find the workaround using close partition by below query.

Is there any performance impact if we close partition using below query?
ALTER TABLE my_table PARTITION (days=epoch) CLOSE;