Mysql에서 innodb_file_per_table을 사용하는 이유

InnoDB의 작업을 할 때, 테이블 스페이스 스토리지를 관리하기위한 두 가지 방법이있다 :

1. 모든 것들을 하나의 파일로 관리(선택적으로 분할).
2. 테이블 당 하나의 파일 생성.

I will discuss the advantages and disadvantages of the two options, 

두 옵션간의 장점과 단점을 논의하고, innodb_file_per_table가 가장 바람직한 방법이라는 점을 이야기 할 것이다. 

A single tablespace

하나의 큰 파일에 모든 것을 저장한다는 것의 의미는 모든 테이블과 스키마를 비롯한 모든 인덱스들이 하나의 파일이 섞이게 된다는 것이다. 

이러한 방식은 다른 테이블들과 다른 스키마들이 유휴 공간을 자유롭게 공유할 수 있다는 좋은 장점이 있다. 따라서, 만약 내가 로그 테이블로부터 많은 행을 지우면 지금은 사용되지 않는 공간은 다른 테이블의 새로운 행으로 채워질 수 있다. 

이 같은 좋은 속성은 좋지 않은 면도 있다: 데이터가 너무 커질 수 있어서 테이블스페이스에 조각화를 가져올 수 있다. 

InnoDB의 테이블 스페이스의 불편한 점은 절대로 shrink하지 않는다는 것이다. 그래서 log테이블에서 row들을 지운 뒤에, 테이블스페이스(일반적으로 ibdata1)의 물리적 크기는 줄어들지 않는다. 이러한 점은 파일 시스템 스토리지를 풀어주지 않는 것이다. 

특정 테이블들이 확인되지 않은 채로 있다가 디스크를 90%이상 사용할 때까지 크기가 커진 경우가 굉장히 많다. 

이러한 경우 해야할 일이 조금 있다. 항상 row들을 지울 수 있어야 한다. 당연히, 공간은 InnoDB에 의해 재사용 된다. 하지만, 디스크 공간의 8~90%를 소비하는 하나의 파일을 갖는것은 퍼포먼스 소모적이다. 이 의미는 disk needle은 큰 거리를 움직일 필요가 있다는 것이다. 전체적으로 디스크 퍼포먼스는 매우 낮아진다.

이 문제를 해결하기 위한 가장 좋은 방법은 새로운 slave를 설치하고, (rows를 지운 다음 그 공간에) dump data를 slave에 넣는 것이다.The best way to solve this is to setup a new slave (after purging of the rows), and dump the data into that slave.

InnoDB Hot Backup

재미있는 점은 ibbackup utility는 테이블스페이스 파일을 그대로 복사한다는 것이다. 만약 30GB가 사용되고, 전체크기가 120GB인 경우, 120GB 전체가 backup, restore 된다.

mysqldump, mk-parallel-dump

만약 함께 작동할 오리지널 기계가 있다면, mysqldump는 가장 좋은 선택일 것이다. InnoDB를 사용한다고 가정하였을 때, 덤프를 --single-transaction 와 함께 사용할 수 있을 것이다. 아니면 속도를 주기 위해 mk-parallel-dump를 사용할 수 있다.(dump method와 accessibility needs와 mind the locking에 따라) 

innodb_file_per_table

이 파라미터 셋으로, .ibd 파일은 테이블 당 생성되고, 아래와 같은 장점이 있다. 

• 테이브스페이스는 다른 테이블들 사이에 공유할 수 벗고, 다른 스키마도 마찬가지이다. 
• 각 파일은 각자의 테이블 스페이스가 있다. 
• 테이블 스페이스의 크기는 절대 감소하지 않는다. 
• 테이블 스페이스 당 공간을 재사용 할 수 있다.

Monitoring

innodb_file_per_table 의 또 다른 장점은 테이블의 크기를 파일 시스템 레벨로 모니터링 할 수 있다는 것이다. mysql에 접속을 할 필요 없이 SHOW TABLE STATUS 아니면 to query the INFORMATION_SCHEMA 명령어를 사용할 수 있다. 가장 큰 순서로 10개 파일을 확인하고, 사이즈를 모니터링 하는 것이 필요하다. 테이블의 크기가 빠르게 증가하는 것을 볼 수 있을 것이다. 

Backup

마지막으로,  .ibd files을 복사하여 싱글 InnoDB 테이블을 백업하는 것은 아직 가능하지 않다. 하지만 이러한 방식으로 작동되기 바란다. 

BY LEE JI EUN