Search

Top 60 Oracle Blogs

Recent comments

How to enlarge an #Exasol database by adding a node

Adding a cluster node will not only increase the available storage capacity but also the total compute power of your cluster. This scale-out is a quite common operation for Exasol customers to do.

My example shows how to change an existing 2+1 cluster into a 3+0 cluster. Before you can enlarge the database with an active node, this node has to be a reserve node first. See here how to add a reserve to a 2+0 cluster. Of course you can add another reserve node to change from 3+0 to 3+1 afterwards. See here if you wonder why you may want to have a reserve node at all.

Initial state – reserve node is present

I start with a 2+1 cluster – 2 active nodes and 1 reserve node:

https://uhesse.files.wordpress.com/2019/09/enlarge1.png?w=150&h=38 150w, https://uhesse.files.wordpress.com/2019/09/enlarge1.png?w=300&h=76 300w, https://uhesse.files.wordpress.com/2019/09/enlarge1.png?w=768&h=195 768w, https://uhesse.files.wordpress.com/2019/09/enlarge1.png 881w" sizes="(max-width: 620px) 100vw, 620px" />

For later comparison, let’s look at the distribution of rows of one of my tables:

https://uhesse.files.wordpress.com/2019/09/enlarge2.png?w=150&h=44 150w, https://uhesse.files.wordpress.com/2019/09/enlarge2.png?w=300&h=89 300w, https://uhesse.files.wordpress.com/2019/09/enlarge2.png 685w" sizes="(max-width: 620px) 100vw, 620px" />

The rows are roughly even distributed across the two active nodes.

Before you continue, it would be a good idea to take a backup on a remote archive volume now – just in case.

Shutdown database before volume modification

A data volume used used by a database cannot be modified while that database is up, so shut it down first:

https://uhesse.files.wordpress.com/2019/09/enlarge3.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/09/enlarge3.png?w=300&h=66 300w, https://uhesse.files.wordpress.com/2019/09/enlarge3.png?w=768&h=170 768w, https://uhesse.files.wordpress.com/2019/09/enlarge3.png 854w" sizes="(max-width: 620px) 100vw, 620px" />

After going to the Storage branch in EXAoperation, click on the data volume:

https://uhesse.files.wordpress.com/2019/09/enlarge4.png?w=150&h=27 150w, https://uhesse.files.wordpress.com/2019/09/enlarge4.png?w=300&h=54 300w, https://uhesse.files.wordpress.com/2019/09/enlarge4.png?w=768&h=137 768w, https://uhesse.files.wordpress.com/2019/09/enlarge4.png 834w" sizes="(max-width: 620px) 100vw, 620px" />

Then click on Edit:

https://uhesse.files.wordpress.com/2019/09/enlarge5.png?w=140 140w, https://uhesse.files.wordpress.com/2019/09/enlarge5.png?w=280 280w" sizes="(max-width: 472px) 100vw, 472px" />

Decrease volume redundancy to 1

Change the redundany from 2 to 1, then click Apply:

https://uhesse.files.wordpress.com/2019/09/enlarge6.png?w=115 115w, https://uhesse.files.wordpress.com/2019/09/enlarge6.png?w=230 230w" sizes="(max-width: 465px) 100vw, 465px" />

Why is the redundancy reduced from 2 to 1 here? Let’s try to explain that. Initially, I had 2 active nodes with a volume using redundancy 2:

https://uhesse.files.wordpress.com/2019/09/cluster.png?w=150&h=95 150w, https://uhesse.files.wordpress.com/2019/09/cluster.png?w=300&h=189 300w, https://uhesse.files.wordpress.com/2019/09/cluster.png?w=768&h=485 768w, https://uhesse.files.wordpress.com/2019/09/cluster.png 790w" sizes="(max-width: 620px) 100vw, 620px" />

A and B are master segments while A’ and B’ are mirrored segments. If I could add a node to this volume keeping the existing segments, it would look like this:

https://uhesse.files.wordpress.com/2019/09/badidea.png?w=133&h=150 133w, https://uhesse.files.wordpress.com/2019/09/badidea.png?w=267&h=300 267w, https://uhesse.files.wordpress.com/2019/09/badidea.png?w=768&h=863 768w, https://uhesse.files.wordpress.com/2019/09/badidea.png 854w" sizes="(max-width: 620px) 100vw, 620px" />

Of course this would be a bad idea. The redundancy is reduced to 1 before the new node is added to the volume:

https://uhesse.files.wordpress.com/2019/09/redundancy1.png?w=133&h=150 133w, https://uhesse.files.wordpress.com/2019/09/redundancy1.png?w=267&h=300 267w, https://uhesse.files.wordpress.com/2019/09/redundancy1.png?w=768&h=863 768w, https://uhesse.files.wordpress.com/2019/09/redundancy1.png 854w" sizes="(max-width: 620px) 100vw, 620px" />

Only distributed master segments with no mirrors at first. Then the redundancy is again increased to 2:

https://uhesse.files.wordpress.com/2019/09/redundancy2.png?w=133&h=150 133w, https://uhesse.files.wordpress.com/2019/09/redundancy2.png?w=267&h=300 267w, https://uhesse.files.wordpress.com/2019/09/redundancy2.png?w=768&h=863 768w, https://uhesse.files.wordpress.com/2019/09/redundancy2.png 854w" sizes="(max-width: 620px) 100vw, 620px" />

This way, every master segment can be mirrored on a neighbor node. That’s why the redundancy needs to be reduced to 1.

Add new node to volume

After having decreased the volume redundancy to 1, click Edit on the volume detail page again and add n13 as a new master node to the volume and click Apply:

https://uhesse.files.wordpress.com/2019/09/enlarge7.png?w=116 116w, https://uhesse.files.wordpress.com/2019/09/enlarge7.png?w=233 233w" sizes="(max-width: 468px) 100vw, 468px" />

Increase redundancy to 2

Now click Edit again and increase the redudancy to 2:

https://uhesse.files.wordpress.com/2019/09/enlarge8.png?w=116 116w, https://uhesse.files.wordpress.com/2019/09/enlarge8.png?w=232 232w" sizes="(max-width: 464px) 100vw, 464px" />

The state of the volume shows now as RECOVERING – don’t worry, it just means that mirrored segments are now created.

https://uhesse.files.wordpress.com/2019/09/enlarge9.png?w=141 141w, https://uhesse.files.wordpress.com/2019/09/enlarge9.png?w=282 282w" sizes="(max-width: 474px) 100vw, 474px" />

Enlarge the database

Now click on the database link on the EXASolution screen:

https://uhesse.files.wordpress.com/2019/09/enlarge10.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/09/enlarge10.png?w=300&h=66 300w, https://uhesse.files.wordpress.com/2019/09/enlarge10.png?w=768&h=169 768w, https://uhesse.files.wordpress.com/2019/09/enlarge10.png 848w" sizes="(max-width: 620px) 100vw, 620px" />

Select the Action Enlarge and click Submit:

https://uhesse.files.wordpress.com/2019/09/enlarge11.png?w=150&h=38 150w, https://uhesse.files.wordpress.com/2019/09/enlarge11.png?w=300&h=76 300w, https://uhesse.files.wordpress.com/2019/09/enlarge11.png?w=768&h=194 768w, https://uhesse.files.wordpress.com/2019/09/enlarge11.png 903w" sizes="(max-width: 620px) 100vw, 620px" />

Enter 1 and click Apply:

https://uhesse.files.wordpress.com/2019/09/enlarge12.png?w=150&h=51 150w, https://uhesse.files.wordpress.com/2019/09/enlarge12.png?w=300&h=102 300w, https://uhesse.files.wordpress.com/2019/09/enlarge12.png 642w" sizes="(max-width: 620px) 100vw, 620px" />

The database detail page looks like this now:

https://uhesse.files.wordpress.com/2019/09/enlarge13.png?w=150&h=37 150w, https://uhesse.files.wordpress.com/2019/09/enlarge13.png?w=300&h=74 300w, https://uhesse.files.wordpress.com/2019/09/enlarge13.png?w=768&h=189 768w, https://uhesse.files.wordpress.com/2019/09/enlarge13.png 912w" sizes="(max-width: 620px) 100vw, 620px" />

Reorganize

Technically, this is a 3+0 cluster now – but the third node doesn’t contain any data yet. If we look at the same table as before, we see that no rows are on the new node:

https://uhesse.files.wordpress.com/2019/09/enlarge2.png?w=150&h=44 150w, https://uhesse.files.wordpress.com/2019/09/enlarge2.png?w=300&h=89 300w, https://uhesse.files.wordpress.com/2019/09/enlarge2.png 685w" sizes="(max-width: 620px) 100vw, 620px" />

To change that, a REORGANIZE needs to be done. Either on the database layer, on schema layer or on table layer. Most easy to perform is REORGANIZE DATABASE:

https://uhesse.files.wordpress.com/2019/09/enlarge14.png?w=150&h=37 150w, https://uhesse.files.wordpress.com/2019/09/enlarge14.png?w=300&h=75 300w, https://uhesse.files.wordpress.com/2019/09/enlarge14.png?w=768&h=192 768w, https://uhesse.files.wordpress.com/2019/09/enlarge14.png 942w" sizes="(max-width: 620px) 100vw, 620px" />

Took me about 10 Minutes on my tiny database. That command re-distributes every table across all cluster nodes and can be time consuming with high data volume. While a table is reorganized, that table is locked against DML. You can monitor the ongoing reorganization by selecting from EXA_DBA_PROFILE_RUNNING in another session.

Final state

Let’s check the distribution of the previous table again:

https://uhesse.files.wordpress.com/2019/09/enlarge15.png?w=150&h=49 150w, https://uhesse.files.wordpress.com/2019/09/enlarge15.png?w=300&h=97 300w, https://uhesse.files.wordpress.com/2019/09/enlarge15.png 684w" sizes="(max-width: 620px) 100vw, 620px" />

As you can see above, now there are rows on the added node. Also EXAoperation confirms that the new node is not empty any more:

https://uhesse.files.wordpress.com/2019/09/enlarge16.png?w=150&h=37 150w, https://uhesse.files.wordpress.com/2019/09/enlarge16.png?w=300&h=74 300w, https://uhesse.files.wordpress.com/2019/09/enlarge16.png?w=768&h=190 768w, https://uhesse.files.wordpress.com/2019/09/enlarge16.png 900w" sizes="(max-width: 620px) 100vw, 620px" />

On a larger database, you would see that the volume usage of the nodes is less than before per node and every node is holding roughly the same amount of data. For failsafety, you could add another reserve node now.

Summary of steps

  1. Add a reserve node (if not yet existing)
  2. Take a backup on a remote archive volume
  3. Shutdown database
  4. Decrease volume redundancy to 1
  5. Add former reserve node as new master node to the volume
  6. Increase redundancy to 2
  7. Enlarge database by 1 active node
  8. Reorganize
  9. Add another reserve node (optionally)