Delayed Jobs MySQL stats extraction

delayed_stats.sql

DROP FUNCTION IF EXISTS dj_extract_active_job_class_from_handler;
CREATE FUNCTION dj_extract_active_job_class_from_handler(handler TEXT)
    RETURNS TEXT DETERMINISTIC
    RETURN SUBSTRING_INDEX(
            SUBSTRING_INDEX(handler, '\n  job_id: ', 1),
            'job_class: ', -1);

DROP FUNCTION IF EXISTS dj_extract_class_method_from_handler;
CREATE FUNCTION dj_extract_class_method_from_handler(handler TEXT)
    RETURNS TEXT DETERMINISTIC
    BEGIN
    DECLARE s TEXT;
    SET s = SUBSTRING_INDEX(handler, 'object: !ruby/class ', -1);
    SET s = SUBSTRING_INDEX(s, '\n', 2);
    SET s = REPLACE(s, '\'', '');
    SET s = REPLACE(s, '\nmethod_name: :', '::'); # period preferable, but special in graphite
    RETURN s;
    END;

DROP FUNCTION IF EXISTS dj_extract_object_method_from_handler;
CREATE FUNCTION dj_extract_object_method_from_handler(handler TEXT)
    RETURNS TEXT DETERMINISTIC
    BEGIN
    DECLARE class TEXT;
    DECLARE method TEXT;
    SET class = SUBSTRING_INDEX(handler, '\n', 2);
    SET class = SUBSTRING_INDEX(class, 'ruby/object:', -1);
    SET method = SUBSTRING_INDEX(handler, 'method_name: :', -1);
    SET method = SUBSTRING_INDEX(method, '\n', 1);
    RETURN CONCAT(class, '#', method);
    END;

DROP FUNCTION IF EXISTS dj_extract_delayed_job_from_handler;
CREATE FUNCTION dj_extract_delayed_job_from_handler(handler TEXT)
    RETURNS TEXT DETERMINISTIC
    BEGIN
    DECLARE s TEXT;
    SET s = SUBSTRING_INDEX(handler, '--- !ruby/object:', -1);
    SET s = SUBSTRING_INDEX(s, '\n', 1);
    RETURN s;
    END;

DROP FUNCTION IF EXISTS dj_extract_type_from_handler;
CREATE FUNCTION dj_extract_type_from_handler(handler TEXT)
  RETURNS TEXT DETERMINISTIC
  IF INSTR(handler,'job_class') THEN
      RETURN dj_extract_active_job_class_from_handler(handler);
  ELSEIF INSTR(handler,'Delayed::PerformableMethod\nobject: !ruby/class') THEN
      RETURN dj_extract_class_method_from_handler(handler);
  ELSEIF INSTR(handler,'Delayed::PerformableMethod\nobject: !ruby/object') THEN
      RETURN dj_extract_object_method_from_handler(handler);
  ELSEIF INSTR(handler,'--- !ruby/object:') THEN
      RETURN dj_extract_delayed_job_from_handler(handler);
  ELSE
      RETURN 'Unknown';
  END IF;

#
# This gives us the normalised names we need
#

CREATE OR REPLACE VIEW delayed_jobs_for_stats AS
SELECT dj_extract_type_from_handler(dj.handler) AS type,
       dj.*
FROM delayed_jobs AS dj;

#
# This view looks at the processable backlog of work and its state
#

CREATE OR REPLACE VIEW delayed_jobs_queued AS
SELECT delayed_jobs_for_stats.*,
       TIMESTAMPDIFF(SECOND, run_at, NOW()) AS time_in_queue
FROM delayed_jobs_for_stats
WHERE failed_at IS NULL
  AND run_at <= NOW();

#
# This view looks at future scheduled work in the queue
#

CREATE OR REPLACE VIEW delayed_jobs_scheduled AS
SELECT delayed_jobs_for_stats.*,
       TIMESTAMPDIFF(SECOND, NOW(), run_at) AS time_to_sheduled
FROM delayed_jobs_for_stats
WHERE failed_at IS NULL
  AND run_at > NOW();

#
# This makes querying from our diamond script straightforward
# and gives us directly useful observations from the database
#

CREATE OR REPLACE VIEW delayed_jobs_queued_stats AS
SELECT type,
       COUNT(id)          AS queued_count,
       AVG(time_in_queue) AS avg_time_in_queue,
       SUM(attempts)      AS failed_attempts
FROM delayed_jobs_queued
GROUP BY type;