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.
About the Author
Vadim TkachenkoVadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.
A similar (if not the same) problem was reported in Bug #17260 – “Multiple invocations of triggers or stored functions hog memory”. Fixed in 5.0.22 and 5.1.11 (or not).
Right, Sorry I did not mention I use 5.0.45. The bug you refer marked as fixed in 5.0.22
Dmitri, similar problem but seems a different cause, see:
[22 May 2006 3:50] Paul DuBois
Noted in 5.022, 5.1.11 changelogs.
Revised memory allocation for local objects within stored
functions and triggers to avoid memory leak for repeated
function or trigger invocation.
That was a memory leak, this seems like a design problem
Hi,
your work is greate, after reading some of your posts, i decide to run my website in innodb.
but I’m using xampp on localhost. ( downloadable from: apachefriends.org )
this is the best localhost software.
but, in mysql hasnt innodb storage engine… can you tell me how can i add this?
thanks.
Massoud,
For general questions please use our forums.
To be honest I did not try xampp , perhaps it has no compiled InnoDB.
Please check SHOW ENGINES output.
If InnoDB is NO – then you should install mysql by yourself from dev.mysql.coms site.
Just an update on this – this is Bug#30663, http://bugs.mysql.com/30663, which is in state “Can’t repeat”.
I have a very strong inclination to think that something got messed up on this box – we indeed had a similar bug in stored functions, but it was a while ago.
Konstantin.
I wrote another post
http://www.mysqlperformanceblog.com/2007/09/07/withdrawal-of-memory-allocation-in-stored-function/
Sorry again for confusion.