最近做drop table操作,24G的sas机器,ibd文件17G,大约需要14S,在此期间mysql(5.1.48)基本hang住。详细了解了一下mysql drop table过程,发现是mysql drop table的逻辑引起,主要有2方面的原因:drop table过程会持有buffer pool mutex,做2次遍历--对于大内存的mysql服务器,会导致mysql hang住;rm ibd文件的过程,会持有LOCK_open mutex,对于大表,也会导致mysql hang住。对于第二个问题,bug#41158提到一个解决办法:对磁盘做碎片整理;drop前对ibd文件做一个硬链接,这样drop的时候,ibd文件就不会被rm掉,LOCK_OPEN持有的时间就会很短,drop完之后再单独rm。
下面详细理一下drop table的过程:
drop table的调用路径如下(5.1.58): do_command(sql_parse.cc) ->dispatch_command(sql_parse.cc) ->mysql_parse(sql_parse.cc) ->mysql_execute_command(sql_parse.cc) ->mysql_rm_table(sql_table.cc) ->mysql_rm_table_part2(sql_table.cc:2072)(LOCK_open发生在这里) ->ha_delete_table(handler.cc) ->handler::ha_delete_table(handler.cc) ->ha_innodb::delete_table(handler/ha_innodb.cc)(这里开始就是innodb层的实现) ->row_drop_table_for_mysql(row/row0mysql.c) ->fil_delete_tablespace(fil/fil0fil.c) ->buf_LRU_invalidate_tablespace(buf/buf0lru.c) ->buf_LRU_drop_page_hash_for_tablespace(buf/buf0lru.c)(尝试批量删除被drop的space id的hash index entries,这里开始了对buffer pool的遍历) ->os_file_delete(os/os0file.c)(unlink()发生在这里)
unlink是在innodb的os_file_delete函数内调用的,而mysql_rm_table_part2(sql_table.cc:1923)开始持有LOCK_open,直到调用innodb层drop table完成 & mysql层表定义文件(frm和这个表上的trigger)被删除后才释放,这个就是本文一开头说的drop 大表时引起mysql hang住。
接下来看一下2次遍历的问题。fil_delete_tablespace()在unlink ibd文件前清理buffer pool中对应表的block,具体函数是buf_LRU_invalidate_tablespace()。这个函数一开始就调用buf_LRU_drop_page_hash_for_tablespace(),尝试清理哈希索引中的entries(不保证完全清理),这里开始了对buffer pool的第一次遍历(从后往前),遍历的开始就执行buf_pool_mutex_enter(),拿到buf_pool_mutex。处理完hash index之后,buf_LRU_invalidate_tablespace()就开始了对buffer pool的第二次遍历(从后往前),同样的在遍历的整个过程中持有buf_pool_mutex。如果你的buffer pool比较大,2次 buffer pool的遍历自然会花费很久,mysql也会hang的更久。
mysql5.5引入metadata lock,unlimit的时候持有LOCK_open的问题得到解决。同时对2次遍历的问题做了一些改进:每扫描1024个pages后(满足某些条件,bug#64284),会释放buffer pool mutex;将对lru list的第一遍扫描改为对flush list的扫描。
percona也对这个问题做了一些改进。percona引入buf_LRU_mark_space_was_deleted函数来解决这个问题。扫描lru list,对lru list中要被删除的page做个flag;对AHI加一把共享锁,扫描buffer pool,清理要被删除的page;释放AHI上的共享锁。
其实drop table的这个问题完全是设计产生的问题,按照正常的处理逻辑,drop table 完全不需要处理buffer pool,buffer pool中要被删除的block完全可以通过LRU淘汰掉。这个改动会涉及到比较多的地方,看起来mysql还没有这么做的打算。
另外没有外键的情况下,mysql中的truncate是通过drop table + create table 的方式完成的(mysql_truncate->mysql_truncate_by_delete->mysql_delete->ha_delete_all_rows,sql_delete.cc->ha_innodb::ha_delete_all_rows,handler/ha_innodb.cc->row_truncate_table_for_mysql,row/row0mysql.c->fil_discard_tablespace,fil/fil0fil.c->fil_delete_tablespace,fil/fil0fil.c->os_file_delete,os/os0file.c),因此truncate table也会有drop table所带来的问题,所以drop和truncate大表最好都通过硬链接的方式进行。
参考链接:
http://www.mysqlperformanceblog.com/2011/02/03/performance-problem-with-innodb-and-drop-table/
http://bugs.mysql.com/bug.php?id=56332
http://bugs.mysql.com/bug.php?id=51325
http://bugs.mysql.com/bug.php?id=39939
http://bugs.mysql.com/bug.php?id=41158(LOCK_OPEN is hold while unlink())
http://bugs.mysql.com/bug.php?id=56655
http://bugs.mysql.com/bug.php?id=56696(truncate相关)
相关推荐
MySQL表drop恢复,使用 undrop-for-innodb恢复被drop的表。使用 undrop-for-innodb恢复被drop的表
DROP TABLE在不同数据库中的写法整理 1,MySql中 DROP TABLE IF EXISTS [table_name] 2,Oracle中: BEGIN EXECUTE IMMEDIATE 'DROP TABLE [table_name]'; EXCEPTION WHEN OTHERS THEN NULL; END; 3,在Sql ...
MySQL的: drop table if exists 表名; SQL Server的: IF EXISTS (SELECT name FROM sysobjects WHERE name = '表名' AND type = 'U') DROP TABLE 表名; Oracle的: create or replace table 表名 ...; -- 直接写...
MySQL在进行alter table等DDL操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)...
mysql> drop table MyClass; 4、插入数据 命令:insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )] 例如,往表 MyClass中插入二条记录, 这二条记录表示:编号为1的名为Tom的成绩为96.45...
DROP TABLE IF EXISTS tb_dept; 2,mysql创建表 CREATE TABLE tb_dept ( deptno INT NOT NULL COMMENT ‘编号’, deptname VARCHAR ( 10 ) NOT NULL COMMENT ‘名称’, deptaddr VARCHAR ( 20 ) NOT ...
掌握在MySQL中创建数据库和表的方法,理解MySQL支持的数据类型、数据完整性在MySQL下的表现形式,练习MySQL数据库服务器的使用,练习CREATE TABLE,SHOW TABLES,DESCRIBE TABLE,ALTER TABLE,DROP TABLE语句的操作...
删除表mysql> drop table Contact。 授权部分建立用户并授权mysql> grant all on location.* to gk1020@'10.1.11.71' identified by 'gk1020'。 取消授权mysql> revoke all on location.* from gk1020@'10.1.11.71'...
mysql的一些常用语句解释的很清楚 创建,删除和最基本查询: 显示数据库 mysql->show databases;...删除表 mysql->drop table mytable; 显示表的内容 mysql->show tables; 显示表的结构 mysql->describe mytable;
7.6 DROP DATABASE (抛弃数据库)句法 7.7 CREATE TABLE (创建表)句法 7.7.1 隐含(silent)的列指定变化 7.8 ALTER TABLE (改变表)句法 7.9 OPTIMIZE TABLE (优化表) 句法 7.10 DROP TABLE ...
用mysql的存储过程查询表中的数据 mysql数据库 tb_users表 use test; DROP TABLE IF EXISTS tb_users; CREATE TABLE IF NOT EXISTS tb_users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, password ...
+ 5.4.2 SELECT INTO TABLE + 5.4.3 事务(Transactions) + 5.4.4 存储过程和触发器 + 5.4.5 外键(Foreign Keys) # 5.4.5.1 不使用外键的理由 + 5.4.6 视图(Views) + 5.4.7 '--'作为一个 注解的开始 o 5.5 ...
14.1.2 参数处理 14.1.3 返回值和出错处理 14.1.4 编译并安装用户定义函数 14.2 增加一个新的原生(native)函数 15 为MySQL增加新过程 15.1 analyse过程 15.2 编写一个过程 ...
关于多级别菜单栏或者权限系统中部门上下级的树形遍历,oracle中有connect by来实现,mysql没有这样的便捷途径,所以MySQL遍历数据表是我们经常会遇到的头痛问题,下面通过存储过程来实现。 1,建立测试表和数据: ...
show databases; 刚开始时才两个数据库:mysql和test。mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。...drop table 表名; 7、将表中记录清空:
删除字段: alter table 表 drop [column] 字段名; 修改字段类型: alter table 表 modify 字段名 新的字段类型; 修改字段名称 : alter table 表 change 旧字段名 新字段名 字段类型; 修改表名称: alter table ...
掌握在MySQL中创建数据库和表的方法,理解MySQL支持的数据类型、数据完整性在MySQL下的表现形式,练习MySQL数据库服务器的使用,练习CREATE TABLE,SHOW TABLES,DESCRIBE TABLE,ALTER TABLE,DROP TABLE语句的操作...
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 4.导入数据库 A:常用source 命令 进入mysql数据库控制台, 如mysql -u root -p mysql>use 数据库 然后使用source命令,后面参数为脚本文件(如...
mysql>drop TABLE MYTABLE; 11:清空表 mysql>delete from MYTABLE; 12:更新表中数据 mysql>update MYTABLE set sex="f" where name='hyq'; posted on 2006-01-10 16:21 happytian 阅读(6) 评论(0) 编辑 收藏 收藏至...
drop table if exists `User`; create table User ( id int(11) not null auto_increment, name varchar(20), age int(11), primary key (id) ); #插入一些数据 insert into user values (NULL, 'name1', 11); ...