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,却依然存在,谁碰到同样的情况请不吝留下言,谢谢 :)
评论
&FROST (未验证)
周四, 2009/11/12 - 19:48
Permalink
不小心点到头条,没
不小心点到头条,没看都两年前的贴了 。~。~
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耗时有点长。
祛痘印产品 (未验证)
周二, 2010/04/06 - 17:11
Permalink
博主的mysql非常厉害。
博主的mysql非常厉害。收藏。
黄铃斐 (未验证)
周三, 2011/04/20 - 14:57
Permalink
我有一次遇到修复慢
我有一次遇到修复慢时,因问表正在被访问,增加--skip-networking 重新启动mysql后很快修复完成