MySQL에서 DDL과 Metadata Lock, 장애와 자동화

실무에서 MySQL이 제공해주는 동시 요청 제어 컨트롤을 제대로 이해하지 못하고 유지보수 및 개발자분들의 요청을 처리하다가 장애를 맞은 경험이 있습니다.

다행히도, 서비스 구조상 DB 서버가 MSA로 잘게 나뉘어있어서 상품 목록을 불러오는 기능만 1분 정도 막히는 해프닝으로 넘어갔지만... 아직도 아찔한 경험입니다.

결론부터 말씀드리자면, 이 모든게 !메타데이터 락!에 대한 무지에서 시작되었습니다.

 

1. 먼저 메타데이터 락과 간단한 예제를 살펴보고
2. 이를 해결하는 방법을 소개드리고
3. Java + SpringBoot 코드로 이 과정을 자동화 하는 예제도 살펴봅시다

 

Metadata Lock

메타데이터 락은 MySQL 5.5 버전부터 생긴 개념입니다. 

MySQL 서버는 메타데이터 락을 통해서 데이터베이스 개체(프로시저, 함수, 테이블, 트리거 등)에 대한 concurrent 요청을 관리하고 데이터 일관성을 보장합니다.

메타데이터 락 자체가 무엇인지에 대한 설명은 공식문서(https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html)에 자세히 기재되어있습니다.

 

---

 

가상 예제로 이해해보겠습니다.

예제는 AWS RDS MySQL 8.0.32 버전에서 진행되었습니다.

 

1번 세션이 트랜잭션을 시작하고, SELECT 문으로 'asdf' 테이블에 대해 조회를 수행합니다.

COMMIT 은 아직 실행되지 않았습니다.

-- session 1
mysql> use test_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from asdf limit 1;
+----+-------+------------+-------------+-----------+------------+-------------+------------+------+
| id | test1 | test_test  | test_test2  | test234c  | test234c2  | test234c23  | test13123  | fff  |
+----+-------+------------+-------------+-----------+------------+-------------+------------+------+
|  0 | Test0 | Test_test0 | Test_test20 | Test234c0 | Test234c20 | Test234c230 | Test131230 | NULL |
+----+-------+------------+-------------+-----------+------------+-------------+------------+------+
1 row in set (0.02 sec)

 

2번 세션에서는. 개발자가 특정 컬럼 추가를 DBE에게 요청해서, 수행하는 시나리오입니다.

ALTER 문을 입력하고 Enter 키를 누르면, 터미널상에서는 열심히 수행되는 것처럼 보입니다.

-- session 2
mysql> use test_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table asdf add column ccecaf varchar(255);

 

3번 세션입니다. SHOW PROCESSLIST 명령어로 현재 서버 내에서 수행되고 있는 프로세스 목록을 조회합니다.

잘 진행되고 있는 줄로만 알았던 2번 세션(Id 2733)의 ALTER 명령이 'Waiting for table metadata lock' 이라는 State로 멈춰있습니다.

-- session 3
mysql> show processlist;
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+
| Id   | User            | Host               | db          | Command | Time   | State                           | Info                                            |
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+
|    5 | event_scheduler | localhost          | NULL        | Daemon  | 423063 | Waiting on empty queue          | NULL                                            |
|    8 | rdsadmin        | localhost:64914    | NULL        | Sleep   |      4 |                                 | NULL                                            |
| 2732 | admin           | 61.77.60.253:60280 | test_schema | Sleep   |     26 |                                 | NULL                                            |
| 2733 | admin           | 61.77.60.253:60282 | test_schema | Query   |     13 | Waiting for table metadata lock | alter table asdf add column ccecaf varchar(255) |
| 2734 | admin           | 61.77.60.253:60283 | test_schema | Query   |      0 | init                            | show processlist                                |
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+

 

이 상태에서 세션 4번이 생성되어 asdf 테이블에 SELECT 요청을 하면 어떻게 될까요?

예상되시겠지만, metadata lock이 해소될때 까지 대기하게 됩니다.

-- session 4
mysql> start transaction;
mysql> select * from test_schema.asdf limit 1;
-- 여기서 block됨

.
.
.

-- 다른 세션에서 processlist 조회
mysql> show processlist;
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+
| Id   | User            | Host               | db          | Command | Time   | State                           | Info                                            |
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+
|    5 | event_scheduler | localhost          | NULL        | Daemon  | 425051 | Waiting on empty queue          | NULL                                            |
|    8 | rdsadmin        | localhost:64914    | NULL        | Sleep   |     12 |                                 | NULL                                            |
| 2732 | admin           | 61.77.60.253:60280 | test_schema | Sleep   |   2014 |                                 | NULL                                            |
| 2733 | admin           | 61.77.60.253:60282 | test_schema | Query   |   2001 | Waiting for table metadata lock | alter table asdf add column ccecaf varchar(255) |
| 2734 | admin           | 61.77.60.253:60283 | test_schema | Query   |      0 | init                            | show processlist                                |
| 2742 | admin           | 61.77.60.253:60589 | test_schema | Query   |     11 | Waiting for table metadata lock | select * from test_schema.asdf limit 1          |
+------+-----------------+--------------------+-------------+---------+--------+---------------------------------+-------------------------------------------------+

 

이 block되는 현상을 해소하려면 어떻게 해야할까요?

메타데이터 락을 홀드하고 있는 세션이 종료되길 기다리거나, kill 해야합니다.

그런데 OLTP 성격의 서비스에서는 클라이언트의 요청이 초당 수십 수백건씩 몰려드는데, 당장 밀려오는 요청을 막아두고 앞서 시작된 롱 세션이 끝나길 무작정 기다려야 할까요?

상황에 따라 다르겠지만.. 대부분의 경우 클라이언트 세션을 kill 하는 것이 더 바람직할 것 같습니다.

그래서 DBE가 작업을 할 때 개발자분들에게 해당 시간에 실행되는 배치가 있다면 중단 요청을 드리는 편입니다.

중단할 수 없는 배치라면.... 배치가 없고 부하가 적은 새벽시간대를 잡아서 새벽작업을 해야죠 ㅠ_ㅠ

(아니면... 다른 도구 (pt-online-schema-change 등)를 사용해서 최대한 온라인에 가깝게 작업하는 방법도 있습니다. 개체가 클수록 시간이 너무 오래 걸리는게 단점이긴 합니다)

 

---

 

메타데이터 락은 두 경우에 의해 발생됩니다.

• 개체에 대한 변경작업을 시작할 때
• 개체에 대한 변경작업을 마무리할 때

개체에 대해 변경작업을 시작할 때 발생하는 메타데이터 락 대기현상은 ALTER 를 시도하는 세션을 kill 하면 됩니다.

반대로, 변경작업을 마무리할 때 발생하는 대기현상은 어떻게 대응해야 할까요? 

ALTER 세션을 kill 하는 것으로 대응이 될까요? -> 안됩니다. kill이 안됩니다....

결론은 메타데이터 락을 유발하는 세션을 kill 해야합니다.

 

 

 

block 되는 현상의 해결책으로, metadata lock holder session을 kill 하는 것으로 결정했습니다.

그렇다면 holder session을 어떻게 찾아야 할까요?

예제는 기껏해봐야 4개의 세션이 존재하지만 실무에서는 초당 수십개의 세션이 생성되었다가 해소됩니다.

show processlist를 통해 눈으로 확인하는 것은 너무 비효율적이고, 사실 불가능합니다... 힘들어요..

 

 

---

 

performance_schema 에서 threads 테이블과 processlist_info 테이블을 조합해서 어떤 세션이 메타데이터 락을 유발하고 있는지 확인해봅니다. 아까 살펴봤던 동일 예제입니다.

결과를 살펴보면, PROCESSLIST_ID 2732 세션이 메타데이터 락을 홀드하고 있는 상태입니다.

mysql> SELECT b.OBJECT_TYPE, b.OBJECT_SCHEMA, b.OBJECT_NAME,
    -> b.LOCK_TYPE, b.LOCK_STATUS, c.THREAD_ID, c.PROCESSLIST_ID,c.PROCESSLIST_INFO
    -> FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b
    -> ON a.OWNER_THREAD_ID<>b.OWNER_THREAD_ID AND a.OBJECT_NAME=b.OBJECT_NAME
    -> AND a.LOCK_STATUS = 'PENDING'
    -> JOIN performance_schema.threads c
    -> ON b.OWNER_THREAD_ID = c.THREAD_ID;
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE   | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+
| TABLE       | test_schema   | asdf        | SHARED_READ | GRANTED     |      2774 |           2732 | NULL             |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+

 

그럼 2732 세션을 kill 해봅시다.

-- session 3
mysql> kill 2732;
Query OK, 0 rows affected (0.01 sec)

-- session 2
mysql> alter table asdf add column ccecaf varchar(255);
Query OK, 0 rows affected (1 hour 3 min 10.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

세션 2732가 kill 되자마자

block 되어있던 alter 구문이 해소되는 것을 확인했습니다.

MySQL8 버전부터는 add column 이 instant 하게 처리되어서 순식간에 처리된 것 처럼 보입니다만.. 메타데이터 락의 획득과 해제가 각 2번씩 일어났습니다.

 

 

---

 

자동화하기 좋지 않나요?

1. 개발자의 요청을 백오피스 시스템에서 실행
2. 백오피스는 요청을 실행하는 동안 MySQL 서버의 메타데이터 락을 검사
3. 메타데이터 락 경합이 X초 이상 진행되면 락 홀더 세션을 kill

쉘로 만들어놔도 되고.. (저는 쉘 별로 안좋아합니다...ㅋㅋ)

자신있는 언어로 스크립트화해도 괜찮을 것 같네요

 

---

 

저는 자바+스프링으로 DB 유지보수 백오피스 서버를 만들고 있습니다.

 

간단히 자바코드 예제로 살펴보시죠.

이번엔 instant로 실행되지 않는, add index 작업입니다. 동일하게 메타데이터 락이 발생되는 작업입니다.

 

 

 

컨트롤러 코드입니다. 매개변수와 서버 상태에 대한 validation을 수행 한 후에 ddlService의 createIndex() 메서드를 호출합니다.

(MSA를 염두하고 만든 코드라서 TargetDatabase 정보를 매개변수로 받고 @RequestBody를 통해서 인덱스 생성에 대해 필요한 정보를 DTO에 매핑해옵니다. 중요한건 아닙니다.. 그냥 흐름만 따라오셔도 됩니다.)

@PutMapping("/ddl/index")
public CreateIndexResponseDTO createIndex(@TargetDatabase DatabaseConnectionInfo databaseConnectionInfo,
                                          @RequestBody CreateIndexRequestDTO ddlRequestDTO) {
    ddlValidator.validateCreateIndex(databaseConnectionInfo, ddlRequestDTO);
    return ddlService.createIndex(databaseConnectionInfo, ddlRequestDTO);
}

 

 

다음은 서비스 코드입니다. 흐름만 보시면,

1. 사용자 요청으로부터 SQL문을 생성하고
2. mysqlClient를 통해 SQL을 실행합니다.
3. 작업으로 인해 변경된 table statement를 return 합니다.

public CreateIndexResponseDTO createIndex(DatabaseConnectionInfo databaseConnectionInfo, CreateIndexRequestDTO createIndexRequestDTO) {
    String createIndexSQL = ddlParser.commandToSql(createIndexRequestDTO);
    mysqlClient.executeSQL(databaseConnectionInfo, createIndexSQL);
    String createTableStatement = mysqlClient.findCreateTableStatement(databaseConnectionInfo, createIndexRequestDTO.getSchemaName(), createIndexRequestDTO.getTableName());
    return new CreateIndexResponseDTO("test@gmail.com", databaseConnectionInfo.getDatabaseName(), createIndexRequestDTO.getSchemaName(), createIndexRequestDTO.getTableName(), createIndexRequestDTO.getIndexName(), createTableStatement);
}

별다른 것 없죠? 그냥 평범하게 인덱스 생성해주는 서비스로직입니다.

 

 

여기에 AOP를 적용해서 메타데이터 락을 detect 하고 holder session을 kill 하는 로직을 삽입했습니다.

startCheckMetadataLock() 메서드가 서비스클래스에서 createIndex() 메서드가 실행될때마다 실행됩니다.

그 결과로 MetadataLockDetector 클래스의 startcheck() 메서드가 실행됩니다.

@RequiredArgsConstructor
@Aspect
@Component
public class MetadataLockAspect {
    private final DynamicDataSourceProperties dynamicDataSourceProperties;
    private final MetadataLockDetector metadataLockDetector;

    @Pointcut("execution(* zzangmin.db_automation.service.DDLService.*(..))")
    public void ddlServiceMethods() {
    }

    @Before("ddlServiceMethods()")
    public void startCheckMetadataLock(JoinPoint joinPoint) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String databaseName = request.getParameter("databaseName");
        DatabaseConnectionInfo databaseConnectionInfo = dynamicDataSourceProperties.findByDbName(databaseName);
        metadataLockDetector.startCheck(databaseConnectionInfo);
    }

    @After("ddlServiceMethods()")
    public void endCheckMetadataLock(JoinPoint joinPoint) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String databaseName = request.getParameter("databaseName");
        DatabaseConnectionInfo databaseConnectionInfo = dynamicDataSourceProperties.findByDbName(databaseName);
        metadataLockDetector.endCheck(databaseConnectionInfo);
    }
}

 

디텍터에서는 mysqlClient를 통해 메타데이터 락 홀더 세션을 100ms 마다 검색하고, 3초 이상의 홀더가 존재하면 kill 하는 로직입니다.

MetadataLockDetector 클래스 코드:

@RequiredArgsConstructor
@Component
public class MetadataLockDetector {

    private Map<String, DatabaseConnectionInfo> targetDatabases = new ConcurrentHashMap<>();
    private static final int METADATA_LOCK_THRESHOLD_TIME = 3;
    private final MysqlClient mysqlClient;

    public void startCheck(DatabaseConnectionInfo databaseConnectionInfo) {
        targetDatabases.put(databaseConnectionInfo.getDatabaseName(), databaseConnectionInfo);
    }

    public void endCheck(DatabaseConnectionInfo databaseConnectionInfo) {
        targetDatabases.remove(databaseConnectionInfo.getDatabaseName());
    }

    @Scheduled(fixedDelay = 100)
    public void checkMetadataLock() {
        if (targetDatabases.isEmpty()) {
            return;
        }

        for (String databaseName : targetDatabases.keySet()) {
            DatabaseConnectionInfo databaseConnectionInfo = targetDatabases.get(databaseName);
            List<MetadataLockHolder> metadataLockHolders = mysqlClient.findMetadataLockHolders(databaseConnectionInfo);
            killLongMetadataLockHolder(databaseConnectionInfo, metadataLockHolders);
        }
    }

    private void killLongMetadataLockHolder(DatabaseConnectionInfo databaseConnectionInfo, List<MetadataLockHolder> metadataLockHolders) {
        for (MetadataLockHolder metadataLockHolder : metadataLockHolders) {
            if (metadataLockHolder.getProcesslistTime() >= METADATA_LOCK_THRESHOLD_TIME) {
                long sessionId = metadataLockHolder.getProcesslistId();
                mysqlClient.killSession(databaseConnectionInfo, sessionId);
            }
        }
    }

}

 

 

각종 프레임워크 기술이 섞여서 복잡해보이지만 핵심 로직은 별거 아닙니다.

1. 사용자가 index 생성 요청
2. 백오피스 서버는 요청에 따라 SQL을 생성하고 해당 DB 서버에 인덱스 생성문 실행
3. 서버는 해당 DB 서버를 0.1초마다 메타데이터 락 경합이 발생하는지 감시
4. 검사 결과로 메타데이터 락 경합이 3초 이상 발생하면, kill 조치

 

여러분도 도전해보세용 ㅎ_ㅎ

 

 

 

 

 

 

 

https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html

MySQL :: MySQL 8.0 Reference Manual :: 8.11.4 Metadata Locking