'mysql'에 해당되는 글 57건

  1. 2008.12.16 MySQL Functions 생성, Stored 루틴 및 트리거 바이너리 로깅
  2. 2008.10.27 MySQL에서 Query Cache 사용하기
  3. 2008.10.20 MySQL InnoDB Table Space 관리하기
2008.12.16 16:32

MySQL Functions 생성, Stored 루틴 및 트리거 바이너리 로깅

먼저 function 을 만들려는 데 다음과 같은 에러가 발생했다.

 

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

 

처리방법

1. mysql 서버를 시작할 때 다음 옵션을 추가 한다.

    --log-bin-trust-function-creators=1

2. 계정에 접속해서 다음을 실행한다.
   mysql>SET GLOBAL log_bin_trust_function_creators = 1;

 

 

스토어드 루틴 및 트리거의 바이너리 로깅

바이너리 로그는 데이터 베이스의 컨텐츠를 수정하는 SQL명령문에 대한 정보를 가지고 있다. 이러한 정보는 수정 사항을 설명하는 “이벤트(Event)”형태로 보관되어 진다. 바이너리 로그에는 두 가지 중요한 목적이 담겨 있다:

리플리케이션의 경우, 마스터 서버는 자신의 바이너리 로그에 포함된 이벤트를 자신의 슬레이브에 전달하는데, 이렇게 함으로서 서버에서 이루어진 데이터의 변경과 동일한 내용을 전달된 이벤트가 실행하도록 하게 한다 . Section 6.2, “리플리케이션 구현 소개” 참조.
특정 데이터 복구 동작은 바이너리 로그의 사용을 요구한다. 백업 파일이 재저장된 후에, 백업이 이루어진 후 기록된 바이너리 로그에 있는 이벤트가 재 실행된다. 이러한 이벤트들은 백업이 이루어지는 시점부터 데이터 베이스를 갱신하게 낸다.  Section 5.10.2.2, “복구를 위한 백업 사용하기” 참조.
이 섹션에서는 MySQL 5.0에 있는 스토어드 루틴(프로시저 및 함수)와 트리거에 관련된 바이너리 로깅의 개발을 설명하도록 하겠다. 첫 번째로는 로깅 실행에서 발생하는 변경 사항에 대해 정리를 할 것이고, 그 다음에는 스토어드 루틴의 사용에서 있게 되는 실행의 현재 조건문(current condition)에 대해 언급하기로 한다. 마지막으로, 언제 그리고 어떻게 다앵한 변경이 만들어 지는지에 대한 정보를 제공하는 실행에 대한 자세한 내용을 설명하겠다. 이러한 상세 내용들은 현재의 로깅 행위에 관련된 몇몇 사항이 이전 버전과는 어떻게 달리 실행되는지를 보여 준다.

일반적으로, 여기에서 설명하는 논제들은 바이너리 로깅이 SQL명령문 레벨에서 생긴다는 사실에서부터 출발한다. 향후의 MySQL버전은 로우-레벨(row-level) 바이너리 로깅을 실행할 예정이며, SQL명령문의 실행으로 인해 각 개별 열(row)을 변경시키는 것을 열거할 것이다.

다른 것들은 고려하지 말고, --log-bin옵션으로 서버를 구동해서 바이너리 로깅을 활성화 시켰다고 가정하자. ( Section 5.12.3, “The Binary Log” 참조.) 만일 바이너리 로그가 활성화 되지 않는다면, 리플리케이션은 불가능하게 되거나, 또는 데이터 복구를 위한 바이너리 로그가 불가능하게 된다.

MySQL 5.0 에서 스토어드 루틴 로깅의 개발은 아래와 같이 요약할 수 있다 :

MySQL 5.0.6 이전 버전 : 스토어드 루틴 로깅의 초기 실행에서, 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되지 않음. 이러한 누락은 리플리케이션과 데이터 복구에 문제를 야기할 수 있다.
MySQL 5.0.6 : 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되어짐.  스토어드 함수 호출은 데이터를 업데이트 하도록 하는 명령문이 실행될 때 로그 되어 진다 (이러한 명령문들이 로그 되어졌기 때문에). 하지만, 비록 함수 자체에서 데이터의 변경이 이루어 진다 하더라도, 데이터를 변경시키지 않는 SELECT와 같은 명령문이 실행될 때에는 로그 되어지지 않는다; 이것은 문제를 일으키게 된다. 어떤 환경에서는, 서로 다른 시간 또는 서로 다른(서버 및 슬레이브)기계에서 함수 및 프로시저가 실행된다면 서로 다른 영향을 받을 수 있기 때문에 데이터 복구 또는 리플리케이션 자체가 불안정할 수 있다. 이런 문제를 처리하기 위해, 안정적인 루틴의 동일성을 제공하고, 충분한 권한을 갖고 있는 사용자에 의한 행위를 제외한, 일반적으로 불안정한 루틴을 방지하기 위한 조치가 실행된다.
MySQL 5.0.12: 스토어드 함수에 대해서는, 데이터를 변경시키는 함수 호출이 SELECT와 같이 로그 되지 않는(non-logged)명령어 내에서 발생할 때, 서버는 그 함수를 호출하는 DO 명령문을 로그 시킴으로써 데이터가 복구되거나 슬레이브 서버에 리플리케이션되는 동안 함수가 실행되도록 한다. 스토어드 프로시저에 대해서는, 서버는 CALL 명령문을 로그 시키지 못한다. 대신에, 서버는 CALL명령문의 결과로 실행되는 프로시저에 포함되어 있는 개별 명령문은 로그 시킨다. 이것은 프로시저가 마스터 서버가 아닌 슬레이브 서버상에서 서로 다른 실행 경로를 따라 실행될 때 발생할 수 있는 문제들을 제거한다.
 MySQL 5.0.16: MySQL 5.0.12에서 제공하는 프로시저 로깅 변경을 통해 불안정한 루틴상의 조건문이 스토어드 프로시저에 대해 안정적으로 동작하도록 해 준다. 따라서, 이러한 조건문을 제어하는 사용자 인터페이스를 함수에 적용 되도록 수정한다. 프로시저 생성자를 더 이상 제한 할 수 없게 되는 것이다.
앞에서 설명한 변경의 결과로, 바이너리 로깅이 활성화될 때에 다음에서 설명하는 조건문을 스토어드 함수에 적용할 수 있게 된다. 이러한 조건문은 스토어드 프로시저 생성에는 적용되지 않는다.
스토어드 함수를 생성 또는 변경하기 위해서는, 일반적으로 CREATE ROUTINE 또는 ALTER ROUTINE 권한을 요구하는 것에 더불어. 반드시 SUPER 권한을 가져야 한다.
스토어드 함수를 생성할 때에는, 그것이 확정적(deterministic)인지 또는 그것이 데이터를 수정하는 않는다는 것을 선언해야 한다. 그렇지 않으면, 그 함수는 데이터 복구 또는 리플리케이션에 대해 덜 안정한 상태가 되어 버린다. 함수의 특성 중에 두 가지가 여기에 적용된다 :
DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해 항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면, 디폴트는NOT DETERMINISTIC 이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는 DETERMINISTIC를 확실하게 지정해 주어야 한다.
NOW() 함수(또는 동일 기능 함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게 만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은 올바르게 포함한다. 또한, RAND()도 함수내에서 일단 한번 호출 되어 지면 정확하게 복사본을 만들게 된다. (함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는 동일한 암시적 입력(implicit input)으로 간주할 수 있다.)
CONTAINS SQL, NO SQL, READS SQL DATA, 및 MODIFIES SQL DATA 특성은 함수가 데이터를 읽거나 또는 쓰는 정보를 제공한다. NO SQL 또는 READS SQL DATA 는 함수가 데이터를 변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이 되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야 한다.
CREATE FUNCTION 명령문이 디폴트로 수용되도록 하기 위해서는, DETERMINISTIC 또는 NO SQL 및 READS SQL DATA 중에 한 개는 반드시 확실하게 표현되어야 한다. 그렇지 않으면 에러가 발생한다:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,

or READS SQL DATA in its declaration and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_function_creators

variable)

함수의 특성에 대한 평가는 생성자의 “honesty”를 근거로 한다:  MySQL은 DETERMINISTIC으로 선언된 함수가 non-deterministic결과를 만드는 명령문을 갖고 있지 않음을 검사하지 않는다.

함수 생성(SUPER권한이 있어야 하며 함수가 deterministic으로 선언되거나 또는 데이터를 수정하지 않아야 함)에서 앞에서 언급한 조건을 피하기 위해서는,  글로벌 시스템 변수log_bin_trust_function_creators 를  1로 설정 해야 한다. 디폴트로는, 이 변수의 값은 0이지만, 아래와 같이 변경할 수 있다:
·                mysql> SET GLOBAL log_bin_trust_function_creators = 1;

또한, 이 변수는 서버를 구동할 때 --log-bin-trust-function-creators 옵션을 사용해서 설정할 수 있다.

만일 바이너리 로깅이 활성화되지 않으면, log_bin_trust_function_creators 는 적용되지 않으며 루틴 생성을 위한 SUPER는 필요 없게 된다.

트리거는 스토어드 함수와 유사하고, 따라서 앞에서 언급한 주의 사항 역시 트리거에도 적용된다. 트리거에 대한 예외적인 사항은 다음과 같다: CREATE TRIGGER 는 옵셔널(optional) DETERMINISTIC 특성을 갖지 않기 때문에, 트리거는 항상 deterministic으로 간주된다. 하지만, 이러한 가정은 어떤 경우에서는 틀릴 수도 있다. 예를 들면, UUID() 함수는non-deterministic (그리고 복사되지 않음)이다. 이러한 함수를 트리거에 사용할 경우에는 주의하여야 한다.

트리거는 테이블을 업데이트할 수 있으며(MySQL 5.0.10 버전 현재), 따라서 SUPER권한이 없고 log_bin_trust_function_creators 의 값이 0 이라면, CREATE TRIGGER 과 함께 나타나는 스토어드 함수에 대한 에러 메시지와 비슷한 에러가 발생하게 된다.

이 섹션의 나머지에서는 스토어드 루틴 로깅 개발에 대해서 상세하게 설명하기로 한다. 상세 설명 중에 몇 가지는 현재 스토어드 루틴 사용에서 로깅 관련 조건문에 대한 이론적인 기본 지식을 제공하여 준다.

MySQL 5.0.6 이전 버전에서 루틴 로깅:  스토어드 루틴을 생성하고 사용하는 명령문이 바이너리 로그되는 것이 아니라, 스토어드 루틴내에서 선언된 명령문이 로그되어 진다. 아래의 명령문을 작성 하였다고 가정하자:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

CALL mysp;

이 예에서 보면, INSERT 명령문만이 바이너리 로그에서 나타난다. CREATE PROCEDURE 와 CALL 명령문은 나타나지 않는다. 바이너리 로그에서 루틴 관련(routine-related) 명령문이 없다는 것은 스토어드 루틴이 올바르게 복사되지 않았다는 것을 의미한다. 이것은 또한 데이터 복구 동작에 대해, 바이너리 로그에 있는 이벤트의 재실행은 스토어드 루틴를 복구시키지 않는다는 것을 의미하기도 한다.

MySQL 5.0.6에서 루틴 로깅 변경:  스토어드 루틴 생성과 CALL 명령문(그리고 관련된 리플리케이션 및 데이터 복구 문제)에 대한 로깅 부재를 연결(address)하기 위해, 스토어드 루틴에 대한 바이너리 로깅의 특성은 여기에서 설명하였듯이 변경되었다. (아래의 리스트중에 몇 가지 항목은 다음 버전에서 다루어지기 때문에 제외한다.)

서버는CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, DROP;;;; PROCEDURE, 및 DROP;;;; FUNCTION 명령문을 바이너리 로그에 쓴다. 또한, 서버는 프로시저내에서 실행되는 명령문이 아닌, CALL 명령문을 로그 한다. 아래의 명령문을 작성하였다고 가정하자:
·                CREATE PROCEDURE mysp INSERT INTO t VALUES(1);

·                CALL mysp;

이 예문에서 보면, CREATE PROCEDURE 및 CALL 명령문은 바이너리 로그에서 나타나지만, INSERT 명령문은 나타나지 않는다. 이것은 MySQL 5.0.6 이전 버전에서 발생했던 INSERT만 로그되는 문제를 해결해 준다.

CALL 명령문 로깅은 리플리케이션에 대한 보안 문제를 갖게 되는데, 두 가지 요소로 인해 이런 문제가 생긴다:
프로시저가 마스터와 슬레이브 서버상에 있는 서로 다른 실행 경로를 따라갈 수 있게 한다.
슬레이브에서 실행되는 명령문은 전체 권한을 갖고 있는 슬레이브 SQL 쓰레드(Thread)에 의해 실행된다.
비록 사용자가 루틴을 생성하기 위해서는 반드시 CREATE ROUTINE권한을 가져야 함을 의미 하지만, 전체 권한을 갖는 SQL 쓰레드가 실행하는 명령문이 있는 슬레이브 위에서만 실행될 위험한 명령문을 작성할 수 있다. 예를 들면, 마스터 와 슬레이브 서버가 서버 ID 1과 2를 갖고 있다면, 마스터 서버의 사용자는 아래와 같이 불안정한 프로시저 unsafe_sp() 를 생성해서 호출할 수 있다:

mysql> delimiter //

mysql> CREATE PROCEDURE unsafe_sp ()

    -> BEGIN

    ->  IF @@server_id=2 THEN DROP;;;; DATABASE accounting; END IF;

    -> END;

    -> //

mysql> delimiter ;

mysql> CALL unsafe_sp();

CREATE PROCEDURE 와 CALL 명령문은 바이너리 로그를 작성할 수 있고, 따라서 슬레이브는 이것을 실행할 수 있다. 슬레이브 SQL 쓰레드는 전체 권한이 있기 때문에, accounting 데이터 베이스를 끝내는(DROP;;;;) DROP;;;; DATABASE 명령문을 실행한다. 따라서, CALL 명령문은 마스터와 슬레이브에서 서로 다른 영향을 받게 되고, 이것은 리플리케이션이 안전하게 이루어 지지 않게 된다.

앞선 예문은 스토어드 프로시저를 사용하고 있으나, 바이너리 로그를 작성하는 명령문 내에서 호출되는 스토어드 함수에 대해서도 비슷한 문제가 발생한다: 함수 호출은 마스터와 슬레이브에 서로 다른 효과를 나타낸다.

바이너리 로깅을 갖는 서버에 대해 이러한 위험을 피하도록 하기 위해, MySQL 5.0.6은 스토어드 프로시저와 함수는 반드시 일반적인 CREATE ROUTINE 권한을 요구하는 것과 아울러 SUPER권한을 갖도록 한다. 비슷하게, ALTER PROCEDURE 또는 ALTER FUNCTION을 사용하기 위해서는, ALTER ROUTINE



Trackback 0 Comment 0
2008.10.27 09:22

MySQL에서 Query Cache 사용하기

Query Cache


 MySQL 서버에서 Query Cache를 사용하고 있으며, SELECT 문을 수행한 결과의 텍스트 내용이 query cache에 저장하게 된다. (mysqld --query_cache_size=#을 지정하여 데몬을 시작한 경우)

앞서 진행했던 것과 동일한 query를 나중에 다시 받게 되면, 서버는 전에 query cache에 저장한 내용에서 클라이언트에 보낸다.

query cache는 테이블 내용이 자주 바뀌지 않거나, 동일한 쿼리를 반복하는 경우에는 아주 유용하다.

Query Cache에서 다음 두 문장은 다르게 처리된다.

        mysql> SELECT * FROM tbl_name;
        mysql> select * from tbl_name;

query cache에서 결과를 받아오려면 두 쿼리가 바이트 단위로 동일해야 하기 때문에 위 두 문장은 다르게 해석된다.

또한 Query가 서로 다른 데이터베이스를 사용한 경우, 서로 다른 프로토콜 버전을 사용한 경우, 서로 다른 디폴트 문자세트를 사용한 경우는 동일하지 않은 쿼리로 처리하여 query cache에서 결과를 받아 오지 않게 된다.

SELECT SQL_CALC_FOUND_ROWSSELECT FOUND_ROWS()와 같은 쿼리에도 이미 query cache에 직전의 정보가 담겨 있기 때문에 query cache로부터 결과를 가져온다.

쿼리 결과를 query cache에서 받아 오는 경우에는 show status 변수인 Com_select는 증가하지 않지만, Qcache_hits는 증가한다.

        mysql> show status like 'Com_select';
        mysql> show status like 'Qcache_hits';

INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE, DROP DATABASE 문에 의해서 테이블이 변경되면, 이들 테이블로부터 사용했던 모든 캐시화된 쿼리는 무용지물이 되므로 query cache의 cache에서 제거된다.
(MRG_MyISAM 형 테이블도 포함됨)

트랜젝션 테이블인 InnoDB형 테이블이 COMMIT에 의해서 변경된 경우도
query cache의 내용은 무용지물이 된다.

다음에 나열된 함수가 포함된 경우도 query cache에 저장되지 않는다.

User-Defined Functions  GET_LOCK        MASTER_POS_WAIT
CURRENT_TIMESTAMP       CURDATE         ENCRYPT
UNIX_TIMESTAMP          CONNECTION_ID   RELEASE_LOCK
NOW                     CURTIME         CURRENT_TIME
LAST_INSERT_ID          USER            FOUND_ROWS
LOAD_FILE               SYSDATE         CURRENT_DATE
DATABASE                RAND            BENCHMARK


또한 사용자 변수가 있는 경우도 query cache에 저장되지 않으며, mysql system 데이터베이스를 참조한 다음과 같은 문장도 query cache에 저장되지 안는다.
  SELECT ... IN SHARE MODE
  SELECT ... INTO OUTFILE ...
  SELECT ... INTO DUMPFILE ...
  SELECT * FROM AUTOINCREMENT_FIELD IS NULL


비록 FOUND_ROWS()를 실행하여 바른 값이 반환되더라도 이는 직전의 쿼리가 query cache에 저장된 값이 출력되는 것이다.

쿼리가 어떤 테이블도 사용하지 않은 경우, 임시 테이블을 사용한 경우, 사용자가 테이블의 컬럼에 권한이 없는 경우도 쿼리 결과가 query cache에 저장되지 않는다.

쿼리 결과를 query cache에서 가져오기 전에 MySQL은 해당 데이터베이스와 테이블에 대한 SELECT 권한을 먼저 체크한다.

query_cache_size의 크기가 '0'이라면, 이는 query cache가 허용되지 않은 경우이다.(디폴트 임)

mysql> show variables like 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

이처럼 query cache가 설정되지 않았다면, mysqld를 시작할 때 옵션으로 다음과 같이 지정한다.

  --query_cache_limit=# (디폴트: 1MB)
  --query_cache_min_res_unit=# (디폴트: 4KB)
  --query_cache_size=# (디폴트: 0)
  --query_cache_type=#


여기서  query_cache_type은 다음과 같이 수치로 지정한다.

Option 설 명
0 OFF로 query cache를 사용하지 않음
1 ON으로 SELECT SQL_NO_CACHE...에 의한 쿼리를 제외하고 query cache를 허용함
2 DEMAND로 SELECT SQL_CACHE...에 의해서만 사용함



QUERY CACHE을 지정하여 시스템 관리자가 데몬을 구동하면 된다.

【예제】query_cache_size를 지정한 구동 스크립트
# cat /etc/rc2.d/S99mysqld
/export/home/mysql/bin/mysqld_safe \
   --user=mysql --query_cache_size=8M --log-bin &

#
mysql> show variables like 'query_cache_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 8388608 |
+------------------+---------+
1 row in set (0.00 sec)

mysql> select * from aa;

mysql> select * from aa;

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 1     |
+---------------+-------+
1 row in set (0.02 sec)

mysql>

다음 명령처럼 실행하면, query cache의 디폴트 값에서 내부적으로 변경할 수 있다.

  QUERY_CACHE_TYPE=OFF | ON | DEMAND
  QUERY_CACHE_TYPE=0   | 1  | 2


여기서 사용되는 옵션의 의미는 다음과 같다.

Option 설 명
0 또는 OFF query cache를 사용하지 않음
1 또는 ON SELECT SQL_NO_CACHE...에 의한 쿼리를 제외하고 query cache를 허용함
2 또는 DEMAND SELECT SQL_CACHE...에 의해서만 사용함



SELECT 문에서 사용되는 Query Cache 옵션은 다음과 같다.

SQL_CACHE QUERY_CACHE_TYPE이 DEMAND이면 query한 내용이 cache됨
QUERY_CACHE_TYPE이 ON이면 디폴트이고,
QUERY-CACHE_TYPE이 OFF이면 query한 내용이 cache 안됨
SQL_NO_CACHE query를 non-cachable로 하여 cache에 저장하지 않음



FLUSH QUERY CACHE 명령으로 query cache의 조각을 재정리하지만
  query cache에 존재하는 어느 query도 제거해 없애지 않는다.

또한 FLUSH TABLES도 query cache를 flush 시킨다.

RESET QUERY CACHE 명령은 query cache에 존재하는 모든 query 내용을 제거한다.

현재의 MySQL 버전에서 서버가 query cache의 지원여부는 have_query_cache 변수로
확인이 가능하다.

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

그리고 SHOW STATUS로 query cache의 퍼포먼스를 알아 볼 수 있는 변수는 다음과 같다.

variable 의 미
Qcache_queries_in_cache cache에 등록된 query의 수
Qcache_inserts cache에 추가된 query의 수
Qcache_hits query cache에서 받아온 횟수
Qcache_lowmem_prunes low memory 때문에 cache에서 삭제된 query 수
Qcache_not_cached QUERY_CACHE_TYPE나 not cachable 때문에 query한 정보가 cache에 저장되지 않는 수
Qcache_free_memory query cache의 여유 메모리량
Qcache_free_blocks query cache의 여유 메모리 블록 수
Qcache_total_blocks query cache에 할당된 총 블록 수


 

Query Cache는  MySQL에서 4.0.1 부터 지원 함

간단히 쿼리 결과에 대해 캐슁을 하여 속도 향상을 시행함

*/etc/my.cnf 적용 예 *

vi /etc/my.cnf

# 쿼리 캐싱 제한 메모리
set variable = query_cache_limit=10M

# 쿼리 캐싱 메모리 사이즈
set variable = query_cache_size=30M

# Query Cache 사용 타입

# 0 : 사용 안함 
# 1 : 모든 쿼리에 대해 캐싱하고
#     select문에서 seelct sql_no_cache를 쓰면 사용 안함

# 2 : 쿼리 캐쉬 선택 함 
#     slq 사용 예 : select sql_cache count(*) from board;

set variable = query_cache_type=1 


Trackback 0 Comment 0
2008.10.20 10:19

MySQL InnoDB Table Space 관리하기

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의 적절한 선택이 필요하다.


Trackback 0 Comment 0