Category Archives: MySQL

MySQL 블로그

[MySQL] 슬레이브 하나 더 추가했을 뿐인데.. :-)

Overview

MySQL의 꽃중의 꽃은 역시 비동기 방식의 데이터 복제라고 볼 수 있는데요. 지극히 개인적인 생각이기는 하지만, 슬레이브 노드를 데이터 일관성이 반드시 필요한 상황에서의 READ 스케일아웃을 “제외”하고는, 지금의 MySQL을 있게한 결정적인 한방이라고 봅니다. 물론 소셜 서비스에 따른 기존 스케일업으로는 도저히 감당할 수 없는 데이터 사이즈와 비용 요소도 직접적인 영향을 주었겠지만요.

뜬구름잡는 얘기는 여기까지로 마무리하고.. 슬레이브 노드를 READ 분산 혹은 배치 서버 용도를 제외하고는 스탠바이 용도로 한대를 더 추가하는 경우는 극히 드뭅니다. 뭐, 나랏님이 시키는대로.. 반드시 수십 km 떨어진 IDC에 DR 서버를 구축해야한다는 “법적인 제약”인 경우를 제외하고는요.

이유야 어쨌든, 이전 운영하는 서버 클러스터 노드와는 다르게 3대의 DB 서버를 세트 하나로 구성하게 되었는데요. 나름 고민에 고민을 거쳐, 최종적으로 현재 구조로 마무리하면서 얻게된 잇점과 효율에 대해서 얘기를 풀어보도록 하겠습니다.

2-Nodes vs 3-Nodes

서버를 두대로 구성하는 경우는 일반적으로 아래와 같이 마스터/슬레이브 구조로 데이터를 복제하는 경우입니다. 평시에는 Master에서 모든 서비스를 하고 장애 시 슬레이브로 커넥션을 (자동이든/수동이든) 넘겨서 서비스 다운타임을 최소화하기 위함입니다. 데이터가 논리적으로 복제가 되기 때문에, 슬레이브를 활용하여 꽤 무거운 배치 혹은 데이터 처리를 서비스 영향없이 수행 가능합니다.

MYSQL-2-NODES-REPL

비슷하게 비슷한 목적을 가졌지만, 앞선 경우에는 데이터를 논리적으로 복제를 해서 원격에 데이터를 위치시키는 것과 다르게, 스토리지를 활용하여 서버 장애에 대응한 경우도 있습니다. 이 경우 아쉽게도 서버 장애에는 대응이 가능하나, 스토리지 장애에는 “스토리지 이중화”외에는 방안이 없으며, 스탠바이 노드는 디비 데몬이 내려가 있기 때문에, 정말 1도 활용할 수 없다는 아쉬움이 남습니다. 게다가 “물리적으로 저장공간을 공유”하는 구조이기 때문에, NIC를 통해 논리적으로 데이터를 복사하는 앞선 경우와는 다르게 “스토리지 물리적인 장소”에 영향을 받을 수밖에 없습니다.

MYSQL-2-NODES-STORAGE

일반적으로 2대의 노드로는 위 두 가지 경우를 크게 벗어나는 경우는 없습니다. 두가지 경우 모두 둘 중 하나만 서비스에 투입이 되기 때문에, 한대는 거의 대부분 서비스에 직접적인 트래픽을 받지 않습니다. (뭐 첫번째 경우 배치 쿼리 정도는 날려볼만 하겠지만요.)

그러나 타의든 자의든, 일단 세 대를 아래와 같은 형태로 운영을 해야하는 상황이 도래합니다. 아래와 같이 3대 노드로 데이터를 구성하는 경우, MySQL은 세트 하나 늘어날 시에 세대씩 증가하기 때문에관리 포인트는 2-노드인 경우보다 훨씬 많아집니다. 게다가 MySQL의 꽃은 샤딩.. 무한 확장을 꿈꾸며 데이터 라우팅을 지대로 하는 것이 묘미니까요. ㅎㅎ

MYSQL-3-NODES-REPL

또한 노드가 늘어남에 따라 “복제 시 전송되는 로그양”도 적은 비용이 아닙니다. (금전적인 것 이외에도 말입니다.)

사용하지 않을 장비만 늘어나고, 이에 따라 관리 포인트만 증가하는 현실 속에서, 이 글을 쓰기 시작하였고, 어찌됐건 기존 1M-1S 구조에서와는 다른 “얻은점”에 대해서 얘기를 풀어보도록 하겠습니다. (공감하신다면, 야밤에 박수 세번 짝짝짝)

1. Semi-Sync Replication

데이터 자체가 굉장히 민감한 서비스(이를테면, 돈이 오가는 서비스)에서는, 절대적으로 데이터의 유실은 허용될 수 없습니다. 트랜잭션이 실패할지라도, 장애 시 데이터 유실은 상상조차 해서는 안되는 것이죠.

MySQL5.5 부터는 이런 현상을 최소화하기 위해, semi-sync replication 기술을 도입하였습니다. 이는 마스터와 슬레이브 간의 데이터 동기화를 100% 보장할 수는 없지만, 적어도 복제 로그(릴레이로그)에 대한 보장을 어느정도 하겠다는 것을 의미합니다.

일반적으로 5.5부터 언급되는 MySQL semi-sync는 “AFTER_COMMIT”모드로, 커밋 이후 사용자에게 응답을 주기 직전(엔진에는 이미 데이터 적용 완료)에 잠시 슬레이브 릴레이 로그를 기다리는 형태로 동작을 하였습니다.

mysql-semisync-5.5

MySQL 5.7.2 부터는 Lossless Replication이라는 멋진 이름 하에 “AFTER_SYNC”모드의 semi-sync 옵션이 추가가 되었는데, 간단하게 얘기하자면, 엔진에 커밋이 되기 직전에 슬레이브 릴레이 로그 저장을 잠시 기다려보겠다는 것을 의미합니다. (아래 빨간 글씨)

mysql-semisync-5.7

그런데, AFTER_SYNC든 AFTER_COMMIT이든 공통적으로 가지는 문제가 하나 있는데.. “트랜잭션 응답 시간이 슬레이브의 빠른 응답에 의존적”이라는 것입니다. 만약, 슬레이브가 한 대인 상황에서 “슬레이브”가 장애가 나게 된다면, 서비스 트랜잭션이 일시적으로 블록킹 형태로 머물러있게 됩니다.

물론 rpl_semi_sync_master_timeout 파라메터를 조정해서, 슬레이브의 응답을 기다리는 최대 시간을 조정하여, 빠르게 Async모드로 돌아오도록 조정할 수 있지만.. 기본값이 10초인 상황을 인지 못한 채, 운영하다보면 영문도 모른채 서비스에 이상을 감지할 수 있습니다. (여기서, 분명히 구분해야할 것은, 서비스 투입되지 않은 “슬레이브” 이상으로 발생하는 상황이라는 점입니다.)

그런데, 슬레이브 노드 하나를 더 추가함으로써 “특정 슬레이브 노드의 지연”이 마스터의 트랜잭션 지연으로 이어지지 않습니다. 둘 중 한군데에서 응답이 오면 마스터는 더이상 기다리지 않고 트랜잭션을 완료 처리하기 때문이죠. 물론 몇 군데의 응답을 기다릴지도 옵션으로 지정할 수 있지만, 굳이 그럴 이유는 없어보입니다. (이유는 MHA에서..)

semi-sync-3-node

슬레이브 하나를 더 추가했을 뿐인데.. semi-sync 를 더욱 안정적으로 멋지게 사용할 수 있는 발판이 되었습니다. 눈누난나~

2. Backup & Restore

백업/복구.. 데이터 솔루션을 운용하는 입장에서는 반드시 초반부터 계획을 잡아야하는 상황인데요. 앞선 블로그 “세상만사 귀찮은 MySQL DBA를 위한 자동 복구 시나리오” 에서 어떻게 자동화를 하는지에 대해서 공유한 적이 있었습니다. 슬레이브를 한대 더 추가함으로써 복구 시 서비스 품질 관련하여 곁다리로 하나 더 얻게된 점이 하나 더 있는데요.

이전에 슬레이브 한대로만 서비스를 운영한다면, 마스터 장비 페일오버 이후 데이터 복구 수행 시, 그 날 새벽 백업 데이터를 활용하여 슬레이브를 복구해야만 합니다. 물론, (서비스에 투입된)마스터에서 직접 받아와도 되겠지만, 수백기가 데이터를 전송하는 동안 네트워크 트래픽을 비롯해서 디스크 I/O를 고려한다면, 서비스 안정성 측면에서 좋은 선택을 아니라고 봅니다.

새벽 데이터로 슬레이브 구성을 한다는 말은 곧, 새벽부터 쌓이기 시작한 “바이너리로그”를 모두 끌어와서 현재 시점까지 리플레이하여 적용하겠다는 말인데요. 굉장히 바쁜 서버 경우에는 하루 70~80G이상 바이너리 로그가 쌓이기도 하는데.. 가져오는 것도 부담일 뿐만 아니라, 적용하는 시간 조차도 굉장히 부담이 됩니다. (23시간 전의 데이터로 복구를 한다고 생각을 해보세요. 바쁜서버는 10시간 넘게도 걸릴 수도 있습니다.)

자. 여기서 슬레이브를 하나 더 추가를 해보았다면, 어떤 것을 얻을 수 있을까요?  슬레이브 구성이 필요한 상황이 오더라도, 아래와 같이 슬레이브로부터 데이터를 받아와서 바로 원래 구조로 쉽게 돌아갈 수 있게 됩니다. 서비스 영향도는 거의 제로에 가깝게.. 굳굳굳

Restore_From_Slave_Data

Restore_From_Slave_Data

3. Collaboration with MHA

개인적인 관점에서.. MHA야말로 신의 한 수라고 생각합니다.ㅋㅋ

MHA? 개념이 아직 생소하신 분도 있겠죠. 간단하게 설명하자면, MHA는 Master의 헬스 체크를 주기적(3초단위 쿼리를 날리면서)으로 수행하면서, 설정된 카운트 이상 실패 시 스탠바이 슬레이브로 마스터의 롤을 체인지시켜주는 “요시노리”가 오픈소스로 공개한 MySQL HA 솔루션입니다.

재미있는 것은 MHA가 마스터 장애를 감지하고, 슬레이브로 마스터의 롤을 넘기는 과정에서 살아있는 슬레이브들의 리플리케이션 또한 신규 마스터 기준으로 재구성을 해주는데요. 이 경우 기존 마스터로부터 전달받은 릴레이 로그를  비교 및 버전 동기화를 함으로써 최종적으로 정상적인 리플리케이션 형태로 만들어줍니다.

semi-sync-repl

“릴레이 로그”를 비교한다라는 것은.. 앞서 1번에서 언급했던 “Semi-Sync Replication”을 생각해볼 수 있는데요. Semi-Sync로 구성된 리플리케이션 클러스터 내부에서는.. “커밋된 트랜잭션”의 릴레이 로그는 “슬레이브 어디엔가에는 반드시 존재한다”는 가정하에 동작합니다.

AFTER_SYNC 모드로 semi-sync를 운영하고 있다면, 커밋된 트랜잭션의 릴레이로그는 “슬레이브 중 어딘가”에는 반드시 존재할 것이고, 이 경우 마스터 장애 시 “어딘가에 존재하는 릴레이로그”를 활용한 복구로 절대적으로 데이터 유실 가능성은 제로에 가깝게 될 것이다라고 돌려 말을 해봐도 되겠습니다.

4. High Availability

2번 백업에서도 얘기했던 것과 일맥상통하는 얘기일 수 있겠습니다.

슬레이브가 한대인 경우에 마스터 장애가 발생했을 시에는 늘 걱정되는 것이 바로 2차 장애였습니다. 장애 후 복구까지는 어찌됐건 싱글 DB로 데이터를 운영하고 있는 것이고.. 이 한대에 이슈가 발생하면 “메가톤급 서비스 장애”와 “데이터 유실”이라는 부담감을 가지고 늘 지내왔죠. 그렇기때문에, 새벽 몇시가 되건 “유휴장비”를 시스템부서로부터 제공받아 빠르게 슬레이브를 붙이는 것이 장애 대응 1순위 작업이었죠. 서비스는 소중하니까요. :-)

그런데, 슬레이브를 하나 더 붙였을 뿐인데.. 2차 장애에 대응해서 우리에게는 하나 더 선택지가 생겼습니다.

  1. MHA를 2노드로 설정해서 다시 모니터링한다.
  2. 모니터링을 하지 않되, 문제 시 바로 남은 슬레이브로 롤체인지 한다.

이 두가지 모두, 정책적으로 운영을 어떻게 할 지에 대한 선택일 뿐, 2차 장애에 대한 대응은 동일합니다. 다른 점이라면, 자동으로 넘길지 혹은 수동으로 확인하고 넘길지 두 가지 입니다. 2차 장애에 대응할 수 있는 새로운 선택지가 생겼다는 사실은 운영 부담을 압도적으로 줄일 수 있는 계기가 되었습니다.

5. ETC

이것말고도 생각해보면 많습니다. 정말로 사용하지 않는 슬레이브에 개발자 접근 권한을 줘서 “무슨 짓”을 하든 적어도 서비스 간접적인 영향마저도 최소화 한다거나.. 엄청난 쿼리를 한번 날려본다거나..

Conclusion

슬레이브를 하나 더 추가했을 뿐인데.. 생각보다 많은 것을 얻었습니다.

  1. MHA+semi-sync로 데이터 신뢰성과 안정성
  2. 2차 장애에 대한 가용성 정책
  3. 백업/복구 효율성

물론 한대 더 추가함으로써 운영 리소스가 더 들어갈 수 있습니다. 그러나. 이런 것들은 자동화함으로써.. “다수 서버 운영 시 허들이 될만한 부분은 모두 “잔머리 굴려서” 자동화” 하면 되겠죠. 실제 제가 운영하고 있는 서비스에서는 “정말 크리티컬한 명령”을 제외하고는 자동화하는 정책을 굉장히 지향하고 있습니다.

서비스 특성에 따라 이것이 정답은 아니겠지만, 제가 운영하는 “OLTP 위주”의 “데이터 신뢰”가 대단히 중요한 서비스에서는 꽤나 좋은 데이터 프로세싱 “철학”이자 새로운 “경험”이라 생각해봅니다.

간만에 긴 주저리 블로깅이었습니다. 짝짝짝~! 좋은 주제로 다시 만나용~

MySQL_5.7의 n-gram? 버그앤런!

Overview

바로 얼마전 포스팅에서 n-gram에 대한 간단한 소개를 했었는데.. 아무래도 5.7에 처음으로 소개된 기능인만큼 현재 이슈 사항에 대해서 공유를 해볼 필요가 있어보입니다. (이전 포스팅: http://gywn.net/2017/04/mysql_57-ngram-ft-se/)

제가 겪은 상황과 우회할 수 있는 방안.. 그리고 현재 진행 상황에 대한 내용이예요. ^^

1. Performance Problem

일단 InnoDB의 n-gram 인덱싱은 두 글자로만 나뉘어서 토큰으로 만들어집니다. 그리고 이 토큰들은 도큐멘트 아이디를 각각 가짐으로써, 빠르게 “두 글자”가 포함된 문서를 바로 찾아낼 수 있는 것이지요.

예를들어, 실제 n-gram 인덱싱이 관리되는 모양새를 바라보면.. 아래처럼 인덱싱 모습을 볼 수 있답니다요. 공백을 제외한 모든 데이터를 두 글자(ngram_token_size가 2인 경우)로 나눠서 구조화하는 것이죠.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+------+--------------+-------------+-----------+--------+----------+ 
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | 
+------+--------------+-------------+-----------+--------+----------+ 
|   ef |            2 |          11 |        10 |     10 |        4 | 
|   ef |            2 |          11 |        10 |     11 |        4 | 
|   fg |            2 |          11 |        10 |      2 |        5 | 
|   fg |            2 |          11 |        10 |      3 |        5 | 
|   fg |            2 |          11 |        10 |      4 |        5 | 
+------+--------------+-------------+-----------+--------+----------+

그리고 검색어는 두 글자 씩 나눠지면서 아래와 같이 쿼리가 변환됩니다. (boolean모드에서입니다.)

## 원래 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('나는나야' in boolean mode);

## 변환된 쿼리
mysql> SELECT * FROM test WHERE match(j) against ('"나는 는나 나야"' in boolean mode);

두 글자 씩 내부적으로 “순서까지 일치”하도록 2글자 단어 검색을 3회 한다는 것을 의미하지요. 이것은 곧.. 검색어가 길어질수록.. 특히나 검색어 내부에 분포도 최악인 단어가 포함된다면.. 흠.. 쿼리 성능에도 영향을 줄 수 있습니다. 하단 간단한 테스트를 보시지요. :-)

“은행나무” 라는 데이터 30만 건을 생성한 후 “는나무”와 “나무는” 두 검색(0건인 결과)을 해본 결과입니다. “은행” 단어의 위치에 따라 상이한 응답 시간을 보여주지요. 아직 옵티마이저의 한계로 결론을..

## '"는나 나무"'
mysql> select * from test where match(j) against ('는나무' in boolean mode) ;
Empty set (0.00 sec)

## '"나무 무는"'
mysql> select * from test where match(j) against ('나무는' in boolean mode) ;
Empty set (0.21 sec)

검색어 위치에 따라 쿼리 성능이 달라질 수 있습니다.

일반적으로는 이렇게 특정 단어에 쏠리는 경우는 희박하기에.. S급 이슈는 아닐 것으로 보여지지만.. 적어도 사용자 패턴에 따른 성능 차가 발생할 수 있는 만큼, 인지는 하고 있어야겠습니다.

2. Wrong Result Problem

MySQL 기본 Collation이 대소문자를 구분하지 않기 때문에.. 기본 설정으로는 이슈가 없습니다. 그러나, 만약 대소문자 구분하도록 서버 설정을 구성하였다면, 의도치않게 원하는 결과가 나타나지 않는 이슈가 있습니다.
(참고 : https://bugs.mysql.com/bug.php?id=78048)

n-gram 인덱싱은 대소문자 구분을 하지 않습니다.

이것은 단순히 ngram의 이슈라기 보다는 InnoDB Fulltext Index 자체적인 문제로 보이는데.. 꽤 오래전부터 이 부분에 대한 개선은 진행 중인것으로 보이네요. 간단하게 우회할 수 있는 방안으로는 추가로 “LIKE” 검색 조건을 주는 것입니다.

## 변경 전 ##
SELECT * FROM test
WHERE MATCH (j) against ('aA' IN boolean MODE);

## 변경 후 ##
SELECT * FROM test
WHERE MATCH (j) against ('aA' IN boolean MODE)
  AND j LIKE '%aA%';

3. Symbol Character Searching Problem

최근들어 Percona와 가장 활발하게 논쟁 중인 이슈입니다. 논쟁하면서 오라클 쪽으로도 하단 리포트가 올려서 상황을 지켜보고 있는 사항이기도 하죠. (참고 : https://bugs.mysql.com/bug.php?id=86164)

특수문자 검색이 되지 않습니다.

이슈가 되는 것은.. 실제  ngram 파싱에는 정상적으로 인덱싱이 잘 토큰화 되었는데.. 실제 검색에서는 특수문자가 포함된 경우 전혀 검색이 안되는 케이스입니다. 처음에는 이 이슈의 원인이, n-gram에서 특수 문자 자체를 공백과 같이 무시하기 때문에, 토크나이징이 안된 것이 아닐까 의심을 했었지만.. 실제 내부 인덱스 현황을 살펴봤을 때 토크나이징 자체까지는 이슈가 없음을 확인했습니다.

mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;                                                                                                                                                
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| !e   |            2 |          12 |         2 |      2 |        3 |
| !e   |            2 |          12 |         2 |     12 |        3 |
| #e   |            4 |           4 |         1 |      4 |        3 |
| $e   |            5 |           5 |         1 |      5 |        3 |
| %e   |            6 |           6 |         1 |      6 |        3 |
| &e   |            8 |           8 |         1 |      8 |        3 |
+------+--------------+-------------+-----------+--------+----------+

현재로써는 특수문자 관련된 내용을 정제해서 풀텍스트 인덱스로 만들든지.. 혹은 아래와 같이 풀텍스트 검색 시 패턴 검색 식으로 우선 데이터 스캔량을 줄이고, LIKE 검색을 추가하는 방안 밖에 없어 보입니다.(현재로써는..)

## 변경 전 ##
SELECT * FROM test
WHERE MATCH (j) against ('A&T' IN boolean MODE);

## 변경 후 ##
SELECT * FROM test
WHERE MATCH (j) against ('A*T' IN boolean MODE)
  AND j LIKE '%A&T%';

단, 위와 같이 우회하는 경우, A와 T가 포함되는 모든 단어들이 n-gram으로 필터링 되기 때문에 의도치 않게 스캔량이 많아질 리스크는 있습니다. 이런 상황을 인지하고 “특수문자 검색”을 허용할지.. 혹은 일단 특수문자 검색이 안되는 사항을 인지하고 다른 방안을 찾아볼지는 서비스 요구사항에 따라 다르겠습니다.

Mroonga에서 사용하는 Groonga 경우에는 이러한 특수문자 처리 여부도 파서 선택을 통해 수행할 수 있는데.. 아직 InnoDB의 n-gram에는 아쉬운 부분입니다.

Performance Test

무작위로 데이터를 밀어넣어 놓고, 간단하게 두 가지 상황에 대해서 트래픽을 날려본 결과를 간단하게 공표(?)해봅니다. 단, 현재 MySQL 5.7에서는 소팅없이 limit을 수행하는 경우 결과가 정상적으로 나오지 않거나, 최악의 경우에는 크래시 되는 심각한 버그가 존재합니다. (핫픽스로 우리는 이슈 해결을 하였으나, GA 적용까지는 조금 시기가 더 걸릴 듯 합니다요. 참고만. ^^ )

당연한 이야기겠지만, 소팅없이 limit으로 자르는 경우에는 전체 데이터를 살펴볼 필요가 없기에, 적절한 응답 시간을 보여줍니다. 만건 이상 데이터에서도 33ms 정도로 나쁘지 않은 속도이죠.

초당수행건수(평균수행마이크로초)
1 : 1~9건
10 : 10~99건
100 : 100~999건
1000 : 1000~9999건
10000 : 10000 이상

#############################
## SORTING
#############################
1365qps, 1:709(409),  10:496(747), 100:140(2958), 1000:17(19266), 10000:3(170724)
1402qps, 1:761(398),  10:476(713), 100:148(2858), 1000:16(18395), 10000:1(188640)

#############################
## REMOVE SORTING
#############################
2807qps, 1:1517(363), 10:966(465), 100:279(807), 1000:41(4153), 10000:4(33704)
2818qps, 1:1512(352), 10:938(473), 100:333(781), 1000:34(3774), 10000:1(32702)

Conclusion

Mroonga로 n-gram 풀텍스트 엔진을 써번 경험으로 무작정 시도해본, InnoDB n-gram 풀텍스트 엔진.. 아직까지는 대량의 데이터 처리에는 미흡한 모습을 보입니다. 옵티마이저의 쿼리 파싱부터 버그 이슈까지.. 차츰 좋아지겠지만.. 현재는 이러한 이슈로 정리해 보겠습니다.

  • 검색어 위치에 따라 쿼리 성능이 달라질 수 있습니다.
  • n-gram 인덱싱은 대소문자 구분을 하지 않습니다.
  • 특수문자 검색이 되지 않습니다.

 

그렇다고 해도 전문 검색을 기존의 Like ‘%검색%’ 방식과 같이 모든 데이터를 매번 풀스캔 하는 것보다는 훨씬 나아요.

만약.. 전문 검색 엔진을 도입하기에는 볼륨이 크지 않고, Like검색을 하기에는 풀스캔 부담이 있으신 분들은.. 그래도 도입을 고려해봐도 나쁘지는 않을 것 같네요. 단, GA버전으로 n-gram 버그가 픽스되기 전까지는 반드시! 소팅(order by)을 붙여서 사용하시기를 극히 추천합니다.

(하아.. 몇 달 사이 많은 것을 배웠네요.)

MySQL_5.7의 n-gram 전문 검색을 이상하지 않게 써보아요.

Overview

MySQL5.6부터는 InnoDB에서도 전문검색이 가능하기는 하였습니다만.. 아쉽게도 여전히 공백 기준으로 단어들이 파싱이 되는 “MeCab Full-Text Parser Plugin” 방식으로 동작합니다. 즉, 한국말처럼 공백만으로 단어를 파싱할 수 없는 언어의 경우에는 크게 매력적이지는 않습니다. InnoDB에서 전문검색 인덱싱이 가능하다는 것은 Transaction이 전제로 이루어지는 것이라고 볼 수 있기에.. 리플리케이션 및 시점 백업/복구 측면에서는 혁신으로 볼 수 있습니다.

반드시 Limit로 끊어서 가져오고자 한다면, “Order By”로 정렬을 하세요~ 이 관련해 버그가 있고 조만간 픽스될 예정이기는 합니다. (n-gram 처리 시  스토리지 엔진에서  limit이 영향을 미쳐 제대로된 결과 도출 혹은 최악의 경우 크래시까지 발생할 수 있어요.)

그런데 MySQL 5.7부터는 InnoDB에서도 n-gram 방식의 전문 검색 인덱스를 지원하면서, 한국어/중국어/일본어에서도 효율적인 전문 검색이 가능하게 되었습니다. 물론 mroonga와 같은 Third party 스토리지 엔진을 설치해서 사용을 할 수 있겠지만.. 백업/복구가 늘 이슈가 늘 따라다니고는 했습니다.

참고로, 전문 검색이란.. LIKE ‘%검색어%’ 과 동일한 역할을 한다고 보시면 되겠습니다. (조금 의미는 다르지만.. ㅋ)

N-GRAM?

자세한 내용은 하단 링크를 쭉 읽오보시면 되겠습니다만..

https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html

간단하게 저 메뉴얼 내용을 인용하자면, 컨텐츠의 인덱스를 아래와 같이 첫글자/두글자..(1그램,2그램,3그램..) 등등으로 나누어서 파싱하여 전문검색 인덱스를 만드는 것이라고 생각하면 되겠습니다.

n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'

그리고, 기본적으로 InnoDB에서 제공해주는 ngram의 최소 토큰 사이즈(ngram_token_size)는 2이고, 위에서 “n=2″ 부터 토큰을 만들게 됩니다. 당연한 이야기겠지만, n-수치가 낮을수록 토큰 수가 많아질 것이기에, 모든 검색어들이 3글자부터 시작된다면 이 수치를 3으로 상향 조정하는 것도 인덱싱 관리 및 사이즈 안정성에 도움이 되겠습니다.

Simple Test

mysql> CREATE TABLE `articles` (
    ->  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->  `body` text,
    ->  PRIMARY KEY (`id`),
    ->  FULLTEXT KEY `ftx` (`body`) WITH PARSER ngram
    ->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into articles (body) values ('east');
mysql> insert into articles (body) values ('east area');
mysql> insert into articles (body) values ('east job');
mysql> insert into articles (body) values ('eastnation');
mysql> insert into articles (body) values ('eastway, try try');
mysql> insert into articles (body) values ('try try');

“WITH PARSER ngram” 부분을 명시적으로 적지 않으면, 기존 전문검색 인덱스로 구성되니 유의하시기 바랍니다. 자, 이 상태에서 ‘st’가 들어있는 데이터를 검색해볼까요?

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('st' IN BOOLEAN MODE); 
+----+------------------+
| id | body             |
+----+------------------+
|  7 | east area        |
|  6 | east             |
|  8 | east job         |
|  9 | eastnation       |
| 10 | eastway, try try |
+----+------------------+

참고로, 위 정렬 순서는 단어 매칭 수가 많은 데이터 순서로 자동 정렬이 됩니다.

STOPWORD Problem

우연찮게 동료가 자신의 이름을 검색을 하다 발견했던 이슈인데.. 위 상태에서 “ea”를 포함하는 데이터를 검색해봅니다. 분명히 데이터가 존재함에도, 아래와 같이 한 건도 나오지 않는 점을 확인할 수 있습니다.

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('ea' IN BOOLEAN MODE); 
Empty set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('eas' IN BOOLEAN MODE); 
Empty set (0.01 sec)

아무래도 5.7에서 새롭게 들어온 n-gram방식이라서 그런지요. INNODB_FT_DEFAULT_STOPWORD에 의존적으로 n-gram 도 동작을 하게 됩니다. 기존 전문검색인덱스에서는 공백 기준으로 토큰화되기 때문에, 영문에서 자주 쓰이는 단어들은 인덱스로 분류되지 않도록 지정을 합니다. 이 단어들이 모여있는 테이블이 바로 information_schema 에 위치한 INNODB_FT_DEFAULT_STOPWORD 테이블이죠.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
..중략..
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

그런데 참 재미있는 것이.. 단어 파싱의 기준은 n-gram으로 하면서, 실제 룰 일부는 기존의 “MeCab” 기준으로 적용한다는 사실이죠. 위 예제에서는 위에 포함이 되어 있는 “a” 단어로 인하여 “east”문자는 “e”, “st” 이렇게 파싱이 되고, 결과적으로 “ea” 검색 결과에서 누락이 되는 상황이 되어버리고 만 것입니다.

INNODB_FT_DEFAULT_STOPWORD에 대한 조금더 상세한 얘기는 하단 매뉴얼을 참고하시면 되겠습니다.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-stopwords.html

Workaround

현재로서는 “information_schema” 하단에 위치한 이 테이블의 데이터를 조작하거나, 변경할 수 있는 방안은 없습니다.

오라클/Percona에서 어떻게 대응해줄지는 모르겠지만, 일단 당장 이 이슈가 해결될 것 같지는 않기에, 조금 트릭으로 풀어야겠습니다.  그래서 아래와 같이 데이터가 전혀 없는 ngram_stopwords 테이블을 만들고, 이 테이블을 INNODB_FT_DEFAULT_STOPWORD 대용으로 사용하겠다고 파라메터 지정하는 것인데요.

mysql> CREATE TABLE mysql.ngram_stopwords(value VARCHAR(18)) ENGINE = INNODB;
mysql> SET GLOBAL innodb_ft_server_stopword_table='mysql/ngram_stopwords';

기존에 이미 n-gram 전문검색인덱스가 존재한다면, 반드시 테이블 재구성을 하시기 바랍니다. (모든 데이터를 다시 파싱을 해야하기 때문이죠.)

mysql> alter table articles engine = innodb;

이제 아까처럼 다시 “ea”로 검색을하면, 아래와 같이 예~쁜 결과가 나오게 됩니다. ^____^

mysql> SELECT * FROM articles WHERE MATCH(body) AGAINST('ea' IN BOOLEAN MODE); 
+----+------------------+
| id | body             |
+----+------------------+
|  7 | east area        |
|  6 | east             |
|  8 | east job         |
|  9 | eastnation       |
| 10 | eastway, try try |
+----+------------------+

단, 이렇게 트릭을 사용한 이상.. 사이드이펙트가 무엇일지 생각을 해봐야겠지요? stopword와 관련된 테이블을 여러개를 가질 수 없다는 점입니다. 즉, MeCab 파서에도 영향을 줄 것이므로, 반드시 이 점 인지시기 바랍니다. ^^ (가장 좋은 해결책은 n-gram 파서 경우에는 INNODB_FT_DEFAULT_STOPWORD를 적용하지 않는 것일텐데.. ㅎㅎ)

이 부분은 조만간 Percona 에서는 파라메터로 제어할 수 있도록 해준대요~ 기다리세요. ㅋㅋ

Conclusion

역시. 신기능은 조금 기다려야했나.. 버그 이슈로 마음고생 많이 했습니다.

그렇지만.. 어찌어찌 해결이 되었고. 아마도 다음 릴리즈에서는 Percona에서는 픽스해서 GA로 오픈할 듯 하네요. 안정화를 더 해봐야하고, 성능 튜닝도 이루어져야할 것이겠지만.. 점차 좋은 기능들이 InnoDB로 들어오게 되면서, 리플 안정성 및 데이터 백업/복구 측면에서 대단한 발전이 있는 것은 사실입니다.

물론. Elastic Search와 같은 솔루션으로 해결해볼 수 있겠으나, 적절한 데이터 사이즈에서는 충분히 MySQL의 쿼리캐시(Result Cache)를 같이 조합해보면 대단한 효과를 낼 수 있다고 판단합니다.

데이터는 데이터일뿐.. ㅋㅋ 좋은밤 되세요.^^