REPAIR TABLE导致死锁

问题表现:
以 discuz 论坛的帖子数据表(cdb_posts)为例,该表有 887106 条记录,正常情况下,在一个 P4 2.4G、1G 内存的 PC 机上执行 REPAIR TABLE 的耗时大概 95s,如下:

mysql> SHOW GLOBAL VARIABLES LIKE 'myisam_repair_threads';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| myisam_repair_threads | 1     | 
+-----------------------+-------+

mysql> REPAIR TABLE cdb_posts;
+-----------------+--------+----------+----------+
| Table           | Op     | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| Forum.cdb_posts | repair | status   | OK       | 
+-----------------+--------+----------+----------+
1 row in set (1 min 35.58 sec)

而同等硬件条件下,如果把 myisam_repair_threads 设置成 >1(例如7,设置完之后记得重启 MySQL) 的话,则会有问题,表现为 REPAIR 进程类似发生死锁的状况,一直无法完成,如下:

mysql> SHOW GLOBAL VARIABLES LIKE 'myisam_repair_threads';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| myisam_repair_threads | 2     | 
+-----------------------+-------+

mysql> REPAIR TABLE cdb_posts;

再找个终端登录 MySQL,执行 SHOW PROCESSLIST 可以看到如下情况:

mysql> SHOW PROCESSLIST;
....
|  1 | root | localhost | Forum | Query   |  1330 | Repair with 7 threads | REPAIR TABLE cdb_posts | 
....

最后,google了一下,发现也有其他人报告这个bug,详情请看:OPTIMIZE TABLE cause race condition in IO CACHE SHARE,号称是在 5.0.40 已经修复了,可是我用的版本是 5.0.45,却依然存在,谁碰到同样的情况请不吝留下言,谢谢 :)

技术相关:

评论

不小心点到头条,没看都两年前的贴了 。~。~
MySQL 版本 5.1.31

Test >> show global variables like 'myisam_repair_threads';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| myisam_repair_threads | 2 |

Test >> repair table t1;
+---------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------+
| Test.t1 | repair | status | OK |
+---------+--------+----------+----------+
1 row in set (7.92 sec)

Test >> show global variables like 'myisam_repair_threads';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| myisam_repair_threads | 7 |

Test >> repair table t1;
+---------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------+
| Test.t1 | repair | status | OK |
+---------+--------+----------+----------+
1 row in set (5.24 sec)

虚拟机测得,repair耗时有点长。

博主的mysql非常厉害。收藏。

我有一次遇到修复慢时,因问表正在被访问,增加--skip-networking 重新启动mysql后很快修复完成