늘모자란, 개발

오늘 mysqldump 한 sql 파일들을 보다가 기존 백업에 비해 용량이 1/3도 안되게 남아있는걸 봤다.
디비가 개박살이 났나. 왜이러나 하면서 근심과 걱정에 빠져있었는데 아무리봐도 멀쩡했다.

알고보니까, myisam 을 쓰는 한 테이블이 repair가 필요하다고 리턴했고, mysqldump는 에러가 발생하니 그대로 작업을 멈춘것이었다.
황당함 그 자체였는데 repair table을 해주고 나니 제대로 덤프가 되었다.

음..
mysqldump에는 -f옵션이 있어서 에러가 나던말던 진행하라는것도 있는데 고려해보던지 해야겠다.

*수정
이를 해결하기 위해서 mysqlcheck 를 넣기로 했다.

mysqlcheck -u -password= --auto-repair --check --all-databases

이런느낌이다.
https://stackoverflow.com/questions/4582832/repair-all-tables-in-one-go 늘모

slow query 로그를 모니터링하다가 괴이한걸 발견했다.

 # Time: 2017-06-07T21:02:08.862259Z
# User@Host:  @ localhost []  Id: 369414
# Query_time: 5.894014  Lock_time: 0.000000 Rows_sent: OOOO  Rows_examined: OOOO
use DB;
SET timestamp=1496869328;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `table`;

/*40001 SQL_NO_CACHE*/ 라는것도 처음보고, DB를 풀스캔하고 있는데 시간대를 보니 밤 9시라고 한다. 밤9시엔 이런 작업을 할리가 없는데..  그래서 이래저래 검색을 해보니 40001 은 mysql 버전 4.0.0 이상에서 진행된 쿼리라는거고, 뒤에는 말그대로 캐시없이 돌리겠다는 명령어라고 한다. 그리고 mysqldump에서 이렇게 질의한다고 하는데...

문제는 9시에 mysqldump를 진행하지 않는다. 혹시 해서 select now()를 해도 정상시간으로 되어있고, OS시간도 정상이다.
판타즘넷 DB서버는 새벽6시와 또 다른 특정시간대에 백업을 진행하는데, 친구와 얘기하다보니 로그 시간뒤에 Z가 붙어있는걸 확인했다. 그렇다. 이건 UTC +0 시간이었던것이다...

mysql 5.7.2 부터는 로그시간의 혼동을 피하기위해 따로 변수를 추가해 관리하는것이다. 이걸 확인하려면,

show variables;

를 통해 log_timestamps의 값을 확인하면 되는데 나의 경우

log_timestamps | UTC

로 설정되어 있다.
해당 변수의 값은 SYSTEM 혹은 UTC로만 설정할 수 있으며, 서버시간대로 로그를 확인하고 싶다면 SYSTEM으로 맞춰주면된다.
my.cnf 에 mysqld 밑에 다음과 같이 적어주자

 log_timestamps = SYSTEM

Ref:
https://serverfault.com/questions/759532/mysql-logs-different-time-than-now
https://stackoverflow.com/questions/8282788/what-does-select-n-sql-no-cache-from-mytable-mean-in-in-mysqls-slow

늘모

근래들어 엄청나게 이상한 이슈가 있었다.

다음과 같은 쿼리문이 있다.

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;

이후엔 별로 문제가 발생하지 않았다. 생각할수록 어이가 없지만 결국 이 쿼리도 배치에 넣어줘야했다. 허참..

늘모

디비 쿼리 최적화는 정말 끝나지 않는 싸움같다.
싴갤러스에서 사용하고 있는 쿼리도 여러번 바뀌었는데 다 기억은 못하고..

글 내용을 말하기에 앞서 테이블 구조부터 소개를 하자면,
아래와 같이 생겼다. 아주 심플한 테이블이라 할 수 있다.

CREATE TABLE `TABLE` (   
`index` int(11) NOT NULL AUTO_INCREMENT,   
`date` varchar(10) NOT NULL,   
`time` varchar(10) NOT NULL,   
`nick` varchar(30) NOT NULL,   
`data` varchar(200) NOT NULL,  
 PRIMARY KEY (`index`),   
 KEY `indx_date` (`date`),   
 KEY `indx_nick` (`nick`) ) 
 ENGINE=InnoDB AUTO_INCREMENT=4016741 DEFAULT CHARSET=utf8

사실 처음에 몇만건 몇십만건 됐을때는 아무렇게나 짜도 무리가 없었는데 백만단위로 들어가면서부터 검색 범위를 제한하게 됐다.
유쾌하지 않은건 사실이다. 성능 때문이라는 변명에 괜히 숨는 느낌이라고 해야하나...
그래서 항상 고민이 많다. 이 글을 작성하는 시점에는 멀쩡한데 저녁에 서비스가 몰리는 시간엔 어찌될지...

검색 쿼리는 다음 조건을 가진다.
1. 날짜 범위 지정
2. 닉네임은 매칭 / 내용은 와일드카드 검색
3. 최근 순서대로 정렬

이를 간단히 생각하면 다음과 같이 작성해볼 수 있을 것 같다.
(실제로 생활코딩에 질문을 올렸을때 어떤 누가 이런식으로 쿼리를 짜서 주었음)

select `index`, `date`,`time`, `nick`, `data` from TABLE 
where `date` BETWEEN '20150709' AND '20150716'  and `data` like '%검색어%' OR `nick` = '검색어' 
order by date desc

별 다른 특이사항이 보이지 않는 쿼리이다. 과연 성능은 어떨까?

Explain extended 를 사용하여 쿼리가 어떻게 수행되는지 확인을 해보자.

SIMPLE    TABLE    ALL    indx_date,indx_nick                387809    100.00    Using where; Using filesort

검색은 SIMPLE하고, possible한 key도 기재가 되지만, 실제로는 인덱스를 타지 않는다.
왜냐면 컬럼중에 인덱싱 범주에 포함되지 않는것들이 다수 있기때문이다.
또한 검색에 소요되는 Row의 수도 거의 테이블 전체를 seeking 해야하는 수준이며, 검색 조건중 반드시 배제되어야 된다는 filesort를 통해 order by를 수행한다.

따라서, 위 쿼리로 서비스를 했다간 그냥 db가 뻗어버릴것이다

그래서 두번째로 생각한것이 서브쿼리를 이용해보는것이었다.
내 착안은 이렇다.

1. 날짜 범위별로 검색범위를 제한한다.
2. 서브쿼리에서 배달된 (delivered) 쿼리중에서만 추가검색을 진행한다.

결론부터 말하자면 근접한 방법이긴한데 정답은 아니다.
쿼리를 보면서 계속하자

SELECT `index`, `date`,`time`, `nick`, `data` 
FROM     (         
                 select `index`, `date`,`time`, `nick`, `data` from TABLE
                 WHERE `date` BETWEEN '20150700' AND '20150716'
              ) as origindata
WHERE (    `data` like '%검색어%' OR `nick` = '검색어' )
ORDER BY `index` DESC

서브쿼리를 이용해서 인덱싱이 되어있는 Date 컬럼을 이용해 범위를 제한하고,
나온 결과에서만 검색을 수행하겠다는 계획이고, 실제로 위 쿼리로 서비스를 오래 했다.

이 쿼리의 explain은 다음과 같다.

1    PRIMARY    <derived2>    ALL                    177838    100.00    Using where; Using filesort
2    DERIVED    TABLE    ALL    indx_date                386966    45.96    Using where

읽는 순서는 역순으로..
인덱스를 타라고 일부러 서브쿼리로 나눠놨는데 위에서 언급한것과 마찬가지로 인덱싱이 되지 않는 컬럼을 select하기때문에 결국 전체 테이블을 seeking하게 된다.

결과적으로 놓고보면, 첫번째 쿼리와 다를게 없다....
filesort도 그대로 사용하고 있고, 인덱스도 여전히 이용하지 않고 있다.

그럼 어떻게 해야할까? 우선 인덱스를 타게 해야겠다고 생각했다.
그래서 쿼리를 좀 고쳐봤다. 요렇게

select b.`index`, `date`,`time`, `nick`, `data` from TABLE as b
left join (
   select `index` from TABLE where `date` BETWEEN '20150709' AND '20150716' ) a
on a.`index` = b.`index` 
where a.`index` = b.`index` and  `data` like '%검색어%' OR `nick` = '검색어'
ORDER BY b.`index` DESC

위 쿼리는 인덱싱이 되어있는 `index`컬럼과 `date`컬럼을 사용해보자고 생각을 했다.
그래서 서브쿼리 조인을 사용해서, 날짜에 해당하는 index들만 뽑아낸 후 바깥 테이블에서 해당 primary index에 해당하는 녀석들에서만 검색을 해보자고 시도를 해봤다. 결과는 실패였지만 과정은 괜찮았다.

1    PRIMARY    b    index    PRIMARY,indx_nick    PRIMARY    4        387047    100.00    Using where
1    PRIMARY    <derived2>    ref    <auto_key0>    <auto_key0>    4    lute.b.index    10    100.00    Using where; Using index
2    DERIVED    TABLE    range    indx_date    indx_date    32        179508    100.00    Using where; Using index

드디어 Extra에 using index가 등장하기 시작했다.
하지만 결국 b 테이블은 전체 테이블을 seek해서 배달되는 index랑 비교를 해야되기때문에 컬럼수가 무자비했고, 간혹 돌리다보니 using filesort뿐 아니라 using temporary까지 .....
오히려 더 안좋은 결과가 되었다. 실제 쿼리타임도 쿼리를 두번 돌려야되는 꼴이기때문에 더 느린건 두말할것도 없다

하지만 아이디어 자체가 나쁘다고 생각하지 않았기때문에 using temporary라는 키워드로 검색해보다가 이곳을 발견하게 되었다.
이제 내가 해결해야될 문제는 명확했다.

1. using index를 무조건 띄워야된다.
2. filesort 및 temporary가 뜨면 안된다
3. 최소한의 Row를 사용해야한다

링크한 블로그의 커버링 인덱스(covering index)를 참조하여 다시 작성한 쿼리는 다음과 같다.

select a.`index`, a.`date`,a.`time`, a.`nick`, a.`data` 
from (      
       select `index`  from TABLE
       where  `date` BETWEEN '20150700' AND '20150716'
       order by `date` desc
       ) b 
join TABLE a
on b.`index` = a.`index`
where a.`data` like '%검색어%' OR a.`nick` = '검색어'

사실 뭐가 다른지도 잘 구분이 안된다만 엄연히 다르니 잘 보자
따로 말로 설명하면 길어질것 같으니 성능부터 보자

1    PRIMARY    <derived2>    ALL                    193547    100.00    
1    PRIMARY    a    eq_ref    PRIMARY,indx_nick    PRIMARY    4    b.index    1    100.00    Using where
2    DERIVED    TABLE    range    indx_date    indx_date    32        193547    100.00    Using where; Using index

확연한 차를 보이고 있고, 제시한 모든 문제가 다 해결되었다.
최소한의 Row를 사용하고, index를 이용해 order by 까지 처리하는걸 볼 수 있다.

이번글에서는 엄청난 내용을 소개한것은 아니다.
하지만 쿼리 한개라도 아무렇게나 작성하면 안되고 충분한 테스트 및 성능 검증이 필요하다고 말하고 싶었다.
그래서 일일히 삽질한 쿼리도 공개를 해보면서 풀어나가듯이 작성해봤다.
어쨌든 디비쿼리와의 전쟁은 계속되겠고 최적화를 위해서 계속 삽질을 하자.



Reference
http://gywn.net/2012/04/mysql-covering-index/




늘모