Ephemeral node before gracefully shut it down

If you want to do maintenance on nodes one by one, you have two options:
Option 1:
Bring one node down for maintenance(short maintenance window) :
For example, if you have 8 node cluster and if one node is brought down for maintenance then two adjoining nodes will be critical nodes and if any of them go down DB will go down. Your DB will stay UP if any other node that is not critical goes down. Disadvantage of this option is availability is at risk but you don't have to rebalance your cluster and rebalance may not be a quick operation depending on your data size.

Option 2:
remove node , do maintenance and then add node back( long maintenance window):
alter node and mark it ephemeral and then run SELECT START_REBALANCE_CLUSTER(); -- a back ground operation or SELECT REBALANCE_CLUSTER(); --a foreground operation. Once cluster is rebalanced, you may go to admintools and remove node from cluster. Once maintenance is complete , you will add this node back and run rebalance to add data on to node. Since data has to be rebalanced this option may be time and resource intensive operation and you should plan for it .

Marking a node ephemeral before bringing it down, will only not let any queries to use that node & data on that node will not be updated. So yes, marking a node
as ephemeral is an option,so that it will not be used by any queries.

Regards'
Abhishek

Hi Abhishek,

Thanks for your answer.

I am indeed aware of the different methods with their pros and cons, but my question was really related to the moment the node is taken down. If any queries are running on it they will be killed.

This is why I wanted to mark a node as ephemeral before taking it down, to make sure it is not used at this time.

Would you know if there is a way to know if a node is used by a query?

Say node3 is used by a long running query. I mark it as ephemeral, the query will carry on but no new queries will run on this node. How can I know when node3 is not used at all? In other word, how can I know which nodes are used for a query?

Thanks,

You should be able to query user_sessions to see if any query is active in the node. Something like select * from user_sessions where is_active=true and node_name="node name";

I did try this, using the query_requests table:

ALTER NODE db_dwh_node0005 EPHEMERAL;

then a few

select NODE_NAME, count(*)  from QUERY_REQUESTS WHERE IS_EXECUTING AND NODE_NAME='db_dwh_node0005 ';

I would expect to see the number of queries running on this node to go down, to eventually reach 0.

Unfortunately, node5 is still used. 

Is there something wrong in my understanding?

Thanks,

I meant to say use the query_requests instead of user_sessions.

Thanks Abhishek. Sadly it does not seem to do what I expected, the node is still in use (see my comment below). Did I understand wrongly?

Although I agree (with query_requests) the effect is not the one I expected. The node is still used. I am probably understanding something wrongly.