|
Significant memory usage when using min or max function within a user defined function. Several gb can be consumed in a few seconds. Droping the function or closing the client connection releases the memory.
Attached is a example. In reality our function is much more complicated.
I get an error when attempting to add an attachment so here is the sql:
CREATE DATABASE IF NOT EXISTS sample;
|
|
USE sample;
|
|
DROP TABLE IF EXISTS sample_data;
|
|
CREATE TABLE IF NOT EXISTS sample_data (
|
id int(10) unsigned
|
) ENGINE=MyISAM;
|
|
DROP FUNCTION IF EXISTS udf_example;
|
|
DELIMITER $$
|
|
CREATE FUNCTION udf_example (cnt INT(10) UNSIGNED) RETURNS int(10) unsigned
|
BEGIN
|
DECLARE i, j INT UNSIGNED DEFAULT 0;
|
|
WHILE i < cnt
|
DO
|
SELECT max(id) FROM sample_data
|
INTO j;
|
SET i = i + 1;
|
END WHILE;
|
|
|
RETURN cnt;
|
|
END
|
$$
|
|
DELIMITER ;
|
SHOW GLOBAL STATUS LIKE 'Memory_used';
|
SELECT udf_example(10000);
|
SHOW GLOBAL STATUS LIKE 'Memory_used';
|
SELECT udf_example(10000);
|
SHOW GLOBAL STATUS LIKE 'Memory_used';
|
SELECT udf_example(10000);
|
SHOW GLOBAL STATUS LIKE 'Memory_used';
|
|
DROP FUNCTION IF EXISTS udf_example;
|
SHOW GLOBAL STATUS LIKE 'Memory_used';
|
|