OPTIMIZE TABLE
OPTIMIZE TABLE 구문은 두 개의 함수로 이루어져 있다. 각각 테이블의 조각화 현상을 해결하거나 전문인덱스를 업데이트하는데 사용된다.
Defragmenting
OPTIMIZE TABLE 명령은 InnoDB, Aria, MyISAM, ARCHIVE 테이블에서 동작 가능하며 많은 양의 레코드를 삭제하였거나 가변길이 row(VARCHAR나 VARBINARY, BLOB, Text 컬럼등)를 대량 변경하였을 경우 수행해야 한다. (MariaDB 10.1.1 이전에는 innodb_file_per_table 파라미터가 ON 된 경우에만 가능했었음) 삭제된 row는 링크된채로 리스트에 보관되며 insert 동작이 오면 해당 row위치를 재사용한다.
이 구문은 테이블에 대해 SELETE와 INSERT 권한이 필요하다.
기본적으로 OPTIMIZE TABLE 명령은 바이너리 로그에 남고 복제본에 전달되는데 NO_WRITE_TO_BINLOG 키워드를 (혹은 별칭으로 그냥 LOCAL만 쳐도 됨) 사용하면 바이너리 로그를 남기지 않게 된다.
OPTIMIZE TABLE은 또한 파티셔닝 된 테이블에도 지원된다. ALTER TABLE … OPTIMIZE PARTITION 명령을 사용하면 다수의 파티셔닝도 최적화할 수 있다.
Updating an InnoDB fulltext index
전문 인덱스에 row가 추가되거나 삭제되어도 전문인덱스의 재구성 작업은 비용이 크기 때문에 즉시 수행되지 않는다. 변경된 통계정보는 다른 위치에 저장된다. 전문 인덱스는 오직 OPTIMIZE TABLE 명령을 통해서만이 재구성된다.
기본적으로 OPTIMIZE TABLE 명령은 테이블의 조각화 현상을 해결한다. 만약 전문 인덱스의 통계정보를 업데이트하려면 innodb_optimize_fulltext_only 시스템 변수를 1로 셋팅해야 한다. 이것은 반드시 임시적으로만 쓰여야 하며 전문인덱스의 재구성이 완료되면 0으로 돌려놔야 한다. 전문인덱스의 재구성 작업은 시간이 꽤 오래 걸리기 때문에 innodb_ft_num_word_optimize 변수는 재구성하려는 단어의 값을 제한하고 있다. (기본적으로 2000) 전문인덱스 재구성 명령을 동시에 여러 개 수행해도 된다.
Defragmenting InnoDB Tablespaces
mariaDB 10.1.1 에서 페이스북과 카카오의 조각모음 방식 패치를 받아들여 InnoDB 테이블스페이스에 대해서도 조각모음이 가능하게 되었다. 이 기능을 사용하려면 innodb_defragment 변수를 활성화시켜야 한다.
InnoDB 테이블에서 row가 삭제되면 물리적으로 제거하는 대신 delete maker가 새겨놓는다. 그리고 삭제된 영역의 재사용을 위해 OS로 반환하지 않는다. purge 쓰레드가 인덱스의 키와 row를 지우기 위해 수행되는데, 이렇게 지워진 free 여역도 OS에 반환되지 않는다. 이것은 page에 빈 공간을 유발하게 된다. 만약 당신이 가변길이 row를 사용하고 있을 때 새로운 row의 길이가 방금 지운 row의 길이보다 크다면 지운 영역을 재사용할 수 없게 된다.
OPTIMIZE TABLE이나 ALTER TABLE … ENGINE=InnoDB 명령은 테이블을 재구성하도록 해준다. 그런데 불행히도 공통 테이블스페이스 파일인 ibdata1에 대해 최적화를 수행하면 아래와 같은 현상이 일어난다.
- 테이블의 데이터와 인덱스를 연속적으로 만든다.
- 연속적인 데이터와 인덱스 페이지가 ibdata1에 이어써지기 때문에 ibdata1의 크기가 증가한다. (?)
MariaDB 10.1 에서는 페이스북의 Matt, Kakao의 이성욱에 의해 제공된 조각모음 패치를 포함하였다. 페이스북의 코드와 Matt코드의 차이점은 MariaDB는 새로운 literal을 SQL에 포함하지 않았고 서버 코드를 변경하지 않았다는 것이다. 대신 InnoDB/XtraDB 스토리지 엔진 내부에만 코 변경을 하였다.
OPTIMIZE TABLE의 동작 원리는 기본적으로 변하지 않았다. 새로 추가된 이 기능을 사용하려면 innodb_defragment 시스템 변수를 1로 설정해야 한다.
새로운 테이블을 만들어서 데이터를 이전 테이블에서 새로운 테이블로 옮기는 작업이 필요없어졌다. 대신 n개의 새로운 page를 만들어서 record를 새로운 page로 옮기게 된다. 새로운 page가 꽉 차면 기존 record가 있던 page는 빈 영역이 된다. 이와 관련하여 새로 소개된 시스템 변수는 아래와 같다. (이 변수들도 10.1.1부터 추가됨)
System variables
- Innodb_defragment: InnoDB의 조각모음을 활성화한다.
- innodb_defragment_n_pages: 조각모음시 한 타임에 몇 개의 page를 가지고 작업할 것인지를 결정하는 파라미터이다. 기본 값: 7
- innodb_defragment_stats_accuracy: 조각모음 통계의 개수… (?)
- innodb_defragment_fill_factor_n_recs: 조각모음 수행시 추후 사용을 위해 남겨놓을 페이지 여유분으로 record 건수를 기준으로 함. 기본 값: 20
- innodb_defragment_fill_factor: 조각모음 수행시 페이지를 얼마나 채울 지 비율을 결정하는 변수. 기본 값: 0.9
- innodb_defragment_frequency: 단일 인덱스에 대해 조각모음을 수행할 수 있는 초당 횟수의 최대값. 기본 값: 40
Status variables
- innodb_defragment_compression_failures: 조각모음 재압축의 실패 횟수
- innodb_defragment_failures: 조각모음 실패 횟수
- innodb_defragment_count: 조각모음 수행 횟수
ANALYZE TABLE
ANALYZE TABLE은 테이블의 키 분포에 대해 분석하고 저장하는 구문이다. MyISAM, Aria, InnoDB 테이블에서 모두 수행 가능한데, MyISA 엔진에서 수행하면 테이블에 read 락이 걸리며 InnoDB 엔진에서 수행하면 write 락이 걸린다. MyISAM 테이블 같은 경우 myisamchk –analyze 명령을 수행한 것과 동일한 효과를 보인다.
Join 수행시 MariaDB는 키의 분포도를 보고 테이블의 조인 순서를 결정한다. 그리고 키 분포를 보고 특정 테이블에서 어떤 인덱스를 사용할 것인지를 결정하기도 한다. ANALYZE 문을 수행하기 위해선 SELECT와 INSERT 권한이 필요하다. 기본적으로 ANALYZE문은 바이너리 로그에 남고 replication에 전달되기 때문에 만약 바이너리 로그에 남기지 않으려면 NO_WRITE_TO_BINLOG 키워드를 추가하면 된다.
※ MariaDB 10.0.1에서 ANALYZE시 스토리지 엔진과 무관한 통합 통계정보를 수집하도록 되었으며 현재 이러한 통계정보는 풀 테이블 스캔이나 풀 인덱스 스캔시 수집된다. 물론 비용은 꽤 크다.
MariaDB 10.0.1부터 ANALYZE TABLE 명령은 PERSISTENT FOR 키워드를 이용하여 특정 컬럼이나 인덱스를 선택할 수 있도록 되었다. 아래는 사용법에 대한 예제이다. 그리고 BLOB이나 텍스트 컬럼은 통계정보 수집되지 않는다.
ANALYZE TABLE tbl PERSISTENT FOR ALL;
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (); --> 컬럼과 인덱스에 대한 통계는 빼고 테이블의 통계 정보만 수집함.
use_stats_tables 변수가 never로 설정되어 있지 않은 한 단순 ANALYZE TABLE 명령은 통합 통계 테이블에 저장되게 된다.
SET SESSION use_stat_tables='COMPLEMENTARY'; ANALYZE TABLE tbl;
테이블 옵션에 STATS_PERSISTENT=1이 설정되어 있다 하더라도 통합 통계정보를 업데이트하도록 하려면 PERSISTENT FOR 키워드를 써야 한다.
Converting Tables from MyISAM to InnoDB
MyISAM 을 InnoDB로 옮기는 것은 ALTER TABLE … ENGINE=innoDB 명령으로 간단히 바꿀수는 있다. 그러나 스토리지 엔진을 변경하는 것은 미처 알아채기 힘든 여러 이슈들을 포함하고 있다.
인덱스가 어떻게 작동하는지 이해하는 것은 InnoDB에서 무엇이 더 빠르고 무엇이 더 느릴지 이해하는데에 도움을 줄 것이다.
INDEX Issues
모든 InnoDB 테이블은 PK를 갖는다. 만약 별도로 지정하지 않았다면, Not null & Unique 속성을 갖는 컬럼이 PK가 될 것이며, 그것도 없으면 6바이트의 숨겨진 컬럼이 내부적으로 생성된다.
별도로 PK컬럼을 지정하지 않은 테이블을 생각해보자. 비록 인위적인 AUTO_INCREMENT 속성이 포함된다 하더라도 PK를 명시적으로 지정하자. 이것은 강제적인 사항은 아니지만 MyISAM이 아닌 InnoDB를 사용할 강력한 권고사항이다. 아래는 PK에 대해 몇 가지 사실들을 이야기하고 있다.
Fact: 모든 세컨더리 인덱스의 키는 PK를 포함하고 있다.
PRIMARY KEY(id), INDEX(b), -- effectively the same as INDEX(b, id) INDEX(b, id) -- effectively the same as INDEX(b)
Index(b)를 만드나 Index(b, id)를 만드나 동일하기 때문에 하나는 삭제해도 좋다.
PRIMARY KEY(id), UNIQUE(b), -- keep for uniqueness constraint INDEX(b, id) -- DROP this one
이런 경우도 동일한 인덱스이지만 UNIQUE(b)는 b가 Unique하다는 것을 알려주기 때문에 좀 더 많은 정보를 담고 있다. 따라서 INDEX(b,id)를 삭제하는 것이 좋다.
PRIMARY KEY(id), INDEX(id, b) -- DROP this one; it adds almost nothing
이 경우에도 인덱스는 삭제하는 것이 좋다.
Contrast: MyISAM의 특성이 InnoDB에서는 발현되지 않는 케이스도 있다. MyISAM에서 아래 케이스의 경우 ‘abc’의 값마다 별도의 ‘id’의 값을 가지며 1씩 증가하게 된다.
id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (abc, id)
이런 MyISAM의 특성을 흉내내기 위해 아마 당신은 아래와 같이 코딩할 것이다.
INSERT INTO foo
(other, id, ...)
VALUES
(123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);
그러나 테이블을 두 번 언급하였기 때문에 아래와 같이 에러를 반환하게 된다.
ERROR 1093 (HY000): Table ‘foo’ is specified twice, both as a target for ‘INSERT’ and as a separate source for data
따라서 변수를 이용하여 아래와 같이 만들어야 할 것이다.
BEGIN;
SELECT @id := MAX(id)+1 FROM foo WHERE other = 123;
INSERT INTO foo
(other, id, ...)
VALUES
(123, @id, ...);
COMMIT;
다른 쓰레드에서 중복된 id값을 가져가는 것을 방지하기 위해 반드시 트랜잭션을 형성해야 한다.
wwarchblog 