Bug #11333 Stored Procedure: Memory blow up on repeated SELECT ... INTO query
Submitted: 15 Jun 2005 0:10 Modified: 26 Aug 2005 19:27
Reporter: Shuichi Tamagawa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.8-beta (bk Jun 23) OS:Linux (SuSE linux 9.2)
Assigned to: Petr Chardin CPU Architecture:Any

[15 Jun 2005 0:10] Shuichi Tamagawa
Description:
When I repeat SELECT ... INTO statement many times in a stored procedure defined below, it consumes a lot of memory and finary makes mysql crush.

CREATE PROCEDURE `test`.`sp_select`(in i int)
begin
    declare tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8 varchar(8);
    set @x = 0;
    repeat
        SELECT c1,c2,c3,c4,c5,c6,c7,c8
        INTO tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8
        FROM t1
        WHERE c1 = 'AAA99999';
        set @x = @x + 1;
        until @x >= i
    end repeat;
end

How to repeat:
1. Create a table

Create Table: CREATE TABLE `t1` (
  `c1` char(8) NOT NULL,
  `c2` char(8) NOT NULL,
  `c3` char(8) NOT NULL,
  `c4` char(8) NOT NULL,
  `c5` char(8) NOT NULL,
  `c6` char(8) NOT NULL,
  `c7` char(8) NOT NULL,
  `c8` char(8) NOT NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2. Insert 100,000 of rows in to the table

You can use the procedure below to create the test data.

delimiter //
CREATE PROCEDURE `test`.`sp_insert`(in i int)
begin
set @x = 0;
start transaction;
repeat
insert into t1 values(concat('AAA',right(concat('0000',@x),5)),'AAAAAAAA','AAAAAAAA','AAAAAAAA','AAAAAAAA','AAAAAAAA','AAAAAAAA','AAAAAAAA');
set @x = @x + 1;
until @x >= 100000
end repeat;
commit;
end
//

3. Create and call the stored procedure below (Change the value of the parameter depending on the memory size)

delimiter //
CREATE PROCEDURE `test`.`sp_select`(in i int)
begin
    declare tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8 varchar(8);
    set @x = 0;
    repeat
        SELECT c1,c2,c3,c4,c5,c6,c7,c8
        INTO tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8
        FROM t1
        WHERE c1 = 'AAA99999';
        set @x = @x + 1;
        until @x >= i
    end repeat;
end
//

mysql> call sp_select(100000)//

After a while, mysqld crushes

./mysqld_safe: line 363: 10056 強制終了                nohup ./mysqld --basedir=/usr/local/mysql/50060/ --datadir=/usr/local/mysql/50060/data --user=mysql --pid-file=/usr/local/mysql/50060/data/linux.pid --skip-locking --port=50060 --socket=/usr/local/mysql/50060/mysql.sock >>/usr/local/mysql/50060/data/linux.err 2>&1
ERROR 2013 (HY000): Lost connection to MySQL server during query

Number of processes running now: 0
050614 16:30:47  mysqld restarted
[15 Jun 2005 0:16] Shuichi Tamagawa
Here are some additional info.

System Memory: 256MB

Configuration parameters:
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 128MB
innodb_additional_memj_pool_size = 20M

I've also attached the result of 'vmstat 1' while executing the procedure.
[15 Jun 2005 0:18] Shuichi Tamagawa
result of "vmstat 1"

Attachment: vmstat.log (text/x-log), 5.26 KiB.

[15 Jun 2005 14:42] Sinisa Milivojevic
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.
[15 Jun 2005 15:33] Sergei Golubchik
This looks like a duplicate of http://bugs.mysql.com/bug.php?id=10968
[16 Jun 2005 18:11] Shuichi Tamagawa
Hi,

I was able to reproduce this on 5.0.7-beta-max-log, which I downloaded today. 
This might be a duplicate of http://bugs.mysql.com/bug.php?id=11247, though.

(It seems I can't change the status because it has been closed.)
[16 Jun 2005 23:30] Shuichi Tamagawa
Still reproducible on 5.0.8-beta-log (bk Jun 16)
[20 Jun 2005 20:23] Miguel Solorzano
I was unable for to repeat with a Windows server built with
BK June 17:

mysql> delimiter //
mysql> CREATE PROCEDURE `test`.`sp_select`(in i int)
    -> begin
    ->     declare tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8 varchar(8);
    ->     set @x = 0;
    ->     repeat
    ->         SELECT c1,c2,c3,c4,c5,c6,c7,c8
    ->         INTO tmp1,tmp2,tmp3,tmp4,tmp5,tmp6,tmp7,tmp8
    ->         FROM t1
    ->         WHERE c1 = 'AAA99999';
    ->         set @x = @x + 1;
    ->         until @x >= i
    ->     end repeat;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_select(100000)//
Query OK, 0 rows affected (9 min 21.84 sec)

mysql> select version();
    -> //
+---------------+
| version()     |
+---------------+
| 5.0.8-beta-nt |
+---------------+
1 row in set (0.36 sec)
[20 Jun 2005 20:38] Shuichi Tamagawa
Hi Miguel,

My laptop has only 256MB memory. Try to call the procedure with much larger number like call sp1(1000000).

Anyway, isn't it too slow that 100000 SELECT statements takes 9 minutes? On my environment if I don't use stored procedure, it takes only 42 seconds. In that case, I wrote a script to generate 100000 SELECT statements and redirected it to mysql command line client.
[20 Jun 2005 20:55] Shuichi Tamagawa
Here is the perl script that I used.
----------------------------------------------------------------
#!/usr/bin/perl

foreach my $i (0..99999) {
        printf("
select c1,c2,c3,c4,c5,c6,c7,c8 from t1 where c1 = CONCAT('AAA',RIGHT(CONCAT('0000',%d),5));\n",$i)
}
----------------------------------------------------------------

And the result is:

linux:/usr/local/mysql/50080/bin # perl select_multi.pl|time ./mysql -u root -p test > /dev/null
Enter password:
9.02user 0.49system 0:42.43elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+396minor)pagefaults 0swaps
[20 Jun 2005 22:18] Miguel Solorzano
Below the result I got in the mysql client, not crashing the server:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.8-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> call sp_select(1000000);
ERROR 5 (HY000): Out of memory (Needed 2490020 bytes)
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.8-beta-nt |
+---------------+
1 row in set (0.28 sec)

mysql>
[23 Jun 2005 15:07] Per-Erik Martin
So far I haven't quite been able to repeat this in a "production like" build (i.e. without mem_root code in "debug mode"). It does exercise the disk a bit and takes awhile, but it doesn't crash, or freeze the machine.

Note that a normal debug or valgrind build allocates memory in a different way (it never reuses mem_root blocks even when requested, it always allocates new blocks), which might result in a different behaviour. I'm not sure it's possible to fix leaks caused by this;
memory stress tests should always be done in a non-debug build.
[23 Jun 2005 23:30] Shuichi Tamagawa
Hi PEM,

I tested on today's bk build, using both BUILD/compile-pentium, BUILD/compile-pentium-max. But it seems that there are still leaks.
What do you mean by 'Production like build' or 'Non-debug build'? 

Thanks
[25 Aug 2005 11:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28822
[25 Aug 2005 14:49] Petr Chardin
pushed into 5.0.13
[26 Aug 2005 19:27] Paul Dubois
Noted in 5.0.13 changelog.