[MySQL][InnoDB] 메모리구조

■ Bufferpool 

버퍼 풀은 InnoDB가 액세스 할 때 테이블 및 인덱스 데이터를 캐시하는 메인 메모리의 영역입니다. 버퍼 풀은 자주 사용하는 데이터를 메모리에서 직접 처리 할 수있게하여 처리 속도를 높입니다. 전용 서버에서 실제 메모리의 최대 80%정도가 버퍼 풀에 할당됩니다.

 

대량 읽기 조작의 효율성을 위해 버퍼 풀은 여러 행을 보유 할 수있는 페이지로 분할됩니다. 캐시 관리 효율성을 위해 버퍼 풀은 링크 된 페이지 목록으로 구현됩니다. 거의 사용되지 않는 데이터는 다양한 LRU 알고리즘을 사용하여 캐시에서 종료(혹은 만료:aged out)됩니다.

 

버퍼 풀을 활용하여 자주 액세스하는 데이터를 메모리에 유지하는 노하우(기술)는 MySQL 튜닝의 중요한 측면입니다.

 

▶ Buffer Pool LRU Algorithm

버퍼 풀은 가장 최근에 사용 된 (LRU) 알고리즘의 다양성을 사용하여 목록으로 관리됩니다. 버퍼 풀에 새 페이지를 추가하기 위해 공간이 필요한 경우 가장 최근에 사용한 페이지가 제거되고 새 페이지가 목록의 중간에 추가됩니다. 이 중간 점 삽입 전략은 목록을 두 개의 하위 목록으로 취급합니다.

 

• 헤드(처음)부분, 최근에 액세스한 새로운 ("신규") 페이지들의 서브리스트

• 테일(끝)부분, 덜 최근에 액세스한 오래된 페이지들의 서브리스트

 

Figure 14.2 Buffer Pool List

 

 

이 알고리즘은 새 하위 목록에 많은 페이지들을 유지합니다. 이전 서브리스트는 덜 사용 된 페이지를 포함합니다. 이 페이지는 제거될 후보입니다.

기본적으로 알고리즘은 다음과 같이 운영된다.

 

• 버퍼풀의 3/8이 오래된 서브리드트로 보내짐(등록됨.)

• 목록의 중간 점은 새 서브리스트의 끝부분이 이전 하위 서브리스트의 윗 부분(헤드)과 만나는 경계입니다.

• InnoDB는 페이지를 버퍼 풀로 읽을 때 처음에 중간 지점(이전 하위 목록의 헤드)에 페이지를 삽입합니다. 페이지는 SQL 쿼리와 같은 사용자 시작 작업에 필요하거나 InnoDB에 의해 자동으로 수행되는 미리 읽기 작업의 일부로 필요하므로 읽을 수 있습니다.

• 이전 하위 목록의 페이지에 액세스하면 "젊은"페이지가되어 새 하위 목록의 헤드로 이동합니다. 사용자가 시작한 작업에 의해 페이지가 필요했기 때문에 페이지를 읽은 경우 첫 번째 액세스가 즉시 발생하고 페이지가 젊어집니다. 미리 읽기 작업으로 인해 페이지를 읽은 경우 첫 번째 액세스는 즉시 발생하지 않으며 페이지가 제거되기 전에 전혀 발생하지 않을 수 있습니다.

• 데이터베이스가 작동함에 따라, 버퍼 풀에서 액세스되지 않은 페이지는 목록의 꼬리쪽으로 이동하여“연령”입니다. 새로운 하위 목록과 이전 하위 목록 모두의 페이지는 다른 페이지로 새로 만들어집니다. 이전 하위 목록의 페이지도 중간 지점에 페이지가 삽입 될 때 만료됩니다. 결국, 사용되지 않은 채로 남아있는 페이지는 이전 하위 목록의 끝에 도달하여 제거됩니다.

 

기본적으로 쿼리에서 읽은 페이지는 즉시 새 하위 목록으로 이동하므로 버퍼 풀에 더 오래 머무 릅니다. 예를 들어, mysqldump 작업 또는 WHERE 절이없는 SELECT 문에 대해 수행 된 테이블 스캔은 새 데이터가 다시 사용되지 않더라도 많은 양의 데이터를 버퍼 풀로 가져와 동일한 양의 이전 데이터를 제거 할 수 있습니다 . 마찬가지로 미리 읽기 백그라운드 스레드에 의해로드되고 한 번만 액세스되는 페이지는 새 목록의 헤드로 이동됩니다. 이러한 상황은 자주 사용되는 페이지를 이전 하위 목록으로 푸시하여 제거 될 수 있습니다. 이 동작을 최적화하는 방법에 대한 자세한 내용은 14.8.3.3 절“버퍼 풀 검색 방지 만들기”및 14.8.3.4 절“InnoDB 버퍼 풀 프리 페치 구성 (미리 읽기)”을 참조하십시오.

 

InnoDB 표준 모니터 출력은 버퍼 풀 LRU 알고리즘의 작동과 관련하여 BUFFER POOL AND MEMORY 섹션에 여러 필드를 포함합니다.

 

▶ Buffer Pool Configuration

성능 향상을 위해 버퍼 풀의 다양한 측면을 구성 할 수 있습니다.

• 이상적으로는 버퍼 풀의 크기를 실제 값보다 크게 설정하여 서버의 다른 프로세스가 과도한 페이징없이 실행되도록 충분한 메모리를 남겨 둡니다. 버퍼 풀이 클수록 더 많은 InnoDB가 메모리 내 데이터베이스처럼 작동하여 디스크에서 데이터를 한 번 읽은 다음 후속 읽기 중에 메모리에서 데이터에 액세스합니다.

• 충분한 메모리가있는 64 비트 시스템에서 버퍼 풀을 여러 부분으로 분할하여 동시 작업 중 메모리 구조에 대한 경합을 최소화 할 수 있습니다.

• 많은 양의 자주 액세스하지 않는 데이터를 버퍼 풀로 가져 오는 조작으로 인해 갑자기 활동이 급증하더라도 자주 액세스하는 데이터를 메모리에 유지할 수 있습니다.

• 페이지가 곧 필요할 것으로 예상하여 미리 읽기 요청을 수행하여 페이지를 버퍼 풀로 비동기식으로 프리 페치하는시기와 방법을 제어 할 수 있습니다.

• 백그라운드 플러시가 발생하는시기와 플러시 속도가 워크로드에 따라 동적으로 조정되는지 여부를 제어 할 수 있습니다.

• 서버가 다시 시작된 후 긴 워밍업 기간을 피하기 위해 InnoDB가 현재 버퍼 풀 상태를 유지하는 방법을 구성 할 수 있습니다.

 

+ 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 표준 모니터 출력이 마지막으로 출력된 된 이후의 경과 시간을 기준으로 합니다.

 

Table 14.2 InnoDB Buffer Pool Metrics

Name	Description
Total memory allocated	버퍼 풀에 할당 된 총 메모리 (바이트)입니다.
Dictionary memory allocated	InnoDB 데이터 사전에 할당 된 총 메모리 (바이트)입니다.
Buffer pool size	버퍼 풀에 할당 된 총 페이지 크기입니다.
Free buffers	버퍼 풀 사용 가능 목록의 총 페이지 크기입니다.
Database pages	버퍼 풀 LRU 목록의 총 페이지 크기입니다.
Old database pages	버퍼 풀 이전 LRU 서브리스트의 총 페이지 크기입니다.
Modified db pages	버퍼 풀에서 수정 된 현재 페이지 수입니다.
Pending reads	버퍼 풀로 읽기 대기중인 버퍼 풀 페이지 수 입니다.
Pending writes LRU	LRU 목록의 맨 아래에서 쓸 버퍼풀 내의 오래된 더티 페이지 수입니다.
Pending writes flush list	체크포인트 작성동안 플러시 할 버퍼 풀 페이지 수 입니다.
Pending writes single page	버퍼 풀 내에서 보류중인 독립 페이지 쓰기 수 입니다.
Pages made young	버퍼 풀 LRU 목록에서 최신으로 만들어진 총 페이지 수 ("새로운" 페이지의 하위 목록 처음으로 이동)
Pages made not young	버퍼 풀 LRU 목록에서 새롭게 만들지 않은 총 페이지 수(신규로 만들지 않고 "오래된" 하위 목록에 남아 있는 페이지 수).
youngs/s	페이지를 신규 만드는 버퍼 풀 LRU 목록의 이전 페이지에 대한 초당 평균 액세스 수입니다. 자세한 내용은이 표 뒤에 나오는 참고 사항을 참조하십시오.
non-youngs/s	페이지를 신규로 만들지 않은 버퍼 풀 LRU 목록의 이전 페이지에 대한 초당 평균 액세스 수입니다. 자세한 내용은 이 표 뒤에 나오는 참고 사항을 참조하세요.
Pages read	버퍼 풀에서 읽은 총 페이지 수입니다.
Pages created	버퍼 풀 내에 작성된 총 페이지 수입니다.
Pages written	버퍼 풀에서 쓴 총 페이지 수입니다.
reads/s	초당 평균 버퍼 풀 페이지 읽기 수입니다.
creates/s	초당 생성 된 초당 평균 버퍼 풀 페이지 수입니다.
writes/s	초당 평균 버퍼 풀 페이지 쓰기 수입니다.
Buffer pool hit rate	버퍼 풀 메모리와 디스크 스토리지에서 읽은 페이지의 버퍼 풀 페이지 적중률입니다.
young-making rate	페이지 액세스의 평균 적중률로 인해 페이지가 젊어졌습니다. 자세한 내용은이 표 뒤에 나오는 참고 사항을 참조하세요.
not (young-making rate)	페이지 액세스의 평균 적중률로 인해 페이지가 젊어지지 않았습니다. 자세한 내용은이 표 뒤에 나오는 참고 사항을 참조하세요.
Pages read ahead	미리 읽기 작업의 초당 평균입니다.
Pages evicted without access	버퍼 풀에서 액세스하지 않고 제거 된 페이지의 초당 평균입니다.
Random read ahead	임의 미리 읽기 작업의 초당 평균입니다.
LRU len	버퍼 풀 LRU 목록의 총 페이지 크기입니다.
unzip_LRU len	버퍼 풀 unzip_LRU 목록의 총 페이지 크기입니다.
I/O sum	지난 50 초 동안 액세스 한 총 버퍼 풀 LRU 목록 페이지 수입니다.
I/O cur	액세스 한 총 버퍼 풀 LRU 목록 페이지 수
I/O unzip sum	액세스 한 총 버퍼 풀 unzip_LRU 목록 페이지 수입니다.
I/O unzip cur	액세스 한 총 버퍼 풀 unzip_LRU 목록 페이지 수입니다.

 

참고사항:

• youngs/s(신규/초) 측정값은 오래된 페이지에만 적용됩니다. 페이지 수가 아니라 페이지에 대한 접근 횟수를 기준으로 합니다. 주어진 페이지에 대한 다중 액세스가 있을 수 있으며, 모두 계산됩니다. 큰 스캔이 발생하지 않을 때 매우 낮은 youngs/s(신규/초) 값이 나타나는 경우, 지연 시간을 줄이거나 이전 하위 목록에 사용된 버퍼 풀의 비율을 증가시켜야 할 수 있습니다. 백분율을 높이면 이전 서브리스트가 더 커지기 때문에 해당 서브리스트의 페이지가 꼬리로 이동하는데 더 오래 걸리기 때문에 해당 페이지는 다시 액세스하여 젊게 만들 가능성이 커지게 됩니다.

 

• 비 youngs/s(신규/초) 메트릭은 이전 페이지에만 적용할 수 있다. 페이지 수가 아니라 페이지에 대한 접근 횟수를 기준으로 합니다. 주어진 페이지에 대한 다중 액세스가 있을 수 있으며, 모두 계산됩니다. 큰 테이블 스캔을 수행할 때 더 높은 비 youngs/s(신규/초) 값(그리고 더 높은 youngs/s(신규/초) 값)이 보이지 않으면 지연 값을 증가시킵니다.

 

• young-making(신규생성) 제작률은 모든 버퍼 풀 페이지에 대한 액세스를 설명하며, 이전 서브리스트의 페이지에만 액세스하는 것이 아닙니다. young-making(신규생성)층과 그렇지 않은 비율은 일반적으로 전체 버퍼 풀 적중률에 합치지는 않습니다. 이전 서브리스트의 페이지 히트는 페이지를 새 서브리스트로 이동시키지만, 새로운 서브리스트의 페이지 히트는 페이지가 헤드에서 일정 거리인 경우에만 리스트의 맨 위로 이동하게 합니다.

 

• not (young-making:신규생성 제작률)은 innodb_old_blocks_time에 의해 정의된 지연으로 인해 페이지를 young(신규)로 만들지 못하거나 새로운 서브리스트의 페이지 히트율(적중률)로 인해 페이지가 처음(head)로 이동되지 않은 평균 히트율(적중률)이다. 이 비율은 이전 하위 목록의 페이지에 대한 액세스뿐만 아니라 모든 버퍼 풀 페이지에 대한 액세스를 설명합니다.

 

Buffer pool server status variables and the INNODB_BUFFER_POOL_STATS table provide many of the same buffer pool metrics found in InnoDB Standard Monitor output. For more information, see Example 14.10, “Querying the INNODB_BUFFER_POOL_STATS Table”.

버퍼 풀 서버 상태 변수와 INNODB_BUFFER_POOL_STATS 표는 InnoDB Standard Monitor 출력에서 볼 수 있는 많은 버퍼 풀 메트릭을 제공합니다.

 

 

 

■ 변경 버퍼

변경 버퍼는 해당 페이지가 버퍼 풀에 없을 때 보조 인덱스 페이지의 변경 사항을 캐시하는 특수 데이터 구조입니다. INSERT, UPDATE 또는 DELETE 조작 (DML)으로 인해 버퍼링 된 변경 사항은 나중에 다른 읽기 조작으로 페이지를 버퍼 풀에로드 할 때 병합됩니다.

 

Figure 14.3 Change Buffer

 

클러스터형 인덱스와 달리 보조 인덱스는 일반적으로 고유하지 않으며 보조 인덱스에 대한 삽입은 비교적 임의의 순서로 수행됩니다. 마찬가지로, 삭제 및 업데이트는 인덱스 트리에 인접하지 않은 보조 인덱스 페이지에 영향을 줄 수 있습니다. 영향을받는 페이지를 다른 조작으로 버퍼 풀로 읽을 때 나중에 캐시 된 변경 사항을 병합하면 디스크에서 보조 인덱스 페이지를 버퍼 풀로 읽는 데 필요한 실질적인 임의 액세스 I/O를 피할 수 있습니다.

 

시스템이 대부분 유휴 상태이거나 느리게 종료되는 동안 실행되는 제거작업은 정기적으로 업데이트된 인덱스 페이지를 디스크에 씁니다. 제거작업은 각 값이 디스크에 즉시 기록된 경우보다 일련의 인덱스값에 대한 디스크 블록을 보다 효율적으로 기록 할 수 있습니다.

 

영향을 받는 행과 업데이트 할 보조 인덱스가 많은 경우 변경 버퍼 병합에 몇 시간이 걸릴 수 있습니다. 이 시간 동안 디스크 I/O가 증가하여 디스크 바운드 쿼리 속도가 크게 저하 될 수 있습니다. 트랜잭션이 커밋된 후에도 서버가 종료되고 다시 시작된 후에도 변경 버퍼 병합이 계속 발생할 수 있습니다 (자세한 내용은 14.22.2 절“InnoDB 복구 강제 수행”참조).

 

메모리에서 변경 버퍼는 버퍼 풀의 일부를 차지합니다. 디스크에서 변경 버퍼는 데이터베이스 서버가 종료 될 때 인덱스 변경이 버퍼링되는 시스템 테이블 스페이스의 일부입니다.

 

변경 버퍼에 캐시 된 데이터 유형은 innodb_change_buffering 변수에 의해 관리됩니다. 자세한 내용은 변경 버퍼링 구성을 참고하세요. (아래에 내용 있음) 최대 변경 버퍼 크기를 구성 할 수도 있습니다. 자세한 내용은 버퍼 최대 크기 변경 구성을 참고하세요. (아래에 내용 있음)

 

인덱스에 내림차순 인덱스 열이 포함되어 있거나 기본 키에 내림차순 인덱스 열이 포함되어 있으면 보조 인덱스에 대한 변경 버퍼링이 지원되지 않습니다.

 

▶ 변경 버퍼링(Change Buffering) 구성

테이블에서 INSERT, UPDATE 및 DELETE 작업을 수행 할 때 보통 인덱스된 열의 값(특히 보조 키 값)이 정렬되지 않은 순서로 되어있는 경우가 많은데 이때 보조 인덱스를 최신 상태로 유지하기 위해 상당한 I/O가 필요합니다. 변경 버퍼는 관련 페이지가 버퍼 풀에 없을 때 보조 인덱스 항목에 대한 변경 사항을 캐시하므로 디스크에서 페이지 즉시 읽기를 회피해 비용이 많이 생기는 I/O 조작을 피할 수 있습니다. 버퍼링 된 변경 사항은 페이지가 버퍼 풀에로드 될 때 병합되고 업데이트된 페이지는 나중에 디스크로 플러시됩니다. InnoDB 메인 스레드는 서버가 거의 유휴 상태이고 느리게 종료되는 동안 버퍼링 된 변경 사항을 병합합니다.

 

디스크 읽기 및 쓰기 횟수가 줄어들기 때문에 변경버퍼 기능은 대량 삽입과 같은 대량의 DML 작업을 수행하는 응용 프로그램과 같이 I/O 바운드 작업에 가장 유용합니다.

 

그러나 변경 버퍼는 버퍼 풀의 일부를 차지하므로 데이터 페이지를 캐시하는 데 사용 가능한 메모리가 줄어 듭니다. 작업 세트가 버퍼 풀에 거의 맞거나 테이블에 보조 인덱스가 비교적 적은 경우 변경 버퍼링을 비활성화하는 것이 유용 할 수 있습니다. 작업 데이터 세트가 버퍼 풀에 완전히 맞는 경우 변경 버퍼링은 버퍼 풀에없는 페이지에만 적용되므로 추가 오버 헤드가 발생하지 않습니다.

 

innodb_change_buffering 구성 매개 변수를 사용하여 InnoDB가 변경 버퍼링을 수행하는 범위를 제어 할 수 있습니다. 삽입, 삭제 조작 (인덱스 레코드가 처음으로 삭제된 것으로 표시되는 경우) 및 제거 조작 (인덱스 레코드가 실제로 삭제 된 경우)에 대한 버퍼링을 사용 가능 또는 사용 불가능하게 할 수 있습니다. 업데이트 작업은 삽입과 삭제의 조합입니다. 기본 innodb_change_buffering 값은 all입니다.

 

허용되는 innodb_change_buffering 값은 다음과 같습니다.

+ all

기본값 : buffer inserts, delete-marking operations, and purges.

 

+ none

작업을 버퍼링하지 마십시오.

 

+ inserts

버퍼 삽입 작업.

 

+ deletes

버퍼 삭제 표시 작업

 

+ changes

삽입 및 삭제 표시 작업을 모두 버퍼링합니다.

 

+ purges

백그라운드에서 발생하는 버퍼 물리적 삭제 작업

 

MySQL 옵션 파일 (my.cnf 또는 my.ini)에서 innodb_change_buffering 매개 변수를 설정하거나 전역 시스템 변수를 설정하기에 충분한 특권이 있다면 SET GLOBAL문을 통해 동적으로 변경할 수 있습니다. 5.1.8.1 절.“시스템 변수 권한”을 참조하십시오. 설정을 변경하면 새 작업의 버퍼링에 영향을줍니다. 기존 버퍼링 된 항목의 병합에는 영향을 미치지 않습니다.

 

▶ 변경 버퍼 최대 크기 구성

innodb_change_buffer_max_size 변수를 사용하면 변경 버퍼의 최대 크기를 버퍼풀의 총 크기에 대한 백분율로 구성할 수 있습니다. 기본적으로 innodb_change_buffer_max_size는 25로 설정되어 있습니다. 최대 설정은 50입니다.

 

변경 버퍼 병합이 새 변경 버퍼 항목과 보조를 맞추지 않아 변경 버퍼가 최대 크기 제한에 도달하는 경우, 삽입, 업데이트 및 삭제 활동이 많은 MySQL 서버에서 innodb_change_buffer_max_size를 늘리십시오.

 

보고에 사용되는 정적 데이터로 MySQL 서버에서 innodb_change_buffer_max_size를 줄이거 나 변경 버퍼가 버퍼 풀과 공유되는 메모리 공간을 너무 많이 소비하여 페이지가 버퍼 풀에서 원하는 시간보다 빨리 만료되는 경우를 고려하세요.

 

대표적인 워크로드로 다른 설정을 테스트하여 최적의 구성을 결정합니다. innodb_change_buffer_max_size 설정은 동적이므로 서버를 다시 시작하지 않고도 설정을 수정할 수 있습니다.

 

▶ 변경 버퍼 모니터링

변경 버퍼 모니터링에는 다음과 같은 옵션을 사용할 수 있습니다.

+ InnoDB 표준 모니터 출력에는 변경 버퍼 상태 정보가 포함됩니다. 모니터 데이터를 보려면 SHOW ENGINE INNODB STATUS 문을 실행합니다.

mysql> SHOW ENGINE INNODB STATUS\G;

 

버퍼 변경 상태 정보는 INSERT BUFFER AND ADAPTIVE HASH INDEX 라는 문구 아래에 있으며 다음과 유사하게 나타납니다.

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

 

+ INFORMATION_SCHEMA.INNODB_METRICS 테이블은 InnoDB 표준 모니터 출력에 있는 대부분의 데이터 포인트와 기타 데이터 포인트를 제공합니다. 변경 버퍼 메트릭과 각각에 대한 설명을 보려면 다음 쿼리를 실행합니다.

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

 

+ INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 테이블은 변경 버퍼 인덱스 및 변경 버퍼 비트 맵 페이지를 포함하여 버퍼 풀의 각 페이지에 대한 메타 데이터를 제공합니다. 변경 버퍼 페이지는 PAGE_TYPE으로 식별됩니다. IBUF_INDEX는 변경 버퍼 인덱스 페이지의 페이지 유형이고 IBUF_BITMAP은 변경 버퍼 비트 맵 페이지의 페이지 유형입니다.

 

경고

INNODB_BUFFER_PAGE 테이블을 쿼리하면 상당한 성능 오버 헤드가 발생할 수 있습니다. 성능에 영향을주지 않으려면 테스트 인스턴스에서 조사하려는 문제를 재현하고 테스트 인스턴스에서 쿼리를 실행하는게 좋습니다.

 

예를 들어, INNODB_BUFFER_PAGE 테이블을 쿼리하여 대략적인 IBUF_INDEX 및 IBUF_BITMAP 페이지 수를 총 버퍼 풀 페이지의 백분율로 판별 할 수 있습니다.

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100)) 
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

 

+ 성능 스키마는 고급 성능 모니터링을위한 변경 버퍼 뮤텍스 대기 계측을 제공합니다. 변경 버퍼 계측을 보려면 다음 쿼리를 실행하니다.

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+

 

 

 

■ 적응적 해쉬 인덱스

적응 형 해시 인덱스 기능을 통해 InnoDB는 트랜잭션 기능이나 안정성을 희생하지 않고도 적절한 워크로드 조합과 버퍼풀에 충분한 메모리 조합을 갖춘 시스템에서 메모리내 데이터베이스와 같은 성능을 발휘할 수 있습니다. 적응형 해시 인덱스 기능은 innodb_adaptive_hash_index 변수에 의해 활성화되거나 --skip-innodb-adaptive-hash-index에 의해 서버 시작시 꺼집니다.

 

관찰된 검색 패턴을 기반으로 해시 인덱스는 인덱스키의 접두사를 사용하여 작성됩니다. 접두어는 임의의 길이일 수 있으며 B-트리의 일부 값만 해시 인덱스에 표시 될 수 있습니다. 해시 인덱스는 자주 액세스하는 인덱스 페이지에 대해 요청시 작성됩니다.

 

테이블이 거의 대부분 주 메모리에 맞으면 해시 인덱스는 요소를 직접 조회하여 인덱스 값을 일종의 포인터로 전환하여 쿼리 속도를 높일 수 있습니다. InnoDB에는 인덱스 검색을 모니터링하는 메커니즘이 있습니다. InnoDB가 쿼리가 해시 인덱스를 구축함으로써 이익을 얻을 수 있음을 알아 차리면 자동으로 그렇게합니다.

 

일부 워크로드에서 해시 인덱스 조회의 속도 향상은 인덱스 조회를 모니터링하고 해시 인덱스 구조를 유지 관리하는 추가 작업보다 훨씬 뛰어납니다. 적응형 해시 인덱스에 대한 액세스는 여러 개의 동시 조인과 같이 많은 작업 부하에서 경합의 원천이 될 수 있습니다. LIKE 연산자 및 % 와일드 카드를 사용한 쿼리도 도움이되지 않습니다. 적응형 해시 인덱스 기능의 이점이 없는 워크로드의 경우 이를 끄면 불필요한 성능 오버 헤드가 줄어 듭니다. 적응형 해시 인덱스 기능이 특정 시스템 및 워크로드에 적합한지 여부를 미리 예측하기 어렵 기 때문에 활성화 및 비활성화된 벤치 마크 실행을 고려해야 합니다. MySQL 5.6의 아키텍처 변경으로 인해 이전 릴리스보다 적응 형 해시 인덱스 기능을 비활성화하는 것이 더 적합합니다.

 

MySQL 5.7에서는 어댑티브 해시 인덱스 기능이 분할되어 있습니다. 각 인덱스는 특정 파티션에 바인딩되며 각 파티션은 별도의 래치로 보호됩니다. 분할은 innodb_adaptive_hash_index_parts 변수에 의해 제어됩니다. 이전 릴리스에서는 적응 형 해시 인덱스 기능이 단일 래치로 보호되어 워크로드가 많은 경우 경합 지점이 될 수 있었습니다. innodb_adaptive_hash_index_parts 변수는 기본적으로 8로 설정되어 있습니다. 최대 설정은 512입니다.

 

SHOW ENGINE INNODB STATUS 출력의 SEMAPHORES 섹션에서 적응 형 해시 인덱스 사용 및 경합을 모니터링 할 수 있습니다. btr0sea.c에서 작성된 RW 래치를 기다리는 스레드가 많으면 적응 해시 인덱스 파티션 수를 늘리거나 적응 해시 인덱스 기능을 비활성화합니다.

 

■ Log Buffer

로그 버퍼는 디스크의 로그 파일에 기록 될 데이터를 보유하는 메모리 영역입니다. 로그 버퍼 크기는 innodb_log_buffer_size 변수에 의해 정의됩니다. 기본 크기는 16MB입니다. 로그 버퍼의 내용은 주기적으로 디스크로 플러시됩니다. 큰 로그버퍼를 사용하면 트랜잭션이 커밋되기전에 재실행 로그 데이터를 디스크에 쓸 필요없이 큰 트랜잭션을 실행할 수 있습니다. 따라서 많은 행을 업데이트, 삽입 또는 삭제하는 트랜잭션이 있는 경우 로그 버퍼 크기를 늘리면 디스크 I/O가 절약됩니다.

 

innodb_flush_log_at_trx_commit 변수는 로그 버퍼의 내용을 디스크에 쓰고 플러시하는 방법을 제어합니다. innodb_flush_log_at_timeout 변수는 로그 플러시 빈도를 제어합니다.

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※