SQL 채점 서비스 부하 테스트로 발견한 Deadlock과 성능 병목 해결기

서비스: https://querify.dev

기존 서비스 아키텍처

운영 중인 Querify 서비스는 저희 집에 직접 구축한 미니PC 기반 온프레미스 서버에서 운영되고 있었습니다.

조금 어지럽긴 하지만.. 서비스 아키텍처입니다!

Spring Boot API 서버가 클라이언트 요청을 받으면, 일반 조회는 메인 MySQL에서 처리하고, 채점 요청은 별도의 Sandbox MySQL에서 사용자 SQL을 실행하는 구조입니다. Sandbox DB는 채점마다 테이블을 생성하고 테스트 데이터를 삽입한 뒤, 사용자 쿼리를 실행하여 정답과 비교합니다.

설계하면서 가장 많이 고려한 점이 채점 로직입니다. 사용자가 제출한 SQL을 메인 DB에서 직접 실행하면 실제 서비스 데이터를 훼손할 수 있고, 무거운 쿼리가 다른 API의 응답 시간에 영향을 줄 수 있습니다.

 

채점 SQL 격리 및 위험 쿼리 제한

• Sandbox DB를 분리함으로써 채점용 SQL 실행이 서비스 데이터에 영향을 주지 않도록 격리하고, 메인 DB의 커넥션 풀을 채점 요청이 점유하지 않도록 부하를 분산했습니다.
• 또한 Sandbox DB에 접근하기 이전에 JsqlParser를 이용해 SELECT문만 가능하도록 필터링했습니다.

fun validate(query: String) {
    val statement = try {
        CCJSqlParserUtil.parse(query)
    } catch (e: Exception) {
        throw BusinessException(GradingErrorCode.INVALID_SQL_SYNTAX)
    }

    if (statement !is Select) {
        throw BusinessException(GradingErrorCode.FORBIDDEN_SQL_STATEMENT)
    }
}

• Sandbox권한은 readonly로 설정하여 SELECT 요청만 허용하도록 했습니다.

CREATE USER 'sandbox_readonly'@'%' IDENTIFIED BY 'sandbox_readonly';
GRANT SELECT ON sandbox.* TO 'sandbox_readonly'@'%';
FLUSH PRIVILEGES;

• 마지막으로 기존 존재하던 ELK 스택에서 채점 요청만 모아서, 매일 정해진 시간에 SQL Injection 의심 쿼리를 디스코드로 발송하는 n8n 워크플로우를 만들었습니다. 이를 통해 지속적으로 의심 쿼리를 제출하는 사용자를 탐지하고 제재할 수 있습니다.

 

부하테스트 배경

사용자가 늘어난다면 채점 요청에 병목이 발생하지 않을까?

 

SQL 채점 서비스는 여러 사용자가 동시에 문제를 풀고 쿼리를 실행하는 환경입니다. 사용자가 제출 버튼을 눌렀는데 채점이 늦게 되거나 서비스가 멈춘다면 학습 경험이 크게 떨어질 수 있습니다.

특히 SQL 채점은 단순 요청이 아니라 DB 실행, 결과 비교, 채점 로직 처리까지 포함되기 때문에 동시에 많은 요청이 들어올 경우 서버와 데이터베이스에 큰 부하가 발생할 수 있습니다.

따라서 실제 사용자들이 동시에 문제를 제출하는 상황을 가정하여 부하테스트를 진행했고, 이를 통해 병목이 발생하는 지점을 미리 파악하고 개선하고자 했습니다.

 

부하테스트

 

부하테스트 환경

부하테스트는 AWS EC2 + k6 환경에서 진행했습니다. 기존 미니PC에서는 다른 프로그램도 동작하고 있었고, 부하테스트 중 서버가 다운되더라도 빠르게 복구할 수 있는 EC2 환경이 더 적합하다고 판단했습니다.

인스턴스는 미니PC와 비슷한 스펙의 t4g.medium을 고려했으나, 더 작은 환경에서도 버틸 수 있다면 프로덕션에서의 안정성을 더 높은 수준으로 보장할 수 있을 것 같아 t4g.small(vCPU 2개, 메모리 2GB)을 선택했습니다.

첫 부하테스트는 퍼블릭 IP를 통해 요청을 보냈기 때문에 측정된 latency에 네트워크 구간이 포함되어 있습니다. 이번 부하테스트의 목적은 서버 내부 병목을 더 정확히 측정하는 것이였기 때문에, 같은 VPC 내 프라이빗 IP로 요청하는 것이 적절하다는 것을 알게되었습니다. 다음부터는 부하 테스트 목적에 맞게 적절한 네트워크 환경을 구축해야겠다는 점을 알게 되었습니다.

EC2에 데이터를 마이그레이션한 후, Prometheus + Grafana로 모니터링 대시보드를 구성했습니다. Spring Boot Actuator + Micrometer를 통해 HTTP 응답 시간(p95/p99), RPS, 에러율과 함께 HikariCP 커넥션 풀 상태(active/pending)를 수집했습니다.

 

1차 부하테스트: 고부하 (초당 30~100 요청)

k6 스크립트는 두 가지 시나리오로 구성했고 타깃 API는 문제 전체 조회와 제출, 제출 조회입니다.

export const options = {
  scenarios: {
    // 시나리오 1: 평상시 트래픽 (3분)
    normal_load: {
      executor: 'constant-arrival-rate',
      rate: 30,            // 초당 30 요청
      timeUnit: '1s',
      duration: '3m',
      preAllocatedVUs: 50,
      maxVUs: 100,
    },
    // 시나리오 2: 스파이크 (3분)
    exam_end_spike: {
      executor: 'ramping-arrival-rate',
      startRate: 30,
      timeUnit: '1s',
      stages: [
        { duration: '30s', target: 100 },  // 30초간 3배 급증
        { duration: '2m', target: 100 },   // 2분간 피크 유지
        { duration: '30s', target: 30 },   // 정상화
      ],
      preAllocatedVUs: 200,
      maxVUs: 300,
      startTime: '3m',
    },
  },
};

평상시 초당 30 요청을 3분간 유지한 뒤, 초당 100 요청으로 급증시키는 시나리오입니다. 총 6분간의 테스트를 진행하려는 도중 스파이크 구간에서 서버가 다운되었습니다.

 

지표	측정값
p95 latency	4,901ms
p99 latency	10,000ms (타임아웃)
Error rate	26.28%
RPS	41.8/s
총 처리 건수	8,072건 (중간에 다운)

 

부하테스트 결과 분석

 

실행계획 분석

가장 먼저 의심한 것은 DB 쿼리입니다. 채점 결과 조회는 가장 자주 호출되는 API 중 하나이므로, 이 쿼리에 EXPLAIN ANALYZE를 실행해보았습니다.

기존 인덱스 현황은 다음과 같았습니다.

인덱스명	구성
PRIMARY	id
idx_user_id	user_id
idx_problem_user	problem_id, user_id
idx_deleted_at	deleted_at

 

(다른 용도로 만든 인덱스) idx_problem_user(problem_id, user_id)로 problem_id 필터는 성공했지만, created_at 정렬을 커버하지 못했습니다.

자세히 살펴보면, 채점 결과 조회 요청에 페이지네이션을 적용하여서 최신 20건만 가져오면 되는데 인덱스에 정렬 정보가 없으니까 일단 전부(6,933행) 읽고 → 정렬하고 → 20개 자르는 비효율적인 방식으로 동작했습니다. 이는 데이터가 쌓일수록 느려지는 구조였습니다.

 

커넥션 풀 포화

Grafana를 확인해보니, HikariCP의 pending(커넥션 대기 수)이 180건 이상으로 치솟는 것이 보였습니다. 기본 커넥션 풀 크기인 10개로는 동시 요청을 감당하기 어려웠고, 커넥션을 얻지 못한 스레드가 기본 타임아웃 30초 동안 묶이면서 톰캣 스레드 풀까지 연쇄적으로 고갈되면서 최종적으로 서버가 다운되었습니다.

max-pool-size 기본값 10개에 동시 요청이 몰리면, 10개의 커넥션이 전부 사용 중일 때 나머지 요청은 대기합니다. 이때 connection-timeout 기본값이 30초이므로, 커넥션을 못 얻은 스레드가 30초 동안 묶이면서 톰캣 스레드 풀까지 연쇄적으로 고갈, 최종적으로 서버가 다운된 것이었습니다.

 

인덱스, 커넥션 풀 튜닝

 

Index 추가

최신 20건을 판단하는 방법이 created_at이였기 때문에 해당 컬럼에 인덱스가 필요했습니다. 따라서 문제와 제출 시점을 순서로 idx_submission_problem_created 복합 인덱스를 생성했습니다.

추가로 커버링 인덱스도 검토했으나 현재 조회가 전체 컬럼을 반환하기 때문에 커버링 인덱스를 구성하려면 모든 컬럼을 인덱스에 포함해야 하며, 이는 인덱스 크기가 테이블과 거의 동일해져 오히려 INSERT 성능을 저하시킨다고 판단하고 배제했습니다.

 

커넥션 풀 사이즈와 타임아웃 수정

DB 커넥션 풀 사이즈는 HikariCP wiki의 풀 사이징 가이드라인 connections = ((core_count * 2) + effective_spindle_count)을 참고하여 튜닝했습니다. vCPU 2개 기준으로 약 5개가 권장값이지만, 채점 요청이 sandbox DB에서 SQL을 실행하는 동안 커넥션을 오래 점유하는 I/O 바운드 특성을 감안하여 20으로 설정했습니다.
한편 커넥션을 획득하지 못한 요청이 장시간 대기하며 톰캣 스레드를 점유하지 않도록 connectionTimeout을 3초로 설정해 빠르게 실패하도록 구성했습니다. 이를 통해 커넥션 풀 고갈 상황이 전체 요청 처리 지연으로 확산되는 것을 방지했습니다.

 

결과

인덱스 추가로 쿼리 실행 시간이 26.2ms에서 0.05ms로 99.8% 단축되고, 스캔 행 수 6,933행에서 20행으로 감소하였습니다.

RPS는 41.8/s에서 84.7/s로 103% 향상, 총 처리 건수도 8,072건에서 15,351건으로 90% 증가하였으며,
p95 latency는 4,901ms에서 3,536ms로 28% 개선, p99는 10,000ms(타임아웃)에서 4,830ms로 52% 개선되었습니다.

 

SubmissionCount 증가 문제 해결

인덱스 추가와 커넥션 풀 튜닝을 적용한 뒤, 동일한 시나리오로 부하테스트를 재진행했습니다.

지표	Before	After (인덱스+풀)
p95 latency	4,901ms	3,536ms
p99 latency	10,000ms (타임아웃)	4,830ms
median	92ms	888ms
성공 요청 median	45ms	596ms
Error rate	26.28%	31.02%
RPS	41.8/s	84.7/s
처리 건수	8,072건 (다운)	15,351건 (생존)

 

서버가 다운되지 않고 끝까지 생존했고, RPS가 41.8에서 84.7로 약 2배 향상되었습니다. 에러율이 31%로 오히려 높아진 것처럼 보이지만, Before에서는 서버가 중간에 다운되어 이후 요청이 집계되지 않은 반면 After에서는 끝까지 살아남아 2배 많은 요청을 처리한 결과입니다.

😱 그러나 에러율 31%는 여전히 높았습니다. 인덱스와 커넥션 풀 외에 다른 병목이 있다는 의미입니다.

앱 에러 로그를 분석한 결과, Deadlock이 다수 발생하고 있었습니다.

MySQLTransactionRollbackException: 
Deadlock found when trying to get lock; try restarting transaction

at ProblemRepositoryImpl.incrementSubmittedCount()

원인은 채점 제출 시 호출되는 incrementSubmittedCount()에 있었습니다. 이 메서드는 매 요청마다 problem 테이블의 동일 행에 UPDATE SET submissionCount = submissionCount + 1을 실행합니다. 

 

원인은 InnoDB의 FK 제약 체크 메커니즘에 있었습니다. INSERT INTO submissions 실행 시, InnoDB는 FK로 참조하는 problem 행의 존재를 확인하기 위해 해당 행에 S Lock(공유 락)을 자동으로 설정합니다. S Lock끼리는 호환되므로 동시에 여러 트랜잭션이 같은 problem 행에 S Lock을 획득할 수 있습니다. 문제는 그 다음입니다. 각 트랜잭션이 UPDATE problem SET submissionCount = submissionCount + 1을 실행하려면 같은 행에 X Lock(배타 락)이 필요한데, 상대방이 이미 잡고 있는 S Lock이 해제될 때까지 기다려야 합니다. 두 트랜잭션이 서로의 S Lock 해제를 기다리는 순환 대기가 형성되면서 Deadlock이 발생한 것입니다.

Deadlock 시퀀스 다이어그램

트랜잭션 A:
  1. INSERT INTO submissions
     → FK 제약 체크를 위해 problem 행에 S Lock 획득 ✅
  2. UPDATE problem SET submissionCount = submissionCount + 1
     → 같은 problem 행에 X Lock 요청 → B의 S Lock 때문에 대기

트랜잭션 B:
  1. INSERT INTO submissions
     → FK 제약 체크를 위해 같은 problem 행에 S Lock 획득 ✅
     (S Lock끼리는 호환되므로 둘 다 성공)
  2. UPDATE problem SET submissionCount = submissionCount + 1
     → 같은 problem 행에 X Lock 요청 → A의 S Lock 때문에 대기

A는 B의 S Lock 해제를 기다리고, B는 A의 S Lock 해제를 기다림
→ 순환 대기 발생 → Deadlock

단위 테스트나 코드 리뷰에서는 발견하기 어려운 문제였습니다. 실제로 동시 요청이 몰리는 환경에서만 드러나는 동시성 버그였고, 부하 테스트를 하지 않았다면 프로덕션에서 처음 마주쳤을 것으로 예상합니다.

 

Dead Lock 해결

처음엔 실시간 COUNT 쿼리로 대체하는 방법을 고려했습니다. submissionCount 컬럼을 제거하고 필요할 때마다 SELECT COUNT(*)로 직접 계산하는 방법입니다. UPDATE 자체가 사라지므로 Deadlock은 해결이 됩니다. 다만 제출 데이터가 쌓일수록 COUNT 비용이 증가하고, 문제 목록에서 문제 20개의 문제에 제출 수를 보여주려면 COUNT를 20회 실행해야 한다. 데이터가 수십만 건 이상 쌓이면 목록 조회 한 번에 수 초가 소요될 수 있어 장기적으로는 좋지 않은 판단이라고 생각했습니다.

Kafka 등 메시지 큐를 이용해 비동기 처리 하는 방법도 있었습니다. Deadlock 없이 정확한 카운트를 유지할 수 있지만, 정수 하나를 증가시키기 위해 메시지 브로커를 경유하는 것은 현재 상황에선 오버엔지니어링이라고 생각했습니다.

 

채택된 방법은 Redis입니다. Redis의 INCR는 원자적으로 처리되므로, 동일 키에 대한 동시 증가 요청을 안전하게 누적할 수 있었습니다. 부가적으로 응답 시간이 1ms 미만이라 사용자 체감 지연도 없으며, 구현도 기존 UPDATE문 한 줄을 Redis INCR 한 줄로 교체하는 수준이어서 변경 범위가 최소화됩니다.

제출 수 증가 방식 비교

특히 제출 건수는 실제 제출 레코드에서 언제든 정확한 값을 복구할 수 있는 통계성 데이터이므로, 강한 정합성 대신 최종 정합성으로 충분하다고 판단했습니다.

 

Redis로 제출 횟수를 저장하는 구조 설계

• 누적된 카운트는 스케줄러가 1분 주기로 DB에 동기화합니다.
• 이때 get → DB 반영 → decrement 순서로 처리하여, DB 업데이트가 실패하더라도 Redis에 값이 남아 다음 주기에 재시도되도록 설계했습니다.
• getAndDelete로 한 번에 가져오고 삭제하는 방식도 검토했으나, Redis에서 삭제 후 DB 반영 전에 서버가 다운되어있다면 카운트가 유실되는 문제가 예상되어 배제했습니다.

Redis INCR 적용 후 동일 시나리오로 부하테스트를 재진행했는데요, Deadlock이 제거되어 에러율이 31%에서 19%로 감소했습니다. 성공한 요청의 median latency는 596ms에서 18ms로 97% 개선됐습니다.

 

테이블 이름 중복 문제 해결

Deadlock 해결 후에도 에러율이 19%로 남아있어 앱 에러 로그를 다시 확인했습니다. Deadlock 예외는 사라졌지만, sandbox DB 관련 에러가 발생하고 있었습니다. QueryExecutor 코드를 확인한 결과, 모든 채점 요청이 같은 sandbox DB에서 동일한 테이블명으로 DROP → CREATE → INSERT → SELECT를 실행하는 구조였습니다. 동시에 채점 A가 테이블을 생성하고 데이터를 삽입하는 사이에 채점 B가 같은 테이블을 DROP하면서 충돌이 발생한 것입니다.

문제는 에러 뿐만 아니라 데이터 정합성이였습니다. 기존 구조에서는 동시 채점 시 다른 사용자의 데이터가 섞여 채점 결과가 틀릴 수 있는 치명적 문제가 존재한 것입니다.

MySQL의 DDL은 일반 DML과 달리 implicit commit을 유발하거나 트랜잭션 원자성을 깨뜨릴 수 있어, 단순 트랜잭션 처리만으로는 동시 채점 간 DDL 충돌을 안전하게 제어하기 어려웠습니다.

 

 

해결 방안 탐색

락을 도입하기에는 채점 자체가 SQL 실행과 결과 비교를 포함하는 무거운 작업이라 직렬화 시 대기 시간이 크게 증가하여 부하 테스트의 개선 의미가 사라진다고 판단했습니다

 

채점 요청마다 새 Docker 컨테이너를 띄워 완전히 격리하는 방법도 고려했으나, 컨테이너 기동 오버헤드가 수 초 단위로 발생하고 메모리 점유량도 커서 현재 인프라에서는 적합하지 않았습니다. 다만 컨테이너 기반 샌드박스는 보안 격리 측면에서 장점이 있으므로, 향후 스키마 분리 방식과 장단점을 비교하여 도입을 검토할 계획입니다.

 

최종적으로 채점 요청마다 고유한 스키마를 동적으로 생성하여 물리적으로 격리하는 방식을 선택했습니다. 각 채점은 자신만의 스키마에서 테이블 생성, 데이터 삽입, SQL 실행을 수행하고 완료 후 스키마를 삭제합니다. 서로 다른 공간에서 작업하므로 동시 100명이 채점해도 충돌이 구조적으로 발생하지 않습니다.

 

수정 후 마지막으로 부하테스트를 진행하였습니다.

지표	Before	After
median	92ms	26ms
성공 요청 median	45ms	17ms
Error rate	26.28%	15.13%
RPS	41.8/s	48.5/s
Deadlock	다수	0건
sandbox 충돌	발생	0건
서버 상태	다운	생존

 

수치만 비교하자면 첫 결과에서 드라마틱한 차이는 보이지 않습니다. 이는 첫 부하테스트에는 스파이크 테스트 직후 다운되어 처리되지 못한 요청이 많다는 점을 감안해야합니다. 인덱스, 커넥션 풀, 원자적 증가, schema 분리 등의 튜닝 후 정확성과 안정성 모두 확보하면서, 성능까지 개선된 성과가 있습니다.

 

스키마 풀

지금까지의 개선을 통해 서버 다운, Deadlock, sandbox 충돌 문제는 해결할 수 있었고 성공 요청 latency도 크게 개선되었습니다. 하지만 최종 부하 테스트에서도 에러율은 15%가 남아 있었습니다.

남아 있던 에러율의 주요 원인은 채점 작업의 동시 실행 수가 제어되지 않는 점에 있었습니다. 지금 방식에서는 적당한 동시 실행 개수를 구하는 공식이 따로 없기 때문에 확인해야할 부분이였습니다.

 

또한 기존 방식은 DDL 비용이 매 채점마다 반복된다는 한계가 있었습니다. 따라서 동시 실행 수를 제한하는 것만으로는 남아 있던 오버헤드를 줄이기 어렵다고 판단했고, 이를 해결하기 위해 미리 생성한 sandbox 스키마를 재사용하는 pool 구조를 선택했습니다. 따라서 Kafka consumer 측에서 동시 실행 가능한 채점 수를 sandbox 개수와 동일하게 제한했습니다.

스키마 풀 구조

val schemaName = "sandbox_${Thread.currentThread().id}_${System.nanoTime()}"
...
try {
    adminDataSource.connection.use { conn ->
        conn.createStatement().execute("DROP DATABASE IF EXISTS `$schemaName`")
    }
}

val schemaName = schemaPool.acquire()
...
try {
    schemaPool.release(schemaName)
}

 

앱 기동 시 sandbox_pool_0 ~ sandbox_pool_N 스키마를 미리 생성해두고 ArrayBlockingQueue로 관리합니다. 채점 요청이 들어오면 acquire()로 유휴 스키마를 빌려 채점을 수행하고, 완료 후 release()로 반납합니다. CREATE/DROP DATABASE는 기동 시 1회만 수행되며, 이후 채점은 이전 테이블을 DROP하고 새로 생성하는 테이블 레벨 초기화만 수행합니다. 또한 Kafka consumer의 concurrency를 풀 사이즈와 동일하게 설정해, 풀이 고갈될 상황 자체를 구조적으로 차단했습니다.

그 결과 채점 실패율이 15% → 4.64% 로 감소했고, 전체 처리 건수도 무려 21,231으로 증가했습니다.

지표	스키마 생성 방식	스키마 풀 방식
Error rate	15.13%	4.64%
RPS	48.5/s	70.31/s

 

느낀 점 & 회고

우선 부하테스트를 할 때 같은 VPC내에서 진행하지 못한 점이 아쉬웠습니다. 이 경험을 통해 다음에는 부하테스트 목적에 맞게 환경을 구성할 수 있도록 노력할 예정입니다. 커넥션 풀 사이즈도 테스트해보고 개선이 되었다고 느꼈는데, 공식을 적용해보거나 임의의 숫자로 조정해보며 공식이 의미가 있는지 그리고 몇개가 가장 최적값인지 확인해보았더라면 더 좋을 것 같습니다!

 

코드만으로는 발견하기 어려운 운영 환경의 문제들을 간접적으로 경험할 수 있어 의미 있는 경험이었습니다. 문제를 발견했을때, 해결 방법을 가설로 세우고 여러 가지 시도를 해보며 어떤 방법이 가장 최선이였는지를 판단하는 과정들이 뿌듯했습니다.

아직 많이 부족하지만 읽어주셔서 감사합니다! 어색한 부분이나 더 좋은 의견이 있다면 편하게 댓글 남겨주세요. :)