Memory allocation in Stored Function

UPDATE : Post is not actual anymore

Not so long time ago I had task to update string column in table with 10mil+ rows, and, as the manipulation was non-trivial, I decided this task is good to try Stored Function. Function written – go ahead. Since 5 min I got totally frozen box with no free memory and giant swap.

The case was worth to look deeply – let’s try simple table (experiments with MySQL 5.0.45)

CREATE TABLE `testf` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20971521 DEFAULT CHARSET=latin1

mysql> select count(*) from testf;
+----------+
| count(*) |
+----------+
| 20971520 |
+----------+
1 row in set (0.00 sec)

and simple function I’ve found in MySQL manual:

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!');

Originally I used UPDATE statement, but for avoiding I/O and allocating os cashes let’s check only select:

mysql> select avg(length(hello(name))) from testf;

and here is vmstat 5 output:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
0  0      0 5103732 212556 9558976    0    0     0     0 1052  3179  0  0 100  0
1  0      0 4878900 212556 9558976    0    0     0     0 1078  3171 15  3 82  0
1  0      0 4534900 212556 9558976    0    0     0     3 1048  3091 21  4 75  0
1  0      0 4190964 212556 9558976    0    0     0     2 1071  3131 21  5 75  0
1  0      0 3848372 212556 9558976    0    0     0     4 1048  3093 21  5 75  0
1  0      0 3506868 212556 9558976    0    0     0     2 1070  3135 21  5 74  0
1  0      0 3165876 212556 9558976    0    0     0     2 1049  3092 21  5 75  0
1  0      0 2824820 212556 9558976    0    0     0     2 1071  3126 21  5 75  0
1  0      0 2483188 212556 9558976    0    0     0     0 1047  3213 21  5 75  0
1  0      0 2091380 212556 9558976    0    0     0     2 1070  3024 21  9 71  0
1  0      0 1798836 212556 9558976    0    0     0     2 1049  2141 21 16 63  0
1  0      0 1457012 212556 9558976    0    0     0     4 1072  2770 21  5 75  0
1  0      0 1114484 212556 9558976    0    0     0     2 1049  2729 21  5 75  0
1  0      0 771444 212556 9558976    0    0     0     2 1071  2772 21  4 75  0
1  0      0 428724 212556 9558976    0    0     0     2 1050  2729 21  4 74  0
1  0      0  85748 212556 9558976    0    0     0     2 1073  2770 21  5 75  0
1  0      0  22964 204920 9291280    0    0     0     3 1049  2753 21  5 74  0

As you see MySQL ate 5GB of memory in 85 sec. That makes things clearer. As I understand MySQL allocates memory in each call of Stored Function, but de-allocates it only at the end of statement. This makes usage of Stored Function very limited from my point of view. And, yes, if you are looking for a way to DoS attack of your hosting provider – this is worth to try.