MariaDB(MySQL)을 튜닝하다.

DB용량이 하루가 다르게 커져가고 있다. 하루에 작성되는 글의 수도 엄청날 뿐더러 그것보다 더 많은 코멘트, 꽤나 많은 용량의 이미지와 플래시등과 같은 첨부파일 등등...블로그가 공개된 곳이다보니 DB규모를 정확하게 적을 순 없지만 테이블 하나의 크기가 거의 100G에 육박하는 것도 있으니 그 크기가 얼마나 큰지 대략 짐작이 갈 것이다.

문제는 DB의 크기가 커지다보니 컨트롤 하기가 힘들어질 뿐더러 결정적으로 하드 디스크용량의 문제때문에 어떻게든 튜닝포인트를 찾아 불필요한 Data를 삭제하여 용량을 확보하는 것이 시급한 문제로 부각되기 시작한 것이다.

회사에 DBA가 없기 때문에 이 문제는 개발자 한명이 진행을 하기로 했지만 SE가 강건너 불구경 할 수 있는 처지는 아니다. DB는 서버에 설치되어 있고 서버는 SE가 관리를 한다. 즉, 어쩔 수 없이 강제로 DB튜닝작업에 참여하게 되었다.

사실 처음에는 아주 간단하게 생각했다.

1. 삭제할 Data 선정작업.

2. Delete 고고싱.

3. 용량확보.

4. 끝!!

이라고 생각했지만 이런 생각은 사전조사를 시작하자마자 날아가 버렸다.

회사 DB는 MariaDB라는 외산 DB로써 조금은 생소한 DB이다. MariaDB의 탄생에 대한 비화나 에피소드등 꽤 재미있는 이야기가 많지만 그 이야긴 차후 기회가 있을 때 이야기하기로 하겠다.

MariaDB는 한마디로 MySQL DB와 그 모습이 99% 닮아있는 DB이다. 혹자들은 MySQL이랑 똑같다!라고도 하는데 크게 부정할 수도 없는 말이다. MariaDB의 태생이 MySQL이니 닮다 못해 똑같다고 느낄 수 밖에.

1번 작업은 순조롭게 진행되었으니 패스.

2번작업부터 삐걱거리기 시작했다. 현재 DB에사 사용중인 Engine은 Innodb인데 Innodb는 단순히 delete를 사용하는 것만으로 Data가 삭제되는게 아니라는 것이다. 즉, delete를 사용하여 Data를 삭제했더라도 용량은 확보되지 않는 것이다.

헐...뭐 이런 경우가...-_-...

그리고 작업을 하면서 delete를 했음에도 불구하고 하드용량 사용율이 오히려 더 늘어나는 진귀한(?)현상을 목격하기도 했다. 덕분에 테스트로 진행한 서버의 하드 용량이 Full이 되어버려 디비가 안전모드로 들어가서 서버가 먹통이 되기도 했다. 지금 포스팅에서야 하드 용량이 Full이 되었기 때문에 디비의 사용이 자동으로 차단되었다고 담담히 적을 수 있지만 해당 문제가 발생했을 때 정말 진땀빼면서 겨우겨우 원인을 찾았었다. 아마 그 전에 이런 경험을 해보지 못했더라면 아직까지 원인찾는다고 헤매고 있을지도 모르겠다.

Innodb는 delete를 사용한 다음 optimize라는 명령을 실행해줘야 Data가 모두 삭제되고 삭제된 Data만큼의 디스크 용량이 확보 된다. Syntax는

optimize table [table명];

이다.

회사에서 사용중인 DB의 크기가 상당히 크고 정리를 해야하는 테이블의 크기또한 상당했기 때문에 테스트서버에서 어느정도 크기가 되는 테이블의 delete와 optimize진행을 해볼 필요가 있었다. 무턱대고 실서버에서 진행하다가 서버가 다운되어 버리거나 DB가 유실되는 경우가 발생하게 되면...후...생각만 해도 끔찍하다. SE로써 이런 경험은 그닥 유쾌한 것이 되지 못하는 걸 알기에 최대한 테스트를 많이 진행해본 다음 상황 변수를 파악하고 신중하게 실서버에 적용하는 과정이 필요했다.

여기서 또 하나의 문제가 발생했는데 튜닝에 포함되는 테이블중에 100G정도의 크기가 되는 테이블도 포함이 되어 있었기 때문에 테스트에 사용할 테이블도 꽤 큰 용량의 테이블을 사용해야만 했다. 그렇게해서 고른 테이블은 32G정도의  table이였는데 이 테이블을 dump뜨고 rsync로 테스트서버까지 옮기는 것은 크게 문제가 되지 않았다. 문제는 import할때 발생했는데 워낙 큰 용량의 테이블이고 테스트 서버또한 사양이 크게 좋은 편은 아니었기에(옥타코어지만 메모리는 8G...뭔가 언밸런스한 느낌이 들지 않는가?)이 작업이 거의 20시간 가까이 진행되어야 했던 것이다. 덕분에 테스트 진행 날짜는 하루 연기되었다.

그 후 순조롭게 진행되어 원하던 튜닝작업을 마칠 수 있었다......

로 이야기가 끝난다면 참 좋으련만 시련은 여기서 멈추지 않았다.

MySQL을 조금 다뤄 본 사람이라면 

'lock wait timeout exceeded try restarting transaction'

라는 오류메세지를 본적이 있을 것이다.

풀어보자면 작업 대상이 되는 테이블이 lock상태라서 작업이 진행되지 않는다는 이야기인데 나 같은 경우 이 메세지가 갑작스레, 뜬금없이 나왔다. 앞선 몇가지 작업에서는 이러한 오류 메세지가 나오지 않았는데 너무도 갑자기 나타나는 바람에 멘붕직전까지 갔다.

구글링을 통해 몇가지 해결 방법을 찾긴 했다.

1. my.cnf에 innodb_lock_wait_timeout의 값을 높게 설정한다. 기본값은 50s이다.(해당 값이 없다면 기본적으로 50s를 가지게 된다.)MySQL상에서 설정값을 체크해보려면 

show variables like 'innodb_lock_%';

명령어를 사용하면 된다.

2. 대량의 쿼리를 잘게 쪼개어 빠르게 결과값이 도출되도록 한다.

1번의 방법은 가장 간단한 방법이긴 하지만 deadlock을 만나거나 했을 경우 대처가 어렵다는 단점이 있다고 하고 2번의 경우 테이블 자체의 용량이 크다면 효율성이 극히 낮기 때문에 해당사항이 나에겐 해당사항이 없었다. 결국 두가지 방법 모두 적용할 수 없었기에 다른 방법을 찾아야 했다.

그러던 와중에 한가지 방법을 찾았다. MySQL은 기본적으로 auto commit을 사용하도록 설정되어 있는데 이를 막고 트랜잭션을 사용하겠다고 선언해주는 것이다. 쉽게 이야기해 delete작업을 진행하기 전에

START TRANSACTION;

을 선언해서 트랜잭션을 시작하겠다고 선언한 다음 delete 작업을 진행하고 해당 과정을 마치겠다는 의미의

COMMIT;

을 선언해주면 lock 관련 오류 메세지를 보지 않아도 되는 것이다.

그다음 마지막으로 

OPTIMIZE TABLE [TABLE명];

을 실행하면 계획했던 튜닝은 끝~!

이 방법을 이용해서 현재 테스트를 진행중이고 결과가 나오기를 기다리면서 포스트를 작성하고 있다. 결과가 나온다면 댓글에 결과를 적도록 하겠다.