Amazon RDS for MySQL의 파라미터 구성 모범 사례, 2부: 복제와 관련된 파라미터

https://aws.amazon.com/ko/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-2-parameters-related-to-replication/

Best practices for configuring parameters for Amazon RDS for MySQL, part 2: Parameters related to replication | Amazon Web Servi

 

이 블로그 게시물은 2022년 5월에 마지막으로 검토되거나 업데이트되었습니다.

이 시리즈 의 이전 블로그 게시물 에서는 MySQL 성능을 위해 Amazon Relational Database Service(Amazon RDS) 를 조정하고 최적화하는 데 사용되는 MySQL 파라미터 와 이와 관련된 모범 사례에 대해 논의했습니다. 오늘의 게시물에서는 RDS MySQL 환경에서 복제 구성 및 복제 최적화에 사용되는 가장 중요한 MySQL 매개변수에 대해 설명합니다.

참고: 이 게시물에 언급된 기본값은 Amazon RDS for MySQL 5.7에 적용됩니다. 해당 MySQL 설명서 또는 AWS CLI 또는 Amazon RDS 콘솔 을 사용하여 액세스할 수 있는 RDS for MySQL 8.0 기본 파라미터 그룹에서 MySQL 8.0용 Amazon RDS의 기본값을 찾을 수 있습니다 .

단일 및 다중 스레드 복제와 관련된 매개변수

다음에는 단일 및 다중 스레드 복제 모두에 사용할 수 있는 매개변수와 각각의 구성에 대한 모범 사례 제안을 나열합니다.

sync_binlog

이 sync_binlog옵션은 MySQL이 바이너리 로그를 디스크로 플러시하는 방법을 제어합니다.

기본값 sync_binlog은 1입니다. 복제 마스터에서 sync_binlog 가 0으로 설정되면 디스크와 동기화되지 않습니다. 이 경우 서버는 운영 체제에 의존하여 때때로 바이너리 로그의 내용을 플러시합니다. 파일. 따라서 이 설정은 MySQL이 바이너리 로그(binlog)를 디스크로 플러시하지 않도록 하여 복제 성능을 향상시킵니다. 이 접근 방식은 최상의 성능을 제공합니다.

그러나 MySQL이 충돌하면 바이너리 로그에 여러 트랜잭션이 누락될 수 있습니다. 일반적으로 마스터와 동기화되도록 복제본을 다시 빌드해야 합니다. 백업이 비활성화되고 "연결된" 읽기 전용 복제본이 없는 RDS 읽기 복제 sync_binlog의 경우 binlog를 동기화할 필요가 없기 때문에 적용되지 않습니다. 바이너리 로그 생성을 방지하려면 복제본에서 백업 보존 기간을 0으로 설정하는 것이 좋습니다.

그러나 데이터 손실을 최소화 sync_binlog하려면 복제본 소스에서 매개변수를 1로 설정해야 합니다. 설정하는 가장 좋은 값은 성능 또는 내구성을 우선시하는지 여부에 따라 다릅니다.

binlog_row_image

매개변수를 사용하여 바이너리 로그에서 지원하는 두 가지 이벤트 형식을 지정할 수 있습니다 binlog_format. STATEMENT 및 ROW입니다. 행 기반 형식을 사용하면 비결정적 쿼리를 기록할 수 있으며 복제본에 임시 테이블이 생성되지 않습니다. 반면에 명령문 기반 형식은 행 기반 형식보다 더 간결합니다.

매개변수를 사용하여 binlog_row_image행 기반 이벤트에 대해 이진 로그에 기록되는 정보의 양을 제어할 수 있습니다. 행의 상태는 바이너리 로그에서 "이미지"로 표시됩니다. 모든 행 기반 이벤트에는 이전 이미지와 이후 이미지의 두 가지 이미지가 있습니다. 변경 이전 행은 이전 이미지로 표시됩니다. 변경 후 행은 사후 이미지로 표시됩니다. 모든 이벤트에 전후 이미지가 있는 것은 아닙니다.

다음 표에는 다양한 행 기반 이벤트와 사용 가능한 이미지가 요약되어 있습니다. INSERT 문은 Write_rows이벤트를 생성합니다.

이벤트 유형	이미지 전	애프터 이미지
쓰기_행		포함
Update_rows	포함	포함
Delete_rows	포함

다음 예제는 사람이 읽을 수 있는 형식으로 이러한 이미지를 읽기 위해 mysqlbinlog 유틸리티를 사용하여 이러한 이미지를 자세히 보여줍니다.

mysql> SHOW CREATE TABLE test.table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL,
  `val1` int(10) unsigned NOT NULL,
  `val2` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO test.table1 VALUES (1, 10, '');
Query OK, 1 row affected (0.03 sec)
mysql> UPDATE test.table1 SET val2 = UUID() WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> DELETE FROM test.table11 WHERE id = 1;
Query OK, 1 row affected (0.07 sec)

다음은 mysqlbinlog --base64-output=decode-rows --verbose앞 INSERT의 UPDATE, 및 DELETE문에 의해 생성된 이벤트에 대한 명령의 출력입니다. 이후 섹션 where은 이전 이미지를 나타냅니다. 다음 섹션 SET은 애프터 이미지를 나타냅니다.

이 INSERT예에는 애프터 이미지가 있습니다.

#170820  7:44:22 server id 582927608  end_log_pos 6985 CRC32 0x00499cfa         Table_map: 'test'.'table1' mapped to number 240
# at 6995
#170820  7:44:22 server id 582927608  end_log_pos 7030 CRC32 0xf0a5e5ad         Write_rows: table id 240 flags: STMT_END_F
### INSERT INTO 'test'.'table1'
### SET
###   @1=1
###   @2=10
###   @3=''

이 UPDATE예에는 이전 이미지와 이후 이미지가 모두 있습니다.

#170820  7:44:42 server id 582927608  end_log_pos 7402 CRC32 0x842b11e5         Update_rows: table id 240 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3=''
### SET
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

이 DELETE예에는 이전 이미지만 있습니다.

#170820  7:44:52 server id 582927608  end_log_pos 7750 CRC32 0x7f842c22         Delete_rows: table id 240 flags: STMT_END_F
### DELETE FROM 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

앞의 예에서 모든 열 세부 정보는 기본 구성으로 기록됩니다. binlog_row_image매개변수는 이전 이벤트에 대해 기록되는 열과 이미지를 제어하는 ​​데 도움이 됩니다 .

매개변수에 지원되는 값은 다음과 같습니다.

• 전체 – 전후 이미지의 모든 열을 기록합니다.
• 최소 – 사후 이미지에는 변경된 열만 기록하고 사전 이미지에서는 행을 식별하는 데 필요한 열만 기록합니다.
• noblob – 변경되지 않는 한 BLOB 및 TEXT 열을 제외한 모든 열을 기록합니다.

다음 섹션에서는 이러한 형식 값에 대해 설명합니다. 사용할 이미지 형식을 결정할 때 이러한 형식의 장단점과 사용 사례 및 작업 부하에 대한 적용을 고려하십시오.

가득한

full값의 경우 이전 이미지와 이후 이미지가 모두 있는 이전 예제의 이벤트 update_row예제가 뒤따릅니다.

#170820  7:44:42 server id 582927608  end_log_pos 7402 CRC32 0x842b11e5         Update_rows: table id 230 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
###   @2=10
###   @3=''
### SET
###   @1=1
###   @2=10
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

두 번째 열 val1 은 업데이트되지 않아도 비포 이미지와 애프터 이미지에 포함됩니다.

full이미지 사용의 몇 가지 이점 은 다음과 같습니다.

• 모든 스토리지 엔진이 이 형식을 지원합니다.
• 이 형식을 사용하여 DML(데이터 조작 언어) 문을 롤백할 수 있습니다. 이 형식은 행을 삭제하는 이벤트에 대해 행의 이전 이미지의 모든 열을 기록합니다. 따라서 이전 이미지에 기록된 값을 다시 삽입하여 재구성할 수 있는 행을 재구성할 수 있습니다.

그러나 사용의 단점은 binlog_row_image=full이벤트를 기록하기 위해 바이너리 로그에 더 많은 공간이 필요하다는 것입니다. 이는 특히 BLOB 또는 TEXT 값이 있는 열이 있는 업데이트 문이 많은 경우 문제가 될 수 있습니다.

최소한의

이 minimal옵션은 변경 사항을 적용하는 데 필요한 정보만 기록합니다.

• 이미지 이전 – 기본 키 값만 기록됩니다.
• After image – 값이 변경된 열이 기록됩니다.

앞의 삽입, 업데이트, 삭제 예에서 minimal옵션을 사용하면 세 가지 이벤트는 다음과 같습니다.

이 INSERT예에는 애프터 이미지만 있습니다.

#170820  10:20:22 server id 582927608  end_log_pos 8434 CRC32 0xdea26bc4         Write_rows: table id 240 flags: STMT_END_F
### INSERT INTO 'test'.'table1'
### SET
###   @1=1
###   @2=10
###   @3=''

이 UPDATE예에는 이전 이미지와 이후 이미지가 모두 있습니다.

#170820  10:40:42 server id 582927608  end_log_pos 8500 CRC32 0xecc1bb1f         Update_rows: table id 240 flags: STMT_END_F
### UPDATE 'test'.'table1'
### WHERE
###   @1=1
### SET
###   @3='e3c27a9e-7e25-11e7-b749-08002715584a'

이 DELETE예에는 이전 이미지만 있습니다.

#170820  10:44:52 server id 582927608  end_log_pos 8555 CRC32 0x5850de79         Delete_rows: table id 240 flags: STMT_END_F
### DELETE FROM 'test'.'table1'
### WHERE
###   @1=1

앞의 예에서 볼 수 있듯이 명령문 의 특성으로 인해 이벤트 에 대한 full및 minimal옵션 에는 차이가 없습니다 . 그러나 행 업데이트 및 행 삭제 이벤트에 해당하는 명령문에 대해 기록되는 이미지의 크기는 전체 옵션보다 최소 옵션에서 훨씬 작습니다.Write_rowsINSERTUPDATEDELETE

최소 이미지 형식을 사용하면 다음과 같은 이점이 있습니다.

• 대부분의 업데이트가 단일 열이고 varchar, char, text, blob 및 유사한 데이터 유형이 있는 큰 열이 많은 경우 바이너리 로그 이벤트가 더 작고 UPDATE절약 할 수 있습니다.DELETE
• 작은 바이너리 로그는 공간을 절약할 뿐만 아니라 복제를 위한 디스크 I/O 및 네트워크 트래픽을 줄입니다.

이미지 형식 을 사용할 때의 단점은 minimal다음과 같습니다.

• 이 접근 방식은 명시적 기본 키 또는 NULL이 아닌 고유 인덱스가 있는 테이블에서만 작동합니다.
• 사후 이미지에 모든 컬럼이 기록되는 것이 아니라 변경된 컬럼만 기록되기 때문에 레플리카 인스턴스에 대한 컬럼 정의 요건이 더 까다롭다. 다음은 요구 사항입니다.
  • 두 테이블 모두 동일한 기본 키를 가져야 합니다.
  • 데이터 유형은 동일해야 합니다.
  • 열의 순서는 동일해야 합니다.
  • 마스터와 복제본은 동일한 열을 가져야 합니다. 그렇지 않으면 업데이트 이벤트로 인해 알림 없이 복제본이 동기화되지 않을 수 있습니다.

노블롭

이미지는 컬럼이 필요하거나 변경되는 경우에만 사후 이미지에 BLOB 또는 텍스트 컬럼이 포함된다는 점을 제외하고는 이미지 와 noblob동일합니다 .full

slave_pending_jobs_size_max

이 매개변수는 아직 적용되지 않은 이벤트를 보유하는 복제본 작업자 큐에 할당된 최대 메모리 양을 정의합니다. 기본값은 16MB입니다. 이 값은 에 대한 마스터 값보다 작아서는 안 됩니다 max_allowed_packet(다음 참조). 그렇다면 마스터에서 처리해야 하는 이벤트가 있더라도 복제본 작업자 큐가 가득 찰 수 있습니다.

max_allowed_packet

max_allowed_packet마스터와 읽기 전용 복제본 모두에 동일한 값이 구성되어 있는지 확인합니다 . max_allowed_packet그렇지 않으면 차이로 인해 복제본의 낮은 값 제약으로 인해 복제 지연이 발생할 수 있습니다 . 이 주제에 대해서는 블로그 시리즈의 다음 부분인 연결 및 시간 제한과 관련된 매개변수에 대해 자세히 설명하겠습니다.

innodb_rollback_on_timeout

이 매개변수가 지정되면 트랜잭션 시간 초과로 인해 InnoDB는 실행이 성공한 마지막 쿼리뿐만 아니라 전체 트랜잭션을 중단하고 롤백합니다. 이렇게 하면 중복 항목과 관련된 다양한 복제 오류를 방지하는 데 도움이 됩니다. 기본값은 이 기능을 비활성화하는 OFF입니다.

innodb_flush_log_at_trx_commit

이 블로그 시리즈의 1부 에서 성능과 관련된 매개변수에 대해 논의한 것처럼 innodb_flush_log_at_trx_commit복제본 인스턴스에서 0 또는 2로 설정하면 로그 버퍼가 디스크로 플러시되는 빈도가 줄어듭니다. 이는 차례로 디스크 쓰기로 인한 성능 저하를 줄입니다. 그러나 언급했듯이 이렇게 하면 충돌이 발생할 경우 일부 트랜잭션이 손실될 가능성이 있습니다.

query_cache 설정

읽기 전용 복제본에서 쿼리 캐시를 비활성화하는 것이 좋습니다. 읽기 전용 복제본이 데이터베이스에 쓸 때 대부분의 경우 쿼리 캐시를 무효화해야 하므로 이 방법을 권장합니다.

query_cache_type쿼리 캐시를 비활성화하려면 0과 0으로 설정할 수 있습니다 query_cache_size. 쿼리 캐시 작업에 대한 자세한 내용은 이 블로그 시리즈의 1부를 참조하세요 .

읽기 전용

이 매개변수를 사용하여 클라이언트에서 읽기 전용 복제본으로 업데이트를 허용할 수 있습니다. 기본값은 TrueIfReplica입니다. 복제본 인스턴스 TrueIfReplica의 경우 값을 ON(1)으로 설정하고 클라이언트의 모든 쓰기 활동을 비활성화합니다. 마스터/라이터 인스턴스 TrueIfReplica의 경우 값을 OFF(0)로 설정하여 마스터/라이터 인스턴스에서 클라이언트의 쓰기 활동을 활성화합니다.

read_only파라미터 그룹에서 1로 설정하여 이 후자의 기능을 활성화합니다 . 1 로 설정하여 이 매개변수를 끄면 read_only읽기 전용 복제본이 쓰기 가능하게 됩니다. 그러나 RDS MySQL 읽기 전용 복제본에 대한 모범 사례로 읽기 전용 복제본을 이러한 방식으로 장기간 쓰기 가능으로 변경하지 않는 것이 좋습니다. 이 설정은 복제 오류 및 데이터 일관성 문제를 일으킬 수 있습니다.

log_bin_trust_function_creators

바이너리 로깅이 활성화된 경우 기능, 절차 또는 트리거를 활성화하려면 MySQL SUPER 권한이 필요합니다. 이 권한은 RDS MySQL DB 인스턴스에 대해 제한됩니다. 그러나 바이너리 로깅이 활성화된 경우 RDS MySQL 인스턴스에서 기능, 절차 및 트리거를 활성화할 수 있습니다. 매개변수 값을 1 로 설정하면 log_bin_trust_function_creators됩니다. 그렇지 않으면 다음과 같은 오류가 발생할 수 있습니다.

You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable).

기본값은 0입니다.

다중 스레드 복제에만 관련된 매개변수

다음은 다중 스레드 복제와 관련된 매개변수를 나열하고 각각을 구성하기 위한 모범 사례 제안과 함께 나열합니다.

slave_parallel_type

이 매개변수는 MySQL 버전 5.7에서 사용할 수 있습니다. 이 값은 다중 스레드 복제가 활성화된 경우 병렬로 실행되는 트랜잭션을 결정하는 정책을 정의합니다. 이를 위해 slave_parallel_workers매개변수에 0이 아닌 값을 사용합니다.

이 매개변수는 LOGICAL_CLOCK 및 DATABASE의 두 가지 값을 가질 수 있습니다. 기본값은 DATABASE입니다. LOGICAL_CLOCK이 설정되면 마스터에서 동일한 바이너리 로그 그룹 커밋의 일부인 트랜잭션이 복제본에서 병렬로 실행됩니다. 추가 병렬화를 제공하기 위해 트랜잭션 간의 종속성은 타임스탬프를 사용하여 추적됩니다.

값이 DATABASE로 설정되면 다른 데이터베이스에서 실행되는 트랜잭션이 병렬로 적용되지만 이 값으로는 스키마 내에서 병렬화가 불가능합니다. 이 값은 서로 다른 데이터베이스의 데이터가 마스터에서 동시에 독립적으로 분할 및 업데이트될 때 적용할 수 있습니다. DATABASE를 사용하려면 데이터베이스 간 제약 조건이 없어야 합니다.

이 매개변수에 사용할 값을 결정하려면 앞의 제약 조건과 스키마 내에서 또는 스키마 간 병렬화를 원하는지 여부를 고려하십시오. 스키마 내 병렬화의 경우 LOGICAL_CLOCK이 유일한 옵션입니다.

사용하려면 slave_preserve_commit_order=1(다음에서 설명함) 에 LOGICAL_CLOCK을 사용해야 합니다 slave_parallel_type.

slave_parallel_type가 LOGICAL_CLOCK이고 slave_preserve_commit_orderMTS(다중 스레드 슬레이브) 작업자가 제한 시간과 동일한 시간(초) 동안 정지할 수 있는 경우 알려진 문제가 있습니다 innodb_lock_wait. 이 문제는 MySQL 버그 82400 및 MySQL 버그 25082593으로 인해 발생합니다. 해결 방법은 READ COMMITTED의 트랜잭션 격리 수준을 사용하도록 복제본을 설정하는 것입니다. 매개변수를 사용하여 이 작업을 수행할 수 있습니다 tx_isolation.

slave_parallel_workers

이 매개변수는 트랜잭션을 병렬로 실행하는 복제본 인스턴스의 작업자 스레드 수를 설정합니다. 기본값은 0이며 이 값은 작업자 스레드의 병렬 실행을 비활성화합니다. 허용되는 값은 0–1,024입니다.

복제본에서 병렬 트랜잭션 실행을 사용하면 복제에서 더 나은 확장성을 제공합니다. 그러나 이 접근 방식은 마스터와 복제본이 모두 MySQL 5.6 이상에 있는 경우에만 작동합니다. slave_parallel_workers이 값을 0보다 크게 설정하면 트랜잭션을 재시도할 수 없으며 0 으로 slave_transaction_retries처리됩니다. 이 매개변수에 대해 이 값을 설정하는 것이 MySQL 5.6.3(버그 13334470)에서 항상 올바르게 적용되는 것은 아니었으며, 이는 5.6에서 수정되었습니다. .4. 또한 버그 84415는 이 매개변수가 활성화된 경우 영향을 줄 수 있습니다.

MySQL 5.6에서 병렬 복제는 스키마당 하나의 스레드만 사용하므로 여러 데이터베이스가 있는 경우 효과적으로 사용할 수 있습니다. 5.7에서 이 접근 방식은 스키마 내의 워크로드에도 사용할 수 있습니다.

값을 늘리면 slave_parallel_workers복제 성능이 선형적으로 향상되지 않습니다. 이 매개변수의 최상의 값을 추정하는 방법은 작업 부하에 따라 다르며 실제 작업 부하를 시뮬레이션하고 복제 지연을 모니터링하여 테스트해야 합니다.

트랜잭션과 관련된 일부 성능 스키마 계측을 활성화하여 실행된 트랜잭션을 기록할 수 있습니다. 그런 다음 각 복제 스레드에서 몇 개의 트랜잭션을 실행하는지 알기 위해서는 성능 스키마 테이블 performance_schema.events_transactions_summary_by_thread_by_event_name 과 performance_schema.replication_applier_status_by_worker. 이렇게 하면 모든 스레드가 올바르게 사용되었는지 확인한 다음 필요에 따라 slave_parallel_workers 값을 조정하는 데 도움이 됩니다. 자세한 내용 은 Percona의 유용한 블로그 게시물 을 참조하세요.

이 값을 조정하는 방법은 작업 부하에 따라 다릅니다. 읽기 전용 복제본이 읽기 작업에 사용되지 않고 대기 복제본으로만 사용되는 경우 이 값을 인스턴스의 vCPU 수로 설정할 수 있습니다. 읽기 전용 복제본이 대기가 아닌 읽기 워크로드를 제공하는 데 사용되는 경우 값을 설정한 후 워크로드를 테스트합니다.

slave_preserve_commit_order

이 매개변수는 MySQL 5.7에서 사용할 수 있습니다. 이는 트랜잭션이 복제본의 릴레이 로그에 나타나는 것과 동일한 순서로 복제본에서 외부화되도록 합니다. 기본값은 이 기능을 비활성화하는 0입니다.

 slave_parallel_typeMySQL 5.7에서 가 LOGICAL_CLOCK으로 설정된 경우 다중 스레드 복제 설정에 대한 복제 간격을 피하기 위해 이 매개변수를 권장합니다 . 이 매개변수는 복제본이 마스터가 아닌 상태로 들어가지 않도록 합니다.

이 옵션을 사용하려면 자동 백업(및 바이너리 로깅) 및 log_slave_updates매개변수를 사전 요구 사항으로 활성화해야 합니다. 매개변수 는 log_slave_updates기본적으로 활성화되어 있습니다. 복제 인스턴스에서 바이너리 로깅을 활성화하면 스토리지 활용도와 성능에 영향을 미칠 수 있습니다. 따라서 바이너리 로깅(자동 백업)이 있거나 없는 복제를 테스트하는 것이 좋습니다.

다중 스레드 슬레이브 옵션이 활성화되면 트랜잭션이 병렬로 실행될 수 있습니다. 매개변수가 설정 되면 slave_preserve_commit_order실행 중인 복제본은 모든 이전 트랜잭션이 커밋될 때까지 트랜잭션 커밋을 기다립니다. 복제 스레드가 이전 트랜잭션의 다른 작업자 스레드가 커밋되기를 기다리는 경우 해당 상태는 이전 트랜잭션이 커밋되기를 기다리는 것으로 보고됩니다.

이 옵션을 사용하지 않으면 재생 로그에서 실행되는 트랜잭션 시퀀스에 간격이 있을 수 있습니다. 기본값은 이 매개변수를 비활성화하는 0입니다. 그러나 이 경우 Exec_master_log_posSQL 스레드가 읽고 실행한 현재 마스터 바이너리 로그 파일의 위치 뒤에 있는 것처럼 보일 수 있습니다.

결론

앞의 매개변수는 RDS MySQL에서 복제 성능과 안정성에 영향을 줄 수 있는 가장 중요한 매개변수입니다. 이러한 매개변수에 대해 논의한 모범 사례를 따르면 MySQL RDS 복제본이 가능한 최소 지연으로 실행되고 다른 운영 문제를 방지하는 데 도움이 됩니다.

이 블로그 시리즈 의 다음 부분 에서는 다양한 보안 기능을 구현하기 위해 일반적으로 사용되는 MySQL 매개변수, RDS DB 인스턴스의 운영 및 문제 해결을 관리하는 데 도움이 되는 일부 매개변수, 데이터 정렬 및 문자 집합과 관련된 몇 가지 유용한 매개변수에 대해 설명합니다.