[RealMysql 8.0]04아키텍처 4.11.1 ~ 4.2.12 어댑티브 해시 인덱스

• 리두 로그 아카이빙
  •  사전적 의미로 '보관'이라는 뜻을 가진 아카이빙은 개인 및 단체가 활동하며, 남기는 수많은 기록물 중 가치가 있는 것을 선별하여 보관하는 장소, 또는 그 기록물 자체를 이르는 용어를 뜻한다.
  • https://ju-hyung.tistory.com/151

아카이빙 (Archiving) 이란?

• 8.0 버전부터 innoDb 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가되었다.
• 데이터베이스의 모든 변경 내용을 기록한 redo 로그를 포함한다.
  • 보관 목저은 오류 또는 충돌 시 데이터베이스 안전성과 일관성을 보장하기 위함
• 데이터 손실을 방지하고 장애 발생 시 데이터베이스를 복구할 수 있도록 리두 로그를 정기적으로 아카이브 하는것이 중요하다.

 

- 리두 로그 활성화 및 비활성화

• 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화 되지않는 반면, 리두로그는 항상 디스크로 기록된다.
• 8.0 이전 버전까지는 리두 로그를 비활성화 할 수 있는 방법이 없었지만, 8.0 버전부터는 데이터 복구하거나 대용량 데이터를 한번에 적재하는 경우 리두 로그를 비활성화 할 수 있다.
  • 데이터 적재 후 리두 로그를 다시 활성화 하는것을 잊지말자.

- 어댑티브 해시 인덱스

• 일반적인 인덱스라 하면 테이블에 사용자가 생성해둔 B-Tree 인덱스를 의미하지만 어댑티브 해시 인덱스는 InnoDb 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이며, innodb_adaptive_hash_index 시스템 변수를 이용해 활성화 비활성화를 제어한다.
• B-Tree는 루트로드 -> 브랜치 노드 -> 리프노드까지 찾아가서 레코드를 읽는데, 기본적인 성능은 매우 빠르지만, 극한의 작업을 하게될시 당연히 성능은 떨어진다.
• 어댑티브 해시 인덱스는 이러한 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이다.
• innodb 스토리지 엔진은 자주 읽히는 데이터 페이지의 키값을 이용해 해시 인덱스를 만들고 필요할 때마다 인덱스를 검색해서 레코드가 저장된 페이지를 즉시 찾아갈 수 있다.
• 예전 버전까지는 어댑티브 해시 인덱스는 하나의 메모리 객체인 이유로 어댑티브 해시 인덱스의 경합이 상당히 심했다.
• 그래서 8.0 부터는 내부잠금(세마포어) 경합을 줄이기 위해 어댑티브 해시 인덱스의 파티션 가능을 제공한다
  • 내부 잠금(internal lock)은 Mysql에서 여러 스레드 또는 프로세스에 의해 데이터베이스에 대한 액세스를 동기화하는 데 사용되는 메커니즘이다. 이것은 Mysql에서 세마포어로 알려져 있다. 내부 잠금의 목적은 데이터가 일관되게 유지되고 여러 스레드 또는 프로세스가 동시에 동일한 데이터를 수정하지 않도록 하는 것.
  • 파티션 개수는 변경할 수 있는데 기본값은 8개이며 이며 늘릴 수 있다.
• 활성화가 되면 좋은 경우와 비활성화가 오히려 좋은 경우도 있다.
  • 어댑티브 해시 인덱스의 도움이 되는 경우:
    • 디스크의 데이터가 innodb의 버퍼 풀 크기와 비슷하 경우(디스크 읽기가 많지 않은 경우)
    • 동등 조건 검색(동등 비교와 in 연산자)이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우 
  • 어댑티브 해시 인덱스의 도움이 되지 않는 경우:
    • 디스크 읽기가 많은경우나, 특정 패턴의 쿼리가 많은경우(join 이나 like 패턴 검색), 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우..
  • 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀) 내에서 접근하는 것을 더 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다는 점.
  • 어댑티브 해시 인덱스는 '공짜 점심'이 아니다, 저장 공간인 메모리를 사용하며, 떄때로 매우 큰 메모리 공간을 사용할 수 있다.
  • 테이블을 삭제하거나 변경하려고 하면 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거 해야한다. 이로인해 많은 cpu 자원을 사용하고 서버의 성능이 느려질 수 있다.
• 어댑티브 해시 인덱스의 효율성 판단
  • 가장 쉬운 방법은 mysql 서버의 상태 값들을 살펴보는 것이다.
  • show engine innodb status 로 알아볼 수 있으며,
  • hash serches/s, non-hash serches/s 의 비율을 보고 판단한다 100%로 가까울 수록 효율이 좋다.
• 추천 글
  • https://tech.kakao.com/2016/04/07/innodb-adaptive-hash-index/

--음...실제로 우리디비 보니..히트 타겟이라 해야하나..거의 없는거 같은데..-,.-...출근해서 한번 더 살펴봐야겠다..ㅎㅎ

MySQL InnoDB의 Adaptive Hash Index 활용