MySQL5.6 버퍼 풀을 스캔에 견딜 수 있도록 한다

14.13.1.3 버퍼 풀을 스캔에 견딜 수 있도록 한다

InnoDB는 LRU 알고리즘을 엄격하게 사용하는 것이 아니라, 버퍼 풀 에 읽은 이후 다시 액세스되는 데이터의 양을 최소화하기 위해 기술을 사용합니다. 목표는 예측 이나 전체 테이블을 스캔 하면 후속 액세스 될지 모르는 새로운 블록을 읽은 경우에도 자주 액세스되는 페이지 ( "핫 페이지")이 확실히 버퍼 풀에 남아 있도록하는 것 입니다.

새로 읽은 블록은 LRU리스트의 중간에 삽입됩니다. 새로 읽은 모든 페이지는 기본적으로 LRU리스트의 끝에서 3/8 에 해당하는 장소에 삽입됩니다. 이 페이지는 처음 버퍼 풀에 액세스 할 때 목록의 전면 (최근에 사용 된 끝)로 이동됩니다. 따라서 액세스 할 수없는 페이지는 결코 LRU리스트의 전면 부분은 이동되지 않고 정확한 LRU 방식의 경우보다 빨리 "오래된"입니다. 이 위치는 LRU 목록이 2 개의 세그먼트로 분할됩니다. 즉, 삽입 지점의 하류에있는 페이지는 "이전"으로 간주되며 LRU 새우 션의 바람직한 대상이됩니다.

InnoDB 버퍼 풀의 내부 동작과 그 LRU 교체 알고리즘에 대한 자세한 내용은 섹션 8.9.1 "InnoDB 버퍼 풀" 을 참조하십시오.

LRU리스트의 삽입 지점을 제어하고 InnoDB가 같은 최적화를 테이블 또는 인덱스 스캔에 의해 버퍼 풀에 읽은 블록에도 적용할지 여부를 선택할 수 있습니다. 구성 매개 변수 innodb_old_blocks_pct 는 LRU리스트의 "이전"블록의 비율 (%)을 제어합니다. innodb_old_blocks_pct 의 기본값은 37 이며, 원래 고정 된 3/8의 비율로 대응합니다. 이 값의 범위는 5 (버퍼 풀의 새로운 페이지가 곧 낡아집니다)부터 95 (버퍼 풀의 5 % 밖에 핫 페이지로 예약되지 않기 때문에 알고리즘이 익숙한 LRU 방식에 가깝습니다 )까지입니다.

버퍼 풀을 예측하여 혼란 상태가되지 않도록 유지하는 최적화는 테이블 또는 인덱스 스캔에 의한 유사한 문제를 방지 할 수 있습니다. 이 검사는 일반적으로 데이터 페이지는 빠르게 연속해서 몇 번 읽혔 이후 다시는 액세스되지 않습니다. 구성 매개 변수 innodb_old_blocks_time 는 한 페이지에 처음으로 방문한 후 해당 페이지를 LRU리스트의 전면 (최근에 사용 된 끝)으로 이동되지 않고 접근 가능 시간 창 (밀리 초)을 지정 합니다. MySQL 5.6.6 이전에는 innodb_old_blocks_time 의 기본값은 0 이며, 처음 버퍼 풀에 액세스 할 때 페이지를 버퍼 풀의 목록의 최근에 사용 된 가장자리로 이동하는 원래의 동작에 대응 합니다. 이 값을 크게하면 더 많은 블록이 버퍼 풀에서 빨리 오래 될 가능성이 있습니다. MySQL 5.6.6의 시점에서 innodb_old_blocks_time 의 기본값은 표준의 성능을 향상시키기 위해 1000 으로 증가했습니다.

innodb_old_blocks_pct 과 innodb_old_blocks_time 모두 역동적이고 글로벌이며, MySQL 옵션 파일 ( my.cnf 또는 my.ini )로 지정하거나 SET GLOBAL 명령 실행시 변경할 수 있습니다. 이 설정을 변경하려면 SUPER 권한이 필요합니다.

이러한 매개 변수를 설정 한 경우의 효과 측정에 도움이되는 SHOW ENGINE INNODB STATUS 명령은 추가 통계를보고합니다. BUFFER POOL AND MEMORY 섹션은 다음과 같습니다.

Total memory allocated 1107296256; in additional pool allocated 0
Dictionary memory allocated 80360
Buffer pool size   65535
Free buffers       0
Database pages     63920
Old database pages 23600
Modified db pages  34969
Pending reads 32
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 414946, not young 2930673
1274.75 youngs/s, 16521.90 non-youngs/s
Pages read 486005, created 3178, written 160585
2132.37 reads/s, 3.40 creates/s, 323.74 writes/s
Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000
Pages read ahead 1510.10/s, evicted without access 0.00/s
LRU len: 63920, unzip_LRU len: 0
I/O sum[43690]:cur[221], unzip sum[0]:cur[0]

Old database pages 는 LRU리스트의 "이전"세그먼트 내의 페이지 수입니다.
Pages made young 와 not young 는 각각 새로 워진 「오래된」페이지의 총수가되어 있지 않은 페이지 수입니다.
youngs/s 및 non-young/s 는 각각이 명령의 마지막 호출 뒤, "이전"페이지에 페이지 액세스에 의해이 같은 페이지가 새로 워진 비율로되어 있지 않은 비율입니다.
young-making rate 와 not 은 "오래된"페이지에 액세스뿐만 아니라 전반적인 버퍼 풀 액세스 점에서 볼 때 같은 비율을 보여줍니다.

참고

InnoDB 모니터의 출력으로 표시되는 초당 평균은 현재의 시간과 InnoDB 모니터의 출력이 마지막으로 출력 된 시간 사이의 경과 시간에 따라 있습니다.

이러한 매개 변수의 효과는 하드웨어 구성, 사용하는 데이터 및 워크로드에 대한 자세한 따라 크게 다를 수 있기 때문에 성능이 중요한 환경과 프로덕션 환경에서 이러한 설정을 변경하기 전에 항상 벤치마킹을 통해 그 유효성을 확인하십시오.

대부분의 활동이 큰 검사로 이어지는 정기적 인 배치 보고서 쿼리를 포함 OLTP 유형 인 혼합 워크로드 환경에서 일괄 처리 실행 중에 innodb_old_blocks_time 값을 설정하면 일반 작업의 작업 집합을 버퍼 풀에 유지하는 데 도움이 될 수 있습니다.

버퍼 풀에 완전히 들어 가지 않는 큰 테이블을 스캔하는 경우 innodb_old_blocks_pct 을 작은 값으로 설정하면 1 번 밖에 읽지 데이터가 버퍼 풀의 큰 부분을 소모하는 것은 없습니다. 예를 들어, innodb_old_blocks_pct=5 를 설정하면 1 번 밖에 읽지 데이터가 버퍼 풀의 5 %로 제한됩니다.

메모리에 들어가는 작은 테이블을 스캔하는 경우, 버퍼 풀에서 페이지를 이동하기위한 오버 헤드가 낮기 때문에 innodb_old_blocks_pct 을 기본값으로 유지하거나 경우에 따라서는 ( innodb_old_blocks_pct=50 등) 증가 수 수 있습니다.

innodb_old_blocks_time 매개 변수의 효과는 비교적 효과적으로 작은 innodb_old_blocks_pct 매개 변수에 비해 예측이 곤란하고, 작업 부하에 따른 변동도 커집니다. 최적 값에 도달하려면 innodb_old_blocks_pct 조정을 통한 성능 향상이 불충분 한 경우는 자신의 벤치 마크를 실시합니다.

InnoDB 버퍼 풀의 자세한 내용은 섹션 8.9.1 "InnoDB 버퍼 풀" 을 참조하십시오.