MySQL 5.1 events
Saturday, April 8, 2006 at 2:11AM 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:
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.
mysql
Reader Comments (1)