• MySQL매뉴얼
    • MySQL 5.6 매뉴얼
    • MySQL 5.1 매뉴얼
    • MySQL 5.0 매뉴얼
    • MySQL HA 매뉴얼
  • 기술문서
    • Xtrabackup 구성
    • 메모리 사용량 모니터링
  • 라이선스
  • 온라인문의
  • 회사소개
  • → 목 록 (MySQL5.6 한글메뉴얼) [close]
  • 1. MySQL 5.6 새로운 기능
  • 2. MySQL 설치 및 업그레이드
  • 3. MySQL Tutorial
  • 4. MySQL 프로그램
  • 5. MySQL 서버관리
  • 6. 보안
  • 7. 백업 및 복구
  • 8. 최적화
  • 9. Language Structure(언어구조)
  • 10. Character Sets(Globalization)
  • 11. 데이터형(Data Types)
  • 12. 함수와 연산자
  • 13. SQL 문법
  • 14. InnoDB 스토리지 엔진
  • 1. InnoDB 소개
    2. InnoDB의 개념과 아키텍처
    3. InnoDB 구성
    4. InnoDB 관리
    5. InnoDB 테이블 스페이스 관리
    6. InnoDB 테이블 관리
    7. InnoDB 압축 테이블
    8. InnoDB 파일 형식 관리
    9. InnoDB Row Storage and Row Formats
    10. InnoDB 디스크 I/O 및 파일 영역 관리
    11. InnoDB와 온라인 DDL
    1. 온라인 DDL 개요
    2. 온라인 DDL의 성능과 동시성 고려 사항
    3. 온라인 DDL의 SQL 구문
    4. DDL 문의 결합 또는 분리
    5. 온라인 DDL의 예
    6. 온라인 DDL 구현에 대한 세부 항목
    7. 온라인 DDL에서 충돌 복구의 작동 방식
    8. 분할 된 InnoDB 테이블에 대한 온라인 DDL
    9. 온라인 DDL 제한
    12. InnoDB 부팅 옵션 및 시스템 변수
    13. InnoDB의 성능
    14. InnoDB INFORMATION_SCHEMA 테이블
    15. InnoDB 모니터
    16. InnoDB 백업 및 복구
    17. InnoDB와 MySQL 복제
    18. InnoDB 및 memcached의 통합
    19. InnoDB 문제 해결
  • 15. 기타 스토리지 엔진
  • 16. 고가용성 및 확장성
  • 17. 리플리케이션
  • 18. MySQL Cluster
  • 19. 파티셔닝
  • 20. Stored Programs and Views
  • 21. INFORMATION_SCHEMA
  • 22. PERFORMANCE SCHEMA
  • 23. 컨넥터 및 API
  • 24. MySQL 확장
  • 25. MySQL Enterprise Edition
  • 26. MySQL Workbench
  • 27. 제약 및 제한
  • 28. MySQL 5.7 새로운 기능

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 작업을 수행하기 전에 해당 작업이 빠른지 느린 지에 다음과 같이 확인할 수 있습니다.

  1. 테이블 구조를 복제합니다.

  2. 복제 된 테이블에 매우 소량의 데이터를 채 웁니다.

  3. 복제 된 테이블에 DDL 작업을 수행합니다.

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

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

서울시 강남구 영동대로 602 6층
TEL: 02-6061-0006  /  E: csr@mysqlkorea.com
주식회사 이노클러스터  등록번호 : 727-86-02261
Copyright © innocluster Co. ltd. all rights reserved