본문 바로가기

카테고리 없음

MYSQL 5.5 Event v1.0

MYSQL 5.5 Event

 

Date

Ver

Etc.

12.01.09

 

 

 

 

 

 

 

 

 

 

1.    MySQL Event

이벤트는 스케쥴에 따라 작업을 수행한다. ORACLE ‘Job’ 이나 ‘Scheduler’ 와 동일한 기능을 한다.

기본적으로 Unix crontab (cron job 으로 알려짐) 이나 Windows Task Scheduler 와도 비슷하다.

 

MySQL Event 는 다음과 같은 특징을 가진다.

l  MySQL Event name schema 로 구분된다.

l  events 의 수행방법은 one-time (일회성) 이나 recurrent (주기적인) 가 될 수 있다.
recurrent
의 기본셋팅은 create 시점에 바로 수행되며, disabled drop 되기 전까지 반복수행된다.

l  반복되는 event interval 안에 종료하지 않으면, 동시수행되는 결과가 생길 수 있다.
이를 회피하기 위한 방안으로 GET_LOCK() function 을 사용할 수 있다.

l  event default definer event 가 변경되지 않은 한 (alterd) 생성한 유저이다. alterd event
경우 definer alter 구문을 수행한 유저이다.

 

2.    Event Scheduler Configuration

Event ‘event scheduler thread’ 에 의해 수행된다. event scheduler   thread 와 현재상태는 ‘event’ 권한을 가진 유저의 ‘show processlist’ 결과로 알 수 있다.

 

Global ‘event_scheduler’ system variable event scheduler 의 사용가능 여부를 결정한다. 이 값은 3가지 값을 가진다.

 

l  OFF – Event Scheduler 는 정지되어 있음. show processlist 결과를 볼 수 없다. event_scheduler 의 기본 값이다.

l  ON – Event Scheduler 가 시작되어 있음.

l  DISABLED – Event Scheduler 가 수행 불가능함을 의미. 이 상태는 runtime 에 변경하지 못한다.

 

event scheduler 의 비활성화 (disabled) 는 다음과 같은 방법을 사용할 수 있다.

 

l  Starting option : --event-scheduler=DISABLED

l  my.cnf : in a [mysqld] section : event_scheduler=DISABLED

 

event_scheduler 의 상태는 ON | OFF | 1 (ON) | OFF (0) 과 같은 Value 로도 컨트롤 할 수 있다.

 

3.    Event Syntax

A.     CREATE EVENT

<one-time style>

create event myevent1

on schedule at ‘2011-01-10 15:00:00’

do insert into test.totalse values(‘myevent1’,now());

 

<recurrent style>

#1

CREATE EVENT myevent2

on schedule at current_timestamp + interval 1 hour

do insert into test.totalse values(‘myevent2’,now());

 

#2

CREATE EVENT myevent3

on schedule every 1 hour

do insert into test.totalse values(‘myevent3’,now());

 

 

B.     ALTER EVENT

alter event myevent 3

on schedule every 2 hour starts current_timestamp + interval 4 hour;

 

alter event myevent3 disable;

 

alter event myevent3 rename to myevent5;

 

alter event a.myevent3 rename to b.myevent3;

 

C.     DROP EVENT

DROP EVENT [IF EXISTS] EVENT_NAME

 

4.    Event Metadata

Event Metadata 를 확인하는 방법은 다음과 같다.

 

l  mysql.event 를 조회

l  information_schema.events 를 조회

l  show create events 의 사용

l  show events 를 사용

 

5.    The Event Scheduler and MySQL Privilleges

global event_scheduler system variable 을 수정하기 위해서는 super 권한이 필요하다.

 

event 는 이를 정의한 definer 의 권한을 가지고 수행된다. 때문에 definer 가 필요한 권한이 없는 경우 아무 동작을 하지 못한다.

 

6.    Event 의 사용 (실습)

<event 를 사용할 user 생성>

 

mysql> grant insert, event on *.* to 'hello'@'localhost' identified by 'hi';

Query OK, 0 rows affected (0.00 sec)

 

<event 가 사용할 테이블 생성>

 

mysql> use test_case;

Database changed

mysql> create table test(dt date);

Query OK, 0 rows affected (0.03 sec)

 

<create event>

 

[mysql@dg1 mysql]$ ./bin/mysql -uhello –p

 

mysql> create event myevent

    -> on schedule every 1 second

    -> do insert into test_case.test values(now());

 

위 상태에서는 event 가 동작하지 않는다. system variable ‘event_scheduler’ ‘on’ 으로 변경해야 한다.

 

mysql> set global event_scheduler=on;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test_case.test;

+------------+

| dt         |

+------------+

| 2012-01-10 |

| 2012-01-10 |

| 2012-01-10 |

| 2012-01-10 |

| 2012-01-10 |

| 2012-01-10 |

+------------+

 

<alter event>

 

mysql> alter event test_case.myevent disable;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select name, status from event;

+---------+----------+

| name    | status   |

+---------+----------+

| myevent | DISABLED |

+---------+----------+

1 row in set (0.00 sec)

 

<Metadata>

-      mysql.event

mysql> select db, name, body from event;

+-----------+---------+------------------------------------------+

| db        | name    | body                                     |

+-----------+---------+------------------------------------------+

| test_case | myevent | insert into test_case.test values(now()) |

+-----------+---------+------------------------------------------+

1 row in set (0.00 sec)

 

-      information_schema.events

 

mysql> select event_schema, event_name, event_body, event_definition from events;

+--------------+------------+------------+------------------------------------------+

| event_schema | event_name | event_body | event_definition                         |

+--------------+------------+------------+------------------------------------------+

| test_case    | myevent    | SQL        | insert into test_case.test values(now()) |

+--------------+------------+------------+------------------------------------------+

1 row in set (0.00 sec)

 

 

-      show events

mysql> show events\G

*************************** 1. row ***************************

                  Db: test_case

                Name: myevent

             Definer: root@localhost

           Time zone: SYSTEM

                Type: RECURRING

          Execute at: NULL

      Interval value: 1

      Interval field: SECOND

              Starts: 2012-01-10 13:49:03

                Ends: NULL

              Status: DISABLED

          Originator: 1

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

 

-      show create event

mysql> show create event myevent\G

*************************** 1. row ***************************

               Event: myevent

            sql_mode:

           time_zone: SYSTEM

        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `myevent` ON SCHEDULE EVERY 1 SECOND STARTS '2012-01-10 13:49:03' ON COMPLETION NOT PRESERVE DISABLE DO insert into test_case.test values(now())

character_set_client: utf8

collation_connection: utf8_general_ci

  Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

 

7.    Latency in executing event

가정 : 1 (MySQL shutdown 되어 있는 상황)

 

여기서의 가정사항은 인터벌에 대해 수행 못한 동작들이 누적되지 않을까 하는 것이다.

다시 말해 1분마다 수행이 되는 작업이 있는데 중간에 3분의 shutdown 이 있었다면, MySQL startup 후에

3번의 작업이 한번에 호출되나 하는 것이다.

 

확인을 위해 1분마다 수행되는 test 테이블에 now() 값을 insert 하는 event 를 만들었다.

추가로 event_scheduler system variable my.cnf 수정을 통해 on 으로 수정한 상태이다.

 

mysql> select * from test_case.test;

+---------------------+

| dt                  |

+---------------------+

| 2012-01-10 16:08:48 |

| 2012-01-10 16:09:48 |

| 2012-01-10 16:10:48 |

| 2012-01-10 16:11:48 |

| 2012-01-10 16:19:48 |

| 2012-01-10 16:20:48 |

| 2012-01-10 16:21:48 |

| 2012-01-10 16:22:48 |

| 2012-01-10 16:23:48 |

| 2012-01-10 16:24:48 |

| 2012-01-10 16:25:48 |

| 2012-01-10 16:26:48 |

+---------------------+

12 rows in set (0.00 sec)

 

결과를 보면 shutdown 이 발생했던 시각에 대해 단순히 event 가 호출되지 않았음을 알 수 있다.

 

가정 : 2 (Table Lock 경합)

 

여기서의 가정사항은 MySQL 의 작업을 이루는 SQL Table Lock 경합으로 인해 지연되는 상황이다.

MySQL CTAS, Insert-select 작업 시 CTAS S 의 테이블, INSERT-SELECT SELECT 테이블에 대해 UPDATE, DELETE 하지 못한다.

 

아래 예제에서는 myevent4 라는 event 를 사용하였다.

이 테이블은 test3 (258만건) 의 데이터를 하나 삭제하고, 타임정보를 test4 insert 하는 event 이다.

 

CTAS 를 이용해 test3 테이블을 test5 로 복제하는 경우 test4 에 기록된 타임정보가 어떻게 되는가를 보겠다.

 

mysql> set global event_scheduler=on;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create event myevent4 on schedule every 1 second

    -> do

    -> begin

    -> delete from test3 limit 1;

    -> insert into test4 values(now());

    -> end|

Query OK, 0 rows affected (0.00 sec)

 

 

mysql> create table test5 as select * from test3;

Query OK, 2586579 rows affected (15.90 sec)

Records: 2586579  Duplicates: 0  Warnings: 0

 

mysql> set global event_scheduler=off;

Query OK, 0 rows affected (0.00 sec)

 

 

mysql> select * from test4;

+---------------------+

| dt                  |

+---------------------+

~~ (중략) ~~

| 2012-01-11 10:49:40 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:57 |

| 2012-01-11 10:49:58 |

~~ (중략) ~~

 

test4 의 타임정보를 보니 10:49:57 데이터가 중첩되는 것을 알 수 있다.

event 자체를 매초 수행하도록 만들었고, 그 작업 내용도 시간이 안 걸리는 작업이기에 위 데이터의 의미는 작업에 지연이 발생했다는 것이다.

 

CTAS 를 다시 수행해보면서 다른 세션에서 processlist 를 확인해 보았다.

 

l  CTAS 수행 전

 

mysql> show processlist;

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

| Id  | User            | Host      | db        | Command | Time | State                       | Info             |

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

|   1 | root            | localhost | test_case | Sleep   |    3 |                             | NULL             |

| 435 | root            | localhost | NULL      | Query   |    0 | NULL                        | show processlist |

| 436 | event_scheduler | localhost | NULL      | Daemon  |    1 | Waiting for next activation | NULL             |

+-----+-----------------+-----------+-----------+---------+------+-----------------------------+------------------+

3 rows in set (0.00 sec)

 

l  CTAS 수행 중

 

mysql> show processlist;

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

| Id   | User            | Host      | db        | Command | Time | State                       | Info                                  |

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

|    1 | root            | localhost | test_case | Query   |   11 | Sending data                | insert into test5 select * from test3 |

|  435 | root            | localhost | NULL      | Query   |    0 | NULL                        | show processlist                      |

|  436 | event_scheduler | localhost | NULL      | Daemon  |    1 | Waiting for next activation | NULL                                  |

| 1024 | root            | localhost | test_case | Connect |   11 | updating                    | delete from test3 limit 1             |

| 1025 | root            | localhost | test_case | Connect |   10 | updating                    | delete from test3 limit 1             |

| 1029 | root            | localhost | test_case | Connect |    9 | updating                    | delete from test3 limit 1             |

| 1033 | root            | localhost | test_case | Connect |    8 | updating                    | delete from test3 limit 1             |

| 1034 | root            | localhost | test_case | Connect |    7 | updating                    | delete from test3 limit 1             |

| 1038 | root            | localhost | test_case | Connect |    6 | updating                    | delete from test3 limit 1             |

| 1042 | root            | localhost | test_case | Connect |    5 | updating                    | delete from test3 limit 1             |

| 1043 | root            | localhost | test_case | Connect |    4 | updating                    | delete from test3 limit 1             |

| 1047 | root            | localhost | test_case | Connect |    3 | updating                    | delete from test3 limit 1             |

| 1051 | root            | localhost | test_case | Connect |    2 | updating                    | delete from test3 limit 1             |

| 1052 | root            | localhost | test_case | Connect |    1 | updating                    | delete from test3 limit 1             |

+------+-----------------+-----------+-----------+---------+------+-----------------------------+---------------------------------------+

14 rows in set (0.00 sec)

 

event 가 수행한 sql (delete) 이 모두 대기중이며 Time 정보를 보면 해당 동작이 매초 수행되었음을 알 수 있다.

 

8.    Cautions

table 을 지워도 이것과 연관된 event 는 삭제되지 않고 그대로 남아있다.

추가로 status disable 되지도 않는다.

mysql> show tables;

Empty set (0.00 sec)

 

mysql> show events;

+-----------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| Db        | Name     | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |

+-----------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

| test_case | myevent  | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2012-01-10 15:57:48 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

| test_case | myevent1 | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | SECOND         | 2012-01-11 10:34:25 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

| test_case | myevent2 | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | SECOND         | 2012-01-11 10:34:44 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

| test_case | myevent4 | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | SECOND         | 2012-01-11 10:46:38 | NULL | ENABLED |          1 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

+-----------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+

4 rows in set (0.00 sec)

 

 

9.    References

A.     MySQL 5.5 Reference Manual | 18.4.1. Event Scheduler Overview |
http://dev.mysql.com/doc/refman/5.5/en/events-overview.html

 

B.     MySQL 5.5 Reference Manual | 18.4.2. Event Scheduler Configuration |
http://dev.mysql.com/doc/refman/5.5/en/events-configuration.html