Memory allocation in Stored Function

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

Share this post

Comments (8)

  • Dmitri Mikhailov Reply

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

    August 26, 2007 at 6:44 pm
  • Vadim Reply

    Right, Sorry I did not mention I use 5.0.45. The bug you refer marked as fixed in 5.0.22

    August 26, 2007 at 8:13 pm
  • pabloj Reply

    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

    August 27, 2007 at 10:01 am
  • Massoud Reply

    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.

    August 28, 2007 at 2:57 am
  • Vadim Reply

    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.

    August 28, 2007 at 3:02 am
  • Konstantin Reply

    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.

    September 5, 2007 at 7:04 am
  • MySQL Performance Blog » Withdrawal of Memory allocation in Stored Function Reply

    […] to my post about memory consumption with Stored Function at this moment I want to annul that post. Trying to repeat described behavoir […]

    September 7, 2007 at 1:31 am
  • Vadim Reply September 7, 2007 at 1:33 am

Leave a Reply