다음과 같은 쿼리문이 있다.
SELECT 1,2,3,4 FROM `TABLE` WHERE match(`A`,`B`) against('+"테스트"' IN BOOLEAN MODE)멀쩡한 쿼리다.
그런데 이 쿼리에서 숫자가 들어가게 되면 다음과 같은 모양이 되는데
SELECT 1,2,3,4 FROM `TABLE` WHERE match(`A`,`B`) against('+"2000테스트"' IN BOOLEAN MODE)이렇게 숫자가 들어가게 되면 CPU 100%를 달성하는 쾌거를 이루게 된다. (정확히는 엄청나게 긴 fulltext initialization 을 수행하게 되는데, 이 기간을 기다리지 못한 유저의 다수 쿼리 요청에 의해 서버가 터져나가게 된다)
결국 processlist를 보고 kill 해주고 있는식으로 처리하고 있는데, 언제까지나 이렇게 할수도 없고..
Elasticsearch 까지 고려하다가 역시 근본적인 원인을 찾지 않으면 답이 안나올거라 생각되어서 찾아보게 되었다.
우선
테이블은 다음과 같이 생겼다
CREATE TABLE `_test` ( .... FULLTEXT KEY `idx_` (`A`,`B`) /*!50100 WITH PARSER `ngram` */ ) ENGINE=InnoDB AUTO_INCREMENT=9034081 DEFAULT CHARSET=utf8
다른 컬럼은 별 관심도 없을테니 인덱스는 이렇게..
alter table로 fulltext index 라고 명명해줘도 create table을 찍어보면 fulltext key라고 나온다.
또한 ngram token 은 bigram(2) 이며 혹시 모를 innodb ft_min_length 도 2로 설정된 상태이다.
까고보면 절대 문제가 없는데 특이 사항이 있다면
수시로 insert가 일어난다.(update는 없음)
매일 12시에 꽤 규모가 있는 delete가 일어난다.(배치로 필요없는 row를 다른 테이블로 옮기는 작업을 수행함)
이정도인데 이정도는 누구나 할 것이라고 생각한다.
어쨌든 내가 이 문제에 대해 내린 결론은 mysql 이 delete된 row에 대한 index를 그대로 보존하는게 아닌가.. 이다.
(왜 integer에 대해서만 이런 이슈가 있는진 모르겠다만..)
그래서 답을 찾고 해결한것은
그냥 재 인덱싱한것이다 --
DROP INDEX `idx_` ON `T`; ALTER TABLE T ADD FULLTEXT INDEX idx_(A,B) WITH PARSER ngram;
이후엔 별로 문제가 발생하지 않았다. 생각할수록 어이가 없지만 결국 이 쿼리도 배치에 넣어줘야했다. 허참..