나그네소
Goldilocks Cluster 운영 가이드 본문
[Goldilocks Cluster 운영 Guide]
[Goldilocks Cluster 운영 Guide (command)]
| << clsuter table create >> |
DROP TABLE IF EXISTS CDRLOAD;
CREATE TABLE CDRLOAD
(
CREATE_TIME char(9),
FILE_NAME char(10),
TASK_NUM smallint,
RECORD_SEQ smallint,
NODE_NUM smallint,
SERVICE_ID char(10),
CDR_RECORD char(200),
primary key (CREATE_TIME, FILE_NAME, TASK_NUM, RECORD_SEQ, NODE_NUM)
)
SHARDING BY RANGE (NODE_NUM)
SHARD s1 VALUES LESS THAN ( 2 ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( 3 ) AT CLUSTER GROUP g2,
SHARD s3 VALUES LESS THAN ( 4 ) AT CLUSTER GROUP g3,
SHARD s4 VALUES LESS THAN ( 5 ) AT CLUSTER GROUP g4,
SHARD s5 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g1
;
CREATE TABLE FILEAUDIT
(
CREATE_TIME char(9),
FILE_NAME char(10),
PROCESS_TYPE char(2), -- LO, RT, CL
NODE_NUM smallint,
TASK_NUM smallint,
START_WORK_TIME char(16),
END_WORK_TIME char(16),
RECODE_COUNT smallint,
primary key (CREATE_TIME, FILE_NAME, PROCESS_TYPE, NODE_NUM, TASK_NUM)
) sharding by (file_name);
CREATE TABLE DEDUCTION
(
SERVICE_ID char(10),
NODE_NUM smallint,
AMOUT number(15,3),
primary key (SERVICE_ID, NODE_NUM)
)
SHARDING BY RANGE (NODE_NUM)
SHARD s1 VALUES LESS THAN ( 2 ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( 3 ) AT CLUSTER GROUP g2,
SHARD s3 VALUES LESS THAN ( 4 ) AT CLUSTER GROUP g3,
SHARD s4 VALUES LESS THAN ( 5 ) AT CLUSTER GROUP g4,
SHARD s5 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g1
;
CREATE TABLE BUC
(
SERVICE_ID char(10),
AMOUT number(15,3),
primary key (SERVICE_ID)
) sharding by (service_id);
CREATE TABLE CUST_PROD
(
SERVICE_ID char(10),
PROD_ID char(10),
primary key (SERVICE_ID, PROD_ID)
) sharding by (service_id);
CREATE TABLE CUST_EXTRNID
(
EXTERNID char(30),
EXTERNID_TYPE char(1),
SERVICE_ID char(10),
primary key (EXTERNID, EXTERNID_TYPE)
) sharding by (service_id);
CREATE TABLE TEST_01 (
C1 INTEGER PRIMARY KEY,
C2 VARCHAR(100) ,
C3 VARCHAR(100) ,
C4 VARCHAR(100) ,
C5 VARCHAR(100) ,
C6 VARCHAR(100) ,
C7 VARCHAR(100) ,
C8 VARCHAR(100) ,
C9 INT,
C10 INT
) sharding by hash(c1)
SHARD COUNT 256;
<< range sql >>
--# result: success
DROP TABLE IF EXISTS t1;
COMMIT;
--# result: success
CREATE TABLE t1
(
c1 INTEGER
)
SHARDING BY RANGE (c1)
AT CLUSTER WIDE
SHARD s1 VALUES LESS THAN ( 100 ),
SHARD s2 VALUES LESS THAN ( 400 ),
SHARD s3 VALUES LESS THAN ( 300 ),
SHARD s4 VALUES LESS THAN ( MAXVALUE ),
SHARD s5 VALUES LESS THAN ( 200 )
;
--# result: 5 rows
--# 0 S1 ( 100 )
--# 1 S5 ( 200 )
--# 2 S3 ( 300 )
--# 3 S2 ( 400 )
--# 4 S4 ( MAXVALUE )
SELECT shard_no,
shard_name,
value_string
FROM DEFINITION_SCHEMA.SHARD_RANGE AS shd,
DEFINITION_SCHEMA.TABLES AS tab
WHERE tab.TABLE_NAME = 'T1'
AND tab.TABLE_ID = shd.TABLE_ID
ORDER BY 1;
--# result: success
DROP TABLE IF EXISTS t1;
COMMIT;
--# result: success
CREATE TABLE t1
(
c1 VARCHAR(10)
)
SHARDING BY RANGE (c1)
SHARD s1 VALUES LESS THAN ( 'a' ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( 'd' ) AT CLUSTER GROUP g2,
SHARD s3 VALUES LESS THAN ( 'b' ) AT CLUSTER GROUP g1,
SHARD s4 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g2,
SHARD s5 VALUES LESS THAN ( 'c' ) AT CLUSTER GROUP g1
;
--# result: 5 rows
--# 0 S1 ( 'a' )
--# 1 S3 ( 'b' )
--# 2 S5 ( 'c' )
--# 3 S2 ( 'b' )
--# 4 S4 ( MAXVALUE )
SELECT shard_no,
shard_name,
value_string
FROM DEFINITION_SCHEMA.SHARD_RANGE AS shd,
DEFINITION_SCHEMA.TABLES AS tab
WHERE tab.TABLE_NAME = 'T1'
AND tab.TABLE_ID = shd.TABLE_ID
ORDER BY 1;
--# result: success
DROP TABLE IF EXISTS t1;
COMMIT;
--# result: success
CREATE TABLE t1
(
c1 VARCHAR(10),
c2 VARCHAR(10),
c3 VARCHAR(10)
)
SHARDING BY RANGE (c1, c2, c3)
SHARD s1 VALUES LESS THAN ( 'A', 'ABCDEF', MAXVALUE ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( 'D', MAXVALUE, MAXVALUE ) AT CLUSTER GROUP g2,
SHARD s3 VALUES LESS THAN ( 'B', 'ABCD', 'EFG' ) AT CLUSTER GROUP g1,
SHARD s4 VALUES LESS THAN ( MAXVALUE, MAXVALUE, MAXVALUE ) AT CLUSTER GROUP g2,
SHARD s5 VALUES LESS THAN ( 'C', 'AB', MAXVALUE ) AT CLUSTER GROUP g1
;
--# result: 5 rows
--# 0 S1 ( 'A', 'ABCDEF', MAXVALUE )
--# 1 S3 ( 'B', 'ABCD', 'EFG' )
--# 2 S5 ( 'C', 'AB', MAXVALUE )
--# 3 S2 ( 'D', MAXVALUE, MAXVALUE )
--# 4 S4 ( MAXVALUE, MAXVALUE, MAXVALUE )
SELECT shard_no,
shard_name,
value_string
FROM DEFINITION_SCHEMA.SHARD_RANGE AS shd,
DEFINITION_SCHEMA.TABLES AS tab
WHERE tab.TABLE_NAME = 'T1'
AND tab.TABLE_ID = shd.TABLE_ID
ORDER BY 1;
--# result: success
DROP TABLE IF EXISTS t1;
COMMIT;
--############################################
--# Execution : Basic INSERT, UPDATE, DELETE, SELECT
--############################################
--# result: success
DROP TABLE IF EXISTS t1;
COMMIT;
--# result: success
CREATE TABLE t1
(
c1 INTEGER,
c2 INTEGER
)
SHARDING BY RANGE (c1)
SHARD s1 VALUES LESS THAN (4) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN (7) AT CLUSTER GROUP g2,
SHARD s3 VALUES LESS THAN (MAXVALUE) AT CLUSTER GROUP g3
;
COMMIT;
--# result: 10 rows
INSERT INTO t1
VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
(6, 6), (7, 7), (8, 8), (9, 9), (NULL, NULL);
COMMIT;
--# result: 3 rows
--# 1 1
--# 2 2
--# 3 3
SELECT * FROM t1@g1 ORDER BY c1;
--# result: 3 rows
--# 4 4
--# 5 5
--# 6 6
SELECT * FROM t1@g2 ORDER BY c1;
--# result: 4 rows
--# 7 7
--# 8 8
--# 9 9
--# null null
SELECT * FROM t1@g3 ORDER BY c1;
--# result: 10 rows
--# 1 1
--# 2 2
--# 3 3
--# 4 4
--# 5 5
--# 6 6
--# 7 7
--# 8 8
--# 9 9
--# null null
SELECT * FROM t1 ORDER BY c1;
--# result: 1 rows
UPDATE t1 SET c2 = c2 + 10 WHERE c2 = 2;
--# result: 1 rows
UPDATE t1 SET c2 = c2 + 10 WHERE c2 = 6;
--# result: 1 rows
UPDATE t1 SET c2 = c2 + 10 WHERE c2 = 7;
COMMIT;
--# result: 10 rows
--# 1 1
--# 2 12
--# 3 3
--# 4 4
--# 5 5
--# 6 16
--# 7 17
--# 8 8
--# 9 9
--# null null
SELECT * FROM t1 ORDER BY c1;
--# result: 1 rows
DELETE FROM t1 WHERE c1 = 1;
--# result: 1 rows
DELETE FROM t1 WHERE c1 = 5;
--# result: 1 rows
DELETE FROM t1 WHERE c1 IS NULL;
COMMIT;
--# result: 7 rows
--# 2 12
--# 3 3
--# 4 4
--# 6 16
--# 7 17
--# 8 8
--# 9 9
SELECT * FROM t1 ORDER BY c1;
--# result: success
DROP TABLE t1;
COMMIT;
[ sharding key ]
1. sharding key(range)
DROP TABLE IF EXISTS CDRLOAD;
CREATE TABLE CDRLOAD
(
CREATE_TIME char(9),
FILE_NAME char(10),
TASK_NUM smallint,
RECORD_SEQ smallint,
NODE_NUM smallint,
SERVICE_ID char(10),
CDR_RECORD char(200),
primary key (CREATE_TIME, FILE_NAME, TASK_NUM, RECORD_SEQ, NODE_NUM)
)
SHARDING BY RANGE (NODE_NUM)
SHARD s1 VALUES LESS THAN ( 10 ) AT CLUSTER GROUP g1,
SHARD s5 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g2;
insert into CDRLOAD values(6,6,6,6,6,6,6);
insert into CDRLOAD values(10,10,10,10,10,10,10);
insert into CDRLOAD values(11,11,11,11,11,11,11);
--select * from CDRLOAD@local;
CREATE TABLE TB_A_BEREGIST_FNGPRT_SPPNT
(
FNGPRT_INFO_NO NUMBER ( 38) NOT NULL,
BEREGIST_SN NUMBER ( 38) NOT NULL,
IHIDNUM VARCHAR( 82),
NM VARCHAR( 80),
BRTHDY VARCHAR( 8),
REGIST_TTMAGE NUMBER,
SEXDSTN_DSCD VARCHAR( 1),
NLTY_DSCD VARCHAR( 2),
NAT_DSCD VARCHAR( 3),
RECPTAREA_DSCD VARCHAR( 2),
SPPNT_CO NUMBER ( 5),
SPPNT_MG NUMBER ( 10),
SPPNT_SCORE NUMBER ( 3),
SPPNT VARCHAR(4000),
DIST_AUDIT_PNTTM DATE,
SPPNT_SN NUMBER ( 38) NOT NULL,
DIST_MPU_NO NUMBER ( 5) NOT NULL,
CONSTRAINT UK_TB_A_BEREGIST_FNGPRT_SPPNT UNIQUE(SPPNT_SN,FNGPRT_INFO_NO, BEREGIST_SN,DIST_MPU_NO),
PRIMARY KEY (FNGPRT_INFO_NO, BEREGIST_SN,DIST_MPU_NO) INDEX PK_TB_A_BEREGIST_FNGPRT_SPPNT TABLESPACE TS_IFINGER
)
SHARDING BY RANGE (DIST_MPU_NO)
SHARD s1 VALUES LESS THAN ( 10 ) AT CLUSTER GROUP g1,
SHARD s5 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g2
TABLESPACE TS_DFINGER;
CREATE TABLE TB_A_BEREGIST_FNGPRT_SPPNT
(
FNGPRT_INFO_NO NUMBER ( 38) NOT NULL,
BEREGIST_SN NUMBER ( 38) NOT NULL,
IHIDNUM VARCHAR( 82),
NM VARCHAR( 80),
BRTHDY VARCHAR( 8),
REGIST_TTMAGE NUMBER,
SEXDSTN_DSCD VARCHAR( 1),
NLTY_DSCD VARCHAR( 2),
NAT_DSCD VARCHAR( 3),
RECPTAREA_DSCD VARCHAR( 2),
SPPNT_CO NUMBER ( 5),
SPPNT_MG NUMBER ( 10),
SPPNT_SCORE NUMBER ( 3),
SPPNT VARCHAR(4000),
DIST_AUDIT_PNTTM DATE,
SPPNT_SN NUMBER ( 38) NOT NULL,
DIST_MPU_NO NUMBER ( 5) NOT NULL,
CONSTRAINT UK_TB_A_BEREGIST_FNGPRT_SPPNT UNIQUE(SPPNT_SN,DIST_MPU_NO),
PRIMARY KEY (FNGPRT_INFO_NO, BEREGIST_SN,DIST_MPU_NO) INDEX PK_TB_A_BEREGIST_FNGPRT_SPPNT TABLESPACE TS_IFINGER
)
SHARDING BY RANGE (DIST_MPU_NO)
SHARD s1 VALUES LESS THAN ( 2 ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g2;
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(2,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,2,2);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(3,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,3,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(4,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,4,2);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(5,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,5,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(6,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,6,2);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(7,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,7,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(8,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,8,2);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(9,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,9,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(10,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,10,2);
2. hash
CREATE TABLE CUST_PROD
(
SERVICE_ID char(10),
PROD_ID char(10),
primary key (SERVICE_ID, PROD_ID)
) sharding by (service_id);
insert into CUST_PROD values(1,1);
insert into CUST_PROD values(2,1);
insert into CUST_PROD values(3,1);
insert into CUST_PROD values(4,1);
insert into CUST_PROD values(5,1);
insert into CUST_PROD values(6,1);
insert into CUST_PROD values(7,1);
insert into CUST_PROD values(8,1);
insert into CUST_PROD values(9,1);
-- select * from cust_prod@local;
CREATE TABLE TB_A_BEREGIST_FNGPRT_SPPNT
(
FNGPRT_INFO_NO NUMBER ( 38) NOT NULL,
BEREGIST_SN NUMBER ( 38) NOT NULL,
IHIDNUM VARCHAR( 82),
NM VARCHAR( 80),
BRTHDY VARCHAR( 8),
REGIST_TTMAGE NUMBER,
SEXDSTN_DSCD VARCHAR( 1),
NLTY_DSCD VARCHAR( 2),
NAT_DSCD VARCHAR( 3),
RECPTAREA_DSCD VARCHAR( 2),
SPPNT_CO NUMBER ( 5),
SPPNT_MG NUMBER ( 10),
SPPNT_SCORE NUMBER ( 3),
SPPNT VARCHAR(4000),
DIST_AUDIT_PNTTM DATE,
SPPNT_SN NUMBER ( 38) NOT NULL,
DIST_MPU_NO NUMBER ( 5) NOT NULL,
CONSTRAINT UK_TB_A_BEREGIST_FNGPRT_SPPNT UNIQUE(SPPNT_SN,DIST_MPU_NO),
PRIMARY KEY (FNGPRT_INFO_NO, BEREGIST_SN,DIST_MPU_NO) INDEX PK_TB_A_BEREGIST_FNGPRT_SPPNT TABLESPACE TS_IFINGER
)
SHARDING BY (DIST_MPU_NO)
TABLESPACE TS_DFINGER;
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,1);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,2);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,3);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,4);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,5);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,6);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,7);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,8);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,9);
insert into TB_A_BEREGIST_FNGPRT_SPPNT values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,sysdate,1,10);
3. cloned
한번 테이블을 생성 후 데이터가 변하지 않는 테이블은 cluster 양쪽에 동일한 데이터를 가지고 있는 것이
좋기 때문에 위와 같은 환경일 때 사용하기 위하여 cloned table을 두었다.
사용방법 : create table t3 (c1 int) cloned;
위와 같이 하면 양쪽에 동일한 데이터를 가지고 있다.
4. list table
CREATE TABLE t1 ( city VARCHAR(128) )
SHARDING BY LIST (city)
SHARD s1 VALUES IN ( '서울' ) AT CLUSTER GROUP g1,
SHARD s2 VALUES IN ( '부산', '울산', '대구' ) AT CLUSTER GROUP g2,
SHARD s3 VALUES IN ( DEFAULT ) AT CLUSTER GROUP g2;
insert into t1 values('서울');
insert into t1 values('울산');
insert into t1 values('인제');
<< 참고 SQL >>
gSQL> select * from x$cluster_member@g1n1;
MEMBER_ID MEMBER_POSITION LOGICAL_CONNECTION PHYSICAL_CONNECTION SERIAL IS_GLOBAL_COORD IS_DOMAIN_COORD LOCAL_SCN AGABLE_STMT_SCN
--------- --------------- ------------------ ------------------- ------ --------------- --------------- --------- ---------------
1 0 ACTIVE ACTIVE 2 TRUE TRUE 477.0.10 477.0.10
2 1 ACTIVE ACTIVE 1 FALSE TRUE 477.0.567 477.0.0
2 rows selected.
--> cluster 관계가 정상 적으로 설정 되어 있는지 확인.
gSQL> select * from x$cluster_member;
GROUP_ID MEMBER_ID MEMBER_NAME MEMBER_POSITION IS_LEFT CREATED_TIME MODIFIED_TIME COMMENTS
-------- --------- ----------- --------------- ------- -------------------------- -------------------------- --------
1 1 G1N1 0 FALSE 2016-08-25 19:40:10.470000 2016-08-25 19:40:10.470000 null
2 2 G2N1 1 FALSE 2016-08-25 19:40:12.810000 2016-08-25 19:40:12.810000 null
1 1 G1N1 0 FALSE 2016-08-25 19:40:10.470000 2016-08-25 19:40:10.470000 null
2 2 G2N1 1 FALSE 2016-08-25 19:40:12.810000 2016-08-25 19:40:12.810000 null
4 rows selected.
--> 맴버 구성 확인
gSQL> select * from x$instance@local;
VERSION STARTUP_TIME STATUS IS_CLUSTER LOCAL_GROUP_ID LOCAL_MEMBER_ID LOCAL_MEMBER_POSITION
----------------------------------- -------------------------- ------ ---------- -------------- --------------- ---------------------
Release Trunk.3.1.0 revision(19287) 2016-08-25 19:40:01.636542 OPEN TRUE 1 1 0
1 row selected.
--> 현재의 instance 상태 확인
gSQL> select local_member_name() from dual;
LOCAL_MEMBER_NAME()
-------------------
G1N1
--> 현재 local member 확인
| << group 삭제 >> |
group에 있는 데이터를 다른 Group으로 다 이동한 후 삭제를 진행 할 수 있다.
Cluster group 을 제거하기 위해서는 cluster group 제거로 인한 data loss 가 발생하지 않아야 한다.
다음과 같은 구문을 통해 cluster group 을 제거하기 위해서는 해당 cluster group에 shard가 존재하지 않아야 한다.
gSQL> ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP g3;
[참고]
| gSQL> alter table t2 rebalance exclude cluster group g2; ERR-42000(16445): REBALANCE EXCLUDE can be executed only on cluster wide sharded tables : alter table t2 rebalance exclude cluster group g2 -> type이 range, list로 되어 있는 경우에는 위와 같은 오류가 발생 한다. 해당 타입일 때에는 shard move 한 후 group을 삭제 하는 방식으로 진행을 하면 된다. setp1. move shard gSQL> alter table t1 move shard S_DEFAULT to cluster group g1; Table altered. step2. gSQL> select * from user_tab_shards; TABLE_SCHEMA TABLE_NAME SHARD_STRATEGY SHARD_NAME SHARD_NUMBER SHARD_DEFINITION GROUP_ID GROUP_NAME ------------ ---------- -------------- ------------ ------------ --------------------------- -------- ---------- PUBLIC T1 RANGE SHARDING S1 0 VALUES LESS THAN (10) 1 G1 PUBLIC T1 RANGE SHARDING S_DEFAULT 1 VALUES LESS THAN (MAXVALUE) 1 G1 PUBLIC T2 HASH SHARDING SHARD_000000 0 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000001 1 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000002 2 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000003 3 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000004 4 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000005 5 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000006 6 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000007 7 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000008 8 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000009 9 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000010 10 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000011 11 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000012 12 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000013 13 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000014 14 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000015 15 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000016 16 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000017 17 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000018 18 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000019 19 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000020 20 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000021 21 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000022 22 null 1 G1 PUBLIC T2 HASH SHARDING SHARD_000023 23 null 1 G1 step3. group 삭제 gSQL> drop cluster group g2; Cluster Group dropped. 참고] create table t1(c1 int primary key, c2 int) sharding by range(c1) at cluster wide shard s1 values less than(10), shard s2 values less than(maxvalue); gSQL> alter table t1 rebalance exclude cluster group g2; Table altered. --> 위와 같이 cluster wide로 생성 되어 있다면 오류가 발생 하지 않는다. |
gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;
참고 : Table FALSE 처리 ( alter database offline inactive cluster members;)
gSQL> DROP CLUSTER GROUP g3;
group을 삭제 하기 위해서는 group의 memeber가 모두 살아 있는 상태에서 shrad move 하거나
아니면 hash일 경우 위의 특정 cluster group에 shard를 포함하지 않도록 모든 테이블의 shard 를
재배치한후 group을 삭제 할 수 있다.
참고 : 삭제하려는 group이 in-active 상태이면 삭제 할 수 없다.
| << table sharding key 조회 >> |
gSQL> select * from user_shard_key_columns;
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------ ---------- ----------- ---------------
PUBLIC T1 C1 1
PUBLIC T2 C1 1
| << tablespace 개별 추가 방법 >> |
ALTER TABLESPACE TEST_TBS ADD DATAFILE
'TEST_TBS_G2.dbf' SIZE 10M AT G2;
--> G2 group으로 주면 group전체, 그리고 맴버로 주면 맴버로만 추가가 가능하다.
| << disconnect >> |
alter system disconnect session 0, 4,13;
0은 member position을 의미합니다.
| << cluster member 추가 >> |
Cluster group 에 cluster member 를 추가한다.
add cluster member definition 구문은 table 들의 shard 를 재배치하지 않는다.
추가된 cluster member 에 shard 를 재배치하기 위해서는 다음 구문을 수행해야 한다.
<alter database rebalance statement>
<alter table rebalance statement>
사용예 (Examples)
다음은 두 개의 cluster member 를 cluster group 에 추가한 예이다.
ALTER CLUSTER GROUP g1 ADD
CLUSTER MEMBER g1n3 HOST '192.168.0.13' PORT 10103,
CLUSTER MEMBER g1n4 HOST '192.168.0.14' PORT 10104 ;
| << database rebalance >> |
모든 table 들의 shard 를 재배치한다.
[구문 규칙 및 파라미터]
ONLINE | OFFLINE
테이블의 shard 재배치시 DML 을 허용할 지 여부를 결정한다.
' ONLINE
INSERT, UPDATE, DELETE 를 허용한다.
' OFFLINE
INSERT, UPDATE, DELETE 를 허용하지 않는다.
생략할 경우, 기본값은 ONLINE 이다.
GLOBAL MOUNT 단계에서는 ONLINE으로 지정하더라도 OFFLINE으로 동작한다.
[설명]
다음과 같은 구문을 통해 cluster member, cluster group 추가시 table 들의 shard를 재배치하지
않는다. 추가된 cluster group 과 cluster member 에 대해 재배치하지 않은 모든 테이블들의 shard를
재배치하기 위해 <alter database rebalance statement> 구문을 수행한다.
[example]
ALTER DATABASE REBALANCE;
| << member ip & port 변경 >> |
[usage]
ALTER CLUSTER LOCATION g1n1 HOST '192.168.0.21' PORT 10201;
ALTER CLUSTER LOCATION g1n1 HOST '192.168.0.22' PORT 10201;
ALTER CLUSTER LOCATION g1n1 PORT 10202;
[example]
gSQL> ALTER CLUSTER LOCATION g1n1 HOST '10.10.10.3';
altered.
Elapsed time: 50.54900 ms
gSQL> select * from x$cluster_location;
MEMBER_NAME HOST PORT
----------- ------------- -----
G1N1 10.10.10.3 10101
G2N1 192.168.0.221 10101
G3N1 192.168.0.220 10101
G1N1 10.10.10.3 10101
G2N1 192.168.0.221 10101
G3N1 192.168.0.220 10101
G1N1 10.10.10.3 10101
G2N1 192.168.0.221 10101
G3N1 192.168.0.220 10101
9 rows selected.
-> location 변경은 local open 단계 에서 해야 되고 만약 gloabl open 단계에서 했으면 변경은 되나 restart 해야 된다.
(local open단계에서 하였을 때 모든 member에 적용을 하여야 한다.)
| << drop cluster location statement >> |
Cluster system 에 참여하는 Member 의 Location 정보를 삭제한다.
다음은 cluster member g1n1 에 대해 location 정보를 삭제한 예이다.
DROP CLUSTER LOCATION g1n1;
| << gserver member kill 하였을 때 new member 생성 >> |
1. g1n2
shutdown abort
2. g1n1 member 삭제
gSQL> select * from x$cluster_member@local;
MEMBER_ID MEMBER_POSITION STATUS SERIAL
--------- --------------- -------- ------
1 0 ACTIVE 4
2 1 ACTIVE 1
3 2 INACTIVE 2
4 3 ACTIVE 1
alter database drop inactive cluster members;
gSQL> select * from x$cluster_member@local;
MEMBER_ID MEMBER_POSITION STATUS SERIAL
--------- --------------- ------ ------
1 0 ACTIVE 4
2 1 ACTIVE 1
4 3 ACTIVE 1
3. g1n2 관련 data 파일 삭제
4. g1n2 create
gcreatedb --cluster --home=$SUNDB_DATA/g1n2_home --member=g1n2 --host=10.10.10.2 --port=10102
5. g1n2 open & mount
gSQL> \cstartup local open
gSQL> alter system mount global database;
6. member 추가 (g1n1)
alter cluster group g1 add cluster member g1n2 host '10.10.10.2' port 10102;
SQL> select * from x$instance;
VERSION STARTUP_TIME STATUS
----------------------------------- -------------------------- --------------
IS_CLUSTER LOCAL_GROUP_ID LOCAL_MEMBER_ID LOCAL_MEMBER_POSITION
---------- -------------- --------------- ---------------------
Release Trunk.2.3.0 revision(16235) 2015-10-21 15:40:58.806381 OPEN
TRUE 1 1 0
Release Trunk.2.3.0 revision(16235) 2015-10-21 15:40:29.759124 OPEN
TRUE 2 4 3
Release Trunk.2.3.0 revision(16235) 2015-10-21 15:46:22.464416 OPEN
TRUE 2 2 1
Release Trunk.2.3.0 revision(16235) 2015-10-21 15:53:22.746189 GLOBAL MOUNTED
TRUE 1 5 2
4 rows selected.
7. g1n2 open 처리
alter system open global database at g1n2;
8. rebalance 처리
alter database rebalance;
| << group kill 하였을 경우 group 추가 >> |
1. g2n1 (group kill)
shutdown abort
2. g2n1 member delete(g1n1)
alter database drop inactive cluster members;
3. g1n2 관련 data 파일 삭제
-- 파일 삭제 처리
(/g1n2_home/db,/g1n2_home/wal)
4. g1n2 create
gcreatedb --cluster --home=$SUNDB_DATA/g2n1_home --member=g2n1 --host=10.10.10.2 --port=10201
5. g1n2 open & mount
gSQL> \cstartup local open
6. member 추가 (g1n1)
alter cluster group g2 add cluster member g2n1 host '10.10.10.2' port 10201;
7. g2n1 open 처리
alter system open global database at g2n1;
8. rebalance 처리
alter database rebalance;
| << alter database drop inactive cluster members statement >> |
2by1 환경에서 group2가 비정상 종료 하였을 경우 inactive cluster 를 제거하여 정상 처리 할 경우 사용한다.
(비활성화된 inactive cluster member 들을 모두 제거한다,)
gSQL> select * from x$cluster_member@local;
MEMBER_ID MEMBER_POSITION LOGICAL_CONNECTION PHYSICAL_CONNECTION SERIAL IS_GLOBAL_COORD IS_DOMAIN_COORD LOCAL_SCN AGABLE_STMT_SCN
--------- --------------- ------------------ ------------------- ------ --------------- --------------- --------- ---------------
1 0 ACTIVE ACTIVE 2 TRUE TRUE 1.1.11 1.1.11
2 1 INACTIVE INACTIVE 3 FALSE TRUE -1.-1.-1 -1.-1.-1
gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;
gSQL> select * from x$instance@local;
VERSION STARTUP_TIME STATUS OS_USER_ID IS_CLUSTER LOCAL_GROUP_ID LOCAL_MEMBER_ID LOCAL_MEMBER_NAME LOCAL_MEMBER_POSITION
--------------------------------- -------------------------- ------ ---------- ---------- -------------- --------------- ----------------- ---------------------
Debug Trunk.3.1.0 revision(19973) 2016-11-04 22:14:30.557448 OPEN 503 TRUE 1 1 G1N1 0
1 row selected.
--> 정상 처리 된다
gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;
RR-42000(16361): sharded table "PUBLIC"."T1" must be accessible to at least one member of group 'G3'
--> 테이블이 없으면 정상 적으로 잘종료 되나 테이블을 생성 하고 삭제시에는 위와 같은
오류가 발생 한다. 위의 결과는 정상 적이다. group이 2개 였을 경우 하나의 group이 비정상
종료 하면 데이터를 어떻게 할 수 없기 때문에 위의 오류가 발생 하는 것은 정상 적인 상황
이다. member를 새로 생성 하여 처리 하지 않는다 현재의 inactive의 상태를 제거할 방법은
없다. 다만 group이 crach 되어도 정상 적으로 다른 group은 local로 정상 처리 할 수 있다.
다만 DDL 작업은 진행 할 수 없다.
| << alter database join inactive cluster member statement >> |
비활성화된 inactive cluster member 들 중 global mount 단계에 있는 cluster member 들을 cluster system 에 다시 포함한다.
group2등 비정상 종료 하여 inactive 상태를 다시 active 상태로 바꿀라고 할 때 사용한다.
1. g2n1 kill
shutdown abort
2. g2n1 start
\startup
3. g2n1 join
alter system join database;
| << alter table rebalance statement >> |
테이블별 shared를 재배치 한다.
ALTER TABLE t1 REBALANCE;
| << 3by1 에서 2개의 server shutdown >> |
3by1에서 group1, group2 stop 하고 start 처리 하는 방법
1. group1 shutdown
2. group2 shutdown
3. group3 search
gSQL> select * from x$cluster_member@local;
MEMBER_ID MEMBER_POSITION LOGICAL_CONNECTION PHYSICAL_CONNECTION SERIAL IS_GLOBAL_COORD IS_DOMAIN_COORD LOCAL_SCN AGABLE_STMT_SCN
--------- --------------- ------------------ ------------------- ------ --------------- --------------- --------- ---------------
1 0 INACTIVE INACTIVE 3 FALSE TRUE -1.-1.-1 -1.-1.-1
2 1 INACTIVE INACTIVE 4 FALSE TRUE -1.-1.-1 -1.-1.-1
3 2 ACTIVE ACTIVE 4 TRUE TRUE 2.1.877 2.1.877
---> group1, group2 INACTIVE 상태
4. group 1 startup
\startup
- 상태
gSQL> select * from x$instance@local;
VERSION STARTUP_TIME STATUS OS_USER_ID IS_CLUSTER LOCAL_GROUP_ID LOCAL_MEMBER_ID LOCAL_MEMBER_NAME LOCAL_MEMBER_POSITION
--------------------------------- -------------------------- ---------- ---------- ---------- -------------- --------------- ----------------- ---------------------
Debug Trunk.3.1.0 revision(19973) 2016-11-05 17:10:41.041910 LOCAL OPEN 507 TRUE 2 2 G2N1 1
1 row selected.
5. group 2 startup
\startup
6. group1 join
gSQL> alter system join database;
System altered.
7. group2 join
gSQL> alter system join database;
System altered.
8. 상태
gSQL> select * from x$cluster_member@local;
MEMBER_ID MEMBER_POSITION LOGICAL_CONNECTION PHYSICAL_CONNECTION SERIAL IS_GLOBAL_COORD IS_DOMAIN_COORD LOCAL_SCN AGABLE_STMT_SCN
--------- --------------- ------------------ ------------------- ------ --------------- --------------- --------- ---------------
1 0 ACTIVE ACTIVE 4 FALSE TRUE 3.1.12 3.2.0
2 1 ACTIVE ACTIVE 5 FALSE TRUE 3.0.878 3.2.0
3 2 ACTIVE ACTIVE 8 TRUE TRUE 3.2.877 3.2.877
3 rows selected.
참고 : join database 처리 시 group간 property가 같아야 한다. 같지 않으면 오류가 발생한다.
| << sharding key, 속성 확인 >> |
gSQL> select SHARDING_STRATEGY from DEFINITION_SCHEMA.TABLES where table_name='CUSER';
SHARDING_STRATEGY
-----------------
HASH SHARDING
[shard 배치 정보]
SELECT group_name, COUNT(*) AS shard_count
FROM USER_TAB_SHARDS
WHERE TABLE_NAME = 'T1'
GROUP BY group_name;
- data 분배 정보
SELECT CLUSTER_GROUP_NAME, COUNT(*) AS row_count
FROM t1
GROUP BY CLUSTER_GROUP_NAME;
| << 하나의 머신에서 두개의 instance 하는 방법 >> |
1. 각각의 home direcotry 생성
mkdir $SUNDB_DATA/g1n1_home
mkdir $SUNDB_DATA/g2n1_home
2. g1n1_home-g2n1_home 각각의 conf directory 생성
mkdir archive_log
mkdir backup
mkdir conf
mkdir db
mkdir journal
mkdir trc
mkdir wa
ex)
dev2:/home/son2865/extern_db/product/sundb_data/g1n1_home] ls
archive_log backup conf db journal trc wal
dev2:/home/son2865/extern_db/product/sundb_data/g2n1_home] ls
archive_log backup conf db journal trc wal
3. configure file 생성
3-1) $SUNDB_DATA/g2n1_home/conf/sundb.properties.conf 생성
SHARED_MEMORY_STATIC_KEY = 302100
SHARED_MEMORY_ADDRESS = 10737418240
LOCAL_CLUSTER_MEMBER = 'G2N1'
LOCAL_CLUSTER_MEMBER_HOST = '127.0.0.1'
LOCAL_CLUSTER_MEMBER_PORT = 10201
DATABASE_INSTANCE_NAME = 'G2N1'
3-2) $SUNDB_DATA/g2n1_home/conf/sundb.listener.conf 생성
LISTENER]
LISTEN_PORT = 20201
3-3) $SUNDB_DATA/g1n1_home/conf/sundb.properties.conf 생성
3-4) $SUNDB_DATA/g1n1_home/conf/sundb.listener.conf 생성
[LISTENER]
LISTEN_PORT = 20101
4. .odbc.ini create
vi ~/.odbc.ini
[G1N1]
host=192.168.0.221
db_home=g1n1_home
date_format=yyyymmddhh24miss
[G2N1]
host=192.168.0.221
db_home=g2n1_home
date_format=yyyymmddhh24miss
5. g2n1 create
gcreatedb --cluster --home=$SUNDB_DATA/g2n1_home --member=g2n1 --host=192.168.0.221 --port=10201
gsql sys gliese --as sysdba --no-prompt --dsn=g2n1
\startup
ALTER SYSTEM OPEN GLOBAL DATABASE;
glsnr --start --home=$SUNDB_DATA/g2n1_home
6. g1n1 create
gcreatedb --cluster --home=$SUNDB_DATA/g1n1_home --member=g1n1 --host=192.168.0.221 --port=10101
gsql sys gliese --as sysdba --dsn=g1n1
\startup
ALTER SYSTEM OPEN GLOBAL DATABASE;
glsnr --start --home=$SUNDB_DATA/g1n1_home
create cluster group g1 cluster member g1n1 host '192.168.0.221' port 10101;
create cluster group g2 cluster member g2n1 host '192.168.0.221' port 10201;
gsql sys gliese --as sysdba --dsn=g1n1 --import $SUNDB_HOME/admin/cluster/DictionarySchema.sql
gsql sys gliese --as sysdba --dsn=g1n1 --import $SUNDB_HOME/admin/cluster/InformationSchema.sql
gsql sys gliese --as sysdba --dsn=g1n1 --import $SUNDB_HOME/admin/cluster/PerformanceViewSchema.sql
| << Lange 처리 시 모든 group search >> |
== cdispatcher 네트웍 접근(무)
select min(acct_balance_id) , count(*) from ( select acct_balance_id from acct_balance@g1n1 where acct_balance_id between 1 and 100000 ) ;
== cdispatcher 네트웍 접근(유)
select min(acct_balance_id) , count(*) from ( select acct_balance_id from acct_balance where acct_balance_id between 1 and 100000 ) ;
등호조건만 처리되는 것 같습니다. (현재)
--> lang로 select 처리 시 해당 group에서의 범위만 조건으로 처리 하였는데 between으로 검색 하였을 때 cdispatcher의
cpu가 증가 한것으로 보아 network으로 보내고 있다.
검색 하게 되는 것으로 보인다(2017.05.17 일자 기준)
| << cluster 유효 함수 >> |
- LOCAL_GROUP_ID
사용자 질의를 받아 수행하는 Server에 대한 Cluster Group ID를 반환하는 함수
gSQL> SELECT LOCAL_GROUP_ID() FROM DUAL;
LOCAL_GROUP_ID()
----------------
1
- LOCAL_GROUP_NAME
사용자 질의를 받아 수행하는 Server에 대한 Cluster Group Name을 반환하는 함수
gSQL> SELECT LOCAL_GROUP_NAME() FROM DUAL;
LOCAL_GROUP_NAME()
------------------
G1
- LOCAL_MEMBER_ID
사용자 질의를 받아 수행하는 Server에 대한 Cluster Member ID를 반환하는 함수
gSQL> SELECT LOCAL_MEMBER_ID() FROM DUAL;
LOCAL_MEMBER_ID()
-----------------
1
- LOCAL_MEMBER_NAME
사용자 질의를 받아 수행하는 Server에 대한 Cluster Member Name을 반환하는 함수
gSQL> SELECT LOCAL_MEMBER_NAME() FROM DUAL;
LOCAL_MEMBER_NAME()
-------------------
G1N1
- ROWID_GRID_BLOCK_ID
GRID Block ID를 반환하는 함수
gSQL> SELECT C1, ROWID_GRID_BLOCK_ID( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_ID( ROWID )
-- ----------------------------
1 52
2 52
3 52
- ROWID_GRID_BLOCK_SEQ
GRID Block Sequence를 반환하는 함수
gSQL> SELECT C1, ROWID_GRID_BLOCK_SEQ( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_SEQ( ROWID )
-- -----------------------------
1 747465
2 747466
3 747467
GRID : global record id를 의미 하는데 global index에서 사용하기 위한 key를 말한다.
GRID 구조 : (memberid,member block id, member block sequence) 위와 같은 구조로 되어 있다.
위에서 ROWID_GRID_BLOCK_SEQ GRID에서 member lock sequence를 가져 올 때 사용한다.
- ROWID_MEMBER_ID
해당 Row에 대한 Member ID를 반환하는 함수
gSQL> SELECT C1, ROWID_MEMBER_ID( ROWID ) FROM T1;
C1 ROWID_MEMBER_ID( ROWID )
-- ------------------------
1 1
2 1
3 1
- ROWID_SHARD_ID
해당 Row에 대한 Shard ID를 반환하는 함수
gSQL> SELECT C1, ROWID_SHARD_ID( ROWID ) FROM T1;
C1 ROWID_SHARD_ID( ROWID )
-- -----------------------
1 0
2 1
3 2
- SHARD_GROUP_ID
[구문]
SHARD_GROUP_ID( table_name, shard_key_value [, ... ] )
[설명]
SHARD_GROUP_ID 함수는 table_name에 Shard Strategy가 정의된 경우 shard_key_value 값을 저장할 수 있는
Shard를 관리하는 Group ID를 반환한다.
입력인자 table_name은 identifier로 기술하여야 하며, table_name에 해당하는 객체가 Base Table이 아니거나
Shard Strategy가 정의되지 않은 경우 에러가 발생한다.
입력인자 shard_key_value는 table_name에 정의된 Shard Strategy의 Shard Key Column 순으로 나열되어야 한
다. shard_key_value 개수와 Shard Key Column 개수가 불일치할 경우 에러가 발생한다.
--> row data가 어느 group에 속해 있는지를 확인 할 수 있다. (좋은 정보이다.)
gSQL> select t1.c1 , shard_group_id(t1,t1.c1) from t1;
C1 SHARD_GROUP_ID(T1,T1.C1)
-- ------------------------
90 2
1 1
50 1
30 1
10 1
- SHARD_ID
[구문]
SHARD_ID( table_name, shard_key_value [, ... ] )
[설명]
SHARD_ID 함수는 table_name에 Shard Strategy가 정의된 경우 shard_key_value 값을 저장할 수 있는
Shard에 대한 ID를 반환한다. 입력인자 table_name은 identifier로 기술하여야 하며, table_name에
해당하는 객체가 Base Table이 아니거나 Shard Strategy가 정의되지 않은 경우 에러가 발생한다.
입력인자 shard_key_value는 table_name에 정의된 Shard Strategy의 Shard Key Column 순으로 나열되어야
한다. shard_key_value 개수와 Shard Key Column 개수가 불일치할 경우 에러가 발생한다.
--> 해당 row data가 어떤 shard 규칙에 포함 되어 있는지 알 수 있다.
gSQL> select t1.c1, shard_id(t1,t1.c1) from t1;
C1 SHARD_ID(T1,T1.C1)
-- ------------------
90 18
1 1
50 2
30 6
10 10
- STATEMENT_VIEW_SCN_DCN
현재 STATEMENT의 VIEW SCN 의 DCN(Domain Change Number) 값을 얻는다.
gSQL> SELECT STATEMENT_VIEW_SCN_DCN() FROM dual;
STATEMENT_VIEW_SCN_DCN()
------------------------
658
--> domain scn를 볼 수 있따.
- STATEMENT_VIEW_SCN_GCN
현재 STATEMENT의 VIEW SCN 의 GCN(Global Change Number) 값을 얻는다.
STATEMENT_VIEW_SCN_GCN()
------------------------
17697
--> global scn을 볼 수 있다.
- STATEMENT_VIEW_SCN_LCN
현재 STATEMENT의 VIEW SCN 의 LCN(Local Change Number) 값을 얻는다.
gSQL> SELECT STATEMENT_VIEW_SCN_LCN() FROM dual;
STATEMENT_VIEW_SCN_LCN()
------------------------
17880
--> Local scn을 볼 수 있다.
| << cluster 성능 지연 시 체크 >> |
[x$cluster_queue]
gSQL> select * from x$cluster_queue@local;
NAME ID QUEUED FULL_COUNT WAIT_COUNT TOTALQ
----------------- --- ------ ---------- ---------- -------
MASTER_IN 0 0 0 0 0
MASTER_OUT 0 0 0 0 9302
MASTER_OUT 100 0 0 0 1611319
MASTER_OUT 200 0 0 0 1481908
SYNC_IN 0 0 0 0 0
SYNC_IN 1 0 0 0 0
SYNC_IN 100 0 0 0 0
SYNC_IN 101 0 0 0 0
SYNC_IN 200 0 0 0 0
COMMIT_IN 0 0 0 0 39500
COMMIT_SLAVE_IN 0 0 0 0 0
COORDINATOR_IN 0 0 0 0 39500
URGENT_IN 0 0 0 0 3647
RECOVER_OUT 0 0 0 0 0
LOCATOR_AGENT_OUT 0 0 0 0 0
HEARTBEAT 0 0 0 0 0
- 평상시
MASTER_OUT ID 0 번인것만 TOTALQ가 증가
URGENT_IN TOTALQ가 증가
MASTER_OUT : Insert 처리 시 다른 group에 처리 할 것이 있으면 TOTALQ가 version에
따라 1나 또는 2가 증가.
commit 처시 시 다른 group에 commit 처리 할 것이 있으면 1증가.
MASTER_IN : insert 처리 시 cluster를 통해 다른 group에서 들어 오는 것이 있으면
version에 따라 1 또는 2가 증가.
COMMIT_IN : commit 처리 시 외부 group에서 들어 오는 것이 있으면 totalq 1증가.
SYNC_IN : replica을 통하여 전송 받은 totalq(slave 쪽에서 증가)
위의 4가지 정보를 이용하여 실제 전송 되고 오가는 Transaction을 알 수 있다.
--> local Transaction 처리 시 증가 하지 않는다.
참고]
coordinator,commit cpu 100%를 사용하고 있을 때 정말 cpu가 모자르다면 WAITP_COUNT(누적치)로 계속 증가 하는지
를 확인 할 수 있고 QUEUE는 현재 쌓여 있는 값으로 현재 상황을 모니터링 할 때 사용 할 수 있다.
[x$cl_latency_stat]
select * from X$CL_LATENCY_STAT@local where TOTAL_LATENCY > 0;
gSQL> \desc X$CL_LATENCY_STAT
COLUMN_NAME TYPE IS_NULLABLE
------------------- -------------- -----------
SESSION_ID NATIVE_INTEGER FALSE
COMMAND_TYPE NATIVE_INTEGER FALSE
LOCAL_REQ_QUEUEING NATIVE_INTEGER FALSE
REQ_TRANSMISSION NATIVE_INTEGER FALSE
REMOTE_REQ_QUEUEING NATIVE_INTEGER FALSE
REQ_PROCESSING NATIVE_INTEGER FALSE
REMOTE_RES_QUEUEING NATIVE_INTEGER FALSE
RES_TRANSMISSION NATIVE_INTEGER FALSE
LOCAL_RES_QUEUEING NATIVE_INTEGER FALSE
TOTAL_LATENCY NATIVE_INTEGER FALSE
--> cdispatcher에 패킷 전송 시간 확인
--> 전송 시간이 엄청 많네 (transmission 마이크로초) 요걸로 현재 네트워크에 문제가 있다고 판단할 수 있다.
[X$CONTROLFILE_CLUSTER_MEMBER]
gSQL> select * from X$CONTROLFILE_CLUSTER_MEMBER;
MEMBER_NAME IS_LOCAL_MEMBER GROUP_ID MEMBER_ID MEMBER_POSITION
----------- --------------- -------- --------- ---------------
G1N1 FALSE 1 1 0
G2N1 TRUE 2 2 1
G1N1 TRUE 1 1 0
G2N1 FALSE 2 2 1
4 rows selected.
gSQL> select * from X$CL_LATENCY_STAT@local where TOTAL_LATENCY > 0;
SESSION_ID COMMAND_TYPE REQ_QUEUEING1 REQ_TRANSMISSION REQ_QUEUEING2
---------- ------------ ------------- ---------------- -------------
REQ_PROCESSING RES_QUEUEING1 RES_TRANSMISSION RES_QUEUEING2 TOTAL_LATENCY
-------------- ------------- ---------------- ------------- -------------
4 5 2 58 36
23 1 58 7 186
4 5 48 408 35
23 1 408 29 953
4 5 2 639 36
25 1 639 28 1370
4 5 50 152 35
22 1 152 10 422
4 5 2 1273983 40
26 1 1273983 30 2548065
4 5 1 77 7
--> 전송 시간이 엄청 많네 (transmission 마이크로초) 요걸로 현재 네트워크에 문제가 있다고 판단할 수 있다.
[X$CL_SYSTEM_INFO]
select * from X$CL_SYSTEM_INFO;
gSQL> select * from X$CL_SYSTEM_INFO;
NAME VALUE CATEGORY COMMENTS
-------------------- ----- -------- -----------------------------------------
GLOBAL_COMMIT_COUNT 0 0 globally committed count
CM_BUFFER_MISS_COUNT 0 0 latch miss count on communication buffer
FAILOVER_ENV_ID 46 0 environment identifier of cluster monitor
GLOBAL_COMMIT_COUNT 0 0 globally committed count
CM_BUFFER_MISS_COUNT 0 0 latch miss count on communication buffer
FAILOVER_ENV_ID 46 0 environment identifier of cluster monitor
--> CM_BUFFER_MISS_COUNT : CM Buffer에 데이터를 넣기 위하여 spin lock try 하는데 이에 실패 시 count가
증가 한다. hash type이나 replica에서 전송 시 위의 값이 증가 한다면 성능이 지연 될 수 있다. 그리고
session이 여러개 한꺼번에 접속 시 증가 한다.
--> global commit 조회
[x$cluster_dispatcher]
select * from x$cluster_dispatcher;
| << 2by2 환경에서 2by1 insert shard로 1by1에서 처리 commit 하지 않은 상태 >> |
gSQL> select * from x$sm_system_info@local where name like '%SCN';
NAME VALUE VALUE_STR CATEGORY COMMENTS
------------------------ ----- --------- -------- ----------------------------------
RECOVERED_SCN 0 554.0.10 0 last recovered scn
GLOBAL_SCN 0 579.0.10 0 global scn
LOCAL_SCN 0 579.0.44 0 local scn
MIN_TRANS_VIEW_SCN 0 579.0.44 0 minimum transaction view scn
AGABLE_SCN 0 579.0.44 0 agable lcn
AGABLE_STMT_SCN 0 579.0.44 0 agable statement scn
AGABLE_VIEW_SCN 0 579.0.0 0 agable view scn
LOCAL_AGABLE_SCN 0 579.0.44 0 local agable scn
LOCAL_AGABLE_VIEW_SCN 0 579.0.44 0 local agable view scn
LOCAL_AGABLE_STMT_SCN 0 579.0.44 0 local agable statement scn
LOCAL_MIN_TRANS_VIEW_SCN 0 579.0.44 0 local minimum transaction view scn
REMOTE_AGABLE_SCN 0 579.0.44 0 remote agable scn
REMOTE_AGABLE_STMT_SCN 0 579.0.44 0 remote agable statement scn
REMOTE_AGABLE_VIEW_SCN 0 579.0.44 0 remote agable view scn
CREATE TABLE TEST
(
SERVICE_ID int,
NODE_NUM int,
AMOUT int,
primary key (SERVICE_ID)
)
SHARDING BY RANGE (SERVICE_ID)
SHARD s1 VALUES LESS THAN ( 10000 ) AT CLUSTER GROUP g1,
SHARD s2 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g2
;
create index test_idx1 on test(service_id,node_num);
create sequence seq1;
SELECT COUNT(*) FROM D$MEMORY_BTREE_HEADER('TEST_IDX1');
#1. transaction 처리 (2by1에서 shard 1by1 Insert)
insert into test values(seq1.nextval,1,1);
#2. transaction
gSQL> select * from x$transaction;
PHYSICAL_TRANS_ID LOGICAL_TRANS_ID DRIVER_MEMBER_POS DRIVER_MEMBER_ID DRIVER_TRANS_ID SLOT_ID STATE IS_XA INDOUBT_TRANS_BEHAVIOR ATTRIBUTE ISOLATION_LEVEL VIEW_SCN COMMIT_SCN PREV_COMMIT_SCN TCN BEGIN_LSN USED_UNDO_PAGE_COUNT UNDO_SEGMENT_ID SEQ BEGIN_TIME PROPAGATE_LOG REPREPARABLE GRID_SEQ WEIGHT
----------------- ---------------- ----------------- ---------------- --------------- ------- ------ ----- ---------------------- -------------------- --------------- --------- ---------- --------------- --- --------- -------------------- --------------- --- -------------------------- ------------- ------------ -------- ------
837523210252 32964620 2 -1 28704820 12 ACTIVE FALSE 0 UPDATABLE | LOCKABLE READ COMMITTED 580.0.44 -1.-1.-1 580.0.44 14 201496 0 244813135872 503 2018-02-13 06:25:46.847559 TRUE FALSE 1804 HIGH
9118222188596 32571444 2 -1 28704820 52 ACTIVE FALSE 0 UPDATABLE | LOCKABLE READ COMMITTED 580.0.907 -1.-1.-1 580.0.907 14 211865 0 416611827712 497 2018-02-13 06:25:46.841156 TRUE FALSE 497 HIGH
-65484 28704820 2 3 28704820 52 ACTIVE FALSE 0 UPDATABLE | LOCKABLE READ COMMITTED 580.0.907 -1.-1.-1 580.0.907 13 -1 0 4294901760 438 2018-02-13 06:25:46.845031 TRUE TRUE 450 HIGH
3 rows selected.
---> insert를 했는데 3개의 Transaciton이 생겼다.
gSQL> select * from x$instance;
VERSION STARTUP_TIME STATUS OS_USER_ID IS_CLUSTER LOCAL_GROUP_ID LOCAL_MEMBER_ID LOCAL_MEMBER_NAME LOCAL_MEMBER_POSITION
----------------------------------- -------------------------- ------ ---------- ---------- -------------- --------------- ----------------- ---------------------
Release Venus.3.1.7 revision(24431) 2018-02-08 15:21:20.434065 OPEN 507 TRUE 1 1 G1N1 0
Release Venus.3.1.7 revision(24431) 2018-02-08 15:20:32.057306 OPEN 501 TRUE 2 4 G2N2 3
Release Venus.3.1.7 revision(24431) 2018-02-08 15:21:21.059999 OPEN 502 TRUE 1 2 G1N2 1
Release Venus.3.1.7 revision(24431) 2018-02-08 15:21:21.081912 OPEN 505 TRUE 2 3 G2N1 2
4 rows selected.
gSQL> select * from x$transaction@local;
PHYSICAL_TRANS_ID LOGICAL_TRANS_ID DRIVER_MEMBER_POS DRIVER_MEMBER_ID DRIVER_TRANS_ID SLOT_ID STATE IS_XA INDOUBT_TRANS_BEHAVIOR ATTRIBUTE ISOLATION_LEVEL VIEW_SCN COMMIT_SCN PREV_COMMIT_SCN TCN BEGIN_LSN USED_UNDO_PAGE_COUNT UNDO_SEGMENT_ID SEQ BEGIN_TIME PROPAGATE_LOG REPREPARABLE GRID_SEQ WEIGHT
----------------- ---------------- ----------------- ---------------- --------------- ------- ------ ----- ---------------------- -------------------- --------------- -------- ---------- --------------- --- --------- -------------------- --------------- --- -------------------------- ------------- ------------ -------- ------
837523210252 32964620 2 -1 28704820 12 ACTIVE FALSE 0 UPDATABLE | LOCKABLE READ COMMITTED 580.0.44 -1.-1.-1 580.0.44 14 201496 0 244813135872 503 2018-02-13 06:25:46.847559 TRUE FALSE 1804 HIGH
1 row selected.
gSQL> select * from x$session@local where trans_id=32964620;
ID SERIAL TRANS_ID TOP_LAYER CONNECTION USER_ID LOGIN_USER_ID STATUS WATCH SERVER CLIENT_CHARSET CLIENT_PROCESS SERVER_PROCESS LOGON_TIME PROGRAM ADDRESS PORT DRIVER_MEMBER_POS DRIVER_SESSION_ID DRIVER_SESSION_SERIAL IN_FULL IN_JOB FAILOVER_TYPE FAILED_OVER
-- ------ -------- --------- ---------- ------- ------------- --------- ----- ------ -------------- -------------- -------------- -------------------------- ------------ ------- ---- ----------------- ----------------- --------------------- ------- ------ ------------- -----------
12 37 32964620 12 TCP 6 6 CONNECTED LEAVE SHARED UNKNOWN 0 2765 2018-02-13 06:25:29.103390 cluster peer null 0 2 52 4 0 0 NONE NO
1 row selected.
--> 원격에서 요청한 거임(cluster peer)
DRIVER_MEMBER_POS 2 / DRIVER_SESSION_ID 52
위의 결과로 해당 session 조회
gSQL> select * from x$session@g2n1 where id=52;
ID SERIAL TRANS_ID TOP_LAYER CONNECTION USER_ID LOGIN_USER_ID STATUS WATCH SERVER CLIENT_CHARSET CLIENT_PROCESS SERVER_PROCESS LOGON_TIME PROGRAM ADDRESS PORT DRIVER_MEMBER_POS DRIVER_SESSION_ID DRIVER_SESSION_SERIAL IN_FULL IN_JOB FAILOVER_TYPE FAILED_OVER
-- ------ -------- --------- ---------- ------- ------------- --------- ----- --------- -------------- -------------- -------------- -------------------------- ------- ------- ---- ----------------- ----------------- --------------------- ------- ------ ------------- -----------
52 4 28704820 13 DA 6 6 CONNECTED LEAVE DEDICATED UTF8 24035 24035 2018-02-13 06:24:31.071822 gsql null 0 -1 -1 -1 0 0 NONE NO
1 row selected.
gSQL> select * from x$statement;
ID SESSION_ID START_EXEC VIEW_SCN SQL_TEXT START_TIME TOTAL_EXEC_TIME LAST_EXEC_TIME EXECUTIONS
-- ---------- ---------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------- --------------- -------------- ----------
64 4 TRUE 580.0.44 select * from x$statement 2018-02-13 06:37:03.447559 0 0 1
10 4 TRUE 580.0.907 SELECT /*+ FULL("_A1") */ "_A1"."ID","_A1"."SESSION_ID","_A1"."START_EXEC","_A1"."VIEW_SCN","_A1"."SQL_TEXT","_A1"."START_TIME","_A1"."TOTAL_EXEC_TIME","_A1"."LAST_EXEC_TIME","_A1"."EXECUTIONS" FROM "FIXED_TABLE_SCHEMA"."X$STATEMENT"@LOCAL "_A1" 2018-02-13 06:36:12.766529 0 0 1
10 4 TRUE 580.0.907 SELECT /*+ FULL("_A1") */ "_A1"."ID","_A1"."SESSION_ID","_A1"."START_EXEC","_A1"."VIEW_SCN","_A1"."SQL_TEXT","_A1"."START_TIME","_A1"."TOTAL_EXEC_TIME","_A1"."LAST_EXEC_TIME","_A1"."EXECUTIONS" FROM "FIXED_TABLE_SCHEMA"."X$STATEMENT"@LOCAL "_A1" 2018-02-13 06:37:03.441155 0 0 1
11 4 TRUE 580.0.907 SELECT /*+ FULL("_A1") */ "_A1"."ID","_A1"."SESSION_ID","_A1"."START_EXEC","_A1"."VIEW_SCN","_A1"."SQL_TEXT","_A1"."START_TIME","_A1"."TOTAL_EXEC_TIME","_A1"."LAST_EXEC_TIME","_A1"."EXECUTIONS" FROM "FIXED_TABLE_SCHEMA"."X$STATEMENT"@LOCAL "_A1" 2018-02-13 06:37:03.445038 0 0 1
4 rows selected.
--> insert 처리는 Transaction 처리가 끝났기 때문에 없어짐.
gSQL> select * from x$sm_system_info@local where name like '%SCN';
NAME VALUE VALUE_STR CATEGORY COMMENTS
------------------------ ----- --------- -------- ----------------------------------
RECOVERED_SCN 0 554.0.10 0 last recovered scn
GLOBAL_SCN 0 579.0.10 0 global scn
LOCAL_SCN 0 579.0.44 0 local scn
MIN_TRANS_VIEW_SCN 0 579.0.44 0 minimum transaction view scn
AGABLE_SCN 0 579.0.44 0 agable lcn
AGABLE_STMT_SCN 0 579.0.44 0 agable statement scn
AGABLE_VIEW_SCN 0 579.0.0 0 agable view scn
LOCAL_AGABLE_SCN 0 579.0.44 0 local agable scn
LOCAL_AGABLE_VIEW_SCN 0 579.0.44 0 local agable view scn
LOCAL_AGABLE_STMT_SCN 0 579.0.44 0 local agable statement scn
LOCAL_MIN_TRANS_VIEW_SCN 0 579.0.44 0 local minimum transaction view scn
REMOTE_AGABLE_SCN 0 579.0.44 0 remote agable scn
REMOTE_AGABLE_STMT_SCN 0 579.0.44 0 remote agable statement scn
REMOTE_AGABLE_VIEW_SCN 0 579.0.44 0 remote agable view scn
gSQL> select * from x$sm_system_info@local where name like '%SCN';
NAME VALUE VALUE_STR CATEGORY COMMENTS
------------------------ ----- --------- -------- ----------------------------------
RECOVERED_SCN 0 554.0.10 0 last recovered scn
GLOBAL_SCN 0 580.0.10 0 global scn
LOCAL_SCN 0 580.0.44 0 local scn
MIN_TRANS_VIEW_SCN 0 580.0.44 0 minimum transaction view scn
AGABLE_SCN 0 580.0.44 0 agable lcn
AGABLE_STMT_SCN 0 580.0.44 0 agable statement scn
AGABLE_VIEW_SCN 0 580.0.0 0 agable view scn
LOCAL_AGABLE_SCN 0 580.0.44 0 local agable scn
LOCAL_AGABLE_VIEW_SCN 0 580.0.44 0 local agable view scn
LOCAL_AGABLE_STMT_SCN 0 580.0.44 0 local agable statement scn
LOCAL_MIN_TRANS_VIEW_SCN 0 580.0.44 0 local minimum transaction view scn
REMOTE_AGABLE_SCN 0 580.0.44 0 remote agable scn
REMOTE_AGABLE_STMT_SCN 0 580.0.44 0 remote agable statement scn
REMOTE_AGABLE_VIEW_SCN 0 580.0.44 0 remote agable view scn
create table test2(c1 int);
create sequence seq2;
insert into test2 values(seq2.nextval);
insert into test2 select seq1.nextval from test2;
gSQL> select * from x$sm_system_info@local where name like '%SCN';
NAME VALUE VALUE_STR CATEGORY COMMENTS
------------------------ ----- --------- -------- ----------------------------------
RECOVERED_SCN 0 554.0.10 0 last recovered scn
GLOBAL_SCN 0 584.0.10 0 global scn
LOCAL_SCN 0 584.0.54 0 local scn
MIN_TRANS_VIEW_SCN 0 580.1.44 0 minimum transaction view scn
AGABLE_SCN 0 584.0.54 0 agable lcn
AGABLE_STMT_SCN 0 584.0.54 0 agable statement scn
AGABLE_VIEW_SCN 0 580.1.0 0 agable view scn
LOCAL_AGABLE_SCN 0 584.0.54 0 local agable scn
LOCAL_AGABLE_VIEW_SCN 0 580.1.44 0 local agable view scn
LOCAL_AGABLE_STMT_SCN 0 584.0.54 0 local agable statement scn
LOCAL_MIN_TRANS_VIEW_SCN 0 580.1.44 0 local minimum transaction view scn
REMOTE_AGABLE_SCN 0 584.0.54 0 remote agable scn
REMOTE_AGABLE_STMT_SCN 0 584.0.54 0 remote agable statement scn
REMOTE_AGABLE_VIEW_SCN 0 580.1.44 0 remote agable view scn
gSQL> select * from x$sm_system_info@local where name like '%SCN';
NAME VALUE VALUE_STR CATEGORY COMMENTS
------------------------ ----- --------- -------- ----------------------------------
RECOVERED_SCN 0 554.0.10 0 last recovered scn
GLOBAL_SCN 0 587.0.10 0 global scn
LOCAL_SCN 0 587.0.54 0 local scn
MIN_TRANS_VIEW_SCN 0 580.1.44 0 minimum transaction view scn
AGABLE_SCN 0 587.0.54 0 agable lcn
AGABLE_STMT_SCN 0 587.0.54 0 agable statement scn
AGABLE_VIEW_SCN 0 580.1.0 0 agable view scn
LOCAL_AGABLE_SCN 0 587.0.54 0 local agable scn
LOCAL_AGABLE_VIEW_SCN 0 580.1.44 0 local agable view scn
LOCAL_AGABLE_STMT_SCN 0 587.0.54 0 local agable statement scn
LOCAL_MIN_TRANS_VIEW_SCN 0 580.1.44 0 local minimum transaction view scn
REMOTE_AGABLE_SCN 0 587.0.54 0 remote agable scn
REMOTE_AGABLE_STMT_SCN 0 587.0.54 0 remote agable statement scn
REMOTE_AGABLE_VIEW_SCN 0 580.1.44 0 remote agable view scn
insert into test2 values(3);
commit;
-->???? 궁금 global scn은 언제 밀리는 거지 / 그리고 agable 영향도는 뭐가 있지
| << failover master 지정 >> |
alter system set failover_driver_member = 0
-> fail-over master 지정 할 수 있다.
| [DB 확인] select * from x$instance; gSQL> select * from x$cluster_location@local; [접속확인] - DB접속 확인 select cluster_member_name, count(*) from x$session a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g1n1 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g1n2 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g2n1 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g2n2 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g3n1 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; select cluster_member_name, count(*) from x$session@g3n2 a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; - App 접속 확인 select cluster_member_name,count(*) from x$session where program like '%SAMain%' group by cluster_member_name; select cluster_member_name,count(*) from x$session where program like '%SSMain%' group by cluster_member_name; [실행확인] select * from V$SYSTEM_SQL_STAT@g1n1 where stat_name like '%INSERT INTO%'; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g1n1; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g1n2; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g2n1; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g2n2; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g3n1; select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g3n2; [App 확인] select cluster_member_name,program from x$session where program like '%SAMain%'; select cluster_member_name,program from x$session where program like '%SSMain%'; select cluster_member_name,count(*) from x$session where program like '%SSMain%' group by cluster_member_name; - g1n1 확인 select cluster_member_name,program from x$session@g1n1 where program like '%SAMain%'; select cluster_member_name,program from x$session@g1n1 where program like '%SSMain%'; - g1n2 확인 select cluster_member_name,program from x$session@g1n2 where program like '%SAMain%'; select cluster_member_name,program from x$session@g1n2 where program like '%SSMain%'; - g2n1 확인 select cluster_member_name,program from x$session@g2n1 where program like '%SAMain%'; select cluster_member_name,program from x$session@g2n1 where program like '%SSMain%'; - g2n2 확인 select cluster_member_name,program from x$session@g2n2 where program like '%SAMain%'; select cluster_member_name,program from x$session@g2n2 where program like '%SSMain%'; - g3n1 확인 select cluster_member_name,program from x$session@g3n1 where program like '%SAMain%'; select cluster_member_name,program from x$session@g3n1 where program like '%SSMain%'; - g3n2 확인 select cluster_member_name,program from x$session@g3n2 where program like '%SAMain%'; select cluster_member_name,program from x$session@g3n2 where program like '%SSMain%'; [in-active member 삭제] gSQL> alter database drop inactive cluster members; gSQL> alter cluster group g1 add cluster member g1n1 host '192.168.11.213' port 10101; gSQL> alter cluster group g1 add cluster member g1n2 host '192.168.11.214' port 10101; gSQL> alter cluster group g2 add cluster member g2n1 host '192.168.11.215' port 10101; gSQL> alter cluster group g2 add cluster member g2n2 host '192.168.11.216' port 10101; gSQL> alter cluster group g3 add cluster member g3n1 host '192.168.11.201' port 10101; gSQL> alter cluster group g3 add cluster member g3n2 host '192.168.11.202' port 10101; ======== create cluster group g1 cluster member g1n1 host '192.168.11.213' port 10101; alter database rebalance; alter cluster group g1 add cluster member g1n2 host '192.168.11.214' port 10101; alter database rebalance; create cluster group g2 cluster member g2n1 host '192.168.11.215' port 10101; alter database rebalance; alter cluster group g2 add cluster member g2n2 host '192.168.11.216' port 10101; alter database rebalance; alter cluster group g3 add cluster member g3n1 host '192.168.11.201' port 10101; gSQL> alter database rebalance; ALTER SYSTEM RECONNECT GLOBAL CONNECTION; select cluster_member_name, count(*) from x$session a where GLOBAL_CONNECTION = 'TRUE' group by cluster_member_name; --> 새로이 접속 안되는 것은 execute 처리 시 신규로 접속 처리 된다. select * from X$SQL_SYSTEM_STAT_EXEC_STMT@g3n2; gSQL> select * from x$instance order by LOCAL_MEMBER_ID asc; select cluster_member_name, count(*) from test_01 group by cluster_member_name; select cluster_member_name , count(*) from x$session where global_connection='TRUE' group by cluster_member_name; select 'G1N1 : ' || STMT_TYPE, STMT_TYPE_ID, EXECUTE_COUNT from X$SQL_SYSTEM_STAT_EXEC_STMT@g1n1 where STMT_TYPE ='SELECT' union all select 'G1N2 : ' || STMT_TYPE, STMT_TYPE_ID, EXECUTE_COUNT from X$SQL_SYSTEM_STAT_EXEC_STMT@g1n2 where STMT_TYPE = 'SELECT' union all select 'G2N1 : ' || STMT_TYPE, STMT_TYPE_ID, EXECUTE_COUNT from X$SQL_SYSTEM_STAT_EXEC_STMT@g2n1 where STMT_TYPE = 'SELECT' union all select 'G2N2 : ' || STMT_TYPE, STMT_TYPE_ID, EXECUTE_COUNT from X$SQL_SYSTEM_STAT_EXEC_STMT@g2n2 where STMT_TYPE = 'SELECT'; select * from gv$SYSTEM_SQL_STAT where stat_name like '%SELECT .. INTO%' order by STAT_NAME; |
| << cluster Member reset >> |
<< ALTER DATABASE RESET LOCAL CLUSTER MEMBER >>
1. 기능
Local cluster member 를 tablespace 객체를 제외하고 database 생성 시점으로 초기화한다.
2. 구문
<alter database reset local cluster member statement> ::=
ALTER DATABASE RESET LOCAL CLUSTER MEMBER;
3. 사용 범위 및 접근 권한
Cluster system 에서 수행할 수 있다.
Start-up 과정 중 LOCAL OPEN 단계에서 수행할 수 있다.
4. 설명
Local cluster member 를 tablespace 객체를 제외하고 database 생성 시점으로 초기화한다.
Tablespace 객체를 제외한 사용자가 생성한 모든 객체를 제거한다.
<alter database reset local cluster member statement> 구문은 inactive cluster member 를 초기화하고,
새로운 cluster member 로 cluster system 에 참여하기 위해 사용한다.
Cluster system 과 연결이 단절된 inactive cluster member 들은 다음과 같은 처리가 가능하다.
Cluster system 에 다시 참여할 수 있는 경우, JOIN 구문을 이용하여 참여
ALTER SYSTEM JOIN DATABASE새 창에서 링크 열기
Cluster system 에 다시 참여할 수 없는 경우, DROP 구문을 이용하여 cluster system 에서 제외
ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS새 창에서 링크 열기
이 때, cluster system 에서 제외된 cluster member 에 해당하는 장비는 다음과 같은 두 가지 방법으로 재사용할 수 있다.
[방법1]: local cluster member 의 database 재생성
[방법2]: <alter database reset local cluster member statement> 구문을 이용해 local cluster member 를 초기화
<alter database reset local cluster member statement> 구문을 사용한 [방법2]는
database 를 재생성하는 [방법1]보다 tablespace 를 재생성하는 비용을 줄일 수 있다.
5. example
gSQL> \startup nomount
Startup success
gSQL> ALTER SYSTEM MOUNT DATABASE;
System altered.
gSQL> ALTER SYSTEM OPEN LOCAL DATABASE;
System altered.
gSQL> ALTER DATABASE RESET LOCAL CLUSTER MEMBER;
Database altered.
[ Example ]
1. g1n1 서버를 shutdown abort 해야 합니다
2. g1n2 에서 Index g1n1 member를 drop member 한다.
gSQL> alter database drop inactive cluster members;
Database altered.
3. g1n1 meber startup 하여 local open 단계로 만든 후 Tablespace 제외하고 초기화 한다.
gSQL > startup
gSQL> ALTER DATABASE RESET LOCAL CLUSTER MEMBER;
Database altered.
gSQL> alter system open global database;
4. g1n2에서 g1n1 member를 추가한다.
gSQL> alter cluster group G1 add cluster member g1n1 host '10.10.10.3' port 10101;
Cluster Group altere
5. g1n2에서 alter database rebalance;
| << cserver 조절 명령어 >> |
alter system set usable cserver_map=1 at g1n1;
| << global transaction 관련 조회 >> |
gSQL> select * from x$global_transaction@local;
gSQL> select * from x$global_lock_wait@local;
gSQL> select * from x$cluster_server@local;
gSQL> select * from x$session@local where program like '%peer%';
| << MEMBER NAME 조회 >> |
gSQL> SELECT A.MEMBER_ID, (SELECT MEMBER_NAME FROM USER_GSI_PLACE@LOCAL WHERE MEMBER_ID = A.MEMBER_ID LIMIT 1) , PHYSICAL_CONNECTION FROM X$CLUSTER_MEMBER@LOCAL A;
MEMBER_ID (SELECT MEMBER_NAME FROM USER_GSI_PLACE@LOCAL WHERE MEMBER_ID = A.MEMBER_ID LIMIT 1) PHYSICAL_CONNECTION
--------- ------------------------------------------------------------------------------------ -------------------
1 G1N1 INACTIVE
2 G1N2 ACTIVE
3 G2N1 ACTIVE
4 G2N2 ACTIVE
4 rows selected.
| << v$tablespace_stat >> |
tablespace 통계 정보를 보여 준다.
gSQL> select * from v$tablespace_stat;
TBS_NAME TBS_ID TOTAL_EXT_COUNT USED_META_EXT_COUNT USED_DATA_EXT_COUNT FREE_EXT_COUNT EXTENT_SIZE
-------------- ------ --------------- ------------------- ------------------- -------------- -----------
DICTIONARY_TBS 0 8191 1 1722 6468 65536
MEM_UNDO_TBS 1 16383 1 258 16124 65536
MEM_DATA_TBS 2 20479 1 1526 18952 262144
MEM_TEMP_TBS 3 8191 1 1436 6754 262144
DISK_DATA_TBS 4 799 1 0 798 262144
MEM_AUX_TBS 5 799 1 12 786 262144
MEM_TRANS_TBS 6 1 1 0 0 262144
참고 : MEM_TRANS_TBS global transaciton 상태를 저장할 때 사용 하는 것으로 실제 user 단에서 사용 하는 것은
아니며 FREE_EXT_COUNT 0인것은 신경 쓰지 마라 정상 이다.
(10240 * member 수)
| << cluster position 변경 기능 >> |
1. 정의
아래와 같이 새로 추가되는 member 에 대해 member position 을 지정할 수 있도록 하여,
member 추가시 group 내에서 master 나 slave 에 위치할 수 있도록 합니다.
> ALTER CLUSTER GROUP group_name ADD CLUSTER MEMBER
<member position>
Cluster Member 의 position number 를 지정한다. 생략할 경우, 기본값은 POSITION DEFAULT 이다.
* POSITION DEFAULT
시스템이 자동으로 position number 를 부여한다.
* POSITION MAX
* 빈 position number 가 있더라도 새로운 member position number 을 할당한다.
* 가장 큰 member position 보다 큰 값을 부여한다.
* POSITION number
* number 에 해당하는 position number 를 부여한다.
* 해당 position number 는 cluster system 내에서 유일해야 한다.
* 해당 position number 는 비어있는 position number 이고 가장 큰 position number 보다 작아야 한다.
Cluster Member 의 member_position 정보는 DBA_CLUSTER view 를 통해 조회할 수 있다.
SELECT member_name, member_id, member_position FROM dba_cluster;
예를 들어 다음과 같이 position number 가 사용되고 있다고 하자.
G1N1 : 0
G1N2 : 1
G2N2 : 3
G3N2 : 5
각 옵션에 따라, 다음과 같은 값을 부여한다.
* POSITION DEFAULT
비어있는 2 값을 부여한다.
* POSITION MAX
새로운 position number 값인 6 을 부여한다.
* POSITION 3
중복되므로 error
* POSITION 4
position number 4 를 부여한다.
Examples1)
다음은 비어 있는 member position 을 cluster member position 으로 지정한 예이다.
ALTER CLUSTER GROUP g2 ADD
CLUSTER MEMBER g2n1 HOST '192.168.0.21' PORT 10210 POSITION 4;
Examples2)
예를 들어, [G2N1] 이 제거되어 다음과 같이 (2) position 이 비었다고 할때,
[G1N1(0)][G1N2(1)]
[----(2)][G2N2(3)]
[G3N1(4)][G3N2(5)]
* G2N1 를 추가하여 G2 group 의 master 역할을 하고자 하는 경우
ALTER CLUSTER GROUP G2 ADD
MEMBER G2N1 HOST '192...' PORT 10210 POSITION 2
[G1N1(0)][G1N2(1)]
[G2N1(2)][G2N2(3)]
[G3N1(4)][G3N2(5)]
* G3N0 를 추가하여 G3 group 의 master 역할을 하고자 하는 경우
ALTER CLUSTER GROUP G3 ADD
MEMBER G3N0 HOST '192...' PORT 10300 POSITION 2
[G1N1(0)][G1N2(1)]
[G2N2(3)]
[G3N0(2)][G3N1(4)][G3N2(5)]
* G2N3 를 추가하여 G2 group 의 slave 로 추가하고자 하는 경우
ALTER CLUSTER GROUP G2 ADD
MEMBER G2N3 HOST '192...' PORT 10200 POSITION MAX
[G1N1(0)][G1N2(1)]
[----(2)][G2N2(3)][G2N3(6)]
[G3N1(4)][G3N2(5)]
| << rebalance 관련 조회 >> |
rebalance 관련 조회 뷰는 없다. 다만 master trc log에 실제 처리되고 있는 건수를 확인 할수 있다.
또는 tablespace 관련 사이즈로 확인 할 수 있다.
| << xa transaction 확인 및 commit >> |
1. select * from x$transaction order by BEGIN_TIME;
IS_XA : xa 사용 하냐 안하냐 볼 수 있다.
참고 : xa에서는 x$transaction에 남아 있어도 x$session 정보에 남아 있지 않을 수 있다.
2. x$xa_transaction(driver 에서 수행 된 xa transaction을 보여준다)
gSQL> select * from x$transaction order by BEGIN_TIME;
PHYSICAL_TRANS_ID LOGICAL_TRANS_ID DRIVER_MEMBER_POS DRIVER_MEMBER_ID DRIVER_TRANS_ID SLOT_ID STATE IS_XA INDOUBT_TRANS_BEHAVIOR ATTRIBUTE ISOLATION_LEVEL VIEW_SCN COMMIT_SCN PREV_COMMIT_SCN TCN SESSION_ID BEGIN_LSN USED_UNDO_PAGE_COUNT UNDO_SEGMENT_ID SEQ BEGIN_TIME PROPAGATE_LOG REPREPARABLE GRID_SEQ WEIGHT
----------------- ---------------- ----------------- ---------------- --------------- ------- ------ ----- ---------------------- -------------------- --------------- -------------- ---------- --------------- --- ---------- --------- -------------------- --------------- ----- -------------------------- ------------- ------------ ----------- ------
24408299864419 439419235 0 -1 473760191 355 ACTIVE TRUE 0 UPDATABLE | LOCKABLE READ COMMITTED 138208.0.5911 -1.-1.-1 136454.0.5911 6 355 677169950 0 1717986918400 6705 2021-04-16 13:57:34.840154 TRUE FALSE 12907023406 5
285379107553602 515572034 0 -1 473760191 322 ACTIVE TRUE 0 UPDATABLE | LOCKABLE READ COMMITTED 138208.0.5836 -1.-1.-1 136454.0.5836 6 322 677184664 0 1576252997632 7867 2021-04-16 13:58:55.052786 TRUE FALSE 12884909184 5
176274052415794 510132530 0 -1 473760191 306 ACTIVE TRUE 0 UPDATABLE | LOCKABLE READ COMMITTED 138208.0.5863 -1.-1.-1 136454.0.5863 6 306 677436473 0 1507533520896 7784 2021-04-16 13:59:12.455610 TRUE FALSE 12884909101 5
30734789247423 473760191 0 1 473760191 447 ACTIVE TRUE 0 UPDATABLE | LOCKABLE READ COMMITTED 138208.0.25733 -1.-1.-1 136454.0.25704 6 447 677731572 0 2113123909632 7229 2021-04-16 14:03:06.682057 TRUE FALSE 12901609562 5
DRIVER_TRANS_ID : 473760191, LOGICAL_TRANS_ID: (439419235, 515572034, 510132530, 473760191)
gSQL> select * from x$xa_transaction where LOCAL_TRANS_ID = 473760191;
XA_TRANS_ID LOCAL_TRANS_ID DRIVER_MEMBER_ID STATE ASSO_STATE START_TIME REPREPARABLE
-------------------------------------------------------------------------------------------------------------------- -------------- ---------------- ----- -------------- -------------------------- ------------
40.76626F7330310000000000000000000000000000000000000000000000000000473C0500BF4E756088000000.280000000A0000000A000000 473760191 1 IDLE NOT_ASSOCIATED 2021-04-16 14:03:06.682057 FALSE
1 row selected.
-> NOT_ASSOCIATED ( session이 접속 되지 않을 것을 알 수 있다)
3. x$remote_xa_transaction(driver가 아닌 local에서만 수행 된 xa transaction을 보여 주게 된다.)
gSQL> select * from x$remote_xa_transaction where LOCAL_TRANS_ID = 439419235;
DRIVER_TRANS_ID DRIVER_MEMBER_POS LOCAL_TRANS_ID STATE START_TIME REPREPARABLE
--------------- ----------------- -------------- ----- -------------------------- ------------
473760191 0 439419235 IDLE 2021-04-16 13:57:34.840154 FALSE
1 row selected.
--> 상태가 IDLE 상태인 것을 알 수 있따.
4. transaction 의미(x$transaction)
DRIVER_TRANS_ID : 실제 Transaction이 시작된 Transaction id
LOGICAL_TRANS_ID : Local에서 수행 된 Transaction id
참고 : g1n1에서 transaction이 수행 되어 txid1 이 따졌고 g1n2,g2n1,g2n2에 해당 transaction이
퍼지게 되면 g1n2 (tx2) , g2n1(tx3), g2n2(tx4) 와 같이 local transaction id가 따지게 되고
driver transaction id g1n1에서만 있게 된다.
xa commit '40.76626F7330310000000000000000000000000000000000000000000000000000473C0500BF4E756088000000.280000000A0000000A000000' onephase;
Commit completed;
(commit, rollback, recovery)
[ XA VIEW]
gSQL> select * from x$xa_transaction where LOCAL_TRANS_ID = 473760191;
XA_TRANS_ID LOCAL_TRANS_ID DRIVER_MEMBER_ID STATE ASSO_STATE START_TIME REPREPARABLE
-------------------------------------------------------------------------------------------------------------------- -------------- ---------------- ----- -------------- -------------------------- ------------
40.76626F7330310000000000000000000000000000000000000000000000000000473C0500BF4E756088000000.280000000A0000000A000000 473760191 1 IDLE NOT_ASSOCIATED 2021-04-16 14:03:06.682057 FALSE
1 row selected.
- STATE
END : XA Transacton END 까지 하고 한 상태
Rollback Only : XA Transaction DML 처리 후 나간 상태
(XA Beging DML 처리 후 다른 작업 없이 나간 상태 이 때는 session은 정리 되고 해당 Transacton은 남아 있다.)
- ASSO_STATE
NOT_ASSOCIATED : session이 접속 되어 있지 않은 상태
ASSOCIATED : session이 접속 되어 있는 상태
[ XA Transaction ]
start : xa start
dml : dml 처리 (insert into t1 values(1))
prepare : xa prepare
commit : xa commit, rollback : xa rollback;
참고 : xa 에서는 prepare 하고 session이 끊길 수 있다.
참고2 : transacton.ts.sql
[ XA Trace ]
alter system set trace_xa = 1 ( 0:default) // xa trace log 남긴다.
(xa.trc : session_id / gserver thread id)
xas rollback 10;
xa start 10;
insert into t1 values(1); //dml 처리
xa end 10 success;
xa prepare 10;
xa commit 10;
(xa commit 10 onephase : xa prepare,commit 을 한꺼번에 처리 하는 명령어)
-> xa end 처리 후 부터는 session에서 xa transaction은 끝낸다. 이후부터 xa transaction은 normal transaction으로 처리 된ㄷ.)
참고 : sslDef.h , knldef.h (knlxastate)
| << sequence 확인 >> |
> global sequence latch 잡고 있는 session 조회
setp1. definition_schema.sequences
gSQL> select * from definition_schema.sequences@local where SEQUENCE_NAME = 'FTS_ORD_SEQ';
step2. physical id
gSQL> select GLOBAL_LATCH_SESSION_ID from x$sequence where physical_id=99553046953984;
540
(x$sequenc e 전체 조회 정보)
참고 : IS_ONLINE // 시퀀스가 TRUN 인지 확인
sete3. session id 조회
gSQL> select * from x$session@local where id=540;
no rows selected.
'sunjesoft > Goldilocks Cluster' 카테고리의 다른 글
| Goldilocks Cluster Performance View (0) | 2021.09.02 |
|---|---|
| Goldilocks Cluster Install 가이드 (0) | 2021.08.17 |
