MySQL architecture - Adaptive Hash Indexes
Adaptive Hase Index(AHI)
workload와 충분한 buffer pool memory의 적절한 조합하에서, transactional 특성이나 reliability에 위배되지 않는 선에서, InnoDB를 in-memory database처럼 동작하게 한다.
1. 관련 파라메터
- innodb_adaptive_hash_index
- default : ON
- Scope : Global
- dynamic Variable : Yes
- turn off option : –skip-innodb_adaptive_hash_index
- disable하면 hash table을 즉시 비운다. hash table을 사용하여 실행중이던 쿼리는 B-tree 인덱스를 직접 access하여 처리된다.
2. 동작
- MySQL은 search 패턴을 고려하여 index prefix로 hash index를 만든다
- 인덱스 페이지가 자주 access 되는 경우 필요시 hash index가 생성된다.
- hash index는 자주 access되는 인덱스 페이지에 대해서만 부분적으로 생성될 수도 있다.
- 만약 테이블 전체가 메모리에 들어갈수 있는 사이즈인 경우, hash index는 쿼리 속도를 향상시킨다. 인덱스를 pointer와 같이 동작하게 해서 원하는 로우를 바로 다이렉트하게 찾을수 있다.
- InnoDB는 index search 패턴을 모니터링하는 메커니즘을 가지고 있다. 해당 쿼리가 hash index를 사용하면 이익이라고 판단되면, 자동으로 hash index를 생성한다.
- workload에 따라, hash index를 통한 속도향상이 hash index를 유지관리하는 비용보다 더 이득일수도 있고 아닐수도 있다.
- heavy workload하에서는(such as multiple concurrent joins), adaptive has index를 접근할때 획득하는 read/write lock이 contention의 원인이 되기도 한다.
- LIKE % 쿼리의 경우에도 AHI로 별 이득을 얻지 못한다.
- AHI가 필요없는 workload하에서는 이를 disable해서 불필요한 overhead를 줄이도록 한다.
3. monitoring
- adaptive hash index로 인한 contention 확인하는 방법
- SHOW ENGINE INNODB STATUS
- SEMAPHORE 섹션에 RW-latch를 대기하는 thread가 여러개 있다면, adaptive hash indexing을 disable하는 것이 더 좋을 수도 있다.