Bug #36673 drop database uses excessive memory.

• Submitted: 12 May 2008 20:19
• Modified: 21 May 2008 14:14
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Falcon storage engine
• Severity: S3 (Non-critical)
• Version: 6.0.4
• OS: Microsoft Windows (vista 32 bit)
• Assigned to: Hakan Küçükyılmaz
• CPU Architecture: Any
• Tags: qc

[12 May 2008 20:19] Peter Laursen

Description:
populate a database with the Stored Proceddure from here:
http://bugs.mysql.com/?id=36672
(only replace engine to be FALCON in the example).

Next drop the database.

It is much slower than dropping a similar MyISAM or InnoDB database on 5.0.51.
On my 1G RAM laptop it took ~15 minutes because the operation used excessive memory and caused swapping.

Doing the same on 5.0.51 with MyIsam or InnoDB leaves ~250 MB RAM free on this PC (with what is running on it).

While it was happening I was able to connect with other clients, but not to get returns from queries.

I think the excessive memory usage here may in extreme situations render the server unusable ... what happens if somebody kills the server process in frustration?

How to repeat:
see above

Suggested fix:
Not sure! Not sure either if it is a server or a engine problem...

[21 May 2008 13:18] Kevin Lewis

Hakan, Please try to verify this or ask someone form support to do it.

[21 May 2008 13:58] Hakan Küçükyılmaz

Can't repeat with latest Falcon on Linux:

MyISAM:
6.0.6-alpha-debug
[15:57] root@test>select count(*) from tab;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

[15:57] root@test>drop schema test;
Query OK, 1 row affected (0.02 sec)

Falcon:

[15:58] root@test>select count(*) from tab;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (4.86 sec)

[15:58] root@test>drop schema test;
Query OK, 1 row affected (0.15 sec)

[21 May 2008 13:58] Hakan Küçükyılmaz

I used this stored procedure:

drop table tab;
drop procedure p1;

-- create table tab (id integer primary key auto_increment, ts timestamp, t varchar(50)) engine myisam;
create table tab (id integer primary key auto_increment, ts timestamp, t varchar(50)) engine falcon;

delimiter //
create procedure p1 ()
  begin
    declare innercount int default 0;
    start transaction;
    while innercount < 100000 do
        insert into tab (t) values (hex(now()));
        set innercount = innercount + 1;
    end while;
    commit;
  end//
delimiter ;

call p1();

[21 May 2008 14:14] MySQL Verification Team

I could not repeat with source server on Windows too:

mysql> select count(*) from tab;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.25 sec)

mysql> show create table tab\G
*************************** 1. row ***************************
       Table: tab
Create Table: CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=Falcon AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 6.0.6-alpha-nt      |
| version_comment         | Source distribution |
| version_compile_machine | ia32                |
| version_compile_os      | Win32               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> drop schema test;
Query OK, 4 rows affected (0.50 sec)

mysql>