온라인 DDL 3

온라인 DDL 2에서 본 것처럼, DDL(Data Definition Language, 데이터 정의어)을 막는 것은 대개 다른 세션의 쿼리나 끝나지 않은 트랜잭션(transaction)이다. 이 문서는 운영 중에 지금 무엇이 돌고 있는지를 보는 프로세스 목록(process list)과, 커밋(commit)하지 않고 열려 있는 트랜잭션을 찾는 조회를 다룬다. DDL 직전 점검과 장애 대응에서 같은 조회를 쓴다.

실행 중인 쿼리 — SHOW PROCESSLIST

SHOW FULL PROCESSLIST;
-- 또는 조건을 걸어 조회
SELECT id, user, db, command, time, state, info
FROM performance_schema.processlist
WHERE command <> 'Sleep' ORDER BY time DESC;

MySQL은 접속 하나마다 스레드 하나를 두고, 프로세스 목록은 지금 각 스레드가 무엇을 하는지 보여 준다. 주요 칼럼은 다음과 같다.

• Id: 접속 번호. 접속을 강제로 끊는 KILL 명령에 이 번호를 넘긴다.
• Command·Time: 스레드가 하는 일(Query는 실행 중, Sleep은 대기 중)과 그 상태로 머문 초.
• State: 세부 상태. Waiting for table metadata lock이면 메타데이터 락(metadata lock) 대기다.
• Info: 실행 중인 SQL. FULL을 빼면 앞 100자만 보인다.

다른 사용자의 스레드까지 보려면 PROCESS 권한(privilege)이 필요하다. SHOW PROCESSLIST의 기본 구현은 스레드 목록을 도는 동안 전역 뮤텍스(mutex)를 잡아 바쁜 서버에서 부담이 된다. Performance 스키마(Performance Schema)의 performance_schema.processlist 테이블은 뮤텍스 없이 같은 정보를 준다. 시스템 변수(system variable) performance_schema_show_processlist를 ON으로 바꾸면 SHOW PROCESSLIST도 이 테이블을 쓴다. 서버 메타데이터를 테이블로 보여 주는 INFORMATION_SCHEMA를 쓰는 구현은 8.4에서 폐기 예정(deprecated)이다.

함정: Sleep인데 트랜잭션은 열려 있다. 애플리케이션이 START TRANSACTION 뒤 SELECT 하나만 하고 커밋하지 않은 채 커넥션을 쥐고 있으면, 프로세스 목록에는 Sleep, Info는 NULL로만 보인다. 트랜잭션 안에서 한 번이라도 읽은 테이블의 메타데이터 락은 커밋이나 롤백(rollback)할 때까지 풀리지 않고, DDL은 그 테이블의 배타 메타데이터 락이 필요하다. 그래서 이 세션이 DDL을 막는 범인일 때가 많다. 그래서 프로세스 목록만 보지 말고 열린 트랜잭션을 따로 본다.

열린 트랜잭션 — innodb_trx

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id AS pid,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND
ORDER BY trx_started;

information_schema.innodb_trx는 InnoDB 안에서 지금 열려 있는 트랜잭션을 한 행씩 보여 준다. trx_started는 시작 시각, trx_state는 RUNNING·LOCK WAIT(잠금 대기)·ROLLING BACK 같은 상태, trx_rows_modified는 바꾼 행 수다. trx_mysql_thread_id가 프로세스 목록의 Id와 같아서 두 결과를 이어 볼 수 있다. trx_query가 NULL인데 오래된 트랜잭션이 있으면 앞에서 본 "트랜잭션을 연 채 Sleep으로 쉬는 세션"이다. DDL 전에는 이 조회로 오래 열린 트랜잭션이 없는지부터 본다.

이어지는 내용은 온라인 DDL 4에서 다룬다. 누가 누구를 막고 있는지 보는 잠금 대기 조회, 막는 쪽을 끊는 KILL, 오래 걸리는 ALTER의 진행률 확인을 본다.

면접 포인트

• "DDL 전에 무엇을 확인하나요?" → innodb_trx로 오래 열린 트랜잭션. 프로세스 목록의 Sleep만 보고 안심하지 않는다.
• "Sleep인데 DDL을 막을 수 있나?" → 트랜잭션을 연 채 쉬는 세션은 Info가 NULL이어도 잠금을 쥐고 있다. trx_mysql_thread_id로 프로세스 목록 Id와 잇는다.
• "SHOW PROCESSLIST 대신?" → performance_schema.processlist. 전역 뮤텍스 없이 같은 정보를 조건을 걸어 본다.