책너두 (Real MySQL 8.0 1권) 27일차 (~295p)

요약

• MySQL의 기본 데이터 처리 방식에 대해 이해하게 됨.
• 풀 테이블 스캔과 풀 인덱스 스캔의 결정 조건을 알게 됨.
  • 풀 테이블 스캔 시, InnoDB 스토리지 엔진에서는 리드 어헤드를 통해 백그라운드 스레드가 예측되어 미리 디스크에 읽어진 데이터 페이지를 읽어들임.
• 하나의 쿼리를 병렬로 처리할 수 있다는 사실을 알게 됨.
• 정렬 방식으로 인덱스와 파일소트 방식을 알게됨.
  • 정렬을 수행하는 별도 메모리인 소트 버퍼에 대해 알게 됨.
  • 정렬 레코드 건수가 소트 버퍼가 감당할 메모리 보다 크다면 멀티 머지가 이루어지게 됨.
• 정렬 알고리즘의 2가지 방식을 알게 됨
  • 싱글 패스 정렬 방식 → 조회 대상 칼럼을 모두 디스크에서 읽어서 정렬
  • 투 패스 정렬 방식 → 정렬 대상 칼럼 & 프라이머리 키 칼럼을 디스크에서 읽어 정렬 후, 나머지 조회 대상 칼럼을 다시 디스크에 읽는 방식

메모

기본 데이터 처리

• MySQL 서버를 포함한 모든 RDBMS 는 데이터 정렬, 그루핑 등의 기본 데이터 가공 기능을 가지고 있음.
  • 결과물은 동일하더라도, RDBMS 별 결과를 만들어내는 과정은 천차만별임.
  • MySQL 서버가 기본적인 가공에 어떤 알고리즘을 쓰는지 살펴본다.

풀 테이블 스캔과 풀 인덱스 스캔

• 풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 처리하는 작업임.
• MySQL 옵티마이저는 다음과 같은 조건이 일치할 때, 주로 풀 테이블 스캔을 선택한다.
  • 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우 (일반적으로, 테이블이 페이지 1개로 구성된 경우임)
  • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준으로 판단함)
• 일반적으로 테이블 전체 크기는 인덱스보다 훨씬 크기에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요함.
  • 그래서 대부분 DBMS 는 풀테이블 스캔 실행할 때, 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있음.
    • MySQL에는 풀 테이블 스캔을 실행할 때, 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없음.
    • 그래서 MySQL은 풀 테이블 스캔 실행할 때, 디스크로부터 페이지를 하나씩 읽어 오는 것으로 생각함.
      • 이는, MyISAM 스토리지 엔진에는 맞는 이야기지만, InnoDB에서는 틀린 말임.
      • InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면, 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작됨.
        • 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서, 요청이 오기 전에 미리 디스크를 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미함.
        • 즉, 풀 테이블 스캔이 실행되면, 처음 몇 개의 데이터 페이지는 포그라운드 스레드(Foreground thread, 클라이언트 스레드)가 페이지 읽기를 실행하지만, 특정 시점부터 읽기 작업을 백그라운드 스레드로 넘김.
        • 백그라운드 스레드가 읽기를 넘겨받는 시점부터 한 번에 4개, 또는 8개씩 페이지를 읽으면서, 그 수를 계속 증가시킴.
        • 한 번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해 둠.
        • 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면되므로 쿼리가 상당히 빨리 처리됨.
• MySQL 서버에서 innodb_read_ahead_threshold 시스템 변수를 이용해 InnoDB 스토리지 엔진이 언제 리드 어헤드를 시작할지 임계값을 설정할 수 있음.
  • 포그라운드 스레드에 의해 innodb_read_ahead_threshold 시스템 변수에 설정된 개수만큼 연속된 데이터 페이지가 읽히면 InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그다음 페이지들을 읽어서 버퍼 풀로 적재함.
  • 일반적으로 디폴트 설정 값으로 충분함.
    • 데이터 웨어하우스용 MySQL을 쓴다면 이 옵션을 더 낮은 값으로 설정해서 더 빨리 리드 어헤드가 시작되게 유도하는 것도 좋은 방법임.
• 리드 어헤드는 풀 테이블 스캔에서만 사용되는 건 아님.
  • 풀 인덱스 스캔에서도 동일하게 사용됨.

mysqw> SELECT COUNT(*) FROM employees;

• 위 쿼리는 풀 인덱스 스캔을 하게될 가능성이 높음.
  • 인덱스 테이블은 2~3개 정도의 칼럼만으로 구성되므로 테이블 자체보다 용량이 작아서 훨씬 빠른 처리가 가능함.

mysql> SELECT * FROM employees;

• 위 쿼리는 인덱스외의 레코드에만 있는 칼럼이 필요한 경우이므로, 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔하게 됨.

병렬 처리

• 8.0부터 용도가 한정되어 있긴하지만, 처음으로 MySQL 서버에서도 쿼리의 병렬 처리가 가능해짐.

여기서 설명하는 병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것을 의미함.

• 8.0 에서, innodb_parallel_read_threads 라는 시스템 변수를 이용하여 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지 변경할 수 있음.
• 병렬 처리용 스레드 개수가 늘어날 수록 쿼리 처리 시간이 줄어듬.
  • 하지만, 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우, 오히려 성능이 떨어질 수 있으니 주의.

ORDER BY 처리(Using filesort)

• 레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용됨.
• 데이터 웨어하우스처럼 대량의 데이터를 조회해서 일괄 처리하는 기능이 아니라면 레코드 정렬 요건은 대부분 조회 쿼리에 포함되어 있을 것임.
• 정렬을 처리하는 방법으로 인덱스를 이용하는 방법과, 쿼리가 실행될 때, “Filesort” 라는 별도 처리를 이용하는 방법으로 나눌 수 있음.
  • 인덱스 이용
    • 장점
      • INSERT, UPDATE, DELETE 쿼리가 실행될 때, 이미 인덱스가 정렬되어 있기에 순서대로 읽기만 하면 되기에 매우 빠름.
    • 단점
      • INSERT, UPDATE, DELETE 작업 시, 부가적인 인덱스 추가/삭제 작업이 필요하므로 느림.
      • 인덱스 때문에 디스크 공간이 더 많이 필요함.
      • 인덱스 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요함.
  • Filesort 이용
    • 장점
      • 인덱스를 생성하지 않아도 되므로, 인덱스를 이용할 때의 단점이 장점으로 바뀜.
      • 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort 처리가 되므로 충분히 빠름.
    • 단점
      • 정렬 작업이 쿼리 실행시 처리되므로 레코드 대상 건수가 많아질 수록 쿼리의 응답 속도가 느림.
• 레코드 정렬하기 위해 항상 “Filesort” 정렬 작업을 거쳐야 하는 것은 아님.
  • B-Tree 인덱스의 정렬 & 스캔 방향에서 살펴봤었음.
  • 하지만 다음과 같은 이유로 모든 정렬을 인덱스를 이용하도록 튜닝하기란 거의 불가능함.
    • 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능함.
    • GROUP BY 결과, 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우.
    • UNION 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
    • 랜덤하게 결과 레코드를 가져와야 하는 경우
• MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 칼럼에 “Using filesort” 메시지가 표시되는지 여부로 판단할 수 있음.
  • MySQL의 정렬이 어떻게 처리되는지 살펴보자.
    • MySQL 정렬 특성을 이해하면 쿼리 튜닝할 때, 어떻게 하면 조금이라도 더 빠른 쿼리가 될지 쉽게 판단할 수 있을 것임.

소트 버퍼

• MySQL은 정렬을 수행하기 위해, 별도의 메모리 공간을 할당받아서 사용함.
  • 이 메모리 공간을 소트 버퍼(Sort buffer) 라고 함.
  • 소트 버퍼는 정렬이 필요한 경우에만 할당됨.
  • 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가함.
    • 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정할 수 있음.
    • 소트 버퍼를 위한 메모리 공간은 쿼리 실행이 완료되면 즉시 시스템으로 반납됨.
• 정렬해야 할 레코드가 아주 소량이어서, 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 정렬이 처리될 것임.
  • 하지만 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면?
    • 이때, MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리함. → 이 과정에서 임시 저장을 위해 디스크를 사용함.
      • 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둠.
      • 그리고, 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장함.
        • 이처럼, 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야함.
        • 이, 병합 작업을 멀티 머지(Multi-merge) 라고 표현함.
        • 수행된 멀티 머지 횟수는 Sort_merge_passes 라는 상태 변수에 누적해서 집계됨.
        • 이 작업들은 모두 디스크의 쓰기와 읽기를 유발함.
        • 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아짐.
• sort_buffer_size 시스템 변수의 설정값이 무조건 크면 메모리에서 모두 처리되니, 빨라질 것으로 예상하지만, 실제 결과는 그렇지 않음.
  • 리눅스 운영체제에서는 너무 큰 sort_buffer_size 를 사용하는 경우, 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수 있음.
  • 경험적으로, 일반적인 트랜잭션 처리용 MySQL 서버의 소트 버퍼 크기는 56KB 에서 1MB 미만이 적절해 보임.
    • 4장에서 메모리 할당 & 사용구조를 알아보았는데, 소트 버퍼는 세션(로컬) 메모리영역에 해당됨.
    • 즉, 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아님.
    • 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을 수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미한다.
      • 소트 버퍼의 크기를 10MB 이상으로 설정하면, 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시에 실행되면 운영체제는 메모리 부족현상을 겪게 될 수 있음.
      • 따라서 운영체제의 OOM-Killer가 여유 메모리 확보를 위해 프로세스를 강제 종료할 것임.
      • OOM-Killer는 메모리를 가장 많이 사용하는 프로세스를 강제 종료하므로 일반적으로 메모리를 가장 많이 사용하는 MySQL 서버가 강제 종료 1순위가 됨.

소트 버퍼를 크게 설정해서 빠른 성능을 얻을 순 없지만, 디스크 읽기, 쓰기 사용량은 줄일 수 있음.
MySQL 서버의 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이 될 수 있음.
대량의 데이터 정렬이 필요한 경우, 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 좋은 방법임.

정렬 알고리즘

• 레코드를 정렬할 떄, 레코드 전체를 소트 버퍼에 담을지, 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 “싱글 패스(Single-pass)”와 “투 패스(Two-pass)” 2가지 정렬 모드로 나눌 수 있음.
  • 정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지는 옵티마이저 트레이스 기능으로 확인할 수 있음.
  • 트레이스 기능을 통해 출력된 내용중, “filesort_summary” 섹션의 “sort_algorithm” 필드에 정렬 알고리즘이 표시됨.
  • 정확히 MySQL 서버의 정렬 방식은 다음 3가지가 있음.
    • <sort_key, rowid> : 정렬 키와 레코드의 로우 아이디만 가져와서 정렬하는 방식
    • <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식임. 레코드의 칼럼들은 고정 사이즈로 메모리에 저장함.
    • <sort_key, packed_Additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식임. 레코드의 칼럼들은 가변 사이즈로 메모리에 저장함.
      • 첫 번째 방식은 “투 패스” 정렬 방식이라 명명함.
      • 두 번째와 세 번째 방식을 “싱글 패스” 정렬 방식이라 명명함.
      • 5.7부터 세 번째 방식이 도입되었는데, 이는 정렬을 위한 메모리 공간의 효율적인 사용을 위해 추가로 도입된 방식임.

싱글 패스 정렬 방식

• 소트 버퍼에 정렬된 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 정렬 방식임.

mysql> SELECT emp_no, first_name, last_name
             FROM employees
             ORDER BY first_name;

• 위 쿼리와 같이 first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT 하는 쿼리를 싱글 패스(Single-pass) 정렬 방식으로 처리하는 절차를 그림으로 보면 다음과 같다.

• 위 그림에서 알 수 있듯, 처음 employees 테이블을 읽을 때, 정렬에 필요하지 않은 last_name 칼럼 까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행함.
• 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨주는 과정을 볼 수 있음.

투 패스 정렬 방식

• 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식임.
  • 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식임.
  • 하지만, MySQL 8.0 에서도 여전히 특정 조건에서는 투 패스(Two-pass) 정렬 방식을 사용함.

• 위 그림은 투 패스 방식 정렬 과정을 표현함.
• 처음 employees 테이블을 읽을 때는 정렬에 필요한 first_name 칼럼과 프라이머리 키인 emp_no만 읽어서 정렬을 수행했음.
• 정렬이 완료되면 그 결과 순서대로 employees 테이블을 한 번 더 읽어서 last_name을 가져오고, 최종적으로 그 결과를 클라이언트 쪽으로 넘기는 과정을 확인할 수 있음.
• MySQL 예전 정렬 방식인 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리함.
  • 새로운 정렬 방식인 싱글 패스는 이러한 불합리가 없음.
  • 하지만, 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요함.
• 최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용함.
• 최신 버전이라고 해서 항상 싱글 패스 정렬 방식을 사용하는건 아님.
  • 다음의 경우, MySQL 서버는 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용함.
    • 레코드 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
    • BLOB 이나 TEXT 타입의 칼럼이 SELECT 대상에 포함할 때
• 싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보임
• 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적임.

SELECT 쿼리에서 꼭 필요한 칼럼만 조회하지 않고, 모든 칼럼(*)을 가져오도록 개발할 때가 많음. 하지만 이는 정렬 버퍼를 몇 배에서, 몇십 배까지 비효율적으로 사용할 가능성이 큼. SELECT 쿼리에서 꼭 필요한 칼럼만 조회하도록 쿼리를 작성하는 것이 좋다고 권장하는 것은 바로 이런 이유 때문임. 특히, 정렬이 필요한 SELECT는 불필요한 칼럼을 SELECT 하지 않게 쿼리를 작성하는 것이 효율적임.