5 Replies Latest reply: Nov 5, 2012 7:57 AM by Sebastian Solbach -Dba Community-Oracle RSS

    Application partitioning on RAC recommendations

    Chuck1958
      I've got a 2 node 11GR2 RAC cluster with lots of schemas (145 to be exact) that I inherited. The schemas are for the most part identical. They are used by different customers running the same application against their own data. Up until now we've used the same service name to connect each client to the database. There are no preferred instances configured.

      I'm thinking about partitioning the application such that half of the customers default to node 1 and the other half to node 2. I plan on doing this using service names and preferred instances. Both instances will support both service names but I'll make each instance the preferred one for one service name.

      I was wondering what other peoples' opinions are regarding this? Is this a good idea? If so is it a good way to partition the application? Is it better to do it with the cluster node VIPs instead of service names? Are they any gotcha's I should watch out for?

      There are a couple of benefits I hope to achieve.

      1) Spread out the CPU load. CPU is < 10% most of the time on both nodes but during nightly batch processing, one node spikes to 100% while the other remains under 10%. I suspect all of the batch processing for all of the schemas is running on the same node.
      2) Reduce child cursors. I have clients on both nodes using every schema. That leads to lots of child cursors. In some cases I have 4000 child cursors for the same SQL_ID.
        • 1. Re: Application partitioning on RAC recommendations
          doginthesun
          I found partitioning application workloads across instances in RAC clusters using service names and resource plans to work very well, especially in reducing interconnect congestion. One our RAC databases runs on a four node cluster and has about 15 applications accessing different schemas. I have them all using unique service names; so I can localize processing and data access as much as possible for those applications and their workload cycles to specific instances in the cluster.

          Oracle has a good introduction white paper here:

          http://www.oracle.com/technetwork/products/clustering/overview/awm11gr2-130711.pdf
          • 2. Re: Application partitioning on RAC recommendations
            JohnWatson
            The scheme you are considering (service names, not VIPs) should partition the work perfectly. On the positive side, you should find that data objects are (largely) used in only one instance. This will reduce the cache fusion traffic, and resource remastering will result in the objects being mastered by the instance in which they are used. This should reduce your Global Cache wait events (if you have any) hugely. But negatively, you will be disabling RAC's load balancing capabilities.
            I would try it.
            • 3. Re: Application partitioning on RAC recommendations
              Chuck1958
              We were hoping rac would load balance for us when it was set up but we are not seeing that when during nightly batch processing one node spikes to 100% cpu and the other stays below 10. How exactly does the load balancing work? Does it consider cpu or is it purely based on the number of sessions?
              • 4. Re: Application partitioning on RAC recommendations
                JohnWatson
                Chuck1958 wrote:
                We were hoping rac would load balance for us when it was set up but we are not seeing that when during nightly batch processing one node spikes to 100% cpu and the other stays below 10. How exactly does the load balancing work? Does it consider cpu or is it purely based on the number of sessions?
                The default algorithm for distributing sessions is LONG (determined by the -j switch of srvctl add service) which is based on session count. The alternative is SHORT, which is either response time or throughput or CPU usage, determined by the -B switch. It sounds as though you might need
                -j short -B none
                which should balance new sessions according to CPU utilization.
                • 5. Re: Application partitioning on RAC recommendations
                  Sebastian Solbach -Dba Community-Oracle
                  However your applications must be working with connection pooling and/or make new connections to be able to profit from load balancing.

                  See here to get some informations about this:

                  http://www.oracle.com/technetwork/products/clustering/overview/awm11gr2-130711.pdf

                  Regards
                  Sebastian