InnoDB 과 MyISAM 의 비교
페이지 정보
작성자 JMStudy 작성일07-11-30 12:31 조회8,567회 댓글0건본문
※ innodb 과 isam 의 비교
innodb :
데이터 파일 : Shared Datafile (인덱스와 데이터 공간이 공유)
innodb_file_per_table 를 설정하면 테이블 단위의 데이터 파일로 분리가 된다.
그러나 테이블 정보는 메인 Shared DataFile에 저장 된다.
따라서 백업에 반드시 메인 SharedDB 파일도 같이 이루어 져야한다.
MyIsam에 비해 약 1.5 ~ 2.5배의 정도 파일이 더 크게 필요로 한다.
데이터 량이 감소하더라도 증가된 데이터 파일 사이즈는 그대로임.
(OPTIMIZE TABLE `테이블` 을 수행 하면 리사이징 됨.)
트랜젝션 : ib_logfileN log파일을 통하여 대량의 트랜젝션을 버퍼링 하고 Seriallization 을 한다.
(로그파일은 최소 2개의 그룹이며, 서로 Rotate 스위칭되며 갱신되 데이터를 파일로
적용 한다. )
속 도 : 인덱스와 데이터 파일이 같은 파일에 있어 속도가 느리다.
대량의 insert, update가 일어 나면 fragment가 발생되어 더느려 진다.
백 업 : 테이블 단위의 hot Backup (파일 복사) 가 불가능하고,
mysqldump나 데이터 베이스 전체적인 복사가 필요하다.
백업에 반드시 메인 SharedDB 파일도 같이 이루어 져야한다.
MyIsam :
데이터 파일 : 인덱스(*.MYI) 와 데이터 파일(*.MYD) 분리
( innodb 보다 파일 크기가 작게 소요)
데이터 량이 감소하더라도 증가된 데이터 파일 사이즈는 그대로임.
OPTIMIZE TABLE `테이블` 을 수행 하면 리사이징 됨.
트랜젝션 : 테이블 락을 기본으로 insert update delete 가 이루어 진다.
속 도 : 인덱스와 데이터 파일이 테이블 단위 이므로 속도가 빠르다.
백 업 : 테이블 단위의 hot Backup (파일 복사) 가 유용하다.
테이블 파일만 있더라도 복구가 가능하다.
* 필자의 견해
일반적으로 데이터 베이스의 생명은 lock 처리 이며, 성능은 디스크 분산에서 부터 결정된다
디스크 분산이 잘되어야 복구부터 로드분산이 가능하고 성능이 향상 되는 것이다.
아직 mysql은 이부분이 미흡하다.
기본 적으로 innodb는 디스크별 스토리지를 배분하기에 아직 Oracle만큼 기대할수 없다.
전반적인 평가는 Oracle을 10점으로 준다면 Mysql은 5 ~ 6점 정도가 적합하다고 본다.
아직 mysql은 속도만이 흡족한 상태이며 전문적인 부분에 있어 아직은 미흡한상태다..
하지만 미래의 데이터 베이스는 Hybrid Database 가 된다고 한다.
지금 mysql NDB Clustering 을 보면 hybrid 에 근접한 부분을 볼수가 있다.
필자는 아직 이부분을 실습 해보지는 않았지만 지금의 일반적인 mysql의 버전 보다
성능이나 기능에 있어 진보적이라고 생각한다.
※ InnoDB 란?
1. MyISAM
1) 예전의 MySQL 의 Storage Engines 으로 MyISAM 을 사용했었습니다.
예를 들자면 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에
최적의 성능을 발휘를 하지요. 지금도 많이 사용하고 있는 방식입니다.
2) 제공하는 웹서비스다 그닥 크지 않다면 이것을 사용해도 괜찮다고 생각을 합니다.
2. InnoDB
1) 트랜잭션-세이프 스토리지 엔진입니다.
2) MyISAM 과 비슷하지만 ORACLE 처럼 많은 기능을 지원을 합니다.
(* commit, rollback, 장애복구, row-level locking, 외래키 등)
3) 다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다.
CPU효율은 어느 디스크 기반의 데이터 베이스와 비교해도 손색이 없고
자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리합니다.
4) 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있습니다.
이것은 MyISAM 과 다른 점인데, MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리합니다.
여기서 중요한것이 이제 InnoDB 를 제대로 사용을 하기 위해서는 테이블 스페이스 라는 개념을 파악을 하셔야합니다.
이것에 대해서는 밑에서 따로 언급을 하겠습니다.
5) InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있습니다.
6) InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.
3. InnoDB 사용하기
1) InnoDB 는 MyISAM 과 공유하는 메모리도 있지만 별도의 Buffer pool을 가지고 있으니까
InnoDB 전용 DB를 구성한다면 MyISAM 이 사용하는 record_boffer 과 key_buffer 에 너무 많은 메모리를 할당하지 마세요
2) InnoDB 설정
ㄱ) MySql 을 설치한 폴더 아래에 ibdata 와 iblogs 폴더를 생성합니다.
ㄴ) my.ini 파일 설정을 변경
* innodb_buffer_pool_size
- 현재 자신의 시스템 메모리의 50~80% 사이로 만듭니다.
x86 시스템에서는 2G 이상 설정을 할 수 없습니다.
* innodb_additional_mem_pool_size
- 데이터 사전정보 나 내부의 데이터 구조 정보를 담는 메모리 입니다.
보통 2M 정도 잡아주면 아주 많은 테이블을 사용한다면 좀 늘려주시면 됩니다.
만약 메모리공장이 부족하면 error log 에 warning 메서지를 남기니 그때 늘려주세요
* innodb_flush_log_at_trx_commit
- insert, update 등 데이터 삽입과 관계가 있습니다.
commit 을 하였을때 그 즉시 commit 된 데이터를 log file 에 기록할지 안할지를 설정합니다.
로그파일을 기록할 경우 갑작스러운 경우 데이터 손실을 막을 수 있지만 매번 로그를 기록하므로 속도가 저하됩니다.
1 일경우 기록을 하는것이고, 0일 경우 기록을 안하는것입니다.
* innodb_log_file_size
- 트랜잭션을 기록하는 로그 파일의 크기를 결정하는 옵션입니다.
inno_buffer_pool_size 옵션은 성능을 위한것이지만 시스템이 다운되었을 경우 데이터가 손실이 되므로
이것을 방지하기 위해 log file 을 만들어서 commit 될때마다 로그에 기억을 하고 자동복구를 합니다.
로그파일은 무한정 계속 커지는 것이 아니라 일정한 크기와 갯수를 가지고 순환식으로 처리되므로 크기는
inno_buffer_pool_size 의 15% 정도로 설정을 합니다.
만약 메모리가 1기가이면 inno_buffer_pool_size = 512M 이고, innodb_log_file_size = 80M 가 됩니다.
* innodb_log_buffer_size
- 로그 파일을 기록하기 위한 버퍼 사이즈입니다.
트랜잭션이 작거나 거의 없다면 크게 잡는것은 낭비이므로 보통 1M~8M 사이로 설정을 합니다.
[mysqld]
innodb_data_home_dir="C:/MySQL/MySQL Server 5.0/ibdata/"
innodb_log_group_home_dir="C:/MySQL/MySQL Server 5.0/iblogs"
innodb_data_file_path=ibdata1:10M:autoextend:max:1000M
innodb_additional_mem_pool_size=3469K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=256M
innodb_log_file_size=40M
innodb_thread_concurrency=8
innodb_log_archive=0
ㄷ) my.ini 을 수정했으면 mysql 서버를 재시작합니다.
3) InnoDB 테이블 만들기
create table test_inno (
~
)type=innodb;
으로 맨 마지막에 type=innodb; 라고 명시해주시면 됩니다.
4) InnoDB 트랜잭션 사용
ㄱ) 트랜잭션을 사용하기 위해서는 처음에 set autocommit=0; 이나 begin; 을 선언해야 합니다.
선언 후 데이터 변경이 있을 때, 이상이 없을 경우는 commit를 하고, 이상이 있을 경우 rollback을 실행합니다.
오라클이랑 비슷하다고 보시면 되요
mysql>set autocommit=0; //begin; 같음
mysql>insert into test_inno values (1,'aaa');
mysql>select * from test_inno; //현재창에서는 입력한 내용이 보이지만 다른창에서는 보이지않음
mysql>commit; //다른창에서 select를 할경우 입력한 값이 보임
4. InnoDB 테이블 스페이스
좀전에 MyISAM 과 차이점이 InnoDB 는 테이블과 인덱스를 테이블 스페이스 라는곳에 저장을 한다고 하였습니다.
그런데 사용을 하다보니 점차 DB가 늘어나서 테이블 스페이스가 FULL 이 발생하는 경우가 생깁니다.
4.0.x 버전부터는 일일이 할 필요가 없다는 글이 있는데 그래도 FULL이 발생하였을 때 대처방법을 알면 좋겠죠
my.ini 에서 아래와 같이 추가를 해주면 안됩니다.
innodb_data_file_path = /ibdata/ibdata1:1000M:autoextend 라고 할 경우
innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend 라고 ibdata2를 추가하면 안됩니다.
아래와 같은 과정대로 하세요
1. Use mysqldump to dump all your InnoDB tables.
2. Stop the server.
3. Remove all the existing tablespace files.
4. Configure a new tablespace.
5. Restart the server.
6. Import the dump files.
즉, 일단 mysqldump 로 InnoDB 테이블의 전체 덤프뜬 다음 MySQL 서버를 중지시킵니다.
그다음 테이블스페이스 파일을 모두 지우고 테이블스페이스를 아래와 같이 추가를 합니다.
innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend
그다음 MySQL 서버를 구동시킨다음 처음에 덤프뜬 파일을 import 하시면 됩니다.
5. 마지막으로
위의 단계처럼 참 어렵게 설정을 하였지만, mysql.com 사이트에 들러보시면 MySQL GUI Tools 라는 프로그램이 있습니다.
물론 무료로 다운로드 가능하고요, 이 프로그램을 설치를 하면 MySQL Administrator 라는 것이 있는데 이것을 통해서
아주 쉽게 MySQL 의 설정을 변경하실 수 있습니다.
innodb :
데이터 파일 : Shared Datafile (인덱스와 데이터 공간이 공유)
innodb_file_per_table 를 설정하면 테이블 단위의 데이터 파일로 분리가 된다.
그러나 테이블 정보는 메인 Shared DataFile에 저장 된다.
따라서 백업에 반드시 메인 SharedDB 파일도 같이 이루어 져야한다.
MyIsam에 비해 약 1.5 ~ 2.5배의 정도 파일이 더 크게 필요로 한다.
데이터 량이 감소하더라도 증가된 데이터 파일 사이즈는 그대로임.
(OPTIMIZE TABLE `테이블` 을 수행 하면 리사이징 됨.)
트랜젝션 : ib_logfileN log파일을 통하여 대량의 트랜젝션을 버퍼링 하고 Seriallization 을 한다.
(로그파일은 최소 2개의 그룹이며, 서로 Rotate 스위칭되며 갱신되 데이터를 파일로
적용 한다. )
속 도 : 인덱스와 데이터 파일이 같은 파일에 있어 속도가 느리다.
대량의 insert, update가 일어 나면 fragment가 발생되어 더느려 진다.
백 업 : 테이블 단위의 hot Backup (파일 복사) 가 불가능하고,
mysqldump나 데이터 베이스 전체적인 복사가 필요하다.
백업에 반드시 메인 SharedDB 파일도 같이 이루어 져야한다.
MyIsam :
데이터 파일 : 인덱스(*.MYI) 와 데이터 파일(*.MYD) 분리
( innodb 보다 파일 크기가 작게 소요)
데이터 량이 감소하더라도 증가된 데이터 파일 사이즈는 그대로임.
OPTIMIZE TABLE `테이블` 을 수행 하면 리사이징 됨.
트랜젝션 : 테이블 락을 기본으로 insert update delete 가 이루어 진다.
속 도 : 인덱스와 데이터 파일이 테이블 단위 이므로 속도가 빠르다.
백 업 : 테이블 단위의 hot Backup (파일 복사) 가 유용하다.
테이블 파일만 있더라도 복구가 가능하다.
* 필자의 견해
일반적으로 데이터 베이스의 생명은 lock 처리 이며, 성능은 디스크 분산에서 부터 결정된다
디스크 분산이 잘되어야 복구부터 로드분산이 가능하고 성능이 향상 되는 것이다.
아직 mysql은 이부분이 미흡하다.
기본 적으로 innodb는 디스크별 스토리지를 배분하기에 아직 Oracle만큼 기대할수 없다.
전반적인 평가는 Oracle을 10점으로 준다면 Mysql은 5 ~ 6점 정도가 적합하다고 본다.
아직 mysql은 속도만이 흡족한 상태이며 전문적인 부분에 있어 아직은 미흡한상태다..
하지만 미래의 데이터 베이스는 Hybrid Database 가 된다고 한다.
지금 mysql NDB Clustering 을 보면 hybrid 에 근접한 부분을 볼수가 있다.
필자는 아직 이부분을 실습 해보지는 않았지만 지금의 일반적인 mysql의 버전 보다
성능이나 기능에 있어 진보적이라고 생각한다.
※ InnoDB 란?
1. MyISAM
1) 예전의 MySQL 의 Storage Engines 으로 MyISAM 을 사용했었습니다.
예를 들자면 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에
최적의 성능을 발휘를 하지요. 지금도 많이 사용하고 있는 방식입니다.
2) 제공하는 웹서비스다 그닥 크지 않다면 이것을 사용해도 괜찮다고 생각을 합니다.
2. InnoDB
1) 트랜잭션-세이프 스토리지 엔진입니다.
2) MyISAM 과 비슷하지만 ORACLE 처럼 많은 기능을 지원을 합니다.
(* commit, rollback, 장애복구, row-level locking, 외래키 등)
3) 다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다.
CPU효율은 어느 디스크 기반의 데이터 베이스와 비교해도 손색이 없고
자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리합니다.
4) 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있습니다.
이것은 MyISAM 과 다른 점인데, MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리합니다.
여기서 중요한것이 이제 InnoDB 를 제대로 사용을 하기 위해서는 테이블 스페이스 라는 개념을 파악을 하셔야합니다.
이것에 대해서는 밑에서 따로 언급을 하겠습니다.
5) InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있습니다.
6) InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.
3. InnoDB 사용하기
1) InnoDB 는 MyISAM 과 공유하는 메모리도 있지만 별도의 Buffer pool을 가지고 있으니까
InnoDB 전용 DB를 구성한다면 MyISAM 이 사용하는 record_boffer 과 key_buffer 에 너무 많은 메모리를 할당하지 마세요
2) InnoDB 설정
ㄱ) MySql 을 설치한 폴더 아래에 ibdata 와 iblogs 폴더를 생성합니다.
ㄴ) my.ini 파일 설정을 변경
* innodb_buffer_pool_size
- 현재 자신의 시스템 메모리의 50~80% 사이로 만듭니다.
x86 시스템에서는 2G 이상 설정을 할 수 없습니다.
* innodb_additional_mem_pool_size
- 데이터 사전정보 나 내부의 데이터 구조 정보를 담는 메모리 입니다.
보통 2M 정도 잡아주면 아주 많은 테이블을 사용한다면 좀 늘려주시면 됩니다.
만약 메모리공장이 부족하면 error log 에 warning 메서지를 남기니 그때 늘려주세요
* innodb_flush_log_at_trx_commit
- insert, update 등 데이터 삽입과 관계가 있습니다.
commit 을 하였을때 그 즉시 commit 된 데이터를 log file 에 기록할지 안할지를 설정합니다.
로그파일을 기록할 경우 갑작스러운 경우 데이터 손실을 막을 수 있지만 매번 로그를 기록하므로 속도가 저하됩니다.
1 일경우 기록을 하는것이고, 0일 경우 기록을 안하는것입니다.
* innodb_log_file_size
- 트랜잭션을 기록하는 로그 파일의 크기를 결정하는 옵션입니다.
inno_buffer_pool_size 옵션은 성능을 위한것이지만 시스템이 다운되었을 경우 데이터가 손실이 되므로
이것을 방지하기 위해 log file 을 만들어서 commit 될때마다 로그에 기억을 하고 자동복구를 합니다.
로그파일은 무한정 계속 커지는 것이 아니라 일정한 크기와 갯수를 가지고 순환식으로 처리되므로 크기는
inno_buffer_pool_size 의 15% 정도로 설정을 합니다.
만약 메모리가 1기가이면 inno_buffer_pool_size = 512M 이고, innodb_log_file_size = 80M 가 됩니다.
* innodb_log_buffer_size
- 로그 파일을 기록하기 위한 버퍼 사이즈입니다.
트랜잭션이 작거나 거의 없다면 크게 잡는것은 낭비이므로 보통 1M~8M 사이로 설정을 합니다.
[mysqld]
innodb_data_home_dir="C:/MySQL/MySQL Server 5.0/ibdata/"
innodb_log_group_home_dir="C:/MySQL/MySQL Server 5.0/iblogs"
innodb_data_file_path=ibdata1:10M:autoextend:max:1000M
innodb_additional_mem_pool_size=3469K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=256M
innodb_log_file_size=40M
innodb_thread_concurrency=8
innodb_log_archive=0
ㄷ) my.ini 을 수정했으면 mysql 서버를 재시작합니다.
3) InnoDB 테이블 만들기
create table test_inno (
~
)type=innodb;
으로 맨 마지막에 type=innodb; 라고 명시해주시면 됩니다.
4) InnoDB 트랜잭션 사용
ㄱ) 트랜잭션을 사용하기 위해서는 처음에 set autocommit=0; 이나 begin; 을 선언해야 합니다.
선언 후 데이터 변경이 있을 때, 이상이 없을 경우는 commit를 하고, 이상이 있을 경우 rollback을 실행합니다.
오라클이랑 비슷하다고 보시면 되요
mysql>set autocommit=0; //begin; 같음
mysql>insert into test_inno values (1,'aaa');
mysql>select * from test_inno; //현재창에서는 입력한 내용이 보이지만 다른창에서는 보이지않음
mysql>commit; //다른창에서 select를 할경우 입력한 값이 보임
4. InnoDB 테이블 스페이스
좀전에 MyISAM 과 차이점이 InnoDB 는 테이블과 인덱스를 테이블 스페이스 라는곳에 저장을 한다고 하였습니다.
그런데 사용을 하다보니 점차 DB가 늘어나서 테이블 스페이스가 FULL 이 발생하는 경우가 생깁니다.
4.0.x 버전부터는 일일이 할 필요가 없다는 글이 있는데 그래도 FULL이 발생하였을 때 대처방법을 알면 좋겠죠
my.ini 에서 아래와 같이 추가를 해주면 안됩니다.
innodb_data_file_path = /ibdata/ibdata1:1000M:autoextend 라고 할 경우
innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend 라고 ibdata2를 추가하면 안됩니다.
아래와 같은 과정대로 하세요
1. Use mysqldump to dump all your InnoDB tables.
2. Stop the server.
3. Remove all the existing tablespace files.
4. Configure a new tablespace.
5. Restart the server.
6. Import the dump files.
즉, 일단 mysqldump 로 InnoDB 테이블의 전체 덤프뜬 다음 MySQL 서버를 중지시킵니다.
그다음 테이블스페이스 파일을 모두 지우고 테이블스페이스를 아래와 같이 추가를 합니다.
innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend
그다음 MySQL 서버를 구동시킨다음 처음에 덤프뜬 파일을 import 하시면 됩니다.
5. 마지막으로
위의 단계처럼 참 어렵게 설정을 하였지만, mysql.com 사이트에 들러보시면 MySQL GUI Tools 라는 프로그램이 있습니다.
물론 무료로 다운로드 가능하고요, 이 프로그램을 설치를 하면 MySQL Administrator 라는 것이 있는데 이것을 통해서
아주 쉽게 MySQL 의 설정을 변경하실 수 있습니다.
댓글목록
등록된 댓글이 없습니다.