April 18, 2014

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)

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.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces the Percona Server and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

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. Vadim says:

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

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

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

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

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

  7. Vadim says:

Speak Your Mind

*