책너두 (Real MySQL 8.0 1권) 9일차 (~119p)

요약

• InnoDB 버퍼풀의 역할과 관리하는 방법의 자료구조를 알게 되었다.
• 쓰기 버퍼링 기능 향상을 위한 버퍼풀과 리두로그와의 관계를 이해하게 되었다.
• 버퍼 풀에 등록되어있는 더티 페이지의 플러시 전략을 이해하게 되었다.
• 버퍼풀에 적재된 내용을 확인하는 방법을 알게 되었다.
  • 적재된 내용 확인에 대한 쿼리도 설명해 줬지만 완벽히 이해하지는 못함...
• 버퍼풀과 리두로그의 관계를 이해하는것이 어려웠다.
  • 다른사람이 정리한 블로그도 읽으면서 내용을 이해하려고 노력했다. https://new-age-jh.tistory.com/288

발췌

• InnoDB 스토리지 엔진은 체크 포인트를 발생시켜 디스크의 리두 로그와 데이터 페이지의 상태를 동기화 하게 된다.
  • 체크 포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점을 만드는역할을 한다.

메모

InnoDB 버퍼 풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간임.
• 쓰기 작업을 지연시켜 일괄 작업으로 처리하는 버퍼 역할도 함.
  • 일반 어플리케이션은 INSERT, UPDATE, DELETE 와 같은 데이터 변경 쿼리가 데이터 파일의 여러곳에 위치한 레코드를 변경하므로 랜덤 디스크 작업을 발생시킴
    • 버퍼 풀은 변경된 데이터를 모아서 처리하기 때문에 랜덤 디스크 작업 횟수를 줄일 수 있음.

버퍼 풀의 크기 설정

• 운영체제와 각 클라이언트 스레드가 사용할 메모리를 충분히 고려하여 InnoDB 버퍼풀에 할당할 물리 메모리를 정해야 함.
  • 전체 물리 메모리의 80% 정도 설정하라는 말도 있지만 그렇게 단순히 설정할 값이 아님. (위 내용을 고려해야 함)
• 버퍼 풀은 MySQL 서버 내에서 메모리를 필요하는 부분이 크게 없음.
  • 아주 독특한 경우 레코드 버퍼가 상당한 메모리를 사용하기도 함.
  • 레코드 버퍼 : 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간
    • 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수도 있음.
    • MySQL 서버가 사용하는 레코드 버퍼 공간은 별도로 설정할 수 없음
      • 전체 커넥션 개수와 각 커넥션에서 읽고 쓰는 테이블의 개수에 따라 결정됨.
      • 이 버퍼 공간은 동적으로 해제되기도 하므로 정확히 필요한 메모리 공간의 크기를 계산할 수 없음.
• 5.7 버전부터 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됨.
  • InnoDB 버퍼 풀 크기를 적절히 작은 값으로 설정해서 조금씩 증가시키는 방법이 최적이다.
• 이미 회사에 MySQL 서버를 사용하고 있다면 그 서버의 메모리 설정을 기준으로 InnoDB 버퍼 풀 크기를 조정하면 된다.
• 만약 처음 MySQL 서버를 준비한다면 다음 방법으로 InnoDB 버퍼 풀 설정을 찾아가는 것을 권장한다.
  • 운영체제 전체 메모리 공간이 8GB 미만이면 50% 정도만 InnoDB 버퍼 풀로 설정한다.
    • 나머지 공간은 MySQL 서버, 운영체제, 그리고 다른 프로그램이 사용할수 있는 공간으로 확보해두는게 좋다.
  • 전체 메모리 공간이 8GB 이상이라면 InnoDB 버퍼 풀 크기를 전체 메모리의 50%에서 시작해서 조금씩 올려가며 최적점을 찾는다.
  • 운영체제 전체 메모리 공간이 50GB 이상이라면 대략 15~30GB 정도를 운영체제 & 다른 응용 프로그램을 위해 남겨두고 나머지를 InnoDB 버퍼 풀로 할당하자.
• InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있음.
  • 동적으로 버퍼 풀의 크기를 확장할 수 있다.
  • 이 버퍼 풀 크기 변경 작업은 크리티컬한 작업이므로 가능하면 MySQL 서버가 한가한 시점에 진행하는게 좋다.
  • InnoDB 버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않음.
  • InnoDB 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능한 버퍼 풀 크기를 줄이는 작업은 하지 않도록 주의하자.
• InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리됨.
  • 버퍼 풀의 크기를 줄이거나 늘리기 위한 단위 크기로 사용됨.
• InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어) 로 인해 내부 잠금 경합을 많이 유발했음.
  • 이 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선됨.
    • 버퍼 풀이 여러 개의 작은 버퍼 풀로 쪼개지면서 개별 버퍼 풀 전체를 관리하는 잠금(세마포어) 자체도 경합이 분산되는 효과를 내게 됨.
  • innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개로 분리해서 관리할 수 있음.
    • 각 버퍼 풀을 버퍼 풀 인스턴스라고 표현한다.
  • 기본적으로 버퍼 풀 인스턴스의 개수는 8개로 초기화 됨.
    • 전체 버퍼 풀을 위한 메모리 크기가 1GB 미만이면 버퍼 풀 인스턴스는 1개만 생성된다.
    • 버퍼 풀로 할당할 수 있는 메모리 공간이 40GB 이하 수준이면 기본 값인 8을 유지하고 메모리가 크다면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는게 좋다.

버퍼 풀의 구조

• InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
  • 페이지 크기는 innodb_page_size 시스템 변수로 설정된 값임.
• 버퍼 풀의 페이지 크기 조각을 관리하기 위해 InnoDB 스토리지 엔진은 아래 3개의 자료 구조를 관리함.
  • LRU (Least Recently Used) 리스트
  • 플러시 (Flush) 리스트
  • 프리 (Free) 리스트

프리 (Free) 리스트

• InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록
• 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우에 사용됨.

LRU 리스트 (Least Recently Used)

• 엄밀하게 LRU 와 MRU(Most Recently Used) 리스트가 결합된 형태이다.
  • Old 서브리스트 영역은 LRU 에 해당한다.
  • New 서브리스트 영역은 MRU 에 해당한다.
• LRU 리스트를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화 하는 것임.
• InnoDB 스토리지 엔진에서 데이터를 찾는 과정을 대략 다음과 같음.
  1. 필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사한다.
     1. InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색한다.
     2. 해당 테이블의 인덱스 (B-Tree) 를 이용해 버퍼 풀에서 페이지 검색한다.
     3. 버퍼 풀에 이미 데이터 페이지가 있다면 해당 페이지의 포인터를 MRU 방향으로 승급한다.
  2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가한다.
  3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면, MRU 헤더 부분으로 이동한다.
     1. Read Ahead 와 같이 대량 읽기의 경우, 디스크의 데이터 페이지가 버퍼 풀로 적재는 되지만, 실제 쿼리에서 사용되지 않을 수 있음. 이런 경우 MRU 로 이동되지 않음)
  4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 최근에 접근했었는지에 따라 나이(Age)가 부여됨.
     1. 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 데이터 페이지에 부여된 나이가 오래됨. (Aging) → 결국 해당 페이지는 버퍼 풀에서 제거된다. (Eviction)
     2. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화 되어 다시 젊어지고 MRU 헤더 부분으로 옮겨진다.
        1. 버퍼 풀 내부에서 최근 접근 여부에 따라 데이터 페이지는 서로 경쟁하면서 MRU 또는 LRU 로 이동하게 된다.
        2. InnoDB 스토리지 엔진은 LRU 의 끝으로 밀려난 데이터 페이지들을 버퍼 풀에서 제거해서 새로운 데이터 페이지를 적재할 빈 공간을 준비한다.
  5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가한다.
• 처음 한 번 읽힌 데이터 페이지가 이후 자주 사용된다면 그 데이터 페이지는 InnoDB 버퍼 풀의 MRU 영역에 계속 살아남게 됨.
  • 반대로 거의 사용되지 않으면 새롭게 디스크에서 읽히는 데이터 페이지들에 밀려 LRU 끝으로 밀려나 결국은 InnoDB 버퍼 풀에서 제거될 것임.

플러시 (Flush) 리스트

• 디스크로 동기화되지 않은 데이터를 가진 데이터 페이지(= 더티 페이지)의 변경 시점 기준의 페이지 목록을 관리한다.
  • 디스크에서 읽은 상태 그대로 전혀 변경이 없다면 플러시 리스트에서 관리되지 않음.
  • 일단 한 번 데이터 변경이 가해진 데이터 페이지는 플러시 리스트에 관리되고 특정 시점이 되면 디스크로 기록되야함.
• 데이터가 변경되면 InnoDB 는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다.
  • 리두 로그의 각 엔트리는 특정 데이터 페이지와 연결된다.
  • 리두 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 항상 보장하지 않는다. 반대의 경우도 발생할 수 있음.
    • InnoDB 스토리지 엔진은 체크포인트를 발생시켜서 디스크의 리두 로그와 데이터 페이지의 상태를 동기화시킴.
    • 체크 포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구시킬지 판단하는 기준점을 만드는 역할을 한다.

버퍼 풀과 리두 로그

• InnoDB 버퍼 풀과 리두 로그는 매우 밀접관 관계를 맺고 있음.
• InnoDB 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정할수록 쿼리의 성능이 빨라진다.
  • 이미 디스크의 모든 데이터 파일이 버퍼 풀에 적재될 정도의 버퍼 풀 공간이면 그 이상의 버퍼 풀 크기를 늘리는 것은 성능에 도움되지 않음.
    • 그렇지 않다면 디스크의 데이터가 버퍼 풀 메모리로 적재되면 성능이 좋아질 것임.
  • InnoDB 버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 데이터 캐시와 쓰기 버퍼링이라는 두가지 방법이 있음.
    • 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상 시키는 것이다.
• InnoDB 버퍼 풀의 쓰기 버퍼링 기능 까지 향상시키려면 InnoDB 버퍼 풀과 리두 로그와의 관계를 이해해야 함.

• InnoDB 버퍼 풀은
  • 디스크에서 읽은 상태로 전혀 변경되지 않은 클린 페이지(Clean Page)
  • INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 더티 페이지(Dirty Page)
  • 를 가지고 있음.
• 더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 언젠가 디스크로 기록돼야 함.
  • 하지만 더티 페이지는 버퍼 풀에 무한정 머무를 수 없다.
• InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.
  • 즉, 데이터 변경이 계속 발생하면 리두 로그 파일에 기록됐던 로그 엔트리는 어느 순간 다시 새로운 로그 엔트리로 덮어 쓰임.
    • 따라서, InnoDB 스토리지 엔진은 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 함.
      • 재사용 불가능한 공간을 활성 리두 로그(Active Redo Log) 라고 함.
      • 위 그림에서 리두 로그에서 가리기코 있는 InnoDB 버퍼 풀의 각 영역들이 활성 리두 로그 공간이다.
• 리두 로그 파일의 공간은 계속 순환되어 재사용 되지만, 매번 기록될 때마다 로그 포지션은 계속 증가된 값을 갖게 됨.
  • 그 값을 LSN(Log Sequence Number) 라고 함.
• InnoDB 스토리지 엔진은 주기적으로 체크포인트를 발생시켜 리두 로그와 버퍼 풀의 더티페이지를 디스크로 동기화 한다.
  • 이렇게 발생한 체크포인트 중, 가장 최근 체크 포인트 지점의 LSN 이 활성 리두 로그 공간의 시작점이 된다.
  • 하지만 활성 리두 로그 공간의 마지막은 계속 증가하기 때문에 체크 포인트와 무관함.
  • 가장 최근 체크 포인트의 LSN 과 마지막 리두 로그 엔트리의 LSN 차이를 체크 포인트 에이지 (Checkpoint Age) 라고 한다.
    • 활성 리두 로그 공간의 크기를 말한다.
• InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가진다.
  • 체크포인트가 발생하면 체크포인트 LSN 보다 작은 리두 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화돼야 함.
  • 당연히 체크포인트 LSN 보다 작은 LSN 값을 가진 리두로그 엔트리도 디스크로 동기화돼야 함.
• 예시를 살펴 보자. → 이 예제에서 설명하는 걸 이해 하지 못함...
  • InnoDB 버퍼 풀은 100GB 이고, 리두 로그 파일의 전체 크기가 100MB 인 경우
    • 리두 로그 파일의 크기가 100MB 밖에 안되므로 체크 포인트 에이지도 최대 100MB 만 허용한다.
    • ex) 평균 리두 로그 엔트리가 4KB 였다면 25600 개 (100MB/4KB) 정도의 더티 페이지만 버퍼 풀에 보관할 수 있게 된다.
    • 데이터 페이지가 16KB 라고 가정하면 허용 가능한 전체 더티 페이지 크기는 400MB 수준밖에 안됨
    • → 결국 이 예시는 버퍼 풀의 크기는 매우 크지만 실제 쓰기 버퍼핑을 위한 효과는 거의 못보는 상황임.
  • InnoDB 버퍼 풀은 100MB 이고, 리두 로그 파일의 전체 크기가 100GB 인 경우
    • 위 예시와 동일한 방식으로 계산해보면 대략 400GB 정도의 더티 페이지를 가질 수 있다.
    • 하지만 버퍼 풀의 크기가 100MB 이기 때문에 최대 허용 가능한 더티 페이지는 100Mb 크기가 된다. (물론 InnoDB 버퍼 풀의 여러 가지 설정으로 인해 100MB 까지는 아님)
  • 리두 로그 공간이 무조건 크다고 좋은게 아님.
    • 서비스를 운영하다 보면 급작스러운 디스크 쓰기가 발생할 가능성이 높음.
    • 버퍼 풀에 더티 페이지의 비율이 높은 상태에서 갑자기 버퍼 풀이 필요해지는 상황이 오면 InnoDB 스토리지 엔진은 매우 많은 더티 페이지를 한 번에 기록해야 하는 상황이 옴.
    • 처음 부터 리두 로그 파일의 크기를 적절히 선택하기 어렵다면 버퍼 풀의 크기가 100GB 이하의 MySQL 서버에서는 리두 로그 파일의 전체 크기를 약 5~10GB 정도로 선택하고 필요에 따라 조금씩 늘려가며 최적값을 선택하는게 좋음.

버퍼 풀의 크기가 100GB 라고 해서 리두 로그의 공간이 100GB 가 돼야 하는건 아님. 일반적으로 리두 로그는 변경분만 가지고 있고, 버퍼 풀은 데이터 페이지를 통쨰로 가지고 있기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만 있으면 된다.

버퍼 풀 플러시(Buffer Pool Flush)

• 5.6 버전까지 InnoDB 스토리지 더티 페이지 플러시 기능이 있었지만 부드럽게 처리 되지 않았음.
  • 급작스럽게 디스크 기록이 폭증해서 MySQL 서버의 사용자 쿼리 처리 성능에 영향을 받는 경우가 많았음.
• 5.7 버전을 거쳐 8.0버전으로 업그레이드 되면서 대부분의 서비스에서 더티 페이지를 디스크에 동기화 하는 부분 (더티 페이지 플러시)에서 예전과 같은 디스크 쓰기 폭증 현상은 발생하지 않음.
• InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같은 2개의 플러시 기능을 백그라운드로 실행함.
  • 플러시 리스트(Fluish_list) 플러시
  • LRU 리스트(LRU_list) 플러시

플러시 리스트 플러시

• InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 오래된 리두 로그 엔트리가 사용하는 공간을 비워야 함.
  • 오래된 리두 로그 공간이 지워지려면 우선 InnoDB 버퍼 풀의 더티 페이지가 먼저 디스크에 동기화 돼야 함.
  • InnoDB 스토리지 엔진은 주기적으로 플러시 리스트(Flush_list) 플러시 함수를 호출해서 오래전에 변경된 데이터 페이지 순서대로 디스크에 동기화 하는 작업을 수행함.
    • 이때 언제부터 얼마나 많은 더티 페이지를 한번에 디스크로 기록하냐에 따라 사용자 쿼리 처리에 악영향을 받는지 안받는지 결정됨.
  • InnoDB 스토리지 엔진은 플러시를 위한 아래 시스템 변수를 제공함.
    • innodb_page_cleaners
    • innodb_max_dirty_pages_pct_lwm
    • innodb_max_dirty_pages_pct
    • innodb_io_capacity
    • innodb_op_capacity_max
    • innodb_flush_neighbors
    • innodb_adaptive_flushing
    • innodb_adaptive_flushing_lwm
• InnoDB 스토리지 엔진에서 더티 페이지를 디스크로 동기화 하는 스레드를 클리너 스레드 (Cleaner Thread) 라고 함.
• innodb_page_cleaners 시스템 변수는 클리너 스레드의 개수를 조정함.
• InnoDB 스토리지 엔진은 여러 개의 InnoDB 버퍼 풀 인스턴스를 동시에 사용할 수 있음.
  • innodb_page_cleaners 설정 값이 버퍼 풀 인스턴스 개수보다 많은 경우 innodb_buffer_pool_instances 설정값으로 자동으로 변경함.
  • innodb_page_cleaners 설정 값이 버퍼 풀 인스턴스 개수보다 적은 경우 하나의 클리너 스레드가 여러 개의 버퍼 풀 인스턴스를 처리함.
  • 따라서 가능하면 innodb_page_cleaners 설정값과 innodb_buffer_pool_instances 설정값을 동일하게 가져가자.
• InnoDB 버퍼 풀은 클린 페이지 뿐만 아니라 사용자의 DML (INSERT, UPDATE, DELETE)에 의해 변경된 더티 페이지도 함꼐 가지고 있음.
  • InnoDB 버퍼 풀은 한계가 있기에 무한정 더티 페이지를 그대로 유지할 수 없음.
    • 기본적으로 InnoDB 스토리지 엔진은 전체 버퍼 풀이 가진 페이지의 90% 까지 더티 페이지를 가질 수 있음.
      • 때로 이 값이 너무 높을 수 있음.
    • innodb_max_dirty_pages_pct 라는 시스템 변수를 통해 더티 페이지 비율을 조정할 수 있음.
  • 일반적으로 InnoDB 버퍼 풀은 더티 페이지를 많이 가지고 있을 수록 버퍼링함으로써 여러 번의 디스크 쓰기를 한 번으로 줄이는 효과를 극대화 할 수 있음.
    • innodb_max_dirty_pages_pct 설정 값은 가능한 기본 값을 유지하는게 좋음.
• InnoDB 버퍼 풀에 더티 페이지가 많을수록 디스크 쓰기 폭발 (Disk IO Burst) 현상이 발생할 가능성이 높아짐.
  • InnoDB 스토리지 엔진은 innodb_io_capacity 시스템 변수에 설정된 값을 기준으로 더티 페이지 쓰기를 실행함.
    • 만약 디스크로 기록되는 더티페이지 개수보다 더 많은 더티 페이지가 발생하면 버퍼 풀에 더티 페이지가 계속 증가하게 됨.
    • 어느 순간 더티 페이지의 비율이 90%를 넘어가게 되면 InnoDB 스토리지 엔진은 갑자기 더티 페이지를 디스크로 기록해야 한다고 판단함.
    • → 디스크 쓰기 폭증 현상 발생
      • 이 문제를 완화 하기 위해 InnoDB 스토리지 엔진에서 innodb_max_dirty_pages_pct_lwm 시스템 변수를 이용해 일정 수준 이상의 더티 페이지가 발생하면 조금씩 더티 페이지를 디스크로 기록하게 함.
      • innodb_max_dirty_pages_pct_lwm 기본 값은 10% 수준임.
        • 만약 더티 페이지의 비율이 얼마 되지 않은 상태에서 디스크 쓰기가 많이 발생하고 더티 페이지의 비율이 너무 낮은 상태로 계속 머물러 있다면 innodb_max_dirty_pages_pct_lwm 값을 좀더 높이는 것도 디스크 쓰기 횟수를 줄이는 효과를 얻을 수 있음.
• innodb_io_capacity 와 innodb_io_capacity_max 시스템 변수는 각 데이터베이스 서버에서 어느 정도의 디스크를 읽고 쓰기가 가능한지 설정하는 값임
  • innodb_io_capacity 는 일반 상황에서 디스크가 적절히 처리할 수 있는 수준의 값을 설정
  • innodb_io_capacity_max 는 디스크가 최대의 성능을 발휘할 때 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정함.
    • 여기서 디스크 읽고 쓰기란, InnoDB 스토리지 엔진의 백그라운드 스레드가 수행하는 디스크 작업을 의미함.
    • 대부분, InnoDB 버퍼 풀의 더티 페이지 쓰기가 여기에 해당됨.
    • 이외에도 InoDB 스토리지 엔진은 사용자의 쿼리를 처리하기 위해 디스크 읽기도 해야함.
      • 따라서, 현재 장착된 디스크가 초당 1000 IOPS 를 처리할 수 있다고 해서 이 값 그대로 innodb_io_capacity , innodb_io_capacity_max 변수에 설정하면 안됨.

innodb_io_capacity, innodb_io_capacity_max 시스템 변수가 1000 으로 설정한다고 해서 초당 1000번의 디스크 쓰기를 보장하는것은 아님. InnoDB 스토리지 엔진 내부 최적화 알고리즘을 통해 설정된 값을 기준으로 적당히 계산된 횟수만큼 더티 페이지 쓰기를 함. → 어느 정도의 디스크 쓰기를 하는지 관찰하고 분석하여 패턴을 익혀야 함.

• 관리해야할 MySQL 서버가 많으면 일일이 서버 트래픽을 보면서 innodb_io_capacity , innodb_io_capacity_max 값을 설정하는게 상당히 번거로움
  • 위 문제를 해결하기 위해 InnoDB 스토리지 엔진은 어댑티브 플러시 (Adaptive flush) 기능을 제공함.
    • 켜고 끌 수 있음. 기본 값은 어댑티브 플러시를 사용하는 것임.
    • 이 기능이 활성화 되면 InnoDB 스토리지 엔진은 단순히 버퍼 풀의 더티 페이지 비율이나 innodb_io_capacity , innodb_io_capacity_max 값에 의존하지 않고 새로운 알고리즘을 사용함.
    • 더티 페이지를 어느 정도 디스크로 기록해야 할것인지 == 어느 정도 속도로 더티 페이지가 생성 되는지 == 리두 로그가 어느 정도 속도로 증가되는지 분석
      • 정도로 이해할 수 있다.
    • 따라서 어댑티브 플러시 알고리즘은 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행함.
    • innodb_adaptive_Flushing_lwm 시스템 변수 기본값은 10% 임.
      • 이는 전제 리두 로그 공간에서 활성 리두 로그 공간이 10% 미만일떄 어댑티브 플러시가 작동하지 않고 10% 를 넘어서면 어댑티프 플러시 알고리즘이 작동하게 됨.
• innodb_flush_neighbors 시스템 변수는 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지 중에서 더티 페이지가 있다면 InnoDB 스토리지 엔진이 함꼐 묶어 디스크로 기록하게 해주는 기능을 활성화 시킬지 말지 결정해줌.
  • 예전에 HDD 에 디스크 읽기 쓰기는 고비용의 작업이었음.
    • 데이터베이스 서버들은 한 번이라도 디스크 읽고 쓰기를 줄이기 위해 많은 노력을 함.
    • innodb_flush_neighbors 는 그 고민의 결과물임. (이웃 페이지 동시 쓰기)
  • 데이터 저장을 하드디스크로 하고 있으면 innodb_flush_neighbors 값을 1 또는 2로 활성화 하는게 좋음
  • 요즘은 SSD 를 사용하므로 기본 값인 비활성 모드로 유지하는게 좋음.

LRU 리스트 플러시

• InnoDB 스토리지 엔진은 LRU 리스트 에서 사용 빈도가 낮은 데이터 페이지를 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 함.
  • LRU 리스트(LRU_list) 플러시 함수가 사용됨
  • InnoDB 스토리지 엔진은 LRU 리스트 끝 부분부터 시작해서 최대 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼 페이지를 스캔한다.
    • InnoDB 스토리지 엔진은 스캔하면서 더티 페이지는 디스크에 동기화 한다.
    • 클린 페이지는 즉시 프리(Free) 리스트 페이지로 옮긴다.
    • InnoDB 스토리지 엔진은 버퍼 풀 인스턴스 별로 최대 innodb_lru_scan_depth 개수만큼 스캔함
      • 실질적으로 LRU 리스트 스캔은 innodb_buffer_pool_instances * innodb_lru_scan_depth 수만큼 수행함.

버퍼 풀 상태 백업 및 복구

• InnoDB 서버의 버퍼 풀은 쿼리 성능과 매우 밀접하다.
• 쿼리 요청이 매우 빈번한 서버를 셧다운했다가 다시 시작하고 서비스를 시작하면 쿼리 처리 성능이 평소보다 1/10도 안되는 경우가 대부분임.
  • 평상시에는 버퍼 풀에 쿼리들이 사용할 데이터가 이미 준비돼 있으므로 디스크에서 데이터를 읽지 않아도 쿼리가 처리될 수 있었기 때문
  • 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍 업(Warming Up) 이라고 표현함.
  • 버퍼 풀이 잘 워밍업된 상태는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보임.
  • 5.5 버전에서는 점검을 위해 MySQL 서버를 셧다운했다가 다시 시작하는 경우, 서비스 오픈 전, 강제 워밍업을 위해 주요 테이블과 인덱스에 대해 풀 스캔을 한 번씩 실행하고 서비스를 오픈했었음.
  • 5.6버전부터 버퍼 풀 덤프 및 적재 기능이 도입됨
    • 서버 점검 등의 이유로 MySQL 서버 재시작해야할 경우, 서버 셧다운 전에 innodb_buffer_pool_dump_now 시스템 변수를 통해 현재 InnoDB 버퍼 풀 상태를 백업할 수 있음.
      • 다시 MySQL 서버 시작시 innodb_buffer_pool_dump_now 변수를 통해 백업된 버퍼 풀의 상태를 다시 복구할 수 있음

      // MySQL 서버 셧다운 전에 버퍼 풀의 상태 백업
      mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;
      
      // MySQL 서버 재 시작 후, 백업된 버퍼 풀의 상태 복구
      mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

    • InnoDB 버퍼풀 백업은 데이터 디렉터리의 ib_buffer_pool 이라는 이름의 파일로 생성됨.
      • 이 파일 크기는 InnoDB 버퍼 풀이 아무리 크더라도 몇십 MB 이하임.
        • 이는 InnoDB 스토리지 엔진이 버퍼 풀의 LRU 리스트에 적재된 데이터 페이지의 메타 정보만 가져와 저장하기 때문
        • 따라서 버퍼 풀의 백업은 매우 빨리 완료됨.
        • 하지만 백업된 버퍼 풀의 내용을 다시 버퍼 풀로 복구하는 과정은 InnoDB 버퍼 풀 크기에 따라 오래걸릴 수 있음.
          • 백업된 내용에서 각 테이블의 데이터 페이지를 다시 디스크에서 읽어와야 하기 때문임.
        • InnoDB 스토리지 엔진은 버퍼 풀을 다시 복구하는 과정이 어느 정도 진행됐는지 확인할 수 있는 상태 값을 제공함.

        mysql> SHOW STATUS LIKE `Innodb_buffer_pool_dump_status'\G

    • 버퍼 풀 적재 작업이 너무 오래걸려 중간에 멈추려면 innodb_buffer_pool_load_abort 시스템 변수를 이용하면 됨.
      • InnoDB 버퍼 풀을 다시 복구하는 작업은 상당히 많은 디스크 읽기를 필요로 하기 때문에 버퍼 풀 복구가 실행 중인 상태에서 서비스 재개하는 것은 좋은 방법이 아님.
      • 따라서 다음과 같이 버퍼 풀 복구를 멈추고 서비스를 재개하는걸 권장함.

      mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;

    • 지금까지의 과정(버퍼 풀 백업 & 복구 작업)을 수동으로 하기 쉽지 않음.
      • InnoDB 스토리지 엔진은 MySQL 서버가 셧다운 되기 직전에 버퍼 풀의 백업을 실행하고, MySQL 서버가 시작되면 자동으로 백업된 버퍼 풀의 상태를 복구할 수 있는 기능을 제공함.
        • 이 자동화를 실행하려면 innodb_buffer_pool_dump_at_shutdown 과 innodb_buffer_pool_load_at_startup 설정을 MySQL 서버 설정 파일에 넣어두면 됨.

InnoDB 버퍼풀 백업은 ib_buffer_pool 파일에 기록됨. 근데, 이게 반드시 셧다운하기 직전의 파일일 필요는 없음. InnoDB 스토리지 엔진은 ib_buffer_pool 파일에서 데이터 페이지 목록을 가져와서 실제 존재하는 데이터 페이지면 InnoDB 버퍼 풀로 적재하고, 그렇지 않은 경우 무시함. 따라서 실제 존재하지 않은 데이터 페이지 정보가 ib_buffer_pool 파일에 명시돼어있어도 MySQL 서버가 비정상적으로 종료되지는 않음.

버퍼 풀의 적재 내용 확인

• 5.6 버전부터 MySQL 서버의 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDb 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있음.
• InnoDB 버퍼 풀이 큰 경우에는 이 테이블 조회가 상당히 큰 부하를 일으키면서 서비스 쿼리가 느려지는 문제가 있었음..
  • 그래서 실제 서비스용으로 사용되는 MySQL 서버에서 버퍼 풀의 상태를 확인하는게 거의 불가능했음.
• 8.0 부터 이 문제를 해결하기 위해 information_schema 테이터베이스에 innodb_cached_indexes 테이블이 새로 추가됨.
  • 이 테이블을 통해 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있음.