MYSQL教程:缓慢的drop table 操作


大家都知道,Ext3并不是最有效的文件系统,例如,删除文件会非常缓慢(那真是一个痛苦的过程,不是吗老兄?),造成大量的随机I / O。然而事实上,有时候它比你想象的更能影响MySQL的性能。那么,什么时候会发生,又为什么会发生呢?

当您运行DROP TABLE时,会有好几件事情需要去做:对表进行write lock,这样它不会被其他线程使用;存储引擎删除数据文件;当然,最后MySQL会删除表定义文件(.frm文件)。这还不是所有的事,还有另外一件事需要去做:

代码:
  1. VOID(pthread_mutex_lock(&LOCK_open));
  2. error= mysql_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0);
  3. pthread_mutex_unlock(&LOCK_open);

这整段删除表操作的代码都被LOCK_open互斥信号量所包围。这个互斥信号量在MySQL中不少地方都用到过,但主要是表在开启或关闭的时候。这意味着,当LOCK_open锁定时,没有查询语句可以执行,因为他们阻止任何访问。

这就解释了在ext3文件系统上删除10GB的文件何时成为了痛苦的等待的开始。删除10GB的文件将持续一段时间,如果这是一个MySQL表,这段时间mutex里将会一直存在,而这个互斥会拖延所有查询。

纯文字
代码:

 

  1. +—–+——+———–+——+———+——+ —————-+——————————— —————+
  2. | Id | User | Host | db | Command | Time | State | Info |
  3. +—–+——+———–+——+———+——+ —————-+——————————— —————+
  4. | 1 | root | localhost | test | Query | 7 | NULL | drop table large_table |
  5. | 329 | root | localhost | test | Query | 7 | Opening tables | select sql_no_cache * from other_table limit 1 |
  6. +—–+——+———–+——+———+——+ —————-+——————————— —————+

我尝试了一些替代的办法,让MySQL来在DROP TABLE时删除小文件,以尽量减少影响,如:

  • TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table;
  • TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table;

不幸的是,原来所有管理指令:ALTER TABLE <ALTER ALTER TABLEOPTIMIZE TABLE实际都一样,或是在旧的表文件删除时使用了其他的LOCK_open互斥信号锁。

纯文字
代码:

 

  1. | 3 | root | localhost | test | Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM |
  2. | 679 | root | localhost | test | Query | 6 | Opening tables | select * from other_table limit 1 |

唯一的选择似乎就只能是改变文件系统了。例如,换成处理文件删除更有效的XFS文件系统。

EXT3

纯文字
代码:

 

  1. mysql> drop table large_table;
  2. Query OK, 0 rows affected (7.44 sec)

的xfs

纯文字
代码:

 

  1. mysql> drop table large_table;
  2. Query OK, 0 rows affected (0.29 sec)

一个MySQL的内部操作可能更好一点:可以通过先重命名对应的数据文件,再在没有互斥信号锁的情况下删除物理文件。但是事实上可能没有那么简单,因为实际的删除操作是由存储引擎来完成的,因此不是MySQL的代码能控制的。

这肯定不是一个共同的情况,但是有时候(虽然我们极其不愿)确实可能会让任何人头痛(例如,删除一个老的不再使用的表) 。

这里绍明同学说应该翻译成:这不是一个常见的情况,但是它可能会在最不期望出现的时候成为一个问题(例如 删除没有使用的旧表).


后附:

 

一个评论回复说:

从一个完全不同的领域的解决办法。

mythtv (电视记录系统)开发者们在ext3系统上删除大型文件时,通过“缓慢删除”功能取得更好的效果。一般来说,电视录音大概1 – 12GB每小时,所以一部电影可以20 + GB大小的文件。如果没有该功能,删除文件操作将锁定ext3整个系统约20-30秒。

“慢删除”只是在后台执行将文件中的块清空的操作。

另一个评论说:

我应该指出,这并不只适用于MyISAM表,对于InnoDB表,在innodb_file_per_table模式下也一样。
我最近刚跟客户做了个spoke,他们几乎无法删掉一个400G的表,因为这个操作阻塞了很长时间。。


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3