How do I scale an Autonomous Database?

Traditionally database deployments have been designed and provision for the peak possible workload. And in reality, a substantial margin of safety was also provisioned on top of that in order to make sure the system could cope with any unforeseen demands.
But peak workloads tend to occur infrequently, leaving most of this costly capacity idle the majority of the time.

In order to enable customers to pay for only the resources they need, Oracle Autonomous Database allow customers to elastically adjust their compute and storage resources when necessary.

An Autonomous Database can be scaled through the UI as shown in the video below or via our cloud APIs or CLI commands. In the video you see how I can scale an 2 CPU  configuration to an 8 CPU configuration in under a minute  to accommodate 48 concurrent users running a JSON workload.

Alternatively, you can enable Auto Scaling and let Oracle scale the Autonomous Database up to 3x the initial CPU/IO resources immediately when needed by the workload. Once the resource demand drops off, the Autonomous Database will automatically be scaled back to the original or base CPU. Allow you to truly only pay for the resources you need, when you need them since the additional CPU is only charged for the time you use them.

You can enable Auto Scaling either during provisioning or by modify an existing database using the Scale Up/Down dialog on the UI (or via the API).

So what can you expect for Auto Scaling?

Let’s take a look at it in action by comparing what happens on two identical Autonomous Databases, one with Auto Scaling (ADW2) and one without (ADW1).  Both Autonomous Database have a CPU count of 4, which means ADW1 can use up to 4 CPUs, while ADW2 can use up to 12 CPUs (3 X Base CPU) if the workload demands it.

If I start 8 sessions on each database and begin running queries, and look at the Overview tab on the Autonomous Database Service Console, we can see how the two databases behave.

As you can see from the service console on the left above, with 8 active sessions (2 threads X 4 CPUs), we were able to max out our 4 CPUs on ADW1 (100% busy). However, on ADW2 we have the same 4 CPUs busy but the utilization is only indicating the system is only 33% busy as ADW2 has a maximum of 12 CPUs thanks to Auto Scaling.

Let’s now increase the number of concurrent sessions to 24 (2 threads X 12 CPUs) and see what happens on both systems.

If we look at ADW1 now, we see in the upper left hand corner that 8 sessions are on CPU but 16 sessions are waiting, as the CPU is fully utilized (100% busy) by 8 concurrent queries.

But if we look at ADW2 on the right hand side, we see that all 24 sessions are active and now 12 CPUs are fully utilized (100% busy).

Once the workload drops off, ADW2 will be automatically scaled back to just 4 CPUs and you will only pay for the additional CPU when they are used.

But how do I know what CPU has been used when?

Also available on the Overview tab of the Autonomous Database service console is a graph labeled “Number of OCPUs allocated”, which indicates exactly how many CPUs were used over time.

From the graph above, you would only be charged for the additional CPUs for the period of the peaks, where the CPU goes above the base CPU of count of 4.

What’s the difference between manual scaling and Auto Scale?

When you manually scale an Autonomous Database, we will increase the memory allocated, the number of concurrent sessions allowed, and the degree of parallelism used for queries issued on the HIGH service, as well as the CPU count and IO throughput. The assumption being that you will need these additional resources going forward or for some period of time.

However, when Auto Scale kicks in, you do not get additional memory, or a higher degree of parallelism, since the goal of Auto Scale is to help you deal with a rapid increase in concurrent queries / workload. Once the demand drops off you want the system to go right back to what it was before.

Therefore, if you need more CPU power or a higher degree of parallelism for an individual SQL statement you should manually increase the base number of CPUs on your system. If you just need additional resources periodically when there is an increase in concurrency, you should use Auto Scale.

This entry was posted in Autonomous Database, Top_Tip and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *