InnoDB의 버퍼2

#Insert buffer

Insert할 때에 변경해야하는 인덱스 페이지가 버퍼풀안에 없으면 인덱스에 넣는 레코드를 insert buffer에 넣어둔다. 

그리고, secondary index와 insert buffer내용을 merge하고 secondary index를 갱신한다. 

secondary index는 유일키가 아닌 경우가 많아서 insert할 때  secondary index변경을 빈번하게 수행한다는 것은 부하를 주기 십상이다. 

그것을 피하기 위한 조치이지만  반대로 검색할 때에는 Insert Buffer도 검색대상이 되는 경우도 있다.

merge는 insert트랜잭션이 commit된 후에 발생한다.   mysql 서버의 shutdown, 재기동시에도 발생한다.   merge할 때에는 디스크 액세스가 증가한다. 

#innodb log buffer

Innodb 트랜잭션로그를 잔류시켜놓는 버퍼이다. 

한개의 mysql서버당 한개의 버퍼가 할당된다.  

commit할 때에 로그 파일에 flush된다. 

innodb_flush_log_at_trx_commit옵션에 따라서 flush하는 간격을 지정가능하다. 

• innodb_log_buffer_size  InnoDB 로그 버퍼사이즈
• innodb_flush_log_at_trx_commit  0:1초간격으로 flush, 1: commit할  때 flush, 2: commit할 때 디스크 저장 그리고 1초간격으로 flush

innodb_flush_log_at_trx_commit는 2>1>0순으로 안전성이 뛰어나다. 

innodb_log_buffer_size의 상한값은 5.1.12-beta에서는 8M이다.(표준 1MB)

#innodb_additional_mem_pool_size

데이터 사전등의 InnoDB내부정보를 보존하는 버퍼이다. 

이 영역이 부족하게 되면 OS로부터 메모리를 확보하려고 한다. 

테이블이 늘어나면 그만큼 이 영역이 필요하게 된다. 

 20M정도 있으면 괜찮다고 한다. 

InnoDB 버퍼(buffer)

○InnoDB buffer pool 

InnoDB의 레코드, 인덱스등을 캐쉬하는 메모리 영역을 말한다. 

한개의 MySQL서버당 한개의 영역이 할당되어 페이지(16KB)단위로 관리된다. 

이 버퍼사이즈는 innodb_buffer_pool_size옵션으로 지정가능하다. 

많이 할당하면 디스크 액세스가 줄어드므로 가능한한 크게 할당한다. 

이 버퍼의  dirty 페이지(갱신된 페이지)가 innodb_max_dirty_pages_pct(%)에 설정된 수치에 도달하면 (표준은 90%), 체크포인트가 동작하기 시작한다.  

또, 버퍼의 dirty 부분이 로그 영역 사이즈(REDO로그 파일참조)에 가까워지면 체크포인트가 동작한다.  체크포인트가 동작하면 테이블스페이스에 쓰여진다. 

만약 데이터가 버퍼에 담어지지 않는다면 LRU(Least Recently Used) 알고리즘을 사용해서 디스크에 쓰여진다. 

InnoDB 파일배치3

[데이터, 인덱스를 테이블 단위로 보존]

mysqld옵션 innodb_file_per_table을 지정하면 데이터하고 인덱스를 각 테이블 단위로 개별 작성된다.  파일은 .frm파일과 같은 디렉토리에 작성된다.

파일명은 다음과 같다. 

  테이블명.ibd

이 한개의  파일안에 데이터와 인덱스 모두 포함된다. 

부수적으로  innodb_file_per_table을 지정했더라도 테이블 스페이스는 필요하다. 

데이터 사전과 UNDO로그는 이 영역을 사용하기 때문이다. 

[REDO로그 파일]

REDO로그 파일은 트랜잭션로그를 말하는 것으로 여러개의 파일로 구성된다. 

파일은 innodb_log_group_home_dir에 지정되는 디렉토리 밑에 작성된다. 

파일은 표준으로는 2개이지만 설정에 따라서 파일 수를 변경하는 것도 가능하다. 

ib_logfile0

ib_logfile1

보존위치, 파일수 , 파일의 사이즈는 다음 옵션으로 지정가능하다. 

innodb_log_group_home_dir  REDO로그파일 보존위치 디렉토리 

innodb_log_files_in_group REDO로그파일 수

innodb_log_file_size REDO로그 파일의 사이즈 

로그 영역사이즈는 다음과 같다. 

innodb_log_files_in_group  X  innodb_log_file_size 

이 사이즈는 체크포인트의 발생에도 관계가 있다.  

트랜잭션 처리가 가능한 정도의 크기가 설정되어져야한다. 

너무 크기가 크면 리커버리(복구)에 시간이 걸리게 됨으로  크게하더라도 Buffer Pool까지 이다. 

로그 파일을 미러링하는 기능은 현재 존재하지 않는다. 

InnoDB 파일배치2

[테이블 스페이스/데이터파일의 지정]

InnoDB는 데이터, 인덱스, 데이터사전, 롤백세그먼트(UNDO LOG)를 테이블스페이스라고 불리우는 파일에 보존한다. 

테이블 스페이스는 1개이상의 파일(또는 raw파일)으로 구성되어 데이터베이스디렉토리내에 ibdata1등의 이름파일로 작성된다. 

InnoDB테이블스페이스의 최대 사이즈는 64T바이트이다. 

기본적으로 mysqld의 옵션, innodb_data_home_dir옵션에 지정되는 디렉토리 밑에 파일이 작성된다. 

그러나 innodb_data_file_path의 지정에 따라서 다른 디렉토리에 작성하는 것도 가능하다. 

이 지정방법은 innodb_data_home_dir에서 상대패스로 지정됨으로 주의해야한다. 

복수의 파일을 사용하는 경우에는 설정파일 my.cnf(또는 my.ini)의 innodb_data_file_path에 지정한다. 

복수의  테이블스페이스를 사용하는 경우

innodb_data_file_path=/disk1/data/datafile:1G;/disk2/data/datafile2:1G

값의 서식은 다음과 같다. 

• 파일명:사이즈[;파일명:사이즈]
• 파일명:사이즈:autoextend
• 파일명:사이즈:autoextend:max:사이즈

파일명뒤에 콜론(:)으로 사이즈(바이트)를 지정한다. (단위로서는 K,M,G가 사용가능하다. )

파일정의를 여러개 할 경우에는 세미콜론(;)으로 지정한다. 

다음과 같이 스페이스를 넣으면 「 data2」처럼 스페이스가 들어간 파일명이 됨으로 조심해야한다. 

 data1:10M; data2:20M

마지막으로 지정된 파일에 대해서만 autoextend라는 키워드를 지정하는 것이 가능하다. 

이것은 영역이 부족하게 되었을 경우 파일을 자동으로 확장하는 것을 의미한다. 

자동확장의 한계치를 지정할 때에는 autoextend뒤에  :max:사이즈 를 붙인다. 

한번 지정한 autoextend키워드를 생략할 때에는  그 시점에서의 파일 사이즈를 지정해 둘 필요가 있다.  사이즈는 1024바이트의 배수이다. 

또, 다음과 같은 것들을 주의해야한다. 

• 한번 작성한 데이터파일의 기술순서를 바꾸어서는 안된다. 
• 테이블 스페이스를 한 번 작성한 후에 데이터파일을 지우는 것은 불가능하다. 
• 테이블 스페이스를 한 번 작성한 후에 기술의 최후에 새로운 데이터 파일을 추가하는 것은 가능하다. 
• 데이터 파일의 명칭과 패스변경은 가능하다. 

InnoDB 파일배치1

[ frm 파일]

    InnoDB 테이블의 정의 파일이다.  MyISAM테이블과 마찬가지로 데이터베이스 디렉토리 밑에 다음과 같은 파일이 생성된다.  

    테이블명.frm

MySQL은 테이블 정의 정보를 데이터베이스 디렉토리 내의 frm파일안에 가지고 있다. 

그러나 InnoDB는 frm파일과는 별도로 테이블관리정보를 테이블스페이스안에 가지고 있다. 

따라서 InnoDB테이블의 frm파일을 수동으로 지우는 것은  부정합을 일으키는 원인이 된다. 

InnoDB는 원래 MySQL AB 밖에서 작성된 엔진으로 2001년에 MySQL 하위층에 추가된 것이다. 

InnoDB내부에서 관리정보가 개별로 존재하고 MySQL 상위층과 연계를 위해서 frm파일이 존재한다는 것에 주의해야한다. 

  

InnoDB - 트랜잭션5

○트랜잭션 분리 레벨의 변경

InnoDB 트랜잭션 분리레벨은 표준으로는 REPEATABLE READ로 되어있다. 

사용 분리레벨을 변경하는 경우에는 다음과 같이 조작한다. 

트랜잭션분리레벨을 READ COMMITTED로 변경

mysql> SET TRANSACTION ISOLATION LEVEL Read Committed;

○MySQL표준의 트랜잭션 분리레벨의 변경

MySQL의 표준 트랜잭션 분리레벨은 REPEATABLE READ이다. 이것을 변경할 때는 가각 다음과 같이 조작한다. 

1. SET문을 이용해서 트랜잭션 분리레벨을 변경한다. 

  SET문에서 tx_isolation변수의 값을 변경함으로써 표준 트랜잭션분리레벨을 변경가능 하다. 

서버 변수를 이용하면 서버를 멈추지 않고 MySQL표준 트랜잭션분리레벨을 변경가능하다. 

SET문으로 변경한다.

mysql>SET GLOBAL tx_isolation="read-committed";

SET문으로 지정한 경우에는 명령을 실행한 후에 접속한 클라이언트에 대해서만 유효하다. 

SET문 실행중에 접속하고 있던 클라이언트(SET을 실행하고 있던 자신포함)에는 영향을 끼치지 않는다. 또 tx_isolation변수에 설정하는 값에는 다음과 같이 하이픈(-)를 적어 넣는 것을 잊어버리지 말기 바란다. 

READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

이 서식은 GLOBAL이외에도 SESSION 지정도 가능하다.  SESSION의 경우는 자신이 사용하는 분리레벨의 설정이 된다. 

2. --transaction-isolation옵션으로 트랜잭션 분리레벨을 변경한다. 

명령어 라인에서 --transaction-isolation옵션을 사용해서 변경하는 경우는 다음과 같이 조작한다. 

기동할 때 옵션을 변경한다. 

mysqld --transaction-isolation=read-committed 

이 경우에는 지정하는 트랜잭션 분리레벨에 하이픈(-)을 넣을 필요가 있다. 

또 이 옵션을 설정파일인 my.cnf 또는 my.ini의 [mysqld] 그룹에 추가하는 것으로 트랜잭션 레벨을 변경할 수 있다. 

설정파일을 추가한다. 

[mysqld]

transaction-isolation=read-committed

InnoDB - 트랜잭션4

○4개의 트랜잭션 분리레벨

여러개의 클라이언트가 동시에 같은 테이블에  대해서 트랜잭션처리를 실행하는 경우는 데이터의 조회, 저장의 동작이 문제가 되곤 한다. 

이것을 ANSI/ISO SQL규격에서 규정한 것이 「트랜잭션 분리레벨」이다. 

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

InnoDB는 이 4개의 트랜잭션 분리레벨을 전부 지원하고 있다. 

그러나 NDB하고 BDB는 READ COMMITTED만을 지원하고 있다. 

MySQL에서는 SQL문에서 사용하는 트랜잭션 분리레벨을 지정가능하다. 

이 4개의 트랜잭션 분리레벨에 대한 데이터의 조회방법의 차이는 다음과 같다. 

	dirty read	non repeatable read	phantom read
READ UNCOMMITTED	o	o	o
READ COMMITTED	x	o	o
REPEATABLE READ	x	x	o
SERIALIZABLE	x	x	x

o:발생을 허가, x:발생하지 않음

1. dirty read

여러 개의 클라이언트가 동시에 테이블에 접속해 있다고 하자. 

이 때 한쪽에서 아직 COMMIT하고 있는 데이터를 다른 한쪽에서 조회가 가능한 상황이 dirty read이다. 

2. non-repeatable read (반복불능 조회)

여러 개의 클라리언트가 동시에 테이블에 접속해 있다고 하자. 

한쪽(A)이 어떤 레코드를 SELECT하고 다른 한쪽(B)가 A가 SELECT한 레코드를 갱신하고 COMMIT한다. 

여기에서 다시 A가 같은 레코드를 SELECT했을 때  값이 B가 변경한 것으로 변해 있는 상황이 non repeatable read이다. 

다시 말하면 지난번 조회내용과 이번 조회내용이 다르다는 것이다. 

3. phantom read

non-repeatable read가 UPDATE에 관한 사항이었던 것에 비해 phantom read는 INSERT에 관한 사항이다. 

한쪽(A)이 어떤 레코드를 SELECT한 후 다른 한쪽(B)가 새로운 레코드를 INSERT하고 COMMIT한다고 하자.  여기에서 다시 A가 같은 조건으로 레코드를 조회할 경우 B가 추가한 새로운 레코드도 조회되는 상황이 phantom read이다. 

지난번 조회내용과 이번 조회내용이 다르다.

InnoDB에서는 REPEATABLE READ대해서 phantom read는 발생하지 않는다. 

또 InnoDB의 SERIALIZABLE는 lock방법을 명시하지 않은 모든 SELECT문을 

SELECT ... LOCK IN SHARE MODE로서 취급한다. 

InnoDB - 트랜잭션3

○auto commit모드를 무효로 하기

auto commit모드를 무효로 하기 위해서는 다음과 같이 조작한다. 

auto commit모드를 무효화하기

mysql> SET AUTOCOMMIT=0;

auto commit모드를 무효화하면 START TRANSACTION의 실행을 잊어버렸어도 자동으로 트랜잭션이 시작된다. 또  COMMIT, ROLLBACK을 실행하면 그 다음 트랜잭션이 자동적으로 개시된다. 

또 다른 auto commit모드를 무효화하는 방법에는 다음처럼 init-connect옵션을 지정하는 방법도 있다. 

init-connect옵션으로 auto commit모드를 무효화하기 

shell$ mysqld --init-connect='SET AUTOCOMMIT=0'

이것은 클라이언트가 서버에 접속할 때마다 최초에 자동적으로 SET AUTOCOMMI=0를 실행하는 옵션이다.  다만 이 기능은 SUPPER권한이 있는 유저로 접속해온 클라이언트에 대해서는 기능하지 않으므로 주의해야 한다. 

InnoDB - 트랜잭션2

◎트랜잭션이 무효인 SQL문

MySQL에서는 모든 SQL문이 트랜잭션 대상이 되지는 않는다. 

다음과 같은 SQL문이 실행되었을 때  만약 트랜잭션모드이었다고 하더라도 자동적으로  commit되어버린다.  주의 해야한다!

• ALTER FUNCTION  ※묵시적 commit은 5.0.13부터..
• ALTER PROCEDURE ※묵시적 commit은 5.0.13부터..
• ALTER TABLE
• BEGIN
• CREATE DATABASE  ※묵시적 commit은 5.0.8부터..
• CREATE FUNCTION  ※묵시적 commit은 5.0.13부터..
• CREATE INDEX
• CREATE PROCEDURE  ※묵시적 commit은 5.0.13부터..
• CREATE TABLE      ※묵시적 commit은 5.0.8부터..
• DROP DATABASE  ※묵시적 commit은 5.0.8부터..
• DROP FUNCTION  ※묵시적 commit은 5.0.13부터..
• DROP INDEX
• DROP PROCEDURE  ※묵시적 commit은 5.0.13부터..
• DROP TABLE
• LOAD MASTER DATA
• LOCK TABLES
• LOAD DATA INFILE
• RENAME TABLE
• SET AUTOCOMMIT=1
• START TRANSACTION
• TRUNCATE TABLE  ※묵시적 commit은 5.0.8부터..
• UNLOCK TABLES

◎스냅샷의 타이밍 

보통 InnoDB의 REPEATABLE READ에서는 SELECT한 시점의 데이터가 스탭샷용으로 보존된다.  그러나 START TRANSACTION WITH CONSISTENT SNAPSHOT의 경우에는 이 문장을 실행한 때의 데이터를 스냅샷용으로 보존한다.

InnoDB - 트랜잭션1

MySQL에서는 모든 스토리지 엔진이 트랜잭션기능을 지원하는 것은 아니다. 

트랜잭션을 지원하는 스토리지 엔진은 InnoDB하고 NDB, BDB이다. 

또  MySQL에는 auto commit 모드라는 개념이 있다. 

auto commit이 유효(MySQL의 표준)인 경우 트랜잭션개시에는 반드시 START TRANSACTION이나 BEGIN이 필요하다. 

이것을 잊어버리면 각 문장을 트랜잭션 처리가 되지 않고 실행할 때 곧바로 Commit되어 버린다. 

덧붙여 BEGIN은 Stored Preocedure에서도 사용하는 키워드이기도 하기 때문에 

이글에서 트랜잭션에 관해서는 START TRANSACTION을 사용하는 것으로 한다. 

START TRANSACTION예

mysql>START TRANSACTION

mysql>UPDATE t SET a=a+1 WHERE a<100;

mysql>INSERT t(a) values(200);

mysql>ROLLBACK;

MySQL에서는 트랜잭션중에  또다시 트랜잭션을 작성하는 것은 불가능하다. 

또 트랜잭션 도중에 COMMIT/ROLLBACK을 잊어버리고 다시 START TRANSACTION을 실행했을 때,  지금까지의 트랜잭션데이터는 데이터에 반영되어 버리기 때문에 주의 해야한다. 

InnoDB 3

●UPDATE/DELETE할 때의 lock

MyISAM에서는 UPDATE/DELETE할 때 테이블에 lock을 건다. 

InnoDB에서는 스캔된 레코드에 대해서 lock이 걸리게 된다. 

InnoDB에서는 SELECT...FOR UPDATE때 와 마찬가지로 UPDATE/DELETE에 있어서도 

스캔된 레코드 전부에 lock이 걸리게 된다. 

인덱스를 가진 컬럼을 WHERE문에 사용해서 스캔되는 행을 한정해야 한다. 

●Next-Key Locking

InnoDB에서는   팬텀리드현상이 갱신할 때 발생하기않게 하기 위해서 Next-key Locking이라는 lock을 도입하고 있다.  예를 들어 하나의 레코드를 갱신한다고 치자. 

mysql＞ SELECT a FROM t WHERE b＜10;

mysql＞UPDATE t SET a=100 WHERE b＜10;

이 때, 다른 쓰레드가 b＜10조건을 만족하는 레코드를 삽입했다고 하자. 

그러면 2개의 레코드가 변경될 가능성이 나오게 된다. 

팬텀(환상, 유령) 레코드가 생길 수 밖에 없다. 

이것을 방지하기 위해서 InnoDB에서는  레코드를 삽입할려고 하는 다른 스레드를 lock대기시킨다.  InnoDB에서는 디폴트로 Next-key Locking이 유효화 되어있다. 

이것을 해제하기 위해서는 MySQL 기동할 때 innodb_locks_unsafe_for_binlog옵션을 설정한다. 

※MyISAM의 INSERT할 때의 lock

MyISAM에서는 레코드를 추출할 때 테이블에 읽기 전용 lock을 건다. 

다만, 테이블 도중에 삭제된 레코드가 없다면 레코드를 추출중에도 레코드 삽입이 가능하다.

(동시 삽입)

InnoDB 2

○ FLUSH TABLES WITH READ LOCK

모든 테이블에 READ lock을 건다. 

UNLOCK TABLES를 실행하면 lock이 해제된다.

MySQL은 이 명령이 실행되었을 때  모든 테이블을  닫는다. 

모든 테이블에 공유lock을 건다. 

mysql> FLUSH TABLES WITH READ LOCK;

○ SELECT ... LOCK IN SHARE MODE

InnoDB에서만 동작한다. 

SELECT로 스킨하는 레코드에 대해서 공유lock을 건다. 

SELECT로 스킨하는 레코드에 공유lock을 걸기

mysql>SELECT * FROM t WHERE a<10>

주의점으로 위 예의 경우에는 a필드에 인덱스를 만들어 놓지 않으면 스킨된 모든 레코드에 lock을 걸게 되는 점이다.  SELECT추출조건에 사용하는 필드에는 인덱스를 만들어놓지 않으면 안된다는 것이다. 

a의 인덱스는 유일키가 아니어도 된다.

○ SELECT ... FOR UPDATE

InnoDB에서만 동작한다. 

대상이 되는 레코드에 대해서 트랜잭션이 끝날 때까지 lock을 건다. 

다른 클라이언트가 갱신을 하려고 하면 그 클라이언트는 lock의 해제를 기다려야 한다. 

5.1.12-beta현재,  lock경합시에 에러를 반환하는 구문(오라클의 SELECT NOWAIT문)은 지원되지 않는다. 

 

대상 레코드를 갱신용으로 lock을 걸기

mysql> SELECT * FROM t WHERE a<10>

SELECT ... LOCK IN SHARE MODE와 같이 a 필드에 인덱스를 만들어 놓지 않으면 조사하는 모든 레코드에 lock이 걸리므로 조심해야한다. 

InnoDB - Lock

MyISAM에서는 테이블단위로만 lock이 되지않지만 InnoDB는 row level lock을 서포트한다. 

InnoDB가 서포트하는 row level lock은 대상이 되는 레코드만 lock을 거는 방법이다. 

row level lock을 잘 동작시키기 위해서는  최소한 InnoDB에 유일키나 프라이머리 키가 필요하다. 

InnoDB 에서 lock을 거는 방법에는 다음과 같다. 

• InnoDB가 자동으로 건다.(갱신 쿼리인 경우 자동적으로 걸린다.)
• 오퍼레이터가 명시적으로 건다.

            SELECT... FOR UPDATE 문                            : 배타lock

            SELECT... LOCK IN SHARE MODE문           : 공유lock

            LOCK TABLES문

            FLUSH TABLES WITH READ LOCK문

한편, MyISAM에서는 다음과 같이 lock을 건다.

• MyISAM이 자동으로 건다.(갱신 쿼리인 경우 자동적으로 걸린다.)
• 오퍼레이터가 명시적으로 건다. 

            LOCK TABLES문

            FLUSH TABLES WITH READ LOCK문

LOCK TABLES/ UNLOCK TABLES

LOCK TABLES문은 트랜잭션에 안전하지는 않다. 

LOCK TABLES는 테이블에 READ LOCK, WRITE LOCK(배타 LOCK)을 건다.

READ에서는 자기가 lock을 건 테이블을 다른 사람은 읽는 것을 가능하지만 자기자신도 다른 사람도 변경하는 것은 불가능하다. 

WRITE에서는 자기자신만이 lock을 건 테이블을 변경가능하다. 

다른 사람은 읽는 것도, 변경하는 것도 불가능하다. 

또,  lock을 걸고 있는 중에는 lock을 건 테이블만 접근가능하다. 

LOCK TABLES을 실행중에 클라이언트가 세션을 끊거나  현재의 스레드가 LOCK TABLES을 발행하면 서버는 lock을 해제한다. 

lock을 해제할 때는 UNLOCK TABLES를 실행한다.  모든 lock이 해제된다. 

LOCK TABLES 테이블명 {READ|WRITE} [,테이블명 {READ|WRITE}]

a테이블에는 공유lock, b테이블에는 배타lock을 거는 법

mysql> LOCK TABLES a READ, b WRITE;