|
Real Application Cluster
|
Hello everyone. In this issue, I start a new topic, Real Application Cluster (RAC).
Background
What is an advantage of constructing a new system in RAC database or moving
the system on to RAC?
Generally, RAC has following advantages:
1. High Availability
When a problem occurs in a cluster system with Hot/Cold standby system,
we need to stop a service to swicth. If we use RAC database,
we don't need to stop anything.
2. Scalability
By adding a node to a cluster, we are able to increase processing capabilities of entire
database.
Scalability is enabled by using Inter Connect, which is different from Oracle
Parallel Server (OPS).
In other words, RAC database easily enables resolving a system-related problem
without stopping a service and increasing the processing capabilities to
deal with the increase in the number of users.
Scalable processing capability is tricky. If you move an existing application
from single instance to RAC, scalability will drop below 1.
Scalability=1 indicates that the performance is same as the one operated in a single node.
This means that performance is slower than a single instance.
In this issue, I focus on scalability and find a factor that changes scalability.
I use two Linux environments to analyze RAC database.
Processing capability of RAC
Now, I actually examine the processing capability.
First, I create a table.
SQL> CREATE TABLE TEST01
( C1 NUMBER
,C2 VARCHAR2(100));
SQL> CREATE INDEX IDX_TEST01
ON TEST01(C1);
|
I get EPS value by creating, updating, and selecting the data in this table.
Execution Per Second (EPS):indicates number of SQL statements executed per second.
This value is used to determine the relative evaluation of processing capability.
Next, I get a value that can be used as a standard of evaluation.
I execute INSERT/UPDATE/SELECT from a single session in a single node.(See the end of the
content for scripts.)
EPS values are as follows:
Analysis 1
NODE1 EPS
----------------------
INSERT 274
UPDATE 260
SELECT 239
|
Next, I execute INSERT/UPDATE/SELECT from two sessions in a single node.
Analysis 2
NODE1 / NODE1 EPS
------------------------
INSERT/INSERT 350
UPDATE/UPDATE 590
SELECT/SELECT 242
|
When compared with Analysis 1, performance seems to increase in Analysis 2.
Regarding UPDATE process, however, EPS value is affected by disk I/O and that is why
EPS of UPDATE process in Analysis 1 is smaller than the one in Analysis 2.
Now, I execute INSERT/UPDATE/SELECT from a single session each in two nodes.
Analysis 3
NODE1 / NODE2 EPS(NODE1) EPS(NODE2)
------------------------------------------------------------
INSERT/INSERT 95 to 208 92 to 211 (not stable)
UPDATE/UPDATE 17 to 160 11 to 164 (decrease gradually)
SELECT/SELECT 238 239
NODE1 / NODE2 EPS(TOTAL)
-----------------------------
INSERT/INSERT 187 to 419
UPDATE/UPDATE 28 to 324
SELECT/SELECT 477
|
Regarding SELECT, performance seems to increase.
Regarding INSERT and UPDATE, on the other hand, results are not stable.
Next time, I will further talk about this issue.
Reference
//============<>=================
require 'dboralib.slb'
global i
global csr={''}
connect darling/darling
if DBERR != 0
print('connect error=[${DBERR}]',stdout)
exit
endif
print('start=[${TIME}]',stdout)
loop i=0;i<50000;i++
if i%100 == 0
print('select count=[${i}]',stdout)
endif
sql select c1 ,c2 from test01 where c1 = :i;
if DBERR != 0
print('select error=[${DBERR}]',stdout)
exit
endif
csr = fetch(0)
endloop
print('end=[${TIME}]',stdout)
exit
//============<>=================
|
Tadashi Yamashita
|
|
Ora! Ora! Oracle