innodb_adaptive_hash

Tech Note 정보: kimdubi 님이 작성하신 글입니다.; 카테고리: [ MariaDB ]; 게시됨: 07 March 2018; 작성됨: 07 March 2018; 최종 변경: 07 March 2018; 조회수: 6834

-1. innodb_adaptive_hash_index란?

mysql, mariaDB의 대표적인 인덱스는 B-Tree 구조이며 primary key , Secondary key ( 인덱스 키 +PK ) 가 있음

CREATE TABLE "test" (
  "id" varchar(20) COLLATE utf8_bin NOT NULL,
  "test1" varchar(128) COLLATE utf8_bin NOT NULL,
  "test2" varchar(128) COLLATE utf8_bin NOT NULL,
  "test3" varchar(128) COLLATE utf8_bin NOT NULL,
  "ptitles" varchar(256) COLLATE utf8_bin NOT NULL,
  "pdescriptions" varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  "plife_cycle" varchar(128) COLLATE utf8_bin NOT NULL,
  "pstates" varchar(128) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY ("id"),
  KEY "seconday_key" ("test1","test2","test3")

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

mysql / mariaDB는 테이블의 데이터가 primary key 순으로 정렬되어 저장되기 때문에

Secondary key ( 인덱스 키 + PK) 의 경우,

특정 데이터를 찾기 위해서는 1) PK 찾기 -> 2) PK를 통해 원하는 데이터로 찾아감

=> 자주 사용 되는 컬럼은 매번 B-Tree 를 타지 않고도 바로 접근하기 위해

자주 사용되는 컬럼을 memory에 hash_index로 생성하는 것이 innodb_adpative_hash_index 기능임

-2. 관련 변수

-. innodb_adaptive_hash_index

=> my.cnf에 innodb_adaptive_hash_index=1 혹은

set global innodb_adaptive_hash_index=1 명령어로 on/off 할 수 있음

-. innodb_adaptive_hash_index_partitions

=> memory에 올라와 있는 hash_index를 partitioning 해서 효율을 높히는 변수

my.cnf 에 innodb_adaptive_hash_index_partitions=4 혹은

set global innodb_adaptive_hash_index_partitions=4 명령어로 파티셔닝 수를 지정 가능 (1~64)

MariaDB [(none)]> show variables like '%adaptive%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| innodb_adaptive_hash_index            | ON        |
| innodb_adaptive_hash_index_partitions | 16        |
+---------------------------------------+-----------+

-. Innodb_mem_adaptive_hash

=> innodb_adaptive_hash_index에 할당된 메모리 크기값으로 innodb_buffer_pool_size 의 1/64 만큼 초기 할당 되며

최대 사용 가능 메모리 양을 지정할 수 없기 때문에 현재 사용되는 메모리양을 주기적으로 모니터링 해야 함

MariaDB [(none)]> show global status like '%adaptive%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Innodb_mem_adaptive_hash | 1520393472 |
+--------------------------+------------+

MariaDB [(none)]> show variables like 'innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 42949672960 |
+-------------------------+-------------+

-. 관련 통계정보 확인

MariaDB [(none)]> show global status like 'Innodb_adaptive_hash%';
+----------------------------------------+------------+
| Variable_name                          | Value      |
+----------------------------------------+------------+
| Innodb_adaptive_hash_cells             | 42499631   |
| Innodb_adaptive_hash_heap_buffers      | 0          |
| Innodb_adaptive_hash_hash_searches     | 21583      |
| Innodb_adaptive_hash_non_hash_searches | 3768761684 |
+----------------------------------------+------------+

=> show global status like 명령어로 innodb_adaptive_hash_index 사용 관련 통계정보 확인가능

-3. 주의사항

-. 자주 사용되는 데이터 hashing 을 옵티마이저가 판단하기 때문에 제어가 어려움

-. innodb_adaptive_hash_index 를 사용하는 시스템의 경우,

더 이상 사용하지 않는 테이블을 drop 하는 경우에도 해당 테이블의 data가 hashing 되어 있다면

hash memory를 비우는 과정에서 쿼리응답속도가 늦어지는 등의 이슈발생 가능성이 있음

ID 생성