CUBRID Development Tips and Known Issues_kr

설정

* ACL 설정을 통해 개발자가 쿼리 브라우저를 통해 접근하는 브로커 포트를 통제 할 수 있는 기능.
* 쿼리 브라우져를 통해 접속하는 브로커의 설정을 통해 잘못된 동작을 방지할 수 있는 기능
- where 절 없는 update, delete 질의에 대해 오류 처리하는 기능. (cubrid.conf 에 block_nowhere_statement 설정)
- ddl 구문에 대해 오류 처리하는 기능 (cubrid.conf에 block_ddl_statement 설정)
- 개발자가 쿼리 브라우저를 통해 접속하는 브로커는 SO (slave only)로 설정함으로써 마스터 DB에 접속하지 못하도록 하는 기능.
* 쿼리 타임아웃을 설정하는 기능
- 쿼리 별로 query timeout을 설정하는 기능
- connection url을 통해 설정함으로 써, 해당 커넥션에서 생성된 모든 쿼리에 대해 query timeout이 적용될 수 있도록 하는 기능
- 응용에서 query timeout이 설정되지 않은 경우에도 브로커 query timeout 설정을 통해 쿼리 실행 시간을 제한하는 기능

쿼리

DB의 page size인 16KByte를 넘어가는 레코드들에 대해 UNION 수행시 메모리 누수 현상이 있음.
따라서 아래와 같이 UNION ALL로 우회하여 쿼리 작성해야 함
(UNION ALL은 레코드 정렬 및 중복값을 제거하지 않기 때문에 메모리릭 현상이 발생하지 않음)

변경전
SELECT c_cd, c_type,c_cont
FROM hlt INNER JOIN
((SELECT c_cd, c_type,c_cont FROM ch_tbl WHERE id='BB00095')
UNION
(SELECT c_cd, c_type,c_cont FROM tr_tbl WHERE id='BB00095 ')
UNION
(SELECT c_cd, c_type,c_cont FROM bh_tbl WHERE id='BB00095')) def
ON hlt.id=def.id LIMIT 1

변경후
SELECT c_cd, c_type,c_cont
FROM hlt INNER JOIN
((SELECT c_cd, c_type,c_cont FROM ch_tbl WHERE id='BB00095')
UNION ALL
(SELECT c_cd, c_type,c_cont FROM tr_tbl WHERE id='BB00095 ')
UNION ALL
(SELECT c_cd, c_type,c_cont FROM bh_tbl WHERE id='BB00095')) def
ON hlt.id=def.id LIMIT 1

위 예제는 id가 PK이므로 단순 UNION ALL만으로 가능하나 만약 row수가 여러건일 경우 UNION ALL 결과에 대해 distinct를 다시 수행해야 함

쿼리

테이블 존재여부를 확인하기 위해서 select * from tbl limit 0를 사용하는 경우가 있는데, CUBRID는 rownum이 1부터 시작하기 때문에 limit 0인 경우에 테이블 전체를 스캔하게됨.
테이블의 존재 여부를 확인 하기위해서는 다음과 같이 사용하는것을 권고함.

Select 1 from tbl limit 1
또는
select count(*) from db_class where class_name='tbl'

로직

cubrid에서 auto_increment는 명시적으로 수정 가능함에 따라 적절히 응용 개발할 필요 있음

1. CUBRID/MYSQL의 경우 auto_increment 컬럼에 대한 명시적인 값 insert 가 허용
2. MSSQL 의 경우 identity 컬럼에 대한 명시적인 값 insert 가 안되는 것이 기본 스펙
3. auto_increment 에 명시적으로 insert 했을 경우

ex) 데이터가 1, 2 들어있는 테이블에 강제로 3을 insert 한 후의 동작
CUBRID : serial에서 3을 채번해서 insert 하므로 unique violation 발생
MYSQL : 오류 없이 4가 insert됨
==> CUBRID의 경우 SERIAL_NEXT_VALUE("nst_prod_ai_prod_no", 10) 를 이용해 값을 강제로 jump 시켜야 함

쿼리

복합 인덱스에서 중간에 NULL이 들어있을 경우 불필요한 내부 scan으로 성능저하 발생할 수 있음. 서비스 특성(스키마 구조)을 고려해 아래와 같이 우회 할 수있음(DBA와 협의)

쿼리 : SELECT usid FROM tbl_user WHERE ser_id = 1130 AND feed_id = 17243 AND in_flag = 'Y'

1. in_flag가 NULL, NULL, NULL, 'Y' 로 있을 때
Num_data_page_fetches = 2626
Num_data_page_dirties = 2

2. in_flag가 'N','N','N','Y' 로 있을 떄
Num_data_page_fetches = 15
Num_data_page_dirties = 2

쿼리

해당 형태의 쿼리에서는 select 절에 인덱스가 있더라도 order 를 하기 때문에 응답시간이 길어질 수 있음. 따라서 명시적으로 힌트를 사용해 우회함

변경전
INSERT INTO ... SELECT ... ORDER BY ..

변경후
INSERT INTO ... SELECT ... USING INDEX ...로 우회

쿼리

deadlock발생시 응용에서 대응할 수 있는 방법:

1. deadlock 발생시 retry 로직이 꼭 필요하며 일반적으로 2~3회 retry 시도하도록 함
2. 가능한 꼭 필요한 부분만 트랜잭션으로 묶고 이외에는 단일 트랜잭션(auto_commit) 처리하도록 함
3. 불필요한 인덱스 제거
5. 싱글쓰레드로 데이터 처리
4. 쿼리 수정

팁 : delete를 위한 대상 row수를 최소화한다. (select 는 shared lock만 잡는다.)

변경전
DELETE FROM a WHERE usid=? AND obj_cnt=?
- pk (usid , unrgst_cnt_no)
- idx1 (usid, obj_cnt)

변경후
DELETE FROM a WHERE usid =? AND unrgst_cnt_no IN
(SELECT unrgst_cnt_no
FROM a WHERE usid=? AND
obj_cnt=? );

쿼리

변경전
UPDATE v_info V,
(SELECT DISTINCT s_info_id FROM s_info WHERE p_key =? AND s_id = ?) S
SET V.p_flag = #pStatus#
WHERE V.s_info_id = S.s_info_id

변경후
UPDATE v_info V
SET V.p_flag = #pStatus#
WHERE V.s_info_id in
(SELECT DISTINCT s_info_id FROM s_info WHERE p_key =? AND s_id =?)

스키마 구조상 한번의 쿼리 우회로 불가능할 경우 조인 결과값을 받아둔 후 각 테이블 처리해야 함

쿼리

주로 다음과 같은 경우 잘못된 플랜으로 쿼리가 실행됨으로 slow-query 발생하여 장애로 이어질 수 있음.
- DB재구축과 같은 대량의 데이터 변경
- 테이블에 데이터가 작은(없는)상태에서 급격한 데이터 증가
- DB 옵티마이져의 한계
- DBA는 대량 작업 후 반드시 통계정보 갱신 작업을 실행하고 slow-query에 대한 모니터링을 강화한다.
- 개발자는 쿼리 플랜을 보는 방법을 숙지하고 쿼리 작성시 힌트를 적절히 활용하도록 한다

쿼리

대량의 데이터 작업 시 꼭 limit로 끊어 여러 번 분할 실행하여 운영DB의 영향을 최소화 해야함. 특히 쿼리 수행 빈도가 높은 테이블의 경우 lock/deadlock으로 인해 장애로 이어질 수 있어 주의해야 함

변경전
DELETE from a where w_dt > '1972-01-01';

변경후
DELETE from a where w_dt > '1972-01-01' limit 1000;

쿼리

1:N 구조에서 read분산할 경우 주의 점
- write와 select를 구분하여 DB에 연결할 수 있도록 구현
- write직후 select는 master에서 수행하여 데이터 정합성 유지
(write직후 slave에서 바로 데이터 조회시 복제 지연으로 데이터가 보이지 않을 수 있음)

쿼리

특정 인덱스가 매우 안 좋은 데이터 분포도를 가지고 있고, 이 데이터를 삭제할 경우 매우 느린 성능 이슈 있음
- Cubird의 B+tree 인덱스는 leaf node에 (Key : OID list)를 저장하고 있으며,
하나의 key에 해당하는 레코드가 많으면 OID List도 많은 모습이 됨.
- 서비스에서 발생했던 이슈로 Delete 수행시 해당 OID를 찾는 부분에서 많은 CPU 연산을 유발하면서 삭제가 오래 걸렸음
인덱스 수정 및 쿼리를 limit로 끊어 여러번 분할 수행하도록 함

쿼리

MySQL에서 대소문자 구분하지 않고 검색 가능
- lower, upper 함수를 사용하여 별도 컬럼 추가하여 검색
- 함수 기반 인덱스 제공 및 collation 추가 예정(CUBRID 8.4.x 이후 버전)

모델/쿼리

수치형
1. int, bigint는 동일하게 변환(unsigned는 무시함)
2. tinyint, smallint는 표준 도메인 적용하여 모두 smallint로 변환
3. float(5,1)은 numeric(5,1)로 변환

문자
1. 영문만 들어가는 스트링형은 그대로 변환
2. 한글의 경우 3 or 4자리까지 사이즈 늘려줌
(MySQL에서 varchar(3)은 글자 3개까지 입력되는데, Cubrid에서 varchar(3)은 글자 1개만 입력됨-utf8일 경우)
3. enum 타입은 char로 변환
4. blob, text는 string으로 변환
5. MySQL에서 "를 사용해서 문자열을 넣지만, CUBRID에서는 '을 사용
6. blob/clob는 NOT NULL 및 인덱스 생성 하지 못함(복제 안됨)

날짜
1. datetime, timestamp는 표준 도메인 적용하여 모두 datetime로 변환
2. date는 표준 도메인 적용하여 char(8)로 변환
3. timestamp에 default로 CURRENT_TIMESTAMP가 설정되어 있을 경우 제거후 SQL 변경해야 함
(Cubrid의 경우 테이블 생성시점으로 timestamp값이 기본 저장됨-데이터 insert 시점이 아님)
4. date, datetime의 default 값으로 '0000-00-00' 을 사용할 수 없음
(단, 8.4.1버전 이상에서는 사용 가능)

인덱스
1. SET 타입에 대해 Index를 걸 수 없음.
2. fulltext 인덱스를 지원하지 않음
3. MySQL에서는 리버스 인덱스가 없어 이를 위한 역술 컬럼 or 음수(-)를 사용하는 경우 있어 주의 필요
4. 테이블에 PK가 있어야만 복제가 됨

기타
1. auto increment는 동일하게 변환
2. 채번한 1개의 값 확인이 필요할 경우 or 2개 이상 테이블에 insert해야 하는 경우 last_insert()로 확인
(단, 8.4.0 이하 버전에서는 last_insert() 함수가 없으므로 serial 을 사용해야 함)
3. DB간 조합을 위해서는 같은 DB내에 테이블을 생성해야 함
(MySQL에서는 database간 조인이 되어 분리하는 경우가 많음)
4. 예약어가 다르기 때문에 주의 필요
( 예약어 사용을 위해서는 큰따옴표 혹은 대괄호 혹은 백쿼터를 사용해야 함

MySQL to CUBRID 마이그레이션 관련 주의할점
1.MySQL의 원본 데이터에서 NOT NULL 컬럼에 NULL이 들어있는 경우가 있음. 소스테이블의 최초 스키마가 Nullable 이었다가 운영 도중 속성이 변경되었을 수 있음. 이 경우, CUBRID의 NOT NULL 컬럼에 데이터를 마이그레이션하는 경우, 일부 데이터가 violation 으로 인해 누락될 수 있다.
2.mysqldump를 사용해서 마이그레이션할 때, --tab으로 데이터를 뽑을 때는 mysqld를 실행한 계정과 같은 계정으로 뽑아내야 함.
3.MySQL의 AUTO_INCREMENT 컬럼이 있는 경우, 이 컬럼의 최대값보다 큰 값으로 CUBRID의 SERIAL이 시작되도록 하여야 함. 그렇지 않으면 Insert 질의 실행할 때 Unique Violation이 날 수 있음.  

설정

1. statement pool 사용으로 동일한 쿼리에 대한 파싱 생략
(lucy에서 Pooled Prepare Statement = true, DB는 STATEMENT_POOLING=on)
2. connection 개수는 broker의 max cas개수보다 작아야 함
(max connection수 * 웹서버 수 =< max cas 수)