주문 등록 API를 클라우드로 옮긴 뒤 한 건에 90ms가 걸리고 타임세일마다 커넥션 풀이 바닥난다

실전INSERT한빛유통589 / 700

상황

한빛유통 쇼핑몰의 「주문 등록」 API는 지난달 API 서버를 클라우드로 옮긴 뒤 한 건에 평균 90ms가 걸린다(옮기기 전 8ms). 저녁 8시 타임세일에는 주문이 초당 140건, 순간적으로는 초당 450건까지 몰리면서 커넥션 풀(서버 한 대에 40개)이 바닥나 연결 대기 시간 초과가 난다. DB CPU는 20%도 쓰지 않는다. 개발팀은 세 가지 안을 냈다. ① 커넥션 풀을 150개로 늘린다. ② 지금 코드를 두고 SQL을 합치고 JDBC 배치와 RETURNING으로 호출 수를 줄인다. ③ 주문 등록 전체를 프로시저 하나로 옮겨 한 번에 부른다. 박팀장이 숫자로 견주어 하나를 고르고 고른 안의 대가도 적어 달라고 했다.

테이블과 인덱스

T_ORD: 100,000행 · PK_T_ORD (ORD_SEQ) · 주문 번호는 시퀀스 SEQ_ORD(CACHE 1000)로 매긴다
T_ORD_DET: 200,000행 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3줄(타임세일은 평균 2줄)
M_CUS: 2,000행 · PK_M_CUS · 등급 CUS_GD / M_ITM: 100행 · PK_M_ITM · 단가 UNT_PRC
등급 할인 규칙(discount)은 API 서버의 자바 코드에 있고, 장바구니 화면의 예상 금액도 같은 코드로 계산한다.
API 서버와 DB의 왕복은 약 9ms다(옮기기 전 0.4ms). 주문 한 건이 한 트랜잭션이다.

원본 SQL

sql

// OrderRegisterService.java : 주문 한 건 (상품 2줄이면 DB 호출 9번)
String gd = jdbc.queryForObject("SELECT CUS_GD FROM M_CUS WHERE CUS_ID = ?", String.class, cusId);        // ①
long amt = 0;
for (CartLine c : cart) {                                                                                    // ② 줄마다
    c.prc = jdbc.queryForObject("SELECT UNT_PRC FROM M_ITM WHERE ITM_ID = ?", Long.class, c.itmId);
    amt += c.prc * c.qty;
}
long seq = jdbc.queryForObject("SELECT SEQ_ORD.NEXTVAL FROM DUAL", Long.class);                              // ③
jdbc.update("INSERT INTO T_ORD (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT) VALUES (?, ?, SYSDATE, 'PENDING', ?)",
            seq, cusId, discount(gd, amt));                                                                  // ④
int no = 0;
for (CartLine c : cart) {                                                                                    // ⑤ 줄마다
    jdbc.update("INSERT INTO T_ORD_DET (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC) VALUES (?, ?, ?, ?, ?)",
                seq, ++no, c.itmId, c.qty, c.prc);
}
Map<String, Object> saved = jdbc.queryForMap(
    "SELECT ORD_SEQ, ORD_DT, ORD_AMT FROM T_ORD WHERE ORD_SEQ = ?", seq);                                   // ⑥ 화면에 돌려줄 값
// ⑦ COMMIT (트랜잭션 끝)

실행계획 학습용 예시

xplan

[주문 한 건 (상품 2줄) · APM 구간]
  호출     문장                                   DB 안       왕복 대기
  ①        SELECT CUS_GD FROM M_CUS ...           0.10 ms     9.0 ms
  ② × 2    SELECT UNT_PRC FROM M_ITM ...          0.10 ms     9.0 ms × 2
  ③        SELECT SEQ_ORD.NEXTVAL FROM DUAL       0.05 ms     9.0 ms
  ④        INSERT INTO T_ORD ...                  0.30 ms     9.0 ms
  ⑤ × 2    INSERT INTO T_ORD_DET ...              0.20 ms     9.0 ms × 2
  ⑥        SELECT ORD_SEQ, ORD_DT, ORD_AMT ...    0.10 ms     9.0 ms
  ⑦        COMMIT                                 0.60 ms     9.0 ms
  합계     9번                                     약 1.8 ms   81 ms     → 주문 한 건 약 90 ms (나머지는 API 서버 안)

[타임세일 20:00 ~ 20:10 · 인스턴스]
  주문 84,000건 · execute count 초당 1,120 · SQL*Net roundtrips 초당 1,270 · DB CPU 18%
  한 건이 연결을 90 ms 쥔다 → 초당 140건이면 동시에 12.6개, 순간 초당 450건이면 40.5개 (풀 40개)

문제

DB 안의 일은 2ms도 안 되는데 한 건에 90ms가 걸리고 풀이 바닥나는 이유를 위 숫자로 설명하시오.
세 안을 호출 수, 한 건의 시간, 동시에 쥐는 연결 수로 견주어 하나를 고르고, 고른 안을 코드로 쓰시오.
고른 안과 고르지 않은 안의 대가를 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

// OrderRegisterService.java : ② 안 · 주문 한 건에 DB 호출 4번
// [1] ①+② : 고객 등급과 상품 단가를 한 문장으로 (줄 수 1 ~ 3 → 문장 텍스트 셋)
List<Map<String, Object>> ref = jdbc.queryForList(
    "SELECT c.CUS_GD, i.ITM_ID, i.UNT_PRC FROM M_CUS c, M_ITM i "
  + "WHERE c.CUS_ID = ? AND i.ITM_ID IN (" + marks(cart.size()) + ")", args(cusId, cart));

// [2] ③+④+⑥ : 번호는 INSERT 안에서 NEXTVAL로, 화면에 돌려줄 값은 RETURNING으로
OraclePreparedStatement ins = conn.prepareStatement(
      "INSERT INTO T_ORD (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT) "
    + "VALUES (SEQ_ORD.NEXTVAL, ?, SYSDATE, 'PENDING', ?) "
    + "RETURNING ORD_SEQ, ORD_DT, ORD_AMT INTO ?, ?, ?").unwrap(OraclePreparedStatement.class);
ins.setString(1, cusId);
ins.setLong(2, discount(gd, amt));                 // 할인 규칙은 자바 한 곳 그대로
ins.registerReturnParameter(3, Types.NUMERIC);
ins.registerReturnParameter(4, Types.DATE);
ins.registerReturnParameter(5, Types.NUMERIC);
ins.executeUpdate();
ResultSet r = ins.getReturnResultSet();
r.next();
long seq = r.getLong(1);

// [3] ⑤ : 상품 줄은 한 번에
PreparedStatement det = conn.prepareStatement(
    "INSERT INTO T_ORD_DET (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC) VALUES (?, ?, ?, ?, ?)");
int no = 0;
for (CartLine c : cart) {
    det.setLong(1, seq); det.setInt(2, ++no); det.setString(3, c.itmId); det.setInt(4, c.qty); det.setLong(5, c.prc);
    det.addBatch();
}
det.executeBatch();

// [4] ⑦ : 커밋 (주문마다 한 번)
conn.commit();

개선 후 실행계획 학습용 예시

xplan

[주문 한 건 (상품 2줄) · 세 안 비교 (학습용 예시)]
  안                     DB 호출   왕복 대기   한 건     동시 연결 (초당 140 / 순간 450)   풀
  지금                     9번      81 ms     90 ms        12.6개 / 40.5개              40개 · 바닥남
  ① 풀 150개               9번      81 ms     90 ms        12.6개 / 40.5개             150개 · DB 세션 4배
  ② 합치기 · RETURNING      4번      36 ms     40 ms         5.6개 / 18.0개              40개 그대로
  ③ 프로시저 한 번           1번       9 ms     12 ms         1.7개 /  5.4개              40개 그대로

[② 반영 뒤 타임세일 10분]  SQL*Net roundtrips 초당 1,270 → 560 · 연결 대기 시간 초과 0건 · DB CPU 18% → 16%

모범 답안 서술

원인: 주문 한 건이 DB를 9번 부른다(고객 등급 1번, 상품 단가 2번, 주문 번호 1번, 주문 INSERT 1번, 상품 줄 INSERT 2번, 되읽기 1번, 커밋 1번). DB 안의 일은 다 합쳐 약 1.8ms인데, 왕복 한 번이 9ms라 오가는 데만 81ms를 쓴다. 옮기기 전에는 왕복이 0.4ms라 같은 9번이 3.6ms였다. 문제는 일의 양이 아니라 호출 수 × 왕복 지연이다. 그리고 그동안 연결을 쥐고 있다. 한 건이 연결을 90ms 쥐면 초당 140건에 동시에 12.6개, 순간 초당 450건이면 40.5개가 필요해서 풀 40개가 바닥난다. DB CPU가 18%인 것도 같은 까닭이다. DB는 바쁜 것이 아니라 기다리고 있다. 세 안 비교: ① 풀을 150개로 늘리면 연결 대기는 사라지지만 한 건은 여전히 90ms이고, 연결을 쥐는 시간도 그대로다. 서버 네 대면 DB 세션이 600개가 되어 DB 서버의 프로세스와 메모리만 는다. 문제를 가릴 뿐 줄이지는 않는다. ② 지금 코드를 두고 호출을 합치면 9 → 4번이 된다. 고객 등급과 단가는 한 문장으로 읽고, 주문 번호는 INSERT 안의 SEQ_ORD.NEXTVAL로 매기고 RETURNING으로 돌려받아 번호를 받는 호출과 되읽는 호출을 없앤다. 상품 줄은 addBatch로 한 번에 넣는다. 한 건은 약 40ms, 동시 연결은 5.6개(순간 18개)로 풀 안에 든다. ③ 프로시저로 옮기면 호출이 1번(커밋까지 블록 안에서)이라 약 12ms, 동시 연결은 1.7개(순간 5.4개)다. 고른 안: ②를 고른다. 풀 문제를 푸는 데 충분하고, 할인 규칙이 자바 한 곳에 그대로 남는다. 커밋은 주문마다 한 번 해야 하므로 주문끼리 묶을 수는 없고, 커밋도 왕복 한 번이라 4번 가운데 하나다. 대가는 코드가 오라클 전용 API(registerReturnParameter)에 기대게 된다는 것과, 상품 줄 수(1 ~ 3)에 따라 단가 조회 문장이 셋으로 나뉜다는 것이다(문장은 셋뿐이라 하드 파싱 걱정은 없다). ③의 대가: 호출은 가장 적지만 할인 규칙을 PL/SQL로 옮겨야 한다. 장바구니 화면은 같은 규칙을 자바로 계산하므로 규칙이 두 곳으로 나뉘고, 등급 할인이 바뀔 때마다 둘을 같이 고쳐야 한다. 배포와 시험도 DB 코드에 묶이고 장바구니 배열을 넘길 SQL 타입이 필요하다. 왕복이 더 길어지거나(다른 지역 클라우드) ②로도 풀이 모자라면 그때 ③을 다시 따진다.

해설

클라우드로 옮기면 SQL은 그대로인데 왕복 한 번이 몇 배에서 수십 배로 길어진다. 옮기기 전에 화면 하나, API 하나가 DB를 몇 번 부르는지 세어 두면 옮긴 뒤의 시간을 미리 셈할 수 있다(호출 수 × 왕복 시간).
RETURNING은 표준 JDBC에서도 쓸 수 있다. prepareStatement(sql, new String[] { "ORD_SEQ", "ORD_DT" })로 준비하고 getGeneratedKeys()로 받으면 드라이버가 RETURNING … INTO를 붙여 보낸다.
시퀀스 CACHE 1000은 그대로 둔다. 번호를 INSERT 안에서 매겨도 시퀀스를 부르는 횟수는 같고, 줄어드는 것은 따로 오가던 왕복이다.
③을 고른다면 BEGIN PKG_ORD.REGISTER(…); COMMIT; END;처럼 커밋까지 블록 안에 넣어야 호출이 정말 한 번이 된다. 커밋을 자바에서 따로 하면 두 번이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황