MySQL Bugs: #11333: Stored Procedure: Memory blow up on repeated SELECT ... INTO query

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

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

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.

result of "vmstat 1"

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

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.

This looks like a duplicate of http://bugs.mysql.com/bug.php?id=10968

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

Still reproducible on 5.0.8-beta-log (bk Jun 16)

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)

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.

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

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>

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.

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

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

pushed into 5.0.13

Noted in 5.0.13 changelog.