Testing Scalability of RAC: Using SwingBench OLTP

(VERSION INFORMATION: Oracle GI & DB11.2.0.3; Red Hat 5u7)

I used swingbench and a particular service in swingbench that does this “Models the classic order entry stress test. It has a similar profile to the TPC-Cbenchmark. This version models a online order entry system with users being required to log-on before purchasing goods.”

Section A: Creating oracle database service

I will create a service called swing to easily monitor and evaluate using one instance of RAC cluster (singleton). Later, I will modify the swing service to use two instances in the RAC cluster.

1.    Create service with preferred instance. (it only use one instance at a particular time, if node 1 is down, swing.tobi.com service will failover to orcl2)

$ srvctl add service -d orcl -s swing.tobi.com -r orcl1 -a orcl2

2      $srvctl start service -d orcl -s swing.tobi.com

Section B: Setting up SwingBench & Loading data

Download SwingBench from : http://dominicgiles.com/downloads.html

1.      Make sure you have the right JRE version that works well with the swingbench software you downloaded.

$ java -version

Cd to swingbench/bin directory

$cd /tmp/swingbench/bin

$./oewizard

Create tablespace for soe database user. SOE tablespace will contain Order Entry data.

SQL> CREATE TABLESPACE SOE DATAFILE SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Section C: Generate OLTP Activities

1.      Edit configs/oeconfig.xml (your swingbench location )

<ConnectString>//red-scan.tobi.com/swing.tobi.com</ConnectString>

<Password>soe</Password>

<UserName>soe</UserName>

//red-scan.tobi.com/swing.tobi.com connection string to the particular service I created above.

2.      $ bin/charbench -c /tmp/swingbench/configs/oeconfig.xml -uc 20  -a -v users,trans,tps   

N.B: to know more about charbench command options $charbench -h

3.      Check service time and through using gv$servicemetric_history

SELECT

service_name

, TO_CHAR(begin_time, 'HH:MI:SS') begin_time , TO_CHAR(end_time, 'HH:MI:SS') end_time

, instance_name

, elapsedpercall service_time

, callspersec throughput

FROM

gv$instance i , gv$active_services s , gv$servicemetric_history m WHERE s.inst_id = m.inst_id AND s.name_hash = m.service_name_hash

AND i.inst_id = m.inst_id

AND m.group_id = 10 and service_name='swing.tobi.com'

ORDER BY

service_name

, i.inst_id

, begin_time ;

On the sheet above, swing.tobi.com only runs on instance (node) orcl1 and has average value of 11000 and 1500 for service time and throughput respectively.

You can only use some Linux utility such as sar and vmstat to monitor the resources consumption on all the node on the cluster.

Section D: Increase The Preferred Instance of swing.tobi.com Service to 2

The following procedure is like turning on Active/Active mode for swing.tobi.com service. Earlier it was created to be available at a point in time on only one instance (node) i.e. Active/Passive.

[oracle@red1 ~]$ srvctl modify service -d orcl -s swing.tobi.com -i orcl2 -r

[oracle@red1 ~]$ srvctl config service -d orcl

Service name: swing.tobi.com

Service is enabled

Server pool: orcl_swing.tobi.com

Cardinality: 2

Disconnect: false

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Failover type: NONE

Failover method: NONE

TAF failover retries: 0

TAF failover delay: 0

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: SERVICE_TIME

TAF policy specification: NONE

Edition:

Preferred instances: orcl1,orcl2

Available instances:

[oracle@red1 ~]$  srvctl start service -d orcl -s swing.tobi.com -n red2

[oracle@red1 ~]$ srvctl status  service -d orcl -s swing.tobi.com

Service swing.tobi.com is running on instance(s) orcl1,orcl2

1.      Check service time and through using gv$servicemetric_history

SELECT

service_name

, TO_CHAR(begin_time, 'HH:MI:SS') begin_time , TO_CHAR(end_time, 'HH:MI:SS') end_time

, instance_name

, elapsedpercall service_time

, callspersec throughput

FROM

gv$instance i , gv$active_services s , gv$servicemetric_history m WHERE s.inst_id = m.inst_id AND s.name_hash = m.service_name_hash

AND i.inst_id = m.inst_id

AND m.group_id = 10 and service_name='swing.tobi.com'

ORDER BY

service_name

, i.inst_id

, begin_time ;

Conclusion:

The result shows that throughput is reduced to half, when compared with first query above when one instance (server) is hosting the swing.tobi.com service. This is a show of RAC scalability in OLTP environment; you can accept more users without contention by add more nodes/instances. But NOTE “service time” did not reduce, because in an OLTP environment, users database activities are very light (i.e. short transaction that cannot be further broken down).

OLTP service time can be improve by other means e.g. using fast disk for critical datafiles.

At OS Level: sar –q shows that system/nodes load averages is halved when the two instance is enable to receive requested through the swing services.

Tobi.com is strictly an internal private domain name for me  J

Ref & More reading:

1.       Oracle® Real Application Clusters Administration and Deployment Guide
11g Release 2 (11.2)

2.       http://dominicgiles.com