MySQL8 bufferpool (버퍼풀)

15.5.1 버퍼 풀

버퍼 풀은InnoDB이 접근 시 테이블 및 인덱스 데이터를 캐시 하는 메인 메모리 내의 영역입니다. 버퍼 풀을 사용하면 자주 사용되는 데이터를 메모리에서 직접 처리하는 바람에 처리 속도가 향상됩니다. 전용 서버에서는 대부분의 경우 최대 80%의 물리 메모리를 버퍼 풀에 할당할 수 있습니다.

대용량 읽기 조작의 효율을 높이기 위한 버퍼 풀은 복수행을 유지할 수 있는페이지로 분할됩니다.캐시 관리를 효율을 위해서, 버퍼 풀은 페이지의 링크 리스트로 구현됩니다사용 빈도가 낮은 데이터는LRU알고리즘의 변형을 사용하여 캐시에서 삭제됩니다.

버퍼 풀을 이용하고 자주 방문되는 데이터를 메모리에 유지하는 방법을 이해하는 것은 MySQL튜닝의 중요한 측면입니다.

 

버퍼 풀 LRU알고리즘

버퍼 풀은 최저 사용 빈도(LRU)알고리즘의 변형을 사용하고 목록으로 관리됩니다.버퍼 풀에 새 페이지를 추가하기 위한 영역이 필요한 경우는 가장 최근사되고 있지 않은 페이지가 삭제되고 새 페이지가 리스트의 중앙에 추가됩니다.이 미드(mid) 포인트 삽입 전략은 리스트를  2개의 서브 리스트로서 다뤄집니다.

• 선두는 최근 접속된 "새로운"("젊은") 페이지의 서브 리스트
• 말미는 최근 접속되지 않은 "오래된" 페이지의 서브 리스트

그림 15.2버퍼 풀 리스트

이 알고리즘은 자주 사용하는 페이지를 새 서브 리스트에 유지합니다. 낡은 서브 리스트에는 자주 사용되지 않는 페이지가 포함되어 있습니다.이들 페이지는eviction후보입니다.

기본적으로, 알고리즘은 다음과 같이 동작합니다:

• 버퍼 풀의 3/8이 낡은 서브 리스트에 배정됩니다.
• 리스트의 미드 포인트는 새 서브 리스트의 끝과 낡은 서브 리스트의 선두가 접하는 경계입니다.
• InnoDB는 버퍼 풀에 페이지를 읽을 때 처음에 중간 포인트( 오래된 서브 리스트의 선두)에게 페이지를 삽입합니다. 페이지 판독은 SQL쿼리 등의 사용이 시작하는 작업 또는 InnoDB에 의해서 자동적으로 실행되는 read-ahead 조작이 요구 된 경우에 발생할 수 있습니다.
• 오래된 서브 리스트의 페이지에 접속하면 "새로운" 으로 되어, 새로운 서브 리스트의 선두에 이동합니다. 사용자가 시작한 어느 조작의 요구에 의해서 페이지를 읽어들인 경우, 최초 접속이 즉각 진행되며 페이지는 새로운 페이지로 전환됩니다. read-ahead 조작 때문에 페이지를 읽어들인 경우 첫 접근은 바로 발생되지 않고, 페이지가 삭제될 때까지 전혀 이뤄지지 않을 수도 있습니다.
• 데이터베이스가 동작하면서 접속되지 않는 버퍼 풀 내의 페이지는 리스트의 후방으로 이동되고 "age" 됩니다. 새로운 서브 리스트와 오래된 서브 리스트의 페이지는 다른 페이지로 새롭게 만들어 집니다. 오래된 서브 리스트 내의 페이지도 미드 포인트에 페이지가 삽입되면 유효하게 됩니다. 최종적으로 사용하지 않은 채 오래된 서브 리스트의 말미에 도달한 페이지는 삭제됩니다.

 

기본적으로, 쿼리에 의해서 읽혀진 페이지는 곧 새로운 서브 리스트로 이동됩니다. 즉 이들 페이지는 버퍼 풀에 유지됩니다. 예를 들어mysqldump 또는 WHERE구 없는 SELECT 등에서 실행하는 테이블 스캔은 새로운 데이터가 다시 사용되지 않는 경우도 대량의 데이터를 버퍼 풀에 넣어, 동등한 양의 예전 데이터를 삭제할 가능성이 있습니다. 마찬가지로 read-ahead 백그라운드 스레드에 의해서 로드되고 한번 접근 된 페이지는 새로운 리스트의 선두로 이동됩니다. 이러한 상황에서는 자주 사용하는 페이지가 삭제 대상이 되는 오래된 서브 리스트로 이동될 가능성이 있습니다. 이 동작의 최적화의 자세한 것은,섹션 15.8.3.3"버퍼 풀을 스캔에 견딜 수 있도록 한다"및섹션 15.8.3.4"InnoDB버퍼 풀의 선반입(예측)의 구성"를 참조하십시오.

InnoDB표준 모니터 출력에는 버퍼 풀 LRU알고리즘의 조작에 관한BUFFER POOL AND MEMORY섹션의 몇가지 필드가 포함되어 있습니다.자세한 것은,InnoDB표준 모니터를 사용한 버퍼 풀의 모니터링를 참조하십시오.

 

버퍼 풀 구성

 

버퍼 풀의 다양한 측면을 구성하고 성능을 향상시킬 수 있습니다.

• 이상적으로는 버퍼 풀의 크기를 가급적 큰 값을 설정하고 서버의 다른 프로세스가 과잉 페이징 없이 실행하도록 충분한 메모리를 남깁니다. 버퍼 풀이 클수록 InnoDB는 더욱 더 인메모리 데이터베이스와 같이 동작하며 디스크로1회의 데이터를 읽고, 그 이후에 다시 읽어들일 경우에는 메모리에서 데이터에 접근합니다. 섹션 15.8.3.1"InnoDB버퍼 풀 사이즈의 구성"를 참조하십시오.
• 충분한 메모리가 있는 64비트 시스템에서는 버퍼 풀을 여러 부분으로 나누고, 동시 조작 간 메모리 구조의 경합을 최소화할 수 있습니다. 자세한 것은,섹션 15.8.3.2"복수의 버퍼 풀 인스턴스의 구성"를 참조하십시오.
• 접속 빈도가 낮은 대량의 데이터를 버퍼 풀에 받아들이도록 갑작스러운 스파이크에 관계 없이 자주 방문되는 데이터를 메모리에 유지할 수 있습니다. 자세한 것은,섹션 15.8.3.3"버퍼 풀을 스캔에 견딜 수 있도록 한다"를 참조하십시오.
• read-ahead 리퀘스트를 실행하고 버퍼 풀에 페이지를 비동기적으로 선반입하는 방법과 타이밍을 제어하고, 페이지의 수요가 떨어질 것을 예측합니다.자세한 것은,섹션 15.8.3.4"InnoDB버퍼 풀의 선반입(예측)의 구성"를 참조하십시오.
• 백그라운드 플래시가 발생하는 타이밍 및 플래시률을 워크 로드에 기초하여 동적으로 조정 여부를 제어할 수 있습니다. 자세한 것은, 섹션 15.8.3.5"버퍼 풀의 플래시의 구성"를 참조하십시오.
• 서버의 재기동 후의 긴 워밍업 기간을 회피하기 위해서InnoDB하지만 현재의 버퍼 풀의 상태를 유지하는 방법을 구성할 수 있습니다. 자세한 것은,섹션 15.8.3.6"버퍼 풀의 상태의 보존과 복원"를 참조하십시오.

InnoDB표준 모니터를 사용한 버퍼 풀의 모니터링

SHOW ENGINE INNODB STATUS를 사용하고 접근할 수 있는InnoDB표준 모니터 출력에서는 버퍼 풀의 조작에 관한 메트릭이 제공됩니다.버퍼 풀 메트릭은InnoDB표준 모니터 출력의BUFFER POOL AND MEMORY섹션에 있으며 다음과 같이 표시됩니다:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

다음 테이블에서는InnoDB표준 모니터에 의해서 리포트하는 버퍼 풀 메트릭에 대해서 설명합니다.

InnoDB표준 모니터 출력으로 제공되는 초당 평균은InnoDB표준 모니터 출력이 마지막으로 출력된 이후 경과 시간에 근거합니다.

 

표 15.2 InnoDB버퍼 풀 메트릭

이름	설명
할당된 총 메모리	버퍼 풀에 할당된 총 메모리(바이트).
할당된 디크쇼나리메모리ー	InnoDB데이터 사전에 할당된 총 메모리(바이트).
버퍼 풀 사이즈	버퍼 풀에 할당된 페이지 단위의 총 크기.
빈 버퍼	버퍼 풀 빈 목록의 총 크기(페이지 수).
데이터베이스 페이지	버퍼 풀 LRU리스트의 총 크기(페이지 수).
오래된 데이터베이스 페이지	버퍼 풀의 오래된 LRU서브 리스트의 총 크기(페이지 수).
변경된 DB페이지	버퍼 풀로 변경된 현재의 페이지 수.
보류 검침	버퍼 풀로 읽기를 기다리고 있는 버퍼 풀 페이지 수의.
보류 중인 글 LRU	LRU목록 아래에서 올라오는 버퍼 풀 내의 오래된 더티 페이지 수의.
보류 중인 쓰기 플래시 리스트	체크 포인트 중에 플래시되는 버퍼 풀 페이지의 수.
보류 중인 쓰기 단 한페이지	버퍼 풀 내의 보류 중인 독립한 페이지 글의 수.
젊은 페이지	버퍼 풀 LRU목록에서 젊어진 페이지의 합계 수("new"페이지의 서브 리스트의 선두에 이동).
작성된 페이지가 젊지 않아	버퍼 풀 LRU목록에서 젊어지지 않은 페이지( 젊어지지 않은"old"서브 명단에 남아 있는 페이지)합계 수.
youngs/s	페이지를 젊어진 버퍼 풀 LRU목록 내의 오래된 페이지 액세스의 초당 평균. 자세한 사항은 이 테이블 뒤 노트를 참조하세요.
non-youngs/s	버퍼 풀 LRU목록 내의 페이지를 젊은 안 핸 오래된 페이지 액세스의 초당 평균.상세는 이 테이블 뒤 노트를 참조하세요.
읽혀진 페이지	버퍼 풀에서 읽혀진 페이지의 합계 수.
작성된 페이지	버퍼 풀 내에 작성된 페이지의 합계 수.
적힌 페이지	버퍼 풀로부터 올라온 페이지의 합계 수.
reads/s	버퍼 풀 페이지 판독/초의 평균 수.
creates/s	작성된 버퍼 풀 페이지의 초당 평균 수/초.
writes/s	버퍼 풀 페이지 글의 초당 평균 수.
버퍼 풀 히트율	버퍼 풀 메모리에서 읽혀진 페이지와 디스크 기억 영역에서 읽혀진 페이지의 버퍼 풀 페이지 히트율.
젊은 표시율	페이지 접근으로 페이지가 젊어진 평균 히트율. 자세한 사항은 이 테이블 뒤 노트를 참조하세요.
not( 젊은 표시율)	페이지 접근으로 페이지가 젊어지지 않은 평균 히트율. 자세한 사항은 이 테이블 뒤 노트를 참조하세요.
예측된 페이지	read-ahead의 초당 평균.
접근 권한 없이 삭제된 페이지	버퍼 풀로부터 접근하지 않고 삭제된 페이지의 초당 평균.
랜덤 예측	랜덤 예측 조작의 초당 평균.
LRU len	버퍼 풀 LRU리스트의 총 크기(페이지 수).
unzip_LRU len	버퍼 풀의 unzip_LRU리스트의 총 크기(페이지 수).
I/O합계	과거 50초에 접속된 버퍼 풀 LRU리스트 페이지의 합계 수.
I/O cur	접속한 버퍼 풀 LRU리스트 페이지의 합계 수.
I/O unzip sum	접속된 버퍼 풀 unzip_LRU리스트 페이지의 합계 수.
I/O unzip cur	접속된 버퍼 풀 unzip_LRU리스트 페이지의 합계 수.

메모:

• youngs/s 메트릭은 오래된 페이지에만 적용할 수 있습니다. 이는 페이지 수가 아니라 페이지 액세스 수에 근거합니다.특정 페이지로 복수의 접속이 가능하고 그 모든 것이 카운트됩니다. 대규모로 스캔이 발생하지 않을 때에 매우 낮은 youngs/s 값이 표시될 경우는 지연 시간을 단축하거나 오래된 서브 리스트에 사용되는 버퍼 풀의 비중을 늘릴 필요가 있는 경우가 있습니다. 비중을 늘리면 오래된 서브 리스트가 커지는 만큼 그 서브 리스트 내에서 페이지가 말미로 이동하는데 시간이 걸리게 되고, 이들 페이지에 다시 접근해서 새롭게 될 가능성이 높아집니다.
• non-youngs/s 메트릭은 오래된 페이지에만 적용할 수 있습니다. 이는 페이지 수가 아니라 페이지 액세스 수에 근거합니다. 특정 페이지로 복수의 접속이 가능하고 그 모든 것이 카운트됩니다. 대규모 테이블 스캔의 실행 시 non-youngs/s 값이 높지 않은 (또는, youngs/s값이 큰)경우 지연 값을 늘립니다.
• young-making 비율은 오래된 서브 리스트 내의 페이지 액세스뿐 아니라 모든 버퍼 풀 페이지 액세스를 고려합니다. young-making 비율 및 not 비율은 통상 전체적인 버퍼 풀 히트율에 가산되지 않습니다. 오래된 서브 리스트의 페이지 히트(hit) 에 의해 페이지가 새 서브 리스트로 이동하지만, 새로운 서브 리스트의 페이지 히트에 의해서 리스트의 선두로 이동되는 것은 선두부터 일정한 거리에 있는 경우 뿐입니다.
• not (young-making rate)은 innodb_old_blocks_time에서 정의된 지연이 충족되지 않거나 페이지가 헤드로 이동되지 않은 새로운 서브 리스트의 페이지 히트가 원인으로 페이지 액세스의 결과 페이지가 새로워지지 않은 평균 히트율입니다.이 비율은 오래된 서브 목록 내의 페이지 액세스뿐 아니라 모든 버퍼풀 페이지 액세스를 고려합니다.

 

버퍼 풀server status variables토INNODB_BUFFER_POOL_STATS테이블에는InnoDBStandard Monitor출력과 같은 버퍼 풀 메트릭이 다수 준비되어 있습니다. 자세한 것은,사례 15.10"INNODB_BUFFER_POOL_STATS테이블 쿼리"를 참조하십시오.