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 //

Event Scheduler 2

이벤트는 SHOW문을 실행하던지 INFORMATION_SCHEMA.EVENTS테이블을 SELECT하는 것으로 확인가능하다.

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로 표시됨.

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

쿼리캐쉬로 성능개선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　쿼리캐쉬는 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