DROP PROCEDURE IF EXISTS estimate_working_set;
DELIMITER //
CREATE PROCEDURE estimate_working_set (IN time_between_observation INT, IN observations INT) 
BEGIN

DECLARE i INT;
SET i = 0;

DROP TEMPORARY TABLE IF EXISTS test.estimate_working_set;

CREATE TEMPORARY TABLE test.estimate_working_set (
 space INT UNSIGNED NOT NULL,
 page_number INT UNSIGNED NOT NULL,
 PRIMARY KEY (space, page_number)
);

-- Repeat for i observations

loop_label:  LOOP
 IF  i >= observations THEN
  LEAVE  loop_label;
 END  IF;
  SET  i = i + 1;
  INSERT IGNORE INTO test.estimate_working_set
   select space, page_number from information_schema.innodb_buffer_page where PAGE_STATE != 'NOT_USED';
  SELECT SLEEP(time_between_observation) INTO @null;
  SELECT CONCAT(i, '/', observations) AS progress; # give visual progress
  
END LOOP;
              
SELECT COUNT(*) as pages_in_working_set FROM test.estimate_working_set;
DROP TEMPORARY TABLE IF EXISTS test.estimate_working_set;

END//
DELIMITER ;
