MySQL Bugs: #25820: CPU pegged at 100% after optimizing table belonging to merge set

Bug #25820	CPU pegged at 100% after optimizing table belonging to merge set
Submitted:	24 Jan 2007 11:15	Modified:	12 Feb 2007 9:17
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.36BK	OS:	Any (*)
Assigned to:		CPU Architecture:	Any
Tags:	cpu, hang, merge, Optimize

Description:
mysqld will peg the cpu at 100%  after running optimize table on a table belonging to a merge.

the table is first locked with write lock, then flushed first, so this should work without hanging the cpu up.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                          
 8297 sbester   25   0 82656  50m 2696 R 99.4  6.6   4:03.22 mysqld      

mysql> show processlist;
+----+------+------+------+---------+------+-------+-----------------------------------+
| Id | User | Host | db   | Command | Time | State | Info                              |
+----+------+------+------+---------+------+-------+-----------------------------------+
|  1 | root |      | test | Query   |    0 | NULL  | show processlist                  |
|  2 | root |      | test | Query   |  280 | NULL  | insert into `m1`(`c1`) values (1) |
+----+------+------+------+---------+------+-------+-----------------------------------+
2 rows in set (0.00 sec)

How to repeat:
open two connections to mysql server using command line client.

in connection 1:
---------------
drop table if exists `m1`;
drop table if exists `t1`;
create table `t1`(`c1` int)engine=myisam;
create table `m1`(`c1` int)engine=merge union=(`t1`) insert_method=last;
lock table `t1` write;
flush tables;

then in connection 2:
-------------
insert into `m1`(`c1`) values (1);  #will hang due to lock

now in connection 1:
------------
optimize table `t1`;

#now watch the cpu peg

Suggested fix:
...

stack trace of the thread which is stuck looping

Attachment: thread_stack_5.0.36_windows.txt (text/plain), 1.91 KiB.

Just realized this is a duplicate of

bug #25966

Sorry for any inconvenience caused.  It's pegged at 100% cpu because there's continuous memory allocations going on.  I hadn't noticed it immediately when filing this bug report (Was looking for another specific bug). Also, it's NOT merge specific, just was easier to reproduce with merge tables.