Aurora MySQL에서 MySQL 기능에 대한 권장 사항 - Amazon Aurora
Aurora MySQL에서 다중 스레드 복제 사용네이티브 MySQL 함수로 AWS Lambda 함수 호출Amazon Aurora MySQL에서 XA 트랜잭션 방지DML 문 동안 외래 키 켜기 유지로그 버퍼를 플러시하는 빈도 구성Aurora MySQL 교착 상태 최소화 및 문제 해결

Aurora MySQL에서 MySQL 기능에 대한 권장 사항

다음 기능은 MySQL 호환성을 위해 Aurora MySQL에서 사용할 수 있습니다. 그러나 Aurora 환경에서는 성능, 확장성, 안정성 또는 호환성 문제가 있습니다. 따라서 이러한 기능을 사용할 때 정해진 가이드라인을 따르는 것이 좋습니다. 예를 들어 프로덕션 Aurora 배포에는 특정 기능을 사용하지 않는 것이 좋습니다.

Aurora MySQL에서 다중 스레드 복제 사용

다중 스레드 이진 로그 복제를 사용하면 SQL 스레드가 릴레이 로그에서 이벤트를 읽고 SQL 작업자 스레드에서 적용하도록 이벤트를 대기열에 넣습니다. SQL 작업자 스레드는 코디네이터 스레드에서 관리합니다. 가능한 경우 이진 로그 이벤트가 병렬로 적용됩니다.

다중 스레드 복제는 Aurora MySQL 버전 3, Aurora MySQL 버전 2.12.1 이상에서 지원됩니다.

Aurora MySQL 버전 3.04 미만에서 Aurora는 Aurora MySQL DB 클러스터가 이진 로그 복제의 읽기 전용 복제본으로 사용될 때 기본적으로 단일 스레드 복제를 사용합니다.

Aurora MySQL 버전 2의 초기 버전은 MySQL Community Edition에서 다중 스레드 복제와 관련된 여러 문제를 물려받았습니다. 이러한 버전의 경우 프로덕션 환경에서 다중 스레드 복제를 사용하지 않는 것이 좋습니다.

다중 스레드 복제를 사용할 경우 철저히 테스트할 것을 권장합니다.

Amazon Aurora에서의 복제에 대한 자세한 정보는 Amazon Aurora를 사용한 복제 단원을 참조하십시오. Aurora MySQL의 다중 스레드 복제에 대한 자세한 내용은 다중 스레드 이진 로그 복제 섹션을 참조하세요.

네이티브 MySQL 함수로 AWS Lambda 함수 호출

네이티브 MySQL 함수 lambda_sync 및 lambda_async를 사용하여 Lambda 함수를 호출하는 것이 좋습니다.

더 이상 사용되지 않는 mysql.lambda_async 프로시저를 사용하는 경우 저장 프로시저의 mysql.lambda_async 프로시저로 호출하는 것이 좋습니다. 트리거 또는 클라이언트 코드와 같은 여러 소스에서 저장 프로시저를 호출할 수 있습니다. 이 접근 방식을 통해 임피던스 불일치 문제를 방지하고 데이터베이스 프로그래머가 Lambda 함수를 보다 쉽게 호출할 수 있습니다.

Amazon Aurora에서 Lambda 함수 호출에 대한 자세한 정보는 Amazon Aurora MySQL DB 클러스터에서 Lambda 함수 호출 단원을 참조하십시오.

Amazon Aurora MySQL에서 XA 트랜잭션 방지

XA가 PREPARED 상태인 경우 복구 시간이 길어질 수 있으므로 Aurora MySQL에서 XA(eXtended Architecture) 트랜잭션을 사용하지 않는 것이 좋습니다. Aurora MySQL에서 XA 트랜잭션을 사용해야 하는 경우 다음 모범 사례를 따르십시오.

  • XA 트랜잭션을 PREPARED 상태로 열어두지 마십시오.

  • XA 트랜잭션을 가능한 작게 유지하십시오.

MySQL에서 XA 트랜잭션 사용에 대한 자세한 정보는 MySQL 설명서의 XA TRANSACTIONS를 참조하십시오.

DML 문 동안 외래 키 켜기 유지

foreign_key_checks 변수가 0(off)으로 설정되어 있을 때는 데이터 정의 언어(DDL) 문을 실행하지 않는 것이 좋습니다.

외래 키의 일시적 위반이 필요한 행을 삽입하거나 업데이트해야 하는 경우, 다음 단계에 따르십시오.

  1. foreign_key_checks를 0으로 설정합니다.

  2. 데이터 조작 언어(DML)를 변경합니다.

  3. 완료된 변경이 외래 키 제약 조건을 위반하지 않아야 합니다.

  4. foreign_key_checks를 1(on)로 설정합니다.

또한 외래 키 제약 조건에 대한 다음과 같은 다른 모범 사례에 따르십시오.

  • 클라이언트 애플리케이션이 init_connect 변수의 일부로 foreign_key_checks 변수를 0으로 설정하지 않아야 합니다.

  • mysqldump와 같은 논리적 백업으로부터 복원이 실패하거나 불완전할 경우, 같은 세션에서 다른 작업을 시작하기 전에 foreign_key_checks가 1로 설정되어 있는지 확인합니다. 논리적 백업은 시작할 때 foreign_key_checks를 0으로 설정합니다.

로그 버퍼를 플러시하는 빈도 구성

MySQL Community Edition에서 트랜잭션의 내구성을 높이려면 InnoDB 로그 버퍼를 내구성 있는 스토리지로 플러시해야 합니다. innodb_flush_log_at_trx_commit 파라미터를 사용하여 로그 버퍼가 디스크로 플러시되는 빈도를 구성합니다.

innodb_flush_log_at_trx_commit 파라미터를 기본값인 1로 설정하면 트랜잭션이 커밋될 때마다 로그 버퍼가 플러시됩니다. 이 설정은 데이터베이스 ACID 준수 상태를 유지하는 데 도움이 됩니다. 기본 설정인 1을 그대로 유지하는 것이 좋습니다.

innodb_flush_log_at_trx_commit을 기본값이 아닌 값으로 변경하면 데이터 조작 언어(DML) 지연 시간을 줄이는 데 도움이 되지만 로그 레코드의 내구성은 저하될 수 있습니다. 이러한 내구성 결여로 인해 데이터베이스 ACID 규정을 준수하지 못하게 됩니다. 서버 재시작 시 데이터 손실 위험을 예방하기 위해 데이터베이스를 ACID 규정을 준수하는 것이 좋습니다. 이 파라미터에 대한 자세한 내용은 MySQL 설명서에서 innodb_flush_log_at_trx_commit을 참조하세요.

Aurora MySQL에서는 다시 실행 로그 처리가 스토리지 계층으로 오프로드되므로 DB 인스턴스에서 로그 파일을 플러시하지 않습니다. 쓰기가 실행되면 라이터 DB 인스턴스에서 Aurora 클러스터 볼륨으로 직접 다시 실행 로그가 전송됩니다. 네트워크를 통과하는 유일한 쓰기는 다시 실행 로그 레코드입니다. 데이터베이스 계층에서 작성되는 페이지는 없습니다.

기본적으로 트랜잭션을 커밋하는 각 스레드는 Aurora 클러스터 볼륨의 확인을 기다립니다. 이 확인은 이 레코드와 모든 이전 다시 실행 로그 레코드가 기록되어 쿼럼을 얻었음을 나타냅니다. 로그 레코드를 유지하고 쿼럼을 얻으면 자동 커밋이든 명시적 커밋이든 관계없이 트랜잭션의 내구성이 확보됩니다. Aurora 스토리지 아키텍처에 대한 자세한 내용은 Amazon Aurora 스토리지에 관한 이해를 돕는 문서를 참조하세요.

Aurora MySQL은 MySQL Community Edition처럼 로그를 데이터 파일로 플러시하지 않습니다. 하지만 innodb_flush_log_at_trx_commit 파라미터를 사용하여 다시 실행 로그 레코드를 Aurora 클러스터 볼륨에 쓸 때 내구성 제약을 완화할 수 있습니다.

Aurora MySQL 버전 2의 경우:

  • innodb_flush_log_at_trx_commit = 0 또는 2 - 데이터베이스는 다시 실행 로그 레코드가 Aurora 클러스터 볼륨에 기록된다는 확인을 기다리지 않습니다.

  • innodb_flush_log_at_trx_commit = 1 - 데이터베이스는 다시 실행 로그 레코드가 Aurora 클러스터 볼륨에 기록된다는 확인을 기다립니다.

Aurora MySQL 버전 3의 경우:

  • innodb_flush_log_at_trx_commit = 0 - 데이터베이스는 다시 실행 로그 레코드가 Aurora 클러스터 볼륨에 기록된다는 확인을 기다리지 않습니다.

  • innodb_flush_log_at_trx_commit = 1 또는 2 - 데이터베이스는 다시 실행 로그 레코드가 Aurora 클러스터 볼륨에 기록된다는 확인을 기다립니다.

따라서 Aurora MySQL 버전 2에서 값을 0 또는 2로 설정했을 때와 동일한 기본값이 아닌 동작이 작동하려면 Aurora MySQL 버전 3에서 파라미터를 0으로 설정하세요.

이러한 설정은 클라이언트에 대한 DML 지연 시간을 줄일 수 있지만 장애 조치 또는 재시작 시 데이터가 손실될 수도 있습니다. 따라서 innodb_flush_log_at_trx_commit 파라미터를 기본값 1로 유지하는 것이 좋습니다.

MySQL Community Edition과 Aurora MySQL 모두에서 데이터 손실이 발생할 수 있지만 아키텍처가 다르기 때문에 데이터베이스마다 동작이 다릅니다. 이러한 아키텍처 차이로 인해 데이터 손실 정도가 달라질 수 있습니다. 데이터베이스가 ACID 규정을 준수하도록 하려면 innodb_flush_log_at_trx_commit을 항상 1로 설정하세요.

참고

Aurora MySQL 버전 3에서는 innodb_flush_log_at_trx_commit을 1이 아닌 값으로 변경하려면 먼저 innodb_trx_commit_allow_data_loss의 값을 1로 변경해야 합니다. 이렇게 하면 데이터 손실의 위험을 인정하는 것으로 간주됩니다.

Aurora MySQL 교착 상태 최소화 및 문제 해결

고유한 보조 인덱스 또는 외래 키에 대한 제약 조건 위반이 정기적으로 발생하는 워크로드를 실행하는 사용자가 동일한 데이터 페이지의 레코드를 동시에 수정할 경우, 교착 상태가 증가하고 잠금 대기 시간 초과가 발생할 수 있습니다. 이러한 교착 상태 및 시간 초과는 MySQL Community Edition 버그 수정으로 인한 것입니다.

이 수정 사항은 MySQL Community Edition 버전 5.7.26 이상에 포함되었으며 Aurora MySQL 버전 2.10.3 이상에 백포트되었습니다. 이 수정 사항은 InnoDB 테이블의 레코드 변경 사항에 대해 이러한 유형의 데이터 조작 언어(DML) 작업에 대한 추가 잠금을 구현하여 직렬화 기능을 적용하는 데 필요합니다. 이 문제는 이전 MySQL Community Edition 버그 수정으로 인해 발생한 교착 상태 문제를 조사하는 과정에서 발견되었습니다.

이 수정 사항으로 InnoDB 스토리지 엔진의 튜플(행) 업데이트의 부분 롤백에 대한 내부 처리가 변경되었습니다. 외래 키 또는 고유한 보조 인덱스에서 제약 조건 위반을 생성하는 작업은 부분 롤백을 초래합니다. 여기에는 동시 INSERT...ON DUPLICATE KEY UPDATE, REPLACE INTO,, INSERT IGNORE 문(upserts)이 포함되며 이에 국한되지 않습니다.

이 컨텍스트에서 부분 롤백은 애플리케이션 레벨 트랜잭션의 롤백을 의미하지 않으며, 제약 조건 위반이 발생할 때 클러스터링된 인덱스에 대한 변경 사항의 내부 InnoDB 롤백을 의미합니다. 예를 들어 upsert 작업 중에 중복된 키 값이 발견된 경우를 가정해 보겠습니다.

일반적인 삽입 작업에서 InnoDB는 각 인덱스에 대해 클러스터링된 보조 인덱스 항목을 원자적으로 생성합니다. InnoDB가 upsert 작업 중에 고유한 보조 인덱스에서 중복 값을 감지하면 클러스터링된 인덱스에 삽입된 항목을 되돌리고(부분 롤백), 기존 중복 행에 업데이트를 적용해야 합니다. 이 내부 부분 롤백 단계에서 InnoDB는 작업의 일부로 간주되는 각 레코드를 잠가야 합니다. 이 수정 사항은 부분 롤백 후 추가 잠금을 도입하여 트랜잭션 직렬화 기능을 보장합니다.

InnoDB 교착 상태 최소화

다음과 같은 접근 방식을 사용하여 데이터베이스 인스턴스의 교착 상태 빈도를 줄일 수 있습니다. 더 많은 예제는 MySQL 설명서에서 찾을 수 있습니다.

  1. 교착 상태가 발생할 가능성을 줄이려면 관련 변경 사항을 적용한 후 즉시 트랜잭션을 커밋하세요. 이렇게 하려면 큰 트랜잭션(커밋 간 다중 행 업데이트)을 작은 트랜잭션으로 나누면 됩니다. 행을 일괄적으로 삽입할 경우 특히 앞서 언급한 upsert 작업을 사용할 때는 배치 삽입 크기를 줄이세요.

    발생 가능한 부분 롤백 수를 줄이려면 다음 방법 중 일부를 시도해 봅니다.

    1. 배치 삽입 작업을 한 번에 한 행씩 삽입하는 방식으로 바꿉니다. 이렇게 하면 충돌이 발생할 수 있는 트랜잭션으로 인한 잠금 유지 시간을 줄일 수 있습니다.

    2. REPLACE INTO를 사용하는 대신 SQL 문을 다음과 같은 다중 문 트랜잭션으로 다시 작성합니다.

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. INSERT...ON DUPLICATE KEY UPDATE를 사용하는 대신 SQL 문을 다음과 같은 다중 문 트랜잭션으로 다시 작성합니다.

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. 잠금을 유지할 가능성이 있는 활성 또는 유휴 상태의 장기 실행 트랜잭션을 방지합니다. 여기에는 커밋되지 않은 트랜잭션으로 장기간 열려 있을 가능성이 있는 대화형 MySQL 클라이언트 세션이 포함됩니다. 트랜잭션 크기 또는 배치 크기를 최적화할 경우 동시성, 중복 수, 테이블 구조와 같은 여러 요인에 따라 미치는 영향이 달라질 수 있습니다. 모든 변경 사항은 워크로드를 기반으로 구현하고 테스트해야 합니다.

  3. 상황에 따라 두 트랜잭션이 하나 또는 여러 테이블에서 서로 다른 순서로 동일한 데이터 세트에 액세스하려고 할 경우 교착 상태가 발생할 수 있습니다. 이를 방지하려면 같은 순서로 데이터에 액세스하도록 트랜잭션을 수정하여 액세스를 직렬화합니다. 예를 들어, 완료할 트랜잭션 대기열을 생성합니다. 이런 접근 방식은 여러 트랜잭션이 동시에 발생할 때 교착 상태를 방지하는 데 도움이 될 수 있습니다.

  4. 신중하게 선택한 인덱스를 테이블에 추가하면 선택성이 향상되고 행에 액세스할 필요가 줄어들기 때문에 잠금 횟수도 감소합니다.

  5. 격차 잠금이 발생할 경우 세션 또는 트랜잭션에 맞게 트랜잭션 격리 수준을 READ COMMITTED로 수정하여 이를 방지할 수 있습니다. InnoDB 격리 수준 및 해당 동작에 대한 자세한 내용은 MySQL 설명서의 트랜 잭션 격리 수준을 참조하세요.

참고

교착 상태가 발생할 가능성을 줄이기 위한 예방 조치를 취할 수는 있지만, 교착 상태는 예상된 데이터베이스 동작이며 계속 발생할 수 있습니다. 애플리케이션에는 교착 상태가 발생했을 때 이를 처리하는 데 필요한 로직이 있어야 합니다. 예를 들어, 애플리케이션에서 재시도 및 백오프 로직을 구현합니다. 문제의 근본 원인을 해결하는 것이 가장 좋지만 교착 상태가 발생할 경우 애플리케이션에는 대기 후 다시 시도할 수 있는 옵션이 있습니다.

InnoDB 교착 상태 모니터링

애플리케이션 트랜잭션이 순환 대기를 야기하는 방식으로 테이블 수준 및 행 수준 잠금을 시도할 경우 MySQL에서 교착 상태가 발생할 수 있습니다. InnoDB 스토리지 엔진은 해당 조건을 즉시 감지하고 트랜잭션 중 하나를 자동으로 롤백하기 때문에 InnoDB 교착 상태가 가끔 발생하는 건 문제가 되지 않습니다. 단, 교착 상태가 자주 발생할 경우에는 애플리케이션을 검토 및 수정하여 성능 문제를 완화하고 교착 상태를 방지하는 것이 좋습니다. 교착 상태 감지가 켜져 있으면(기본값) InnoDB는 트랜잭션 교착 상태를 자동으로 감지하고 하나 이상의 트랜잭션을 롤백하여 교착 상태를 해제합니다. InnoDB는 롤백할 작은 트랜잭션을 선택하려는 시도를 합니다. 이때 트랜잭션의 크기는 삽입되거나, 업데이트되거나, 삭제된 행 수에 따라 결정됩니다.

  • SHOW ENGINE 문 - SHOW ENGINE INNODB STATUS \G 문에는 마지막 재시작 이후 데이터베이스에서 발생한 가장 최근의 교착 상태에 대한 세부 정보가 포함됩니다.

  • MySQL 오류 로그 - SHOW ENGINE 문의 출력이 부적절하여 교착 상태가 자주 발생할 경우 innodb_print_all_deadlocks DB 클러스터 파라미터를 켤 수 있습니다.

    이 파라미터를 켜면 InnoDB 사용자 트랜잭션의 모든 교착 상태에 대한 정보가 Aurora MySQL 오류 로그에 기록됩니다.

  • Amazon CloudWatch 지표 - 또한 CloudWatch 지표 Deadlocks를 사용하여 교착 상태를 사전에 모니터링하는 것이 좋습니다. 자세한 내용은 Amazon Aurora에 대한 인스턴스 수준 지표 단원을 참조하십시오.

  • Amazon CloudWatch Logs - Amazon CloudWatch Logs를 통해 지표를 보고, 로그 데이터를 분석하고, 실시간 경보를 생성할 수 있습니다. 자세한 내용은 Amazon CloudWatch를 사용하여 Amazon Aurora MySQL 및 Amazon RDS for MySQL의 오류를 모니터링하고 Amazon SNS를 사용하여 알림 보내기를 참조하세요.

    innodb_print_all_deadlocks를 켠 상태로 CloudWatch Logs Logs를 사용하면 교착 상태 횟수가 지정된 임계값을 초과할 때 알려주는 경보를 구성할 수 있습니다. 임계값을 정의하려면 추세를 관찰하고 정상 워크로드를 기준으로 한 값을 사용하는 것이 좋습니다.

  • 성능 개선 도우미 - 성능 개선 도우미를 사용하면 innodb_deadlocks 및 innodb_lock_wait_timeout 지표를 모니터링할 수 있습니다. 이러한 지표에 대한 자세한 내용은 기본이 아닌 Aurora MySQL용 카운터 섹션을 참조하세요.