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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 |
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:
|
1 |
mysql> select avg(length(hello(name))) from testf; |
and here is vmstat 5 output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
