JBPM数据清理

接手一个JBPM3项目, MYSQL INNODB ibdata1都50GB了, 关联查询processdefinition, processintance很慢, 需要清楚一些过期的processinstance.

使用GraphSession.deleteProcessInstance删? 一两秒才能删一个, 几十万条, outage不允许.

网上一哥们使用delete from ... where processinstance in (...) 批量的删, 慢到你不信, SET FOREIGN_KEY_CHECKS=0;也不好使.

 

jbpm_token 70万条, jbpm_variableinstance 700万条, jbpm_bytearray, jbpm_log, jbpm_byteblock好像都是千万条的级别.

用户要求保留近期的数据不能直接drop掉.

 

硬着头皮用power designer导入这些jbpm_表慢慢分析这些关联.

DROP TABLE IF EXISTS TMP_PROCESSINSTANCE; DROP TABLE IF EXISTS TMP_TOKEN; DROP TABLE IF EXISTS TMP_MODULEINSTANCE; DROP TABLE IF EXISTS TMP_SWIMLANEINSTANCE; DROP TABLE IF EXISTS TMP_TOKENVARIABLEMAP; DROP TABLE IF EXISTS TMP_VARIABLEINSTANCE; DROP TABLE IF EXISTS TMP_TASKINSTANCE; DROP TABLE IF EXISTS TMP_TASKACTORPOOL; DROP TABLE IF EXISTS TMP_LOG; DROP TABLE IF EXISTS TMP_BYTEARRAY; select * from jbpm_processdefinition into outfile 'c:/jbpm_processdefinition.sql'; select * from `jbpm_processinstance` pi where pi.`end_` > '2009-08-01 00:00:00' into outfile 'c:/jbpm_processinstance.sql'; #Assume we create a tmp table named tmp_processinstance create table TMP_PROCESSINSTANCE (ID_ bigint not null auto_increment, VERSION_ integer not null, START_ datetime, END_ datetime, ISSUSPENDED_ bit, PROCESSDEFINITION_ bigint, ROOTTOKEN_ bigint, SUPERPROCESSTOKEN_ bigint, primary key (ID_)) type=InnoDB; load data infile 'c:/jbpm_processinstance.sql' into table TMP_PROCESSINSTANCE; #Each processinstance owns one roottoken and so roottoken is not a condition select t.* from `jbpm_token` t,TMP_PROCESSINSTANCE pi where t.`PROCESSINSTANCE_`=pi.`ID_` or t.`ID_`=pi.`ROOTTOKEN_` or t.`SUBPROCESSINSTANCE_`=pi.`ID_` or t.`ID_`=pi.`SUPERPROCESSTOKEN_` into outfile 'c:/jbpm_token.sql'; #Assume the table name is tmp_token create table TMP_TOKEN (ID_ bigint not null auto_increment, VERSION_ integer not null, NAME_ varchar(255), START_ datetime, END_ datetime, NODEENTER_ datetime, NEXTLOGINDEX_ integer, ISABLETOREACTIVATEPARENT_ bit, ISTERMINATIONIMPLICIT_ bit, ISSUSPENDED_ bit, NODE_ bigint, PROCESSINSTANCE_ bigint, PARENT_ bigint, SUBPROCESSINSTANCE_ bigint, primary key (ID_)) type=InnoDB; load data infile 'c:/jbpm_token.sql' into tab