RAC interconnect traffic

 

Preamble

I was looking for RAC cluster interconnect throughput and so accessed to Oracle® Database Reference 11g Release 2 (11.2) documentation and surprisingly, at least for me, I was not able to find a V$ view simply displaying it.

I was able to find it in AWR reports but as AWR package is wrapped it’s not possible to extract the formula Oracle uses:

racinterconnecttraffic1
racinterconnecttraffic1

The funny thing is that if you generate AWR report for the exact same period on another instance of your cluster you may have different result, it happens to me. They are not completely divergent but not strictly equal.

There is also no MOS (My Oracle Support) note explaining how to compute it…

Below views provide the current hardware configuration:

SQL> SET lines 200 pages 100
SQL> SELECT * FROM gv$cluster_interconnects ORDER BY inst_id,name;
 
   INST_ID NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
         1 eth0:1          169.254.5.247    NO
         1 eth1:1          169.254.192.128  NO
         2 eth0:1          169.254.90.252   NO
         2 eth1:1          169.254.158.153  NO
SQL> SET lines 200 pages 100
SQL> SELECT * FROM gv$configured_interconnects ORDER BY inst_id,name;
 
   INST_ID NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
         1 bond0           10.75.36.4       YES
         1 bond0:1         10.75.36.48      YES
         1 bond0:2         10.75.36.33      YES
         1 eth0:1          169.254.5.247    NO
         1 eth1:1          169.254.192.128  NO
         2 bond0           10.75.36.7       YES
         2 bond0:1         10.75.36.47      YES
         2 bond0:2         10.75.36.49      YES
         2 bond0:5         10.75.36.42      YES
         2 eth0:1          169.254.90.252   NO
         2 eth1:1          169.254.158.153  NO

Then there are multiple views providing the amount of blocks (data, undo, …) exchanged between cluster instances:

SQL> SET lines 200 pages 100
SQL> SELECT inst_id,class,cr_block,current_block
FROM gv$instance_cache_transfer WHERE instance IN (1,2)
ORDER BY inst_id,class;
 
   INST_ID CLASS                CR_BLOCK CURRENT_BLOCK
---------- ------------------ ---------- -------------
         1 1st LEVEL bmb            1566          3016
         1 1st LEVEL bmb               0             0
         1 2nd LEVEL bmb               0             0
         1 2nd LEVEL bmb            2241          1652
         1 3rd LEVEL bmb               0             0
         1 3rd LEVEL bmb              15            18
         1 bitmap block                0             0
         1 bitmap block                0             0
         1 bitmap INDEX block          0           347
         1 bitmap INDEX block          0             0
         1 data block             105067        392338
         1 data block                  0             0
         1 extent map                  0             0
         1 extent map                  0             0
         1 FILE header block         347           771
         1 FILE header block           0             0
         1 free list                   0             0
         1 free list                   8             8
         1 save undo block             0             0
         1 save undo block             0             0
         1 save undo header            0             0
         1 save undo header            0             0
         1 SEGMENT header              0             0
         1 SEGMENT header            358          1687
         1 sort block                  0             0
         1 sort block                  0             0
         1 undo block                  0             0
         1 undo block                 67             1
         1 undo header                 0             0
         1 undo header             89024          5956
         1 unused                      0             0
         1 unused                      0             0
         2 1st LEVEL bmb            9368         25428
         2 1st LEVEL bmb               0             0
         2 2nd LEVEL bmb           12819         10844
         2 2nd LEVEL bmb               0             0
         2 3rd LEVEL bmb               0             0
         2 3rd LEVEL bmb             657           651
         2 bitmap block                0             0
         2 bitmap block                0             0
         2 bitmap INDEX block          0             0
         2 bitmap INDEX block          0          1821
         2 data block                  0             0
         2 data block             612191       1999885
         2 extent map                  0             0
         2 extent map                  0             0
         2 FILE header block        2114          3657
         2 FILE header block           0             0
         2 free list                   0             0
         2 free list                1459          1541
         2 save undo block             0             0
         2 save undo block             0             0
         2 save undo header            0             0
         2 save undo header            0             0
         2 SEGMENT header              0             0
         2 SEGMENT header           1654          6654
         2 sort block                  0             0
         2 sort block                  0             0
         2 undo block                  0             0
         2 undo block               5897             0
         2 undo header            272117         12986
         2 undo header                 0             0
         2 unused                      0             0
         2 unused                      0             0
SQL> SET lines 200 pages 100
SQL> SELECT inst_id,cr_requests,current_requests
FROM gv$cr_block_server;
 
   INST_ID CR_REQUESTS CURRENT_REQUESTS
---------- ----------- ----------------
         1      105862            93810
         2      870616          8415406

If you are wondering the difference between CR and current blocks it is clearly explained in Oracle documentation:

The Global Cache Block Access Latency chart shows data for two different types of data block requests: current and consistent-read (CR) blocks. When you update data in the database, Oracle Database must locate the most recent version of the data block that contains the data, which is called the current block. If you perform a query, then only data committed before the query began is visible to the query. Data blocks that were changed after the start of the query are reconstructed from data in the undo segments, and the reconstructed data is made available to the query in the form of a consistent-read block.

As those table contains figures since the instance started it is not easily usable to get the interconnect traffic which is an amount of information over a period of time. I was also tempted to sum GC CR Block Received Per Second and GC Current Block Received Per Second from V$SYSMETRIC_HISTORY table.

But as explained around on Internet this would not take into account the size of messages exchanged by cluster instances. The formula can be finally find in sprepins.sql file located in $ORACLE_HOME/rdbms/admin directory. This file is script to generate Statspack reports.

Estd Interconnect traffic = ((Global Cache blocks received + Global Cache blocks served)*db_block_size +(GCS/GES messages received + GCS/GES messages sent)*200)/elapsed time

This can be verified in an AWR report…

The bad news is that figures are available in GV$SYSSTAT and GV$GES_STATISTICS (or GV$DLM_MISC if you have not executed $ORACLE_HOME/rdbms/admin/catclust.sql script). Means that we will have to subtract figures to compute the interconnect throughput.

Remark:
Even if Oracle takes 200 bytes as GCS/GES message size there could be a formula to compute it but nothing confirmed officially:

SQL>  SELECT SUM(kjxmsize * (kjxmrcv + kjxmsnt + kjxmqsnt)) / SUM((kjxmrcv + kjxmsnt + kjxmqsnt)) "avg Message size"
      FROM x$kjxm
      WHERE kjxmrcv > 0 OR kjxmsnt > 0 OR kjxmqsnt > 0;
 
AVG Message SIZE
----------------
      312.751415

Last but not least the above formula does not take into account the MTU of network interface:

[root@server1 ~]# ifconfig eth0
eth0      Link encap:Ethernet  HWaddr B4:99:BA:A7:07:2A
          inet addr:10.10.10.11  Bcast:10.10.10.255  Mask:255.255.255.0
          inet6 addr: fe80::b699:baff:fea7:72a/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:475791490 errors:0 dropped:0 overruns:0 frame:0
          TX packets:172347326 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:147344045136 (137.2 GiB)  TX bytes:86219877687 (80.2 GiB)

Conclusion could be that crosschecking figures at OS level (RX / TX) versus the ones of Oracle is anyway interesting…

Script

So all is turning around this query:

SQL> SELECT
DECODE(name,'gc cr blocks received','global cache blocks received','gc cr blocks served','global cache blocks served','gc current blocks received','global cache blocks received','gc current blocks served','global cache blocks served',name) AS name,
  SUM(VALUE) AS VALUE,
  TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS date_taken
  FROM gv$sysstat
  WHERE inst_id=1
  AND name IN ('gc cr blocks received','gc cr blocks served','gc current blocks received','gc current blocks served','gcs messages sent','ges messages sent')
  GROUP BY DECODE(name,'gc cr blocks received','global cache blocks received','gc cr blocks served','global cache blocks served','gc current blocks received','global cache blocks received','gc current blocks served','global cache blocks served',name)
  UNION
  SELECT name,VALUE,TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS date_taken
  FROM gv$dlm_misc
  WHERE name IN ('gcs msgs received','ges msgs received')
  AND inst_id=1;
 
NAME                                                                  VALUE DATE_TAKEN
---------------------------------------------------------------- ---------- -------------------
gcs messages sent                                                   5357944 2012-05-16 17:12:34
gcs msgs received                                                   1807244 2012-05-16 17:12:34
ges messages sent                                                   1428546 2012-05-16 17:12:34
ges msgs received                                                   1453824 2012-05-16 17:12:34
global cache blocks received                                         626606 2012-05-16 17:12:34
global cache blocks served                                           508232 2012-05-16 17:12:34
 
6 ROWS selected.

To compute interconnect transfer rate you have to execute the above query 2 times and divide the result by the elapsed time. In a more advanced script it gives:

racinterconnecttraffic

If you want to recompute what you find in an AWR report you can use DBA_HIST_DLM_MISC and DBA_HIST_SYSSTAT hsitory tables.

Performance queries

Global Cache Service processes (LMS) statistics:

SQL> SET lines 200 pages 100
SQL> SELECT * FROM gv$current_block_server;
 
   INST_ID       PIN1      PIN10     PIN100    PIN1000   PIN10000     FLUSH1
---------- ---------- ---------- ---------- ---------- ---------- ----------
   FLUSH10   FLUSH100  FLUSH1000 FLUSH10000     WRITE1    WRITE10   WRITE100
---------- ---------- ---------- ---------- ---------- ---------- ----------
 WRITE1000 WRITE10000    CLEANDC      RCVDC    QUEUEDC    EVICTDC    WRITEDC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1         25        255        355          0          0       4255
      2163         60          4          0      36586      10143        478
        42         39          0          0          0      23948          0
 
         2        119        393        471          0          0      18724
      6353         61          0          0     163483      58352       2277
       284        623          0          0          0      65906          0

Latency of interconnect messages:

SQL> SET lines 200 pages 100
SQL> SELECT * FROM dba_hist_interconnect_pings WHERE snap_id=2846;
 
   SNAP_ID       DBID INSTANCE_NUMBER TARGET_INSTANCE   CNT_500B  WAIT_500B WAITSQ_500B     CNT_8K    WAIT_8K  WAITSQ_8K
---------- ---------- --------------- --------------- ---------- ---------- ----------- ---------- ---------- ----------
      2846 2446434119               1               1      49270   11135446     2673946      49270   10535445    2393620
      2846 2446434119               1               2      49270   12011547     4889338      49270   15731115    7441966
      2846 2446434119               2               1      49270   11611649     4012287     206735   62938564   29425924
      2846 2446434119               2               2     206896   55943066    15317805     206896   52958767   13732654

Interesting one coming from racdiag.sql script:

SQL> SET lines 200
SQL> SELECT b1.inst_id, b2.VALUE "GCS CR BLOCKS RECEIVED",
     b1.VALUE "GCS CR BLOCK RECEIVE TIME",
     ((b1.VALUE / b2.VALUE) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
     FROM gv$sysstat b1, gv$sysstat b2
     WHERE b1.name = 'global cache cr block receive time'
     AND b2.name = 'global cache cr blocks received'
     AND b1.inst_id = b2.inst_id OR b1.name = 'gc cr block receive time'
     AND b2.name = 'gc cr blocks received'
     AND b1.inst_id = b2.inst_id;
 
   INST_ID GCS CR BLOCKS RECEIVED GCS CR BLOCK RECEIVE TIME AVG CR BLOCK RECEIVE TIME (ms)
---------- ---------------------- ------------------------- ------------------------------
         1                 207686                      5048                     .243059234
         2                1089552                     43020                     .394841182

References

2 thoughts on “RAC interconnect traffic

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>