테이블 유지보수는 데이터베이스의 문제를 고치거나 규명하는데 유용하다. 또 한 쿼리가 좀 더 빨리 수행될 수 있도록 도와준다.
MySQL의 유지보수 작업은 여러 가지 타입으로 수행될 수 있다.
0. 테이블체크는 테이블의 구조와 데이터에 아무 문제가 없다는 것을 확인하기 위해 무결성 체크를 수행한다.
0. Table Repair는 사용 가능한 상태로 테이블을 restore 하기 위해 무결성 문제를 고친다. 이 작업은 MyISAM 타입의 테이블만 가능하다.
0. Table analysis는 인덱스 키 값의 분포도에 대한 통계수치를 업데이트한다. 이것은 옵티마이저가 테이블에 쿼리를 실행할 때 좀 더 나은 실행계획을 만들 수 있게 해주는 정보이다.
0. 테이블 최적화는 데이타에 좀 더 효과적으로 접근 할 수 있도록 테이블을 재구성한다.
테이블 분석과 최적화는 가장 최적으로 수행하도록 유지시키기 위해서 정기적으로 해야 할 작업이다.
0. MySQL이 MyISAM이나 InnoDB 테이블을 분석할 때 인덱스 통계는 업데이트 된다. 옵티마이저는 테이블에서 어떻게 레코드를 찾아야 가장 좋은지, 조인 시에 어떤 테이블을 먼저 읽어야 할지 등에 대해 좀 더 나은 결정을 내리는 작업을 할 때 이런한 통계정보를 활용한다.
0. MySQL이 MyISAM 테이블을 최적화 할 때 MySQL은 사용되지 않은 공간을 회수하고 인덱스를 정렬하고 인덱스 통계를 업데이트하기 위해 데이터 파일을 조각모음한다. 주기적인 조각모음은 VARCHAR, VARBINARY, BLOB 혹은 TEXT와 같이 가변길이 COLUMN을 포함하는 테이블에 대한 접근 속도를 높이는데 유용한다. 이런 테이블에 데이터를 등록하고 삭제하면 데이터에 빈틈이 생길 가능성이 많은데 특히 자주 데이터가 갱신되는 경우 더욱 그러하다. 데이터 조각모음은 이러한 데이터의 빈틈을 제거해 준다.
테이블 분석과 최적화 작업은 완전히 장착되고 데이터가 모두 완벽히 insert 되어 이후로는 변경되지 않을 것이라고 예상되는 테이블에 수행될 때 최대로 효율이 높아진다. 분석과 최적화의 장점은 테이블이 계속적으로 갱신되는 경우 줄어들기 때문에 이러한 작업들을 주기적으로 반복하여 수행해야 할 지도 모른다.
테이블 유지 보수를 위해 사용 가능한 툴은 SQL 문장과 클라이언트 프로그램, 자동 복구를 위한 서버기능 들이 있으나 이 글에서는 클라이언트 프로그램에 관한 글은 배재하였습니다.
1. SQL Statements for Table Maintenance
MySQL은 테이블 유지보수를 위한 여러 가지 SQL구문을 가지고 있다. 무결성 체크를 위한 CHECK TABLE, repair를 위한 REPAIT TABLE, 분석을 위한 ANALYZE TABLE, 최적화를 위한 OPTIMIZE TABLE이 있다.
1) CHECK TABLE
:: 테이블 구문과 내용에 대한 무결성 체크를 수행한다. 이 것은 MyISAM 과 InnoDB 테이블에서 모두 작동한다. MyISAM 테이블 에서는 Check Table이 인덱스 통계정보를 수정하는 것도 가능하다. 테이블이 view 라면 CHECK TABLE은 뷰 정의를 검사한다.
2) REPAIR TABLE
:: REPAIR TABLE 구문은 corrupt 된 테이블의 문제를 수정한다. 이것은 오직 MyISAM 테이블에서만 동작한다. MyISAM 테이블을 자동으로 repair 하도록 서버를 설정할 수 있다.
3) ANALYZE TABLE
:: ANALYZE TABLE 구문은 테이블 안에 있는 키 값의 분포도에 대한 정보를 업데이트한다. 이 정보는 옵티마이저가 좀 더 좋은 쿼리 실행계획을 만드는데 사용한다. 이 구문은 MyISAM 과 InnoDB 테이블에서 동작한다.
4) OPTIMIZE TABLE
:: 이 구문은 MyISAM 테이블의 조각모음을 한다. 이 구문은 DELETE나 UPDATE로 인해 사용되지 않은 공간들을 회수하고 분리되어 연속적으로 저장되어 있지 않은 레코드들을 결합하는 작업을 포함한다. 또 OPTIMIZE TABLE은 인덱스 페이지들의 순서가 제대로 되어 있지 않은 경우 인덱스 페이지를 정렬하고 인덱스 통계를 수정한다.
OPIMIZE TABLE은 또한 InnoDB 테이블에 대해서도 작동을 하지만 테이블을 재구성하는 ALTER TABLE에도 mapping 된다. 이 것은 clustered index 내의 인덱스 통계와 빈 공간을 업데이트 한다.
2. Repairing InnoDB TABLES
CHECK TABLE 문장이나 원하는 문장을 실행하는 클라이언트 프로그램을 이용하여 InnoDB를 체크할 수 있다. 그러나 InnoDB 테이블이 문제를 가지고 있다 해도 REPAIR TABLE은 MyISAM에만 적용되는 구문이기 때문에 이를 사용하여 복구할 수 없다.
만약 tablecheck를 통해 InnoDB 테이블이 문제를 가지고 있는 것을 알았다면 MySQLdump로 테이블을 dump하고 이를 drop한 후 다시 dump 파일을 통해 재생성함으로서 일관된 상태로 테이블을 저장할 수 있도록 해야 한다.
shell> MySQLdump db_name table_name > dump_file
shell> MySQL db_name < dump_file
현재 구동되고 있는 호스트나 MySQL 서버의 crash 이벤트 때문에 InnoDB 테이블은 복구를 필요로 할 수도 있다. 일반적으로 InnoDB 스토리지 엔진이 startup 시퀀스의 부분으로 자동 복구를 수행하기 때문에 단순히 서버를 재실행 시키는 것 만으로도 충분하다. 드물기는 하지만 서버가 InnoDB 자동 복구의 실패로 인해 가동되지 않을 수 있다. 이러한 경우가 발생한다면 다음과 같은 절차를 참고한다.
0. --InnoDB_force_recovery
::옵션의 값을 1에서 6사이를 주고 서버를 재가동한다. 이 값들은 crash를 피하기 위한 경고 레벨이 증가함을 나타내며 복구된 테이블에서 나타날 수 있는 불일치성에 대한 가능한 허용 레벨이 증가하는 것을 나타낸다. 이 값은 4부터 시작하는 것이 적당하다.
::서버를 0이 아닌 값을 주고 시작한 경우 InnoDB는 테이블 스페이스를 읽기 전용으로 취급한다. 결과적으로 InnoDB 테이블을 MySQLdump 명령어로 dump하여 옵션이 유효한 동안 이를 drop 해야 한다. 그리고 서버를 옵션을 주지 않은채로 재가동한다. 그 후에 dump 한 파일을 복구 한다.
0. 이전 작업이 실패한다면 InnoDB 테이블을 이전 백업본으로부터 restore 해야 한다.
3. Enabling MyISAM Auto-Repair
MySQL 서버는 자동으로 MyISAM 테이블을 체크하고 repair 하도록 지시할 수 있다. 자동으로 repair 하도록 하면, 서버는 가장 최근에 문제없이 closed 된 테이블인지 보기 위해 그것을 열 때, 각각의 MyISAM 테이블을 체크한다. 테이블이 정상이 아니라면 서버는 테이블을 repair 한다.
자동으로 MyISAM 테이블을 유지보수 하도록 하기 위해서 서버를 --MyISAM-recover 옵션으로 시작해야한다. 이 옵션은 아래에 나와 있는 값을 한 개나 그 이상의 개수로 지정할 수 있으며 콤마로 분리된다.
0. DEFAULT는 디폴트 체크를 한다.
0. BACKUP은 변경된 테이블의 백업을 만들도록 한다.
0. FORCE는 데이터의 하나 이상의 행을 손실시킨다 하더라도 테이블 복구를 수행하도록 한다.
0. QUIK은 빠른 복구를 수행한다. DELETE나 UPDATE로 인한 hole을 가지고 있지 않은 테이블은 skip 된다.
예를 들어 서버가 문제를 가지고 있지만 변화하는 모든 테이브들에 대해 백업 파일을 가지도록 MyISAM 테이블에 대해 강제 복구를 수행하도록 하려면 옵션 파일 내에 다음 줄을 쓸 수 있다.
[mysqld]
MyISAM-recover=FORCE, BACKUP
'Database > MySql' 카테고리의 다른 글
| [MySQL] Data Backup and Recovery Methods (0) | 2008/10/09 |
|---|---|
| [MySQL] INFORMATION_SCHEMA VS SHOW (0) | 2008/10/08 |
| [MySQL] Types of Table Maintenance Operations (0) | 2008/10/08 |
| [MySQL] The FEDERATED Engine (0) | 2008/10/08 |
| [MySQL] The MEMORY Engine (0) | 2008/10/08 |
| [MySQL] The InnoDB Engine 두번째 이야기 (0) | 2008/10/08 |
