Proof of Concept Maximum Availability Architecture

Performance and Scalability Test
To test the performance and scalability of our 4 node RAC cluster we setup a test script with a trick we learned from Steve Adams. Use a common lock on a table, to kick-off all the sessions you want to have run concurrently on a system at once! We tested for 25, 50, 100, 200 and 400 concurrent users all firing the emp_dml.sql code at the same time.

The used performance scripts:

scott_acor.sql — Re-Creates the scott.emp table with 25 rows in it.

DROP TABLE EMP PURGE;

CREATE TABLE EMP
(EMPNO NUMBER(10) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO EMP VALUES
(1,’SMITH’,'CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,NULL,20);
INSERT INTO EMP VALUES
(2,’ALLEN’,'SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);
INSERT INTO EMP VALUES
(3,’WARD’,'SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);
INSERT INTO EMP VALUES
(4,’JONES’,'MANAGER’,7839,to_date(’2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);
INSERT INTO EMP VALUES
(5,’MARTIN’,'SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);
INSERT INTO EMP VALUES
(6,’BLAKE’,'MANAGER’,7839,to_date(’1-5-1981′,’dd-mm-yyyy’),2850,NULL,30);
INSERT INTO EMP VALUES
(7,’CLARK’,'MANAGER’,7839,to_date(’9-6-1981′,’dd-mm-yyyy’),2450,NULL,10);
INSERT INTO EMP VALUES
(8,’SCOTT’,'ANALYST’,7566,to_date(’13-JUL-1987′)-85,3000,NULL,20);
INSERT INTO EMP VALUES
(9,’KING’,'PRESIDENT’,NULL,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,NULL,10);
INSERT INTO EMP VALUES
(10,’TURNER’,'SALESMAN’,7698,to_date(’8-9-1981′,’dd-mm-yyyy’),1500,0,30);
INSERT INTO EMP VALUES
(11,’ADAMS’,'CLERK’,7788,to_date(’13-JUL-1987′)-51,1100,NULL,20);
INSERT INTO EMP VALUES
(12,’JAMES’,'CLERK’,7698,to_date(’3-12-1981′,’dd-mm-yyyy’),950,NULL,30);
INSERT INTO EMP VALUES
(13,’FORD’,'ANALYST’,7566,to_date(’3-12-1981′,’dd-mm-yyyy’),3000,NULL,20);
INSERT INTO EMP VALUES
(14,’MILLER’,'CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,NULL,10);
INSERT INTO EMP VALUES
(15,’MARTIN’,'SALESMAN’,7698,to_date(’28-9-1991′,’dd-mm-yyyy’),1250,1400,30);
INSERT INTO EMP VALUES
(16,’BLAKE’,'MANAGER’,7839,to_date(’1-5-1991′,’dd-mm-yyyy’),2850,NULL,30);
INSERT INTO EMP VALUES
(17,’CLARK’,'MANAGER’,7839,to_date(’9-6-1991′,’dd-mm-yyyy’),2450,NULL,10);
INSERT INTO EMP VALUES
(18,’SCOTT’,'ANALYST’,7566,to_date(’13-JUL-1997′)-85,3000,NULL,20);
INSERT INTO EMP VALUES
(19,’ALLEN’,'SALESMAN’,7698,to_date(’20-2-1991′,’dd-mm-yyyy’),1600,300,30);
INSERT INTO EMP VALUES
(20,’TURNER’,'SALESMAN’,7698,to_date(’8-9-1991′,’dd-mm-yyyy’),1500,0,30);
INSERT INTO EMP VALUES
(21,’ADAMS’,'CLERK’,7788,to_date(’13-JUL-1997′)-51,1100,NULL,20);
INSERT INTO EMP VALUES
(22,’JAMES’,'CLERK’,7698,to_date(’3-12-1991′,’dd-mm-yyyy’),950,NULL,30);
INSERT INTO EMP VALUES
(23,’FORD’,'ANALYST’,7566,to_date(’3-12-1991′,’dd-mm-yyyy’),3000,NULL,20);
INSERT INTO EMP VALUES
(24,’MILLER’,'CLERK’,7782,to_date(’23-1-1992′,’dd-mm-yyyy’),1300,NULL,10);
INSERT INTO EMP VALUES
(25,’SMITH’,'CLERK’,7902,to_date(’17-12-1990′,’dd-mm-yyyy’),800,NULL,20);

lock_emp.sql — Locks the common table.

prompt Performance Lock Script
prompt
prompt Starts with: delete from scott.emp;
delete from scott.emp;
Prompt
prompt Now start the other SQL*PLus sessions
prompt
prompt Ready for take off?
prompt A rollback statement will be issued and the locks on the rows are freed
pause
rollback;
col syscol format a20 heading “Start Time of Test”
select to_char(sysdate, ‘YYYYMMDD HH24MISS’) syscol from dual;

emp_dml.sql — Fires a heavy select statement and does an insert of 5000 rows in the emp table. Copy the emp table to the emp_org table once (With 25 rows) for the tests!

set lin 300 verify off trimspool on feedback off
col spoolfile noprint new_value _spoolfile
select ‘log/’||INSTANCE_NAME||’_'||to_char(25+1 + &1.*5000)||’_tot_’||to_char(25+5000 + &1.*5000)||’.txt’ spoolfile from sys.v_$instance;
spool &_spoolfile
update emp
set sal = sal + 1
where job = ‘PRESIDENT’;
rollback;
set timing on
select count(*) COUNT_1A from emp_org a, emp_org b, emp_org c, emp_org d, emp_org e
;
begin
for n in 1 .. 5000 loop
INSERT INTO EMP VALUES (n+25 + &1.*5000,’SMITH’,'CLERK’,7902, trunc(sysdate -36500),800,NULL,20);
update emp set EMPNO = n+25 + &1.*5000 where EMPNO = n+25 + &1.*5000;
end loop;
end;
/
commit;
spool off
exit;

The kick_off_.sh shell scripts looked like:

#!/bin/bash
export ORACLE_HOME=/u00/sw1/app/oracle/product/102
PATH=$ORACLE_HOME/bin:.

exec sqlplus -s scott/tiger@acor @emp_dml 1 &
exec sqlplus -s scott/tiger@acor @emp_dml 2 &
exec sqlplus -s scott/tiger@acor @emp_dml 3 &
exec sqlplus -s scott/tiger@acor @emp_dml 4 &
exec sqlplus -s scott/tiger@acor @emp_dml 5 &
exec sqlplus -s scott/tiger@acor @emp_dml 6 &
## etc. etc. etc. up to the number of concurrent sessions you wanted to test.

Test results:

The elapsed time is mentioned in seconds.

We got the best scalability on the 2 node RAC database which was running on the 4 CPU systems (linux1 and linux2). Our test on the 4 node RAC database with respectively 4,4,2 and 1 CPU’s showd that for 400 concurrent users, the single and dual CPU systems were causing the slowdown. The load balancer gives these system more processes because default the single and dual CPU systems have less Unix processes running on the server. Between a 4 CPU and single CPU system you can have easily a difference of 50 Unix processes.

We did not test the performance by tweaking the load balancer.

We can however conclude that the 2 Node RAC cluster with 4 CPU’s gives a very scalable solution. It can handle twice as many transactions as a single node 4 CPU system during heavy (400 concurrent users) load.

Best practice: To prevent load balancing mismatches it is the easiest to ask for servers with the same specification. Because of the fact that the interconnect traffic is CPU intensive you should not use single CPU servers in a RAC cluster.

Popularity: 394 points