MariaDB(MySQL)을 튜닝하다 - 2

어제에 이어 오늘도 DB에 관한 포스팅을 쓴다. 원래는 어제 포스팅 말미에 언급했던 것처럼 댓글에 DB튜닝의 결과를 적으려했으나 내용이 꽤 길어질 듯 하여 새롭게 포스팅을 작성한다.

일단 어제 걸어뒀던 delete작업은 약 세시간여만에 포기하기로 결정하고 프로세스를 kill해버렸다.

'으잉?왜?????????????????????????????'

라고 생각할 수도 있겠다. delete작업을 중지했던 이유는 간단한데 시간이 엄청나게 오래걸리기 때문이었다.

이는 MySQL의 알고리즘 때문인데 데이터에 대한 작업 수행속도를 높이기 위해 Index를 설정해놓아도 테이블에서 일정 수치 이상의 데이터 조작이 감지되면 Index를 사용하지 않고 데이터를 모두 읽고 가공하려 하기 때문이다. 쉽게 이야기해서 100이라는 Data가 있을 때, 10이라는 Data를 가공할 때는 미리 설정해둔 Index를 통해 빠르게 처리를 할 수 있지만 70이나 80정도의 Data를 가공하려고 하면 MySQL은 Index를 사용하지 않고 데이터를 읽고 가공하려 한다는 것이다.

테스트에 사용된 테이블은 총 32G정도의 Data를 가지고 있는데 그중 31G가 넘는 Data를 삭제하고 optimize를 할 예정이었다. 90%가 훨씬 넘는 Data를 가공하려고 하니 delete가 실행되었을 때 MySQL은 Index를 사용하지 않았고 그 때문에 작업의 완료는 언제가 될 지 알수가 없는 것이었다.

후...-_-...

이를 해결하기 위해 사용된 조건문의 범위를 줄이고 쿼리 실행전에 EXPLAIN을 사용하여 쿼리가 어떤 방식으로 처리가 되는지를 먼저 살펴보아야했다.

--------------------------------------------------

* EXPLAIN

 - EXPLAIN은 쿼리의 최적화 정도를 체크할 때 주로 사용하는 명령어이다. 지금과 같은 경우처럼 쿼리가 Index를 타고 실행이 되는지 여부를 따질때 자주 사용된다. Syntax는 간단하다. 만약

delete from 'document' where date between '20111001' and '20131031';

이런 쿼리를 실행하고자 할때 처리 방식이 궁금하다고 하면 

EXPLAIN select * from 'document' where date between '20111001' and '20130131';

이렇게 실행을 해보면 된다. 그러면 해당 쿼리가 어떤 방식으로 처리가 되는지 결과값이 나온다.

--------------------------------------------------

위의 방법으로 Index를 이용하여 처리가 되는 조건문의 범위를 설정하고 쿼리를 여러번 실행하는 방법을 이용하기로 했다. 현재 그 작업은 진행중이고 일부 쿼리는 실행되어 Data가 삭제 된 것을 확인했다. 별 다른 변수가 발생하지 않는다면 이번 테스트는 무난하게 끝날 것 같다.

휴...DB튜닝 정말 어렵다ㅠ

* 정리

1. START TRANSACTION;

2. 쿼리실행.(Index를 이용하는 범위내에서의 쿼리)

3. commit;

4. optimize 혹은 mysql 재실행.(용량 확보면에서는 둘다 차이가 없다.)

일단 요기까지.