어떤 새로운 Native SQL함수를 추가하려는 경우나 새로운 데이터형을 추가하려는 경우 각각 DDL문과 DML문에 있어서 심볼로 먹힐려면 SQL파서를 개조하지않으면 SQL구문해석할 때에문법에러처리되게 된다
그래서 여기서부터는 SQL파서 개조에 대해서 이야기해 볼 까한다.
보통 SQL에서는 "SELECT ... FROM .. "등의 문법에 따라서 SQL문을 작성해서 서버에 송신하지만 여기에서는 "Hello World"라는 명령어를 새로운 SQL 명령어로서 MySQL에 추가해 볼 것이다.
또, 비교적 간단한 것으로 시스템 변수 (my.cnf에서 지정가능하고 SHOW VARIABLES에서 확인가능)의 추가, 상태변수( SHOW STATUS로 확인가능)의 추가에서도 소개해 볼까한다.
이런 해킹은 현실적으로 그다지 필요하지 않을지도 모르겠지만 MySQL를 업무로 다루는 기술자에게는 "몰라, 해킹않해!" 보다는 "해킹할 수 있는 지식도 있고 내부도 잘 알고 있지만 여러가지 이유로 공식바이너리를 사용해!" 라는 자세가 필요하다고 본다.
2010년 11월 28일 일요일
2010년 10월 18일 월요일
파티셔닝5
서브 파티셔닝
RANGE와 LIST일 때 각 파티션을 더 분할할 수가 있다. 매뉴얼에서는 이것을 서브파티션이라고 부른다.
예1>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
PARTITION BY RANGE(a)
SUBPARTITION BY HASH(a)
(
PARTITION pt0 VALUES LESS THAN ( 1000)
(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION pt1 VALUES LESS THAN MAXVALUE
(
SUBPARTITION s2,
SUBPARTITION s3
)
);
예2>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
PARTITION BY RANGE(a)
SUBPARTITION BY HASH(a)
SUBPARTITIONS 2
(
PARTITION pt0 VALUES LESS THAN (1000),
PARTITION pt1 VALUES LESS THAN MAXVALUE
);
MySQL의 파티셔닝에 대해서 정리해보았다. 꽤 유연하게 파일을 분할할 수 있다는 것을 알 수 있다. 한개의 테이블이 큰 경우에는 파티셔닝의 채용을 검토 해 보자.
2010년 10월 14일 목요일
파티셔닝4
파티셔닝의 방법
MySQL의 파티셔닝방법에는 RANGE, LIST, HASH, KEY의 4가지 방법이 제공된다.
이것은 PARTITION BY로 지정한다.
MySQL의 파티셔닝방법에는 RANGE, LIST, HASH, KEY의 4가지 방법이 제공된다.
이것은 PARTITION BY로 지정한다.
- RANGE: 지정된 컬럼 값에 의해서 파티셔닝을 한다. 컬럼명을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다. 조건의 판정에는 VALUES LESS THAN를 사용한다. MAXVALUE는 최대값을 의미한다.
CREATE TABLE ptrange( a INT)
PARTITION BY RANGE(a) (
PARTITION pt0 VALUES LESS THAN ( 1000 ),
PARTITION pt1 VALUES LESS THAN MAXVALUE );
- LIST: 지정된 값에 의해서 파타셔닝을 한다. 컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다. 조건을 지정할때에는 각 파티션정의에 VALUES IN(값 [값,...])을 사용한다.
CREATE TABLE ptlist( a INT UNSIGNED)
PARTITION BY LIST(a) (
PARTITION pt0 VALUES IN (1,4),
PARTITION pt1 VALUES IN (2,5),
PARTITION pt2 VALUES IN (3,6) );
- HASH: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다. 컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다.
CREATE TABLE pthash(a INT, b INT, c CHAR(10) )
PARTITION BY HASH(a)
PARTITION 2;
- KEY: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다. 0개이상의 컬럼명(함수)를 지정할 수 있다. 값을 지정하지 않은 경우 테이블의 PRIMARY KEY가 자동적으로 채용된다.
CREATE TABLE ptkey(a INT, b INT, c CHAR(10) )
PARTITION BY KEY(a, b, c)
PARTITIONS 2;
CREATE TABLE ptkey( a INT PRIMARY KEY, b INT, c CHAR(10) )
PARTITION BY KEY()
PARTITIONS 2;
2010년 9월 16일 목요일
파티셔닝3
파티셔닝의 제한
5.1.12-beta에서 다음과 같은 제한이 있다.
파티셔닝의 지정
파티셔닝의 지정은 다음과 같이 한다.
CREATE TABLE 테이블명(컬럼정의)
PARTITION BY { RANGE(expr) | LIST(expr) }
[ SUBPARTITION BY { [LINEAR] HASH() | [LINEAR] KEY() }
[ SUBPARTITIONS 서브파티션갯수 ]
]
(파티션정의 [, 파티션정의] ... )
파티션정의:
PARTITION 파티션명
[ VALUES { LESS THAN (expr) | MAXVALUE } ] <--RANGE인 경우
[ VALUES IN (값 리스트) ] <--LIST인 경우
[ 테이블 옵션]
[ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
[ NODEGROUP [=] MySQL Cluster의 node_group_id ]
[(서브 파티션정의 [, 서브파티션정의 ]...) ]
서브파티션정의:
SUBPARTITION 서브파티션명
[ 테이블옵션 ]
[ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
[ NODEGROUP [=] MySQL Cluster의 node_group_id ]
테이블옵션:
보통 CREATE TABLE문에서 지정할 수 있는 ENGINE= 키워드등의 테이블옵션. 파티션정의구문에서는 다음을 사용할 수 있다.
[STORAGE] ENGINE [=] 스토리지엔진명
COMMENT [=] 코멘트
DATA DIRECTORY [=] '.MYD파일의 저장디렉토리'
INDEX DIRECTORY [=] '.MYI파일의 저장디렉토리'
MAX_ROWS [=] '테이블에 들어가는 최대 행수'
MIN_ROWS [=] '테이블에 들어가는 최소 행수'
5.1.12-beta에서 다음과 같은 제한이 있다.
- 파티셔닝된 테이블은 스토리지엔진이 같아야 된다.
- 외부키 제약은 사용할 수 없다.
- FULLTEXT인덱스를 사용할 수 없다.
- GIS컬럼을 사용할 수 없다.
- 임시테이블, MERGE스토리지엔진, CSV스토리지엔진은 파티셔닝할 수 없다.
파티셔닝의 지정
파티셔닝의 지정은 다음과 같이 한다.
CREATE TABLE 테이블명 (컬럼 정의)
PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(expr) }
[ PARTITIONS 파티션갯수]
CREATE TABLE 테이블명(컬럼정의)
PARTITION BY { RANGE(expr) | LIST(expr) }
[ SUBPARTITION BY { [LINEAR] HASH() | [LINEAR] KEY() }
[ SUBPARTITIONS 서브파티션갯수 ]
]
(파티션정의 [, 파티션정의] ... )
파티션정의:
PARTITION 파티션명
[ VALUES { LESS THAN (expr) | MAXVALUE } ] <--RANGE인 경우
[ VALUES IN (값 리스트) ] <--LIST인 경우
[ 테이블 옵션]
[ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
[ NODEGROUP [=] MySQL Cluster의 node_group_id ]
[(서브 파티션정의 [, 서브파티션정의 ]...) ]
서브파티션정의:
SUBPARTITION 서브파티션명
[ 테이블옵션 ]
[ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
[ NODEGROUP [=] MySQL Cluster의 node_group_id ]
테이블옵션:
보통 CREATE TABLE문에서 지정할 수 있는 ENGINE= 키워드등의 테이블옵션. 파티션정의구문에서는 다음을 사용할 수 있다.
[STORAGE] ENGINE [=] 스토리지엔진명
COMMENT [=] 코멘트
DATA DIRECTORY [=] '.MYD파일의 저장디렉토리'
INDEX DIRECTORY [=] '.MYI파일의 저장디렉토리'
MAX_ROWS [=] '테이블에 들어가는 최대 행수'
MIN_ROWS [=] '테이블에 들어가는 최소 행수'
2010년 8월 25일 수요일
파티셔닝2
파티셔닝 개요
파티셔닝에서는 데이터를 여러개의 기록영역에 나누어서 기록하는 것을 의미한다.
MySQL의 파티셔닝은 수평 파티셔닝(horizontal partitioning)이라고 불리우는 방법으로 수행한다.
이것은 어떤 컬럼의 값에 따라서 레코드를 기록하는 장소를 바꾸는 방법이다.
이외에도 수직형 파티셔닝(vertical partitioning)처럼 나누는 방법도 있지만 MySQL은 서포트하고 있지 않다. (현재는 잘 모름.. )
다시말하면 수직형 파티셔닝에서는 "이 컬럼은 이쪽 파티션에 기록하고 저컬럼은 저쪽 파티션에 기록한다" 처럼 컬럼에 착목한 분할이 된다.
파티셔닝기본동작
파티셔닝이 어떻게 동작하는지 사용해보면서 알아보자.
MyISAM테이블 mycom의 작성
mysql> CREATE TABLE mycom( a INT) ENGINE=MyISAM
PARTITION BY RANGE(a) (
PARTITION pt0 VALUES LESS THAN (1000) ENGINE=MyISAM,
PARTITION pt1 VALUES LESS THAN (2000) ENGINE=MyISAM,
PARTITION pt2 VALUES LESS THAN MAXVALUE ENGINE=MyISAM) ;
이 때 데이터베이스의 디렉토리에는 다음과 같은 파일이 작성된다.
데이터베이스 디렉토리의 내용(MyISAM테이블)
mycom#P#pt0.MYD
mycom#P#pt0.MYI
mycom#P#pt1.MYD
mycom#P#pt1.MYI
mycom#P#pt2.MYD
mycom#P#pt2.MYI
mycom.frm
mycom.par
다시 말하면, 어떤 MyISAM 테이블을 파티셔닝하면 다음과 같은 파일이 생성된다.
테이블.frm
테이블.par
테이블#P#파티션이름.MYD(데이터)
테이블#P#파티션이름.MYI(인덱스)
.par파일은 바이너리 파일로 파티셔닝 정보를 기록한다.
.par파일의 내용 (일부)
shell# hexdump -C mycom.par
00000000 08 00 00 00 7e 7d 3a 00 03 00 00 00 09 09 09 00
00000010 0c 00 00 00 70 74 30 00 70 74 31 00 70 74 32 00
00000020
그럼, InnoDB의 경우는 어떻게 될까? 아까전에 만든 mycom테이블을 ALTER TABLE문으로 InnoDB로 변경해 보자.
데이터베이스디렉토리는 다음과 같은 파일이 생성된다.
mycom.frm
mycom.par
InnoDB 형식에 따라서 테이블의 데이터와 인덱스부분은 InnoDB의 테이블 영역으로 이동되었다. 그럼 InnoDB의 --innodb_file_per_table옵션을 사용했을 때에는 파티셔닝은 어떻게 될까? 실행해보면 파일은 다음과 같이 되었다.
mycom#P#pt0.ibd
mycom#P#pt1.ibd
mycom#P#pt2.ibd
mycom.frm
mycom.par
이론 그대로이다.
파티셔닝에서는 데이터를 여러개의 기록영역에 나누어서 기록하는 것을 의미한다.
MySQL의 파티셔닝은 수평 파티셔닝(horizontal partitioning)이라고 불리우는 방법으로 수행한다.
이것은 어떤 컬럼의 값에 따라서 레코드를 기록하는 장소를 바꾸는 방법이다.
이외에도 수직형 파티셔닝(vertical partitioning)처럼 나누는 방법도 있지만 MySQL은 서포트하고 있지 않다. (현재는 잘 모름.. )
다시말하면 수직형 파티셔닝에서는 "이 컬럼은 이쪽 파티션에 기록하고 저컬럼은 저쪽 파티션에 기록한다" 처럼 컬럼에 착목한 분할이 된다.
파티셔닝기본동작
파티셔닝이 어떻게 동작하는지 사용해보면서 알아보자.
MyISAM테이블 mycom의 작성
mysql> CREATE TABLE mycom( a INT) ENGINE=MyISAM
PARTITION BY RANGE(a) (
PARTITION pt0 VALUES LESS THAN (1000) ENGINE=MyISAM,
PARTITION pt1 VALUES LESS THAN (2000) ENGINE=MyISAM,
PARTITION pt2 VALUES LESS THAN MAXVALUE ENGINE=MyISAM) ;
이 때 데이터베이스의 디렉토리에는 다음과 같은 파일이 작성된다.
데이터베이스 디렉토리의 내용(MyISAM테이블)
mycom#P#pt0.MYD
mycom#P#pt0.MYI
mycom#P#pt1.MYD
mycom#P#pt1.MYI
mycom#P#pt2.MYD
mycom#P#pt2.MYI
mycom.frm
mycom.par
다시 말하면, 어떤 MyISAM 테이블을 파티셔닝하면 다음과 같은 파일이 생성된다.
테이블.frm
테이블.par
테이블#P#파티션이름.MYD(데이터)
테이블#P#파티션이름.MYI(인덱스)
.par파일은 바이너리 파일로 파티셔닝 정보를 기록한다.
.par파일의 내용 (일부)
shell# hexdump -C mycom.par
00000000 08 00 00 00 7e 7d 3a 00 03 00 00 00 09 09 09 00
00000010 0c 00 00 00 70 74 30 00 70 74 31 00 70 74 32 00
00000020
그럼, InnoDB의 경우는 어떻게 될까? 아까전에 만든 mycom테이블을 ALTER TABLE문으로 InnoDB로 변경해 보자.
데이터베이스디렉토리는 다음과 같은 파일이 생성된다.
mycom.frm
mycom.par
InnoDB 형식에 따라서 테이블의 데이터와 인덱스부분은 InnoDB의 테이블 영역으로 이동되었다. 그럼 InnoDB의 --innodb_file_per_table옵션을 사용했을 때에는 파티셔닝은 어떻게 될까? 실행해보면 파일은 다음과 같이 되었다.
mycom#P#pt0.ibd
mycom#P#pt1.ibd
mycom#P#pt2.ibd
mycom.frm
mycom.par
이론 그대로이다.
2010년 8월 17일 화요일
파티셔닝1
파티셔닝은 MySQL 5.1에서 추가된 기능이다. 이것은 한개의 테이블을 여러개로 나누어 사용하는 기능으로 I/O분산을 위한 것이다.
I/O를 분산시킨다는 의미로는 MySQL에서 다음과 같은 방법이 제공되었다.
MySQL5.1에서 도입된 파티셔닝기능은 MyISAM과 InnoDB둘다 동작하는 공통의 인터페이스(사용방법)이어서 통일감도 있고, 조작은 SQL문의 실행만으로 완료할 수 있으므로 관리자도 이용자도 편하게 되어졌다.
파티셔닝을 이용하는 것으로 지금까지 보다 편하게 I/O분산배치를 시행할 수 있게 되었다.
다만, 파티셔닝이 지금까지의 수법을 완전히 대체할 수 있느냐라고는 할 수 없다.
예를 들어 innodb_data_file_path등은 DB셋업할 때 고려해야할 사항이다.
또 MERGE테이블에서는 myisampack으로 각각의 테이블을 작게 할 수 있으므로 이미 존재하는 테이블을 분할 할 때는 파이셔닝보다 편리할 때도 있다.
각각 때와 장소에 맞게 적절히 사용할 필요가 있다.
파티셔닝기능이 유효인지 확인하는 방법에는 SHOW VARIABLES로 have_partitioning의 값을 보면 알 수 있다. 이 값이 YES이면 파티셔닝기능이 들어있다라고 할 수 있다.
만약 값이 NO로 되어 있거나 원래 have_partitioning자체가 나타나지 않은 경우 사용하고 있는 mysqld 바이너리에 파티셔닝이 포함되어 있지 않는 것이다.
서버 소스를 configure할 때 --with-partition를 지정해서 컴파일하면 될 것이다.
I/O를 분산시킨다는 의미로는 MySQL에서 다음과 같은 방법이 제공되었다.
- 데이터베이스 디렉토리를 심볼릭 링크로 해서 분산 배치
- InnoDB에서는 테이블스페이스 파일(innodb_data_file_path)를 여러개의 파일로 분할
- InnoDB에서는 테이블단위로 파일을 작성(innodb_file_per_table)
- MyISAM에서는 MERGE 테이블
- MyISAM에서는 RAID테이블( MySQL5.0에서 페지: 옛날 OS에서는 한개의 파일 사이즈가 4G제한이 있었지만 현재는 그런 제한이 없어졌으므로 자연스럽게 없어짐... )
- MyISAM에서 파일단위로 심볼릭 링크를 사용해서 분산배치
MySQL5.1에서 도입된 파티셔닝기능은 MyISAM과 InnoDB둘다 동작하는 공통의 인터페이스(사용방법)이어서 통일감도 있고, 조작은 SQL문의 실행만으로 완료할 수 있으므로 관리자도 이용자도 편하게 되어졌다.
파티셔닝을 이용하는 것으로 지금까지 보다 편하게 I/O분산배치를 시행할 수 있게 되었다.
다만, 파티셔닝이 지금까지의 수법을 완전히 대체할 수 있느냐라고는 할 수 없다.
예를 들어 innodb_data_file_path등은 DB셋업할 때 고려해야할 사항이다.
또 MERGE테이블에서는 myisampack으로 각각의 테이블을 작게 할 수 있으므로 이미 존재하는 테이블을 분할 할 때는 파이셔닝보다 편리할 때도 있다.
각각 때와 장소에 맞게 적절히 사용할 필요가 있다.
파티셔닝기능이 유효인지 확인하는 방법에는 SHOW VARIABLES로 have_partitioning의 값을 보면 알 수 있다. 이 값이 YES이면 파티셔닝기능이 들어있다라고 할 수 있다.
만약 값이 NO로 되어 있거나 원래 have_partitioning자체가 나타나지 않은 경우 사용하고 있는 mysqld 바이너리에 파티셔닝이 포함되어 있지 않는 것이다.
서버 소스를 configure할 때 --with-partition를 지정해서 컴파일하면 될 것이다.
2010년 8월 10일 화요일
Replication : 행 레벨 replication
MySQL 5.1.5-alpha에서 행 레벨 replication이 도입되었다. 구현 아이디어로는 바이너리 로그의 기술을 변경하자라는 것이다.
행 레벨 replication은 종래의 replication하고 비교해서 다음과 같은 잇점이 있다.
shell$ ./configure --without-row-based-replication
행 레벨 replication의 도입(binlog_format)
행 레벨 replication을 도입하려면 binlog_format변수에 바이너리로그 기술방법을 지정할 필요가 있다. mysqld의 옵션처리하려면 --binlog-format이다.
mysqld --binlog-format={ROW|1|STATEMENT|2|MIXED|3}
SET문으로도 변경가능하다.
mysql>SET GLOBAL binlog_format=2;
다음과 같은 경우는 SET문으로 동적으로 포맷을 변경할 수 없다.
다음과 같은경우 replication할 수 없다.
BINLOG '
3w0rRRMBAAAAJgAAACYAAAAAA4AAAAAAAAABHRlc3QAAWEAAQM=
';
값은 base64로 인코딩되어 있다. 이 값은 테이블 핸들러(스토리지엔진에 대한 핸들러)에 전달하는 값이다 ( handler::ha_write_row(), handler::ha_update_row(), handler::ha_delete_row()등)
3또는 MIXED는 MySQL 5.1.8에서 추가되었다. 보통은 STATEMENT와 마찬가지로 동작한다. 다음과 같은 경우는 ROW와 마찬가지로 바이너리로그를 기록한다.
행 레벨 replication은 종래의 replication하고 비교해서 다음과 같은 잇점이 있다.
- UDF나 FOUND_ROWS(), LOAD_FILE(), SYSDATE(), USER(), UUID()를 사용한 경우에도 replication이 가능
- 복잡한 SQL문을 slave가 순서대로 실행처리하는 것보다도 빠를 것이다라고 생각되어짐.
- 로그양이 증가한다.
- 처리가 큰 경우 Rollback된 데이터도 로그되는 경우가 있다.
- 인간이 바이너리로그를 보아도 판별되지 않는다.
- Slave가 어떤 SQL문을 받아 처리하는 지 알 수가 없다.
shell$ ./configure --without-row-based-replication
행 레벨 replication의 도입(binlog_format)
행 레벨 replication을 도입하려면 binlog_format변수에 바이너리로그 기술방법을 지정할 필요가 있다. mysqld의 옵션처리하려면 --binlog-format이다.
mysqld --binlog-format={ROW|1|STATEMENT|2|MIXED|3}
SET문으로도 변경가능하다.
mysql>SET GLOBAL binlog_format=2;
다음과 같은 경우는 SET문으로 동적으로 포맷을 변경할 수 없다.
- 스토어드 함수나 트리거 내부
- NDB가 유효한 경우
- 세션이 ROW베이스로 되어있고 임시 테이블을 사용하고 있을 경우
다음과 같은경우 replication할 수 없다.
- UDF를 사용했을 경우
- 다음과 같은 함수를 사용했을 경우 FOUND_ROWS(), LOAD_FILE(), USER(), UUID()
- SYSDATE() 를 --sysdate-is-now옵션이 없는 상태에서 사용한 경우
BINLOG '
3w0rRRMBAAAAJgAAACYAAAAAA4AAAAAAAAABHRlc3QAAWEAAQM=
';
값은 base64로 인코딩되어 있다. 이 값은 테이블 핸들러(스토리지엔진에 대한 핸들러)에 전달하는 값이다 ( handler::ha_write_row(), handler::ha_update_row(), handler::ha_delete_row()등)
3또는 MIXED는 MySQL 5.1.8에서 추가되었다. 보통은 STATEMENT와 마찬가지로 동작한다. 다음과 같은 경우는 ROW와 마찬가지로 바이너리로그를 기록한다.
- UDF나 UUID()를 사용한 경우
- NDB테이블에 대해서 INSERT, UPDATE, DELETE등의 데이터 조작
- INSERT DELAYED를 실행했을 경우
2010년 7월 28일 수요일
Replication : Slave에서의 설정과 조작 5
- SHOW WARNINGS: Replication에러가 나왔을 경우 에러 메세지를 볼 수 있다.
- SET GLOBAL SQL_SLAVE_SKIP_COUNTER: 지정된 수만큼 이벤트의 실행을 건너뛴다.
- SELECT MASTER_POS_WAIT() : 지정된 바이너리 로그파일의 위치까지의 이벤트를 SQL스레드가 실행할 때까지 대기한다. SQL함수인 것을 주목하자. 다음과 같이 사용한다. SELECT MASTER_POS_WAIT('Master 바이너리로그 파일명', 바이너리로그 파일의 위치 [,타임아웃] ) 타임아웃은 0보다 큰 숫자를 지정한 경우, 지정된 초수를 넘어서 기다리게 되는 경우 에러를 낸다. 지정이 없으면 계속 기다린다. 반환되는 값은 이벤트의 수이다. NULL은 SQL스레드가 움직이고 있지 않던가 인수에러이던지 지정된 파일이 존재하지 않던지 하는 경우이다. -1은 타임아웃이 발생한 상태이다.
- START SLAVE (IO_THREAD|SQL_THREAD): Slave를 시작한다.
- STOP SLAVE (IO_THREAD|SQL_THREAD):Slave를 정지시킨다.
- RESET SLAVE: Slave정보를 삭제한다. master.info, relay-bin.#, relay-bin.index, relay-log.info가 삭제되어 초기상태로 된다.
- LOAD DATA FROM MASTER: Master데이터의 복사. 현재는 비추천기능. mysqldump등의 백업툴을 사용할 것을 추천한다. MyISAM테이블만이 대상으로 그 외의 것은 Net error reading from master라는 에러메세지가 나온다. 또 데이터양이 많은 경우 타임아우이 발생할 가능성이 있다.
- LOAD TABLE 테이블명 FROM MASTER: Master의 테이블 복사. 이것도 현재 비추천기능.
2010년 7월 21일 수요일
Replication : Slave에서의 설정과 조작 4
SHOW SLAVE STATUS의 내용
- Slave_IO_State: I/O스레드의 상황
- Maser_Host: Master의 호스트
- Master_User: 접속 유저
- Master_Port: Master의 포트번호
- Connect_Retry: --master-connect-retry의 값
- Master_Log_File: I/O스레드가 현재 읽어들이고 있는 Master의 바이너리로그
- Read_Master_Log_Pos: I/O스레드가 현재 읽어들이고 있는 Master의 바이너리로그의 위치
- Relay_Log_File: SQL스레드가 현재 읽어들여 실행중인 Relay로그 파일
- Relay_Log_Pos: SQL스레드가 현재 읽어들여 실행중인 Relay로그 파일의 위치
- Relay_Master_Log_File: SQL스레드가 가장 최후에 실행한 이벤ㅌ가 Master의 어느 바이너리로그 파일에 있는가
- Slave_IO_Running: I/O스레드가 동작하고 있는가
- Slave_SQL_Running: SQL스레드가 동작하고 있는가
- Replicate_Do_DB: --replicate-do-db의 지정값
- Replicate_Ignore_DB: --replicate-ignore-db 의 지정값
- Replicate_Do_Table: --replicate-do-table의 지정값
- Replicate_Ignore_Table: --replicate-ignore-table의 지정값
- Replicate_Wild_Do_Table: --replicate-wild-do-table의 지정값
- Replicate_Wild_Ignore_Table: --replicate-wild-ignore-table의 지정값
- Last_Errno: 최후에 발생한 에러 번호
- Last_Error: 최후에 발생한 에러 메세지
- Skip_Counter: SQL_SLAVE_SKIP_COUNTER 지정된 값
- Exec_Master_Log_Pos: SQL스레드가 최후에 실행한 이벤트가 Master의 바이너리로그의 어느 위치가 되는 가
- Relay_Log_Space: Relay로그 사이즈. 모든 Relay로그의 합계
- Until_Condition: START SLAVE의 UNTIL의 지정
- Until_Log_File: START SLAVE의 UNTIL의 지정
- Until_Log_Pos: START SLAVE의 UNTIL의 지정
- Master_SSL_Allowed: SSL통신을 수행하고 있는지
- Master_SSL_CA_File: CA의 CERTIFICATE파일
- Master_SSL_CA_Path: CA의 CERTIFICATE파일을 보존하는 디렉토리
- Master_SSL_Cert: CERTIFICATE 파일
- Master_SSL_Cipher: Cipher
- Master_SSL_Key: 키 파일
- Seconds_Behind_Master: Slave가 어느정도 Master로부터 지연되고 있는가. 0인 경우에는 I/O스레드에 완전히 잘 따라가고 있음. 갱신등으로 늦어지고 있는 경우는 늦어진 추정 초수가 나타남.
2010년 7월 15일 목요일
Replication : Slave에서의 설정과 조작 3
Master정보의 변경(CHANGE MASTER TO)
Master정보등의 변경을 수행한다. 구문은 다음과 같다.
CHANGE MASTER TO 변경지정 [,변경지정]
변경지정 키워드를 모두 지정할 필요는 없고 필요한 것만을 「,」로 지정한다.
이 실행에 따라서 master.info , relay-log.info파일이 변경된다.
변경지정 키워드는 아래와 같다.
Slave상태확인 (SHOW SLAVE STATUS)
Slave의 상태를 확인하기 위해서는 SHOW SLAVE STATUS를 사용한다.
Slave_IO_Running과 Slave_SQL_Running중 둘중 하나가 No이면 Replication은 정지하고 있는 것이다. 장해로 SQL스레드만 정지하고 있으면 Relay_Master_Log_File과 Exec_Master_Log_pos 쌍과 Master_Log_File과 Read_Master_Log_Pos쌍의 값이 달라지게 된다.
Master정보등의 변경을 수행한다. 구문은 다음과 같다.
CHANGE MASTER TO 변경지정 [,변경지정]
변경지정 키워드를 모두 지정할 필요는 없고 필요한 것만을 「,」로 지정한다.
이 실행에 따라서 master.info , relay-log.info파일이 변경된다.
변경지정 키워드는 아래와 같다.
- MASTER_HOST='호스트명'
- MASTER_USER='유저명'
- MASTER_PASSWORD='패스워드'
- MASTER_PORT=포트번호
- MASTER_CONNECT_RETRY=시도횟수
- MASTER_LOG_FILE='Master의 바이너리로그파일명'
- MASTER_LOG_POS=Master의 바이너리로그 파일의 위치
- RELAY_LOG_FILE='relay로그파일명'
- RELAY_LOG_POS=relay로그파일의 위치
- MASTER_SSL= {0|1}
- MASTER_SSL_CA = 'CA의 CERTIFICATE파일명'
- MASTER_SSL_CAPATH='CA의 CERTIFICATE 파일저장 디렉토리'
- MASTER_SSL_CERT ='CERTIFICATE파일명'
- MASTER_SSL_KEY='개인키 파일명'
- MASTER_SSL_CIPHER='Cipher지정'
Slave상태확인 (SHOW SLAVE STATUS)
Slave의 상태를 확인하기 위해서는 SHOW SLAVE STATUS를 사용한다.
Slave_IO_Running과 Slave_SQL_Running중 둘중 하나가 No이면 Replication은 정지하고 있는 것이다. 장해로 SQL스레드만 정지하고 있으면 Relay_Master_Log_File과 Exec_Master_Log_pos 쌍과 Master_Log_File과 Read_Master_Log_Pos쌍의 값이 달라지게 된다.
2010년 7월 13일 화요일
Replication : Slave에서의 설정과 조작 2
Slave에 관한 SQL문
Slave가 작성하는 파일
- GRANT REPLICATION CLIENT ON *.* : SHOW SLAVE STATUS를 실행할 수 있는 권한을 부여
- CHANGE MASTER TO : Master 변경, 바이너리 로그의 읽기 위치 변경수행
- SHOW SLAVE STATUS: Slave 서버의 상황
- SHOW WARNINGS : warning의 확인
- SHOW SLAVE HOSTS: Slave리스트를 출력. Slave서버는 --report-host=옵션의 지정이 필요하다. REPLICATION CLIENT권한이 필요
- SET GLOBAL SQL_SLAVE_SKIP_COUNTER=갯수 : 지정된 갯수만큼만 이벤트 실행을 건너뜀.
- SELECT MASTER_POS_WAIT() : 지정된 바이너리로그의 위치까지 실행되기까지 대기
- START SLAVE: Slave개시
- STOP SLAVE: Slave정지
- RESET SLAVE: Master 정보 삭제
- LOAD DATA FROM MASTER: Master로부터 데이터를 복사
- LOAD TABLE 테이블명 FROM MASTER: Master로부터 테이블을 복사
Slave가 작성하는 파일
- master.info: Replication상태를 유지하기위해서 이용되는 파일. Slave서버가 자동으로 생성한다. 임의로 변경하지 않도록 주의한다. 다음과 같은 내용을 포함하고 있다.
- 이 파일의 행수
- Master_Log_File
- READ_Master_Log_Pos
- Master_Host
- Master_User
- 패스워드
- Master_Port
- Connect_Retry
- Master_SSL_Allowed
- Master_SSL_CA_File
- Master_SSL_CA_Path
- Master_SSL_Cert
- Master_SSL_Cipher
- Master_SSL_Key
- relay-log.info: Slave서버의 relay-log상황을 기록하는 파일로 자동적으로 생성된다. 임의로 편집해서는 안된다. 다음과 같은 내용을 포함하고 있다.
- Relay_Log_File
- Relay_Log_Pos
- Relay_Master_Log_File
- Exec_Master_Log_Pos
- 호스트명-relay-bin.NNNNNN: relay로그파일이다. N은 숫자로 000001에서 순차적으로 증가한다. 999999의 다음은 1000000가 된다. 호스트명-relay-bin의 부분은 --relay-log=옵션으로 변경가능하다. 또, 디폴트로는 datadir/에 작성되지만 장소는 --relay-log=옵션으로 변경가능하다.
- 호스트명-relay-bin.index:현재 가지고 있는 relay로그 리스트이다.
2010년 7월 7일 수요일
Replication : Slave에서의 설정과 조작 1
Slave로 동작시키기 위해서는 최소한 다음의 옵션을 my.cnf에 기술해야한다.
server-id = 번호
master-host = 호스트
master-user = 유저명
master-password = 패스워드
Slave에 관한 옵션은 다음과 같다.
server-id = 번호
master-host = 호스트
master-user = 유저명
master-password = 패스워드
Slave에 관한 옵션은 다음과 같다.
- log-slave-updates :Slave겸 Master로 할 때에 지정한다.
- master-connect-retry=기본값은60sec Master와의 접속이 끊긴후 몇초 기다리고 재접속을 시도할까
- master-host=호스트 : Master의 호스트명
- master-port=포트번호 : Master의 포트번호
- master-user=유저명: Master에 접속할 때 사용하는 유저명
- master-password=패스워드: Master에 접속할 때 사용하는 패스워드
- master-info-file=파일명 : master.info파일의 파일명(패스)지정
- master-retry-count= 횟수: Master와의 통신이 끊겼을 경우 최대로 몇번 재접속을 시도할 것인가
- master-ssl : Master와의 통신을 SSL로 수행
- master-ssl-ca=파일: CA의 CERTIFICATE파일
- master-ssl-capath=디렉토리명 : CA의 CERTIFICATE파일을 보존하고 있는 디렉토리
- master-ssl-cert=파일명: CERTIFICATE파일의 지정
- master-ssl-cipher=Cipher : Cipher의 지정
- master-ssl-key=파일명 : private key 파일명
- relay-log=파일명 : relay로그 파일인 호스트명-relay-bin의 지정
- relay-log-index=파일명: 호스명-relay-bin.index파일의 지정
- relay-log-info-file=파일명: relay-log.info파일의 지정
- replicate-do-db=데이터베이스명 : Replication대상이 되는 데이터베이스명
- replicate-do-table=테이블명: Replication대상이 되는 테이블명
- replicate-ignore-db=데이터베이스명: Replication 대상외로 하고픈 데이터베이스명
- replicate-ignore-table=테이블명: Replication 대상외로 하고픈 테이블명
- replicate-rewrite-db="원래 데이터베이스명->Slave에서의 데이터베이스명" : Master의 데이터베이스명을 Slave에서는 다른이름으로 취급
- replicate-wild-do-table=와일드 카드 : Replication대상이 되는 테이블명을 와일드카드로 지정
- replicate-wild-ignore-table=와일드 카드: Replication대상외로 하고픈 테이블명을 와일드 카드로 지정
- report-host=호스트명 : SHOW SLAVE HOSTS에서 표시되는 이름을 지정
- report-user=유저명: SHOW SLAVE HOSTS에 표시되는 유저명을 지정. Master에서는 --show-slave-auth-info옵션을 지정하고 있어야함.
- report-password=패스워드: SHOW SLAVE HOSTS에 표시되는 패스워드를 지정. Master에서는 --show-slave-auth-info옵션을 지정하고 있어야함.
- report-port=포트명: SHOW SLAVE HOSTS에서 표시되는 포트번호를 지정
- skip-slave-start: mysqld기동시에 Slave를 기동하지 않음.
- slave-load-tmpdir="디렉토리 [:디렉토리]":Slave에서 임시 파일을 작성하는 디렉토리를 지정. 「:」로 복수지정가능
- slave-skip-errors="에러번호[,에러번호]" : 지정된 에러가 발생하더라도 Slave처리를 정지시키지 않음. 「,」로 복수지정
- net_retry_count=횟수 : 읽어들이기가 중단된 경우, 몇번 다시 시도할 것인가 지정
- net_buffer_length=바이트수: 통신에 사용하는 버퍼
- net_read_timeout=초수: 읽어들이기가 중단된 경우, 몇초 기다려서 에러를 낼 것인가 지정
- net_write_timeout=초수: 써넣기가 중단된 경우, 몇초를 기다려서 에러를 낼 것인가 지정
- relay_log_purge : 필요없는 relay로그를 제거
- relay_log_space_limit=바이트수: relay로그의 합계 사이즈가 이 값을 넘으면 SQL스레드가 처리를 해서 공간이 생길때까지 I/O스레드는 처리를 정지
- slave_compressed_protocol: 통신내용을 zlib압축
- slave_net_timeout=초수: 데이터통신중 이 초수 이상으로 Master로부터 응답이 없으면 Slave는 접속을 끊고 재접속을 시도
- slave_transaction_retries=횟수 : SQL스레드가 트랜잭션 실패했을 경우 바로 에러로 하지 않고 몇번 재시도할 것인지 지정.
2010년 7월 6일 화요일
Replication : Master에서의 설정과 조작 4
바이너리 로그갱신의 일시적인 ON/OFF ( SET SQL_LOG_BIN)
바이너리 로그의 갱신을 일시적으로 ON/OFF하는 경우는 SET SQL_LOG_BIN을 실행한다.
보통 Replication 대상외로 하고 픈 테이블(권한 테이블)등을 갱신할 때만 사용한다.
구문은 다음과 같다.
SET SQL_LOG_BIN= {0|1}
「1」은 이 세션의 갱신을 바이너리로그에 기록한다. 기본값이다. 「0」은 이 세션의 갱신을 바이너리로그에 기록하지 않는다.
Master이 상태확인(SHOW MASTER STATUS)
현재 써넣고 있는 바이너리로그와 그 위치를 확인하는 경우는 SHOW MASTER STATUS를 실행한다.
바이너리 로그의 목록을 표시(SHOW BINARY LOGS)
현재 유지하고 있는 바이너리 로그를 목록표시하기 위해서는 SHOW BINARY LOGS를 사용한다.
구문은 다음과 같다.
SHOW [MASTER|BINARY] LOGS
바이너리로그내의 이벤트를 표시 ( SHOW BINLOG EVENTS )
지정된 바이너리 로그파일의 이벤트를 나타낸다. 구문은 다음과 같다.
SHOW BINLOG EVENTS
[IN '바이너리로그파일'] [FROM 위치1] [LIMIT [오프셋,] 갯수]
바이너리 로그파일명을 지정하지 않는 경우는 최초의 바이너리 로그를 읽어들인다.
LIMIT으로 이벤트의 읽어들일 위치(오프셋)과 표시할 이벤트의 갯수를 지정할 수 있다.
LIMIT가 없는 경우는 모든 이벤트를 표시하게 되어 엄청난 양이 될 수 있으므로 주의해야한다.
지정한 바이너리 로그의 삭제 (PURGE MASTER LOGS)
바이너리 로그를 삭제하려면 PURGE MASTER LOGS를 사용한다. 구문은 다음과 같다.
PURGE {MASTER|BINARY} LOGS TO '바이너리 로그파일'
PURGE {MASTER|BINARY} LOGS BEFORE 'YYYY-MM-DD hh:mm:ss'
TO '바이너리 로그파일'의 지정의 경우는 지정된 파일 한개 전까지가 삭제된다.
BEFORE '날짜시간'도 지정된 일시보다 전의 파일이 삭제된다.
이 조작을 수행하면 호스트명-bin.index파일도 자동으로 변경된다.
Slave정보의 확인(SHOW SLAVE HOSTS)
Slave 정보를 얻기위해서는 SHOW SLAVE HOSTS를 실행한다.
Slave 서버에 --report-host=옵션을 지정하고 있는 것에 한정된다.
실행후에 나타나는 컬럼 Rpl_recovery_rank는 5.1.12-beta버전에서는 사용되어지지 않고 의미도 없다.
또, Master 서버에 --show-slave-auth-info옵션을 지정한 경우는 유저명과 패스워드도 표시된다.
이것은 Slave에서 다음의 옵션으로 지정한 값에 지나지 않는다. 실제로 Replication이 사용하고 있는 패스워드등은 표시되지 않는다.
--report-host=
--report-port=
--report-user=
--report-password=
Master의 초기화
모든 바이너리 로그파일을 삭제하고 Master를 초기화하는 경우는 RESET MASTER를 실행한다.
Replication운용중에 사용하지는 않겠지만 처음에 Replication을 재셋업할 경우등에 사용한다.
주의해서 사용해야한다.
바이너리 로그의 갱신을 일시적으로 ON/OFF하는 경우는 SET SQL_LOG_BIN을 실행한다.
보통 Replication 대상외로 하고 픈 테이블(권한 테이블)등을 갱신할 때만 사용한다.
구문은 다음과 같다.
SET SQL_LOG_BIN= {0|1}
「1」은 이 세션의 갱신을 바이너리로그에 기록한다. 기본값이다. 「0」은 이 세션의 갱신을 바이너리로그에 기록하지 않는다.
Master이 상태확인(SHOW MASTER STATUS)
현재 써넣고 있는 바이너리로그와 그 위치를 확인하는 경우는 SHOW MASTER STATUS를 실행한다.
바이너리 로그의 목록을 표시(SHOW BINARY LOGS)
현재 유지하고 있는 바이너리 로그를 목록표시하기 위해서는 SHOW BINARY LOGS를 사용한다.
구문은 다음과 같다.
SHOW [MASTER|BINARY] LOGS
바이너리로그내의 이벤트를 표시 ( SHOW BINLOG EVENTS )
지정된 바이너리 로그파일의 이벤트를 나타낸다. 구문은 다음과 같다.
SHOW BINLOG EVENTS
[IN '바이너리로그파일'] [FROM 위치1] [LIMIT [오프셋,] 갯수]
바이너리 로그파일명을 지정하지 않는 경우는 최초의 바이너리 로그를 읽어들인다.
LIMIT으로 이벤트의 읽어들일 위치(오프셋)과 표시할 이벤트의 갯수를 지정할 수 있다.
LIMIT가 없는 경우는 모든 이벤트를 표시하게 되어 엄청난 양이 될 수 있으므로 주의해야한다.
지정한 바이너리 로그의 삭제 (PURGE MASTER LOGS)
바이너리 로그를 삭제하려면 PURGE MASTER LOGS를 사용한다. 구문은 다음과 같다.
PURGE {MASTER|BINARY} LOGS TO '바이너리 로그파일'
PURGE {MASTER|BINARY} LOGS BEFORE 'YYYY-MM-DD hh:mm:ss'
TO '바이너리 로그파일'의 지정의 경우는 지정된 파일 한개 전까지가 삭제된다.
BEFORE '날짜시간'도 지정된 일시보다 전의 파일이 삭제된다.
이 조작을 수행하면 호스트명-bin.index파일도 자동으로 변경된다.
Slave정보의 확인(SHOW SLAVE HOSTS)
Slave 정보를 얻기위해서는 SHOW SLAVE HOSTS를 실행한다.
Slave 서버에 --report-host=옵션을 지정하고 있는 것에 한정된다.
실행후에 나타나는 컬럼 Rpl_recovery_rank는 5.1.12-beta버전에서는 사용되어지지 않고 의미도 없다.
또, Master 서버에 --show-slave-auth-info옵션을 지정한 경우는 유저명과 패스워드도 표시된다.
이것은 Slave에서 다음의 옵션으로 지정한 값에 지나지 않는다. 실제로 Replication이 사용하고 있는 패스워드등은 표시되지 않는다.
--report-host=
--report-port=
--report-user=
--report-password=
Master의 초기화
모든 바이너리 로그파일을 삭제하고 Master를 초기화하는 경우는 RESET MASTER를 실행한다.
Replication운용중에 사용하지는 않겠지만 처음에 Replication을 재셋업할 경우등에 사용한다.
주의해서 사용해야한다.
2010년 7월 5일 월요일
Replication : Master에서의 설정과 조작 3
Slave데이터베이스의 유저명, 호스트, 패스워드 등록
GRANT REPLICATION SLAVE ON *.*
TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']
global 권한이므로 ON *.*이 된다.
권한의 부여
Master와 Slave의 내용을 확인할 수 있도록 SHOW MASTER STATUS와 SHOW SLAVE STATUS를 실행할 권한을 부여한다.
GRANT REPLICATION CLIENT ON *.*
TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']
global 권한이므로 ON *.*이 된다.
바이너리로그의 로테이트(FLUSH LOGS)
바이너리로그를 로테이트시킨다. 이 조작으로 Replication에서 부정합이 발생하지 않는다.
이 조작을 실행하면 호스트명-bin.index파일도 자동으로 변경된다. SQL처리중에 FLUSH LOGS가 실행된 경우, 그것이 완료된 뒤에 로그의 로테이트와 .index파일이 변경된다.
Binlog Dump 스레드의 확인
Master는 바이너리로그에 기록되어있는 쿼리를 Slave에 송신하기 위해서 스레드를 작동한다. (Binlog Dump 스레드) Binlog Dump 스레드는 SHOW PROCESSLIST로 확인가능하다.
내용은 아래와 같다.
GRANT REPLICATION SLAVE ON *.*
TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']
global 권한이므로 ON *.*이 된다.
권한의 부여
Master와 Slave의 내용을 확인할 수 있도록 SHOW MASTER STATUS와 SHOW SLAVE STATUS를 실행할 권한을 부여한다.
GRANT REPLICATION CLIENT ON *.*
TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']
global 권한이므로 ON *.*이 된다.
바이너리로그의 로테이트(FLUSH LOGS)
바이너리로그를 로테이트시킨다. 이 조작으로 Replication에서 부정합이 발생하지 않는다.
이 조작을 실행하면 호스트명-bin.index파일도 자동으로 변경된다. SQL처리중에 FLUSH LOGS가 실행된 경우, 그것이 완료된 뒤에 로그의 로테이트와 .index파일이 변경된다.
Binlog Dump 스레드의 확인
Master는 바이너리로그에 기록되어있는 쿼리를 Slave에 송신하기 위해서 스레드를 작동한다. (Binlog Dump 스레드) Binlog Dump 스레드는 SHOW PROCESSLIST로 확인가능하다.
내용은 아래와 같다.
- User, Host : Slave 접속 유저명과 호스트명
- Command Binlog Dump: COM_BINLOG_DUMP. 바이너리로그를 보내는 루틴에 들어가 있는 상태. msql_binlog_send()로 보낸다.
- Connect Out: Slave가 접속중
- Register Slave : COM_REGISTER_SLAVE. --report-host=를 지정한 Slave의 정보를 취득처리중. register_slave()가 호출된다.
- State Finished reading one binlog; switching to next binlog: 스레드는 어떤 바이너리로그를 다 읽어들여서 다음의 바이너리 로그파일을 open하려고 하는 상태
- Has sent all binlog to slave; waiting for binlog to be updated: 모든 바이너리로그를 Slave에 전송이 끝나고 새로운 이벤트를 기다리고 있는 상태
- Sending binlog event to slave: 바이너리로그를 읽고 Slave에 송신중
- Waiting to finalize termination: 스레드 종료할 때의 상태
2010년 7월 4일 일요일
Replication : Master에서의 설정과 조작 2
바이너리 로그 파일
바이너리로그 파일은 호스트명-bin.NNNNNN 형식으로 작성된다. N은 숫자로 000001에서 순서대로 증가된다. 999999다음은 1000000으로 된다.
호스트명-bin의 부분은 --log-bin=옵션에서 변경가능하다. 또 디폴트로는 datadir/밑으로 작성되어지지만 보존장소도 --log-bin=옵션으로 변경가능하다.
바이너리 로그 인덱스파일
바이너리 로그 인덱스파일은 바이너리 로그 파일의 리스트를 유지한다.
호스트명-bin.index라는 이름으로 작성된다.
바이너리 로그인덱스 파일의 예
shell#> cat master-bin.index
./master-bin.000002
./master-bin.000003
바이너리로그 파일은 호스트명-bin.NNNNNN 형식으로 작성된다. N은 숫자로 000001에서 순서대로 증가된다. 999999다음은 1000000으로 된다.
호스트명-bin의 부분은 --log-bin=옵션에서 변경가능하다. 또 디폴트로는 datadir/밑으로 작성되어지지만 보존장소도 --log-bin=옵션으로 변경가능하다.
바이너리 로그 인덱스파일
바이너리 로그 인덱스파일은 바이너리 로그 파일의 리스트를 유지한다.
호스트명-bin.index라는 이름으로 작성된다.
바이너리 로그인덱스 파일의 예
shell#> cat master-bin.index
./master-bin.000002
./master-bin.000003
2010년 7월 1일 목요일
Replication : Master에서의 설정과 조작 1
Master데이터베이스 설정과 조작
Master데이터베이스로써 동작시키기 위해서는 최소한 다음 옵션을 지정할 필요가 있다.
옵션은 my.cnf에 기술한다.
Replication의 Master로 기동하기 전에 모든 데이터를 백업하고 설정을 한 후 Master로 기동한다.
server-id = 번호
log-bin [=파일명]
Master에 관한 옵션은 다음의 표를 참조하길 바란다.
Master에 관한 SQL문에는 다음과 같은 것이 있다.
Master데이터베이스로써 동작시키기 위해서는 최소한 다음 옵션을 지정할 필요가 있다.
옵션은 my.cnf에 기술한다.
Replication의 Master로 기동하기 전에 모든 데이터를 백업하고 설정을 한 후 Master로 기동한다.
server-id = 번호
log-bin [=파일명]
Master에 관한 옵션은 다음의 표를 참조하길 바란다.
- server-id=자연수 : 서버의 ID번호를 지정. 모든 slave, master에서 유일한 숫자를 지정할 필요가 있다.
- log-bin[=파일명] : 바이너리로그<호스트명-bin>를 기록한다.
- binlog_format={MIXED|STATEMENT|ROW} : 바이너리로그의 서식을 지정한다.
- binlog-do-db=데이터베이스명 : 지정된 데이터베이스로의 변경만을 바이너리로그에 기록한다.
- binlog-ignore-db=데이터베이스명: 지정된 데이터베이스로의 변경만을 바이너리로그에 기록하지 않음.
- binlog-row-event-max-size=바이트수: ROW의 경우 , 한개의 이벤트당 최대 바이트수.
- log-bin-index=이름 : <호스트명-bin.index> 파일명의 지정
- log-bin-trust-function-creators: 스토어드 프로시져 작성의 제한
- show-slave-auth-info : SHOW SLAVE HOSTS문으로 Slave정보목록에 사용자명과 패스워드를 추가한 것을 얻을 수 있음. Slave 서버에 -report-host=Slave호스트명과 옵션을 지정한 것만 표시됨
- net_buffer_length=바이트수 : 통신에 사용하는 버퍼
- net_read_timeout=초수 : 읽어들이는 중에 통신이 끊겼을 경우, 몇초를 기다려서 에러로 할 것인가 지정
- net_write_timeout=초수 : 쓰는 도중에 통신이 끊겼을 경우, 몇초를 기다려서 에러로 할 것인가 지정
- init-slave='SQL문' : Slave가 접속해 오면 최초 지정한 SQL문을 실행
Master에 관한 SQL문에는 다음과 같은 것이 있다.
- GRANT REPLICATION SLAVE ON *.* : Replication Slave가 접속할 유저를 등록(권한 부여)
- GRANT REPLICATION CLIENT ON *.* : SHOW MASTER STATUS를 실행할 수 있는 권한을 부여
- FLUSH LOGS: 바이너리로그를 로테이트
- SHOW PROCESSLIST : Replication 스레드를 확인
- SET SQL_LOG_BIN={0|1} : 현재 세션 기록을 바이너리로그에 수행할 것인가 하지 않을 것인가 지정
- SHOW MASTER STATUS: 바이너리 로그의 써넣기 상황을 확인
- SHOW BINARY LOGS: 현재 존재하는 바이너리 로그 파일명을 표시
- SHOW BINLOG EVENTS: 바이너리로그 이벤트를 표시
- PURGE MASTER LOGS: 지정된 바이너리로그만을 삭제
- SHOW SLAVE HOSTS: Slave 리스트를 표시. Slave서버는 --report-host=의 지정을 해둘 필요가 있다.
- RESET MASTER: 모든 바이너리로그를 삭제
Replication
데이터베이스시스템의 부하를 분산시키고 싶은 경우는 replication을 이용한다.
MySQL의 replication의 특징은 다음과 같다.
MySQL의 Replication은 Master의 바이너리 로그의 내용을 Slave에 보내는 것으로 수행된다.
동작의 흐름은 다음과 같다.
MySQL의 replication의 특징은 다음과 같다.
- 한 방향(Master에서 Slave로 흘러감)
- 비동기
- Master:Slave = 1:N
- Slave겸 Master가 될 수 있다.
- 통신은 TCP/IP를 사용
- 바이너리 로그를 사용
- 갱신은 Master만 가능
MySQL의 Replication은 Master의 바이너리 로그의 내용을 Slave에 보내는 것으로 수행된다.
동작의 흐름은 다음과 같다.
- Slave I/O스레드가 Master에 접속
- Master가 Slave를 인증하고 Slave와의 세션 개시
- Slave I/O스레드가 바이너리 로그파일(파일명, 위치)를 요구
- Master(binlog dump스레드)가 요구되어진 지점으로부터 이벤트를 바이너리 로그에서 읽어들여 Slave 에 전송
- Slave I/O스레드는 받어낸 이벤트를 relay-log에 기록
- Slave SQL스레드가 relay-log내용을 읽어들여 SQL문을 실행
- Master에 새로운 이벤트가 있으면 Master가 Slave에 송신
Event Scheduler 4
이벤트의 수정
이미 등록되어있는 이벤트를 변경하는 경우는 ALTER EVENT문을 사용한다.
구문은 다음과 같다.
ALTER EVENT 이벤트명
[ON SCHEDULE 스케줄]
[RENAME TO 이벤트명]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT '주석']
[ENABLE| DISABLE]
[DO [BEGIN] 실행할 sql문; [ 실행할 sql문;] [END] ]
예>
mysql>delimiter ;
mysql>ALTER EVENT test_event ON COMPLETION PRESERVE;
이벤트의 삭제
등록한 이벤트를 삭제하기 위해서는 DROP EVENT문을 사용한다.
DROP EVENT [IF EXISTS] 이벤트명
이미 등록되어있는 이벤트를 변경하는 경우는 ALTER EVENT문을 사용한다.
구문은 다음과 같다.
ALTER EVENT 이벤트명
[ON SCHEDULE 스케줄]
[RENAME TO 이벤트명]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT '주석']
[ENABLE| DISABLE]
[DO [BEGIN] 실행할 sql문; [ 실행할 sql문;] [END] ]
예>
mysql>delimiter ;
mysql>ALTER EVENT test_event ON COMPLETION PRESERVE;
이벤트의 삭제
등록한 이벤트를 삭제하기 위해서는 DROP EVENT문을 사용한다.
DROP EVENT [IF EXISTS] 이벤트명
2010년 6월 27일 일요일
Event Scheduler 3
이벤트 등록
이벤트 등록을 하기 위해서는 CREATE EVENT문을 사용한다. 구문은 아래와 같다.
CREATE EVENT [IF NOT EXISTS] 이벤트명
ON SCHEDULE 스케줄
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '주석']
DO [BEGIN] 실행할 sql문; [실행할 sql문]; [END]
스케줄:
{ AT 타임 [+ INTERVAL 간격 [+INTERVAL 간격...]]
| EVERY 간격 [STARTS 타임] [ENDS 타임] }
타임:
{CURRENT_TIMESTAMP | 년월일시의 리터럴}
간격:
수 {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY
|HOUR|MINUTE| WEEK| SECOND | YEAR_MONTH|DAY_HOUR|DAY_MINUTE| DAY_SECOND| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
ON SCHEDULE구에서는 이벤트의 실행시간과 간격을 지정한다. 이것은 필수이다. DO 구 뒤에는 실행할 SQL문을 지정한다. 이것도 필수이다.
이벤트명은 64문자까지이고 대소문자 구분하지 않는다.
유니크한 이름을 설정해야한다.
CURRENT_TIMESTAMP는 현재의 일시를 나타내는 특별한 키워드이다.
ON SCHEDULE AT timestamp는 한번만 실행하는 이벤트의 경우에 사용한다.
Unix의 at같은 것이라고 생각하면 될 것이다. 여기에 지정하는 timestamp에는 날짜와 시간 모두 포함할 필요가 있다. 예를 들어 2010-06-27 11:01:00 처럼 지정한다.
또, 지정하는 날짜는 미래의 시간이 되지 않으면 안된다.
+INTERVAL은 복수 지정이 가능하다. + INTERVAL 1 WEEK + INTERVAL 4 HOUR처럼 지정한다.
ON SCHEDULE EVERY는 이벤트를 반복실행할 때 사용한다. Unix의 cron이라고 생각하면 될 것이다. EVERY구인 경우는 + INTERVAL은 지정불가능이다.
STARTS에서는 개시일시 ENDS로 종료일시를 지정한다.
또, DO이하에 지정하는 SQL문이 한개 인경우에는 BEGIN, END, DELIMITER 지정은 필요없다.
복수의 문을 지정하는 경우는 BEGIN ~END로 문장을 감싼다. 이때 안에 있는 SQL문은「 ;」로 구별하기 때문에 CREATE EVENT문 끝을 의미하는 「 ;」하고 구별할 수 없게 된다.
그래서 stored procedure와 마찬가지로 CREATE EVENT실행전에 DELIMITER 를 지정해서 문장의 끝을 나타내는 마크를 변경해 두어야한다.
ON COMPLETION PRESERVE는 이벤트가 완료하더라고 이벤트의 내용을 유지한채 두게 된다. 보통은 바로 삭제된다.
⧈이벤트 등록예
mysql> delimiter //
mysql> CREATE EVENT test_event
-> ON SCHEDULE EVERY 1 DAY
-> STARTS ' 2010-06-27 11:01:00'
-> ENABLE
-> DO
-> BEGIN
-> DELETE FROM test.log
-> WHERE test.log.artime < NOW();
-> END //
이벤트 등록을 하기 위해서는 CREATE EVENT문을 사용한다. 구문은 아래와 같다.
CREATE EVENT [IF NOT EXISTS] 이벤트명
ON SCHEDULE 스케줄
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '주석']
DO [BEGIN] 실행할 sql문; [실행할 sql문]; [END]
스케줄:
{ AT 타임 [+ INTERVAL 간격 [+INTERVAL 간격...]]
| EVERY 간격 [STARTS 타임] [ENDS 타임] }
타임:
{CURRENT_TIMESTAMP | 년월일시의 리터럴}
간격:
수 {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY
|HOUR|MINUTE| WEEK| SECOND | YEAR_MONTH|DAY_HOUR|DAY_MINUTE| DAY_SECOND| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
ON SCHEDULE구에서는 이벤트의 실행시간과 간격을 지정한다. 이것은 필수이다. DO 구 뒤에는 실행할 SQL문을 지정한다. 이것도 필수이다.
이벤트명은 64문자까지이고 대소문자 구분하지 않는다.
유니크한 이름을 설정해야한다.
CURRENT_TIMESTAMP는 현재의 일시를 나타내는 특별한 키워드이다.
ON SCHEDULE AT timestamp는 한번만 실행하는 이벤트의 경우에 사용한다.
Unix의 at같은 것이라고 생각하면 될 것이다. 여기에 지정하는 timestamp에는 날짜와 시간 모두 포함할 필요가 있다. 예를 들어 2010-06-27 11:01:00 처럼 지정한다.
또, 지정하는 날짜는 미래의 시간이 되지 않으면 안된다.
+INTERVAL은 복수 지정이 가능하다. + INTERVAL 1 WEEK + INTERVAL 4 HOUR처럼 지정한다.
ON SCHEDULE EVERY는 이벤트를 반복실행할 때 사용한다. Unix의 cron이라고 생각하면 될 것이다. EVERY구인 경우는 + INTERVAL은 지정불가능이다.
STARTS에서는 개시일시 ENDS로 종료일시를 지정한다.
또, DO이하에 지정하는 SQL문이 한개 인경우에는 BEGIN, END, DELIMITER 지정은 필요없다.
복수의 문을 지정하는 경우는 BEGIN ~END로 문장을 감싼다. 이때 안에 있는 SQL문은「 ;」로 구별하기 때문에 CREATE EVENT문 끝을 의미하는 「 ;」하고 구별할 수 없게 된다.
그래서 stored procedure와 마찬가지로 CREATE EVENT실행전에 DELIMITER 를 지정해서 문장의 끝을 나타내는 마크를 변경해 두어야한다.
ON COMPLETION PRESERVE는 이벤트가 완료하더라고 이벤트의 내용을 유지한채 두게 된다. 보통은 바로 삭제된다.
⧈이벤트 등록예
mysql> delimiter //
mysql> CREATE EVENT test_event
-> ON SCHEDULE EVERY 1 DAY
-> STARTS ' 2010-06-27 11:01:00'
-> ENABLE
-> DO
-> BEGIN
-> DELETE FROM test.log
-> WHERE test.log.artime < NOW();
-> END //
2010년 6월 14일 월요일
Event Scheduler 2
이벤트는 SHOW문을 실행하던지 INFORMATION_SCHEMA.EVENTS테이블을 SELECT하는 것으로 확인가능하다.
SHOW EVENTS [FROM 스키마명] [LIKE 패턴]
SHOW CREATE EVENT 이벤트명
SHOW EVENTS는 정의되어 있는 이벤트리스트를 얻는다.
SHOW CREATE EVENT는 지정된 EVENT의 CREATE문을 표시한다.
[SHOW EVENTS에서 각 컬럼의 의미]
[INFORMATION_SCHEMA.EVENTS에서 각 컬럼의 의미]
*show events에서 얻을 수 있는 컬럼정보와 설명생략
SHOW EVENTS [FROM 스키마명] [LIKE 패턴]
SHOW CREATE EVENT 이벤트명
SHOW EVENTS는 정의되어 있는 이벤트리스트를 얻는다.
SHOW CREATE EVENT는 지정된 EVENT의 CREATE문을 표시한다.
[SHOW EVENTS에서 각 컬럼의 의미]
- Db: 데이터베이스명
- Name: 이벤트명
- Definer: 이벤트를 작성한 유저
- Type: 반복사용시 RECURRING, 한번만 실행할 시 ONE TIME
- Execute at: RECURRING의 경우는 NULL, ONE TIME의 경우는 실행시간
- Interval value: 이벤트의 간격. ONE TIME의 경우는 NULL
- Interval field: 이벤트 간격의 단위. ONE TIME의 경우는 NULL
- Starts: RECURRING인 경우는 개시시간. ONE TIME의 경우는 NULL. UTC로 표시됨.
- Ends: RECURRING인 경우는 종료시간.( 0000-00-00 00:00:00인 경우는 영원히 실행). ONE TIME의 경우는 NULL. UTC로 표시됨.
- Status: ENABLED 나 DISABLED
[INFORMATION_SCHEMA.EVENTS에서 각 컬럼의 의미]
*show events에서 얻을 수 있는 컬럼정보와 설명생략
- EVENT_CATALOG: 항상 NULL
- EVENT_BODY: 항상 SQL
- SQL_MODE: 이벤트가 작성되었을 때의 sql_mode의 값
- ON_COMPLETION: 이벤트가 완료되었을 때 이벤트 내용을 삭제할 때는 NOT PRESERVE 유지한다면 PRESERVE
- CREATED: 이벤트 생성일시. UTC로 표시됨.
- LAST_ALTERED: 이벤트 변경일시. UTC로 표시됨.
- LAST_EXECUTED: 최후의 이벤트 실행일시. UTC로 표시됨.
2010년 6월 7일 월요일
Event Scheduler
정기적으로 실행하거나 자동실행하고픈 스크립트등은 Event Scheduler에 등록해서 사용하면 편리하다.
Event Scheduler는 Unix의 cron이나 at에 해당되는 것이다. 이 기능은 MySQL 5.1.6에서 추가되었다.
Event Scheduler를 기록하는 테이블
Event Scheduler는 mysql.event테이블에 기록된다. MySQL 5.1미만에서 업그레이드해서 이 테이블이 존재하지 않는 경우는 mysql_fix_privilege_tables스크립트를 실행해서 테이블을 작성하면 된다.
mysql.event 테이블의 컬럼
db, name, body, definer, execute_at, interval_value, interval_field, created, modified, last_executed, starts, ends, status, on_completion, sql_mode, comment
옵션/서버 변수
Event Scheduler에 관한 mysqld옵션은 다음과 같다.
--event-scheduler={DISABLED|ON|1|OFF|0}
mysqld가 기동중인 경우는 다음의 SET문으로 Event Scheduler의 ON/OFF를 변경하는 것이 가능하다.
ON하고 1, OFF하고 0은 같은 의미이다. DISABLED에 해당하는 수치는 없다.
DISABLED의 경우, Event Scheduler기능이 무효화되고 SET문으로 ON/OFF변경 불가하다.
mysqld기동옵션에 --event-scheduler=DISABLED가 지정되면 서버 변수 event_scheduler값은 DISABLED로 된다.
옵션을 생략하면 OFF로 된다.
SET GLOBAL event_scheduler = {ON|OFF|1|0}
Event Scheduler가 기동되고 있는 경우는 SHOW PROCESSLIST로 전용 스레드가 한개 떠 있는 것을 확인할 수 있다.
mysql>SHOW PROCESSLIST;
id | User |Host | db |command | Time | State | Info
1 | root | localhost | mysql | Query | 0 | NULL | SHOW PROCESSLIST
2 |event_scheduler|localhost |NULL |Daemon |11 |Wating for next activation | NULL
Event Scheduler는 Unix의 cron이나 at에 해당되는 것이다. 이 기능은 MySQL 5.1.6에서 추가되었다.
Event Scheduler를 기록하는 테이블
Event Scheduler는 mysql.event테이블에 기록된다. MySQL 5.1미만에서 업그레이드해서 이 테이블이 존재하지 않는 경우는 mysql_fix_privilege_tables스크립트를 실행해서 테이블을 작성하면 된다.
mysql.event 테이블의 컬럼
db, name, body, definer, execute_at, interval_value, interval_field, created, modified, last_executed, starts, ends, status, on_completion, sql_mode, comment
옵션/서버 변수
Event Scheduler에 관한 mysqld옵션은 다음과 같다.
--event-scheduler={DISABLED|ON|1|OFF|0}
mysqld가 기동중인 경우는 다음의 SET문으로 Event Scheduler의 ON/OFF를 변경하는 것이 가능하다.
ON하고 1, OFF하고 0은 같은 의미이다. DISABLED에 해당하는 수치는 없다.
DISABLED의 경우, Event Scheduler기능이 무효화되고 SET문으로 ON/OFF변경 불가하다.
mysqld기동옵션에 --event-scheduler=DISABLED가 지정되면 서버 변수 event_scheduler값은 DISABLED로 된다.
옵션을 생략하면 OFF로 된다.
SET GLOBAL event_scheduler = {ON|OFF|1|0}
Event Scheduler가 기동되고 있는 경우는 SHOW PROCESSLIST로 전용 스레드가 한개 떠 있는 것을 확인할 수 있다.
mysql>SHOW PROCESSLIST;
id | User |Host | db |command | Time | State | Info
1 | root | localhost | mysql | Query | 0 | NULL | SHOW PROCESSLIST
2 |event_scheduler|localhost |NULL |Daemon |11 |Wating for next activation | NULL
2010년 6월 4일 금요일
쿼리캐쉬로 성능개선2
[쿼리 캐쉬 설정의 확인]
mysql> SHOW VARIABLES LIKE 'query_cache_%';
[쿼 리 캐쉬에 할당되는 메모리양]
쿼리캐쉬에 할당되어지는 메모리의 최대양은 설정 파일인 my.cnf또는 my.ini의 query_cache_size옵션에 지정된 양이 된다. 기본값은 0으로 쿼리캐쉬용 메모리는 확보되지 않는다. 예> query_cache_size=16M
[쿼리 캐쉬의 블록 사이즈]
쿼리캐쉬의 메모리 영역은 블록으로 관리되지만 블록 사이즈는 변경가능하다.
LRU (Least Recently Used) 알고리즘을 사용해서 캐쉬에 남길 쿼리를 결정한다.
기본값은 query_cache_min_res_unit으로 4KB로 설정되어 있다.
[기억되는 쿼리 결과의 최대값]
query_cache_limit 은 캐쉬하는 쿼리 결과의 최대값이다. 여기에서 설정된 사이즈이상의 결과는 캐쉬되지 않는다. 기본값은 1MB이다.
[쿼리캐쉬의 동작스위치]
query_cache_type옵션에 값을 지정하는 것으로 쿼리캐쉬의 유효등 지정이 가능하다.
0 또는 OFF : 쿼리캐쉬를 사용하지 않음. 기본값
1 또는 ON: 쿼리캐쉬사용. SELECT SQL_NO_CACHE로 된 쿼리만이 캐쉬되지 않는다.
2 또는 DEMAND: 쿼리캐쉬사용. SELECT SQL_CACHE로 된 쿼리만 캐쉬된다.
[Write Lock이 걸렸을 경우 쿼리캐쉬]
MyISAM의 경우 , 어떤 클라이언트(C1)가 어떤 테이블(T1)에 Write Lock를 걸고 있는 경우에 다른 클라이언트(C2)가 쿼리캐쉬로부터 T1에 대한 SELECT결과를 취득할 수 있다.(기본동작)
C1이 T1을 변경하고 있는 중에 C2가 캐쉬되어 있는 T1내용을 읽어 들일 수 있다는 것은 같은 테이블임에도 읽어들여지는 값이 각 클라이언트마다 달라질 수 있다는 것을 의미한다.
query_cache_wlock_invalidate를 1로 하면 lock걸린 MyISAM테이블 캐쉬는 파기된다. C2는 C1갱신이 끝나고나서야 T1에 접속할 수 있다.
[쿼리 캐쉬 사용상황]
mysql> SHOW STATUS LIKE 'Qcache%';
[메모리영역의 fragmentation의 개선]
메모리 효율을 향상 시키기 위해서는 다음과 같은 조작으로 메모리영역의 fragmentation을 개선할 수 있다. 기억되어 있는 쿼리가 삭제되는 것은 아니다.
mysql> FLUSH QUERY CACHE;
[캐쉬의 삭제]
캐쉬되어 있는 내용을 전부 삭제하고 픈 경우에는 다음과 같이 조작한다.
mysql> RESET QUERY CACHE;
mysql> SHOW VARIABLES LIKE 'query_cache_%';
[쿼 리 캐쉬에 할당되는 메모리양]
쿼리캐쉬에 할당되어지는 메모리의 최대양은 설정 파일인 my.cnf또는 my.ini의 query_cache_size옵션에 지정된 양이 된다. 기본값은 0으로 쿼리캐쉬용 메모리는 확보되지 않는다. 예> query_cache_size=16M
[쿼리 캐쉬의 블록 사이즈]
쿼리캐쉬의 메모리 영역은 블록으로 관리되지만 블록 사이즈는 변경가능하다.
LRU (Least Recently Used) 알고리즘을 사용해서 캐쉬에 남길 쿼리를 결정한다.
기본값은 query_cache_min_res_unit으로 4KB로 설정되어 있다.
[기억되는 쿼리 결과의 최대값]
query_cache_limit 은 캐쉬하는 쿼리 결과의 최대값이다. 여기에서 설정된 사이즈이상의 결과는 캐쉬되지 않는다. 기본값은 1MB이다.
[쿼리캐쉬의 동작스위치]
query_cache_type옵션에 값을 지정하는 것으로 쿼리캐쉬의 유효등 지정이 가능하다.
0 또는 OFF : 쿼리캐쉬를 사용하지 않음. 기본값
1 또는 ON: 쿼리캐쉬사용. SELECT SQL_NO_CACHE로 된 쿼리만이 캐쉬되지 않는다.
2 또는 DEMAND: 쿼리캐쉬사용. SELECT SQL_CACHE로 된 쿼리만 캐쉬된다.
[Write Lock이 걸렸을 경우 쿼리캐쉬]
MyISAM의 경우 , 어떤 클라이언트(C1)가 어떤 테이블(T1)에 Write Lock를 걸고 있는 경우에 다른 클라이언트(C2)가 쿼리캐쉬로부터 T1에 대한 SELECT결과를 취득할 수 있다.(기본동작)
C1이 T1을 변경하고 있는 중에 C2가 캐쉬되어 있는 T1내용을 읽어 들일 수 있다는 것은 같은 테이블임에도 읽어들여지는 값이 각 클라이언트마다 달라질 수 있다는 것을 의미한다.
query_cache_wlock_invalidate를 1로 하면 lock걸린 MyISAM테이블 캐쉬는 파기된다. C2는 C1갱신이 끝나고나서야 T1에 접속할 수 있다.
[쿼리 캐쉬 사용상황]
mysql> SHOW STATUS LIKE 'Qcache%';
[메모리영역의 fragmentation의 개선]
메모리 효율을 향상 시키기 위해서는 다음과 같은 조작으로 메모리영역의 fragmentation을 개선할 수 있다. 기억되어 있는 쿼리가 삭제되는 것은 아니다.
mysql> FLUSH QUERY CACHE;
[캐쉬의 삭제]
캐쉬되어 있는 내용을 전부 삭제하고 픈 경우에는 다음과 같이 조작한다.
mysql> RESET QUERY CACHE;
2010년 6월 3일 목요일
쿼리캐쉬로 성능개선
MySQL 쿼리캐쉬는 SELECT문과 그 결과를 기억한다.
SELECT문은 해석되기 전에 쿼리 캐쉬에 기억되어져 있는 것과 비교되어 내용이 같은 경우는 캐쉬에서 결과를 가져온다.
다시말하면, 같은 쿼리가 반복되어 실행되는 환경인 경우 쿼리캐쉬를 적절하게 이용하는 것으로 성능을 개선할 수 있는 것이다.
쿼리캐쉬에 기억되는 쿼리와 일치하기 위해서는 대문자, 소문자차이, 공백등도 포함해 모두 같은 문자열이어야 한다.
또 테이블에 변경이 반영되면 그 테이블에 관하여 캐쉬된 쿼리는 파기되어 버린다.
다음과 같은 경우에는 캐쉬되지 않는다.
SELECT문은 해석되기 전에 쿼리 캐쉬에 기억되어져 있는 것과 비교되어 내용이 같은 경우는 캐쉬에서 결과를 가져온다.
다시말하면, 같은 쿼리가 반복되어 실행되는 환경인 경우 쿼리캐쉬를 적절하게 이용하는 것으로 성능을 개선할 수 있는 것이다.
쿼리캐쉬에 기억되는 쿼리와 일치하기 위해서는 대문자, 소문자차이, 공백등도 포함해 모두 같은 문자열이어야 한다.
또 테이블에 변경이 반영되면 그 테이블에 관하여 캐쉬된 쿼리는 파기되어 버린다.
다음과 같은 경우에는 캐쉬되지 않는다.
- Prepared Statement로 작성된 쿼리
- SELECT ... IN SHARE MODE
- SELECT ... FOR UPDATE
- SELECT ... INTO OUTFILE ...
- SELECT ... INTO DUMPFILE ...
- SELECT * FROM ... WHERE autoincrement_field IS NULL
- 다음의 함수가 사용된 경우: BENCHMARK(), CONNECTION_ID(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(), DATABASE(), ENCRYPT(인수가 한개인 경우), FOUND_ROWS(), GET_LOCK(), LAST_INSERT_ID(), LOAD_FILE(), MASTER_POS_WAIT, NOW(), RAND(), RELEASE_LOCK(), SYSDATE(), UNIX_TIMESTAMP(인수 없음), USER()
- UDF를 사용하고 있는 경우
- 사용자 변수를 사용하고 있는 경우
- 권한 테이블에 관한 SELECT
- TEMPORARY테이블에 관한 SELECT
2010년 5월 23일 일요일
MySQL성능측정2
Super-Smack(http://vegan.net/tony/supersmack/)은 MySQL벤치마킹툴이다.
특징으로는 다음과 같은 것이 있다.
- MySQL과 PostgreSQL에서 동작
- C++로 만들어졌으므로 쓸데없는 준비가 필요없다. (자바나 펄이면 MySQL용 드라이버가 필요), C의 AP를 사용해서 서버에 접속하므로 쓸 데 없는 잡음이 들어가지 않는다. 개조나 확장하기 쉬운 구조로 되어있다. 예를 들면 MySQL고유의 처리는 mysql-client.cc파일에 정리되어져 있으므로 이것을 참고로 Firebird대응도 가능할 것이다.
- 시나리오 파일(smack파일)로 자유롭게 실행하는 쿼리를 여러개 지정할 수 있다.
- 복수의 클라이언트를 가상으로 fork()로 생성하고 각 클라이언트(자식 프로세스)로 쿼리를 실행할 수 있다. 모든 클라이언트는 같은 시나리오로 동작한다.
- 데이터(영문숫자)를 생성하는 툴이 부속되어있다.
- 쿼리는 mysql_query()와 PQexec()를 실행. stored procedure는 사용하지 않음.
- SELECT결과는 fetch하지 않는다.
■컴파일
소스를 풀어놓은 후 configure, make한다. MySQL를 지원하려면 --with-mysql, --with-mysql-lib=, --with-mysql-include=를 지정한다.
make한 후 super-smack(이것이 본체)라는 명령어와 gen-data(데이터 생성 툴)이라는 명령어가 생기게 된다.
■사용방법
super-smack -d {pg|mysql} 시나리오파일 [인수 [인수] ...]
(pg: postgresql, mysql:MySQL, 인수는 시나리오 파일에서 사용하는 변수($1, $2...)가 된다. )
gen-data [-n행수 또는 --num-rows=행수] [-f포맷 또는 --format=포맷]
데이터를 생성하는 명령어로 생성할 레코드 수 , 레코드의 포맷을 지정한다.
■시나리오 파일 기술
다음의 블럭을 기술한다. (main, client, query, dictionary, table )
- main{} : 근간이 된다. 접속, 절단 그리고 어느 쿼리를 몇번이나 실행할 것인지 등의 기본이 되는 동작의 지시를 한다.
- client{} : 여러개 정의가 가능하다. 접속정보나 쿼리단위 정의를 한다.
- query{}: 여러개 정의가 가능하다. 쿼리를 실제로 기술하는 블럭이다.
- dictionry{}:여러개 정의가 가능하다. 필수조건은 아니다. 쿼리에 부여하는 값을 룰을 기술한다.
- table{}:여러개 정의가 가능하다. 필수조건은 아니다. 테이블의 존재를 체크해 없으면 생성한다. 또 레코드수를 체크해서 적으면 일단 테이블을 drop하고 테이블작성과 레코드 작성을 수행한다.
■결점
Super-Smack은 만능은 아니로 다음과 같은 결점도 있다.
- 여러개의 연속되는 쿼리에서 같은 값을 사용할 수 없다. 예를 들어 SELECT * FROM tbl WHERE col=$dict; UPDATE tbl SET col2=xxx WHERE col=$dict; 처럼 연속되는 쿼리의 $dict에는 다른값이 들어 가 버린다.
- 같은 쿼리내에서도 같은 값을 사용할 수 없다. SELECT $dict, $dict; 라고 해도 예를 들어 SELECT 1, 999;처럼 다른 값이 처리된다.
- 전에 실행한 SELECT의 결과를 새로운 쿼리에서 사용할 수 없다. 예를 들어 SELECT price FROM item WHERE id=1; 에서의 price값을 재 사용할 수 없다.
2010년 5월 18일 화요일
MySQL 성능 측정1
MySQL처리 성능을 측정하는 툴을 알아보자.
툴을 사용하면 튜닝이나 설계에 도움이 된다.
그러나 툴을 사용하기 또는 툴의 결과를 분석하는 경우는 기술자로서의 냉정한 눈이 필요하게 된다.
제3자의 실험결과를 그대로 믿어버리는 것이 아니라 자기 스스로 확인해야할 것이다.
하드웨어, 툴 설계 , 테이블 구조, 데이터양, ODBC,Perl, PHP, Java등의 중간층의 구현등 MySQL본체 이외에도 다양한 인자가 실험을 좌우한다.
툴과 그 결과는 절대적인 것이 아니므로 주의하면서 실험과 고찰을 해나가야한다.
물론 기존의 툴을 사용하지 않고 자기가 단순한 테스트 프로그램을 스스로 작성해서 확인해도 좋을 것이다.
MySQL의 C API는 단순하므로 바로 사용할 수 있을 것이다.
툴을 사용하면 튜닝이나 설계에 도움이 된다.
그러나 툴을 사용하기 또는 툴의 결과를 분석하는 경우는 기술자로서의 냉정한 눈이 필요하게 된다.
제3자의 실험결과를 그대로 믿어버리는 것이 아니라 자기 스스로 확인해야할 것이다.
하드웨어, 툴 설계 , 테이블 구조, 데이터양, ODBC,Perl, PHP, Java등의 중간층의 구현등 MySQL본체 이외에도 다양한 인자가 실험을 좌우한다.
툴과 그 결과는 절대적인 것이 아니므로 주의하면서 실험과 고찰을 해나가야한다.
물론 기존의 툴을 사용하지 않고 자기가 단순한 테스트 프로그램을 스스로 작성해서 확인해도 좋을 것이다.
MySQL의 C API는 단순하므로 바로 사용할 수 있을 것이다.
2010년 5월 10일 월요일
MySQL를 MRTG로 감시
「MySQL을 snmpd로 감시」에서 설명한 것은 SNMP서버가 MySQL값을 처리하는 방법이었다.
여기에서는 SNMP클라이언트인 MRTG가 MySQL값을 조사하는 방법을 소개한다.
SNMP클라이언트로 조사할 것인지 SNMP서버로 조사할 것인지 상황에 맞게 선택하면 될 것이다.
Debian에서는 다음과 같은 조작으로 MRTG가 설치된다.
root@shell# aptitude install mrtg mrtg-contrib
사용방법
MRTG설정파일에 MRTG가 명령어(스크립트)를 실행해서 그 결과를 처리하도록 해보자.
Target[Threads_connected]: `/etc/snmp/mysql_mrtg.sh`
Title[Threads_connected]: "MySQL Threads_connected"
Options[Threads_connected]: growright,nopercent,gauge
YLegend[Threads_connected]: times
ShortLegend[Threads_connected]: times
Legend1[Threads_connected]: times
Legent2[Threads_connected]: times
LegentO[Threads_connected]:
Target키워드는 Threads_connected를 조사하는 스크립트를 「`」로 묶어서 지정한다.
스크립트는 /etc/snmp/mysql_mrtg.sh로 한다. 이 스크립트에는 실행권한을 chmod로 부여해둔다. 또 Options에 nopercent를 지정해서 %를 다루지 않게 한다. Options의 gauge는 값을 차이처리, 평균화(/sec.)를 하지않는다는 것을 의미한다.
MRTG는 새롭게 취득한 값과 전에 취득한 값의 차이를 계산하여 그것을 기준으로 그래프를 그린다. 보통 네트워크의 인터페이스의 입출력패킷 양의 수치는 누적되어 처리되므로 이번에 얻은 값과 전에 얻은 값의 차이를 계산해서 출력한다.
그러나 이번에 측정하려고 하는 것은 MySQL의 Threads_connected 즉 지금 접속하고 있는 수이므로 gauge를 이용한다.
참고로 디스크사용량이나 미사용량은 gauge처리를 하는 것이 좋다.
MRTG는 보통 값을 초간격으로 평균(값/sec.)을 낸다. gauge의 경우, 값을 평균화하지 않고 측정값이 그대로 출력된다. 즉 순간순간 측정된 값이 출력된다.
gauge하고 비슷한 것이 absolute가 있다. 이것은 값을 초간격으로 평균처리하는 점이 gauge하고 다른 점이다.
Legend는 축의 단위이다. 단순히 표시할 때의 문자열을 나타내므로 편한대로 설정하면 될 것이다. LegendO[]:처럼 단위를 지정하지 않으면 Outbound선을 출력하지 않는다.
스크립트는 다음과 같이 처리한다.
간략화한 것이므로 인수처리, 에러처리, 배타처리등은 생략한다.
#!/bin/sh
export PATH=/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/sbin:/usr/sbin
TMPFILE=/tmp/mysql_mrtg.$$
mysql -uroot -e 'SHOW STATUS' > $TMPFILE
N=`grep -i '^Threads_connected' $TMPFILE | awk '{print $2}'`
T=`grep -i '^Uptime' $TMPFILE | awk '{print $2}'`
rm TMPFILE
echo "$N"
echo 0
echo "$(expr $T / 86400) days"
echo "MySQL Server Threads_connected"
exit 0
출력포맷은 다음과 같다.
Inbound의 값(수치)
Outbound의 값(수치)
가동시간(문자열)
코멘트(문자열)
MRTG는 인터페이스 패킷의 입출력을 조사하는 것을 전제로 하고 있다.
따라서 인터페이스의 Inbound와 Outbound 둘다 값이 필요하다.
이번 예에서는 한개만 필요하므로 Outbound를 0으로 고정하고 있다. 이것은 숫자이다.
나머지 가동시간, 코멘트는 인간이 읽을 수 있는 문자열이면 뭐든지 괜찮다.
여기에서는 SNMP클라이언트인 MRTG가 MySQL값을 조사하는 방법을 소개한다.
SNMP클라이언트로 조사할 것인지 SNMP서버로 조사할 것인지 상황에 맞게 선택하면 될 것이다.
Debian에서는 다음과 같은 조작으로 MRTG가 설치된다.
root@shell# aptitude install mrtg mrtg-contrib
사용방법
MRTG설정파일에 MRTG가 명령어(스크립트)를 실행해서 그 결과를 처리하도록 해보자.
Target[Threads_connected]: `/etc/snmp/mysql_mrtg.sh`
Title[Threads_connected]: "MySQL Threads_connected"
Options[Threads_connected]: growright,nopercent,gauge
YLegend[Threads_connected]: times
ShortLegend[Threads_connected]: times
Legend1[Threads_connected]: times
Legent2[Threads_connected]: times
LegentO[Threads_connected]:
Target키워드는 Threads_connected를 조사하는 스크립트를 「`」로 묶어서 지정한다.
스크립트는 /etc/snmp/mysql_mrtg.sh로 한다. 이 스크립트에는 실행권한을 chmod로 부여해둔다. 또 Options에 nopercent를 지정해서 %를 다루지 않게 한다. Options의 gauge는 값을 차이처리, 평균화(/sec.)를 하지않는다는 것을 의미한다.
MRTG는 새롭게 취득한 값과 전에 취득한 값의 차이를 계산하여 그것을 기준으로 그래프를 그린다. 보통 네트워크의 인터페이스의 입출력패킷 양의 수치는 누적되어 처리되므로 이번에 얻은 값과 전에 얻은 값의 차이를 계산해서 출력한다.
그러나 이번에 측정하려고 하는 것은 MySQL의 Threads_connected 즉 지금 접속하고 있는 수이므로 gauge를 이용한다.
참고로 디스크사용량이나 미사용량은 gauge처리를 하는 것이 좋다.
MRTG는 보통 값을 초간격으로 평균(값/sec.)을 낸다. gauge의 경우, 값을 평균화하지 않고 측정값이 그대로 출력된다. 즉 순간순간 측정된 값이 출력된다.
gauge하고 비슷한 것이 absolute가 있다. 이것은 값을 초간격으로 평균처리하는 점이 gauge하고 다른 점이다.
Legend는 축의 단위이다. 단순히 표시할 때의 문자열을 나타내므로 편한대로 설정하면 될 것이다. LegendO[]:처럼 단위를 지정하지 않으면 Outbound선을 출력하지 않는다.
스크립트는 다음과 같이 처리한다.
간략화한 것이므로 인수처리, 에러처리, 배타처리등은 생략한다.
#!/bin/sh
export PATH=/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/sbin:/usr/sbin
TMPFILE=/tmp/mysql_mrtg.$$
mysql -uroot -e 'SHOW STATUS' > $TMPFILE
N=`grep -i '^Threads_connected' $TMPFILE | awk '{print $2}'`
T=`grep -i '^Uptime' $TMPFILE | awk '{print $2}'`
rm TMPFILE
echo "$N"
echo 0
echo "$(expr $T / 86400) days"
echo "MySQL Server Threads_connected"
exit 0
출력포맷은 다음과 같다.
Inbound의 값(수치)
Outbound의 값(수치)
가동시간(문자열)
코멘트(문자열)
MRTG는 인터페이스 패킷의 입출력을 조사하는 것을 전제로 하고 있다.
따라서 인터페이스의 Inbound와 Outbound 둘다 값이 필요하다.
이번 예에서는 한개만 필요하므로 Outbound를 0으로 고정하고 있다. 이것은 숫자이다.
나머지 가동시간, 코멘트는 인간이 읽을 수 있는 문자열이면 뭐든지 괜찮다.
2010년 4월 25일 일요일
MySQL를 snmpd로 감시4
pass의 구현과 사용방법
【사용방법】
우선은 사용방법부터 알아보자. snmpd.conf에 다음처럼 기술한다.
pass .1.3.6.1.4.1.2021.51 /etc/snmp/mysql_proc_pass.sh .1.3.6.1.4.1.2021.51
여기에서는 새롭게 /etc/snmp/mysql_proc_pass.sh스크립트를 만들어보자. 이 스크립트가 필요한 값을 표준출력으로 되돌려주게 된다.
exec와 마찬가지로 MIB OID는 맘대로 결정한다.
pass의 포맷은 다음과 같다.
pass MIBOID 명령어
mysql_proc_pass.sh의 뒤에 있는 .1.3.6.1.4.1.2021.51 는 명령어의 인수이다. 이것은 pass의 사양에 따른 것이다.
【snmpd의 동작】
snmpd에 .1.3.6.1.4.1.2021.51 를 질의하면 snmpd는 다음과 같이 실행한다.
/etc/snmp/mysql_proc_pass.sh .1.3.6.1.4.1.2021.51 -n .1.3.6.1.4.1.2021.51
복잡하게 보이므로 간략화하면 다음과 같다.
명령어 -n .1.3.6.1.4.1.2021.51
-n옵션과 지정된 MIB OID를 인수로 추가해서 명령어가 실행된다. -n이외에 -g 옵션이 지정되는 경우도 있다. -n은 SNMP getnext 요청시에 실행된다. -g는 SNMP get요청시에 실행된다.
pass에 지정된 명령어는 -g와 -n 모두의 옵션을 지원하지 않으면 안된다는 것이다.
또 -s옵션도 존재하지만 -s는 값을 set할 때에 사용되는 옵션이다.
【출력포맷】
실행되는 명령어의 -n와 -g옵션때에 출력되는 포맷은 다음과 같다.
MIB OID
타입
값
LF개행으로 세개의 행이다.
타입은 string, integer, counter, gauge, timeticks, ipaddress, objid중에 한개 이다.
스크립트 인수에 MIB OID가 있었던 것은 이 출력의 첫번째 행을 위해서이다.
출력의 첫번째 행을 스크립트내부에서 생성할 필요가 있다.
【에러처리】
-n과 -g 옵션일 때 스크립트가 에러를 처리하고자 할 때에는 표준출력에는 아무것도 되돌리지 말고 exit한다.
【-n 옵션】
ex 1>
명령:
명령어 -n .1.3.6.1.4.1.2021.51
결과:
.1.3.6.1.4.1.2021.51 .1
string
mysql_proc_pass
ex 2>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.1
결과:
.1.3.6.1.4.1.2021.51 .2
string
/etc/snmp/mysql_proc_pass.sh
ex 3>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.2
결과:
.1.3.6.1.4.1.2021.51 .3
integer
1
ex 3>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.3
결과:
(없음)
【-g 옵션】
-g 옵션일 때에는 -n하고는 달리 지정된 MIB OID하고 그 값을 되돌려줄 필요가 있다.
결과의 첫번째 행에 -n의 경우는 「다음 MIB OID」, -g의 경우는 「지정된 MIB OID」가 된다.
【사용방법】
우선은 사용방법부터 알아보자. snmpd.conf에 다음처럼 기술한다.
pass .1.3.6.1.4.1.2021.51 /etc/snmp/mysql_proc_pass.sh .1.3.6.1.4.1.2021.51
여기에서는 새롭게 /etc/snmp/mysql_proc_pass.sh스크립트를 만들어보자. 이 스크립트가 필요한 값을 표준출력으로 되돌려주게 된다.
exec와 마찬가지로 MIB OID는 맘대로 결정한다.
pass의 포맷은 다음과 같다.
pass MIBOID 명령어
mysql_proc_pass.sh의 뒤에 있는 .1.3.6.1.4.1.2021.51 는 명령어의 인수이다. 이것은 pass의 사양에 따른 것이다.
【snmpd의 동작】
snmpd에 .1.3.6.1.4.1.2021.51 를 질의하면 snmpd는 다음과 같이 실행한다.
/etc/snmp/mysql_proc_pass.sh .1.3.6.1.4.1.2021.51 -n .1.3.6.1.4.1.2021.51
복잡하게 보이므로 간략화하면 다음과 같다.
명령어 -n .1.3.6.1.4.1.2021.51
-n옵션과 지정된 MIB OID를 인수로 추가해서 명령어가 실행된다. -n이외에 -g 옵션이 지정되는 경우도 있다. -n은 SNMP getnext 요청시에 실행된다. -g는 SNMP get요청시에 실행된다.
pass에 지정된 명령어는 -g와 -n 모두의 옵션을 지원하지 않으면 안된다는 것이다.
또 -s옵션도 존재하지만 -s는 값을 set할 때에 사용되는 옵션이다.
【출력포맷】
실행되는 명령어의 -n와 -g옵션때에 출력되는 포맷은 다음과 같다.
MIB OID
타입
값
LF개행으로 세개의 행이다.
타입은 string, integer, counter, gauge, timeticks, ipaddress, objid중에 한개 이다.
스크립트 인수에 MIB OID가 있었던 것은 이 출력의 첫번째 행을 위해서이다.
출력의 첫번째 행을 스크립트내부에서 생성할 필요가 있다.
【에러처리】
-n과 -g 옵션일 때 스크립트가 에러를 처리하고자 할 때에는 표준출력에는 아무것도 되돌리지 말고 exit한다.
【-n 옵션】
ex 1>
명령:
명령어 -n .1.3.6.1.4.1.2021.51
결과:
.1.3.6.1.4.1.2021.51 .1
string
mysql_proc_pass
ex 2>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.1
결과:
.1.3.6.1.4.1.2021.51 .2
string
/etc/snmp/mysql_proc_pass.sh
ex 3>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.2
결과:
.1.3.6.1.4.1.2021.51 .3
integer
1
ex 3>
명령:
명령어 -n .1.3.6.1.4.1.2021.51.3
결과:
(없음)
【-g 옵션】
-g 옵션일 때에는 -n하고는 달리 지정된 MIB OID하고 그 값을 되돌려줄 필요가 있다.
결과의 첫번째 행에 -n의 경우는 「다음 MIB OID」, -g의 경우는 「지정된 MIB OID」가 된다.
2010년 4월 20일 화요일
MySQL를 snmpd로 감시3
exec구현과 사용법
우선은 구현이 간단한 exec부터 설명하겠다.
장황하게 설명해도 잘 모르니 현재 MySQL서버에 접속해 있는 클라이언트 수를 snmpd가 되돌려주는 예를 들어보자.
MySQL 서버에 접속하고 있는 클라이언트 수는 SHOW STATUS SQL쿼리를 실행하던지 mysqladmin status라든지 mysqladmin processlist 로 알 수 있다.
여기에서는 SHOW STATUS를 사용해보자.
snmpd.conf에 다음과 같이 지정한다.
exec .1.3.6.1.4.1.2021.50 mysql_proc /etc/snmp/mysql_proc.sh
지정 포맷은 다음과 같다.
exec MIBOID 이름 명령어
MIB의 OID .1.3.6.1.4.1.2021.50 는 이미 사용되고 있지 않는 OID이면 뭐든지 상관없지만 엄밀히 말하면 번호는 IANA가 결정하고 있다.
사용할 수 있는 것은 .1.3.6.1.4, 즉 .iso.org.dod.internet.private , .1.3.6.1.4.1은 .iso.org.dod.internet.private.enterprise 에 할당되어 있다.
http://www.iana.org/assignments/enterprise-numbers
다음에는 MySQL서버에 접속하고 있는 수를 알 수 있는 스크립트인 /etc/snmp/mysql_proc.sh를 작성한다.
#!/bin/sh
export PATH=/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/sbin:/usr/sbin
mysql -uroot -e 'SHOW STATUS' | grep '^Threads_connected' | awk '{print $2}'
exit 0
이것은 현재 접속중인 클라이언트 수를 얻어서 그 수만큼을 표준출력으로 뿌려주는 단순한 스크립트이다.
mysql -uroot -e 'SHOW STATUS' 로 상태정보를 취득하고
grep '^Threads_connected' 로 접속 클라이언트 정보를 추출한다.
--> Threads_connected 1
마지막으로 awk로 필요한 수치만 뽑아낸다.
--> 1
확인할 때는 아래와 같이 실행한다.
shell$ snmpwalk -v 1 -c public -On 127.0.0.1 .1.3.6.1.4.1.2021.50
※현재 스크립트를 작성중에 있으므로 localhost(127.0.0.1)에 snmpd를 설치해 테스트하고 있다.
얻어지는 결과중 다음이 실행된 명령어의 종료 상태이다.
.1.3.6.1.4.1.2021.50.100.1 = INTEGER: 0
문제의 필요한 부분은 exec에서 다음과 같이 할당해서 되돌려준다.
.1.3.6.1.4.1.2021.50.101.1 = STRING: "1" => 출력의 첫째 행
.1.3.6.1.4.1.2021.50.101.2 = STRING: "1" => 출력의 둘째 행
…
.1.3.6.1.4.1.2021.50.101.N = STRING: "1" => 출력의 N번째 행(N은 자연수)
그러나 exec에서는 문제점도 있다. 그것은 얻을 수 있는 값이 전부 STRING형이라는 것이다.
SNMP클라이언트에 따라서는 값의 형이 INTEGER인가 STRING인가를 보고 동작하는 것도 있다.
따라서 잘 동작하지 않을 가능성도 있다. 문자열을 얻는 경우에는 exec를 매우 쉽게 사용할 수 있지만 숫자만 얻어야하는 경우에는 좀 문제가 될 수 있다.
이런 경우에는 pass를 이용한다.
우선은 구현이 간단한 exec부터 설명하겠다.
장황하게 설명해도 잘 모르니 현재 MySQL서버에 접속해 있는 클라이언트 수를 snmpd가 되돌려주는 예를 들어보자.
MySQL 서버에 접속하고 있는 클라이언트 수는 SHOW STATUS SQL쿼리를 실행하던지 mysqladmin status라든지 mysqladmin processlist 로 알 수 있다.
여기에서는 SHOW STATUS를 사용해보자.
snmpd.conf에 다음과 같이 지정한다.
exec .1.3.6.1.4.1.2021.50 mysql_proc /etc/snmp/mysql_proc.sh
지정 포맷은 다음과 같다.
exec MIBOID 이름 명령어
MIB의 OID .1.3.6.1.4.1.2021.50 는 이미 사용되고 있지 않는 OID이면 뭐든지 상관없지만 엄밀히 말하면 번호는 IANA가 결정하고 있다.
사용할 수 있는 것은 .1.3.6.1.4, 즉 .iso.org.dod.internet.private , .1.3.6.1.4.1은 .iso.org.dod.internet.private.enterprise 에 할당되어 있다.
http://www.iana.org/assignments/enterprise-numbers
다음에는 MySQL서버에 접속하고 있는 수를 알 수 있는 스크립트인 /etc/snmp/mysql_proc.sh를 작성한다.
#!/bin/sh
export PATH=/usr/local/mysql/bin:/usr/local/bin:/usr/bin:/bin:/sbin:/usr/sbin
mysql -uroot -e 'SHOW STATUS' | grep '^Threads_connected' | awk '{print $2}'
exit 0
이것은 현재 접속중인 클라이언트 수를 얻어서 그 수만큼을 표준출력으로 뿌려주는 단순한 스크립트이다.
mysql -uroot -e 'SHOW STATUS' 로 상태정보를 취득하고
grep '^Threads_connected' 로 접속 클라이언트 정보를 추출한다.
--> Threads_connected 1
마지막으로 awk로 필요한 수치만 뽑아낸다.
--> 1
확인할 때는 아래와 같이 실행한다.
shell$ snmpwalk -v 1 -c public -On 127.0.0.1 .1.3.6.1.4.1.2021.50
※현재 스크립트를 작성중에 있으므로 localhost(127.0.0.1)에 snmpd를 설치해 테스트하고 있다.
얻어지는 결과중 다음이 실행된 명령어의 종료 상태이다.
.1.3.6.1.4.1.2021.50.100.1 = INTEGER: 0
문제의 필요한 부분은 exec에서 다음과 같이 할당해서 되돌려준다.
.1.3.6.1.4.1.2021.50.101.1 = STRING: "1" => 출력의 첫째 행
.1.3.6.1.4.1.2021.50.101.2 = STRING: "1" => 출력의 둘째 행
…
.1.3.6.1.4.1.2021.50.101.N = STRING: "1" => 출력의 N번째 행(N은 자연수)
그러나 exec에서는 문제점도 있다. 그것은 얻을 수 있는 값이 전부 STRING형이라는 것이다.
SNMP클라이언트에 따라서는 값의 형이 INTEGER인가 STRING인가를 보고 동작하는 것도 있다.
따라서 잘 동작하지 않을 가능성도 있다. 문자열을 얻는 경우에는 exec를 매우 쉽게 사용할 수 있지만 숫자만 얻어야하는 경우에는 좀 문제가 될 수 있다.
이런 경우에는 pass를 이용한다.
2010년 4월 6일 화요일
MySQL를 snmpd로 감시2
Linux의 net-snmp(ucd-snmp)에서는 snmpd.conf를 고쳐서 필요한 프로그램(명령어나 스크립트)의 실행결과를 snmpd(SNMP서버)에 건네는 것이 가능하다.
이것을 수행하기 위해서는 snmpd.conf에 exec키워드나 pass 키워드를 사용해서 실행하는 명령어를 지정한다. exec도 pass도 실행되는 명령어는 결과를 표준출력에 출력하는 것이 전제조건이다. ( 다시 말해 snmpd의 입장에서 보면 명령실행결과는 표준입력으로 얻는다는 것을 알 수 있다. )
exec와 pass의 차이는 다음과 같다.
- exec: 여러행에 걸쳐서 문자열을 리턴하는 경우에 편리. 리턴값의 타입은 STRING으로 고정
- pass: 리턴값의 타입을 STRING이외로 하고 싶은 경우에 사용. snmpd에 건네는 결과에는 exec보다도 고민이 필요하다.
2010년 3월 18일 목요일
MySQL를 snmpd로 감시1
서버의 상태를 정보수집하는 경우, SNMP가 잘 사용된다. 관리업무에 사용되는 SNMP클라이언트 툴로서는 MRTG가 유명하다.
MRTG에서 mysqladmin 명령어나 mysql명령어를 실행해서 정보수집하는 방법도 있지만 이것은 다음과 같은 점에서 좋지 않는 면도 있다.
- mysqladmin명령어나 mysql명령어를 실행하기위해 클라이언트가 되는 MRTG가 있는 머신과 MySQL서버간의 통신이 허용되는 케이스가 적다.
- 힘들여 MySQL서버가 움직이는 컴퓨터에 SNMP서버를 돌리게 했는데도 그것과는 다른 별도의 방법으로 MySQL서버 정보를 취득하는 것도 거시기하다.
[클라이언트] [서버]
SNMP클라이언트 -------------------------------->SNMP서버(->mysql명령->MySQL서버)
SNMP서버가 MySQL정보를 보내주는 것이 가능하다면 간단하게 해결될 것이다.
여기에서는 Linux 2.6과 net-snmp 5.1.2를 사용해서 설명한다. 물론 ucd-snmp계열이라면 통용되는 이야기가 된다.
또, Debian GNU/Linux 3.1 sarge에서는 다음과 같이 조작하는 것으로 SNMP서버와 클라이언트 양쪽이 인스톨된다.
Debian패키지 인스톨
root@shell# aptitude install snmp snmpd
2010년 3월 7일 일요일
튜닝을 위한 팁(InnoDB)
1.데이터 파일, REDO로그 파일을 별도의 디스크에 보존
innodb_data_file_path, innodb_log_group_home_dir
파일을 물리적으로 다른 디스크에 배치함으로 I/O처리를 분산 시킬 수 있다.
2.데이터 파일은 autoextend로 하지 않기
innodb_data_file_path
자동확장을 수행하지않게 함으로 I/O처리를 줄인다.
3. 데이터파일은 2개 이상으로 한다.
innodb_data_file_path
테이블 영역을 구성하는 데이터 파일이 1개면 성능이 나오지 않는다. 데이터 파일을 여러개로 하자.
4. 캐쉬
innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size
기본값은 보통은 작다. 이것들은 공유메모리이다.
innodb_buffer_pool_size는 레코드나 인덱스의 캐쉬등에 사용되므로 가능한한 크게 설정한다.
innodb_additional_mem_pool_size는 데이터 사전등의 보존을 위해서 사용되지만 20MB정도로 할당한다.
5. 한개의 트랜잭션의 크기를 고려해서 REDO로그 파일의 크기나 버퍼의 크기를 결정한다.
innodb_log_buffer_size, innodb_log_file_size, innodb_log_files_in_group
너무 작으면 디스크에 써넣는 횟수가 많아지게 된다. 또 innodb_log_file_size * innodb_log_files_in_group * 70%를 넘는 정도의 써넣기가 buffer pool에서 이루어지면 체크포인트가 수행되어져 버린다. innodb_buffer_pool_size이외에도 REDO로그를 고려해야한다.
기본값에서는 보통 작다.
6. innodb_doublewrite를 무효로 한다.
장애에 잘 견디기 위해서 2개의 장소에 데이터를 써넣기를 하고 있다. ( 기본값은 innodb_doublewrite = ON ) 이것을 무효로 한다. ( --skip-innodb-doublewrite )
7. innodb_max_dirty_pages_pct
InnoDB의 buffer pool중에 innodb_max_dirty_pages_pct(%)가 dirty한 부분인 경우, 체크포인트가 수행된다. buffer pool을 크게 할 수 없는 경우 , 이 값을 크게 해서 조금이나마 체크포인트를 지연시키는 방법도 있다.
그러나 이 값을 변경하는 것보다 innodb_buffer_pool_size의 값이나 innodb_log_file_size * innodb_log_files_in_group의 값을 늘리는 게 효과적이다.
8. innodb_thread_concurrency
InnoDB의 처리를 수행하는 스레드 수를 지정한다. 너무 많이 늘려놓으면 반대로 스레드 사이에 기다리는 시간이 늘어나게 된다. 이것은 운영체제의 SMP나 스레드의 구현에도 영향받게되므로 어떤 운영체제에서는 잘 돌아갔던 설정값이더라도 다른 운영체제에서는 성능이 나오지않는 경우도 있을 수 있다. 4,8,16 정도의 값으로 시도해본다.
9. flush
innodb_flush_method, innodb_flush_log_at_trx_commit
flush방법, 타이밍을 지정한다. 대부분의 경우 innodb_flush_method를 O_DSYNC로 변경한다. 또 innodb_flush_log_at_trx_commit은 기본(COMMIT할 때 flush하기 )값으로 운용하는게 많다.
innodb_data_file_path, innodb_log_group_home_dir
파일을 물리적으로 다른 디스크에 배치함으로 I/O처리를 분산 시킬 수 있다.
2.데이터 파일은 autoextend로 하지 않기
innodb_data_file_path
자동확장을 수행하지않게 함으로 I/O처리를 줄인다.
3. 데이터파일은 2개 이상으로 한다.
innodb_data_file_path
테이블 영역을 구성하는 데이터 파일이 1개면 성능이 나오지 않는다. 데이터 파일을 여러개로 하자.
4. 캐쉬
innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size
기본값은 보통은 작다. 이것들은 공유메모리이다.
innodb_buffer_pool_size는 레코드나 인덱스의 캐쉬등에 사용되므로 가능한한 크게 설정한다.
innodb_additional_mem_pool_size는 데이터 사전등의 보존을 위해서 사용되지만 20MB정도로 할당한다.
5. 한개의 트랜잭션의 크기를 고려해서 REDO로그 파일의 크기나 버퍼의 크기를 결정한다.
innodb_log_buffer_size, innodb_log_file_size, innodb_log_files_in_group
너무 작으면 디스크에 써넣는 횟수가 많아지게 된다. 또 innodb_log_file_size * innodb_log_files_in_group * 70%를 넘는 정도의 써넣기가 buffer pool에서 이루어지면 체크포인트가 수행되어져 버린다. innodb_buffer_pool_size이외에도 REDO로그를 고려해야한다.
기본값에서는 보통 작다.
6. innodb_doublewrite를 무효로 한다.
장애에 잘 견디기 위해서 2개의 장소에 데이터를 써넣기를 하고 있다. ( 기본값은 innodb_doublewrite = ON ) 이것을 무효로 한다. ( --skip-innodb-doublewrite )
7. innodb_max_dirty_pages_pct
InnoDB의 buffer pool중에 innodb_max_dirty_pages_pct(%)가 dirty한 부분인 경우, 체크포인트가 수행된다. buffer pool을 크게 할 수 없는 경우 , 이 값을 크게 해서 조금이나마 체크포인트를 지연시키는 방법도 있다.
그러나 이 값을 변경하는 것보다 innodb_buffer_pool_size의 값이나 innodb_log_file_size * innodb_log_files_in_group의 값을 늘리는 게 효과적이다.
8. innodb_thread_concurrency
InnoDB의 처리를 수행하는 스레드 수를 지정한다. 너무 많이 늘려놓으면 반대로 스레드 사이에 기다리는 시간이 늘어나게 된다. 이것은 운영체제의 SMP나 스레드의 구현에도 영향받게되므로 어떤 운영체제에서는 잘 돌아갔던 설정값이더라도 다른 운영체제에서는 성능이 나오지않는 경우도 있을 수 있다. 4,8,16 정도의 값으로 시도해본다.
9. flush
innodb_flush_method, innodb_flush_log_at_trx_commit
flush방법, 타이밍을 지정한다. 대부분의 경우 innodb_flush_method를 O_DSYNC로 변경한다. 또 innodb_flush_log_at_trx_commit은 기본(COMMIT할 때 flush하기 )값으로 운용하는게 많다.
2010년 3월 3일 수요일
튜닝을 위한 팁(MyISAM)
1.캐쉬
key_buffer_size, preload_buffer_size등의 캐쉬를 수정해 본다. key_buffer_size는 되도록이면 많이 할당한다. 이것은 공유메모리이다. 메모리가 충분히 있다면 myisam_use_mmap를 사용해서 모든 MyISAM테이블을 메모리에 매핑하는 것도 검토해 본다.
2.데이터 테이블, 인덱스 파일을 별도의 디스크에 보존한다.
I/O의 분산처리에 도움이 된다. CREATE TABLE문장에 DATA DICTIONARY, INDEX DICTIONARY를 지정하면 MYD파일, MYI파일의 보존 디렉토리를 변경할 수 있다.
3.써넣기하지 않는 테이블은 압축
써넣기가 없는 테이블은 "myisampack -v 테이블명", "myisamchk -rq 테이블"를 실행해서 테이블을 압축한다. 압축하면 파일사이즈도 작게되고 그로인해 디스크로부터 읽어들이는 양도 줄어 들게 되므로 경우에 따라서는 속도향상도 기대된다.
key_buffer_size, preload_buffer_size등의 캐쉬를 수정해 본다. key_buffer_size는 되도록이면 많이 할당한다. 이것은 공유메모리이다. 메모리가 충분히 있다면 myisam_use_mmap를 사용해서 모든 MyISAM테이블을 메모리에 매핑하는 것도 검토해 본다.
2.데이터 테이블, 인덱스 파일을 별도의 디스크에 보존한다.
I/O의 분산처리에 도움이 된다. CREATE TABLE문장에 DATA DICTIONARY, INDEX DICTIONARY를 지정하면 MYD파일, MYI파일의 보존 디렉토리를 변경할 수 있다.
3.써넣기하지 않는 테이블은 압축
써넣기가 없는 테이블은 "myisampack -v 테이블명", "myisamchk -rq 테이블"를 실행해서 테이블을 압축한다. 압축하면 파일사이즈도 작게되고 그로인해 디스크로부터 읽어들이는 양도 줄어 들게 되므로 경우에 따라서는 속도향상도 기대된다.
2010년 2월 25일 목요일
튜닝을 위한 팁(전체)
1. 쿼리와 테이블 구조
slow query log에 기록되는 쿼리를 수정한다. 또 인덱스도 적절하게 작성한다.
JOIN 이나 GROUP BY 등으로 임시 테이블을 사용하는 빈도를 낮춘다.
2. 쿼리 캐쉬 사용
같은 SELECT문이 실행된다면 효과가 기대된다.
3. 바이너리 로그 파일등은 별도의 디스크에 보존
I/O를 분산처리한다.
4. prealloc영역
한개의 스레드로 많은 쿼리를 처리해야하는 경우 사용할 수 있는 변수이다.
쿼리 해석등에 사용하는 메모리는 그 때마다 할당되고 해제되지만 prealloc변수는 미리 할당한 영역을 해제하지 않고 그 다음 처리에도 사용한다. prealloc변수에는 다음과 같은 것이 있다.
query_prealloc_size
transaction_prealloc_size
5. 메모리 처리량에 주의
sort_buffer_size , join_buffer_size, read_buffer_size, read_rnd_buffer_size 를 늘리면
쿼리의 처리가 빨라질 수 있지만 너무 많이 늘리게 되면 메모리가 부족하게 되므로 조심해야한다. ( 이것들은 스레드단위로 메모리가 할당된다. )
6. 스레드 캐쉬, Listen
접속이나 절단이 많은 경우는 thread_cache_size를 늘려서 재이용할 수 있는 스레드 수를 확보한다. MySQL은 접속이 있을 때마다 자식 스레드를 작성하고 절단할 때에 그 자식 스레드를 파기한다. 이것을 몇개정도는 파기하지 않고 새로운 접속에 준비에 둘까하는 것을 지정하는 것이 thread_cache_size이다. 또, back_log로 Listen의 수를 늘려두면 접속반응이 좋아진다.
7. 테이블 핸들러의 확보
동시접속수가 많은 경우, 한번에 사용할 수 있는 테이블 핸들러의 최대수를 table_cache 지정으로 늘린다. 모든 클라이언트가 사용할 정도의 테이블 수만큼 확보해두면 낫지않을지 싶다. 이것이 적을 경우 테이블 핸들러의 close, open이 증가한다. MySQL설정예에서는 1024, 2048등이 사용되고 있다.
slow query log에 기록되는 쿼리를 수정한다. 또 인덱스도 적절하게 작성한다.
JOIN 이나 GROUP BY 등으로 임시 테이블을 사용하는 빈도를 낮춘다.
2. 쿼리 캐쉬 사용
같은 SELECT문이 실행된다면 효과가 기대된다.
3. 바이너리 로그 파일등은 별도의 디스크에 보존
I/O를 분산처리한다.
4. prealloc영역
한개의 스레드로 많은 쿼리를 처리해야하는 경우 사용할 수 있는 변수이다.
쿼리 해석등에 사용하는 메모리는 그 때마다 할당되고 해제되지만 prealloc변수는 미리 할당한 영역을 해제하지 않고 그 다음 처리에도 사용한다. prealloc변수에는 다음과 같은 것이 있다.
query_prealloc_size
transaction_prealloc_size
5. 메모리 처리량에 주의
sort_buffer_size , join_buffer_size, read_buffer_size, read_rnd_buffer_size 를 늘리면
쿼리의 처리가 빨라질 수 있지만 너무 많이 늘리게 되면 메모리가 부족하게 되므로 조심해야한다. ( 이것들은 스레드단위로 메모리가 할당된다. )
6. 스레드 캐쉬, Listen
접속이나 절단이 많은 경우는 thread_cache_size를 늘려서 재이용할 수 있는 스레드 수를 확보한다. MySQL은 접속이 있을 때마다 자식 스레드를 작성하고 절단할 때에 그 자식 스레드를 파기한다. 이것을 몇개정도는 파기하지 않고 새로운 접속에 준비에 둘까하는 것을 지정하는 것이 thread_cache_size이다. 또, back_log로 Listen의 수를 늘려두면 접속반응이 좋아진다.
7. 테이블 핸들러의 확보
동시접속수가 많은 경우, 한번에 사용할 수 있는 테이블 핸들러의 최대수를 table_cache 지정으로 늘린다. 모든 클라이언트가 사용할 정도의 테이블 수만큼 확보해두면 낫지않을지 싶다. 이것이 적을 경우 테이블 핸들러의 close, open이 증가한다. MySQL설정예에서는 1024, 2048등이 사용되고 있다.
2010년 1월 31일 일요일
MySQL - 모니터링8
- NOOP : 단순한 FIFO(First In/First Out) 이다. I/O스케줄러의 큐에 순서대로 I/O 요청이 저장되어 처리된다.
- Deadline: I/O 요청의 순서를 바꿈으로 I/O 퍼포먼스 향상을 목표로 한다. READ와 WRITE용으로 각각 큐를 가지고 있어 큐는 두개의 리스트로 관리된다. 큐 시간으로 소트된 것과 섹터 개시위치로 소트된 것이다. FIFO 리스트에 있는 최초의 요청이 READ로는 500msec, WRITE로는 5sec이상 기다리게 되면 그 큐를 우선적으로 처리한다.
- Anticipatory: READ 요청의 처리 마지막에 다음의 요청이 지금과 가까운 위치에서의 처리를 수행할 것인가를 확인한다. ( 지금 처리한 근처로의 액세스가 있는 것을 기대해서) 만약 가깝다면 곧 그 요청을 dispatch한다. 디스크 헤드가 한개밖에 없는 것을 전제로 하고 있다.
- CFQ(Completely Fair Queuing): 모든 I/O요청이 공평하게 처리될 것을 목적으로 하고 있다. 프로세스 단위, 프로세스 그룹 단위, 유저 단위로 처리가 공평하게 이루어지도록 한다. 요청의 발신지 모두에 대해서 각각 고유의 큐를 준비한다 이 큐를 라운드로빈으로 처리한다.
피드 구독하기:
글 (Atom)
