Docstoc

Event Scheduler

Document Sample
Event Scheduler Powered By Docstoc
					    MySQL 5.1 Features:

               The
          Event
        Scheduler

    Duleepa “Dups” Wijayawardhana
       MySQL Community Team
"                                        "                   !
                           !"#$%&#'()*#+(,-.$/#+*0#,-$1#-2
The Five Ws and some Buzz
   •What?
   • Where?
   • Why?
   • Who?
   • When?
   • Buzz Schedules
         What is the Event
           Scheduler?
• Essentially described as “Temporal Triggers”
  for the MySQL database.




• A trigger which fires at specific times or
  intervals
         What is the Event
           Scheduler?
• A simple example:
   CREATE EVENT dups_event_1

    ON SCHEDULE
       EVERY 3 SECOND
    DO
       INSERT INTO dups.test (my_timestamp)
       VALUES (UNIX_TIMESTAMP());
           Where is the
         Event Scheduler?
• New to MySQL 5.1
• Start the Event Scheduler:
  SET GLOBAL event_scheduler = ON;
  SET @@global.event_scheduler = ON;
  SET GLOBAL event_scheduler = 1;
  SET @@global.event_scheduler = 1;
            Is it running?
• Check the Process list on the MySQL
  Server




• Only shows the event_scheduler
  process, not the individual events
         Why use the
       Event Scheduler?

• Why add another place for
  scheduled applications? Why not
  use <insert OS scheduler>?
• Isn’t this more to manage?
• Is this production ready?
           Crontab*

• Complex ability to schedule any
 application to run at specific
 times. Typically looks something
 like this

 #run every 5 mins
 */5 * * * * /home/dups/myapp.sh
              Crontab*/2

• Many moving parts, each part has
    % of failure: crond, nfs, etc.
•   System dependent, system access
    dependent
• Removed from the source of
    where the changes happen
          Crontab*/3

• May be away from the DBAs
 maintenance purview and in
 Sysadmin’s hand.
• Not as portable
          Portability: Crontab
•   Potential Dependencies of the Cron:
     •   crond
     •   access to application, hard disk,
         permissions
     •   network connection
     •   MySQL Server
     •   Correct Grants
     •   Data
Portability: Event Scheduler

• Dependencies of the Event
 Scheduler:
  • MySQL Server
  • Event Scheduler
  • Correct Grants
  • Data
      Who runs the Event?
    Access Grants and all that
•   Security is similar to those of
    running any sort of stored
    procedures
• Grant “Event” to the user who
    will administer:
    GRANT EVENT ON dups.* TO dups@localhost
  Who runs the Event?
Access Grants and all that
• Note:You cannot grant for a
    specific table in a schema
• The user must have the rights to
    do whatever your procedure
    does.
•   Note: If the user who owns the
    event is deleted, it still continues
    to run with errors
When should you use the
   Event Scheduler
• Queries/Procedures that
 aggregate information or gather
 information on a regular basis
• Examples:
   • Statistics gathering
   • Data polling
   • Data checking/normalization
     When not to use the
      Event Scheduler
• KISS - If Event Scheduler makes
 things more complicated...
• Where a procedure may take up
 performance on a busy server
• Where complicated programming
 would be better
      Basic Syntax of the
       Event Scheduler
• every 3 seconds
   CREATE EVENT dups_event_1

    ON SCHEDULE
      EVERY 3 SECOND
   DO
      INSERT INTO dups.test (my_timestamp)
      VALUES (UNIX_TIMESTAMP());
         Basic Syntax of the
          Event Scheduler/2
•   Every 5 mins starting in 2 days and
    ending in a month
CREATE EVENT dups_event_2

    ON SCHEDULE
      EVERY 5 MINUTE
      STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
      ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
      ON COMPLETION PRESERVE
   DO
      ....
          Basic Syntax of the
           Event Scheduler/3
•   Calling a procedure

       CREATE EVENT dups_event_2

        ON SCHEDULE
          EVERY 5 MINUTE
        DO
          CALL proc();
    Basic Syntax of the
    Event Scheduler/4
•DROP EVENT
• ALTER EVENT
• SHOW EVENTS
Buzz
Schedules:
a use case
         Buzz Schedule/2

• Rather than
 hit the db
 constantly
 to aggregate...
 store the
 aggregate data
 every so often
           Buzz Schedule/3
•   word frequencies generated once
    a day
                     DELIMITER //
                     CREATE EVENT dups_event_forum
                     
      ON SCHEDULE

Buzz Schedule/4          EVERY 10 MINUTE
                     DO BEGIN
                     
      DECLARE pm_subject VARCHAR (100);
                     
      DECLARE pm_author VARCHAR (100);




• create an event
                     
      DECLARE pm_forum_id INT(10);
                     
      DECLARE pm_message_id INT(10);
                     
      DECLARE pm_datestamp DATETIME;
                     
      DECLARE last_id INT(10);
                            DECLARE done INTEGER;

 to read the posts   
      DECLARE f_get_posts CURSOR FOR
                               SELECT pm.subject, pm.author, pm.forum_id, pm.message_id,
                               pm.datestamp FROM phorum_messages pm
                               LEFT JOIN phorum_forums pf ON pm.forum_id = pf.forum_id

 every 10 minutes              WHERE pm.status = 2 AND pm.message_id > 0
                              AND pm.message_id = pm.thread ORDER BY pm.message_id
                              DESC LIMIT 10;


 and insert into
                            DECLARE CONTINUE HANDLER FOR
                                NOT FOUND SET done = 1;
                     

                     
      OPEN f_get_posts;


 the aggregate
                           cursor_loop: LOOP
                           FETCH f_get_posts INTO pm_subject, pm_author, pm_forum_id,
                               pm_message_id, pm_datestamp;
                           IF done=1 THEN

 table, and remove            LEAVE cursor_loop;
                           END IF;



 any older entries
                           INSERT INTO forum_recent_posts (subject, author, forum_id, message_id,
                           datestamp) VALUES (pm_subject, pm_author, pm_forum_id, pm_message_id
                           pm_datestamp);

                           SET last_id = pm_message_id;
                           END LOOP cursor_loop;

                           CLOSE f_get_posts;

                           SET done = 0;

                       DELETE FROM forum_recent_posts WHERE message_id < last_id;
                     END //
                     DELIMITER ;
                delimiter //
                CREATE PROCEDURE split_demo()
                BEGIN
                      DECLARE v_id INTEGER;

Buzz Schedule/5       DECLARE v_post TEXT;



•
                      DECLARE done INTEGER;

 create a             DECLARE cur_position INT DEFAULT 1 ;
                      DECLARE remainder TEXT;

 procedure
                      DECLARE cur_string VARCHAR(1000);
                      DECLARE delimiter_length TINYINT UNSIGNED;
                      DECLARE delimiter_string VARCHAR(10);

 which reads each     DECLARE c_posts CURSOR FOR
                           SELECT id, LCASE(post) FROM posts;


 post then splits
                      DECLARE CONTINUE HANDLER
                           FOR NOT FOUND SET done = 1;


 the words and       SET done = 0;
                     SET delimiter_string = " ";


 places them into
                     SET delimiter_length = CHAR_LENGTH(delimiter_strin

                     DELETE FROM posts_word;

 a summary table     OPEN c_posts;
                     cursor_loop:LOOP


 with a count
                        FETCH c_posts INTO v_id, v_post;
                        IF done=1 THEN
                           LEAVE cursor_loop;
                        END IF;

                           SET v_post = REPLACE(v_post, ",", "");
                           SET v_post = REPLACE(v_post, "'", "");
                                               SET   v_post   =   REPLACE(v_post,   ",", "");
                                               SET   v_post   =   REPLACE(v_post,   "'", "");
                                               SET   v_post   =   REPLACE(v_post,   "?", "");


Buzz Schedule/6                                SET
                                               SET
                                               SET
                                                     v_post
                                                     v_post
                                                     v_post
                                                              =
                                                              =
                                                              =
                                                                  REPLACE(v_post,
                                                                  REPLACE(v_post,
                                                                  REPLACE(v_post,
                                                                                    "(", "");
                                                                                    ")", "");
                                                                                    ";", "");




•
                                               SET   v_post   =   REPLACE(v_post,   "!", "");


 Check out Jay’s
                                               SET   v_post   =   REPLACE(v_post,   ":", "");
                                               SET   v_post   =   REPLACE(v_post,   ".", "");
                                               SET   v_post   =   REPLACE(v_post,   "\"", "");
                                               SET   v_post   =   REPLACE(v_post,   "\n", " ");


 Forge demo on                                 SET
                                               SET
                                                     v_post
                                                     v_post
                                                              =
                                                              =
                                                                  REPLACE(v_post,
                                                                  REPLACE(v_post,
                                                                                    "\t", " ");
                                                                                    "  ", " ");




 splitting words
                                               SET remainder = v_post;

                                               WHILE CHAR_LENGTH(remainder) > 0
                                                      AND cur_position > 0 DO


 in a procedure:
                                                      SET cur_position =
                                                             INSTR(remainder, `delimiter_string`);
                                                  IF cur_position = 0 THEN
                                                      SET cur_string = remainder;
    http://forge.mysql.com/tools/                 ELSE
                                                      SET cur_string =
                                                             LEFT(remainder, cur_position - 1);

    tool.php?id=4                                 END IF;
                                                  IF TRIM(cur_string) != '' THEN
                                                   INSERT INTO posts_word (word, word_count)
                                                      VALUES (cur_string, 1) ON DUPLICATE KEY UPDATE
                                                      word_count=word_count+1;
                                               END IF;
                                               SET remainder =
                                                      SUBSTRING(remainder, cur_position +
                                                         delimiter_length);
                                               END WHILE;
                                           END LOOP cursor_loop;
                                       CLOSE c_posts;
                                       SET done = 0;

                                    END //
                                    delimiter ;
             Buzz Schedule/7

• Next create the simple event to
  take care of this all... as a demo
  this will only run once...

 CREATE EVENT dups_event_split_demo
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND
      ON COMPLETION PRESERVE
    DO
      CALL split_demo();
 Other Notes / Some Ideas/1

• Replication: The create event
  commands are replicated
• The events are set to
  slaveside_disabled
• Expressly enable them to work on a
  particular slave
 Other Notes / Some Ideas/2

• Use the last_executed status on
 mysql.events to figure out when the
 event last ran... useful if you want to
 use that value in an event itself.
• Remember by default on expiration
 of event, the event is not preserved
        Errors and Monitoring

• Check and monitor the error log:
    090127 15:52:28 [ERROR] Event Scheduler: [root@localhost]
    [dups.dups_event_1] Table 'dups.test' doesn't exist


•   last_executed will be filled even if
    there is an error
• create an event to monitor an
    event ;)
                Use Cases and Other
                     Solutions

• Using the MySQL Event Scheduler to
    Emulate Threads - Schlomo Primak
    http://blog.shlomoid.com/2008/04/using-mysql-event-scheduler-to-emulate.html


•   FC Inter Milan Using MySQL 5.1
    Partitioning and Event Scheduler
    http://dev.mysql.com/tech-resources/articles/pandiani-use-case.html
              Use Cases and Other
                  Solutions/2
•   Using Partitioning and Event Scheduler
    to Prune Archive Tables
    http://dev.mysql.com/tech-resources/articles/partitioning-
    event_scheduler.html


•   Using the event scheduler to purge the
    process list
    http://datacharmer.blogspot.com/2008/10/using-event-scheduler-to-purge-
    process.html
              Use Cases and Other
                  Solutions/3
•   Using the event scheduler with OS
    commands
    http://datacharmer.blogspot.com/2008/03/using-event-scheduler-with-os-
    commands.html


•   The above is an innovative use in
    conjunction with MySQL Proxy.
CREATE EVENT dups_presentation_end
  ON SCHEDULE
    AT CURRENT_TIMESTAMP
DO                               And in the end...
  BEGIN
  DECLARE beer INTEGER;
  DECLARE answer TEXT;
  DECLARE ask CURSOR FOR
    SELECT questions FROM audience;
  DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET beer = 1;

  OPEN ask;
  cursor_loop: LOOP;
     FETCH ask INTO answer;
  IF beer=1 THEN
     LEAVE cursor_loop;
  END IF;
  END LOOP cursor_loop;
  END;
      Duleepa “Dups”
      Wijayawardhana
      dups@sun.com

Slides will be made available

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:18
posted:2/21/2011
language:English
pages:35