MySQL5.6 온라인 DDL의 성능과 동시성 고려 사항

14.11.2 온라인 DDL의 성능과 동시성 고려 사항

온라인 DDL 하여 성능 동시성, 가용성, 확장 성 등의 MySQL 작업의 일부 측면이 개선됩니다.

테이블에서의 쿼리와 DML 작업은 DDL의 진행하더라도 작업을 계속 할 수 있기 때문에 그 테이블에 액세스하는 응용 프로그램의 응답 성이 향상됩니다. MySQL 서버 전체에 걸쳐 다른 리소스의 잠금 과 대기가 감소되므로 변경 될 테이블에 관련되지 않은 조작 이어도, 확장 성 등을 제공합니다.
내부 작업은 테이블을 재 구축하기위한 디스크 I / O와 CPU 사이클을 피할 수 있기 때문에 데이터베이스에 걸리는 전체 부하를 최소화하는 동시에 DDL 작업 중에 좋은 성능과 높은 처리량 가 유지됩니다.
내부 작업은 모든 데이터가 복사되는 경우에 비해 버퍼 풀 에 읽을 데이터가 절감되기 때문에 이전에 DDL 작업 후 일시적인 성능 저하의 원인이 될 수 있었다, 자주 액세스되는 데이터의 메모리에서 삭제가 해결됩니다.

온라인 조작에 임시 파일이 필요한 경우, InnoDB 는 그 파일을 원래의 테이블을 포함하는 디렉토리가 아닌 임시 파일 디렉토리에 만듭니다. 이 디렉토리가 그런 파일을 보유 할 정도로 충분히 크지 않은 경우는 tmpdir 시스템 변수에 다른 디렉토리를 설정해야 할 수 있습니다. ( 섹션 B.5.4.4 "MySQL이 임시 파일을 저장할 위치" 를 참조하십시오.)

온라인 DDL 잠금 옵션

InnoDB 테이블이 DDL 조작에 의해 변경되는 동안 해당 작업의 내부 동작이나 ALTER TABLE 문 LOCK 절에 따라 그 테이블은 잠길 수도 있고 표시되지 않을 수 있습니다. 기본적으로 MySQL DDL 작업 중에 가장 적은 잠금을 사용합니다. 이 절은 잠금을 통상의 경우보다 제한으로한다 (이를 통해 병렬 DML 또는 DML 및 쿼리를 제한하는) 때문에거나하는 작업에 대해 어떤 기대되는 수준의 잠금을 확실하게 허용 있도록 지정합니다. 기본 키를 만들거나 제거하는 동안 LOCK 절이 특정 종류의 DDL 작업에서는 사용할 수없는 잠금 수준 ( LOCK=SHARED 와 LOCK=NONE )를 지정하는 경우이 조항이 표현처럼 기능 때문에이 문은 오류로 실패합니다. 다음 목록은 가장 허용적인 경우에서 가장 제한적인 경우까지 LOCK 절 다양한 가능성을 보여줍니다.

LOCK=NONE 을 사용하여 DDL 작업은 쿼리 병렬 DML 모두 허용됩니다. 이 절은 요청 된 잠금 유형이 유형의 DDL 작업을 수행 할 수없는 경우 ALTER TABLE 를 실패시키기 위해 LOCK=NONE 테이블을 완전히 사용 가능한 상태로 유지하는 것이 필수적이며, 한편 그것은 못하면 DDL을 취소해도 상관없는 경우에 지정합니다. 예를 들어,이 조항은 고객의 가입 또는 구매 관련 테이블의 DDL에서 고가의 ALTER TABLE 문 잘못된 발행해서 이러한 테이블이 사용 불가능하게되지 않도록하기 위해 사용할 수 있습니다.
LOCK=SHARED 를 사용하여 DDL 작업은 테이블에 기록 (즉, DML 작업)이 모두 차단되지만, 그 테이블의 데이터는 읽을 수 있습니다. 이 절은 요청 된 잠금 유형이 유형의 DDL 작업을 수행 할 수없는 경우 ALTER TABLE 를 실패시키기 위해 LOCK=SHARED 테이블을 쿼리에 사용 가능한 상태로 유지하는 것이 필수적이며 한편 그것을 못하면 DDL을 취소해도 상관없는 경우에 지정합니다. 예를 들어,이 절은 DDL이 완료 될 때까지 데이터로드 작업을 늦춰도 상관 없지만 쿼리를 장시간 지연 할 수없는 데이터웨어 하우스의 테이블의 DDL에 사용할 수 있습니다.
LOCK=DEFAULT 를 사용하거나 LOCK 절이 생략 된 DDL 작업은 MySQL은 그 종류의 작업에 사용 가능한 가장 낮은 수준의 잠금을 사용하면 가능한 경우 항상 병렬 쿼리, DML, 또는 둘 모두를 허용합니다. 이것은 그 테이블의 워크로드에 따라 가용성 문제가 발생하지 않는 것을 알고있는 사전 계획 및 테스트 된 변경을 할 경우에 사용하는 설정입니다.
LOCK=EXCLUSIVE 를 사용하여 DDL 작업은 쿼리와 DML 작업이 모두 차단됩니다. 이 절은 요청 된 잠금 유형이 유형의 DDL 작업을 수행 할 수없는 경우 ALTER TABLE 를 실패시키기 위해 LOCK=EXCLUSIVE 는 주요 관심사가 DDL을 수있는 짧은 시간에 완료 할 이고 또한 테이블에 액세스하려고하는 응용 프로그램을 대기 시켜도 상관없는 경우에 지정합니다. LOCK=EXCLUSIVE 또한 테이블에 예기치 않은 액세스를 방지하기 위해 서버가 유휴 상태로 간주되는 경우에도 사용할 수 있습니다.

InnoDB 테이블에 대한 온라인 DDL 문은 DDL 문을 준비하는 동안 짧은 시간 동안 테이블에 대한 단독 액세스가 필요하기 때문에 그 테이블에 액세스하는 현재 실행중인 트랜잭션이 커밋 또는 롤백 할 것을 항상 대기 합니다. 마찬가지로, 완료 전에도 잠깐 동안 테이블에 대한 단독 액세스가 필요합니다. 따라서 온라인 DDL 문은 DDL이 완료하기 전에 DDL의 진행에 시작되어 테이블을 쿼리하거나 수정하는 모든 트랜잭션이 커밋 또는 롤백 될 때까지 대기합니다.

병렬 DML 작업에 의해 수행 된 변경을 기록한 뒤, 마지막으로 이러한 변경 사항을 적용하려면 어느 정도의 처리가 필요하기 때문에 온라인 DDL 작업은 다른 세션에서 테이블 액세스를 차단 옛날 스타일 메커니즘에 비해 전반적으로 시간이 오래 걸릴 수 있습니다. raw 성능 저하는 그 테이블을 사용하는 응용 프로그램의 응답 성 향상과 균형이 잡히고 있습니다. 테이블 구조를 변경하기위한 이상적인 방법을 평가하는 경우, Web 페이지의 로딩 시간 등의 요인에 따라 최종 사용자의 성능의 인식을 고려하십시오.

새로 만든 된 InnoDB 보조 인덱스는 CREATE INDEX 또는 ALTER TABLE 문이 실행을 완료 한 시점에서의 테이블에서 커밋 된 데이터 만 포함되어 있습니다. 커밋되지 않은 값이나 이전 버전의 값 또는 삭제 표시되어 있지만 아직 이전 인덱스에서 제거되지 않은 값은 포함되어 있지 않습니다.

적절한 DDL 작업과 테이블 복사 DDL 작업의 성능 비교

온라인 DDL 작업의 raw 성능은 그 작업이 내부에서 실행되는지 또는 테이블 전체의 복사 및 재 구축이 필요에 따라 대부분 결정됩니다. 내부에서 수행 할 수있는 작업의 종류와 테이블 복사 작업을하지 않기 때문에 어떠한 요구 사항을 확인하려면 표 14.5 "DDL 작업의 온라인 상태의 요약" 을 참조하십시오.

적절한 DDL의 성능 향상은 기본 키 인덱스가 아닌 보조 인덱스에 대한 작업에 적용됩니다. InnoDB 테이블의 행은 기본 키 에 따라 편성 된 클러스터 된 인덱스 에 저장됩니다. 이로 인해 일부 데이터베이스 시스템에서 "인덱스 구성 테이블"라는 것이 형성됩니다. 이 테이블 구조는 기본 키에 매우 밀접하게 관련되어 있기 때문에 기본 키 다시 정의는 데이터의 복사본이 계속 필요합니다.

기본 키에 대한 작업 ALGORITHM=INPLACE 가 사용되는 경우, 데이터가 계속 복사되는에도 불구하고 다음의 이유로 ALGORITHM=COPY 를 사용하는 것보다 효율적입니다.

ALGORITHM=INPLACE 에는 Undo 로깅과 연관된 Redo 로깅이 필요하지 않습니다. 이러한 작업은 ALGORITHM=COPY 를 사용하는 DDL 문 오버 헤드를 늘립니다.
보조 인덱스 항목은 사전에 정렬되어 있기 때문에 순차적으로로드 할 수 있습니다.
보조 인덱스에 랜덤 액세스 삽입은 존재하지 않기 때문에, 변경 버퍼는 사용되지 않습니다.

온라인 DDL 작업의 상대적인 성능을 평가하려면 현재 버전과 이전 버전의 MySQL을 사용하여 이러한 작업을 큰 InnoDB 테이블에서 실행할 수 있습니다. 또한 모든 성능 테스트를 최신의 MySQL 버전에서 실행할 수 있습니다. 즉, old_alter_table 시스템 변수를 설정하여 이전 DDL 동작을 시뮬레이션하고 "이전"결과를 구합니다. 세션에서 문 set old_alter_table=1 을 발행하고 DDL 성능을 측정하고 "이전"수치를 기록합니다. 다음은 set old_alter_table=0 을 발행하여 새 빠른 동작을 다시 활성화하고 DDL 작업을 다시 실행하여 "뒤의"수치를 기록합니다.

DDL 작업이 그 변경을 내부에서 수행하거나 테이블 복사하는 가지 기본적인 생각은 명령이 완료된 후에 표시되는 "rows affected"의 값을보세요. 예를 들어, 다양한 유형의 DDL 작업을 수행 한 후에 나타날 수있는 행을 보여줍니다.

컬럼의 기본값 변경 (매우 빠른이며, 테이블 데이터에는 영향을주지 않습니다) :
```
 Query OK, 0 rows affected (0.07 sec)
```
인덱스 추가 (시간은 걸리지 만, 0 rows affected 테이블이 복사되지 않는 것을 보여줍니다) :
```
 Query OK, 0 rows affected (21.42 sec)
```
열의 데이터 형식 변경 (상당한 시간이 걸릴 테이블의 모든 행의 재구성이 필요) :
```
 Query OK, 1671168 rows affected (1 min 35.54 sec)
```

예를 들어, 큰 테이블에 DDL 작업을 수행하기 전에 해당 작업이 빠른지 느린 지에 다음과 같이 확인할 수 있습니다.

테이블 구조를 복제합니다.
복제 된 테이블에 매우 소량의 데이터를 채 웁니다.
복제 된 테이블에 DDL 작업을 수행합니다.
"rows affected '의 값이 0인지 여부를 확인합니다. 0이 아닌 값이 작업은 전체 테이블 재구성이 필요하기 때문에 특별한 계획이 필요할 수 있음을 나타냅니다. 예를 들어, DDL 작업을 예정된 다운 타임 기간 동안 또는 각 리플리케이션 슬레이브 서버에서 한 번에 하나씩 수행 할 수 있습니다.

MySQL 작업의 절감을보다 잘 이해하려면 DDL 작업 전후에 InnoDB 에 관련한 performance_schema 및 INFORMATION_SCHEMA 테이블을 검사하여 실제적인 읽기, 쓰기, 메모리 할당과 같은 수를 확인합니다.