자바 (23). Mybatis와 SqlSession 그리고 commit 이야기

문제 없이 잘 진행되던 이기종 동기화 작업중, 큰 문제?가 발생하였다. !!! 음 뭐 여기다 적어서 뭐하나 싶긴하지만,,

문제는 이러했다. 이기종간의 테이블 싱크를 맞추면서 upsert를 하더라도 벌크 upsert로 진행하면 그렇게 큰 문제는 되지 않는걸 확인하였다. (2000만 건이 넘는 데이터도 크게 무리는 없었으니깐,,)

문제는 삭제에 관한것이였다. 2000만건(하루 2억 row) 의 데이터를 삭제 하기 위해선 어떻게 해야 할까?? 마냥 비교는 답이 없으려나??

완벽한 해결책은 아직 없다, 그냥 여기다 고민한 내용을 끄적일까 하여 기록해 본다…

일단 팀장님한테 컨펌을 받아야 해서,,

오랜만에 개떡같던 내 자바코드를 보니 반갑기도 하면서 나도 다시 모르겠더라,,

무튼 시작해보도록 하자

0. 목차

1. 기존의 방법
2. Hash key 비교
3. 해당 Table값 Delete Insert
   • sqlSession auto commit
   • MySQl lock
4. Outter Join 으로 체크하여 삭제 하기

1. 기존의 방법

기존의 방법은 이러하였다.

원본(오라클db) 에서 2000만 건의 데이터를 Upsert 이후 number type 인 index를 하나 골라 min,max 값을 가져온다.

그 이후 동기화가 필요한 MysqlDB에서 그 min 보다 작은 itm_index들을 삭제 max값보다 큰 itm_index값을 삭제 하는 방식으로 동기화를 시켰다

그런데,, 이 중간 값이 갑자기 삭제 된다면 어떻게 처리를 해야 할까?.

이러한 방법으론 정합성을 지키기가 많이 힘들었다.

그래서 생각한 방법은 이러하였다.

2. Hash Key 비교

이미 이번 프로젝트에서 Hash Key를 통한 비교는 여러곳에서 사용이 되었다.

그렇기 때문에 내가 가장먼저 생각한 방법은 HashKey 비교였었다.

HashKey를 사용하였을때, 검색에 필요한 연산은 20,000,000 x log2(20,000,000) ~= 480,000,000 (~ 5억 연산정도?) 번

이정도 라면 해볼만 하다고 생각하였다.

아래는 대충 어떻게 할지에 대한 테스트 코드이다.

/**
 * 데이터 싱크를 맞추기 이전 Hash를 통한 비교가 얼마나 빠르게 끝날지 판단하기 위한 테스트
 * @author hasnw90
 */
public class ComapreHashKey {

    public static void main(String[] args) {

        HashMap<Integer, String> oracleOriginData = new HashMap<>();
        HashMap<Integer, String> mariaTargetData = new HashMap<>();

        
        // HashMap에 값 채우기
        for (int i = 0; i < 20000000; i++) {
            mariaTargetData.put(i, null);
            if(i > 100000 && i< 19000000 && i % 2 == 0 ) {
                oracleOriginData.put(i, null);
            }
        }

        System.out.println(oracleOriginData.size()); //9449999
        System.out.println(mariaTargetData.size()); //20000000

        // MariaDB를 돌면서 OracleHash에 있는 값들을 모두 삭제해 버린다.
        // 그냥 mariaKey에서 삭제를 하면 for문이 이상해지므로, 존재하는 key값을 담는 리스트를 생성 하기로 함 deep copy하기에는 2000만개의 데이터 memory가 겁남,,,
        // 또 단순 key만 필요하다면 hash table 방식으로 만들면 insert할때 속도가 느릴수도 있으니 단순 list로 한다.

        Long startTime = System.currentTimeMillis();
        List<Integer> duplicateKeyList = new ArrayList<>();
        for (Integer mariaKey : mariaTargetData.keySet()) {
               if(oracleOriginData.containsKey(mariaKey)) {
                   duplicateKeyList.add(mariaKey);
               }
        }
        System.out.println(String.format("5번째 값 : %d , 총 겹치는 크기 : %d",duplicateKeyList.get(5), duplicateKeyList.size())); // 5번째 값 : 100012 , 총 겹치는 크기 : 9449999
        Long endTIme = System.currentTimeMillis();
        System.out.println(endTIme-startTime); // 563
    }
}

0.5초만에 데이터 비교가 끝이 났다 ㄷㄷ 이건 분명히 해볼만한 방법이였다

그런데,, 2천만개를 내 코드에서 생성해서 만드나??.. 그리고 크기가 저정도가 아닐텐데,,

통계청 오라클 서버에서 2000만개를 가지고 또 Maria에서도 2천만개 정도의 데이터를 매번 가지고 와야 한다..

이게 oracle에서 업데이트가 되었는지 안되었는지를 확인할 방법이 없어서 하루에 이루어지는 모든 업데이트 트랜잭션에 대해서 이러한 작업(full select)을 해야한다..

메모리 타임 모두 너무 비효율적이라는 생각이 들었다…

3. 해당값 delete & insert

3-1 Auto Commit

양쪽에서 모두 full select을 하여 비교하는것이 부담스럽다면 메모리를 줄이기 위해 이전 방식을 응용하도록 해보자

먼저 2000만건에 대하여 delete를 진행 후 batch를 통한 insert 방법식은 어떨까?

upsert보다도 빠르고 좋을듯 하다.

여기서 고려할 점은 delete를 하고 insert를 하는 트랜잭션중에도 서비스가 원활하게 이루어지냐 였다.

당연히 이 트랜잭션 과정중 commit이 이루어지면 서비스에 문제가 발생할 것이다.

그렇기 때문에 먼저 sqlSessionFactory의 auto commit Option을 False로 변경해야할 필요가 있었다.

옵션은 이러하다.

SqlSession session = sqlSessionFactory.openSession(false);	 //false = Not autoCommit

보통 나는 mybatis를 사용할때 이 값을 true로 해서 사용한다. 매번 쿼리를 진행할 때마다 commit 을 하기 얼마나 불편한가,,,

3-2 이러면 해결 끝?

물론 더 고려할 것들이 있다.

Q(3-2-a) 지금은 내 api내에서만 CUD가 진행되지만, DELETE INSERT가 진행될때 다른곳에서 테이블에 접근하게 된다면 TABLE LOCK이 걸려 있을까?

그리고 자바 내에서도 SqlSession의 scope또한 중요해진다.

이러한걸 나도 지식적으로 잘 알지 못하므로,, 하나하나 테스트를 통해 느낌적으로 알아볼 예정이다.

(테스트 data : 4GB, 7,250,000 rows 크기의 데이터로 측정.)

3-3 auto Commit 종료후 테스트 결과

Q(3-2-a) 에 대한 결과는 생각보다 재미가 있었다.

• 작업1 : 725만개의 대한 테이블의 대한 Insert/Delete 작업
• 작업2 : 다른 Session에서의 CRUD 작업

Auto commit을 꺼둔 상태에서 작업1 을 수행 중 다른 Session에서 별도의 작업(CRUD) 은 문제없이 정상적으로 작동이 된다.

• 1. 작업1 중에 SELECT에 대한 질의는 작업1 의 중간과정 결과를 보여주는 것이 아닌 기존 가지고 있던 결과를 보여준다.
• 1. 작업1 중에 (작업2) 또한 가능하다 .
• 1. 작업1 중에 작업2 를 진행하고 SELECT을 하면 예상대로 이전과 같이 작업2 에대한 수행 결과가 나온다.

(그렇다면 데이터의 무결성은 어떻게 지켜진다는 말인가?? 여기서 pk값이 들어가고 작업1 에서도 pk에 대한 값이 들어간다면??? 또 작업1 을 upsert로 하면 될까? 딱히 그럴 필요는 없었다. )

• 1. 작업1 중에 작업2 가 수행되면 작업2 에 대한 결과가 반영되지만, 작업1 이 종료 되는 순간에 commit을 한다면 작업1 에 대한 결과를 Commit 한다.

ex) 기존테이블 값 table {1,2,3,4,5}

작업1 내용  : delete {1,2,3} insert {6} **[굉장히 대용량이라 계속 진행중이라 가정]**

작업2 내용  : insert {6,7,8,9,10}

순차적 실행 결과 (Auto commit은 꺼둔상태)
**select * from table = {1,2,3,4,5,6,7,8,9,10}**

작업1 내용 종료 후 commit 결과

**selet * from table = {6}**

가 된다.

문제점 : 가끔 Delete할때 속도가 굉장히 느려지는 경우가 생겼다. 그렇다고 truncate 하긴 좀 그렇고,,, 뭔가 이전 하던 작업에서 락이 걸린 생태였나,,?

아무튼 뭔가 문제가 있었다.

4. Outter Join 으로 체크하여 삭제 하기

음 그다음 생각한것이 원본을 작업하다, 서버 자체가 끊어 진다 던지 하면,, 그 다음부터 동기화 시키는데 있어 너무 큰 부담이 있어,, 내가 다시 사이트를 가서 그부분 로그를 확인하고, 그 당시 시점을 확인해서 다시 동기화 API를 가동한다,,? 아찔,, 퇴사각 마렵다.. )

그렇기 떄문에 원본을 건드는 것이 아닌 새로운 COPY테이블에 새로운 데이터들을 저장한뒤, 이걸 비교 하여 없는 값들을 찾아 pk로 삭제 하는 방법또한 가능할 것 이라 생각이 되었다.

그래서 이러한 테스트도 한번 해보았다.

4-1. 테스트

• TEST DATA (4GB 7,981,080 row)

• TEST 방법 : 위 크기의 copy 테이블을 만들어 pk값을 변경한 300개 정도의 row를 찾는 테스트를 진행하고 시간을 측정

select * from (
	select tmp.r1 as itm2, tmp.r2, tmp.r3, tmp.r4, tmp.r5, origin.r1 as itm
	from temp_table as tmp -- oracle 데이터가 담긴 테이블
	right outer join 
	(	
	select * from origin_table -- 원본 테이블 
		where
		r1 = '119'
		and
		r2 = 'tbl'
		and
		r3 = 'Y' -- in으로 변경 해서 
		and
		r4 = 2000 -- in으로 변경 해서 

	) as origin on (
	copy.r1 = origin.r1
	and
	copy.r2 = origin.r2
	and
	copy.r3 = origin.r3
	and
	copy.r4 = origin.r4
	and
	copy.r5 = origin.r5
	)
	) as outresult
where
-- itm2 is null -- mysql data에는 없는 값, 즉 새로운 값 이건 어차피 upsert할때 추가된다.
-- or 
itm_rcgn_sn is null -- mysql data에만 있는 값, 삭제 되어야할 값
;

결과를 select 하는대 까지 10초정도의 시간이 소요 되었다.

여기선 col(itm) 이 null이면 삭제할 값, itm2가 null이면 추가할 값으로 생각할 수 있다. (하지만 추가할값은 앞에서 이미 upsert 하였음)

4-2 필요한 총 연산 시간

• new table에 단는 시간 (5만개씩 7,981,080 데이터를 담는 시간) 이건 어차피 어떤 방법을 쓰던 필요한 시간,

• new table 값을 upsert 시키는 시간 (origin table에 temp_table을 upsert하는 시간)

SET AUTOCOMMIT = 0; -- test후 rollback 필요하므로
INSERT INTO 
origin_table -- 1억9천 row들어 있음

(select * from new_table) 

ON DUPLICATE KEY UPDATE

co1 = VALUES(co1),
co2 = VALUES(co2),
co3 = VALUES(co3),
co4 = VALUES(co4),
co5 = VALUES(co5)
;

• new table에는 없고, origin_table에는 있는 row들을 detach하는 과정
  • (테스트에서는 190초 ) 300개가 다른 값으로 있었음
  • 만약 300개가 아니라 만개가 넘어갈 경우 아래 와 같이 삭제

     DELETE FROM TABLEB B
     WHERE (B.COL1,B.COL2,B.COL3) in ( select * from 조인결과 )
     FROM TABLEA