InnoDB 형식의 테이블

InnoDB 형식의 테이블

 트랜젝션 기능을 가진 테이블(InnoDB, BDB) 중의 하나임

 BEGIN/COMMIT/ROLLBACK

 InnoDB 형식의 테이블을 만들 때
    1) CREATE TABLE... TYPE=INNODB 문을 사용하여
    2) MyISAM 형식의 테이블을 InnoDB 형식의 테이블로 변경
       ALTER TABLE  ... TYPE=INNODB 문을 사용
       빈 InnoDB 테이블을 만들어 Insert into ... select * from ...문을 사용

--------------------------------------------------------------------------------

InnoDB에서 트랜젝션의 COMMIT, ROLLBACK을 사용하게 된다.
InnoDB는 row level로 lock할 수 있고, SELECT 문에서 읽을 수 있도록 non-lock할 수도 있다.
 이렇게 함으로써 다중사용자의 동시 사용과 퍼포먼스를 증가시키게 된다.
 이는 InnoDB의 단계적 lock이 요구되지 않기 때문에 InnoDB의 row level lock이 아주 적은 공간에 적합하다.
InnoDB는 FOREIGN KEY 제약을 지원한다.
InnoDB는 대용량의 데이터 처리에서 가장 좋은 퍼포먼스를 위하여 설계되었다.
InnoDB는 데이터와 인덱스를 캐시하기 위한 자체적인 buffer pool로 주 메모리를 사용한다.
InnoDB는 테이블과 인덱스를 여러 파일로 구성되기도 하는 tablespace에 저장한다.
  이는 MyISAM 테이블이 각각의 파일로 저장하는 것과 다르다.
InnoDB는 2GB의 파일크기까지 가능하지만 운영체제에 따라 다를 수 있다.
InnoDB에 관한 정보는 http://www.innodb.com에서 제공하므로 참고하면 좋다.
InnoDB는 MySQL 4.0부터 디폴트로 사용할 수 있도록 허용된다.
 그러므로  InnoDB를 허용하기 위해서 어떤 부분을 지정할 필요가 없다.
MySQL의 datadir에 'ibdata1'라는 10MB의 auto-extending 파일을 디폴트로 만들게 된다
InnoDB를 사용하지 않으려면 MySQL 옵션으로 skip-innodb를 부가하면 된다.
InnoDB가 시작되면 innodb_data_file_path 라인에 auto-extending을 추가되어 다음과 같은 형식이 된다.

   pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
   ;pathtodatafile:sizespecification[:auto-extend[:max:sizespecification]]

이처럼 auto-extend 옵션이 데이터파일에 추가되면, InnoDB는 데이터파일을 확장하여
tablespace의 여유공간을 탕진하게 되는데 한번에 8MB씩 증가되어 다음의 예처럼 된다.

 innodb_data_home_dir=
 innodb_data_file_path=/idbata/ibdata1:100M:autoextend

위 예시는 InnoDB에게 하나의 데이터파일을 100MB크기로 만들며 공간이 소진될 때마다
8MB씩 확장하도록 한 예이다.
점점 확장되어 디스크가 모두 차면 다른 디스크를 데이터 파일로 추가해야 한다.
먼저 'ibdata1'의 크기를 보고서 그 크기가 1MB의 배수에 가장 근접하면
 innodb_data_file_path에 'ibdata1'의 값으로 그 크기를 지정해야 한다.
 그런  다음에 다른 데이터 파일을 다음과 같이 추가할 수 있다:

innodb_data_home_dir=
innodb_data_file_path=/idbata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

파일시스템의 최대 파일크기가 2GB인지 확인해 보라,
 InnoDB는 OS의 최대 파일크기를 인지하지 않는다.
 그런 시스템에서는 다음과 같이 데이터파일의 최대 크기를 지정할 수 있다.

innodb_data_home_dir=
innodb_data_file_path=/idbata/ibdata1:100M:autoextend:max:2000M
       
'my.cnf'의 예
128MB RAM과 하나의 하드디스크로 된 컴퓨터라고 가정한다.
 다음은 InnoDB를 위한 'my.cnf'의(윈도우 버전에서는 'my.ini') 구성 예이다.
 InnoDB와 여러 디스크에 대한 log 파일로서,
   데이터 파일 'ibdata1'과
   두 개의 InnoDB log 파일인 'ib_logfile0'과 'ib_logfile1'가 /mysql/data라는 datadir에 있는 경우이다.
 또한 datadir에 InnoDB log 파일인 ib_arch_log_0000000000가 존재한다.

    [mysqld]
    # Uncomment the following if you are using InnoDB tables
    innodb_data_file_path = ibdata1:10M:autoextend
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    set-variable = innodb_buffer_pool_size=70M
    set-variable = innodb_additional_mem_pool_size=10M
    # Set .._log_file_size to 25 % of buffer pool size
    set-variable = innodb_log_file_size=20M
    set-variable = innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1

mysqld가 구동되면서 읽는 구성 파일은 다음과 같다.
• '/etc/my.cnf' : Global 옵션
• 'COMPILATION_DATADIR/my.cnf' : Server-specific 옵션
• 'defaults-extra-file' : --default-extra-file=...로 지정한 파일
• '~/.my.cnf' : User-specific 옵션

여기서 'COMPILATION_DATADIR' 이란 MySQL의 데이터 디렉토리로 mysqld가 컴파일 할 때
./configure의 옵션으로 지정한 곳으로 source로 인스톨하는 경우에는 '/usr/local/var'이
지만, 본서에서는 /export/home/mysql/var이다..
mysqld --default-file=your_path_to_my_cnf처럼 mysqld가 구동될 때 읽을 my.cnf를 지정
해도 된다.
InnoDB의 데이터 파일에 대한 경로와 디렉토리 지정은 innodb_data_home_dir으로 데이터
파일 디렉토리를 지정하고, innodb_data_file_path로 경로를 지정한다. 만약
innodb_data_home_dir을 지정하지 않을 경우의 디폴트는 MySQL의 datadir을 의미하는 ./ 이 된다.

다음 my.cnf의 예는 2GB RAM과 60GB 디스크의 디렉토리 경로는 '/',  '/dr2', '/dr3'인
경우의 예이다.

    [mysqld]
    # Uncomment the following if you are using InnoDB tables
    innodb_data_home_dir = /export/home/mysql/var/
    innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
    innodb_log_group_home_dir = /export/home/mysql/var/
    innodb_log_arch_dir = /export/home/mysql/var/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    set-variable = innodb_buffer_pool_size=1G
    set-variable = innodb_additional_mem_pool_size=20M
    innodb_log_group_home_dir = /dr3/iblogs
    innodb_log_arch_dir = /dr3/iblogs
    set-variable = innodb_log_files_in_group=3
    # Set .._log_file_size to 25 % of buffer pool size
    set-variable = innodb_log_file_size=150M
    set-variable = innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1
    set-variable = innodb_lock_wait_timeout=50
 
모든 데이터 파일을 동일 디스크에 놓지 않고 서로 다른 디스크에 각각 위치시키는 것 즉,
데이터가 있는 디스크와 다른 디스크에 log 파일을 위치시키는 것이 퍼포먼스상 유리하다.

mysqld의 다른 변수 튜닝
    skip-locking
    set-variable    = max_connections=200
    set-variable    = read_buffer_size=1M
    set-variable    = sort_buffer=1M
    set-variable    = key_buffer=16M
 
my.cnf의 numeric 변수 설정의 형식은 set-variable  = innodb....=123 형식이고, string과
boolean 변수 설정의 형식은 innodb_...=.... 형식이다.
다음은 변수의 의미이다.

innodb_data_home_dir
        InnoDB의 데이터파일의 디렉토리. 'my.cnf'에 지정하지  않으면 MySQL의 datadir
        가 디폴트임, empty string(빈 공간)인 경우에는 innodb_data_file_path의 파일경로
        를 사용

innodb_data_file_path
        각  데이터파일의  경로와   그 크기.   각  데이터파일에   대한  절대   경로로
        innodb_data_home_dir가 사용할 파일의  크기는 MB이며  'M'으로 표시한다.  또한
        'G'는 GB를 의미한다.

innodb_mirrored_log_groups
        복사될 log 그룹의 수로 디폴트는 1임

innodb_log_group_home_dir
        InnoDB log 파일의 디렉토리 경로

innodb_log_files_in_group
        log 그룹에 존재할 log 파일의 수로 3을 추천함

innodb_log_file_size
        log 그룹에 존재할 log 파일의 크기로 MB이다. 1M에서 buffer spool의 1/nth까지의
        범위이며 여기서 n은 그룹내 log 파일의 수이다.

innodb_log_buffer_size
        InnoDB가 log 파일에 기록할 log의 버퍼의 크기로 1M∼8M 범위이다.

innodb_flush_log_at_trx_commit
        디폴트로 1이며, transaction commit에서 log가 디스크에 flush됨
        0이면, log가 log 파일에 기록되며 1초마다 log 파일이 flush됨
        2이면, log가 commit 때마다 log 파일에 기록되며 1초마다 log 파일이 flush됨

innodb_log_arch_dir
        log 파일이 문서화(archive)될 디렉토리로 innodb_log_group_home_dir과 같다.

innodb_log_archive
        디폴트로 0이며,   백업으로부터 복구할  때  log  파일을 사용하므로   현재까지는
        InnoDB log 파일을 archive할 필요가 없다.

innodb_buffer_pool_size
        데이터와 인덱스를 캐시하기 위해 InnoDB가 쓸 메모리 버퍼의 크기로 실제 메모리
        의 80%까지 사용할 수 있다.

innodb_buffer_pool_awe_mem_mb
        buffer   pool의  MB   크기로  32비트   윈도우에서  AWE(address   windowing
        extensions)를 사용하 수 있게 하며 최대값은 64000이다.

innodb_additional_mem_pool_size
        InnoDB가 데이터 dictionary 정보나 다른 내부 데이터 구조를 저장하기  사용할 메
        모리 pool의 크기로 디폴트는 2M이다.

innodb_file_io_threads
        InnoDB에서 파일 I/O 스레드의 수로 보통 4이다.

innodb_lock_wait_timeout
        rollback전에 deadlock까지 기다리는 timeout 시간(초)

innodb_flush_method
        디폴트로 fdatasync이며, 다른 옵션은 0_DSYNC이다.

1) InnoDB tablespace 만들기
2) InnoDB table 만들기
3) InnoDB의 Foreign Key Constraints
4) InnoDB에 대한 Data 파일와 Log 파일의 추가와 제거
5) InnoDB database의 백업과 복구
6) InnoDB database를 다른 운영체제 기계로 이동하기
6) InnoDB 트랜젝션 모델
7) 다중-버전 기능
8) 테이블과 인덱스 구조
9) 파일 공간 관리와 디스크 I/O
10) 오류 처리
11) InnoDB 테이블의 제약조건

--------------------------------------------------------------------------------

【예제】 
mysql> CREATE TABLE customer (a INT, b CHAR(20), INDEX (a)
    -> ) TYPE=INNODB;
mysql> SHOW TABLE STATUS;
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
| Name      | Type   | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |  Create_time        | Update_time         |  Check_time         | Create_options |  Comment               |
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
| customer  | InnoDB | Fixed      |     0 |              0 |       16384 | NULL            |        16384 |         0 |           NULL | NULL                | NULL                | NULL                |                | InnoDB free: 381952kB  |
+-----------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+------------------------+
 
【예제】 
mysql> CREATE TABLE test3 ( a int(11) default NULL) TYPE=MyISAM;
mysql> SHOW TABLE STATUS;
mysql> ALTER TABLE test3 type=innodb;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW TABLE STATUS;

【예제】
mysql> create table test2 ( a int)type=innodb;
mysql> set autocommit=0;
mysql> insert into test2 values(10);
mysql> select * from test2;
+------+
| a    |
+------+
|   10 |
+------+
mysql> commit;
mysql> update test2 set a=20;
mysql> select * from test2;
+------+
| a    |
+------+
|   20 |
+------+
mysql> rollback;
mysql> select * from test2;
+------+
| a    |
+------+
|   10 |
+------+
mysql>
 
 
현재의 autocommit 상태를 확인하는 방법은 다음 예제와 같다.
【예제】
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.03 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.01 sec)

mysql>