MySQL 5.1 events

in

I finally got around to working with the 5.1 scheduler.  I wanted to have a simple but non-trivial example, and when I saw Brian Akers post on the new processlist table, I thought of a useful little application:  I would submit an event that would summarize the users and their statuses at regular intervals so I could track user trends.

First off,  I needed to enable the scheduler by adding the following line to my configuration file:

event_scheduler=1

Now the scheduler is ready for action.  So I created a table to hold my process list details:

CREATE TABLE processhistory (h_timestamp DATETIME,
                             processcount INTEGER,
                             activecount INTEGER,
                             lockedcount INTEGER)$$

And then the DML to create an event:

CREATE EVENT evt_process_history
     ON SCHEDULE EVERY 2 MINUTE
     DO
BEGIN
    INSERT INTO processhistory (h_timestamp,processcount,
            activecount,lockedcount)
     SELECT NOW() AS h_timestamp,COUNT(*) AS processcount,
            SUM(active) AS activecount  ,
            SUM(locked) AS lockedcount
       FROM (SELECT CASE command WHEN 'Sleep' THEN 0 ELSE 1
                     END AS active ,
                    CASE state WHEN 'Locked' THEN 1 ELSE 0
                     END AS locked
               FROM information_schema.`PROCESSLIST` P) Q;
END$$

Every two minutes, the event summarizes the status of the sessions currently connected and stores them to the table.  I could use various tools to analze this data, but for convenience I used Excel with the ODBC driver to create a chart of activity:

Chart_1

Cool! Now I can keep track of active sessions over time, which could be useful. On the test database, there is a little ruby program that locks up a table needed by my java TP simulation, so we see those spikes of lock activity. I'm hoping that MySQL expose the SHOW STATUS command as a table as well, since we can't get at the contents of SHOW STATUS from within the stored program language.