mysql 인덱스 리빌드(재정렬) 관련 질문 0 2 1,986

by 토마톰 [MySQL] [2019.06.13 17:48:17]


안녕하세요 회원가입하고 첫 질문글 남겨요 잘부탁드립니다.

현재 mysql innodb 스토리지 엔진을 사용한 테이블들이 존재합니다.

그중 집계테이블이 여러개 존재하는데 배치잡을돌며 매번 delete, update, insert가 진행됩니다.

이렇게 장기간 데이터가 쌓여 대략 500만건 row가 존재 한다고 했을때에 인덱스 리빌드를 진행했을시 쿼리에대해 속도측면에서 이점이 있는지 궁금합니다.

 

아래 내용이 맞는지, 그리고 조언 부탁드립니다.

1. innodb일경우 일반적으로 인덱스는 아래 두가지 방법이 있다. OPTIMIZE TABLE tablename ALTER TABLE table_name ENGINE engine_name(동일엔진); (optimize 진행시 alter table 작업을 진행하게됨으로 리빌드 효과가 있음)

2. 인덱스가 존재할경우 alter를 진행할때에 데이터 복사가 늦기 때문에 인덱스를 삭제후 alter 혹은 optimize 진행후 다시 인덱스를 생성한다. 와같은 시나리오로 리빌드를 하는게 맞나요??

3. 리빌드를 진행하는중에는 락이 걸리는걸로 알고잇는데 매시간 수집,집계가 진행된다고하면 리빌드를 어떻게 진행 하는건가요?

4. 리빌드를 주기적으로 배치잡을 통해 돌린다고 했을때에 충분한 이점이 될까요?

 

디비에 문외한이라 전문가들의 조언을 얻어보고자 합니다... 전체적인 정책을 어떻게 정해서 어떤방식으로 최적화 진행을 진행하는지 궁금합니다

감사합니다.

by 르매 [2019.06.13 23:08:38]

1. 맞습니다.

2. 맞습니다. 다만, 5.7 부터는 개선되었다고 합니다.

3. OPTIMIZE TABLE의 경우 5.6.17 이전 버전에서는 online DDL이 아니기 때문에 작업 중 INSERT/UPDATE/DELETE 가 불가합니다. 이후 버전에서는 처음 준비단계와 최종 커밋단계에서 테이블의 메타데이터 변경 때문에 잠시 배타 잠금이 걸리지만 일반적으로 중단 없이 사용할 수 있습니다. 단, 이 경우에도 I/O로 인한 부하 때문에 서버의 전반적인 성능이 떨어집니다.

4. 첫째로 데이터와 인덱스 페이지의 빈 공간을 1/16으로 초기화하여 페이지의 밀도가 높아지기 때문에 같은 I/O에서 더 많은 레코드를 읽을 수 있습니다. 이건 양날의 검이 될 수 있는데 테이블에 대한 비순차적인 키값의 INSERT, 레코드 사이즈가 변하는 UPDATE, DELETE 와 같은 작업이 잦을 경우.. page split 과 merging이 급격히 증가해서 오히려 성능에 악영향을 끼칠 수 있습니다.

둘째로 페이지의 물리적인 위치를 순차적으로 재배치함으로써 페이지를 range scan 하는 쿼리를  실행할 때 랜덤 I/O 가 없어지기 때문에 조회 성능이 좋아집니다. 다만 SSD를 사용한다면 성능 향상이 그리 극적이지는 못합니다. SSD 역시 랜덤 I/O가 시퀀셜 I/O 보다 느린 것은 사실이지만 수치 상으로 그 의미가 크지 않기 때문입니다.

인덱스 리빌드가 꼭 필요한 상황이 있기는 합니다. 하지만 모든 상황 모든 테이블에 대해 일괄적으로 리빌드하는 것은 추천하지 않습니다.

위에 말씀드린 내용을 참고하셔서 좀 더 고민해 보시면 좋을 것 같습니다.


by 토마톰 [2019.06.14 10:10:11]

답변 감사드립니다.

해주신 말씀 잘 고려하도록 하겠습니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입