Skip to content

Instantly share code, notes, and snippets.

@morgo
Last active December 30, 2015 20:28
Show Gist options
  • Save morgo/7880479 to your computer and use it in GitHub Desktop.
Save morgo/7880479 to your computer and use it in GitHub Desktop.
Testing performance schema - recreating show_profiles.
/*
* Procedure: show_profiles()
*
* Versions: 5.6.x
*
* Emulates previous behaviour of SHOW PROFILES feature which has been
* officially deprecated from MySQL 5.7 onwards.
*/
DROP PROCEDURE IF EXISTS show_profiles;
DELIMITER $$
CREATE PROCEDURE show_profiles()
BEGIN
# @TODO: Test instruments are setup correctly.
SELECT
event_id as Event_ID, # Called Query_ID in SHOW PROFILES
ps_helper.format_time(timer_wait) as Duration,
format_statement(sql_text) as Query
FROM performance_schema.events_statements_history
INNER JOIN performance_schema.threads ON threads.THREAD_ID=events_statements_history.THREAD_ID
WHERE threads.PROCESSLIST_ID = connection_id();
END$$
DELIMITER ;
/*
* Procedure: show_profile_for_event_id()
*
* Versions: 5.6.x
*
* Emulates previous behaviour of SHOW PROFILES feature which has been
* officially deprecated from MySQL 5.7 onwards.
*
* Parameters
* in_event_id: The event_id as returned by CALL show_profiles() that you would like to inspect.
*
* Obvious differences:
* - It accepts an event_id instead of a Query ID.
* - Time is formatted using ps_helper.
*/
DROP PROCEDURE IF EXISTS show_profile_for_event_id;
DELIMITER $$
CREATE PROCEDURE show_profile_for_event_id(IN in_event_id INT)
BEGIN
SELECT
REPLACE(event_name, 'stage/sql/', '') AS Status,
ps_helper.format_time(timer_wait) as Duration
FROM
performance_schema.events_stages_history_long
INNER JOIN performance_schema.threads ON threads.THREAD_ID=events_stages_history_long.THREAD_ID
WHERE
NESTING_EVENT_ID=in_event_id
AND threads.PROCESSLIST_ID = connection_id();
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment