Chinaunix首页 | 论坛 | 博客
  • 博客访问: 612310
  • 博文数量: 38
  • 博客积分: 587
  • 博客等级: 中士
  • 技术积分: 579
  • 用 户 组: 普通用户
  • 注册时间: 2011-10-17 14:32
文章存档

2013年(15)

2012年(23)

分类: Mysql/postgreSQL

2013-08-13 10:53:15

 【学习目标】
1、pt-archiver工具的基本使用,和内部原理
2、理解过期数据归档和清理的方法
3、有哪些限制和注意事项
4、常常归档或者purge,一定要注意过滤字段,是否存在null, 日期字段是否存在zero date.
而null在MySQL数据库类型的大小情况

【pt-archiver使用的场景】
1、清理线上过期数据
2、清理过期数据,并把数据归档到本地归档表中,或者远端归档服务器
3、两张表之间的数据不完全相同,希望合并。此时加上–ignore或–replace选项,可以轻松实现
4、导出线上数据,到线下数据作处理

1、归档和清理过期数据
1)   归档是把过期的数据从线上表的,移到另一张归档表中(或数据文件中);
清理过期数据,是把过期数据直接从线上表中清理删除。
因为大部分的应用中,只使用近期的数据(工作集数据),如最近六个月。
共同目的:去除掉应用程序不使用的部分数据,使得热数尽可能的小。
2、归档和清理过期数据,都会把原表中的数据Delete掉,而大量Delete存在以下问题:
  1)对于MyISAM表,大的Delete操作,整个过程会lock表。
  2)  对于Innodb表,大的delete,导致一个长事务,使innodb性能下降,purge操作延迟,undo量大。
  3)导致从库延迟增大
3、使用分块删除原来数据, 如:delete xxx order by pk limit 1000;
这样删除比较整个删除好很多,但清理越来越慢。

4、pt-archiver使用删除的原理


二、pt-archiver的使用说明

用于清理过期数据purge

1、执行命令:pt-archiver  --source h=127.0.0.1,P=58886,D=test,t=t_archive --no-check-charset  --where 'insertdate<"2013-08-10"'  --purge --limit=2
1)     注意--source后的DSN之间不能空格出现,否则会出错。 --where条件的值,有字符串的,要用引号括起来。
2) --limit表示,每组一次删除多少条数据(注意:如果数据比较多时,也可以设置大一些,减少循环次数),最终的清理操作,还是通过Where pK=xx来处理的;
整个流程逻辑,请见上图的代码部分。

 
执行全过程打印的general_log
      46 Query     USE `test`

                   46 Query     SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`insertdate` FROM `test`.`t_archive` FORCE INDEX(`PRIMARY`) WHERE (insertdate<"2013-08-10") LIMIT 2
                   46 Query     DELETE FROM `test`.`t_archive` WHERE (`id` = '1')
                   46 Query     commit
                   46 Query     DELETE FROM `test`.`t_archive` WHERE (`id` = '11')
                   46 Query     commit
                   46 Query     SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`insertdate` FROM `test`.`t_archive` FORCE INDEX(`PRIMARY`) WHERE (insertdate<"2013-08-10") AND ((`id` >= '11')) LIMIT 2
                   46 Query     DELETE FROM `test`.`t_archive` WHERE (`id` = '12')
                   46 Query     commit
                   46 Query     DELETE FROM `test`.`t_archive` WHERE (`id` = '13')
                   46 Query     commit
                   46 Query     SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`insertdate` FROM `test`.`t_archive` FORCE INDEX(`PRIMARY`) WHERE (insertdate<"2013-08-10") AND ((`id` >= '13')) LIMIT 2
                   46 Query     DELETE FROM `test`.`t_archive` WHERE (`id` = '14')
                   46 Query     commit
                   46 Query     SELECT /*!40001 SQL_NO_CACHE */ `id`,`name`,`insertdate` FROM `test`.`t_archive` FORCE INDEX(`PRIMARY`) WHERE (insertdate<"2013-08-10") AND ((`id` >= '14')) LIMIT 2
                   46 Query     commit
                   46 Quit


2、用于把数据导出文件,不用删除原表中数据:
命令使用:
pt-archiver  --source h=127.0.0.1,P=58886,D=test,t=t_info_refresh  --no-check-charset  --where 'AddTime>"2013-05-02"'  --progress 4000  --no-delete --file "/tmp/pt-archiver.dat"  --limit=10000
参数说明:
       --statistics  结束的时候给出统计信息:开始的时间点,结束的时间点,查询的行数,归档的行数,删除的行数,以及各个阶段消耗的总的时间和比例,便于以此进行优化。
      --progress  每处理progress指定的行数后,就打印一次信息
      --no-delete :表示不删除原来的数据,注意:如果不指定此参数,所有处理完成后,都会清理原表中的数据。

导出数据的基本原理:
     通过“过滤条件”,得在此范围内的PK的最大值和最小值 , 然后通过PK索引, 过滤条件 + pk <此范围的最大值 + pk >(此范围内pk的最小值+每次增长limit指定的数) + limit. 这样直到处理完成。

SELECT /*!40001 SQL_NO_CACHE */ `id`,`infoid`,`sortid`,`addtime`,`cateid`,`userid`,`distribution`,`state` FROM `test`.`t_info_refresh` FORCE INDEX(`PRIMARY`) WHERE (A
ddTime>"2013-05-02") AND (`id` < '255410336') AND ((`id` > '255043917')) LIMIT 10000

3、把数据归档到线下数据库(字符集问题是否影响)
使用命令:是从线上数据表,归档到指定的线下数据。
pt-archiver  --source h=127.0.0.1,P=58886,D=test,t=t_info_refresh --dest h=10.5.12.13,P=58886,u=archiver,p=123,D=test,t=t_info_refresh  --no-check-charset  --where 'AddTime>"2013-05-02"'  --progress 5000  --no-delete --limit=10000 --statistics


阅读(7863) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

刀尖红叶2014-09-13 11:42:34

为什么“delete xxx order by pk limit 1000;”会越来越慢?

评论热议
请登录后评论。

登录 注册