Home > database > Drop table 出现的问题

Drop table 出现的问题

由于应用下线,需要把数据库中相关应用的表删除,库中有一千多张表,事先已经将所有的表rename到test库中,drop table的脚步也已经准备好,所以接下来的工作本以为是很轻松的事情,但是在执行脚本的过程中,发现删除表的速度感觉有点慢,查看主机的负载也在挺高的,报警消息中thread running过高也出现了,发现大多数线程的状态是Opening Tables,但还是勉强的忍受了过去,事后想想为什么删除表也会这么的慢?

在drop table的时候有几件事情需要去做:

对目标表加上write lock,然后在将数据文件,数据字典文件从存储引擎中删除,看似简单的两个工作,但为什么会这么长?下面的代码中展示了删除过程中的工作,在删除的过程中lock_open mutex会一直用于防止任何query来访问主表;同时在删除表数据文件,数据字典文件的时候由于文件系统的原因(ext3)导致大量的随机i/o,同样会使得删除变慢。

VOID(pthread_mutex_lock(&LOCK_open));

error= mysql_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0);

pthread_mutex_unlock(&LOCK_open);

同时在删除表的过程中,由于innodb_file_per_table=1参数的打开,每个表有一个数据文件,而不是共享一个表空间,在drop table的时候,innodb必须检查LRU链表,把该链表上属于该表的page清除,如果该数据库的buffer pool设置的很大,花费在LRU搜索的时间开销将会很大,同时上千张不同的表打开,如果table_cache设置不当,则会将table_cache充满, mysql使用LRU算法将cache中的表清除,这个时候使得其他查询很有可能看上去被阻塞了,从而导致了性能上的下降。

同时innodb在打开每一张表的时候,还需要重新计算每张表的统计信息,如果有上千张的表,当采用innodb_file_per_table方式管理表的时候,这时候对磁盘i/o,cpu的要求也是很高的,这也是为什么mysql没有像oracle一样需要手动去收集表的的统计信息,而这个操作是串行的,不能够并行处理。

综上所述,drop table的速度受制于文件系统,lock,cache,LRU等诸多因素;上面一个很重要的因素是innodb_file_per_table=1参数打开,导致文件数量的增多,如果你现在的应用中使用的innodb表是经常需要创建,删除,那么将该参数innodb_file_per_table设置为0,使得所有的表共用一个表空间,那么对性能的提升无疑很明显的(10倍?)。

参考:

Performance problem with Innodb and DROP TABLE

Slow DROP TABLE

Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.