장애 처리

1. 서버 과부하

실제 사용자의 수가 늘었거나 사용자의 행동 패턴이 바뀌었을 때 MySQL 서버의 부하가 높아질 수 있다. 사용자의 행동 패턴이 바뀌는 것은 주로 애플리케이션의 업그레이드나 기능 개선으로 발생할 때가 많은데, 이는 쿼리나 실행 빈도를 적절히 튜닝한 후 애플리케이션을 배포하거나 릴리즈해서 충분히 예방할 수 있다.

사용자의 수가 늘어나는 부분에 대비해서 MySQL 서버가 평상시에는 대략 2~30%의 자원만 사용하도록 유지하기도 한다. 하지만 너무 과다하게 서버를 투입하는 것은 비용적인 문제도 있으므로 사용자 수가 급작스럽게 증가할 때를 대비해 확장 가능한 구성으로 준비해 두는 편이 좋다.

1-1. 운영체제의 유틸리티를 이용해 장비의 부하 확인

유닉스 계열의 운영체제에서는 uptime 명령으로 해당 서버의 CPU가 처리해야 하는 작업이 얼마나 쌓여 있는지 확인할 수 있는데, 이 결과로 대략 이 서버의 부하가 어느 정도인지 판단해볼 수 있다.

 

uptime 명령 예시

• load average : 최근 1분, 5분 그리고 15분간의 CPU 작업 대기 큐에서 처리를 기다리는 프로세스의 평균 개수를 나타냄

현재 CPU가 어떤 작업에 주로 사용되고 있는지 알아보려면 vmstat라는 명령어를 이용하면 된다.

vmstat 명령은 현재 CPU가 어떠한 작업을 주로 처리하고 있는지 보여준다.

vmstat 명령 예시

• Procs
  • r : 실행시간을 위해 기다리고 있는 프로세스의 수
  • b : 계속 잠들어있는 프로세스의 수
  • c : swapped out 되었거나 실행 가능한 프로세스의 수
• Memory
  • swpd : virtual memory로 사용되는 양 (KB)
  • free : 쉬고 있는 memory의 양 (KB)
  • buff : buffer에 사용되고 있는 memory의 양 (KB)
• Swap
  • si : swapped in 된 memory의 양 (KB)
  • so : swapped out 된 memory의 양 (KB)
• IO
  • bi : block device에 보낸 blocks (blocks/s)
  • bo : block device로 부터 받은 blocks (blocks/s)
• System
  • in : 초당 발생한 interrupts의 수
  • cs : 초당 발생한 context switches(문맥 교환)의 수
• CPU
  • us : 사용자에 의해 사용된 시간
  • sy : 시스템에 의해 사용된 시간
  • id : 휴지 시간

iostat 명령은 장착된 디스크가 얼마나 사용되고 있는지 보여준다. “-dx” 옵션은 확장된 형태의 보고서를 출력해준다.

 

iostat 명령 예시

• rrqm/s : 장치에 대기중인 초당 읽기 요청 수
• wrqm/s : 장치에 대기중인 초당 쓰기 요청 수
• r/s : 초당 읽기 요청 수
• w/s : 초당 쓰기 요청 수
• rkB/s : 초당 읽기 KB
• wkB/s : 초당 쓰기 KB
• avgrq-sz : 요청의 평균 크기(섹터 단위)
• avgqu-sz : 요청의 평균 대기열 길이
• await : I/O요청의 평균 시간(ms). 이 정보는 대기열 요청 시간과 대기열에 대기하는 시간이 포함
• r_await : 서비스 요청을 받은 장치에 대한 읽기 요청의 평균 시간(ms). 대기열 요청 시간과 대기열에 대기하는 시간이 포함
• w_await : 장치에 제공되는 쓰기 요청의 평균 시간(ms). 이 정보는 대기열 요청 시간과 대기열에 대기하는 시간이 포함
• svctm : 장치에 발급 된 I/O 요청의 평균 시간(ms)
• %util : I/O요청이 장치에 발급된 동안의 CPU %. 이 수치가 100%에 가까울수록 오버헤드 발생

1-2. MySQL 서버의 에러 로그 확인

MySQL 서버에서 특별히 이상 징후나 에러가 발생하면 기본적으로 에러 로그에 기록하게 돼 있다. 그래서 긴급한 상황일수록 에러 로그의 마지막 부분은 반드시 확인해 보는 것이 좋다. MySQL 에러 로그 파일은 별도로 파일명을 지정하지 않으면 MySQL 서버의 데이터 디렉터리에 호스트명.err이라는 파일로 기록된다. 에러 로그가 너무 크다면 “less”명령을 이용해 파일의 마지막 부분만 열어 보는 것이 가능하다.

기본 로그 설정(리눅스)

• 에러 로그 설정 : log-error=/로그저장경로/error.log
• 쿼리 로그 설정 : log=/로그저장경로/query.log
• 바이너리 로그 설정 : log-bin=mysql-bin
• 슬로우 쿼리 로그 설정 : log-slow-queries=/로그저장경로/mysql-slow.log

long_query_time=5

• UPDATE 쿼리 설정 : log-update=update_logs

쿼리 로그 (리눅스 환경에서 my.cnf파일 수정)

• MyISAM 로그 : log-isam=/로그저장경로/myisamlog.log
• 쿼리 로그 : log=/로그저장경로/mysql.log
• 수정 로그 : log-update=/로그저장경로/mysql-update.log
• 바이너리 로그 : log-bin=/로그저장경로/mysql-bins.log
• 슬로우 쿼리 로그 : log-slow-queries=/로그저장경로/mysql-slow.log

long_query_time=5

1-3. MySQL 서버의 프로세스 리스트 확인

로그 파일을 확인했는데, 특별한 문제가 없다면 MySQL 서버에 로그인해서 상태를 확인한다. 우선 MySQL 서버가 어떤 쿼리를 실행하고 있는지, 어떤 프로세스가 특히 오랜 시간 동안 실행되고 있는지 확인하는 것이 좋다. MySQL의 “show processlist”라는 명령은 다음과 같은 다양한 프로세스의 정보를 보여주므로 MySQL 서버에 로그인해서 가장 먼저 확인해 보는 것이 좋다.

show processlist 명령 예시

주의 깊게 봐야 할 항목은 Command 칼럼과 Time 칼럼이며, 때로는 Info 칼럼에 출력되는 SQL도 간단히 문제의 원인을 찾는 데 많은 도움이 된다.

1-4. MySQL 서버의 최대 커넥션 설정 확인

MySQL 서버에서 허용하는 최대 커넥션의 수를 너무 크게 설정해 뒀다면 MySQL 서버는 동시에 너무 많은 요청을 받아 제대로 처리하지 못하는 현상이 발생할 수 있다. 이럴 때는 최대 허용 커넥션의 수를 적절히 줄여서 가능한 범위 내에서 MySQL 서버가 최대한 요청을 처리할 수 있게 해주는 것이 좋다. 만약 초기 최대 허용 커넥션 수가 너무 낮게 설정되어 있어 서버의 처리 용량에 여유가 있다면 최대 커넥션 수를 조금 더 늘려서 설정해주는 것이 좋다. 당장 MySQL 서버에 슬레이브를 추가해서 부하를 분산할 수 없다면 다음과 같이 MySQL 서버의 최대 커넥션 수를 조정하는 것이 좋다. MySQL 서버의 최대 커넥션 수는 max_connections 시스템 변수로 조정할 수 있다.

 

max_connections 사용 예시 (현재 설정된 max_connections 확인)

 

max_connections 사용 예시 (max_connections 변경)

max_connections 사용 예시 (변경 후 max_connections 확인)

1-5. MySQL 서버의 슬로우 쿼리 분석

지금까지 특별히 문제가 되는 부분을 찾지 못했다면 MySQL 서버의 슬로우 쿼리 로그를 확인해 보는 것이 좋다. MySQL 서버는 슬로우 쿼리 로그 설정을 통해 지정된 시간 이상 실행되는 느린 쿼리를 별도의 로그 파일로 기록할 수 있다. 이 로그 파일을 통해 어떠한 쿼리가 얼마나 느리게 실행됐는지 확인하고, 그 중에서 처리 시간이 오래 걸리거나 자주 실행된 빈도순으로 쿼리를 필터링할 수 있다.

우선은 간략하게 파일을 직접 열어서 마지막 부분에서 자주 나타나는 쿼리 위주로 성능에 문제가 있는 쿼리인지 아닌지를 검토해 보는 것이 좋다. 이 부분에서 특별히 큰 문제가 될 만한 쿼리를 찾지 못했다면 mysqldumpslow 명령을 통해 슬로우 쿼리 로그의 통계를 확인할 수 있다.

mysqldumpslow 명령 예시

• 쿼리 실행 횟수 : “mysqldumpslow -r -s c”옵션을 사용하면 쿼리가 많이 실행된 순서대로 슬로우 쿼리를 분석해서 결과를 출력한다.
• 쿼리로 조회한 레코드 : “mysqldumpslow -r -s r”옵션으로 mysqldumpslow 유틸리티를 사용하면 쿼리가 실행된 후 클라이언트로 결과 레코드를 많이 보내준 순서대로 정렬해서 출력한다.

1-6. 쿼리의 실행 빈도 확인

mysqladmin 명령은 여러 가지 기능을 제공하는데 MySQL 서버의 주요 설정값과 상태 정보를 쉽게 확인할 수 있고, 일정 시간 간격으로 MySQL 서버의 글로벌 상태 값이 얼마나 변했는가를 보여주는 기능이 있다.

mysqladmin 명령 예시

1-7. 각 원인별 조치

대부분의 MySQL 서버 과부하는 다음 세 가지 중의 하나가 원인이며, 각 상황별로의 대처법이다.

• 튜닝되지 않은 쿼리

튜닝되지 않은 쿼리가 서버 과부하의 원인이면 일반적으로는 서비스에서 사용되는 쿼리 중에서 상대적으로 부하가 더 높은 쪽에 속한 쿼리가 “SHOW PROCESSLIST” 항목에 많이 표시된다. 이럴 때는 “SHOW PROCESSLIST”에 많이 표시되는 쿼리를 집중적으로 찾아서 튜닝하면 된다. 그리고 MySQL 서버의 슬로우 쿼리로그를 mysqldumpslow 명령으로 분석해 높은 부하를 유발하는 쿼리 순서대로 튜닝을 하는 것이 좋다.

• 잘못 사용된 쿼리

잘못 사용된 쿼리란 애플리케이션의 버그나 사용자의 실수로 트랜잭션이 종료되지 않고 잠금이 계속 유지되면서 다른 서비스용 쿼리에 영향을 미치는 것을 의미한다. 주로 이런 상황에서는 “SHOW PROCESSLIST” 결과를 보면 특정 테이블에 대해 INSERT나 UPDATE, 또는 DELETE 쿼리가 “Waiting…” 또는 “updating…” 상태로 표시될 때가 많다. 이렇게 하나의 잘못된 쿼리가 다른 서비스용 쿼리에 영향을 미친다면 잠금을 해제하지 않는 해당 트랜잭션을 찾아서 종료해주면 된다.

• 실제 사용자가 많은 경우

쿼리는 적절히 튜닝됐지만 실제 사용자가 많아서 MySQL 서버가 과부하 상태로 된 것이라면 최대한 빨리 MySQL서버를 더 투입해서 부하를 분산시키는 것이 좋다. 만약 빠른 시간 내에 장비를 투입하기가 어렵다면 임시 방편으로 바이너리 로그의 동기화 방식이나 InnoDB 트랜잭션 커밋의 동기화 방식을 조금 느슨한 형태로 변경해서 디스크의 병목을 줄여 서비스를 유지하는 것도 한 가지 방법이다.

2. MySQL 서버 셧다운

최근에는 MySQL 서버가 많이 안정화되어 MySQL 서버의 코드 버그로 인한 MySQL 서버의 비정상적인 셧다운은 거의 발생하지 않는다.

만약 특별히 명령이 없었는데도 MySQL 서버가 재시작된다면 MySQL 서버의 에러 로그 파일을 확인하고 “Segmentation fault”메시지가 있는지 확인해 보는 것이 좋다. 또한 MySQL 서버가 Segmentation fault를 일으키고 종료하면 데이터 파일이나 InnoDB의 로그 파일이 손상될 수도 있다. 데이터 파일의 손상은 또다른 문제의 원인이 될 수 있으므로 갑자기 MySQL 서버가 비정상적으로 종료됐을 때는 MySQL 서버의 에러 로그 파일을 통해 어떤 부분이 문제인지 확인하는 것이 중요하다.

3. MySQL 복구(데이터 파일 손상)

3-1. MyISAM

MyISAM 테이블에서는 잘못된 조작으로 인해 인덱스나 데이터 파일이 손상될 수 있다. 이때는 REPAIR TABLE 명령으로 MyISAM의 데이터 파일이나 인덱스 파일을 다시 복구하는 것이 가능하다.

하지만 데이터 페이지가 손상됐다면 테이블의 일부를 복구하지 못할 수도 있다.

3-2. InnoDB

InnoDB 테이블은 하드웨어나 운영체제의 문제가 아니라면 InnoDB 데이터 파일이나 로그 파일이 손상될 가능성은 상당히 낮은 편이다. 하지만 한번 문제가 생기면 복구하기가 쉽지 않다. InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. 이 단계에서 자동으로 복구될 수 없는 손상이 있다면 자동 복구를 멈추고 MySQL 서버는 종료돼 버린다.

이때는 MySQL 서버의 설정 파일에 “innodb_force_recovery” 옵션을 추가해 MySQL 서버를 시작해야 한다. 이 설정 값은 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 선별적으로 진행할 수 있게 한다.

• 만약 InnoDB의 로그 파일이 손상됐다면 6으로 설정하고 MySQL 서버를 기동한다.
• InnoDB 테이블의 데이터 파일이 손상됐다면 1로 설정하고 MySQL 서버를 기동한다.
• 만약 어떤 부분의 문제인지 알 수 없다면 “innodb_force_recovery” 설정 값을 1부터 6까지 변경하면서 MySQL을 재시작해 본다. 즉 “innodb_force_recovery”설정 값을 1로 설정한 후 MySQL 서버를 재시작해 보고, MySQL이 시작되지 않으면 다시 2로 설정하고 재시작해 보는 방식이다. “innodb_force_recovery”값이 커질수록 그만큼 심각한 상황이어서 데이터 손실 가능성이 높아지고 복구 가능성은 낮아진다.

각 숫자값으로 복구되는 장애 상황과 해결 방법

• 1 (SRV_FORCE_IGNORE_CORRUPT)

InnoDB의 테이블 스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 MySQL 서버를 시작한다. 에러 로그 파일에 “Database page corruption on disk or a failed” 메시지가 출력될 때는 대부분 이 경우에 해당한다. 이때는 mysqldump 프로그램이나 SELECT INTO OUTFILE… 명령을 이용해 덤프해서 데이터베이스를 다시 구축하는 것이 좋다.

• 2(SRV_FORCE_NO_BACKGROUND)

InnoDB는 쿼리의 처리를 위해 여러 종류의 백그라운드 스레드를 동시에 사용한다. 이 복구 모드에서는 이러한 백그라운드 스레드 가운데 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다. InnoDB는 트랜잭션의 롤백을 위해 Undo 데이터를 관리하는데, 트랜잭션이 커밋되어 불필요한 Undo 데이터는 InnoDB의 메인 스레드에 의해 주기적으로 삭제된다. 만약 InnoDB의 메인 스레드가 Undo 데이터를 삭제하는 과정에서 장애가 발생한다면 이 모드로 복구하면 된다.

Undo 데이터는 InnoDB의 시스템 테이블 스페이스에 저장되므로 메인 스레드가 Undo 데이터를 삭제할 수 없을 때는 시스템 테이블 스페이스를 새로 구축해야 한다. InnoDB에서 시스템 테이블 스페이스를 새로 구축하려면 mysqldump를 이용해 전체 데이터를 덤프받고 데이터베이스를 새로 구축해야 한다.

• 3(SRV_FORCE_NO_TRX_UNDO)

InnoDB에서 트랜잭션이 실행되면 롤백에 대비해 변경 전의 데이터를 Undo 영역에 기록하게 된다. 일반적으로 MySQL 서버는 다시 시작하면서 Undo 영역의 데이터를 먼저 데이터 파일에 적용하고 그다음 Redo 로그의 내용을 다시 덮어쓰기해서 장애 시점의 데이터 상태를 만들어낸다. 그리고 정상적인 MySQL 서버의 시작에서는 최종적으로 커밋되지 않은 트랜잭션은 롤백을 수행하지만, innodb_force_recovery가 3으로 설정되면 커밋되지 않은 트랜잭션의 작업을 롤백하지 않고 그대로 놔두게 된다. 즉, 커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아 있도록 MySQL서버를 시작하는 모드다. 이때도 우선 MySQL 서버가 시작되면 mysqldump를 이용해 데이터를 백업해서 다시 데이터베이스를 구축하는 것이 좋다.

• 4(SRV_FORCE_NO_IBUF_MERGE)

InnoDB는 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시 처리할 수도 있고 인서트 버퍼에 저장해두고 나중에 처리할 수도 있다. 이렇게 인서트 버퍼에 기록된 내용은 언제 데이터 파일에 병합될지 알 수 없다. MySQL을 종료해도 병합되지 않을 수 있는데, 만약 MySQL이 재시작되면서 인서트 버퍼의 손상을 감지하게 되면 InnoDB는 에러를 발생시키고 MySQL 서버는 시작하지 못한다.

이때 innodb_force_recovery를 4로 설정하면 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL이 시작되게 해준다. 인서트 버퍼는 실제 데이터와 관련된 부분이 아니라 인덱스에 관련된 부분이므로 테이블을 덤프한 후 다시 데이터베이스를 구축하면 데이터의 손실 없이 복구할 수 있다.

• 5(SRV_FORCE_NO_UNDO_LOG_SCAN)

MySQL 서버가 장애나 정상적으로 종료되는 시점에 진행 중인 트랜잭션이 있었다면 MySQL은 그냥 단순히 그 커넥션을 강제로 끊어 버리고 별도의 정리 작업 없이 종료한다. MySQL이 다시 시작하게 되면 InnoDB 엔진은 Undo 레코드를 이용해 데이터 페이지를 복구하고 Redo 로그를 적용해 종료 시점이나 장애 발생 시점의 상태를 재현해 낸다. 그리고 InnoDB는 마지막으로 커밋되지 않은 트랜잭션에서 변경한 작업은 모두 롤백 처리를 한다. 그런데 InnoDB의 Undo 로그를 사용할 수 없다면 InnoDB 엔진의 에러로 MySQL 서버를 시작할 수 없다.

이때 innodb_force_recovery 옵션을 5로 설정하면 InnoDB 엔진이 Undo 로그를 모두 무시하고 MySQL을 시작할 수 있다. 하지만 이 모드로 복구되면 MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남는 것이라 볼 수 있다. 이때도 mysqldump를 이용해 데이터를 백업하고, 데이터베이스를 새로 구축해야 한다.

• 6(SRV_FORCE_NO_LOG_REDO)

InnoDB 스토리지 엔진의 Redo 로그가 손상되면 MySQL 서버가 시작되지 못한다. 이 복구 모드로 시작하면 InnoDB엔진은 Redo 로그를 모두 무시한 채로 MySQL 서버가 시작된다. 또한 커밋됐다 하더라도 Redo 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시된다. 즉 마지막 체크 포인트 시점의 데이터만 남게 되는 것이다. 이때는 기존의 InnoDB의 Redo 로그는 모두 삭제하고 MySQL 서버를 시작하는 것이 좋다. MySQL 서버가 시작하면서 Redo 로그가 없다면 새로 생성하므로 별도로 파일을 만들어 줄 필요는 없다.

이때도 mysqldump를 이용해 데이터를 모두 백업해서 MySQL 서버를 새로 구축하는 것이 좋다.

4. 테이블 메타 정보의 불일치

만약 RENAME TABLE 명령과 같이 테이블의 이름을 변경하는 도중에 문제가 생기면 MySQL에서 관리하는 테이블의 메타 정보와 InnoDB 스토리지 엔진에서 관리하는 메타 정보가 동기화되지 못하는 문제가 발생하기도 한다. 이 상황에서는 MySQL의 테이블 정보를 저장하는 *.FRM파일은 삭제됐지만 InnoDB의 딕셔너리 정보에서는 지워지지 않는 현상이 발생한다. 그래서 그 이후로는 그 이름의 테이블을 삭제할 수도, 새로 생성할 수도 없게 된다.

• MySQL(.FRM 파일)은 테이블 정보를 가지고 있지만 InnoDB에는 없을 때

이때는 MySQL 서버에 로그인해서 해당 테이블을 DROP 명령으로 삭제하면 MySQL 서버는 *.FRM 파일을 삭제한다. 그리고 InnoDB 스토리지 엔진으로 삭제 명령을 보내는데, InnoDB는 해당 테이블 정보가 딕셔너리에 없으면 그냥 무시해버리므로 특별히 문제되지 않는다.

• InnoDB는 테이블 정보를 가지고 있지만 MySQL(.FRM 파일)에는 없을 때

InnoDB 스토리지 엔진에 포함된 딕셔너리 정보는 MySQL 엔진을 통하지 않고서는 제어할 수 없다. 그래서 MySQL엔진이 인식하지 못하는 테이블 정보를 InnoDB 스토리지 엔진이 가지고 있다면 정상적인 방법으로는 이를 해결할 수 없다. 또한 이러한 상황에서는 문제의 테이블과 똑같은 이름의 테이블을 생성할 수도 없다.

이때는 우선 MySQL 서버에 로그인해서 다른 DB로 이동한다. 만약 다른 DB가 없다면 임시로 생성해서 사용한다. 그러고 나서 지금 문제가 되는 테이블과 똑같은 구조와 이름의 테이블을 하나 생성하고, 이 테이블의 .FRM 파일을 복사해서 원래 그 테이블이 있었던 것처럼 MySQL 엔진을 속인다.

5. 복제가 멈췄을 때

슬레이브 MySQL은 마스터 데이터와의 동기화를 위해 “SQL 스레드”와 “IO 스레드”라는 2개의 스레드를 사용하는데, 이 두 스레드의 상태가 모두 정상일 때만 복제가 정상적으로 진행된다. 만약 복제가 되지 않는다면 이 두 스레드 중 하나가 오류로 인해 복제가 멈춰 있기 때문일 것이다.

우선 슬레이브 MySQL에 로그인해서 “show slave status”라는 명령으로 슬레이브 MySQL 서버의 복제 상태를 확인할 수 있다.

 

MySQL 서버 복제 상태 확인 예시

• Slave_IO_Running, Slave_SQL_Running이 “Yes”이면 해당 스레드가 정상적으로 작동하고 있음을 의미하고, 둘 중 하나라도 상태가 “No”이면 복제가 멈춰 있는 상태를 의미한다.
• Seconds_Behind_Master라는 상태의 값은 현재 슬레이브 MySQL 서버의 데이터가 마스터보다 얼마나 지연돼 있는지 보여준다. 슬레이브 MySQL 서버에 특별한 오류가 없더라도 마스터에서 너무 많은 트랜잭션이 실행되거나 무거운 쿼리가 실행되면 복제 지연이 발생해 0보다 큰 값을 표시할 수도 있다.

IO 스레드가 멈췄을 때

마스터 서버로부터 바이너리 로그를 가져오는 역할을 하는 스레드로 IO 스레드가 “No”일때는 슬레이브서버가 마스터 서버에 접속하지 못하거나 로그인을 못하고 있음을 의미한다. 이 때 네트워크가 정상인지 마스터 서버가 정상 작동을 하는지, 복제용 계정이 사용 가능한 상태인지 점검한다.

SQL 스레드가 멈췄을 때

IO 스레드가 가져온 바이너리 로그를 슬레이브서버에 재실행하는 역할을 수행한다. 재실행하는 쿼리 중에서 오류가 발생한다면 SQL 스레드는 더 진행하지 않고 멈춰 있게 된다.

이 에러의 종류에 따라 적절한 조치가 필요하다.

• Lock wait timeout exceeded으로 인한 오류

단순히 아래의 명령으로 복제 슬레이브를 재시작하는 것만으로 대부분 해결된다.

• AUTO_INCREMENT가 아닌 프라이머리 키의 Duplicate key error로 인한 오류

이미 슬레이브에 저장된 데이터를 또 저장하려는 것일 가능성이 높다. 이때는 프라이머리 키 값을 기준으로 기존 테이블의 레코드와 복제 도중 에러가 발생한 쿼리의 각 컬럼 값을 비교해서 그냥 버릴지 기존의 레코드를 SQL의 컬럼 값으로 업데이트할지 결정하면 된다.

레코드의 복구 처리가 완료되면 슬레이브 서버가 복제 도중 에러를 발생시킨 쿼리는 무시하고 바이너리 로그의 다음 쿼리부터 계속 복제를 수행하게 해주면 된다.

• AUTO_INCREMENT 프라이머리 키나 유니크 키의 Duplicate key error 오류

AUTO_INCREMENT 컬럼은 쿼리가 실행될 때마다 증가되는 값이므로 마스터 서버와 슬레이브 서버에서 동기화 되지 못해서 발생한 에러일 가능성이 높다.

우선 SQL 스레드에서 실행하려고 했던 SQL문장의 각 컬럼을 이용해 기존 테이블에 똑같은 컬럼 값을 가지는 레코드가 있는지 찾아보아야한다. 또한 AUTO_INCREMENT 프라이머리 키나 유니크 키로도 검색해 똑같은 레코드인지 비교해야 한다.

위 두 상황을 고려하여 슬레이브 서버에 INSERT나 UPDATE를 하면 된다. 적용이 완료되면 sql_slave_skip_counter를 사용하여 바이너리 로그의 쿼리를 무시하고 복제가 다음으로 진행하게 해주면 된다.

6. 바이너리 로그로 디스크가 꽉 찬 경우

마스터 MySQL 서버에서는 복제를 위해 바이너리 로그를 기록하는데, 데이터를 변경하는 쿼리가 많이 실행되는 서버에서는 바이너리 로그 파일의 개수나 크기가 그만큼 빨리 증가하게 된다.

이 때문에 기본적으로 필요한 요건에 따라 적절히 바이너리 로그 파일이 자동으로 삭제될 수 있게 시스템 설정을 적용하는 것이 좋다.

만약 바이너리 로그 파일이 너무 많아서 삭제할 때 그냥 운영체제의 명령으로 파일을 삭제해서는 안된다. 바이너리 로그 파일의 목록은 MySQL 서버에 의해 별도로 관리되므로 MySQL 서버에 로그인해서 다음 명령으로 삭제하는 것이 좋다.

mysql> PURGE BINARY LOGS BEFORE '2011-07-23 10:00:00';

만약 실수로 바이너리 로그 파일을 운영체제 명령으로 삭제했다면 바이너리 로그 파일의 목록을 관리하는 인덱스 파일(*.index)에도 삭제된 파일의 내용을 업데이트해야 한다.

7. DB의 테이블 생성 DDL만 덤프

mysqldump 명령어들 중 “--no-data”옵션을 사용하면 데이터를 제외하고 나머지 모두를 덤프 받을 수 있다.

mysql> mysqldump -uroot -p --opt --routines --triggers --databases DB1 DB2 > schema.sql

• 각 테이블의 AUTO_INCREMENT값이 1부터 설정되지 않고 기존에 사용되던 자동 증가 값이 명시되어 덤프된다.
• DDL을 덤프받아서 생성할 때는 반드시 AUTO_INCREMENT값을 1로 모두 초기화하거나 CREATE TABLE 문장에서 “AUTO-INCREMENT=xxx”옵션을 제거하고 사용하는 것이 좋다.

8. mysqldump의 결과를 다른 이름의 DB로 적재

덤프 파일을 이용해 다른 이름의 DB로 적재하고자 할 때는 “--databases”나 “--all-databases”옵션은 제거하고 DB명만 명시해서 덤프를 받는 것이 좋다.

또한 “--no-create-db” 옵션을 이용해 덤프된 내용에 “CREATE DATABASE…”명령이 포함되지 않게 하는 것이 중요하다.

mysql> mysqldump -uroot -p --opt --routines --triggers --no-create-db DB1 > backup_db1.sql

“CREATE DATABASE DB1…” 명령이나 “USE DB1”등과 같은 명령이 포함돼 있지 않으므로 데이터 적재 명령을 수행하는 커넥션의 기본 DB로 적재하게 된다.

덤프 파일을 새로운 DB로 적재할 때는 먼저 새로운 DB를 생성하고 “USE…” 명령으로 기본 DB로 선택한 다음 실행하면 된다.

9. 테이블이나 레코드의 잠금 해결

InnoDB 테이블에서 UPDATE나 DELETE와 같은 문장이 레코드를 잠근 상태에서 COMMIT/ROLLBACK으로 트랜잭션을 종료하지 않아 문제가 발생할 때가 있다.

풀리지 않는 잠금으로 인해 다른 트랜잭션에서 그 테이블의 레코드를 변경할 수 없어서 계속 대기 상태로 빠지거나 “Lock wait timeout exceeded”와 같은 에러 메시지가 발생한다.

테이블이 사용하는 스토리지 엔진의 종류별로 잠금의 추적 및 해제 방법이 다르다.

• MyISAM과 MEMORY 스토리지 엔진 잠금 확인 및 해결

“SHOW OPEN TABLES”명령을 바로 실행하면 MySQL 서버의 모든 테이블에 대해 잠금 여부를 보여준다. 하지만 이 명령은 어떤 클라이언트의 커넥션이 잠금을 기다리고 있는지 보여주지 않는데, 이를 확인하려면 “SHOW PROCESSLIST”명령을 사용해야 한다.

mysql > SHOW PROCESSLIST;
+-----+------+-----------+------+----------+------+---------+----------------------------------------+
| Id  | User | Host      | db   | Command  | Time | State   | Info
+-----+------+-----------+------+----------+------+---------+----------------------------------------+
| 1   | root | localhost | test | Sleep    | 46   |         | NULL                                   |
| 3   | root | localhost | test | Query    | 22   | Locked  | UPDATE employees SET hire_date=now()   |
|     |      |           |      |          |      |         | WHERE emp_no=100001                    |
| 4   | root | localhost | test | Query    | 7    | Locked  | UPDATE employees                       |
|     |      |           |      |          |      |         | SET birth_date=now(), hire_date=now()  |
|     |      |           |      |          |      |         | WHERE emp_no=100001                    |
| 5   | root | localhost | NULL | Query    | 0    | NULL    | show processlist                       |
+-----+------+-----------+------+----------+------+---------+----------------------------------------+

위 결과를 보면 Id가 3번인 클라이언트나 4번인 클라이언트의 State가 "Locked"라는 것으로 보아 테이블 락을 기다리고 있다는 것을 알 수 있으며, Id가 1번인 클라이언트는 지금 아무것도 하고 있지 않다. 3번과 4번 클라이언트가 업데이트하고자 하는 테이블이 동일하게 employees인 것으로 보아 3번과 4번 클라이언트가 employees 테이블 잠금을 가지고 있지는 않다는 것을 알 수 있다. 그러므로 1번 클라이언트가 이 잠금을 가지고 있는 것이며, 이 경우에는 Id가 1번인 커넥션을 종료시키면 3번과 4번 커넥션이 차례대로 처리를 진행할 수 있다. 클라이언트를 종료시키는 방법은 "KILL QUERY 클라이언트_Id" 명령으로 클라이언트가 실행하고 있는 쿼리만 종료시키거나 "KILL 클라이언트_Id" 명령으로 클라이언트 커넥션을 종료시킬 수 있다.

• InnoDB 스토리지 엔진 잠금 확인 및 해결

“SHOW PROCESSLIST” 명령으로 출력되는 결과는 동일하다. 하지만 각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금은 어떤 트랜잭션이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있다.

INFORMATION_SCHEMA라는 DB에 INNODB_TRX라는 테이블과 INNODB_LOCKS, 그리고 INNODB_LOCK_WAITS라는 테이블이 준비돼 있다. 잠금이나 대기가 발생할 경우 InnoDB 스토리지 엔진에서 관련 정보를 계속 이 테이블로 업데이트하기 때문에 다음과 같이 간단히 SELECT 해서 확인할 수 있다.

mysql > SELECT * FROM information_schema.innodb_locks;

mysql > SELECT * FROM information_schema.inndb_trx;

INNODB_LOCKS 테이블은 어떤 잠금이 존재하는지를 관리하며, INNODB_TRX 테이블은 어떤 트랜잭션이 어떤 클라이언트 (프로세스)에 의해 기동되고 있으며, 어떤 잠금을 기다리고 있는지를 관리한다. 사실 이 테이블의 각 내용은 그다지 중요하지 않고, 이 3개의 테이블을 조합해서 어떤 커넥션이 어떤 커넥션을 기다리게 만드는지를 알아낼 수 있다는 것이 중요하다.

mysql > SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

이 쿼리의 결과에서 "waiting.." 칼럼은 잠금을 기다리는 트랜잭션이나 프로세스를 의미하며, "blocking..." 칼럼은 잠금을 해제하지 않아서 다른 트랜잭션을 막고(기다리게 하고) 있는 트랜잭션이나 프로세스를 의미한다.

다른 트랜잭션의 진행을 막고 있는 트랜잭션이 있다면 KILL 명령으로 다른 트랜잭션 처리를 재개할 수 있다.

10. InnoDB의 잠금 대기 시간 초과

InnoDB에서는 어떤 한 트랜잭션에서 레코드를 변경하는 중에 다른 트랜잭션에서 똑같은 레코드를 동시에 수정할 수 없다. 그래서 기존의 트랜잭션이 완료될 때 까지 기다려야 하는데, InnoDB에서는 무한정 레코드의 잠금이 해제될 때까지 기다리는 것이 아니라 MySQL 서버의 innodb_lock_wait_timeout 시스템 설정에 정의된 시간만큼 기다린다.

만약 이 시간 동안 기다렸음에도 레코드의 잠금을 획득하지 못한다면 MySQL 서버는 “ Lock wait timeout exceeded; try restarting transaction” 에러 메시지를 출력하고 쿼리를 강제로 종료시킨다.

이 에러가 발생했을 때 다시 쿼리를 재실행하지 않고 트랜잭션을 끝낼 때는 반드시 ROLLBACK이나 COMMIT 명령을 실행해야 한다.

어떤 트랜잭션이 레코드를 변경하려는데, 다른 트랜잭션이 해당 레코드가 포함된 테이블 자체를 잠그고 있다면 이때는 “Lock wait timeout exceeded” 에러가 발생하지 않고 계속 대기한다. 즉 ALTER TABLE로 칼럼이나 인덱스를 추가하는 작업이 실행되고 있다면 INSERT나 UPDATE, 또는 DELETE문장은 잠금을 걸기 위해 기다려야 한다.

데드락이 발생하면 곧바로 InnoDB 스토리지 엔진이 데드락을 감지하고 둘 중에서 하나의 트랜잭션을 강제 종료해서 해결한다.