7 Replies Latest reply: Feb 22, 2012 1:20 PM by 863526 RSS

    High Avg wait for "gc buffer busy" in RAC

    542933
      We are working on a Proof of concept (POC) with a RAC deployment for a application.

      characteristic of the application:

      1. Heavy DML with monotonically increasing indexes.
      2. heavy commit frequency
      3. business critical customer facing application.


      While defining a baseline , when we ran a test with the following scenario:

      1. 1 table with one index
      2. The index is based on a sequence number (Monotonically increasing index)
      3. Insert into the table, simultaneously from 4 sessions , on a single instance DB.
      4. Insert into the table, simultaneously from 2 sessions each from 2 instances of a RAC.

      My hope was that , I will find 20% degradation in throughput , in case of RAC , compared to single instance.
      The degradation I was expecting , to compensate fro Global cache and Global enqueue management.

      However, to my dismay I observed that :

      1. Average "gc buffer busy" wait in RAC was 36 ms , compared to average "buffer busy wait" time of < 1 ms for single instance
      2. Average "enq TX: index contention" was 25 ms , compared to average "enq: TX index contention" time of <1 ms for single instance.
      3. Concurrency + Cluster related waits in RAC DB was 60% of DB time , compared to single instance DB, which was 10% of DB time.

      Is this common or I am missing something.
        • 1. Re: High Avg wait for "gc buffer busy" in RAC
          573740
          Hi,

          This is expected behaviour of waits, when you try any application accessing a single node database Vs RAC.

          The challenge is not only to move a standalone database to RAC, but the real challenge is to tune the application to fit with RAC.

          The best way of utilizing the RAC is to Distribution of data region wise, country wise etc, like google, if you access google from UK it will go to google.co.uk, from india it will go to google.co.in. But this is not simple to implement as it needs a lot of changes in application code and database design also.

          The other way is to identify the bottle necks thats taking place at the database levels, sql tuning, increasing cache for sequences etc.

          You can check out some presentations by Julian Dyke on RAC performances
          http://www.juliandyke.com/Presentations/Presentations.html
          • 2. Re: High Avg wait for "gc buffer busy" in RAC
            Surachart Opun
            2. The index is based on a sequence number (Monotonically increasing index)
            - Did you test on sequence + cache ?
            alter sequence a cache b;

            - Did you use high bandwidth on interconnect?
            Interconnectivity is the key to hardware scalability, which greatly depend on high bandwidth and low latency.
            By the way...
            Online transaction processing (OLTP) is characterized by short transactions that cannot be further broken down and, therefore, no speedup can be achieved. However, by deploying greater amounts of resources, a larger volume of transactions can be supported without compromising the response.
            • 3. Re: High Avg wait for "gc buffer busy" in RAC
              542933
              Sir,

              Thank you a lot for your your insight.

              So, the claim "You do not need an application change to run on RAC" is not correct , in absolute terms .

              For heavy OLTP applications , if we can not implement Application functional partitioning or Horizontal data partitioning across instances,
              RAC is not an option. (PLease correct me if I am wrong).

              We have tried sequence cache 10000, However, I do not understand how that is going to help in the "right most index block " contention .
              Can you please through some light into this /

              Thanks you again for taking time to reply this post,

              best regards,

              amalendu.
              • 4. Re: High Avg wait for "gc buffer busy" in RAC
                Surachart Opun
                For heavy OLTP applications , if we can not implement Application functional partitioning or Horizontal data partitioning across instances,
                RAC is not an option. (PLease correct me if I am wrong).
                about OLTP, RAC can not help about response time , but help to reserve many sessions...

                PARTITION -> help to improve about contentions on table + index

                by the way, Increasing INITRANS -> if you have many sessions to read on the same block, that may help improve performance

                http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/usingpe.htm#i1009092

                We have tried sequence cache 10000, However, I do not understand how that is going to help in the "right most index block " contention .
                Can you please through some light into this /
                Oracle Sequences and Index Contention
                • Insert intensive applications using Sequence
                generated index keys may suffer from index leaf
                block contention
                • In RAC, this may lead to a high rate of Global
                Cache Service traffic
                • By using larger sequence caches with the NOORDER
                option successive index block splits tend to create
                instance affinity to index leaf block ranges
                • After a certain number splits, each instance will
                write to different ranges of leaves in the index tree.
                • The higher the insert rate the larger the CACHE
                value should be.


                example:
                you have 2 nodes and sequence cache 10000 + noorder, so:

                node01 cached sequence in memory 1 - 10000
                node02 cached sequence in memory 10001 - 20000

                http://www.oaktable.net/getFile/158
                • 5. Re: High Avg wait for "gc buffer busy" in RAC
                  420848
                  Before making any solid conclusions, I would seriously consider scaling up your benchmark. I would not at all consider 4 concurrent sessions to be an accurate representation of a "Heavy DML" system. Try running again using the expected number of sessions in production.

                  RAC is more about fail-over and scalability than it is about overall performance. You can certainly build a "faster" system on day one by using a single instance but when you reach capacity it becomes a major pain to add computing resources.

                  The 60% performance hit that you are reporting is not at all in line with what I have seen with larger, more diverse workloads (80% Select, 20% DML)
                  • 6. Re: High Avg wait for "gc buffer busy" in RAC
                    542933
                    Hi David,

                    You are correct, in case more usual OLTP systems, where we have 20/80 distribution of DMLs and Queries, we will not see
                    60% of DB time spent in Cluster + concurrency waits.

                    However, we are seeing this behaviour because of two reasons:

                    1. Our load profile is 80/20 distribution of DML/Query.
                    2. We have couple of MOnotonically Increasing B*TREE indexes on the most active tables in the DB.

                    My point was that, for same Transaction rate , I was expecting 20% degradation in RAC , versus single instance performance.

                    However, the degradation was > 60% in RAC versus single instance.

                    Thanks a lot for all your helpful answers.

                    I think I got my answer.

                    Thanks and regards,

                    amalendu.
                    • 7. Re: High Avg wait for "gc buffer busy" in RAC
                      863526
                      hello,

                      I have some problems too with RAC and searching for answers for months without any clud. Will you please advise as you have seen this before.

                      I have an insert SQL using but 1 - 2 mins with 50000 insert with RAC, without RAC, it is 18 secs.

                      Is this normal? there interconnect time is fast. I've following the guidelines for tuning RAC

                      Will you please advise.

                      thanks

                      andrew