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

[24 Jan 2007 11:15] Shane Bester
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:
...
[24 Jan 2007 11:27] Shane Bester
stack trace of the thread which is stuck looping

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

[12 Feb 2007 9:17] Shane Bester
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.