메뉴
Amazon Relational Database Service
사용 설명서 (API Version 2014-10-31)

MySQL DB 인스턴스에 대한 공통 DBA 작업

이번 섹션에서는 MySQL 데이터베이스 엔진을 기반으로 한 몇 가지 DBA의 공통 DB 인스턴스 작업을 Amazon RDS에 따라 구현하는 방법에 대해 살펴보겠습니다. 관리되는 서비스 환경을 제공하기 위해 Amazon RDS는 DB 인스턴스에 대해 셸 액세스를 제공하지 않으며, 고급 권한을 필요로 하는 특정 시스템 절차와 테이블에 대한 액세스를 제한합니다.

Amazon RDS의 MySQL 로그 파일 작업 방법에 대한 자세한 내용은 MySQL 데이터베이스 로그 파일 단원을 참조하십시오.

세션 또는 쿼리 종료

rds_killrds_kill_query 명령을 사용하여 DB 인스턴스의 사용자 세션이나 쿼리를 종료할 수 있습니다. 먼저 MySQL 데이터베이스 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 단원을 참조하십시오.

Copy
CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)

예를 들어, 스레드 99에서 실행 중인 세션을 종료하려면 다음과 같이 입력합니다.

Copy
CALL mysql.rds_kill(99);

또한 스레드 99에서 실행 중인 쿼리를 종료하려면 다음과 같이 입력합니다.

Copy
CALL mysql.rds_kill_query(99);

현재 복제 오류 넘어가기

Amazon RDS는 오류로 인해 읽기 전용 복제본이 중단되는 경우 읽기 전용 복제본의 오류를 건너뛸 수 있는 메커니즘을 제공하기 때문에 오류가 발생하더라도 데이터 무결성은 어떠한 영향도 받지 않습니다. 먼저 MySQL 데이터베이스 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 단원을 참조하십시오.

참고

먼저 안전하게 건너뛸 수 있는 오류인지 확인해야 합니다. MySQL 유틸리티에서 읽기 전용 복제본에 연결한 후 다음 MySQL 명령을 실행합니다.

Copy
SHOW SLAVE STATUS\G

반환 값에 대한 자세한 내용은 MySQL 문서에서 SHOW SLAVE STATUS Syntax를 참조하십시오.

오류를 건너뛰려면 다음 명령을 실행합니다.

Copy
CALL mysql.rds_skip_repl_error;

이 명령은 원본 DB 인스턴스에서 실행하거나, 혹은 복제 오류가 발생하지 않은 읽기 전용 복제본에서 실행하는 경우 아무런 효과도 없습니다.

mysql.rds_skip_repl_error가 지원되는 MySQL 버전 등에 대한 자세한 내용은 mysql.rds_skip_repl_error 단원을 참조하십시오.

중요

mysql.rds_skip_repl_error를 호출하려고 할 때 ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist와 같은 오류가 발생한 경우에는 MySQL DB 인스턴스를 최신 마이너 버전이나 mysql.rds_skip_repl_error에 등록된 최소 마이너 버전 중 하나로 업그레이드해야 합니다.

충돌 복구 시간 개선을 위한 InnoDB 테이블스페이스 작업

MySQL의 모든 테이블은 테이블 정의, 데이터 및 인덱스로 구성되어 있습니다. InnoDB는 MySQL 스토리지 엔진으로서 테이블 데이터와 인덱스를 테이블스페이스에 저장하는 역할을 합니다. 이 스토리지 엔진은 전역적 공유 테이블스페이스를 생성하여 데이터 사전을 비롯한 기타 관련 메타데이터, 그리고 테이블 데이터와 인덱스도 저장합니다. 또한 테이블 및 파티션마다 별도의 테이블스페이스를 생성할 수도 있습니다. 이렇게 별도로 생성된 테이블스페이스는 확장자가 .ibd인 파일에 저장되며, 각 테이블스페이스 헤더에는 식별할 수 있도록 고유 번호가 포함됩니다.

Amazon RDS는 MySQL 파라미터 그룹을 통해 innodb_file_per_table이라고 하는 파라미터를 하나 제공합니다. 이 파라미터는 InnoDB가 새로운 테이블 데이터와 인덱스를 공유 테이블스페이스에 추가할지(파라미터 값을 0으로 설정), 혹은 개별 테이블스페이스에 추가할지(파라미터 값을 1로 설정) 결정합니다. Amazon RDS에서는 innodb_file_per_table 파라미터 값이 1로 기본 설정되어 있습니다. 이 설정에서는 개별 InnoDB 테이블을 삭제하고 이 테이블에서 DB 인스턴스로 사용되었던 스토리지를 회수할 수 있습니다. 대부분 사용 사례에서 innodb_file_per_table 파라미터는 1로 설정하는 것이 바람직합니다.

하지만 표준 스토리지(마그네틱)나 일반 SSD 스토리지를 사용하여 테이블 수가 1,000개를 넘거나, 혹은 프로비저닝된 IOPS 스토리지를 사용하여 테이블 수가 10,000개를 넘는 등 테이블 수가 많을 때는 innodb_file_per_table 파라미터를 0으로 설정해야 합니다. 이 파라미터를 0으로 설정하면 테이블스페이스가 개별적으로 생성되지 않기 때문에 데이터베이스 충돌 복구에 걸리는 시간을 개선할 수 있습니다.

MySQL은 충돌 복구 주기에서 테이블스페이스가 저장된 메타데이터 파일을 각각 처리합니다. MySQL이 공유 테이블스페이스에 저장된 메타데이터 정보를 처리하는 데 걸리는 시간은 다수의 테이블스페이스로 인해 수천 개의 테이블스페이스 파일을 처리하는 데 걸리는 시간에 비하면 무시해도 될 정도입니다. 테이블스페이스 번호는 각 파일의 헤더에 저장되기 때문에 모든 테이블스페이스 파일을 읽으려면 최대 몇 시간까지 걸릴 수 있습니다. 예를 들어 표준 스토리지에 InnoDB 테이블스페이스가 수백만 개 저장되어 있다면 충돌 복구 주기에서 처리하는 데만 5~8시간이 소요됩니다. 경우에 따라 충돌 복구 주기가 끝나더라도 InnoDB가 추가 정리가 필요하다고 판단할 경우에는 또 다른 충돌 복구 주기가 시작되면서 복구 시간이 연장됩니다. 또 한 가지, 충돌 복구 주기는 테이블스페이스 정보 처리 외에도 롤링백 트랜잭션, 손상된 페이지 복구, 그리고 그 밖의 작업까지 수반한다는 점도 잊어서는 안 됩니다.

innodb_file_per_table 파라미터는 파라미터 그룹에 속하기 때문에 DB 인스턴스에 사용되는 파라미터 그룹만 편집하면 파라미터 값이 변경됩니다. 따라서 DB 인스턴스를 재부팅할 필요가 없습니다. 예를 들어 설정을 1(개별 테이블 생성)에서 0(공유 테이블스페이스 사용)으로 변경하면 새로운 InnoDB 테이블이 공유 테이블스페이스에 추가되는 반면 기존 테이블은 개별 테이블스페이스를 그대로 유지합니다. InnoDB 테이블을 공유 테이블스페이스로 이동하려면 ALTER TABLE 명령을 사용해야 합니다.

여러 테이블스페이스를 공유 테이블스페이스로 마이그레이션

InnoDB 테이블의 메타데이터를 자체 테이블스페이스에서 공유 테이블스페이스로 이동할 수 있습니다. 이렇게 하면 innodb_file_per_table 파라미터 설정에 따라 테이블 메타데이터가 다시 작성됩니다. 먼저 MySQL 데이터베이스 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 단원을 참조하십시오.

Copy
ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY;

예를 들어 다음 쿼리는 InnoDB 테이블마다 ALTER TABLE 문을 반환합니다.

Copy
SELECT CONCAT('ALTER TABLE `', REPLACE(TABLE_SCHEMA, '`', '``'), '`.`', REPLACE(TABLE_NAME, '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND ENGINE = 'InnoDB' AND TABLE_SCHEMA <> 'mysql';

MySQL 테이블을 리빌드하여 테이블의 메타데이터를 공유 테이블스페이스로 이동하려면 테이블을 리빌드할 수 있는 스토리지 공간이 임시로 필요합니다. DB 인스턴스에 여유 스토리지 공간이 있어야 하는 이유도 바로 이 때문입니다. 리빌드 단계에서는 테이블이 잠겨서 쿼리에 액세스하지 못합니다. 작은 용량의 테이블이나 자주 액세스하지 않는 테이블의 경우 이것이 문제가 되지는 않습니다. 하지만 대용량의 테이블이나 동시 접속자 수가 많은 환경에서 자주 액세스하는 테이블이라면 테이블을 읽기 전용 복제본에 리빌드할 수도 있습니다.

읽기 전용 복제본을 생성한 후 테이블 메타데이터를 읽기 전용 복제본의 공유 테이블스페이스로 마이그레이션하는 방법도 있습니다. ALTER TABLE 문이 읽기 전용 복제본에 대한 액세스를 차단하더라도 원본 DB 인스턴스는 영향을 받지 않습니다. 따라서 테이블 리빌딩 프로세스로 인해 읽기 전용 복제본이 지연되더라도 원본 DB 인스턴스는 계속해서 이진 로그를 생성할 수 있습니다. 리빌딩 프로세스에는 스토리지 공간이 추가로 필요할 뿐만 아니라 릴레이 로그 파일이 커질 수도 있기 때문에 원본 DB 인스턴스보다 큰 용량의 스토리지를 할당하여 읽기 전용 복제본을 생성해야 합니다.

읽기 전용 복제본을 생성하여 InnoDB 테이블을 리빌드한 후 공유 테이블스페이스를 사용하려면 다음 단계를 따라야 합니다.

  1. 이진 로그 생성을 계속 할 수 있도록 원본 DB 인스턴스에 백업 보존 기간이 활성화되어 있는지 확인합니다.

  2. AWS 콘솔 또는 AWS CLI를 사용하여 원본 DB 인스턴스의 읽기 전용 복제본을 생성합니다. 읽기 전용 복제본을 생성하려면 충돌 복구와 같이 다수의 동일한 프로세스를 거쳐야 하기 때문에 InnoDB 테이블스페이스가 많을 경우에는 일정 시간이 걸릴 수도 있습니다. 이때 읽기 전용 복제본에 할당하는 스토리지 공간은 현재 원본 DB 인스턴스에 사용 중인 스토리지 공간보다 많아야 합니다.

  3. 읽기 전용 복제본이 생성된 후에는 파라미터 설정 read_only = 0innodb_file_per_table = 0으로 구성된 파라미터 그룹을 생성한 다음 이 그룹을 읽기 전용 복제본과 연동시킵니다.

  4. 복제본에서 마이그레이션하고자 하는 모든 테이블에 대해 ALTER TABLE <name> ENGINE = InnoDB를 실행합니다.

  5. 읽기 전용 복제본에서 ALTER TABLE 문을 모두 실행한 후에는 읽기 전용 복제본이 원본 DB 인스턴스에 연결되어 있고, 두 인스턴스가 동기화되어 있는지 확인합니다.

  6. 준비가 끝났으면 AWS 콘솔 또는 AWS CLI를 사용해 읽기 전용 복제본을 마스터 인스턴스로 승격합니다. 새로운 마스터에 사용한 파라미터 그룹에서 innodb_file_per_table 파라미터가 0으로 설정되어 있는지 확인합니다. 새로운 마스터 이름을 변경하고 모든 애플리케이션이 새로운 마스터 인스턴스를 가리키도록 합니다.

전역적 상태 이력 관리

MySQL은 작업 관련 정보를 알 수 있는 다수의 상태 변수를 유지하고 있습니다. 이 변수 값은 DB 인스턴스에서 잠금 또는 메모리 문제를 파악하는 데 효과적입니다. DB 인스턴스를 마지막으로 시작한 때부터 계속해서 누적되기 때문입니다. 대부분 상태 변수는 FLUSH STATUS 명령을 사용해 0으로 재설정할 수 있습니다.

Amazon RDS는 시간이 지나면서 이 변수 값의 스냅샷을 캡처하거나, 마지막 스냅샷 이후 모든 변경 사항과 함께 변수 값을 테이블에 기록하는 등 시간 경과에 따른 상태 변수 값을 모니터링할 수 있는 프로시저를 지원합니다. 이러한 인프라를 전역적 상태 이력(GoSH)이라고 부릅니다. GoSH는 버전 5.5.23부터 모든 MySQL DB 인스턴스에 설치되기 시작했지만 기본적으로 비활성화되어 있습니다.

GoSH를 활성화하려면 먼저 파라미터 event_scheduler를 ON으로 설정하여 DB 파라미터 그룹의 이벤트 스케줄러를 활성화해야 합니다. DB 파라미터 그룹의 생성 및 변경에 대한 자세한 내용은 DB 파라미터 그룹 작업 단원을 참조하십시오.

그런 다음 아래 표의 프로시저에 따라 GoSH를 활성화 및 구성할 수 있습니다. 먼저 MySQL 데이터베이스 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL 데이터베이스 엔진 기반 DB 인스턴스에 연결하기 단원을 참조하십시오. 각 절차에 대해 다음을 입력합니다.

Copy
CALL procedure-name;

여기서 procedure-name에는 표에 보이는 프로시저 중 하나가 들어갑니다.

프로시저

설명

rds_enable_gsh_collector

rds_set_gsh_collector에서 설정한 주기에 따라 기본 스냅샷을 캡처하도록 GoSH를 활성화합니다.

rds_set_gsh_collector

스냅샷 캡처 주기(분)를 지정합니다. 기본 값은 5입니다.

rds_disable_gsh_collector

스냅샷을 비활성화합니다.

rds_collect_global_status_history

필요할 경우에만 스냅샷을 캡처합니다.

rds_enable_gsh_rotation

mysql.rds_global_status_history 테이블의 내용이 rds_set_gsh_rotation에서 설정한 주기에 따라 mysql.rds_global_status_history_old로 로테이션됩니다.

rds_set_gsh_rotation

테이블 로테이션 주기(일)를 지정합니다. 기본 값은 7입니다.

rds_disable_gsh_rotation

테이블 로테이션을 비활성화합니다.

rds_rotate_global_status_history

필요에 따라 mysql.rds_global_status_history 테이블의 내용을 mysql.rds_global_status_history_old로 로테이션합니다.

GoSH가 활성화되어 있을 때는 쓰기가 가능한 테이블에 쿼리를 요청할 수 있습니다. 예를 들어 Innodb 버퍼 풀의 적중률에 대한 쿼리를 요청하려면 다음과 같이 쿼리를 실행합니다.

Copy
select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'