October 30, 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 Percona Clould Tools, the Percona Server, Percona XraDB Cluster 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

*