Memory allocation in Stored Function

PREVIOUS POST
NEXT POST

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)

and simple function I’ve found in MySQL manual:

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

and here is vmstat 5 output:

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.

PREVIOUS POST
NEXT POST

Comments

  1. Dmitri Mikhailov says

    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).

  2. pabloj says

    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

  3. Massoud says

    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.

  4. Vadim says

    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.

  5. Konstantin says

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *