| Bug #30543 | Falcon: Hang if partition and 65536-row update | ||
|---|---|---|---|
| Submitted: | 21 Aug 2007 19:34 | Modified: | 7 Sep 2007 17:33 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0.2-alpha-debug | OS: | Linux (SUSE 10 64-bit) |
| Assigned to: | CPU Architecture: | Any | |
[6 Sep 2007 6:50]
Valeriy Kravchuk
Thank you for a problem report. I was not able to repeat "hang". I've got: ... +------+ | v | +------+ | 143 | +------+ 1 row in set (7 min 42.88 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.88 sec) +------+ | v | +------+ | 144 | +------+ 1 row in set (7 min 42.89 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.89 sec) +------+ | v | +------+ | 145 | +------+ 1 row in set (7 min 42.89 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.91 sec) +------+ | v | +------+ | 146 | +------+ 1 row in set (7 min 42.91 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.91 sec) +------+ | v | +------+ | 147 | +------+ 1 row in set (7 min 42.92 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.95 sec) +------+ | v | +------+ | 148 | +------+ 1 row in set (7 min 42.95 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.97 sec) +------+ | v | +------+ | 149 | +------+ 1 row in set (7 min 42.97 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 42.98 sec) +------+ | v | +------+ | 150 | +------+ 1 row in set (7 min 42.98 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.00 sec) +------+ | v | +------+ | 151 | +------+ 1 row in set (7 min 43.00 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.00 sec) +------+ | v | +------+ | 152 | +------+ 1 row in set (7 min 43.03 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.03 sec) +------+ | v | +------+ | 153 | +------+ 1 row in set (7 min 43.03 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.05 sec) +------+ | v | +------+ | 154 | +------+ 1 row in set (7 min 43.05 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.06 sec) +------+ | v | +------+ | 155 | +------+ 1 row in set (7 min 43.06 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.06 sec) +------+ | v | +------+ | 156 | +------+ 1 row in set (7 min 43.08 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.08 sec) +------+ | v | +------+ | 157 | +------+ 1 row in set (7 min 43.08 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.09 sec) +------+ | v | +------+ | 158 | +------+ 1 row in set (7 min 43.09 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.11 sec) +------+ | v | +------+ | 159 | +------+ 1 row in set (7 min 43.11 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.11 sec) +------+ | v | +------+ | 160 | +------+ 1 row in set (7 min 43.11 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.13 sec) +------+ | v | +------+ | 161 | +------+ 1 row in set (7 min 43.13 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.13 sec) +------+ | v | +------+ | 162 | +------+ 1 row in set (7 min 43.13 sec) +-------+ | error | +-------+ | error | +-------+ 1 row in set (7 min 43.14 sec) +------+ | v | +------+ | 163 | +------+ 1 row in set (7 min 43.14 sec) So, it "works", but very slow, CPU usage is 100%, and I was forced to kill the server eventually. But pf() does output something all the time.
[6 Sep 2007 13:03]
Peter Gulutzan
The partition function "(1 << s1)" is now illegal.
I replaced it with "(s1 * s1)", and tried again.
It no longer hangs quickly. Instead it slows down
and eventually crashes. There is no COMMIT in the
loop, so perhaps this revised "how to repeat" is
nothing new.
How to repeat
delimiter ;
set @@autocommit=1;
drop table tf;
drop procedure pf;
create table tf (s1 int) engine=falcon partition by list (s1 * s1) (partition p1 values in
(1), partition p2 values in (2), partition p4 values in (4), partition p8 values in (8),
partition p16 values in (16), partition p32 values in (32), partition p64 values in (64),
partition p128 values in (128), partition p256 values in (256), partition pn values in
(null), partition p0 values in (0));
alter table tf modify column s1 tinyint;
insert into tf values (1);
insert into tf values (2);
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
insert into tf select * from tf;
set @@autocommit=0;
delimiter //
create procedure pf ()
begin
declare v int default 0;
declare continue handler for sqlexception select 'error';
while v < 100000 do
select v;
update tf set s1 = s1 - 1;
set v = v + 1;
end while;
end//
call pf()//
After several minutes I see:
+------+
| v |
+------+
| 82 |
+------+
1 row in set (6 min 43.36 sec)
+-------+
| error |
+-------+
| error |
+-------+
1 row in set (8 min 20.69 sec)
+------+
| v |
+------+
| 83 |
+------+
1 row in set (8 min 20.69 sec)
+-------+
| error |
+-------+
| error |
+-------+
1 row in set (10 min 35.13 sec)
+------+
| v |
+------+
| 84 |
+------+
1 row in set (10 min 35.13 sec)
ERROR 2013 (HY000): Lost connection to MySQL server during query
[7 Sep 2007 3:47]
Kevin Lewis
I tried to reproduce this slow down and eventual crash, but could not. The memory grew to about 205 Mbytes. Then the scavenger kicked in and the memory dropped back down to a steady 193 Mb for the duration of the test. The performance stayed steady.
[7 Sep 2007 13:44]
Peter Gulutzan
I noticed today that I had almost no disk space left. It's possible that the crash happened because a write failed.
[7 Sep 2007 14:03]
Peter Gulutzan
Kevin Lewis reminded me that there is already a report Bug#22406 Falcon: crash if no disk space so I close this bug.
[7 Sep 2007 17:34]
Valeriy Kravchuk
So, this is the duplicate of Bug#22406.
Description: I create a partitioned Falcon table. I insert 65536 rows. I create a procedure to repeatedly update the table. I call the procedure. It soon hangs. How to repeat: delimiter ; set @@autocommit=1; drop table tf; drop procedure pf; create table tf (s1 int) engine=falcon partition by list (1 << s1) (partition p1 values in (1), partition p2 values in (2), partition p4 values in (4), partition p8 values in (8), partition p16 values in (16), partition p32 values in (32), partition p64 values in (64), partition p128 values in (128), partition p256 values in (256), partition pn values in (null), partition p0 values in (0)); alter table tf modify column s1 tinyint; insert into tf values (1); insert into tf values (2); insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; insert into tf select * from tf; set @@autocommit=0; delimiter // create procedure pf () begin declare v int default 0; declare continue handler for sqlexception select 'error'; while v < 100000 do select v; update tf set s1 = s1 - 1; set v = v + 1; end while; end// call pf()// The last thing you'll see is: ... mysql> call pf()// +------+ | v | +------+ | 0 | +------+ 1 row in set (0.00 sec) +------+ | v | +------+ | 1 | +------+ 1 row in set (1.20 sec) You will have to say 'kill -9' to stop mysqld. If the "update tf" statement were outside a procedure, it would cause "ERROR 1041 (HY000): Out of memory". But inside a procedure, it hangs.