Mysql InnoDB 관리하기
로빈아빠
본문
1. What is Innodb Table space ?
- 그 동안 Myisam 방식으로 테이블 스페이스를 사용하던 mysql은 rollback 이 되지 않는다.
다른 데이터베이스 와 달리 autocommit 이 자동으로 실행 하여
데이터 베이스의 트렌젝션시 발생되는 lock을 처리 하기 힘들었다.
- innodb는 오라클 처럼 shared tablespace ( ibdataN) 와 로그파일 (ib_logfileN ) 로 구성된다.
로그파일은 트랜젝션들을 처리하는 오라클의 아카이브 로그 같이 생각 하면 된다.
2. Mysql의 주요 특징
참고 : Mysql Technical Reference fo Ver 5.0 : 16장 InnodB Configureation
- rollback 을 구현 할수 있다.
- transaction isolation Level
: READ-UNCOMMITED, READ-COMMITED, REPEATABLE-READ, SERIALIZABLE
- innodb_buffer_pool_size
: MyIsam 방식과 달리 buffer poll을 이용하여 인덱스와 raw data를 캐싱한다.
따라서 사용량이 많아 지면 tablespace Disk I/O 가 그만큼 줄어 든다
pool의 용량은 real memory의 80% 까지 줄수 있으나, 다른 어플리 케이션과 사용시
너무 많이 주게 되면 운영 체제의 paging을 일으키게 된다.
( 참고로 오라클은 SGA 를 50%로 권고 한다)
또 32bit 시스템의 경우 2 ~ 3.5G의 제한되므로 너무 많이 주면 안된다고 한다.
- innodb_data_file_path
아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.
오직 my.cnf 환경 파일로 생성이 되고 변경 된다.
형식
innodb_data_file_path = [파일위치1/]파일명1:파일1사이즈:autoextend:max:MAX파일 사이즈
파일 간의 구분자는 ";" 이고 파일의 속성 구분자는 ":" 이며
autoextend:max:2000M 는 맨마지막 파일에만 해당된다.
innodb_file_per_table
를 사용하면 이전 isam 같이 데이터 베이스 폴더 안에 table_name.ibd 라는 파일이 생긴다.
사용예는 다음과 같다.
innodb_data_home_dir=/export/DATABASE/MyData
innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:200M:autoextend:max:2000M
3. Innodb 관리
3.1 테이블 rename
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
3.2 데이블 복구
이전 백업 된 데이터 파일을 이용하여 복구 하는방법
가) ALTER TABLE tbl_name DISCARD TABLESPACE;
-> 현재 tbl_name.ibd 를 삭제한다.
나) 백업된 tbl_name.ibd 를 복사해온다.
다) ALTER TABLE tbl_name IMPORT TABLESPACE;
3.3 SHOW INNODB STATUS
3.4 Adding and Removing InnoDB Data and Log Files
>my.cnf<
innodb_data_home_dir=/export/DATABASE/MyData
innodb_data_file_path = ibdata1:500M:ibdata2:500M:ibdata3:500M:ibdata4:200M:autoextend:max:2000M
3.5 Shared Table Space 용량 조정
가) mysqldump로 innodb table을 백업 받는다.
나) 서버를 중지
다) 현재 사용중인 ibdata1 .. ibdataN 을 삭제 한다.
라) 위의 환경 파일을 수정한다.
마) 서버를 재구동 한다.
바) dump 파일을 import 한다.
3.6 로그파일 관리
보통 다음 과 같은 디폴트 값을 사용하며
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
3.2 테이블 스페이스 관리
아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.
오직 my.cnf 환경 파일로 생성이 되고 변경 된다.
대량의 데이터 베이스 의 경우 3 ~ 4G 이상이 되는경우
디폴트로 ibdata1:10M:autoextend 를 쓴다면 곤욕을 치르게 될것이다.
다음과 같이 여러개의 파일을 이용하여 분산 하는 것을 추천 합니다.
파일은 path을 이용 할수 도 있고 raw 디바이스를 이용 할수도 있습니다.
/data1 .. /data4 를 각각 다른 디바이스라고 한다면 아래와 같이 사용하면 된다.
innodb_data_home_dir=
innodb_data_file_path = /data1/ibdata1:500M;/data2/ibdata2:500M;/data3/ibdata3:500M;/data4/ibdata4:200M:autoextend:max:2000M
보통 백업을 위해서 오라클의 경우 500MB ~ 1GB로 데이터 파일 을 생성 하였다.
그리고 200MB는 현재 사용량이 얼마인지 모르기 때문에 200MB -> 300MB가 되면
다시 환경 파일을 수정하고 데이터 파일을 추가 하기 위한 방법이다.
그러나 이것도 문제 가 많다는 것을 독자들도 알것이다.
암튼 아직은 미약한 부분이 많다는 것을 착안 하기 바란다.
데이처 파일을 500MB ~ 1GB로 생성 하는이유는
예전에 OS에서 제한이 있어서 그런 적도 있었지만 , 파일이 크면 그 만큼 I/O분산이 안된다.
그리고 파일당 백업 시간이 증가하거나 문제가 발생 하면 복구도 그만큼 힘들다.
예로 500MB데이터 파일 하나가 사라진 경우와 1GB 데이터 파일 1개가 사라진 경우
그안에 속한 데이터의 량은 곱절이 된다.
즉 재난 을 최소화 하기 위해서라고 1GB이하로 쪼개는 것이 낳기 때문이다.
그렇다고 3GB 되는 것을 100MB로 쪼개면 어떨까 performance에서 문제가 될것이다.
그러므로 DBA의 적절한 선태이 필요하다.
출처 : http://qdata.co.kr/bo/bbs/board.php?bo_table=myt&wr_id=47
- 그 동안 Myisam 방식으로 테이블 스페이스를 사용하던 mysql은 rollback 이 되지 않는다.
다른 데이터베이스 와 달리 autocommit 이 자동으로 실행 하여
데이터 베이스의 트렌젝션시 발생되는 lock을 처리 하기 힘들었다.
- innodb는 오라클 처럼 shared tablespace ( ibdataN) 와 로그파일 (ib_logfileN ) 로 구성된다.
로그파일은 트랜젝션들을 처리하는 오라클의 아카이브 로그 같이 생각 하면 된다.
2. Mysql의 주요 특징
참고 : Mysql Technical Reference fo Ver 5.0 : 16장 InnodB Configureation
- rollback 을 구현 할수 있다.
- transaction isolation Level
: READ-UNCOMMITED, READ-COMMITED, REPEATABLE-READ, SERIALIZABLE
- innodb_buffer_pool_size
: MyIsam 방식과 달리 buffer poll을 이용하여 인덱스와 raw data를 캐싱한다.
따라서 사용량이 많아 지면 tablespace Disk I/O 가 그만큼 줄어 든다
pool의 용량은 real memory의 80% 까지 줄수 있으나, 다른 어플리 케이션과 사용시
너무 많이 주게 되면 운영 체제의 paging을 일으키게 된다.
( 참고로 오라클은 SGA 를 50%로 권고 한다)
또 32bit 시스템의 경우 2 ~ 3.5G의 제한되므로 너무 많이 주면 안된다고 한다.
- innodb_data_file_path
아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.
오직 my.cnf 환경 파일로 생성이 되고 변경 된다.
형식
innodb_data_file_path = [파일위치1/]파일명1:파일1사이즈:autoextend:max:MAX파일 사이즈
파일 간의 구분자는 ";" 이고 파일의 속성 구분자는 ":" 이며
autoextend:max:2000M 는 맨마지막 파일에만 해당된다.
innodb_file_per_table
를 사용하면 이전 isam 같이 데이터 베이스 폴더 안에 table_name.ibd 라는 파일이 생긴다.
사용예는 다음과 같다.
innodb_data_home_dir=/export/DATABASE/MyData
innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:200M:autoextend:max:2000M
3. Innodb 관리
3.1 테이블 rename
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
3.2 데이블 복구
이전 백업 된 데이터 파일을 이용하여 복구 하는방법
가) ALTER TABLE tbl_name DISCARD TABLESPACE;
-> 현재 tbl_name.ibd 를 삭제한다.
나) 백업된 tbl_name.ibd 를 복사해온다.
다) ALTER TABLE tbl_name IMPORT TABLESPACE;
3.3 SHOW INNODB STATUS
3.4 Adding and Removing InnoDB Data and Log Files
>my.cnf<
innodb_data_home_dir=/export/DATABASE/MyData
innodb_data_file_path = ibdata1:500M:ibdata2:500M:ibdata3:500M:ibdata4:200M:autoextend:max:2000M
3.5 Shared Table Space 용량 조정
가) mysqldump로 innodb table을 백업 받는다.
나) 서버를 중지
다) 현재 사용중인 ibdata1 .. ibdataN 을 삭제 한다.
라) 위의 환경 파일을 수정한다.
마) 서버를 재구동 한다.
바) dump 파일을 import 한다.
3.6 로그파일 관리
보통 다음 과 같은 디폴트 값을 사용하며
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
3.2 테이블 스페이스 관리
아직 오라클 같이 Shared Table Space 를 온라인상에서 추가 하기 힘들다.
오직 my.cnf 환경 파일로 생성이 되고 변경 된다.
대량의 데이터 베이스 의 경우 3 ~ 4G 이상이 되는경우
디폴트로 ibdata1:10M:autoextend 를 쓴다면 곤욕을 치르게 될것이다.
다음과 같이 여러개의 파일을 이용하여 분산 하는 것을 추천 합니다.
파일은 path을 이용 할수 도 있고 raw 디바이스를 이용 할수도 있습니다.
/data1 .. /data4 를 각각 다른 디바이스라고 한다면 아래와 같이 사용하면 된다.
innodb_data_home_dir=
innodb_data_file_path = /data1/ibdata1:500M;/data2/ibdata2:500M;/data3/ibdata3:500M;/data4/ibdata4:200M:autoextend:max:2000M
보통 백업을 위해서 오라클의 경우 500MB ~ 1GB로 데이터 파일 을 생성 하였다.
그리고 200MB는 현재 사용량이 얼마인지 모르기 때문에 200MB -> 300MB가 되면
다시 환경 파일을 수정하고 데이터 파일을 추가 하기 위한 방법이다.
그러나 이것도 문제 가 많다는 것을 독자들도 알것이다.
암튼 아직은 미약한 부분이 많다는 것을 착안 하기 바란다.
데이처 파일을 500MB ~ 1GB로 생성 하는이유는
예전에 OS에서 제한이 있어서 그런 적도 있었지만 , 파일이 크면 그 만큼 I/O분산이 안된다.
그리고 파일당 백업 시간이 증가하거나 문제가 발생 하면 복구도 그만큼 힘들다.
예로 500MB데이터 파일 하나가 사라진 경우와 1GB 데이터 파일 1개가 사라진 경우
그안에 속한 데이터의 량은 곱절이 된다.
즉 재난 을 최소화 하기 위해서라고 1GB이하로 쪼개는 것이 낳기 때문이다.
그렇다고 3GB 되는 것을 100MB로 쪼개면 어떨까 performance에서 문제가 될것이다.
그러므로 DBA의 적절한 선태이 필요하다.
출처 : http://qdata.co.kr/bo/bbs/board.php?bo_table=myt&wr_id=47
관련링크
댓글목록
등록된 댓글이 없습니다.