MariaDB/Admin

MariaDB 최적화 1

알 수 없는 사용자 2015. 10. 14. 17:56
  • 풀 테이블 스캔
    • 다음과 같은 조건일 때 주로 풀 테이블 스캔
      • 테이블의 레코드 건수가 너무 적어서 풀 테이블 스캔이 빠른 경우
      • WHERE, ON 절에 인덱스를 이용할 수 있는 조건이 없는 경우
      • 조건에 일치하는 레코드 수가 너무 많은 경우
    • Read ahead
      • 풀 테이블 스캔 시 대량의 페이지를 한번에 읽어올 수 있는 기능
      • Innodb나 XtraDB 스토리지 엔진은 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해서 Read ahead 작업이 자동으로 시작
      • 포그라운드 스레드가 페이지를 읽다가 특정 시점부터는 백그라운드 스레드가 읽기 시작하는데 한번에 4 ~ 8개의 페이지를 읽다가 증가시킴. 최대 64개.
      • innodb_read_ahead_threshold의 시스템 변수가 낮을수록 더 자주 Read ahead가 시작됨(기본값: 56, 연속적인 56개의 page를 읽음을 의미)
  • ORDER BY 처리
    • 소트 버퍼
      • 정렬을 수행하기 위해 소트 버퍼라는 별도의 메모리 공간을 할당 받음
      • sort_buffer_size -> MyISAM, XtraDB 스토리지 엔진에서 정렬 시 사용하는 최대 가용 메모리
      • aria_sort_buffer_size -> Aria 스토리지 엔진에서 정렬 시 사용하는 최대 가용 메모리(임시테이블을 Aria 스토리지 엔진으로 사용)
    • 정렬 알고리즘

      투 패스 알고리즘은 테이블을 두 번 읽어야 하기 때문에 성능이 떨어지지만 싱글 패스 알고리즘은 더 많은 소트 버퍼 공간이 필요함. 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때나 BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때는 투 패스 알고리즘을 사용하고 그 외의 대부분은 싱글 패스 알고리즘을 사용함.

      • 싱글 패스 알고리즘
        • 소트 버퍼에 SELECT되는 컬럼 전부를 담아서 정렬을 수행
      • 투 패스 알고리즘
        • 정렬 대상 컬럼과 프라이머리 키 값을 소트 버퍼에 담아서 정렬 수행 후, 순서대로 프라이머리 키로 테이블을 읽어서 SELECT 컬럼을 읽어 들임
    • 정렬의 처리 방식
      • 인덱스를 사용한 정렬 -> Extra : 별도의 내용 표기 없음
        • ORDER BY 에 명시된 컬럼이 드라이빙 테이블에 속해야 함
        • ORDER BY 순서대로 인덱스가 생성되어야 함
        • WHERE절에서 인덱스를 사용한다면 ORDER BY에서 해당 인덱스를 사용할 수 있어야 함
      • 드라이빙 테이블만 정렬 -> Extra : Using filesort가 표시 (조인 후에는 레코드 건수가 늘어나서 결과를 정렬할 경우 비효율이 발생)
        • 드라이빙 테이블의 컬럼 만으로 ORDER BY 절이 작성되어야 함
      • 임시 테이블을 이용한 정렬 -> Extra : Using temporary ;Using filesort가 같이 표시
        • 두 개 이상의 테이블을 조인해서 결과를 정렬할 경우 임시테이블을 사용함
        • 조인 결과를 임시테이블이 저장하고(Using temporary) 그 결과를 정렬 처리 함(Using filesort)
      • 정렬 방식의 성능 비교
        • 스트리밍 방식 -> 조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트로 전송
        • 버퍼링 방식 -> GROUP BY나 ORDER BY 가 사용된 쿼리는 모든 레코드를 처리한 후 클라이언트로 전송
    • ORDER BY .. LIMIT n 최적화

      쿼리 수행 전 변수 값 확인

       

      쿼리 수행 후 변수 값 확인

      • 정렬해야 하는 대상은 많지만 최종적으로 반환해야 할 레코드 건수가 적은 경우에 소트 버퍼에 우선순위 큐를 만들고 정렬을 수행
    • 정렬 관련 상태 변수

      쿼리 수행 전 변수 값 확인

       

      쿼리 수행

       

      멀티 머지 처리 횟수 : 12 – 8 = 4

      정렬 레코드 건수 : 838224 – 558816 = 279408

      풀 테이블 스캔 결과 정렬 횟수 : 2 -1 = 1

      • Sort_merge_passes : 멀티 머지 처리 횟수 (임시 테이블 사용)
      • Sort_ragne : 인덱스 range 스캔 결과에 대한 정렬 작업 횟수 (누적 상태 값)
      • Sort_scan : 풀 테이블 스캔 결과에 대한 정렬 작업 횟수(누적 상태 값)
      • Sort_rows : 지금까지 정렬한 전체 레코드 건수
    • GROUP BY 처리
      • 인덱스 스캔을 이용하는 GROUP BY
        • GROUP BY되는 컬럼에 인덱스 생성 시, 인덱스를 차례대로 읽으면서 그루핑 작업 수행 후 조인 처리 -> 정렬 보장
      • LOOSE 인덱스 스캔을 이용하는 GROUP BY
        • 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져옴
        • 인덱스의 유니크한 값의 수가 적을수록 성능 향상
      • 임시 테이블을 사용하는 GROUP BY
        • 인덱스를 사용하지 못할 때 사용
    • DISTINCT 처리
      • SELECT DISTINCT
        • GROUP BY와 거의 같은 방식으로 처리되지만 정렬 보장 안됨
        • SELECT LIST에 출력된 컬럼 조합 전체가 유니크한 레코드를 가져옴
      • 집합 함수와 함께 사용된 DISTINCT
        • 집합 함수의 인자로 전달된 컬럼 값이 유니크 한 레코드를 가져옴
    • 임시 테이블

      임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용, 디스크에 저장될 때는 Aria 스토리지 엔진을 사용

      임시 테이블을 사용했을 때 메모리를 사용했는지, 디스크를 사용했는지 실행 계획 상 알 수 없음

       

      쿼리를 실행하기 전 임시 테이블 생성 변수를 확인

      • Created_tmp_disk_tables -> 디스크 영역에 임시 테이블 생성 누적 수
      • Created_tmp_tables -> 임시 테이블 생성 누적 수

      쿼리 실행 후 확인해보면 메모리 영역에 임시 테이블을 생성했다는 것을 알 수 있음

       

      • 임시 테이블이 필요한 쿼리
        • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
        • ORDER BY나 GROUP BY에 명시된 컬럼이 드라이빙 테이블에 속하지 않았을 경우
        • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
        • UNION이나 UNION DISTINCT가 사용된 쿼리(SELECT TYPE 컬럼이 UNION RESULT인 경우)
        • UNION ALL이 사용된 쿼리
        • 쿼리의 실행 계획에서 SELECT TYPE이 DERIVED인 쿼리
      • 임시 테이블이 디스크에 생성되는 경우 (Aria 스토리지 엔진을 사용)
        • 대용량 컬럼이 있는 경우
        • 길이가 512 바이트 이상인 크기의 컬럼이 있는 경우
        • 데이터의 전체 크기가 tmp_table_size, max_heap_table_size 시스템 설정 값보다 큰 경우
      • 임시 테이블 관련 상태 변수
        • 임시 테이블이 메모리에서 처리되었는지, 디스크에서 처리되었는지, 몇 개의 임시 테이블이 생성되었는지는 실행계획으로 알 수 없음
        • 임시 테이블이 처리된 공간을 확인하는 시스템 변수 : SHOW SESSION STATUS LIKE 'Created_tmp%')
      • 인덱스를 가지는 내부 임시 테이블
        • 임시테이블에 자동으로 인덱스 생성 설정 : SET optimizer_switch='derived_with_keys=on'
      • 내부 임시 테이블의 주의사항
        • 가능하면 인덱스를 이용해서 처리하거나, 메모리 공간을 사용하는 임시 테이블을 사용할 수 있도록 처리
        • 디스크 공간을 사용하는 임시 테이블은 실제 테이블의 컬럼 사이즈만큼 공간을 차지하기 때문에 SELECT 하는 컬럼을 최소화 하고 BLOB과 TEXT 컬럼은 배제하는 것이 좋음