Archive for September 6th, 2009
Benchmarking Metrics for MySQL Data Purge
MySQL是个非常优秀的免费数据库，下面是我周末做Data Purge的部分记录，可以用做将来的参考之用：
硬件：AMD Opteron 265 1.8G(Cache 1M), RAM:1G, HD: 350G，算是配置比较差的一台服务器。请注意，下面所提到的数据和您的服务器性能有很大的关系。
// Delete Data
2009-09-03 19:45:45 Start…
Row Count: 46500394(46M)
DELETE FROM atedata WHERE DATE < ’2009-06-01 00:00:00′
Row Count: 22699749(22M)
2009-09-03 21:18:06 End…
从上面可以看出，删除数据是比较慢的操作，46M条的数据，删除到22M条，总共用了大概93分钟，大概255,921rows/min。删除操作所需时间主要和将要删除的数据量是成正比的，删除的记录数越多，所用的时间也越多。
// Optimize Table
2009-09-04 21:33:54 Start…
Row Count: 23051455(23M) – 7.1GiB
optimize table atedata
Row Count: 23051455(23M) – 3.4GiB
2009-09-04 22:15:37 End…
对于数据量比较大的表，比如超过1G，如果只是简单的用DELETE删除了数据，这些数据所占据的磁盘空间并没有被释放，在MySQL里我们称之为：Overhead。只有对表做了OPTIMIZE操作，才能真正的释放它。
从上面的数据可以看出，优化前，表大小是7.1G，优化后表大小为3.4G，表的记录数没有变化，用时42分钟，平均88MiB每分钟。该操作耗时大致和(Table Size – Overhead)的差成正比。对于那种比较大的表，千万不要删除了几百条记录就做Optimize操作，因为它太耗费时间了，我们可以累积到一定数量再做。
Optimize命令执行后，MySQL会生产一个临时的.TMD文件，MySQL的状态显示Repair by sorting，它其实就是优化后的表。这个过程很漫长，做完后，还有一步是重建索引，MySQL状态显示Repair with Keycache，结束的时候会有个.TMM的临时文件生成，它可能就是.MYI文件的拷贝，不过官方文档没有说明，只是猜测。
在创建索引文件的过程中(Repair with Keycache)，它的耗时和表的索引多少有很大的关系，我没有验证，但我们可以猜测：索引越多，这个过程就越长。
由于在表很大的情况下，做类似DELETE、OPTIMIZE、ALTER等操作，都是非常的耗时间，所以我们在设计系统的时候就需要提前考虑到：
- 保存数据的时间长度
- 做Data Purge的周期、方法策略
- 数据备份的策略
- Data Purge的过程中，系统该如何应对（以上操作都会造成MySQL被锁定，数据库无法使用）
- 等等
如果数据量不大的话，另当别论。