Mixed Consolidated Workloads and the Sun Oracle Database Machine

By Robert Stackowiak
February 6, 2010

The Sun Oracle Database Machine is often considered for database server and storage consolidation. Though some companies deployed the first version of this platform for data warehouse consolidation and now run multiple databases on the Database Machine using Oracle Database 11g Release 1, 11g Release 2 added many consolidation manageability features and options appropriate as organizations begin to deploy mixed workloads. Key features and options important in today's consolidation efforts include:

• Real Applications Clusters (RAC), RAC One, and Oracle workload management services for distributing and isolating workloads to specific instances
• Instance Caging for defining CPU usage limits
• I/O Resource Management (IORM) and IORM in combination with Automatic Storage Management (ASM) for managing storage Grid resources

As the Sun Oracle Database Machine can support data warehousing and transactions processing applications databases, many organizations are evaluating deploying multiple databases dedicated to such applications on the same box. They generally factor in some of the following metrics as they justify these consolidation efforts:

• Reduction in overall power usage
• Reduction physical footprint in the Data Center relieving space limitations
• Reduction in personnel costs / simplified systems management
• Improved time to Return on Investment through adoption of a standard pre-integrated platform

While these business drivers can drive projects, whether the Sun Oracle Database Machine is technically the right database consolidation platform can depend on a number of considerations including:

• Are applications under consideration supported on Oracle Database 11g Release 2? Are they supported on Oracle RAC, ASM, and Oracle Enterprise Linux?
• Are there significant variations in service level agreements among the different databases and applications?
• Are there significant variations in availability requirements among different databases and applications?
• Are there additional scalability concerns within applications tiers?

If the answer to any of these is "no", you might consider alternatives for your consolidation initiative or re-evaluate some of the requirements.

For many organizations, these considerations are not standing in the way of consolidation. So, we'll next take a quick look at the capabilities in Oracle Database 11g Release 2 that can enable database consolidation to take place on the Sun Oracle Database Machine. Since the hardware platform contains multiple database server nodes and storage cells, we'll first take a look at software features and options that enable both server and storage consolidation.


Server and Storage Level Consolidation

A key to database scalability and high availability using the Sun Oracle Database Machine is Oracle RAC, enabling the Oracle database to be deployed as multiple database instances on multiple nodes. In consolidation efforts, multiple RAC databases are most commonly deployed on the Database Machine enabling easier migration of Oracle databases from legacy platforms than consolidating these databases to a single database RAC implementation would require. Single instance Oracle databases can be deployed to the Database Machine using Oracle RAC One Node. Database server consolidation can further be aided by Oracle database services that are aligned by application, groups of users, and / or workload type, and used in workload management and workload placement (parallel query obeys the defined placement).

Storage level consolidation is enabled through ASM. A single diskgroup for all storage cells is the recommended configuration for striping all data across all cells and providing maximum throughput and ease of management. Alternatively, you can segregate groups of cells to better isolate database workloads, but your tradeoff would be reduced throughput, more management overhead, and fewer CPUs for compression.

Mixed workloads introduce additional sizing and configuration considerations. While each combination of transaction processing and data warehousing workloads is likely to be unique, a rule of thumb to use is that you should choose one of the following: indicate that all of the "priority" workloads are latency sensitive or throughput sensitive, or configure the Flash Cache so that it is only used by the transaction processing workloads.


The Database Resource Manager, Instance Caging, and IORM

The Oracle Database Resource Manager was introduced several database releases ago and continues to evolve. Accessible from Oracle Enterprise Manager Grid Control, it can be used with the Sun Oracle Database Machine to set up consumer groups based on services, username, and other session attributes, set maximum CPU allocation for an application, manage a CPU shared among multiple applications, set degree of parallelism, prevent runaway queries (query execution time estimates are compared to defined consumer group limits before their execution), and pass IORM directives to storage.

For example, Sun Oracle Database Machine database workloads can be managed using a concept called "Instance Caging" to limit the amount of CPU that an Oracle Database instance can use. This is accomplished by setting a "cpu_count" parameter. For non-critical databases sharing a pool, you would specify the maximum value any single database and the percentage of CPU allocated would be based on the cpu_count divided by the cpu_count total of all active databases. Where the databases handle "priority" workloads, the sum of the cpu_count total for such databases should be equal to the number of CPUs in the system so that if all such databases are active, the CPU allocation is 100 percent.

IORM enables Exadata to limit the number of I/O requests by managing queues and issuing enough I/Os to keep disks performing efficiently. Low priority workloads are prevented from flooding disks. Dequeues are based on resource plan directives, either based on allocations between multiple databases (using storage cell configurations) or based on allocations between consumer groups by using the Database Resource Manager. Note that IORM currently provides no management of objects cached in Flash and so it is not effective in managing workloads where there are Flash scans.


Summary

Consolidation of Oracle databases to the Sun Oracle Database Machine has potential benefits that can appeal to lines of business and to IT. Multiple workloads can be deployed and managed using this platform and can include mixed workloads of transaction processing and data warehousing each deployed on their own databases. Oracle Database 11g Release 2 introduced several key capabilities for deploying and managing these multiple workloads including Instance Caging and IORM. While careful planning is necessary, you can succeed in such deployments by using the features and options present in Oracle Database 11g Release 2.


Special thanks to Dan Norris and Lawrence To of Oracle for their guidance.


You might also be interested in:

News Topics

Recommended for You

Got a Question?