How to limit CPU-Consumption of a database

With the trend to consolidate more and more databases on a single server the question arises how to limit the resource consumption of a single database. Memory can easily be limited because most of the memory is going to be allocated either for the SGA or the PGA. But what about the CPU? By default all processes from all instances are balanced across all CPUs of the server.

Instance Caging

Instance Caging was introduced with Oracle 11g Release 2 and uses the database parameter cpu_count to specify the number of CPUs used by an instance. But the drawback is that resource manager is required so instance caging is limited to the Oracle Enterprise Edition.

The setup is very simple as shown in the following example:

Example

SQL> ALTER SYSTEM SET cpu_count=4;
SQL> ALTER SYSETM SET RESOURCE_MANAGER_PLAN='DEFAULT';

You can of course create and use your own resource manager plan as you like. The cpu_count is a dynamic parameter so if you like you can change the number of CPUs used by one instance from time to time (e.g. allocating CPUs for a batch process or for a monthly report).

But this parameter does not allow allocating specific CPUs for one instance. There is a chance that processes from different instances are running on the same CPU.

PROCESSOR_GROUP_NAME

PROCESSOR_GROUP_NAME is again a database parameter which was introduced with Oracle 12c and is now available with 11.2.0.4 as well. The parameter relies on the ability that the operating system is able to control individual resources like CPU, memory, IO or network. On Linux so called control groups (cgroups) are available with kernel 2.6.32 and newer and on Solaris “resource pools” are available with 11 SRU 4 and above.

Let’s have a closer look on how to bind all processes of an instance to dedicated processors using Oracle Enterprise Linux 6U4.

The main file for the configuration is /etc/cgconfig.conf. There are various ways to add or change configurations and groups but the simplest one is to just edit the file as needed.

Per default there is a list of resources which can be throttled. This list can be changed or you can add your own configuration environment. In this example I will create my own environment with only one resource: cpuset “jocpuset”.

# cat /etc/cgconfig.conf
mount {
       cpuset = /cgroup/jocpuset;
}

The next step is to add a group in the configuration. I’ve named the group similar to the name of the database which might not be a bad idea as long as you want to use individual groups for your databases – which make sense to me.

group grp-JOHANN {
       perm {
               admin {
                       uid = oracle;
                       gid = dba;
               }
               task {
                       uid = oracle;
                       gid = dba;
               }
       }
       cpuset {
               cpuset.cpus="1,3";
       }
}

As you can see I’ve set the control group to only use CPUs 1 and 3. There are several other parameters within one resource you can control. E.g. instead of using distinct CPUs you can specify shares like in the VMware environment and many more.

Now the service cgconfig has to be started or restarted:

# service cgconfig start

If the service should automatically start after a reboot (which is preferrable) you need to enable the service with chkconfig.

# chkconfig cgconfig on

If you want to reset the configuration you can just issue the command:

cgclear # reset the entire configuration
cgclear grp-JOHANN # reset reset a dedicated group grp-JOHANN

Now it’s time to bind our database instance to the resource group. Simply speaking just set the parameter PROCESSOR_GROUP_NAME=<group> and that’s it.

SQL> ALTER SYSTEM SET processor_group_name = 'grp-JOHANN' SCOPE=spfile;

Unfortunately the parameter is not dynamic which is obvious because it’s a very restrict binding to the OS.

Keep in mind that, if you stop the service the instance will not be able to start. You probably get an error message like following:

SQL> startup
ORA-56729: Failed to bind the database instance to processor group grp-JOHANN;
Additional Information: cpuset not found in /proc/mounts at skgsnmvpgs:3

Conclusion

The usage of control groups and the parameter PROCESSOR_GROUP_NAME are a huge step forward in the management of databases. Even if this example with a hard binding to a specific CPU might not fit to your requirements the usage of shares or one of the other parameters will help to limit the consumption of several kind of resources in an environment with multiple databases.

And the usage is not limited to Enterprise Edition but can be used with Standard or Standard One as well. That’s again a nice advantage over instance caging.