Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Now You Can Automatically Scale Your Autonomous Data Warehouse With A Single Click...

Keith Laker
Senior Principal Product Manager

Exciting news - your autonomous data warehouse just got even smarter. Welcome to the new world of (big drum roll...AUTO SCALING for your Autonomous Data Warehouse.

When you select auto scaling, your Autonomous Data Warehouse can use up to three times more CPU and IO resources than specified by the number of OCPUs currently shown in the Scale Up/Down dialog. That's a lot of extra power! When auto scaling is enabled, if your workload requires additional CPU and IO resources then the database automatically adds resources without any manual intervention. That means now there is even less to mange and monitor in Autonomous Data Warehouse.

So how do you create an autonomous data warehouse that can "auto scale"?

Creating a new "auto scaling” data warehouse

 If you have already created a few data warehouses in the Oracle Cloud then you will be familiar with the very simple pop-up form we provide to create a new autonomous data warehouse. From today you should notice a new check box just underneath the boxes where you decide how many OCPUs and how much storage you need.... 


Screen Shot 2019 06 04 at 21 08 38


It says “Auto Scaling” and the associated text explains what the feature does - essentially what I just explained above. Simply click the check box and that’s it.

Screen Shot 2019 06 04 at 21 10 52

In one single click you have created a super smart, autonomous data warehouse that is going to scale itself based on your workload. Really, we could not have made it any easier!

But I already created my Autonomous Data Warehouse…

So what do you do if you already have an autonomous data warehouse and it's being used non-stop, every day. Great news, you can switch on auto scaling for an existing data warehouses with a single click. All you need to do is click on the “Scale Up/Down” button on the OCI console and this pop-up form which manages scaling also now contains a new check box to enable (and disable) auto scaling.

Screen Shot 2019 06 04 at 21 15 41

Enabling auto scaling on an existing instance requires zero downtime. We have made it super easy for you to take advantage of this important new feature. 

How does it work?

Let’s work through a simple example. Suppose I have an existing Autonomous Data Warehouse with 4 OCPUs and 1TB of storage and auto scaling is not enabled (i.e it's disabled).



First step is to enable the auto scaling feature by clicking on the “Scale Up/Down” button…




and clicking on the Auto Scaling check box



as soon as I click on the blue Update button my instance will switch to “SCALING IN PROGRESS” mode. 



at this point my data warehouse is still online and fully available. In the background the system is setting up the infrastructure so the data warehouse can just expand itself as the workload grows. Notice at this point, the system is still showing that auto scaling is disabled. After about a minute the status area will change to “AVAILABLE” and auto scaling will be enabled. 


Now let’s throw some queries at this data warehouse. In fact let’s use up all the OCPU capacity on the system and let’s run this workload a number of times at different points in the day and see what happens. If we switch to the Service Console we can monitor the resources and workload on our data warehouse. In the service console graphs below you can see that my workload peaks at around 24 queries and that same workload kicks off at different points in time throughout the day, hence the spikey looking graph…



You will notice that when my queries are running the CPU utilization hits 100%…


But there is something odd going on here because I am maxing out on the OCPU resources but I am not seeing any sign of queries getting queued? 



What is going on here? Well it's got nothing to do with auto scaling and here's why...Firstly, you will notice that my queries are running in the low resource group so queries here won't get queued. Secondly, enabling auto scaling does not change the concurrency and parallelism settings for the predefined services. See Managing Concurrency and Priorities on Autonomous Data Warehouse section in the documentation for more information.

Anyway, if we switch over to the “Overview” graphs we can track what’s happening to the number of OCPUs being used by my data warehouse...



in the graph showing number of OCPUs allocated you can see that with auto scaling the graph is no longer a straight line over time. The number of OCPUs is fluctuating and the reason it’s fluctuating is because my autonomous data warehouse is reacting to the size of my workload by automatically adding, totally transparently behind the scenes, more OCPUs. Each time we get close to fully using all the OCPU capacity, the system adds more OCPUs up to my maximum of 3x the original starting number (which in this case was 4 OCPUs). This means I have a window of between 4 and 12 OCPUs that I can use to run my workload. 




The release of auto scaling takes autonomous data warehouse to the next level (it takes up 3x levels). It makes it even easier to simply deploy a data warehouse then forget about it - everything to do with the management and tuning of the data warehouse is taken care of for you. Your data warehouse is simply there, online and ready to work whenever you need it. 

If you have never tried Autonomous Data Warehouse then sign-up for a free trial - https://cloud.oracle.com/tryit - then you can try auto scaling for yourself.

If you already have an Autonomous Data Warehouse then switch on auto scaling and move your autonomous warehouse to the next level -  enable auto scaling and then sit back and forget about your data warehouse - focus on your data analysis because that's what's important.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.