Tag Archives: MySQL

파티션 제약 극복기! 유니크한 토큰 값을 만들어보자!

Overview

MySQL에는 날짜 별 데이터 관리를 위해 파티셔닝이라는 좋은 기능(?)을 5.1버전부터 무료(!)로 제공합니다. 일정 시간 지난 후 불필요한 데이터는 간단하게 해당 파티셔닝을 제거하면, 굳이 DELETE 쿼리로 인한 오버헤드를 방지할 수 있죠.

그러나, 파티셔닝 적용 시, “파티셔닝 키는 반드시 PK에 포함되어야 한다”, “추가 제약조건(유니크 속성)을 부여할 수 없다”라는 대표적인 제약 조건으로 인하여, 유니크 속성을 가지는 데이터를 파티셔닝 적용이 불가한 경우가 있는데.. 이것을 해결할 수 있는 간단한 트릭을 이 자리에서 설명하고자 합니다. ^^

Plan to..

토큰을 생성하는 경우를 간단하게 생각해볼까요? ^^

토큰, 특히 일정 시간 이후에는 절대로 활용이 되지 않는 Access Token을 생각해본다면.. 수많은 유저들이 어떤 권한을 위해 반드시 발급받아야하는 데이터죠. 기본적으로 이 값은 중복이 발생해서도 안되고, 일정 시간이 지나버리면 폐기해도 무관한 데이터입니다.

  • 반드시 유니크 속성을 보장해야한다.
  • 파티셔닝 관리가 가능해야 한다.

간단하게 스키마를 상상해보면, 아래와 같습니다. ^^

CREATE TABLE access_tokens (
  token         char(64) NOT NULL,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token)
);

자.. 이제 이 엄청난 데이터를 파티셔닝을 하고 싶은데.. 흠.. 가장 간단한 방법으로는 아래처럼 PK제약 조건을 충족하기 위해 PK 속에 파티셔닝 키를 포함시켜서 테이블을 파티셔닝 하는 방법이 있습니다.

CREATE TABLE access_tokens (
  token         char(64) NOT NULL,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token, expires_at)
)
PARTITION BY RANGE COLUMN (expires_at)
(PARTITION PF_20150513 VALUES LESS THAN ('2015-05-14') ,
 PARTITION PF_20150514 VALUES LESS THAN ('2015-05-15') ,
 PARTITION PF_20150515 VALUES LESS THAN ('2015-05-16'));

아.. 그런데 여기서 중요한 문제가.. 반드시 유니크성을 보장해야할 Token을 스키마 레벨에서 완벽하게 보장할 수 없다는 말이죠. 즉, 정말 유니크한지 확인을 하려면 발급할 Token값이 현재 테이블에 존재 여부를 사전에 조회해서 체크 해봐야 합니다. 물론, 랜덤한 Hash로 Token을 발급한다면.. 중복은 매우 희박하기는 하나.. 최악의 경우를 무시할 수는 없을테니요. ^^

How to?

이러한 상황에서 과연 어떤 트릭(?)을 써서 파티셔닝을 하면서 유니크한 Token 발급이 가능할까요? Token 특성 상 굉장히 많은 데이터가 적재될 것이기에.. 테이블 사이즈를 고려하여 파티셔닝을 위한 별도의 칼럼(daynum) 칼럼을 생각해봅시다. 이 값은 smallint 타입으로 2바이트로, PK에 8바이트짜리 datetime 타입이 들어가는 것 보다는 훨씬 유리하죠. 특히나 인덱스를 추가로 만들 경우!! Token에 트릭을 가미하기 위해 기존보다 4바이트가 늘어납니다. 즉, PK는 6바이트 증가!

아래와 같이 테이블을 만들어봅시다.

CREATE TABLE access_tokens (
  token         char(68) NOT NULL,
  daynum        smallint unsigned not null,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token, daynum)
)
PARTITION BY RANGE (daynum)
(PARTITION PF_20150513 VALUES LESS THAN (134),
 PARTITION PF_20150514 VALUES LESS THAN (135),
 PARTITION PF_20150515 VALUES LESS THAN (136));

daynum에는 무슨 값이 들어가냐고요? to_days(now())에서to_days(‘2014-12-31′)을 뺀 값이 들어갑니다. 이렇게 함으로써 단순 2바이트 사이즈로 몇 십년 치(아마도 늙어 죽을 때까지)를 관리할 수 있는 테이블 파티셔닝 관리가 가능한 것이죠.

자, 그럼.. 데이터 처리를 위한 쿼리를 만들어볼까요? Token은 SHA2로 만든다고 가정해봅시다.

## => shar2함수 안의 스트링 값과 날짜 값만 인수로..
insert into access_tokens 
  ( token, daynum, expires_at )
 values 
 (
   concat(SHA2(?, 256), right(hex(TO_DAYS(?)-735963),4)),
   TO_DAYS(?)-735963,
   ?
 );

setString(1, authInfo + nanoTime)
setString(2, expires_at)
setString(3, expires_at)

ex)
insert into access_tokens
 ( token, daynum, expires_at )
values
 (
   concat(SHA2('user1', 256), right(hex(TO_DAYS('2015-05-17')-735963),4)),
   TO_DAYS('2015-05-17')-735963,
   '2015-05-17'
 );

복잡하쥬? 간단하게 말하자면.. SHA2로 64바이트 키를 만들고, 가장 마지막 4글자를 날짜가 가미된 문자열을 넣자는 것입니다. (4글자를 맞추기 위해.. )

결국 PK는 아래와 같은 형태로 관리가 되기에, Token은 유니크 보장이 된다고 할 수 있습니다. ^^ (슈퍼 울트라 캡숑 “꼼수”)

  • Primary Key : (SHA2+날짜, 날짜)
  • “SHA2+날짜”는 유니크 속성 보장

데이터를 넣었으니, 데이터를 끄집어 내는 방법도 고민해봐야겠죠? 매번 조회 시 전체 파티셔닝을 뒤지지 않기 위해서는 원하는 데이터가 위치한 곳의 적절한 파티셔닝 키도 같이 전달을 해야할텐데..  그렇다고, 어플리케이션에서 늘 daynum을 가지고 있을 수는 없는 노릇! Token에서 날짜 정보를 추출할 수 있는 방안을 고안해야 합니다. 아래처럼..

## => 토큰 값만 쿼리에 인수로..
select * from access_tokens 
where daynum = conv(trim((substr(?, 65))), 16, 10) and token = ?;
setString(1, token_string)
setString(2, token_string)

ex)
select *
from access_tokens
where daynum = conv(trim((substr('0a041b9462caa4a31bac3567e0b6e6fd9100787db2ab433d96f6d178cabfce9089', 65))), 16, 10)
and token = '0a041b9462caa4a31bac3567e0b6e6fd9100787db2ab433d96f6d178cabfce9089';

Token에서 65번째 부터 4글자를 가져와서, 그 데이터를 16진수 -> 10진수로 변환하여 최종적으로 daynum을 만들어보자는 것입니다. 실제 호출되는 쿼리는 바로 위 예제처럼 사용되겠죠. ^^

쿼리가 복잡해지기는 하나.. 나름 서버에 부담없이 데이터를 최적으로 추출할 수 있는 방안이라고 봅니다.

기존 토큰 길이가, 64->68로 길어진다는 단점이 있기는 하나.. 음.. 반드시 64글자여야 한다면, SHA2결과 값에서 마지막 4글자를 빼버리는 것도 나쁘지 않다고 생각합니다. 발급된 Token은 스키마 레벨에서 유니크를 보장하고, 날짜별로 파티셔닝도 가능하니.. 일석이조!!

Conclusion

약간(?)의 트릭으로 파티셔닝 제약을 극복하였습니다. 유니크한 Token을 파티셔닝 관리하는.. 유니크 보장을 위해 사전 SELECT 없이도 쿼리 레벨에서 해결할 수 있는 방안이죠.

Token에 날짜가 가미된 스트링을 넣되, PK를 날짜와 같이 엮어서 Token만으로 유니크를 보장하자는 것이 목적입니다.

  • Primary Key : (SHA2+날짜, 날짜) => “SHA2+날짜”는 유니크

정말 간단한 팁같지 않은 팁이기는 하나.. 대규모 Token 관리를 계획하고 있으신 분에게는 꽤 좋은 솔루션이 될 수 있다고 생각해요. 설명장애가 있어서.. 매끄럽지 않았지만.. 의미 전달이 잘 되었기를..

다음에는 또다른 재미난 팁을 소개하도록 할께요. ^^

새벽 4시, 이유없이 디스크 유틸이 튄다면? 디스크 성능에 영향을 주는 크론잡

Overview

새벽에 디스크 성능에 영향을 주는 요소로는 대표적으로 백업과 같은 디비 운영적인 업무가 있습니다. 각 운영 정책에 따라 다르겠지만, 순간적인 시스템 부하에도 굉장히 민감한 서비스 경우에는 별도의 스탠바이 용도의 슬레이브 서버를 두고 그곳에서 백업을 하기 마련입니다.

이런 상황 – 마스터에서는 백업과 같은 무거운 디스크 작업이 일어나지 않는 상황에서 알 수 없는 이유로 새벽 4시 혹은 4시 22분에 디스크가 유틸이 튀는 경우가 있습니다. 그리고 가벼운 쿼리일지라도 대거 슬로우 쿼리로 잡히기도 합니다.

범인은 의외로 리눅스 설치 시 기본적으로 등록되는 두 가지 크론잡에 있는데요, 얼마 전 이와 비슷한 사례를 경험하게 되어 공유 드립니다. (단, 고수님들은 출입금지!)

Default Cron Jobs

OS 설정에 따라 다르겠지만, CentOS를 설치하게 되면 다음과 같이 배치 작업이 등록이 되어 있습니다.

$ cat /etc/crontab
01 * * * * root run-parts /etc/cron.hourly >/dev/null 2>&1
02 4 * * * root run-parts /etc/cron.daily >/dev/null 2>&1
22 4 * * 0 root run-parts /etc/cron.weekly >/dev/null 2>&1
42 4 1 * * root run-parts /etc/cron.monthly >/dev/null 2>&1

이 상태에서 별다른 변경을 하지 않았다면 다음 두 개의 크론잡이 기본적으로 동작하게 되는데, 경우에 따라 두 개의 잡이 데이터베이스의 트랜잭션 로그 혹은 데이터 파일 쪽 디스크에 영향을 주어 순간 퍼포먼스가 크게 저하될 수 있습니다.

/etc/cron.daily/mlocate.cron
/etc/cron.daily/makewhatis.cron
/etc/cron.weekly/makewhatis.cron

간단하게 위 크론잡에 대해서 알아보도록 하겠습니다.

1) mlocate

파일 검색을 빠르게 검색하기 위해, 파일에 대한 색인 정보를 모아 데이터베이스를 만드는 역할을 하며, 매일 4시에 동작합니다.
mloate.cron에 포함된 내용은 아래와 같습니다.

$ cat /ec/cron.daily/mlocate.cron
 
#!/bin/sh
nodevs=$(< /proc/filesystems awk '$1 == "nodev" { print $2 }') 
renice +19 -p $$ >/dev/null 2>&1
/usr/bin/updatedb -f "$nodevs"

또한 이와 관련된 설정은 /etc/updatedb.conf 에 위치합니다.

$ cat /etc/updatedb.conf
PRUNEFS = "auto afs gfs gfs2 iso9660 sfs udf"
PRUNEPATHS = "/afs /media /net /sfs /tmp /udev /var/spool/cups /var/spool/squid /var/tmp"
  • PRUNEFS
    updatedb 가 스캔하지 않을 파일 시스템 리스트
  • PRUNEPATHS
    updatedb 가 스캔하지 않을 디렉토리 패스 리스트

매일 새벽 4시에 도는 작업으로 renice로 우선순위를 낮춰놓아도 디스크 자원을 아래와 같이 크게 잡아먹기도 합니다.

CPU     %user     %nice   %system   %iowait    %steal     %idle
all      0.00      0.51      3.03     46.46      0.00     50.00
all      0.00      1.01      4.55     44.44      0.00     50.00
all      1.00      0.50      2.00     47.50      0.00     49.00
all      0.51      0.00      2.03     47.72      0.00     49.75
all      0.50      0.50      3.50     46.50      0.00     49.00

만약 새벽 4시정도에 디스크 유틸이 이유없이 튀고 있다면, 이와 관련하여 살펴보시기 바랍니다.

2) makewhatis

처음 크론잡을 살펴보았을 때 별 것 아니라고 생각했었지만, 매주 일요일 새벽 4시 22분에 알 수 없는 디스크 유틸을 유발하던 장본인이었습니다.

간단하게 설명하자면, man에 관련된 내용을 신규 생성 또는 업데이트하며, 월~토요일은 증분으로 일요일은 전체를 풀로 새로 작성을 합니다.

## 일단위 크론잡
$ cat /etc/cron.daily/makewhatis.cron
.. 중략 ..
makewhatis -u -w
 
## 주단위 크론잡
$ cat /etc/cron.weekly/makewhatis.cron
.. 중략 ..
makewhatis -w

일 단위에서는 -u 옵션을 주어서, 기존 데이터베이스에 단순히 업데이트를 하지만, 주 단위 작업에서는 완전히 새로 작성합니다. 평일에는 크게 문제가 없다가 매주 일요일 새벽 4시 22분 정도에 시스템이 알 수 없이 튀는 현상을 보인다면, makewhatis 작업을 의심해보시기 바랍니다. ^^
다음은 간단하게 테스트 장비에서 돌렸을 때의 시스템 상황입니다.(다를 수 있으니, 참고만 하세요. ^^)

CPU     %user     %nice   %system   %iowait    %steal     %idle
all     31.00      0.00     17.50      4.50      0.00     47.00
all     30.46      0.00     14.72      7.61      0.00     47.21
all     31.31      0.00     13.64      9.09      0.00     45.96
all     31.31      0.00     15.15      6.06      0.00     47.47
all     35.18      0.00     16.08      1.51      0.00     47.24

Conclusion

운영 정책 및 트래픽에 따라 다르겠지만, 적어도 실DB 장비에서 굳이 디스크 자원을 소비하면서까지 이러한 인덱싱 혹은 매뉴얼을 작성할 필요는 없을 것이라 생각합니다. (물론 이것은 지극히 개인적인 소견이기는 하지만..^^)

만약 새벽 4시 이후로 알 수 없는 이유로 디스크 유틸이 튀고, 이로 인하여 슬로우 로그가 대거 발생하고 있다면 리눅스 새벽 크론작업을 의심해보시기 바랍니다.

(무언가 거창하게 시작했는데.. 성급하게 끝내버린 듯한 이 기분은 무엇일까요? ^^;;)

InnoDB의 Adaptive Hash Index로 쿼리 성능에 날개를 달아보자!!

Overview

MySQL과 같은 RDBMS에서 대표적으로 가장 많이 사용되는 자료 구조는 B-Tree입니다. 데이터 사이즈가 아무리 커져도 특정 데이터 접근에 소요되는 비용이 크게 증가되지 않기 때문에 어느정도 예상할 수 있는 퍼포먼스를 제공할 수 있기 때문이죠. 그치만 상황에 따라서, B-Tree 사용에 따른 잠금 현상으로 최대의 퍼포먼스를 발휘하지 못하는 경우도 있습니다.

이에 대한 해결책으로 InnoDB에는 Adaptive Hash Index 기능이 있는데, 어떤 상황에서 효과가 있고 사용 시 반드시 주의를 해야할 부분에 대해서 정리해보겠습니다.

InnoDB B-Tree Index?

소개하기에 앞서서 먼저 InnoDB에서 B-Tree가 어떠한 방식으로 활용되는 지 알아볼까요?

InnoDB에서는 데이터들은 Primary Key순으로 정렬이 되어서 관리가 되는데.. 이것을 곧 PK로 클러스터링 되어 있다라고 합니다. 데이터 노드 자체가  PK 순으로 정렬이 되어있다는 말인데, 이는 곧 특정 데이터에 접근하기 위해서는 PK가 반드시 필요하다는 말이지요.

그리고 Secondrary Key는 [인덱스키+PK]를 조합으로 정렬이 되어 있습니다. 즉, 특정 데이터를 찾기 위해서는 Secondrary Key에서 PK를 찾아내고, 그 PK를 통해 데이터 트리로 접근하여 원하는 데이터로 최종 접근을 하는 것이죠.

InnoDB B-Tree Index

트리가 가지는 가장 큰 장점은, 데이터 접근 퍼포먼스가 데이터 증가량에 따라서도 결코 선형적으로 증가하지 않다는 점에 있습니다.  다들 아시겠지만, B-Tree에서 특정 데이터 접근에 소요되는 비용은 O(logN)인데, 이는 일정 데이터 건 수에서는 거의 선형으로 비용이 유지됩니다. (참고로, PK접근 시에는O(logN), Secondrary Key ㅈ버근 시에는 2 *O(logN) 이겠죠?)

그런데, B-Tree를 통하여 굉장히 빈도있게 데이터로 접근한다면, 어떻게 될까요? DB 자체적으로는 꽤 좋은 쿼리 처리량을 보일지는 몰라도, 특정 데이터 노드에 접근하기 위해서 매번 트리의 경로를 쫓아가야하기 때문에, “공유 자원에 대한 잠금”이 발생할 수 밖에 없습니다. 즉, Mutex Lock이 과도하게 잡힐 수 있는데, 이 경우 비록 데이터 셋이 메모리보다 적음에도 불구하고 DB 효율이 굉장히 떨어지게 됩니다.

InnoDB Adaptive Hash Index?

앞선 상황에서 좋은 성능을 보이기 위해서, InnoDB에서는 내부적으로 Adaptive Hash Index 기능을 제공합니다. “Adpative”라는 말에서 느껴지듯이, 이 특별한 자료구조는 명쾌하게 동작하지는 않고, “자주” 사용되는 데이터 값을 InnoDB 내부적으로 판단하여 상황에 맞게 해시를 생성” 합니다.

InnoDB-Adaptive-Hash-Index

위 그림에서 자주 사용되는 데이터들이 1,5,13,40이라고 가정할 때 위와 같이 내부적으로 판단하여 트리를 통하지 않고 “직접 원하는 데이터로 접근할 수 있는 해시 인덱스”를 통해 직접 데이터에 접근합니다.

참고로, Adative Hash Index에 할당되는 메모리는 전체 Innodb_Buffer_Pool_Size의 1/64만큼으로 초기화됩니다. 단, 최소 메모리 할당은 저렇게 할당되나, 최대 사용되는 메모리 양은 알 수는 없습니다. (경우에 따라 다르지만, Adaptive Hash Index가 사용하는 인덱스 사이즈를 반드시 모니터링해야 합니다.)

이 기능은 MySQL5.5버전(InnoDB Plugin 1.0.3)부터는 이 기능을 동적으로 On/Off할 수 있습니다. 아래와 같이.. ^^;;

## 켜다
mariadb> set global innodb_adaptive_hash_index = 1;
 
## 끄다
mariadb> set global innodb_adaptive_hash_index = 0;

관련 통계 정보는 아래와 같이 확인..ㅋ

mariadb> 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 |
+----------------------------------------+------------+

자주 사용되는 데이터는 해시를 통해서 직접 접근할 수 있기에, Mutex Lock으로 인한 지연은 확연하게 줄어듭니다. 게다가 B-Tree의 데이터 접근 비용(O(LogN))에 비해, 해시 데이터 접근 비용인 O(1)으로 굉장히 빠른 속도로 데이터 처리할 수 있습니다.

단, “자주” 사용되는 자원만을 해시로 생성하기 때문에, 단 건 SELECT로 인하여 반드시 해당 자원을 향한 직접적인 해시 값이 만들어지지 않습니다.

InnoDB는 Primary Key를 통한 데이터 접근을 제일 선호하기는 하지만, 만약 PK접근일지라도 정말 빈도있게 사용되는 데이터라면 이 역시 Hash Index를 생성합니다. (처음에는 단건 PK 접근에는 절대로 Hash Index를 만들지 않을 것이라 생각했지만, 곧 생각을 고쳐먹었습니다. ㅋㅋ)

Adaptive Hash Index Power!!

흠.. 말만 하지말고.. 눈에 보이는 효과를 보도록 할까요? 글빨이 안되니.. 비주얼로 승부를!!ㅋㅋ

아래와 같이 간단한 테스트 케이스(1300만 건 데이터)를 만들어서, IN 조건으로 PK를 통하여 데이터를 추출하는 테스트를 해보도록 해요. IN절에는 약 30개 정도의 파라메터를 넣고, 300개의 쓰레드에서 5ms 슬립을 줘가며 무작위로 트래픽을 보내봅니다.ㅋㅋ

## 테이블 스키마
create table ahi_test(
  i int unsigned not null primary key auto_increment,
  j int unsigned not null,
  v text,
  key ix_j(j)
);
 
## SELECT 쿼리
select left(v, 1) from ahi_test 
where i in (x,x,x,x,x,...x,x,x,,);

 

Adaptive Hash Index를 사용하지 않는 오른쪽 결과에서는 CPU가 100%였으나, Adaptive Hash Index를 사용한 이후에는 60%정도로 사용률이 내려갔습니다.

InnoDB-Adaptive-Hash-Index-Effect1

게다가, CPU는 줄었으나, 쿼리 응답 시간이 줄었기에 처리량 또한 20,000 -> 37,000으로 늘어났습니다. 참으로 놀라운 결과지요?? ㅋㅋ

InnoDB-Adaptive-Hash-Index-Effect2

Adaptive Hash Index를 켠 이후에는 확실히 B-Tree를 통해서 데이터에 접근하는 빈도가 줄어든 것도 확인할 수 있고요~

InnoDB-Adaptive-Hash-Index-Effect3

이에 따라 내부적인 잠금 현상도 확연하게 줄어들었습니다.

InnoDB-Adaptive-Hash-Index-Effect4

다른 것은 손대지 않고, Adaptive Hash Index만 켰을 뿐인데….

결론은 따~봉!! 굉장히도 풍요로운 꽁짜 점심이죠? ㅋㅋ

Cautions

자.. 이제 InnoDB Adaptive Hash Index 효과를 보았으니, 영혼없이 아무 곳에서나 무조건 쓰도록 할까요? 노노~! InnoDB Adaptive Hash Index 가 필요할 정도라면, SELECT가 꽤 많이 발생하는 서비스라고 생각해볼 수 있는데요.. 이 상황에서 테이블 DROP시 문제가 발생할 수 있습니다.

아래 그림은 percona-online-schema-change 툴을 활용하여 스키마를 변경한 전/후 Adaptive Hash Index사이즈를 체크한 것인데, 전후로 두 배로 해시 사이즈가 증가하였습니다. 그런데 두 배가 문제가 되기보다는, 아래와 같은 상황이 수 개월 동안 유지될 수 있다는 점입니다.

InnoDB-Adaptive-Hash-Index-Effect5

평소 운영 시에는 전~혀 문제가 되지 않지만, 수 개월동안 전~혀 사용하고 있지 않던 테이블을 영혼없이 정리하다보면 치명적인 장애에 직면할 수 있는 것이죠. 바로 저처럼.. -_-;;

아래 수치는 앞선 테스트 테이블에 트래픽을 주는 상황에서 OLD 테이블을 DROP하였는데, 테이블 정리 도중에는 처리량이 급감(3.8만 -> 2.7만)한 상황을 보여줍니다. 아래 현상이 굉장히 많은 SELECT가 발생하던 서버에서 발생을 하였다면, 단위 쿼리 처리량이 줄어듬에 따라 어플리케이션 쪽에 문제가 발생할 수도 있겠죠. ㅠㅠ

| Com_select | 39041 |
| Com_select | 39189 |
| Com_select | 38774 |
| Com_select | 38953 |
| Com_select | 39527 |
| Com_select | 37906 |
| Com_select | 39316 |
| Com_select | 37541 |
| Com_select | 37972 |
| Com_select | 32484 | <=== DROP OLD TABLE START
| Com_select | 27514 |
| Com_select | 27602 |
| Com_select | 27692 |
| Com_select | 27918 |
| Com_select | 27818 |
| Com_select | 28266 |
| Com_select | 28383 |
| Com_select | 28350 |
| Com_select | 37047 | <=== DROP OLD TABLE END
| Com_select | 39572 |
| Com_select | 38868 |
| Com_select | 39315 |
| Com_select | 38738 |
| Com_select | 39548 |
| Com_select | 39413 |
| Com_select | 38978 |

설혹, 데이터가 2G일지라도, 또한 파일 시스템이 xfs일지라도.. 이것은 디스크적인 요소라기 보다는 Memory 내부적인 잠금 이슈이기 때문에.. 어찌 해결해볼 방법은 없습니다.

InnoDB 내부적으로는 테이블 DROP시 Sleep없이 죽어라고 Hash Index에서 관련 노드를 모두 삭제한 후 테이블이 제거합니다. 그런데 이것이 단일 Mutex로 관리되기 때문에 기존 SELECT 성능에도 지대한 영향을 끼치는 것이죠. 그나마 최대한 이러한 현상을 회피할 수 있는 방법은 innodb_adaptive_hash_index_partitions을 수십개(기본값은 1)로 늘려놓고, 경합을 최대한 줄이는 방법만이 유일할 듯하네요. ^^ (테이블 드랍은 트래픽이 제일 없을 때로!!)

아.. 혹은 테이블 드랍을 수행할 때는 최대한 트래픽이 없는 새벽에, Adaptive Hash Index를 순간 OFF/ON을 하여 메모리를 해제하고, 테이블을 DROP하는 방법도 될 수 있겠네요. ^^ 선택은 서비스 상황에 맞게..

InnoDB Adaptive Hash Index가 사용하는 메모리 사이즈도 지속적으로 모니터링을 해야할 요소라는 것도 잊지 말아야할 것이고요. ^^

Conclusion

InnoDB Adaptive Hash Index는 B-Tree의 한계를 보완할 수 있는 굉장히 좋은 기능임에는 틀림 없습니다. 특히나 Mutex와 같은 내부적인 잠금으로 인한 퍼포먼스 저하 상황에서는 좋은 튜닝요소가 될 수 있습니다.

그러나, “자주” 사용되는 데이터를 옵티마이저가 판단하여 해시 키로 만들기 때문에 제어가 어려우며, 테이블 Drop 시 영향을 줄 수 있습니다. Hash Index 구조가 단일 Mutex로 관리되기 때문에, 수개월간 테이블이 사용되지 않던 상황에서도 문제가 발생할 수 있는 것입니다.

굉장한 SELECT를 Adaptive Hash Index로 멋지게 해결하고 있다면, 이에 따른 Side Effect도 반드시 인지하고 잠재적인 장애에 대해서 미리 대비하시기 바래요. ^^