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: | |
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
[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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.