August 26, 2007

Memory allocation in Stored Function

Posted by Vadim |

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)

PLAIN TEXT
SQL:
  1. CREATE TABLE `testf` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(255) DEFAULT NULL,
  4.   PRIMARY KEY  (`id`)
  5. ) ENGINE=MyISAM AUTO_INCREMENT=20971521 DEFAULT CHARSET=latin1
  6.  
  7. mysql> SELECT count(*) FROM testf;
  8. +----------+
  9. | count(*) |
  10. +----------+
  11. | 20971520 |
  12. +----------+
  13. 1 row IN SET (0.00 sec)

and simple function I've found in MySQL manual:

PLAIN TEXT
SQL:
  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:

PLAIN TEXT
SQL:
  1. mysql> SELECT avg(length(hello(name))) FROM testf;

and here is vmstat 5 output:

PLAIN TEXT
SQL:
  1. procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
  2. r  b   swpd   free   buff  cache   si   so    bi    bo   IN    cs us sy id wa
  3. 0  0      0 5103732 212556 9558976    0    0     0     0 1052  3179  0  0 100  0
  4. 1  0      0 4878900 212556 9558976    0    0     0     0 1078  3171 15  3 82  0
  5. 1  0      0 4534900 212556 9558976    0    0     0     3 1048  3091 21  4 75  0
  6. 1  0      0 4190964 212556 9558976    0    0     0     2 1071  3131 21  5 75  0
  7. 1  0      0 3848372 212556 9558976    0    0     0     4 1048  3093 21  5 75  0
  8. 1  0      0 3506868 212556 9558976    0    0     0     2 1070  3135 21  5 74  0
  9. 1  0      0 3165876 212556 9558976    0    0     0     2 1049  3092 21  5 75  0
  10. 1  0      0 2824820 212556 9558976    0    0     0     2 1071  3126 21  5 75  0
  11. 1  0      0 2483188 212556 9558976    0    0     0     0 1047  3213 21  5 75  0
  12. 1  0      0 2091380 212556 9558976    0    0     0     2 1070  3024 21  9 71  0
  13. 1  0      0 1798836 212556 9558976    0    0     0     2 1049  2141 21 16 63  0
  14. 1  0      0 1457012 212556 9558976    0    0     0     4 1072  2770 21  5 75  0
  15. 1  0      0 1114484 212556 9558976    0    0     0     2 1049  2729 21  5 75  0
  16. 1  0      0 771444 212556 9558976    0    0     0     2 1071  2772 21  4 75  0
  17. 1  0      0 428724 212556 9558976    0    0     0     2 1050  2729 21  4 74  0
  18. 1  0      0  85748 212556 9558976    0    0     0     2 1073  2770 21  5 75  0
  19. 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.

Posted by Vadim @ 10:09 am :: production  
Print This Post Print This Post book mark Memory allocation in Stored Function in del.icio.usdel.icio.us :: submit Memory allocation in Stored Function to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Withdrawal of Memory allocation in Stored Function::Wanted: Better memory profiling for MySQL::Read Buffers, mmap, malloc and MySQL Performance:
 

7 Comments »

  1. 1. Dmitri Mikhailov

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

    Comment :: August 26, 2007 @ 6:44 pm

  2. 2. Vadim

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

    Comment :: August 26, 2007 @ 8:13 pm

  3. 3. pabloj

    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

    Comment :: August 27, 2007 @ 10:01 am

  4. 4. Massoud

    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.

    Comment :: August 28, 2007 @ 2:57 am

  5. 5. Vadim

    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.

    Comment :: August 28, 2007 @ 3:02 am

  6. 6. Konstantin

    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.

    Comment :: September 5, 2007 @ 7:04 am

  7. 7. Vadim

    Konstantin.

    I wrote another post
    http://www.mysqlperformanceblog.com/2007/09/07/withdrawal-of-memory-allocation-in-stored-function/

    Sorry again for confusion.

    Comment :: September 7, 2007 @ 1:33 am

 

Subscribe without commenting

Trackbacks/Pingbacks