Many organizations size their database infrastructure to handle the maximum level of load they can anticipate, but very often load is seasonal, in some cases around specific events on certain days of the year. Compromises are often made where infrastructure sits idle most of the year and performance is not as good as desired when requests peak.
CrateDB allows clusters to be scaled both up and down by adding and removing nodes, this allows significant savings during quiet periods, and also the provisioning of extra capacity during particular periods of high activity to have optimal performance.
When nodes are added or removed CrateDB automatically rebalances shards, but in cases where we have very large volumes of historical data and new nodes are only added for a short period of time, we may want to avoid any of the historical data being relocated to the temporary nodes. The approach described below explains how to achieve this using shard allocation filtering on a table that is partitioned by day, the idea is that the period of high activity can be foreseen, so the scaling up will take place the day before a big event, and the scaling down someday after the event has ended.
This same approach can be applied to multiple tables. It is particularly relevant for the larger tables, and smaller tables can be kept on the baseline nodes, but it is always good to consider the impact on querying performance if the small tables will be queried during the big event
JOINed to the big tables that will have data on the temporary nodes.
In this example, we will imagine that the surge in demand we are preparing for is related to the 2022 FIFA Men’s World Cup running from 20/11/2022 to 18/12/2022.
We will start with a 3 nodes cluster, on which we will create a test table and populate it with some pre-World Cup data:
CREATE TABLE test ( ts TIMESTAMP, recorddetails TEXT, "day" GENERATED ALWAYS AS date_trunc('day',ts) ) PARTITIONED BY ("day") CLUSTERED INTO 4 SHARDS WITH (number_of_replicas=1); INSERT INTO test (ts) VALUES ('2022-11-18'),('2022-11-19');
The shards will initially look like this:
We want to make sure that the addition of the temporary nodes does not result on data from the large tables getting rebalanced to use these nodes.
We will be able to identify the new nodes by using a custom attribute (
node.attr.storage=temporarynodes) (see further down for details on how to configure this), so the first step is to configure the existing partitions so that they do not consider the new nodes as suitable targets for shard allocation.
In CrateDB 5.1.2 or higher we can achieve this with:
/* this applies the setting to all existing partitions and new partitions */ ALTER TABLE test SET ("routing.allocation.exclude.storage" = 'temporarynodes'); /* then we run this other command so that the setting does not apply to new partitions */ ALTER TABLE ONLY test RESET ("routing.allocation.exclude.storage");
No data gets reallocated when running this, and there is no impact on querying or ingestion.
Starting in CrateDB 5.2 this setting is visible in
We want to deploy the new nodes setting a custom attribute.
If using containers add a line to
args in your YAML file with:
Otherwise add this to
crate.yml (typically on
Please note the word
storage in this context does not have any special meaning for CrateDB, it is just a name that we have chosen in this case for the custom attribute.
Starting with CrateDB 5.2 these node attributes will be visible in
We need to calculate how many shards will be created each day (that is on each partition) during the special event, since our test table is
CLUSTERED INTO 4 SHARDS
WITH (number_of_replicas=1) we would have 4 (shards per partition) x 2 (primary + copy) = 8
We then need to see what is the ceiling of the number we got (8) divided by the total number of nodes (baseline + temporary), if that is for instance 3+2=5 then we have ceiling(8/5)=2.
That means that if a maximum of 2 of the new shards created each day during the event goes to each node then the new data will be balanced across all nodes.
With the nodes ready, on the day before the event, we need to configure the special tables so that any new partitions follow this rule:
ALTER TABLE ONLY test SET ("routing.allocation.total_shards_per_node" = 2);
This setting (
total_shards_per_node) is visible at partition level in
No data gets reallocated when running this and there is no impact on querying or ingestion.
This setting can be checked by using:
SHOW CREATE TABLE test;
Let’s now simulate the arrival of data during the event:
INSERT INTO test (ts) VALUES ('2022-11-20'),('2022-11-21'),('2022-11-22'),('2022-11-23'), ('2022-11-24'),('2022-11-25'),('2022-11-26'),('2022-11-27'), ('2022-11-28'),('2022-11-29'),('2022-11-30'),('2022-12-01'), ('2022-12-02'),('2022-12-03'),('2022-12-04'),('2022-12-05'), ('2022-12-06'),('2022-12-07'),('2022-12-08'),('2022-12-09'), ('2022-12-10'),('2022-12-11'),('2022-12-12'),('2022-12-13'), ('2022-12-14'),('2022-12-15'),('2022-12-16'),('2022-12-17'), ('2022-12-18')
We can see that data from before the event stays on the baseline nodes while data for the days of the event gets distributed over all nodes:
The same can be checked programmatically with this query:
SELECT table_partitions.table_schema, table_partitions.table_name, table_partitions.values['day']::TIMESTAMP, shards.primary, shards.node['name'] FROM sys.shards JOIN information_schema.table_partitions ON shards.partition_ident=table_partitions.partition_ident ORDER BY 1,2,3,4,5;
On the last day of the event, we need to configure the table so that the next partition goes to the baseline nodes:
ALTER TABLE ONLY test SET ("routing.allocation.exclude.storage" = 'temporarynodes'); ALTER TABLE ONLY test RESET ("routing.allocation.total_shards_per_node");
New data should now again go the baseline nodes only.
Let’s confirm it:
INSERT INTO test (ts) VALUES ('2022-12-19'),('2022-12-20')
When we are ready to decommission the temporary nodes, we need to move the data collected during the days of the event.
In CrateDB 5.1.2 or higher we can achieve this with:
ALTER TABLE test SET ("routing.allocation.exclude.storage" = 'temporarynodes'); ALTER TABLE test RESET ("routing.allocation.total_shards_per_node");
The data movement takes place one replica at a time and there is no impact on querying of the event’s data while it is being moved, new data also continues to flow to the baseline nodes and its ingestion and querying are also not impacted.
We can monitor the progress of the data relocation querying
Once all shards have moved away from the temporary nodes, we can decommission them gracefully:
ALTER CLUSTER DECOMMISSION 'nodename';
Once this is done, the machines can safely be shutdown.
If desired, new nodes can be deployed reusing the same names that were used for the temporary nodes before.