MySQL删除大表更快的DROP TABLE办法

6月 2nd, 2011 | Posted by | Filed under 数据库

原文地址:http://www.mysqlops.com/2011/05/18/mysql%E5%88%A0%E9%99%A4%E5%A4%A7%E8%A1%A8%E6%9B%B4%E5%BF%AB%E7%9A%84drop-table%E5%8A%9E%E6%B3%95.html

 

曾经发文介绍过,DROP table XXX ,特别是碰到大表时,
http://www.mysqlops.com/2011/02/18/mysql-drop-table-%e5%a4%84%e7%90%86%e8%bf%87%e7%a8%8b.html
在DROP TABLE 过程中,所有操作都会被HANG住。
这是因为INNODB会维护一个全局独占锁(在table cache上面),直到DROP TABLE完成才释放。
在我们常用的ext3,ext4,ntfs文件系统,要删除一个大文件(几十G,甚至几百G)还是需要点时间的。
下面我们介绍一个快速DROP table 的方法; 不管多大的表,INNODB 都可以很快返回,表删除完成;
实现:巧用LINK(硬链接)

实测:

root@127.0.0.1 : test 21:38:00> show table status like ‘tt’ \G
*************************** 1. row ***************************
Name: tt
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 151789128
Avg_row_length: 72
Data_length: 11011096576
Max_data_length: 0
Index_length: 5206179840
Data_free: 7340032
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.22 sec)

root@127.0.0.1 : test 21:39:34> drop table tt ;
Query OK, 0 rows affected (25.01 sec)

删除一个11G的表用时25秒左右(硬件不同,时间不同);

下面我们来对另一个更大的表进行删除;
但之前,我们需要对这个表的数据文件做一个硬连接:

root@ # ln stock.ibd stock.id.hdlk
root@ # ls stock.* -l
-rw-rw—- 1 mysql mysql        9196 Apr 14 23:03 stock.frm
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.ibd
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk

你会发现stock.ibd的INODES属性变成了2;

下面我们继续来删表。

root@127.0.0.1 : test 21:44:37> show table status like ‘stock’ \G
*************************** 1. row ***************************
Name: stock
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 49916863
Avg_row_length: 356
Data_length: 17799577600
Max_data_length: 0
Index_length: 1025507328
Data_free: 4194304
Auto_increment: NULL
Create_time: 2011-05-18 14:55:08
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.23 sec)

root@127.0.0.1 : test 21:39:34> drop table stock ;
Query OK, 0 rows affected (0.99 sec)

1秒不到就删除完成; 也就是DROP TABLE不用再HANG这么久了。
但table是删除了,数据文件还在,所以你还需要最后数据文件给删除。

root # ll
total 19096666112
-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk
root # rm stock.id.hdlk
虽然DROP TABLE 多绕了几步。(如果你有一个比较可靠的自运行程序(自动为大表建立硬链接,并会自动删除过期的硬链接文件),就会显得不那么繁琐。)
这样做能大大减少MYSQL HANG住的时间; 相信还是值得的。

至于原理: 就是利用OS HARD LINK的原理,
当多个文件名同时指向同一个INODE时,这个INODE的引用数N>1, 删除其中任何一个文件名都会很快.
因为其直接的物理文件块没有被删除.只是删除了一个指针而已;
当INODE的引用数N=1时, 删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时;

好了. 大家试试吧.

标签: ,

自编译MySQL指南 2.0

4月 13th, 2011 | Posted by | Filed under 数据库

原文:http://www.mysqlops.com/2011/03/06/mysql_compile_reference.html

一般情况下,用户选择的MySQL安装方式为 RPM包 或 二进制压缩包,但是,通用安装包为了适应不同的软硬件平台,都会采用保守的编译方式,功能上也是选择最常用最稳定的功能编译入二进制版本。
虽然这满足了大部分用户的需求,但是有时我们仅仅需要一部分功能(例如我们不需要Query Cache,但这个模块编译时不去掉的话,运行时依然会触发其代码清理Query Cache内存池,并引发过Bug),或者有性能更好的商业编译器(例如ICC),或者对源码做了修改时,就必须采用编译的方式来安装了。

下面我们就来介绍下如何从源码编译安装MySQL。
阅读全文…

评论:同学会催生“恐聚族” 攀比斗富炫耀成风

2月 6th, 2011 | Posted by | Filed under 随笔日记

http://news.163.com/11/0206/03/6S67VFQM00011229.html

网易这篇文章真是说到了点子上,回家感同身受。

没有人关心生活怎么样,没有人关心工作的意义,只在乎有多少钱,甚至家长也是这样,这是一种多么病态的社会。

一个评论说到了我的感受:
国内已经完全畸形了。德国人该比中国人富得多吧,可是年轻人都买二手车,汽车排量多数都在1.0-1.4升。教授在这里绝对是富人,可是许多教授开着小破车乐颠乐颠的上班。大学的清洁工大妈在教授面前绝对不会低人一等。倒是校长在任何人面前都得客客气气。
因为人们有生活,有追求,有尊严。而国内唯一的最求就是钱和权。而且这钱和权来的越不正当,越说明“有本事”。知识不能改变命运,勤劳不能致富。发达的最有效最便捷途径就是无耻,无耻无底线。
我忍受不了这样的社会,也没有能力改变它,只能通过自己的拼搏冲出国门,换一个环境。新的一年,祝各位善良的人都能通过自己的努力,成功逃离苦海。

标签:

[译]InnoDB官方博客:InnoDB Plugin的性能和可伸缩性

1月 27th, 2011 | Posted by | Filed under 数据库

原文地址:http://blogs.innodb.com/wp/2009/03/plug-in-for-performance-and-scalability/

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:
为什么你应该关注最近的InnoDB Plugin 1.0.3版?一个词:性能!这个版本包括了这些特性

  • Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
  • 增强的并发可可伸缩性:”Google 多处理机 补丁” 为Mutext锁操作使用原子操作
  • More efficient memory allocation: ability to use more scalable platform memory allocator
  • 更有效的内存分配:可以使用更多的可扩展内存 分配器(例如tcmalloc)
  • Improved out-of-the-box scalability: unlimited concurrent thread execution by default
  • 改进的即装即用扩展性:默认无限制的线程并发
  • Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing
  • 动态优化调整:在运行时,打开或者关闭插入缓 存和自适应哈希索引

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements mean for performance and scalability?
这些新的性能特新可以提升多大两倍甚至更多的的吞吐量,这依赖于你的负载,平台和其他调整事项。在另一篇文章中,我们会探 讨这些改变的一些细节,但首先,我们现探讨这些性能和可扩展性的增强是什么意思,包括哪些内容

In brief, we’ve tested three different workloads (joins, DBT2 OLTP and a modified sysbench) using a memory-resident database. In all cases, the InnoDB Plugin scales significantly better than the built-in InnoDB in MySQL 5.1. And in some cases, the absolute level of performance is dramatically higher too! The charts below illustrate the kinds of performance gains we’ve measured with release 1.0.3 of the InnoDB Plugin. Your mileage may vary, of course. See the InnoDB website for all the details on these tests.
总之,我们已经使用内存驻留数据库(所有数据都载入在内存中)测试了三种不同的工作负载(关联,DBT2 OLTP和修改过的sysbench)。在所有的情况下,InnoDB Plugin的伸缩性明显优于MySQL 5.1内置的InnoDB。在一些场景中,性能提升的水平高的惊人。下面的图说明了InnoDB Plugin 1.0.3的性能提升。你的测试结果可能不同,当然可以在InnoDB网站看到所有测试的细节。

This release of the InnoDB Plugin incorporates a patch made by Ben Handy and Mark Callaghan at Google to improve multi-core scalability by using more efficient synchronization methods (mutexing and rw-locks) to reduce cpu utilization and contention. We’re grateful for this contribution, and you will be too!
这个InnoDB Plugin版本包含了Google的Ben Handy和Mark Callaghan的补丁来提升多处理机扩展性,包括使用了更有效的同步机制(Mutexing和RW-Locks)来减少CPU利用和竞争。我们非常感 谢这个补丁的贡献,相信你也是。

Now to our test results …
现在来看我们的测试结果

Joins: The following chart shows the performance gains in performing joins, comparing the built-in InnoDB in MySQL (in blue) with the InnoDB Plugin 1.0.3 (in red).
关联:下图展示了执行Join操作时的性能提升,内置InnoDB(蓝)和InnoDB Plugin 1.0.3(红)的比较。

As you can see from the blue bars in the above chart, with MySQL 5.1 using the built-in InnoDB, the total number of joins the system can execute declines as the number of concurrent users increases. In contrast, the InnoDB Plugin slightly improves performance even with one user, and maintains performance as the number of users rises. This performance improvement is due in large part to the use of atomics for mutexing in the InnoDB Plugin.
正如你在上面蓝柱上看到的,MySQL 5.1的内置InnoDB,随着并发数的增加系统的执行速度反而下降了。与此相反,InnoDB Plugin随着并发的提升处理速度甚至略有提高,并且随着用户的增长保持着这种性能。这个性能改善很大程度上是因为对Mutexing使用了原子操作。

Transaction Processing (DBT2): The following chart illustrates a scalability improvement using the OLTP read/write DBT2 benchmark, again comparing the performance of the built-in InnoDB in MySQL with the performance of InnoDB Plugin 1.0.3.
事务处理(DBT2):下入展示了用DBT2测试OLTP读写性能的提升,再次比较了内置InnoDB和InnoDB Plugin 1.0.3的性能。

Here, the InnoDB Plugin scales better than the built-in InnoDB from 16 to 32 users and produces about 12% more throughput with 64 concurrent users, as other bottlenecks are encountered or system capacity is reached. This improvement is likewise due primarily to the changes in mutexing.
这里,InnoDB Plugin伸缩性在16增加到32线程时表现更好,产生比64线程多大约12%的吞吐量。由于其他性能瓶颈或系统容量达到基线。这个提升依然主要依赖于 Mutexing的改变。

Modified Sysbench: This test uses a version of the well-known sysbench workload, modified to include queries based on a secondary index, as suggested by Mark Callaghan of Google.
修改过的sysbench:这个测试使用了著名的sysbench,修改包括基于非主键索引的查询,由Google的 Mark Callaghan建议。

This time, the InnoDB Plugin shows significantly better scalability from 8 to 64 users than the built-in InnoDB in MySQL, yielding as much as 60% more throughput at 64 users. Like the previous examples, this improvement is largely due to the use of atomics for mutexing.
这次,InnoDB Plugin在8~64线程都展示了明显优于内置InnoDB的可伸缩性。在64并发时多大60%的性能提升!像前一个例子,这个提升依然主要靠 Mutexing的原子性。

Modified Sysbench with tcmalloc: This test uses the same modified sysbench workload, but shows the difference between the built-in InnoDB (which uses the internal InnoDB memory allocator) and the InnoDB Plugin when using a more scalable memory allocator, in this case tcmalloc.
使用tcmalloc的修改过的sysbench:这种测试使用相同的sysbench场景,但是不同于内置InnoDB 的是InnoDB Plugin使用了tcmalloc作为内存分配器。

When the new configuration parameter innodb_use_sys_malloc is set to enable use of the memory allocator tcmalloc, the InnoDB Plugin really shines! Transaction throughput continues to scale, and the actual throughput with 64 users has nearly doubled!
当设置innodb_user_sys_malloc变量为tcmalloc作为内存分配器时,InnoDB Plugin依然是亮点!事务吞吐量继续扩展,在64并发时吞吐量提升接近1倍(相对没有tcmalloc的)。

标签: ,

MySQL小技巧问答(一)

1月 19th, 2011 | Posted by | Filed under 数据库

抽空总结一下自己操作MySQL的一些心得体会,做成MySQL小技巧问答系列,给大家作为一些案例参考,也为我自己做一些记录:

1. 在基于ROW的双Master复制下,如何快速大批量订正?
在A<->B的双Master结构下,假设只有一台提供服务,这是我们常用的架构,需要大批量订正数据,如何做最快?用存储过程一批批提交?这有很多的限制,有时候并不可以把一条或多条SQL拆成几段,怎么办呢?binlog不是很好的工具嘛?! ROW格式的binlog,Slave在应用时是直接使用Handler API,并没有走SQL解析,速度非常快,基本上是IO操作了,那么我们可以在备库上直接执行订正SQL,产生的ROW binlog传到主机,就会很快订正完,基本上都比写存储过程快。

2. ROW格式Replication如何实现不带库名的replicate-do-db?
虽然MySQL有replicate-do-db这个参数,但是在ROW格式的binlog下必须使用”db.table”的方式才能生效,USE对ROW格式是无效的。现在我有一个Instance,只需要复制Master的某几个库,但是是ROW格式,SQL都没有使用db前缀,怎么办?可以这么做,把主库需要的库导出来,不需要的库导出结构即可,在Slave导入这些数据及结构,配置skip-slave-errors=all,这样Master复制过来的binlog,只要发现有库有表结构,就不会报找不到表,就不会阻塞复制,但是UPDATE/DELETE过来没有数据也会被跳过错误,间接的实现了replicate-do-db。

3. 大批量乱序数据导入InnoDB很慢如何解决?
InnoDB因为主键聚集索引的关系,如果没有主键或者主键非序列的情况下,导入会越来越慢,如何快速的迁移数据到InnoDB?借助MyISAM的力量是很靠谱的,先关闭InnoDB的Buffer Pool,把内存空出来,建一张没有任何索引的MyISAM表,然后只管插入吧,concurrent_insert=2,在文件末尾并发插入,速度刚刚的,插入完成后,ALTER TABLE把索引加上,记得还有ENGINE=InnoDB,就把MyISAM转到InnoDB了,这样的速度远比直接往InnoDB里插乱序数据来得快。

4. A<–>B–>C–>D结构切换到A<–>B, C<–>D结构出现Slave_lag一直增常如何避免?
这种情况常见与一个双Master集群分离出一套双Master集群,例如从原集群分离一部分库。过快的切换B–>C到C<–>D容易导致主备出现slave_lag,并且一直增长,原因在于A<–>B集群产生的SQL,随同server_id带到了C–>D这个M-S中,当A,B产生的SQL在C,D还没消化完成就CHANGE MASTER为C<–>D时,会导致这写SQL在C,D之间来回传输,因为C,D都认为这个SQL不是自己产生的,因而不销毁,自己执行后写入binlog,于是Slave_Lag就一直增长。
避免的方法很简单,部分写切到C后,先断开B–>C的复制,等一会,看D上已经没有Slave_Lag了,再CHANGE MASTER为C<–>D,这样A,B传过来的SQL都消化完了。

5. 表中存在很多重复数据时,如何删除这些重复数据最快?
在需要给表中某些字段加唯一索引时,而字段中又存在需要重复清理数据的问题,不少DBA都应该遇到过。一般在处理时总是想在数据库中只保留一条,其他的删除,但是这样的SQL写出来总是效率不高,怎么办?其实可以转换思路,把重复的都选出一条出来,存到一张临时表,然后删除原表中所有存在重复的,再把临时表的数据库全部插入原库,这是比较通用并且高效的做法。

标签: , ,